Operation Details
Description: To retrieve records from the InstructionLineItem, CaseNominee database tables using CaseID, Category and Status Codes.
Sterotype: nsmulti
SQL 
SELECT
  InstructionLineItem.instructLineItemID,
  InstructionLineItem.amount,
  InstructionLineItem.unprocessedAmount,
  InstructionLineItem.instructLineItemCategory,
  InstructionLineItem.instructionLineItemType,
  InstructionLineItem.financialCompID,
  InstructionLineItem.coverPeriodFrom,
  InstructionLineItem.coverPeriodTo,
  InstructionLineItem.effectiveDate,
  InstructionLineItem.creationDate,
  InstructionLineItem.statusCode,
  InstructionLineItem.caseID,
  InstructionLineItem.finInstructionID,
  InstructionLineItem.concernRoleID,
  InstructionLineItem.caseNomineeID,
  InstructionLineItem.primaryClientID,
  InstructionLineItem.deliveryMethodType,
  InstructionLineItem.creditDebitType,
  InstructionLineItem.currencyExchangeID,
  InstructionLineItem.currencyTypeCode,
  InstructionLineItem.adjustmentInd,
  InstructionLineItem.adjustmentFrequency,
  InstructionLineItem.nextAdjustmentDate,
  InstructionLineItem.instrumentGenInd,
  InstructionLineItem.fundID,
  InstructionLineItem.maximumAmount,
  InstructionLineItem.versionNo,
  InstructionLineItem.dueDate,
  InstructionLineItem.inRespectOfID,
  CaseParticipantRole.participantRoleID
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,
  :nomineeParticipantRoleID 
FROM
  InstructionLineItem,
  CaseNominee,
  CaseParticipantRole 
WHERE InstructionLineItem.caseID = :caseID 
  AND InstructionLineItem.instructLineItemCategory = :instructLineItemCategory 
  AND InstructionLineItem.statusCode = :statusCode 
  AND InstructionLineItem.caseNomineeID = CaseNominee.caseNomineeID 
  AND CaseParticipantRole.caseParticipantRoleID = CaseNominee.caseParticipantRoleID 
ORDER BY InstructionLineItem.concernRoleID,
  CaseParticipantRole.participantRoleID,
  InstructionLineItem.currencyTypeCode,
  InstructionLineItem.deliveryMethodType