Queries to find orphans records in Account Receivables (AR) - Period closing impacting transaction

Queries to find orphans records in Account Receivables (AR) - Period closing impacting transaction


When a user creates a Receipt/Transaction from respective UI, distributions are populated in XLA.  On deletion of Receipt/Transaction the respective distributions of XLA not get refreshed precisely.
This records may create issue while closing period.


1) Query to get orphans records for AR Transactions


select xte.source_id_int_1, xte.entity_id, xe.event_id, xe.event_date, xe.event_type_code, xe.event_status_code
from xla_events xe, xla.xla_transaction_entities xte
where xte.entity_code = 'TRANSACTIONS'
and xte.entity_id = xe.entity_id
and xte.application_id = 222
and xte.ledger_id = l_ledger_id
and xe.event_date between p_start_gl_date and p_end_gl_date
and xe.application_id = 222
and xe.event_status_code <> 'P'
and not exists
(select 'x' from ra_cust_Trx_line_gl_dist_all
 where gl_date between p_start_gl_date and p_end_gl_date
 and   customer_trx_id = xte.source_id_int_1
 and   posting_control_id = -3
 and   event_id = xe.event_id
 union
 select 'x' from ar_receivable_applications_all
 where gl_date between p_start_gl_date and p_end_gl_date
 and   customer_trx_id = xte.source_id_int_1
 and   posting_control_id = -3
 and   event_id = xe.event_id
 union
 select 'x' from ar_receivable_applications_all
 where gl_date between p_start_gl_date and p_end_gl_date
 and   applied_customer_trx_id = xte.source_id_int_1
 and   posting_control_id = -3
 and   event_id = xe.event_id
 )
 order by xe.event_id;


2) Query to get orphans records for AR Receipts


select xte.source_id_int_1, xte.entity_id, xe.event_id, xe.event_date, xe.event_type_code, xe.event_status_code
from xla.xla_events xe, xla.xla_transaction_entities xte
where xte.application_id = 222
and xe.application_id= xte.application_id
and xte.ledger_id = p_ledger_id
and xe.entity_id = xte.entity_id
and xte.entity_code='RECEIPTS'
and xe.event_status_code <> 'P'
and xe.event_date between p_start_gl_date and p_end_gl_date
and not exists
(select 'x' from ar_distributions_all dis, ar_receivable_applications_all ra
 where ra.cash_receipt_id = xte.source_id_int_1
 and dis.source_table = 'RA'
 and dis.source_id = ra.receivable_application_id
 and ra.gl_date between p_start_gl_date and p_end_gl_date
 and ra.posting_control_id = -3
 and ra.event_id = xe.event_id
 union
 select 'x' from ar_distributions_all dis, ar_cash_receipt_history_all crh
 where crh.cash_receipt_id = xte.source_id_int_1
 and dis.source_table = 'CRH'
 and dis.source_id = crh.cash_receipt_history_id
 and crh.gl_date between p_start_gl_date and p_end_gl_date
 and crh.posting_control_id = -3
 and crh.event_id = xe.event_id
 union
 select 'x' from ar_distributions_all dis, ar_misc_cash_distributions_all mcd
 where mcd.cash_receipt_id = xte.source_id_int_1
 and   dis.source_table = 'MCD'
 and   dis.source_id = mcd.misc_cash_distribution_id
 and   mcd.gl_date between p_start_gl_date and p_end_gl_date
 and   mcd.posting_control_id = -3
 and   mcd.event_id = xe.event_id
 )
order by xe.event_id;


3) Query to get orphans records for AR Adjustments


select xte.source_id_int_1, xte.entity_id, xe.event_id, xe.event_date, xe.event_type_code, xe.event_status_code
from xla_events xe, xla.xla_transaction_entities xte
where xte.entity_code = 'ADJUSTMENTS'
and xte.entity_id = xe.entity_id
and xte.application_id = 222
and xte.ledger_id = p_ledger_id
and xe.event_date between p_start_gl_date and p_end_gl_date
and xe.application_id = 222
and xe.event_status_code <> 'P'
and not exists
(select 'x' from ar_adjustments_all
 where gl_date between p_start_gl_date and p_end_gl_date
 and   adjustment_id = xte.source_id_int_1
 and   posting_control_id = -3
 and   event_id = xe.event_id
)
order by xe.event_id;


If you get any records in this queries then open oracle SR and request for delete orphans records script from oracle which will help you clear all orphans records exist in your application.