Operation Details
Description: To retrieve a subset of InstructionLineItems details for a specified Integrated CaseID, Status & Date Range
Sterotype: nsmulti
SQL 
SELECT
  instructLineItemID,
  amount,
  unprocessedAmount,
  instructLineItemCategory,
  instructionLineItemType,
  InstructionLineItem.effectiveDate,
  finInstructionID,
  InstructionLineItem.concernRoleID,
  creditDebitType,
  InstructionLineItem.statusCode,
  InstructionLineItem.caseID
INTO
  :instructLineItemID,
  :amount,
  :unprocessedAmount,
  :instructLineItemCategory,
  :instructionLineItemType,
  :effectiveDate,
  :finInstructionID,
  :concernRoleID,
  :creditDebitType,
  :statusCode,
  :caseID 
FROM
  InstructionLineItem,
  CaseHeader 
WHERE 
  (
    CaseHeader.integratedCaseID = :caseID 
    AND InstructionLineItem.caseID = CaseHeader.caseID 
  ) 
  AND 
  (
    InstructionLineItem.statusCode = :statusCode 
    OR :statusCode IS NULL 
  ) 
  AND InstructionLineItem.effectiveDate >= :effectiveDateFrom 
  AND InstructionLineItem.effectiveDate <= :effectiveDateTo