SELECT 
AdminLegalAction.legalActionNameCode, 
CaseHeader.effectiveDate,
CaseHeader.startDate, 
CaseHeader.expectedEndDate,
CaseHeader.endDate,
ConcernRole.concernRoleName,
AdminLegalActionCategory.legalCategoryNameCode,
LegalAction.statusCode

INTO
:orderNameCode,
:effectiveDate,
:issuedDate,
:expectedEndDate,
:actualEndDate,
:concernRoleName,
:hearingCategoryCode,
:status

FROM 
CaseHeader, 
AdminLegalAction, 
LegalOrder, 
LegalAction,
AdminLegalActionCategory,
ConcernRole

WHERE 
CaseHeader.caseID =:caseID AND 
ConcernRole.concernRoleID = CaseHeader.concernRoleID AND
LegalOrder.caseID = CaseHeader.caseID AND
LegalAction.caseID = LegalOrder.caseID AND
AdminLegalActionCategory.legalActionCategoryID = LegalAction.configLegalActionCategoryID  AND
LegalAction.configLegalActionID = AdminLegalAction.legalActionID