SELECT
  distinct ch.caseID,
  ch.caseReference,
  ch.statusCode,
  cr.concernRoleName,
  ch.startDate,
  pd.productType
INTO
  :caseID,
  :caseReference,
  :status,
  :primaryClient,
  :startDate,
  :productType 
FROM
  ConcernRole cr,
  OrgObjectLink cool,
  CaseUserRole cur,
  CaseHeader ch 
  
  LEFT OUTER JOIN
  
  ProductDelivery pd 
  ON pd.caseID = ch.caseID 
WHERE ch.statusCode = :caseStatus 
  AND pd.productType = :productType 
  AND ch.caseTypeCode = :caseType 
  AND cr.concernRoleID = ch.concernRoleID 
  AND ch.ownerOrgObjectLinkID = cool.orgObjectLinkID 
  AND cool.orgObjectLinkID = cur.orgObjectLinkID 
  AND cool.orgObjectType = :organisationObject 
  AND 
  (
    cool.orgObjectReference IN 
    ( 
    SELECT
      pos.positionID 
    FROM
      OrgUnitPositionLink ouposlink,
      Position pos 
    WHERE ouposlink.positionID = pos.positionID 
      AND ouposlink.recordStatus <> :recordStatus 
      AND pos.positionID = :objectReference 
      AND ouposlink.organisationUnitID IN 
      ( 
      SELECT
        ou.organisationUnitID 
      FROM
        OrganisationUnit ou,
        OrgUnitPositionLink opl,
        PositionHolderLink phl,
        Position pos2 
      WHERE phl.userName = :supervisorUserName 
        AND phl.positionID = opl.positionID 
        AND opl.positionID = pos2.positionID 
        AND pos2.leadPositionInd = '1' 
        AND opl.organisationUnitID = ou.organisationUnitID 
        AND opl.recordStatus <> :recordStatus 
        AND phl.recordStatus <> :recordStatus 
      
      UNION
        
      SELECT
        OrgUnitParentLink.organisationUnitID 
      FROM
        OrgUnitParentLink 
      WHERE OrgUnitParentLink.recordStatus <> :recordStatus 
        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 = :supervisorUserName 
          AND opl1.recordStatus <> :recordStatus 
          AND phl1.recordStatus <> :recordStatus 
        ) 
      ) 
    
    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 = :supervisorUserName 
      AND phl2.recordStatus <> :recordStatus 
      AND prl.recordStatus <> :recordStatus 
    ) 
    OR cool.orgObjectReference IN 
    ( 
    SELECT
      ou.organisationUnitID 
    FROM
      OrganisationUnit ou,
      OrgUnitPositionLink opl,
      PositionHolderLink phl,
      Position pos2 
    WHERE phl.userName = :supervisorUserName 
      AND pos2.leadPositionInd = '1' 
      AND ou.organisationUnitID = :objectReference 
      AND phl.positionID = opl.positionID 
      AND opl.positionID = pos2.positionID 
      AND opl.organisationUnitID = ou.organisationUnitID 
      AND opl.recordStatus <> :recordStatus 
      AND phl.recordStatus <> :recordStatus 
    ) 
    OR cool.orgObjectReference IN 
    ( 
    SELECT
      wq.workQueueID 
    FROM
      WorkQueue wq 
    WHERE wq.workQueueID = :objectReference 
      OR 
      (
        wq.workQueueID IN 
        ( 
        SELECT
          wqs.workQueueID 
        FROM
          WorkQueueSubscription wqs 
        WHERE 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 <> :recordStatus 
            AND OrgUnitPositionLink.recordStatus <> :recordStatus 
            AND Position.recordStatus <> :recordStatus 
            AND Position.positionID IN 
            ( 
            SELECT
              OrgUnitPositionLink.positionID 
            FROM
              OrgUnitPositionLink 
            WHERE OrgUnitPositionLink.recordStatus <> :recordStatus 
              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 = :supervisorUserName 
                AND phl.recordStatus <> :recordStatus 
                AND opl.recordStatus <> :recordStatus 
              
              UNION
                
              SELECT
                OrgUnitParentLink.organisationUnitID 
              FROM
                OrgUnitParentLink 
              WHERE OrgUnitParentLink.recordStatus <> :recordStatus 
                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 = :supervisorUserName 
                  AND opl1.recordStatus <> :recordStatus 
                  AND phl1.recordStatus <> :recordStatus 
                ) 
              ) 
            
            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 = :supervisorUserName 
              AND phl2.recordStatus <> :recordStatus 
              AND prl.recordStatus <> :recordStatus 
            ) 
          ) 
        ) 
      ) 
    ) 
    OR cool.userName IN 
    ( 
    SELECT
      Users.userName 
    FROM
      Position,
      PositionHolderLink,
      Users 
    WHERE Position.positionID = PositionHolderLink.positionID 
      AND PositionHolderLink.userName = Users.userName 
      AND Users.userName = :assignedTo 
      AND PositionHolderLink.recordStatus <> :recordStatus 
      AND Position.recordStatus <> :recordStatus 
      AND Position.positionID IN 
      ( 
      SELECT
        OrgUnitPositionLink.positionID 
      FROM
        OrgUnitPositionLink 
      WHERE OrgUnitPositionLink.recordStatus <> :recordStatus 
        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 = :supervisorUserName 
          AND phl.recordStatus <> :recordStatus 
          AND opl.recordStatus <> :recordStatus 
        
        UNION
          
        SELECT
          OrgUnitParentLink.organisationUnitID 
        FROM
          OrgUnitParentLink 
        WHERE OrgUnitParentLink.recordStatus <> :recordStatus 
          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 = :supervisorUserName 
            AND opl1.recordStatus <> :recordStatus 
            AND phl1.recordStatus <> :recordStatus 
          ) 
        ) 
      
      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 = :supervisorUserName 
        AND phl2.recordStatus <> :recordStatus 
        AND prl.recordStatus <> :recordStatus 
      ) 
    ) 
  ) 
ORDER BY pd.productType ASC