Operation Details
Description: Search to retrieve a list of instruction line items by categories and status.
Sterotype: nsmulti
SQL 
SELECT
  ili1.instructLineItemID,
  ili1.amount,
  ili1.unprocessedAmount,
  ili1.instructLineItemCategory,
  ili1.instructionLineItemType,
  ili1.financialCompID,
  ili1.coverPeriodFrom,
  ili1.coverPeriodTo,
  ili1.effectiveDate,
  ili1.creationDate,
  ili1.statusCode,
  ili1.caseID,
  ili1.finInstructionID,
  ili1.concernRoleID,
  ili1.caseNomineeID,
  ili1.primaryClientID,
  ili1.deliveryMethodType,
  ili1.creditDebitType,
  ili1.currencyExchangeID,
  ili1.currencyTypeCode,
  ili1.adjustmentInd,
  ili1.adjustmentFrequency,
  ili1.nextAdjustmentDate,
  ili1.instrumentGenInd,
  ili1.fundID,
  ili1.maximumAmount,
  ili1.versionNo,
  ili1.dueDate,
  ili1.inRespectOfID
INTO
  :instructLineItemID,
  :amount,
  :unprocessedAmount,
  :instructLineItemCategory,
  :instructionLineItemType,
  :financialCompID,
  :coverPeriodFrom,
  :coverPeriodTo,
  :effectiveDate,
  :creationDate,
  :statusCode,
  :caseID,
  :finInstructionID,
  :concernRoleID,
  :caseNomineeID,
  :primaryClientID,
  :deliveryMethodType,
  :creditDebitType,
  :currencyExchangeID,
  :currencyTypeCode,
  :adjustmentInd,
  :adjustmentFrequency,
  :nextAdjustmentDate,
  :instrumentGenInd,
  :fundID,
  :maximumAmount,
  :versionNo,
  :dueDate,
  :inRespectOfID 
FROM
  InstructionLineItem ili1 
WHERE ili1.statusCode = :statusCode 
  AND ili1.instructLineItemCategory = :instructLineItemCategory1 

UNION ALL
  
SELECT
  ili1.instructLineItemID,
  ili1.amount,
  ili1.unprocessedAmount,
  ili1.instructLineItemCategory,
  ili1.instructionLineItemType,
  ili1.financialCompID,
  ili1.coverPeriodFrom,
  ili1.coverPeriodTo,
  ili1.effectiveDate,
  ili1.creationDate,
  ili1.statusCode,
  ili1.caseID,
  ili1.finInstructionID,
  ili1.concernRoleID,
  ili1.caseNomineeID,
  ili1.primaryClientID,
  ili1.deliveryMethodType,
  ili1.creditDebitType,
  ili1.currencyExchangeID,
  ili1.currencyTypeCode,
  ili1.adjustmentInd,
  ili1.adjustmentFrequency,
  ili1.nextAdjustmentDate,
  ili1.instrumentGenInd,
  ili1.fundID,
  ili1.maximumAmount,
  ili1.versionNo,
  ili1.dueDate,
  ili1.inRespectOfID 
FROM
  InstructionLineItem ili1 
WHERE ili1.statusCode = :statusCode 
  AND ili1.instructLineItemCategory = :instructLineItemCategory2 
  AND EXISTS 
  ( 
  SELECT
    ili2.concernRoleID 
  FROM
    InstructionLineItem ili2 
  WHERE ili2.concernRoleID = ili1.concernRoleID 
    AND ili2.instructLineItemCategory = :instructLineItemCategory1 
  ) 
ORDER BY concernRoleID,
  currencyTypeCode,
  deliveryMethodType,
  caseID