SELECT PARTY_NAME PARTY_NAME,
LOC.ADDRESS1 ADDRESS1,
LOC.ADDRESS2 ADDRESS2,
LOC.ADDRESS3 ADDRESS3,
LOC.ADDRESS4 ADDRESS4,
LOC.COUNTRY COUNTRY,
MANDATE.AUTHORIZATION_REFERENCE_NUMBER MANDATE_NUMBER,
BANK.BANK_NAME BANK_NAME,
BANK.BANK_ACCOUNT_NUM_ELECTRONIC BANK_ACCT_NUMBER,
RCT.CASH_RECEIPT_ID RCT_ID,
RCT.CURRENCY_CODE CURRENCY ,
RCT.RECEIPT_NUMBER RECEIPT_NUMBER,
TO_CHAR((PS.DUE_DATE),'DD Month, YYYY') DUE_DATE
FROM AR_CASH_RECEIPT_HISTORY CRH,
AR_CASH_RECEIPTS RCT,
AR_PAYMENT_SCHEDULES PS,
AR_RECEIPT_METHODS RCT_MTD,
AR_RECEIPT_CLASSES RCT_CLASS,
IBY_EXTERNAL_BANK_ACCOUNTS_V BANK,
IBY_DEBIT_AUTHORIZATIONS MANDATE,
IBY_FNDCPT_TX_EXTENSIONS TRX_EXT,
IBY_PMT_INSTR_USES_ALL INSTR_USE,
HZ_CUST_SITE_USES SITE_USE,
HZ_CUST_ACCT_SITES CUST_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_PARTIES PARTY,
HZ_LOCATIONS LOC
WHERE CRH.BATCH_ID = SUBSTR(:P_BATCH_ID, 12)
AND CRH.PRV_STAT_CASH_RECEIPT_HIST_ID IS NULL
AND CRH.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID
AND PS.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID
AND RCT.RECEIPT_METHOD_ID = RCT_MTD.RECEIPT_METHOD_ID
AND RCT_MTD.RECEIPT_CLASS_ID =RCT_CLASS.RECEIPT_CLASS_ID
AND RCT_CLASS.CREATION_METHOD_CODE = 'AUTOMATIC'
AND RCT.PAYMENT_TRXN_EXTENSION_ID = TRX_EXT.TRXN_EXTENSION_ID
AND TRX_EXT.INSTR_ASSIGNMENT_ID = INSTR_USE.INSTRUMENT_PAYMENT_USE_ID
AND INSTR_USE.INSTRUMENT_ID = BANK.EXT_BANK_ACCOUNT_ID
AND BANK.EXT_BANK_ACCOUNT_ID = MANDATE.EXTERNAL_BANK_ACCOUNT_ID
AND NVL(MANDATE.AUTH_CANCEL_DATE, RCT.RECEIPT_DATE) >= RCT.RECEIPT_DATE
AND NVL(MANDATE.DEBIT_AUTH_END, RCT.RECEIPT_DATE) >= RCT.RECEIPT_DATE
AND MANDATE.CURR_REC_INDI = 'Y'
AND MANDATE.PRE_NOTIFICATION_REQUIRED_FLAG = 'Y'
AND RCT.CUSTOMER_SITE_USE_ID = SITE_USE.SITE_USE_ID
AND SITE_USE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID
AND CUST_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID =LOC.LOCATION_ID
AND PARTY_SITE.PARTY_ID = PARTY.PARTY_ID
AND INSTR_USE.payment_flow = 'FUNDS_CAPTURE'
AND INSTR_USE.instrument_type = 'BANKACCOUNT' |
