Operation Details
Description: To retrieve any case deduction items using the case ID, record status, business status and case end date. Only case deduction items with an end date after the last paid to date of the case will be considered.
Sterotype: nsmulti
SQL 
SELECT
  caseDeductionItemID,
  concernRoleID,
  createdDate,
  startDate,
  endDate,
  statusCode,
  comments,
  relatedCaseID,
  caseID,
  rate,
  amount,
  deductionType,
  relatedObjectiveID,
  rulesObjectiveID,
  caseNomineeID,
  versionNo
INTO
  :caseDeductionItemID,
  :concernRoleID,
  :createdDate,
  :startDate,
  :endDate,
  :statusCode,
  :comments,
  :relatedCaseID,
  :caseID,
  :rate,
  :amount,
  :deductionType,
  :relatedObjectiveID,
  :rulesObjectiveID,
  :caseNomineeID,
  :versionNo 
FROM
  CaseDeductionItem 
WHERE caseID = :caseID 
  AND businessStatus = :businessStatus 
  AND statusCode = :status 
  AND 
  (
    startDate <= :caseEndDate 
    OR :caseEndDate is null
  )
  AND 
  (
    endDate >= :caseLastPaidDate 
    OR endDate is null 
  )