Oracle Subledger Related Various Important Sql
--Links between Invoice and XLA tables to find unposted invoice transaction
SELECT 1
FROM ap.ap_invoices_all ai,
ap.ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
AND EXISTS (
SELECT 'xla_flag_check'
FROM xla.xla_events xle,
xla.xla_ae_headers xlh,
xla.xla_ae_lines xll,
gl.gl_import_references gir
WHERE xle.event_id = aid.accounting_event_id
AND xle.event_id = xlh.event_id
AND xle.application_id = xlh.application_id
AND xlh.ae_header_id = xll.ae_header_id
AND xlh.application_id = xll.application_id
AND xll.gl_sl_link_id = gir.gl_sl_link_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
AND xlh.gl_transfer_status_code = 'Y'
AND xlh.accounting_entry_status_code = 'F')
AND aid.accrual_posted_flag = 'N'
AND aid.posted_flag = 'N'
AND ai.invoice_id = v_trx_id;
FROM ap.ap_invoices_all ai,
ap.ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
AND EXISTS (
SELECT 'xla_flag_check'
FROM xla.xla_events xle,
xla.xla_ae_headers xlh,
xla.xla_ae_lines xll,
gl.gl_import_references gir
WHERE xle.event_id = aid.accounting_event_id
AND xle.event_id = xlh.event_id
AND xle.application_id = xlh.application_id
AND xlh.ae_header_id = xll.ae_header_id
AND xlh.application_id = xll.application_id
AND xll.gl_sl_link_id = gir.gl_sl_link_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
AND xlh.gl_transfer_status_code = 'Y'
AND xlh.accounting_entry_status_code = 'F')
AND aid.accrual_posted_flag = 'N'
AND aid.posted_flag = 'N'
AND ai.invoice_id = v_trx_id;
--Invoice Hold
SELECT 1
FROM ap_holds_all
WHERE invoice_id = v_trx_id
AND release_lookup_code IS NULL;
FROM ap_holds_all
WHERE invoice_id = v_trx_id
AND release_lookup_code IS NULL;
--Links between Payments and XLA tables
SELECT 1
FROM ap_checks_all ac,
ap_payment_history_all aph,
xla_events xe,
xla_ae_headers xh,
xla_ae_lines xl
WHERE ac.check_id = aph.check_id
AND aph.accounting_event_id = xe.event_id
AND xe.event_id = xh.event_id;
FROM ap_checks_all ac,
ap_payment_history_all aph,
xla_events xe,
xla_ae_headers xh,
xla_ae_lines xl
WHERE ac.check_id = aph.check_id
AND aph.accounting_event_id = xe.event_id
AND xe.event_id = xh.event_id;
/* Unaccounted Invoices */
select ai.org_id,ai.invoice_num, ai.doc_sequence_value vr_no,ai.invoice_date, pv.vendor_name,
gl.padded_concatenated_segments ch_acct, ai.invoice_currency_code,
sum(decode (ai.invoice_currency_code, 'INR' , aid.amount, aid.base_amount)) amt,
--sum(nvl(aid.base_amount,aid.amount)) amt,
gl1.padded_concatenated_segments liab_code
from gl_code_combinations_kfv gl,
gl_code_combinations_kfv gl1,
po_vendor_sites_all pvs,
po_vendors pv,
ap_invoice_distributions_all aid,
ap_invoices_all ai
where 1=1
and gl1.code_combination_id = pvs.accts_pay_code_combination_id
and pvs.vendor_site_id = ai.vendor_site_id
and gl.code_combination_id = aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=ai.vendor_id
and SUBSTR(ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,ai.invoice_amount,ai.payment_status_flag,ai.invoice_type_lookup_code),1,20)
not in ('NEVER APPROVED', 'NEEDS REAPPROVAL')
and aid.posted_flag = 'N'
and aid.cash_posted_flag = 'N'
and aid.match_status_flag in ( 'A','T')
and aid.invoice_id=ai.invoice_id
and aid.accounting_date between '01-NOV-2013' and '30-NOV-2013'
and aid.org_id = ai.org_id
and aid.org_id=&orgid
group by ai.org_id,ai.invoice_num, ai.invoice_date, pv.vendor_name,ai.doc_sequence_value,
gl.padded_concatenated_segments,ai.invoice_currency_code,gl1.padded_concatenated_segments
gl.padded_concatenated_segments ch_acct, ai.invoice_currency_code,
sum(decode (ai.invoice_currency_code, 'INR' , aid.amount, aid.base_amount)) amt,
--sum(nvl(aid.base_amount,aid.amount)) amt,
gl1.padded_concatenated_segments liab_code
from gl_code_combinations_kfv gl,
gl_code_combinations_kfv gl1,
po_vendor_sites_all pvs,
po_vendors pv,
ap_invoice_distributions_all aid,
ap_invoices_all ai
where 1=1
and gl1.code_combination_id = pvs.accts_pay_code_combination_id
and pvs.vendor_site_id = ai.vendor_site_id
and gl.code_combination_id = aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=ai.vendor_id
and SUBSTR(ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,ai.invoice_amount,ai.payment_status_flag,ai.invoice_type_lookup_code),1,20)
not in ('NEVER APPROVED', 'NEEDS REAPPROVAL')
and aid.posted_flag = 'N'
and aid.cash_posted_flag = 'N'
and aid.match_status_flag in ( 'A','T')
and aid.invoice_id=ai.invoice_id
and aid.accounting_date between '01-NOV-2013' and '30-NOV-2013'
and aid.org_id = ai.org_id
and aid.org_id=&orgid
group by ai.org_id,ai.invoice_num, ai.invoice_date, pv.vendor_name,ai.doc_sequence_value,
gl.padded_concatenated_segments,ai.invoice_currency_code,gl1.padded_concatenated_segments
/* Unaccounted Payment */
select
c.check_number,
b.transaction_type,
c.amount,
c.currency_code,
c.base_amount,
c.bank_account_name,
(select concatenated_segments from gl_code_combinations_kfv
where code_combination_id = cbc.CASH_CLEARING_CCID) cash_clearing_ccid
from
CE_GL_ACCOUNTS_CCID CBC,
CE_BANK_ACCOUNTS CBA,
ap_checks_all c,
ap_payment_history_all b
--xla_events a
where
1=1
and cbc.bank_acct_use_id(+) = cba.bank_account_id
and cba.bank_account_name (+)= c.bank_account_name
and c.check_id = b.check_id
and c.org_id = b.org_id
and b.org_id = &orgid
--and b.accounting_event_id = a.event_id
and b.posted_flag = 'N'
and b.accounting_date between '01-NOV-2013' and '30-NOV-2013'
c.check_number,
b.transaction_type,
c.amount,
c.currency_code,
c.base_amount,
c.bank_account_name,
(select concatenated_segments from gl_code_combinations_kfv
where code_combination_id = cbc.CASH_CLEARING_CCID) cash_clearing_ccid
from
CE_GL_ACCOUNTS_CCID CBC,
CE_BANK_ACCOUNTS CBA,
ap_checks_all c,
ap_payment_history_all b
--xla_events a
where
1=1
and cbc.bank_acct_use_id(+) = cba.bank_account_id
and cba.bank_account_name (+)= c.bank_account_name
and c.check_id = b.check_id
and c.org_id = b.org_id
and b.org_id = &orgid
--and b.accounting_event_id = a.event_id
and b.posted_flag = 'N'
and b.accounting_date between '01-NOV-2013' and '30-NOV-2013'
/* Unaccounted Prepayment Query */
select ai.org_id, ai.invoice_num, ai.doc_sequence_value vr_no,ai.invoice_date, pv.vendor_name,
gl.padded_concatenated_segments ch_acct,
sum(decode (ai.invoice_currency_code, 'INR' , aid.amount, aid.base_amount)) amt,
gl1.padded_concatenated_segments liab_code,
aid.description
from gl_code_combinations_kfv gl,
gl_code_combinations_kfv gl1,
po_vendor_sites_all pvs,
po_vendors pv,ap_invoice_distributions_all aid,
ap_invoices_all ai
where 1=1
and gl1.code_combination_id = pvs.accts_pay_code_combination_id
and pvs.vendor_site_id = ai.vendor_site_id
and pv.vendor_id=ai.vendor_id
and gl.code_combination_id = aid.DIST_CODE_COMBINATION_ID
and aid.match_status_flag in ( 'A','T')
and nvl( aid.posted_flag,'N') <> 'Y'
and aid.accounting_date between '01-NOV-2013' and '30-NOV-2013'
and aid.line_type_lookup_code = 'PREPAY'
and aid.org_id = ai.org_id
and aid.invoice_id = ai.invoice_id
and ai.org_id=&orgid
group by ai.org_id,
ai.invoice_num, ai.doc_sequence_value,ai.invoice_date, pv.vendor_name,
gl.padded_concatenated_segments ,
gl1.padded_concatenated_segments ,
aid.description
order by 1
order by 1
gl.padded_concatenated_segments ch_acct,
sum(decode (ai.invoice_currency_code, 'INR' , aid.amount, aid.base_amount)) amt,
gl1.padded_concatenated_segments liab_code,
aid.description
from gl_code_combinations_kfv gl,
gl_code_combinations_kfv gl1,
po_vendor_sites_all pvs,
po_vendors pv,ap_invoice_distributions_all aid,
ap_invoices_all ai
where 1=1
and gl1.code_combination_id = pvs.accts_pay_code_combination_id
and pvs.vendor_site_id = ai.vendor_site_id
and pv.vendor_id=ai.vendor_id
and gl.code_combination_id = aid.DIST_CODE_COMBINATION_ID
and aid.match_status_flag in ( 'A','T')
and nvl( aid.posted_flag,'N') <> 'Y'
and aid.accounting_date between '01-NOV-2013' and '30-NOV-2013'
and aid.line_type_lookup_code = 'PREPAY'
and aid.org_id = ai.org_id
and aid.invoice_id = ai.invoice_id
and ai.org_id=&orgid
group by ai.org_id,
ai.invoice_num, ai.doc_sequence_value,ai.invoice_date, pv.vendor_name,
gl.padded_concatenated_segments ,
gl1.padded_concatenated_segments ,
aid.description
order by 1
order by 1
--Orphan Events:
select * from xla_events xe
where trunc(event_date) between '01-MAY-2013' and '31-MAY-2013'
and xe.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 = 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);
where trunc(event_date) between '01-MAY-2013' and '31-MAY-2013'
and xe.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 = 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);