Operation Details
Description: Search to retrieve a list of instruction line items by categories and status, which have a blank case ID. This will find Tax instruction line items.
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.caseID IS NULL 
  AND 
  (
    
    (
      ili1.instructLineItemCategory = :instructLineItemCategory1 
    ) 
    OR 
    (
      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