VIEW: APPS.AR_REMITTED_RECEIPTS_FORMAT_V

Source

View Text - Preformatted

SELECT cr.receipt_number, cr.currency_code, cr.amount, cr.factor_discount_amount, cr.receipt_date, cr.deposit_date, nvl(ps.due_date,cr.deposit_date), rm.printed_name, cr.exchange_rate, lk1.meaning, 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, lk2.meaning, b.media_reference, DECODE(chbb.bank_institution_type,'CLEARING HOUSE',chbb.bank_name, null), DECODE(chbb.bank_institution_type,'CLEARING HOUSE',chbb.bank_branch_name), DECODE(chbb.bank_institution_type,'CLEARING HOUSE',chbb.bank_number, null), 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.customer_receipt_reference, cr.payment_trxn_extension_id, rm.payment_channel_code FROM ar_cash_receipts cr, ar_receipt_methods rm, ar_lookups lk1, 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_cash_receipt_history crhprv, ar_batches b, ar_lookups lk2, ar_payment_schedules ps, ce_bank_branches_v chbb WHERE cr.receipt_method_id = rm.receipt_method_id AND lk1.lookup_type = 'YES/NO' AND lk1.lookup_code = decode(b.remit_method_code,'FACTORING','Y','N') 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.status = 'REMITTED' AND crh.cash_receipt_history_id = crhprv.reversal_cash_receipt_hist_id AND crhprv.status = 'CONFIRMED' AND crh.batch_id = b.batch_id AND lk2.lookup_type(+) = 'REMITTANCE_METHOD' AND lk2.lookup_code(+) = b.remit_method_code AND cr.cash_receipt_id = ps.cash_receipt_id(+) AND b.remittance_bank_branch_id = chbb.branch_party_id

View Text - HTML Formatted

SELECT CR.RECEIPT_NUMBER
, CR.CURRENCY_CODE
, CR.AMOUNT
, CR.FACTOR_DISCOUNT_AMOUNT
, CR.RECEIPT_DATE
, CR.DEPOSIT_DATE
, NVL(PS.DUE_DATE
, CR.DEPOSIT_DATE)
, RM.PRINTED_NAME
, CR.EXCHANGE_RATE
, LK1.MEANING
, 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
, LK2.MEANING
, B.MEDIA_REFERENCE
, DECODE(CHBB.BANK_INSTITUTION_TYPE
, 'CLEARING HOUSE'
, CHBB.BANK_NAME
, NULL)
, DECODE(CHBB.BANK_INSTITUTION_TYPE
, 'CLEARING HOUSE'
, CHBB.BANK_BRANCH_NAME)
, DECODE(CHBB.BANK_INSTITUTION_TYPE
, 'CLEARING HOUSE'
, CHBB.BANK_NUMBER
, NULL)
, 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.CUSTOMER_RECEIPT_REFERENCE
, CR.PAYMENT_TRXN_EXTENSION_ID
, RM.PAYMENT_CHANNEL_CODE
FROM AR_CASH_RECEIPTS CR
, AR_RECEIPT_METHODS RM
, AR_LOOKUPS LK1
, 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_CASH_RECEIPT_HISTORY CRHPRV
, AR_BATCHES B
, AR_LOOKUPS LK2
, AR_PAYMENT_SCHEDULES PS
, CE_BANK_BRANCHES_V CHBB
WHERE CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND LK1.LOOKUP_TYPE = 'YES/NO'
AND LK1.LOOKUP_CODE = DECODE(B.REMIT_METHOD_CODE
, 'FACTORING'
, 'Y'
, 'N')
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.STATUS = 'REMITTED'
AND CRH.CASH_RECEIPT_HISTORY_ID = CRHPRV.REVERSAL_CASH_RECEIPT_HIST_ID
AND CRHPRV.STATUS = 'CONFIRMED'
AND CRH.BATCH_ID = B.BATCH_ID
AND LK2.LOOKUP_TYPE(+) = 'REMITTANCE_METHOD'
AND LK2.LOOKUP_CODE(+) = B.REMIT_METHOD_CODE
AND CR.CASH_RECEIPT_ID = PS.CASH_RECEIPT_ID(+)
AND B.REMITTANCE_BANK_BRANCH_ID = CHBB.BRANCH_PARTY_ID