Below are the queries for AR. Pass on to the team members if
anyone needs they can use for remediating AR objects.
-- AR
Transactions
SELECT rct.trx_number,
rct.customer_trx_id,
rctd.cust_trx_line_gl_dist_id,
xal.entered_dr,
xal.entered_cr,
xal.code_combination_id,
xah.event_id,
xal.ae_header_id
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctd,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl
WHERE xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.application_id = 222
AND xdl.source_distribution_type IN ('RA_CUST_TRX_LINE_GL_DIST_ALL',
'MFAR_DISTRIBUTIONS_ALL')
AND xdl.source_distribution_id_num_1 = rctd.CUST_TRX_LINE_GL_DIST_ID
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.trx_number = :p_trx_number;
-- AR adjustments
SELECT rct.trx_number,
adj.customer_trx_id,
adj.adjustment_id,
xal.entered_dr,
xal.entered_cr,
xal.code_combination_id,
xah.event_id,
xal.ae_header_id
FROM ra_customer_trx_all rct,
ar_adjustments_all adj,
ar_distributions_all ard,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl
WHERE xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.application_id = 222
AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
'MFAR_DISTRIBUTIONS_ALL')
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_table = 'ADJ'
AND ard.source_id = adj.adjustment_id
AND adj.adjustment_id = :p_adj_id
AND rct.customer_trx_id = adj.customer_trx_id
AND rct.trx_number = :p_trx_number;
-- Misc
Receipts
SELECT cr.receipt_number,
cr.cash_receipt_id,
arm.misc_cash_distribution_id,
xal.entered_dr,
xal.entered_cr,
xal.code_combination_id,
xah.event_id,
xal.ae_header_id
FROM ar_cash_receipts_all cr,
ar_misc_cash_distributions_all arm,
ar_distributions_all ard,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl
WHERE xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
'MFAR_DISTRIBUTIONS_ALL')
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_id = arm.misc_cash_distribution_id
AND ard.source_table = 'MCD'
AND xdl.application_id = 222
AND arm.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = :receipt_id;
-- CASH Receipts
SELECT cr.receipt_number,
cr.cash_receipt_id,
arp.receivable_application_id,
xal.entered_dr,
xal.entered_cr,
xal.code_combination_id,
xah.event_id,
xal.ae_header_id
FROM ar_cash_receipts_all cr,
ar_receivable_applications_all arp,
ar_distributions_all ard,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
gl_code_combinations gcc
WHERE xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
'MFAR_DISTRIBUTIONS_ALL')
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_id = arp.receivable_application_id
AND ard.source_table = 'RA'
AND xdl.application_id = 222
AND arp.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = :receipt_id
AND gcc.code_combination_id = xal.code_combination_id
AND gcc.segment1 != '00000000000000'
