VIEW: APPS.AR_AUTOMATIC_RECEIPTS_FORMAT_V

Source

View Text - Preformatted

SELECT cr.receipt_number, cr.currency_code, cr.amount, cr.receipt_date, ps.due_date, rm.printed_name, cr.exchange_rate, substrb(party.party_name,1,50), cust_acct.account_number, su.location, NULL customer_bank_name, NULL customer_bank_branch_name, NULL customer_bank_branch_number, NULL customer_bank_account_name, decode(iby.instrument_type,'BANKACCOUNT',iby.account_number, 'CREDITCARD', iby.card_number , NULL) customer_bank_account_number, bb.bank_name remit_bank_name, bb.bank_branch_name remit_bank_branch_name, bb.branch_number remit_bank_branch_number, cba.bank_account_name remit_bank_account_name, cba.bank_account_num remit_bank_account_number, b.name, b.batch_date, b.media_reference, cr.cash_receipt_id, cust_acct.cust_account_id, NULL customer_bank_account_id, cr.remit_bank_acct_use_id remit_bank_account_id, b.batch_id , cr.payment_trxn_extension_id, rm.payment_channel_code FROM ar_cash_receipts cr, ar_receipt_methods rm, ar_receipt_classes rc, hz_cust_accounts cust_acct, hz_parties party, hz_cust_site_uses su, iby_trxn_extensions_v iby, ce_bank_accounts cba, ce_bank_acct_uses ba, ce_bank_branches_v bb, ar_cash_receipt_history crh, ar_batches b, ar_payment_schedules ps WHERE cr.receipt_method_id = rm.receipt_method_id AND rm.receipt_class_id = rc.receipt_class_id AND rc.creation_method_code = 'AUTOMATIC' AND cr.pay_from_customer = cust_acct.cust_account_id AND cust_acct.party_id = party.party_id AND cr.customer_site_use_id = su.site_use_id(+) AND iby.trxn_extension_id(+)= cr.payment_trxn_extension_id AND cba.bank_account_id = ba.bank_account_id AND cr.remit_bank_acct_use_id = ba.bank_acct_use_id AND bb.branch_party_id = cba.bank_branch_id AND cr.cash_receipt_id = crh.cash_receipt_id AND crh.prv_stat_cash_receipt_hist_id is null AND crh.batch_id = b.batch_id AND cr.cash_receipt_id = ps.cash_receipt_id

View Text - HTML Formatted

SELECT CR.RECEIPT_NUMBER
, CR.CURRENCY_CODE
, CR.AMOUNT
, CR.RECEIPT_DATE
, PS.DUE_DATE
, RM.PRINTED_NAME
, CR.EXCHANGE_RATE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, SU.LOCATION
, NULL CUSTOMER_BANK_NAME
, NULL CUSTOMER_BANK_BRANCH_NAME
, NULL CUSTOMER_BANK_BRANCH_NUMBER
, NULL CUSTOMER_BANK_ACCOUNT_NAME
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL) CUSTOMER_BANK_ACCOUNT_NUMBER
, BB.BANK_NAME REMIT_BANK_NAME
, BB.BANK_BRANCH_NAME REMIT_BANK_BRANCH_NAME
, BB.BRANCH_NUMBER REMIT_BANK_BRANCH_NUMBER
, CBA.BANK_ACCOUNT_NAME REMIT_BANK_ACCOUNT_NAME
, CBA.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT_NUMBER
, B.NAME
, B.BATCH_DATE
, B.MEDIA_REFERENCE
, CR.CASH_RECEIPT_ID
, CUST_ACCT.CUST_ACCOUNT_ID
, NULL CUSTOMER_BANK_ACCOUNT_ID
, CR.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCOUNT_ID
, B.BATCH_ID
, CR.PAYMENT_TRXN_EXTENSION_ID
, RM.PAYMENT_CHANNEL_CODE
FROM AR_CASH_RECEIPTS CR
, AR_RECEIPT_METHODS RM
, AR_RECEIPT_CLASSES RC
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SU
, IBY_TRXN_EXTENSIONS_V IBY
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, CE_BANK_BRANCHES_V BB
, AR_CASH_RECEIPT_HISTORY CRH
, AR_BATCHES B
, AR_PAYMENT_SCHEDULES PS
WHERE CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND RC.CREATION_METHOD_CODE = 'AUTOMATIC'
AND CR.PAY_FROM_CUSTOMER = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CR.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND IBY.TRXN_EXTENSION_ID(+)= CR.PAYMENT_TRXN_EXTENSION_ID
AND CBA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND CR.REMIT_BANK_ACCT_USE_ID = BA.BANK_ACCT_USE_ID
AND BB.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.PRV_STAT_CASH_RECEIPT_HIST_ID IS NULL
AND CRH.BATCH_ID = B.BATCH_ID
AND CR.CASH_RECEIPT_ID = PS.CASH_RECEIPT_ID