View: AR_REMITTED_RECEIPTS_FORMAT_V

Product:	AR - Receivables
Description:	Holds general information of remitted receipts being formatted for reporting purpose
Implementation/DBA Data:	APPS.AR_REMITTED_RECEIPTS_FORMAT_V

View Text

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

Columns