SELECT
  CaseHeader.caseID,
  CaseHeader.caseReference,
  CaseHeader.statusCode,
  RelatedCaseHeader.caseReference,
  BenefitUnderpaymentEvidence.underpaymentDate,
  BenefitUnderpaymentEvidence.amount,
  BenefitUnderpaymentEvidence.fromDate,
  BenefitUnderpaymentEvidence.toDate,
  BenefitUnderpaymentEvidence.relatedCaseID,
  RelatedProduct.name,
  UnderPmtProduct.name,
  CaseHeader.concernRoleID,
  ConcernRole.concernRoleName,
  ConcernRole.primaryAlternateID,
  ConcernRole.primaryPhoneNumberID,
  EmailAddress.emailAddress,
  Address.addressData,
  OrgObjectLink.orgObjectLinkID,
  FinancialInstruction.finInstructionID,
  FinancialInstruction.amount,
  FinancialInstruction.statusCode,
  FinancialInstruction.typeCode,
  PaymentInstrument.invalidatedInd,
  InstructionLineItem.dueDate,
  FinancialInstruction.lastWritten
INTO
  :caseID,
  :caseReference,
  :caseStatus,
  :relatedCaseReference,
  :underpaymentDate,
  :underpaymentAmount,
  :fromDate,
  :toDate,
  :relatedCaseID,
  :relatedProductName,
  :productName,
  :concernRoleID,
  :concernRoleName,
  :clientPrimaryAlternateID,
  :clientPhoneNumberID,
  :clientEmailAddress,
  :clientAddressData,
  :orgObjectLinkID,
  :paymentFinInstructionID,
  :paymentAmount,
  :paymentStatusCode,
  :paymentInstructionType,
  :paymentInvalidatedInd,
  :paymentDueDate,
  :lastWrittenOpt
FROM
  CaseRelationship,
  CaseHeader RelatedCaseHeader,
  BenefitUnderpaymentEvidence,
  OrgObjectLink,
  ProductDelivery UnderPmtPD,
  Product UnderPmtProduct,
  ProductDelivery RelatedPD,
  Product RelatedProduct,
  ConcernRole
  LEFT OUTER JOIN EmailAddress 
    ON ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID
  LEFT OUTER JOIN Address 
    ON ConcernRole.primaryAddressID = Address.addressID,
  CaseHeader
  LEFT OUTER JOIN InstructionLineItem
    ON (InstructionLineItem.caseID = CaseHeader.caseID AND InstructionLineItem.instructionLineItemType = :instructionLineItemType)
  LEFT OUTER JOIN FinancialInstruction 
    ON FinancialInstruction.finInstructionID = InstructionLineItem.finInstructionID
  LEFT OUTER JOIN PaymentInstruction
    ON PaymentInstruction.finInstructionID = FinancialInstruction.finInstructionID
  LEFT OUTER JOIN PaymentInstrument
    ON PaymentInstruction.pmtInstrumentID = PaymentInstrument.pmtInstrumentID WHERE CaseRelationship.caseID = :caseID
  AND BenefitUnderpaymentEvidence.benefitUnderpaymentEvidenceID = :benefitUnderpaymentEvidenceID
  AND CaseHeader.caseID = CaseRelationship.caseID
  AND BenefitUnderpaymentEvidence.relatedCaseID = RelatedCaseHeader.caseID
  AND BenefitUnderpaymentEvidence.relatedCaseID = CaseRelationship.relatedCaseID
  AND CaseHeader.concernRoleID = ConcernRole. concernRoleID
  AND OrgObjectLink.orgObjectLinkID = CaseHeader.ownerOrgObjectLinkID
  AND RelatedPD.caseID = RelatedCaseHeader.caseID
  AND RelatedProduct.productID = RelatedPD.productID
  AND UnderPmtPD.caseID = CaseHeader.caseID
  AND UnderPmtProduct.productID = UnderPmtPD.productID
ORDER BY FINANCIALINSTRUCTION.LASTWRITTEN DESC