Operation Details
Description: Search of the PaymentReceivedInstrument table based on the concernRoleID and/or a combination of all other input fields.
Sterotype: nsmulti
SQL 
SELECT
  PaymentReceivedInstrument.pmtRecInstrumentID,
  PaymentReceivedInstrument.amount,
  PaymentReceivedInstrument.receivedDate,
  PaymentReceivedInstrument.creationDate,
  PaymentReceivedInstrument.effectiveDate,
  PaymentReceivedInstrument.receiptMethodCode,
  PaymentReceivedInstrument.concernRoleID,
  PaymentReceivedInstrument.issuerName,
  PaymentReceivedInstrument.addressID,
  PaymentReceivedInstrument.currencyTypeCode,
  PaymentReceivedInstrument.currencyExchangeID,
  PaymentReceivedInstrument.depositSlipNumber,
  PaymentReceivedInstrument.receiptNumber,
  PaymentReceivedInstrument.liabilityExternalRefNo,
  PaymentReceivedInstruction.finInstructionID
INTO
  :pmtRecInstrumentID,
  :amount,
  :receivedDate,
  :creationDate,
  :effectiveDate,
  :receiptMethodCode,
  :concernRoleID,
  :issuerName,
  :addressID,
  :currencyTypeCode,
  :currencyExchangeID,
  :depositSlipNumber,
  :receiptNumber,
  :liabilityExternalRefNo,
  :finInstructionID 
FROM
  PaymentReceivedInstrument,
  PaymentReceivedInstruction 
WHERE PaymentReceivedInstrument.pmtRecInstrumentID = PaymentReceivedInstruction.pmtRecInstrumentID 
  AND PaymentReceivedInstrument.concernRoleID = :concernRoleID 
  AND 
  (
    
    (
      PaymentReceivedInstrument.amount = :amount 
      OR :amount = 0 
    ) 
    AND 
    (
      PaymentReceivedInstrument.receiptMethodCode = :receiptMethodCode 
      OR :receiptMethodCode IS NULL 
    ) 
    AND 
    (
      PaymentReceivedInstrument.effectiveDate = :effectiveDate 
      OR :effectiveDate IS NULL 
    ) 
    AND 
    (
      PaymentReceivedInstrument.receivedDate >= :receivedDateFrom 
      OR :receivedDateFrom IS NULL 
    ) 
    AND 
    (
      PaymentReceivedInstrument.receivedDate <= :receivedDateTo 
      OR :receivedDateTo IS NULL 
    ) 
  )