R12 How to Identify Data That Has Posted From SLA, But Is Not Appearing In GL (Doc ID 754835.1)

Last updated on SEPTEMBER 25, 2023

Applies to:

Goal

How to identify a posting batch that is posted from AR and SLA tables, but does not appear in GL:

SELECT xah.ledger_id, xah.gl_transfer_status_code, xah.GROUP_ID, COUNT (*)
FROM apps.xla_ae_headers xah, apps.xla_ae_lines xal
WHERE xah.application_id = xal.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = 222
AND xah.ledger_id = &ledger_id
AND xah.accounting_entry_status_code = 'F'
AND xah.gl_transfer_status_code = 'Y'
AND xah.event_type_code <> 'MANUAL'
AND xal.gl_sl_link_id IS NOT NULL
AND xah.accounting_date <= to_date('&gl_date','dd-mon-yyyy')
AND NOT EXISTS (
SELECT 1
FROM gl_import_references
WHERE gl_sl_link_table = xal.gl_sl_link_table
AND gl_sl_link_id = xal.gl_sl_link_id)
GROUP BY xah.ledger_id, xah.gl_transfer_status_code, xah.GROUP_ID;

Verify data is not in gl_interface or GL temp table:

SELECT * from xla_glt_&group_id;

SELECT group_id, status, user_je_source_name, set_of_books_id 
FROM gl_interface 
WHERE group_id = &group_id;

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.