Operation Details
Description: Search of Payment Instrument by effectiveDateFrom, effectiveDateTo, amount, deliveryMethodType and statusCode
Sterotype: nsmulti
SQL 
SELECT
  PaymentInstrument.pmtInstrumentID,
  PaymentInstrument.amount,
  PaymentInstrument.effectiveDate,
  PaymentInstrument.creationDate,
  PaymentInstrument.deliveryMethodType,
  PaymentInstrument.reconcilStatusCode,
  PaymentInstrument.concernRoleID,
  PaymentInstrument.concernRoleName,
  PaymentInstrument.caseNomineeID,
  PaymentInstrument.nomineeName,
  PaymentInstrument.nomineeAlternateID,
  PaymentInstrument.addressID,
  PaymentInstrument.currencyTypeCode,
  PaymentInstrument.currencyExchangeID,
  PaymentInstrument.bankAccountID,
  PaymentInstrument.referenceNumber,
  PaymentInstrument.referenceText,
  PaymentInstrument.pslipInstructionID,
  PaymentInstruction.finInstructionID
INTO
  :pmtInstrumentID,
  :amount,
  :effectiveDate,
  :creationDate,
  :deliveryMethodType,
  :reconcilStatusCode,
  :concernRoleID,
  :concernRoleName,
  :caseNomineeID,
  :nomineeName,
  :nomineeAlternateID,
  :addressID,
  :currencyTypeCode,
  :currencyExchangeID,
  :bankAccountID,
  :referenceNumber,
  :referenceText,
  :pslipInstructionID,
  :finInstructionID 
FROM
  PaymentInstrument,
  PaymentInstruction 
WHERE PaymentInstrument.pmtInstrumentID = PaymentInstruction.pmtInstrumentID 
  AND PaymentInstrument.amount = :amount 
  AND PaymentInstrument.deliveryMethodType = :deliveryMethodType 
  AND PaymentInstrument.effectiveDate >= :effectiveDateFrom 
  AND PaymentInstrument.effectiveDate <= :effectiveDateTo 
  AND 
  (
    PaymentInstrument.reconcilStatusCode = :statusCode 
    OR :statusCode IS NULL 
  )