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
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
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
|