SELECT DISTINCT
  AppealedPDs.numPDCases,
  AppealedIssues.numIssueCases,
  AppealedAppeals.numAppealCases,
  AppealedICs.numIntegratedCases

INTO
:productDeliveryCount,
:issueCount,
:appealsCount,
:integratedCaseCountOpt

FROM
AppealRelationship

LEFT OUTER JOIN (
SELECT 
COUNT(*) AS numPDCases,
AppealRelationship.appealCaseID

FROM
AppealRelationship, 
CaseHeader

WHERE 
CaseHeader.caseTypeCode = :productDeliveryCaseTypeCode AND 
CaseHeader.caseID = AppealRelationship.caseID AND
AppealRelationship.appealCaseID = :caseID 
GROUP BY AppealRelationship.appealCaseID

) AppealedPDs
ON (AppealedPDs.appealCaseID = AppealRelationship.appealCaseID)

LEFT OUTER JOIN (
SELECT 
COUNT(*) AS numIssueCases,
AppealRelationship.appealCaseID

FROM
AppealRelationship, 
CaseHeader

WHERE 
CaseHeader.caseTypeCode = :issueCaseTypeCode AND 
CaseHeader.caseID = AppealRelationship.caseID AND
AppealRelationship.appealCaseID = :caseID

GROUP BY AppealRelationship.appealCaseID
) AppealedIssues
ON (AppealedIssues.appealCaseID = AppealRelationship.appealCaseID)

LEFT OUTER JOIN (
SELECT 
COUNT(*) AS numAppealCases,
AppealRelationship.appealCaseID

FROM
AppealRelationship, 
CaseHeader

WHERE 
CaseHeader.caseTypeCode = :appealCaseTypeCode AND 
CaseHeader.caseID = AppealRelationship.caseID AND
AppealRelationship.appealCaseID = :caseID

GROUP BY AppealRelationship.appealCaseID
) AppealedAppeals
ON (AppealedAppeals.appealCaseID = AppealRelationship.appealCaseID)

LEFT OUTER JOIN (
SELECT 
COUNT(*) AS numIntegratedCases,
AppealRelationship.appealCaseID

FROM
AppealRelationship, 
CaseHeader

WHERE 
CaseHeader.caseTypeCode = :integratedCaseTypeCodeOpt AND 
CaseHeader.caseID = AppealRelationship.caseID AND
AppealRelationship.appealCaseID = :caseID 
GROUP BY AppealRelationship.appealCaseID

) AppealedICs
ON (AppealedICs.appealCaseID = AppealRelationship.appealCaseID)

WHERE 
AppealRelationship.appealCaseID = :caseID