Common Problems and Solutions Oracle Cloud Fusion Account Receivables (AR) Troubleshooting Guide Month End 

(Reference - Oracle Knowledge Base)

========================================================================= 

Problem --> There are still pending transactions in the table of the distribution for which revenue is not recognized.

SQL for pending transactions in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.trx_number, ct.trx_date, ct.customer_trx_id, gld.gl_date

FROM ra_cust_trx_line_gl_dist_all gld,

ra_customer_trx_all ct

WHERE gld.account_class = 'REC'

AND gld.latest_rec_flag='Y'

AND gld.account_set_flag = 'Y'

AND gld.gl_date BETWEEN p_start_date AND p_end_date

AND ct.customer_trx_id = gld.customer_trx_id

AND ct.complete_flag = 'Y'

AND ct.org_id = gld.org_id

<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

Problem --> Unaccounted transactions in the SLA Sub Ledger exception report. This implies that

there are no un-processed events in SLA, but the period close process is still reporting 

un posted data.This implies that there are possibly AR distributions which have missing

SLA events.

Solution:

We will use the following set of SQL statements to identify the AR transactions which are being reported as un-posted (or unaccounted).

SQL for Unposted Adjustments in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.trx_number,

ct.trx_date, ct.customer_trx_id, adj.gl_date, adj.type

FROM ar_adjustments_all adj,

ra_customer_trx_all ct

WHERE adj.posting_control_id = -3

AND adj.gl_date BETWEEN p_start_date AND p_end_date

AND NVL(adj.postable,'Y') = 'Y'

AND adj.customer_trx_id = ct.customer_trx_id(+)

AND adj.org_id = ct.org_id

SQL for Miscellaneous Receipts in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.CASH_RECEIPT_ID, ct.RECEIPT_NUMBER, ct.RECEIPT_DATE, adj.GL_DATE

FROM ar_misc_cash_distributions_all adj,

ar_cash_receipts_all ct

WHERE adj.posting_control_id = -3

AND adj.gl_date BETWEEN p_start_date AND p_end_date

AND adj.CASH_RECEIPT_ID = ct.CASH_RECEIPT_ID

AND adj.org_id = ct.org_id

SQL for Transactions (Invoices, Credit Memos, Debit Memos) in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.trx_number, ct.trx_date, ct.customer_trx_id, gld.gl_date

FROM ra_cust_trx_line_gl_dist_all gld,

ra_customer_trx_all ct

WHERE ct.customer_trx_id = gld.customer_trx_id

AND gld.account_set_flag = 'N'

AND gld.gl_date BETWEEN p_start_date AND p_end_date

AND ct.complete_flag = 'Y'

AND ct.org_id = gld.org_id

SQL for Receivable Application (cash applications and credit memo applications) in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.trx_number, ct.trx_date, ct.customer_trx_id, ra.gl_date, ra.application_type

FROM ar_receivable_applications_all ra,

ra_customer_trx_all ct

WHERE ra.posting_control_id = -3

AND ra.gl_date BETWEEN p_start_date AND p_end_date

AND NVL(ra.postable,'Y') = 'Y'

AND ( ra.customer_trx_id = ct.customer_trx_id

OR

ra.applied_customer_trx_id = ct.customer_trx_id )

AND ra.org_id = ct.org_id

SQL for Cash Receipt History  in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.CASH_RECEIPT_ID, ct.RECEIPT_NUMBER, ct.RECEIPT_DATE, crh.GL_DATE

FROM ar_cash_receipt_history_all crh,

ar_cash_receipts_all ct

WHERE crh.posting_control_id = -3

AND crh.gl_date BETWEEN p_start_date AND p_end_date

AND crh.CASH_RECEIPT_ID = ct.CASH_RECEIPT_ID

AND crh.org_id = ct.org_id

<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

Problem --> Un-posted receipt batches sitting in the AR_INTERIM_CASH_RECEIPTS table. 

These receipt batches would have possibly come from Lockbox or an excel upload.

Solution:

You can find these receipt batches pending posting in the Receivables Balances Work Area.

Please post these batches.

SQL for Un-posted receipt batches in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT ct.org_id, ct.CASH_RECEIPT_ID, ct.RECEIPT_NUMBER, ct.RECEIPT_DATE, ct.GL_DATE

FROM ar_interim_cash_receipts_all ct

WHERE ct.posting_control_id = -3

AND ct.gl_date BETWEEN p_start_date AND p_end_date;

SQL for posted transactions in Oracle Cloud Fusion R13 Account Receivable

=========================================================================

SELECT DISTINCT customer_trx_id, posting_control_id FROM ra_cust_trx_line_gl_dist_all gldist WHERE EXISTS

 (SELECT ev.event_id FROM xla_events ev WHERE ev.event_id = gldist.event_id AND ev.event_status_code = 'P')

<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>