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 
		pd.caseID = ch.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