SELECT
  cr.concernRoleName,
  ch.caseID,
  ch.caseReference,
  ch.caseTypeCode,
  ch.concernRoleID,
  ch.statusCode,
  ch.startDate,
  pd.productType,
  cool.userName,
  ch.integratedCaseType,  
  ind.investigationType, 
  id.issueType,
  spd.servicePlanType, 
  ac.assessmentType,
 sc.name
INTO
  :primaryClient,
  :caseID,
  :caseReference,
  :caseTypeCode,
  :concernRoleID,
  :status,
  :startDate,
  :productType,
  :userName,
  :integratedCaseType,
  :investigationType,
  :issueType,
  :servicePlanType,
  :assessmentType,
  :name
FROM
  ConcernRole cr,
  CaseHeader ch 

LEFT OUTER JOIN  
InvestigationDelivery ind 
ON 
ind.caseID=ch.caseID

LEFT OUTER JOIN  IssueDelivery id 
ON 
id.caseID=ch.caseID
	
LEFT OUTER JOIN   
(
SELECT
ServicePlan.ServicePlanType, 
ServicePlanDelivery.caseID
FROM 
ServicePlan, 
ServicePlanDelivery 
WHERE 
 ServicePlan.servicePlanID=ServicePlanDelivery.servicePlanID
)   spd 
ON 
spd.caseID=ch.caseID
	
LEFT OUTER JOIN
 (
SELECT
AssessmentConfiguration.assessmentType, 
AssessmentDelivery.caseID 
FROM 
AssessmentDelivery, 
AssessmentConfiguration 
WHERE 
AssessmentConfiguration.assessmentConfigurationID = AssessmentDelivery.assessmentConfigurationID
)  ac 
ON 
ac.caseID=ch.caseID
	
LEFT OUTER JOIN
 (
SELECT
ScreeningConfiguration.name,
Screening.caseID
FROM 
Screening,ScreeningConfiguration 
WHERE 
 Screening.screeningConfigID=ScreeningConfiguration.screeningConfigID
) sc 
ON sc.caseID=ch.caseID
  
  LEFT OUTER JOIN
  
  ProductDelivery pd 
  ON ch.caseID = pd.caseID,
  OrgObjectLink cool 
WHERE ch.statusCode <> :status 
  AND cr.concernRoleID = ch.concernRoleID 
  AND ch.ownerOrgObjectLinkID = cool.orgObjectLinkID 
  AND cool.orgObjectType = :orgObjectType 
  AND cool.orgObjectReference IN 
  ( 
  SELECT
    ou.organisationUnitID 
  FROM
    OrganisationUnit ou ,
    OrgUnitPositionLink opl ,
    PositionHolderLink phl ,
    Position pos2 
  WHERE phl.userName = :userName 
    AND pos2.leadPositionInd = '1' 
    AND phl.positionID = opl.positionID 
    AND opl.positionID = opl.positionID 
    AND opl.organisationUnitID = ou.organisationUnitID 
    AND opl.recordStatus <> :status 
    AND phl.recordStatus <> :status 
  ) 
ORDER BY ch.caseReference ASC