VIEW: APPS.ARBR_FUNDS_CAPTURE_ORDERS_V

Source

View Text - Preformatted

SELECT 'ARBR_'||TRX.REMITTANCE_BATCH_ID, bat.batch_date, NULL, PS.DUE_DATE, CTT.NAME, PS.INVOICE_CURRENCY_CODE, TRX.BR_AMOUNT, TRX.PAYMENT_TRXN_EXTENSION_ID, bau.bank_account_id, PARTY.PARTY_ID, CUST_ACCT.cust_account_id, TRX.DRAWEE_SITE_USE_ID, TRX.ORG_Id, 'OPERATING_UNIT', TRX.LEGAL_ENTITY_ID, party_site.location_id , TRX.CUSTOMER_REFERENCE, CTT.DRAWEE_ISSUED_FLAG, CTT.SIGNED_FLAG, decode (trh.status,'PROTESTED','Y','N'), TRX.ATTRIBUTE_CATEGORY, TRX.ATTRIBUTE1, TRX.ATTRIBUTE2, TRX.ATTRIBUTE3, TRX.ATTRIBUTE4, TRX.ATTRIBUTE5, TRX.ATTRIBUTE6, TRX.ATTRIBUTE7, TRX.ATTRIBUTE8, TRX.ATTRIBUTE9, TRX.ATTRIBUTE10, TRX.ATTRIBUTE11, TRX.ATTRIBUTE12, TRX.ATTRIBUTE13, TRX.ATTRIBUTE14, TRX.ATTRIBUTE15, TRX.CREATED_BY, TRX.CREATION_DATE, TRX.LAST_UPDATED_BY, TRX.LAST_UPDATE_DATE, TRX.LAST_UPDATE_LOGIN, 1, BAT.RECEIPT_METHOD_ID, rc.remit_method_code FROM AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX TRX, ar_transaction_history trh, ar_batches bat, ar_receipt_classes rc, ar_receipt_methods rm, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_SITE_USES SU, ce_bank_acct_uses_ou_v bau WHERE ps.customer_trx_id = TRX.customer_trx_id AND TRX.BR_AMOUNT is Not NULL and nvl(trh.current_record_flag,'Y') = 'Y' and ps.customer_trx_id = trh.customer_trx_id and bat.batch_id = trx.remittance_batch_id AND bat.receipt_method_id = rm.receipt_method_id and rm.receipt_class_id = rc.receipt_class_id AND TRX.drawee_id = CUST_ACCT.cust_account_id AND CUST_ACCT.party_id = PARTY.party_id AND PS.cust_trx_type_id = ctt.cust_trx_type_id and ctt.type = 'BR' and TRX.drawee_site_use_id = su.site_use_id and su.cust_acct_site_id = acct_site.cust_acct_site_id and acct_site.party_site_id = party_site.party_site_id and loc.location_id = party_site.location_id and cust_acct.cust_account_id=acct_site.cust_account_id and bau.bank_acct_use_id = trx.remit_bank_acct_use_id

View Text - HTML Formatted

SELECT 'ARBR_'||TRX.REMITTANCE_BATCH_ID
, BAT.BATCH_DATE
, NULL
, PS.DUE_DATE
, CTT.NAME
, PS.INVOICE_CURRENCY_CODE
, TRX.BR_AMOUNT
, TRX.PAYMENT_TRXN_EXTENSION_ID
, BAU.BANK_ACCOUNT_ID
, PARTY.PARTY_ID
, CUST_ACCT.CUST_ACCOUNT_ID
, TRX.DRAWEE_SITE_USE_ID
, TRX.ORG_ID
, 'OPERATING_UNIT'
, TRX.LEGAL_ENTITY_ID
, PARTY_SITE.LOCATION_ID
, TRX.CUSTOMER_REFERENCE
, CTT.DRAWEE_ISSUED_FLAG
, CTT.SIGNED_FLAG
, DECODE (TRH.STATUS
, 'PROTESTED'
, 'Y'
, 'N')
, TRX.ATTRIBUTE_CATEGORY
, TRX.ATTRIBUTE1
, TRX.ATTRIBUTE2
, TRX.ATTRIBUTE3
, TRX.ATTRIBUTE4
, TRX.ATTRIBUTE5
, TRX.ATTRIBUTE6
, TRX.ATTRIBUTE7
, TRX.ATTRIBUTE8
, TRX.ATTRIBUTE9
, TRX.ATTRIBUTE10
, TRX.ATTRIBUTE11
, TRX.ATTRIBUTE12
, TRX.ATTRIBUTE13
, TRX.ATTRIBUTE14
, TRX.ATTRIBUTE15
, TRX.CREATED_BY
, TRX.CREATION_DATE
, TRX.LAST_UPDATED_BY
, TRX.LAST_UPDATE_DATE
, TRX.LAST_UPDATE_LOGIN
, 1
, BAT.RECEIPT_METHOD_ID
, RC.REMIT_METHOD_CODE
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX TRX
, AR_TRANSACTION_HISTORY TRH
, AR_BATCHES BAT
, AR_RECEIPT_CLASSES RC
, AR_RECEIPT_METHODS RM
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SU
, CE_BANK_ACCT_USES_OU_V BAU
WHERE PS.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.BR_AMOUNT IS NOT NULL
AND NVL(TRH.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND PS.CUSTOMER_TRX_ID = TRH.CUSTOMER_TRX_ID
AND BAT.BATCH_ID = TRX.REMITTANCE_BATCH_ID
AND BAT.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND TRX.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CTT.TYPE = 'BR'
AND TRX.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_SITE.CUST_ACCOUNT_ID
AND BAU.BANK_ACCT_USE_ID = TRX.REMIT_BANK_ACCT_USE_ID