Oracle R12 Subledger Related Various Important Sql

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;

--Invoice Hold   

SELECT 1
                        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;

/* 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

/* 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'

/* 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

--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);