SELECT 
CaseHeader.caseID,
Product.name,
CaseHeader.caseReference,
CaseHeader.startDate,
CaseHeader.statusCode,
CaseHeader.endDate,
ConcernRole.concernRoleID,
ConcernRole.concernRoleName,
ConcernRole.primaryAlternateID,
ConcernRole.primaryPhoneNumberID,
EmailAddress.emailAddress,
Address.addressData,
OrgObjectLink.orgObjectLinkID,
(SELECT 
MAX (ProductDeliveryCertDiary.periodToDate)
FROM ProductDeliveryCertDiary 
WHERE ProductDeliveryCertDiary.statusCode = :certRecordStatus
AND ProductDeliveryCertDiary.caseID = ProductDelivery.caseID
GROUP BY ProductDeliveryCertDiary.caseID
) periodToDate, 
OverpaymentEvidenceCount.overpaymentsCount,
OverpaymentCasesCount.overpaymentCasesCount,
UnderpaymentCasesCount.underpaymentCasesCount,
ActiveDeductionsCount.activeDeductionsCount,
ActiveDeductionsCount.totalActiveDeductions,
CaseReviewDate.latestScheduledDate,
CaseReviewDate.reviewDate,
LatestCaseDecision.decisionDate,
LatestCaseDecision.caseDecisionID,
UnderpaymentCount.underpaymentsCount,
IssueCount.issuesCount,
(SELECT 
COUNT (Verification.verificationID)
FROM Verification, 
VDIEDLink, 
EvidenceDescriptor 
WHERE Verification.verificationLinkedType = :productDeliveryLinkType
AND Verification.VDIEDLinkID = VDIEDLink.VDIEDLinkID 
AND Verification.verificationStatus = :nonVerifiedStatus 
AND VDIEDLink.evidenceDescriptorID = EvidenceDescriptor.evidenceDescriptorID 
AND 
(
	EvidenceDescriptor.statusCode = :evidenceDescriptorActive OR 
	EvidenceDescriptor.statusCode = :evidenceDescriptorInedit
) 
AND Verification.verificationLinkedID = ProductDelivery.caseID  
GROUP BY Verification.verificationLinkedID
) verificationsCount, 
(SELECT 
COUNT (EvidenceDescriptor.caseID)
FROM EvidenceDescriptor, 
CaseHeader 
WHERE EvidenceDescriptor.caseID = CaseHeader.caseID 
AND EvidenceDescriptor.statusCode = :evidenceDescriptorInedit 
AND EvidenceDescriptor.caseID = ProductDelivery.caseID 
GROUP BY EvidenceDescriptor.caseID
) inEditEvidenceCount, 
(SELECT 
COUNT (InstructionLineItem.caseID) 
FROM InstructionLineItem 
WHERE InstructionLineItem.creditDebitType = :debitILIType 
AND InstructionLineItem.instructLineItemCategory = :paymentILICategory 
AND InstructionLineItem.caseID = ProductDelivery.caseID 
GROUP BY InstructionLineItem.caseID
) paymentILICount 

INTO
:caseID,
:productName,
:caseReference,
:startDate,
:statusCode,
:endDate,
:concernRoleID,
:concernRoleName,
:clientPrimaryAlternateID,
:clientPhoneNumberID,
:clientEmailAddress,
:clientAddressData,
:ownerOrgObjectLinkID,
:certifiedToDate,
:overpaymentsCount,
:overpaymentCasesCount,
:underpaymentCasesCount,
:activeDeductionsCount,
:totalActiveDeductions,
:reviewScheduledStartDate,
:reviewExpectedCompletionDate,
:latestCaseDecisionDate,
:latestCaseDecisionID,
:underpaymentsCount,
:issuesCount,
:verificationsCount,
:inEditEvidenceCount,
:paymentILICount

FROM CaseHeader, 
ConcernRole 
LEFT OUTER JOIN EmailAddress 
  ON ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID
LEFT OUTER JOIN Address 
  ON ConcernRole.primaryAddressID = Address.addressID,
OrgObjectLink,
ProductDelivery 

LEFT OUTER JOIN(
SELECT 
COUNT (OverpaymentEvidence.caseID) AS overpaymentsCount,
OverpaymentEvidence.relatedCaseID 
FROM OverpaymentEvidence, 
CaseHeader 
WHERE OverpaymentEvidence.relatedCaseID = :caseID 
AND CaseHeader.caseID = OverpaymentEvidence.relatedCaseID 
GROUP BY OverpaymentEvidence.relatedCaseID
) OverpaymentEvidenceCount 
ON (OverpaymentEvidenceCount.relatedCaseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
COUNT (CaseRelationship.caseID) AS overpaymentCasesCount, 
CaseRelationship.caseID 
FROM CaseRelationship, 
CaseHeader, 
OverpaymentEvidence 
WHERE CaseRelationship.relatedCaseID = :caseID 
AND CaseRelationship.caseID = OverpaymentEvidence.relatedCaseID 
AND CaseHeader.caseID = OverpaymentEvidence.caseID 
AND CaseHeader.statusCode <> :closedCaseStatus 
GROUP BY CaseRelationship.caseID
) OverpaymentCasesCount 
ON (OverpaymentCasesCount.caseID = ProductDelivery.caseID) 
	
LEFT OUTER JOIN(
SELECT 
COUNT (CaseDeductionItem.caseID) AS activeDeductionsCount,
SUM (CaseDeductionItem.amount) AS totalActiveDeductions, 
CaseDeductionItem.caseID 
FROM CaseDeductionItem 
WHERE CaseDeductionItem.statusCode = :caseDeductionItemStatusCode 
AND 
(
	CaseDeductionItem.endDate IS NULL OR 
	CaseDeductionItem.endDate > :currentDate
) 
GROUP BY CaseDeductionItem.caseID
) ActiveDeductionsCount
ON (ActiveDeductionsCount.caseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
MAX (CaseReview.scheduledStartDate) AS latestScheduledDate, 
MAX (CaseReview.expectCompleteDate) AS reviewDate, 
CaseReview.caseID 
FROM CaseReview 
GROUP BY CaseReview.caseID
) CaseReviewDate 
ON (CaseReviewDate.caseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
CaseDecision.decisionDate,
CaseDecision.caseDecisionID,
CaseDecision.caseID 
FROM CaseDecision 
WHERE CaseDecision.caseID = :caseID 
AND CaseDecision.statusCode = :caseDecisionStatusCurrent 
AND CaseDecision.initReasonCode = :caseDecisionInitReason 
AND CaseDecision.decisionDate = 
( 
SELECT 
MAX (decisionDate) 
FROM CaseDecision 
WHERE caseID = :caseID 
AND CaseDecision.statusCode = :caseDecisionStatusCurrent 
AND CaseDecision.initReasonCode = :caseDecisionInitReason) 
) LatestCaseDecision 
ON (LatestCaseDecision.caseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
COUNT (BenefitUnderpaymentEvidence.relatedCaseID) AS underpaymentsCount,
BenefitUnderpaymentEvidence.relatedCaseID 
FROM BenefitUnderpaymentEvidence, 
CaseHeader 
WHERE BenefitUnderpaymentEvidence.relatedCaseID = :caseID 
AND CaseHeader.caseID = BenefitUnderpaymentEvidence.relatedCaseID 
AND CaseHeader.statusCode <> :closedCaseStatus 
GROUP BY BenefitUnderpaymentEvidence.relatedCaseID
) UnderpaymentCount 
ON (UnderpaymentCount.relatedCaseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
COUNT (CaseRelationship.caseID) AS underpaymentCasesCount, 
CaseRelationship.caseID 
FROM CaseRelationship, 
CaseHeader, 
BenefitUnderpaymentEvidence 
WHERE CaseRelationship.relatedCaseID = :caseID 
AND CaseRelationship.caseID = BenefitUnderpaymentEvidence.relatedCaseID 
AND CaseHeader.caseID = BenefitUnderpaymentEvidence.relatedCaseID 
AND CaseHeader.statusCode <> :closedCaseStatus 
GROUP BY CaseRelationship.caseID 
) UnderpaymentCasesCount 
ON (UnderpaymentCasesCount.caseID = ProductDelivery.caseID) 

LEFT OUTER JOIN(
SELECT 
COUNT (IssueDelivery.relatedCaseID) AS issuesCount,
IssueDelivery.relatedCaseID 
FROM IssueDelivery, 
CaseHeader 
WHERE IssueDelivery.relatedCaseID = :caseID 
AND CaseHeader.caseID = IssueDelivery.caseID 
AND CaseHeader.statusCode <> :cancelledCaseStatus 
AND CaseHeader.statusCode <> :closedCaseStatus 
GROUP BY IssueDelivery.relatedCaseID
) IssueCount 
ON (IssueCount.relatedCaseID = ProductDelivery.caseID), 
Product 

WHERE 
CaseHeader.caseID = :caseID AND 
CaseHeader.caseID = ProductDelivery.caseID AND 
ProductDelivery.productID = Product.productID AND 
ConcernRole.concernRoleID = (
SELECT 
ConcernRole.concernRoleID 
FROM ConcernRole, 
CaseParticipantRole 
WHERE CaseParticipantRole.typeCode = :primaryClientRoleType 
AND CaseParticipantRole.caseID = CaseHeader.caseID 
AND ConcernRole.concernRoleID = CaseParticipantRole.participantRoleID
) AND 
OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID