Find Payables Orphan Accounting Events

For orphan processed non-budgetary events the following query can be run.

SELECT DISTINCT 'EVENT', xe.EVENT_ID,xe.EVENT_STATUS_CODE,xe.PROCESS_STATUS_CODE,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xe.event_date,
xe.entity_id,
xe.event_type_code,
xe.budgetary_control_flag,
xe.upg_batch_id,
'Y'
FROM xla_events xe,
xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xe.application_id = 200
AND xe.event_status_code = 'P'
AND xah.application_id = 200
AND xah.event_id = xe.event_id
AND xte.entity_id=xe.entity_id
AND xte.application_id=200
AND nvl(xe.budgetary_control_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND xe.event_type_code NOT IN ('MANUAL', 'REVERSAL')
AND (xe.upg_batch_id IS NULL OR xe.upg_batch_id = -9999);

For unprocessed non-budgetary events run the query below, it should not have any rows other than those updated to N in Step 3:

SELECT DISTINCT 'EVENT', xe.EVENT_ID,xe.EVENT_STATUS_CODE,xe.PROCESS_STATUS_CODE,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xe.event_date,
xe.entity_id,
xe.event_type_code,
xe.budgetary_control_flag,
xe.upg_batch_id,
'Y'
FROM xla_events xe,
xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xe.application_id = xte.application_id
AND xe.event_status_code <> 'P'
and xe.event_id = xah.event_id (+)
and xah.application_id(+) = xe.application_id
AND xte.entity_id=xe.entity_id
AND xte.application_id=200
AND nvl(xe.budgetary_control_flag, 'N') = 'N'
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND xe.event_type_code not in ('MANUAL', 'REVERSAL')
AND (xe.upg_batch_id is NULL or xe.upg_batch_id = -9999)
AND NOT EXISTS
(SELECT 'No final accounted headers'
FROM xla_ae_headers xah
WHERE xah.event_id = xe.event_id
AND xah.application_id=200
AND xah.entity_id = xte.entity_id
AND xah.accounting_entry_status_code='F'
AND xah.gl_transfer_status_code='Y')
UNION
SELECT DISTINCT 'HEADER', xah.EVENT_ID,null,null,
xah.ae_header_id, xte.source_id_int_1 SOURCE_ID,
xte.entity_code source_table,
(case
when xte.entity_code='AP_INVOICES' then
(select invoice_num
from ap_invoices_all ai
where ai.invoice_id = xte.source_id_int_1)
when xte.entity_code='AP_PAYMENTS' then
(select to_char(check_number)
from ap_checks_all ac,
ap_invoice_payments_all aip
where ac.check_id = aip.check_id
and ac.check_id = xte.source_id_int_1
and rownum = 1)
end
) INVOICE_NUM_OR_CHECK_NUMBER,
xah.accounting_date,
xah.entity_id,
xah.event_type_code,
null,
xah.upg_batch_id,
'Y'
FROM xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xah.application_id = xte.application_id
AND xte.entity_id=xah.entity_id
AND xte.application_id=200
AND NOT EXISTS
(SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No Distributions exist for the bc_event_id'
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No payment history rows exists for this event'
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No self assessed tax rows exists for the bc_event_id'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No prepay history rows exists for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.accounting_event_id = xah.event_id)
AND NOT EXISTS
(SELECT 'No prepayment history rows exists for the bc_event_id'
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xah.event_id)
AND NOT EXISTS
(SELECT ' Event for this header does not exist'
FROM xla_events xe
WHERE xe.application_id = xah.application_id
AND xe.event_id = xah.event_id)
AND xah.event_type_code not in ('MANUAL', 'REVERSAL')
AND (xah.upg_batch_id is NULL or xah.upg_batch_id = -9999)
AND nvl(xah.gl_transfer_status_code, 'X') <>'Y'
AND nvl(xah.accounting_entry_status_code, 'X') <> 'F';