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
    WQ.workQueueID 
  FROM
    WorkQueue wq,
    WorkQueueSubscription wqs 
  WHERE wq.workQueueID = wqs.workQueueID 
    AND wqs.userName IN 
    ( 
    SELECT
      Users.userName 
    FROM
      Position,
      PositionHolderLink,
      OrganisationUnit,
      OrgUnitPositionLink,
      Users 
    WHERE Position.positionID = PositionHolderLink.positionID 
      AND OrgUnitPositionLink.positionID = PositionHolderLink.positionID 
      AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
      AND PositionHolderLink.userName = Users.userName 
      AND PositionHolderLink.recordStatus <> :status 
      AND OrgUnitPositionLink.recordStatus <> :status 
      AND Position.recordStatus <> :status 
      AND Position.positionID IN 
      ( 
      SELECT
        OrgUnitPositionLink.positionID 
      FROM
        OrgUnitPositionLink 
      WHERE OrgUnitPositionLink.recordStatus <> :status 
        AND OrgUnitPositionLink.organisationUnitID IN 
        ( 
        SELECT
          opl.organisationUnitID 
        FROM
          OrgUnitPositionLink opl,
          PositionHolderLink phl,
          Position pos 
        WHERE pos.positionID = phl.positionID 
          AND phl.positionID = opl.positionID 
          AND pos.leadPositionInd = '1' 
          AND phl.userName = :userName 
          AND phl.recordStatus <> :status 
          AND opl.recordStatus <> :status 
        
        UNION
          
        SELECT
          OrgUnitParentLink.organisationUnitID 
        FROM
          OrgUnitParentLink 
        WHERE OrgUnitParentLink.recordStatus <> :status 
          AND OrgUnitParentLink.parentOrganisationUnitID IN 
          ( 
          SELECT
            opl1.organisationUnitID 
          FROM
            OrgUnitPositionLink opl1 ,
            PositionHolderLink phl1 ,
            Position pos1 
          WHERE pos1.positionID = phl1.positionID 
            AND pos1.leadPositionInd = '1' 
            AND phl1.positionID = opl1.positionID 
            AND phl1.userName = :userName 
            AND opl1.recordStatus <> :status 
            AND phl1.recordStatus <> :status 
          ) 
        ) 
      
      UNION
        
      SELECT
        PRL.positionID 
      FROM
        PositionReportingLink prl ,
        PositionHolderLink phl2 ,
        Position pos2 
      WHERE phl2.positionID = prl.reportsToID 
        AND phl2.positionID = pos2.positionID 
        AND pos2.leadPositionInd = '1' 
        AND phl2.userName = :userName 
        AND phl2.recordStatus <> :status 
        AND prl.recordStatus <> :status 
      ) 
    ) 
  ) 
ORDER BY ch.caseReference ASC