SELECT
  cr.concernRoleName,
  ch.caseID,
  ch.caseReference,
  ch.caseTypeCode,
  ch.concernRoleID,
  ch.statusCode,
  ch.startDate,
  pd.productType,
  cool.userName
INTO
  :primaryClient,
  :caseID,
  :caseReference,
  :caseTypeCode,
  :concernRoleID,
  :status,
  :startDate,
  :productType,
  :userName 
FROM
  ConcernRole cr,
  CaseHeader ch 
  
  INNER JOIN 

  GenericBatchProcessInput gbp  
  ON  (ch.caseID = gbp.recordID AND gbp.resultStatus IN ('BPRS_IN_PR', 'BPRS_N_PRO' ) ) 

  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.userName = :userName 
ORDER BY ch.caseReference ASC