SELECT
CaseDeductionItem.concernRoleID,
ConcernRole.concernRoleName,
CaseDeductionItem.startDate,
CaseDeductionItem.caseID,
CaseHeader.caseReference,
CaseDeductionItem.amount,
Product.name,
FinancialComponent.frequency
INTO
:concernRoleID,
:concernRoleName,
:startDate,
:relatedCaseID,
:relatedCaseReference,
:deductionAmount,
:productName,
:frequency
FROM
CaseDeductionItem,
CaseHeader,
ProductDelivery,
Product,
FinancialComponent,
CaseDeductionItemFCLink,
ConcernRole
WHERE
CaseDeductionItem.relatedCaseID = :caseID AND
CaseDeductionItem.statusCode = :statusCode AND
CaseHeader.caseID = CaseDeductionItem.caseID AND
ProductDelivery.caseID = CaseHeader.caseID AND
Product.productID = ProductDelivery.productID AND
CaseDeductionItem.caseDeductionItemID = CaseDeductionItemFCLink.caseDeductionItemID AND
CaseDeductionItemFCLink.financialComponentID = FinancialComponent.financialCompID AND
ConcernRole.concernRoleID = CaseDeductionItem.concernRoleID AND
(
CaseDeductionItem.endDate IS NULL
OR :currentDate IS NULL
OR CaseDeductionItem.endDate >= :currentDate
)
|