Oracle Fusion Receivable - Data Flow

Invoice, Debit Memo, Credit Memo

select * from RA_CUSTOMER_TRX_ALL

-> get Customer_Trx_Id
-> get Trx_Class [INV, DM, CM] -> select * from fnd_lookup_values_vl where lookup_type='APPLICATION_TYPE'
-> get bill_to_customer_id


select * from ra_customer_trx_lines_all where customer_trx_id = 

select * from hz_cust_accounts where cust_account_id= <> bill_to_customer_id
-> get customer account details

SELECT * FROM AR_PAYMENT_SCHEDULES_ALL WHERE NVL(CUSTOMER_TRX_ID,-1) = <Customer_Trx_Id>
-> table contains payment details 

• CUSTOMER_TRX_ID
• STATUS [OP: Open, CL: Close]
• CLASS [INV, DM, CM]
• CASH_RECEIPT_ID 
• DUE_DATE
• AMOUNT_DUE_ORIGINAL
• AMOUNT_DUE_REMAINING
• CUSTOMER_ID
• CUSTOMER_SITE_USE_ID
• AMOUNT

SELECT * FROM AR_CASH_RECEIPTS_ALL WHERE CASH_RECEIPT_ID = <AR_PAYMENT_SCHEDULES_ALL .CASH_RECEIPT_ID>

[The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. 

Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts.

 For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. 

 Receivables associates a status with each receipt. These statuses include: APP for applied, UNAPP for unapplied, UNID for unidentified, NSF for nonsufficient funds, REV for reversed receipt and STOP for stop payment. 

 Receivables does not update the status of a receipt from UNAPP to APP until the entire amount of the receipt is either applied or placed on account. 

 A receipt can have a status of APP even if the entire receipt amount is placed on account. 

 Cash receipts proceed through the confirmation, remittance, and clearance steps. 

 Each step creates rows in the AR_CASH_RECEIPT_HISTORY table. 

 The CODE_COMBINATION_ID column in this table stores the general ledger accounts that are debited and credited as part of the cycle of steps. 

 The RECEIVABLES_TRX_ID column links the AR_CASH_RECEIPTS_ALL table to AR_RECEIVABLES_TRX_ALL table and identifies receivables activity associated with miscellaneous receipts. 

 The DISTRIBUTION_SET_ID column links AR_CASH_RECEIPTS_ALL table to AR_DISTRIBUTION_SETS_ALL table and identifies distribution set and distribution set line accounts that are credited for miscellaneous receipts. 

 The CUSTOMER_BANK_ACCOUNT_ID column is a foreign key to the IBY_EXT_BANK_ACCOUNTS table for bank accounts that do not belong to you and have a type of EXTERNAL. 

 The primary key for this table is CASH_RECEIPT_ID. ]