GL Important Queries ( XLA)
XLA_EVENTSSELECT DISTINCT xe.*
FROM ap_invoices_all ai,
xla_events xe,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xe.application_id
--AND ai.invoice_id = '156014'
AND ai.invoice_num = 'SRI_SAL-PY-02-2022-PAYTOGL'
AND ai.invoice_num in ('SRI_SAL-PY-02-2022-PAYTOGL','SRI_SAL-PY-04-2022-PAYTOGL')
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xe.entity_id
ORDER BY
xe.entity_id,
xe.event_number;
'SRI_SAL-PY-02-2022-PAYTOGL'
ENTITY_ID = 15172142
'SRI_SAL-PY-04-2022-PAYTOGL'
ENTITY_ID = 15172201
XLA_AE_HEADERS
SELECT DISTINCT xeh.*
FROM xla_ae_headers xeh,
ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xeh.application_id
--AND ai.invoice_id = '156014'
AND ai.invoice_num in ('SRI_SAL-PY-02-2022-PAYTOGL','SRI_SAL-PY-04-2022-PAYTOGL')
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xeh.event_id,
xeh.ae_header_id ASC;
'SRI_SAL-PY-02-2022-PAYTOGL'
ENTITY_ID = 15172142
'SRI_SAL-PY-04-2022-PAYTOGL'
ENTITY_ID = 15172201
XLA_AE_LINES
SELECT DISTINCT xel.*,
fnd_flex_ext.get_segs('SQLGL','GL#', '50577' , xel.code_combination_id) "Account"
FROM xla_ae_lines xel,
xla_ae_headers xeh,
ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xel.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND ai.invoice_id = '156014'
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xel.ae_header_id,
xel.ae_line_num ASC;
XLA_DISTRIBUTION_LINKS
SELECT DISTINCT xdl.*
FROM xla_distribution_links xdl,
xla_ae_headers xeh,
ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xdl.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND ai.invoice_id = '156014'
AND xdl.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xdl.event_id,
xdl.a_header_id,
xdl.ae_line_num ASC;
XLA_TRANSACTION_ENTITIES
SELECT DISTINCT xte.*
FROM ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND ai.invoice_id = '156014'
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id;
XLA_ACCOUNTING_ERRORS
SELECT DISTINCT xae.*
FROM ap_invoices_all ai,
xla_events xe,
xla.xla_transaction_entities xte,
xla_accounting_errors xae
WHERE xte.application_id = 200
AND xae.application_id = xte.application_id
AND xte.application_id = xe.application_id
AND ai.invoice_id = '156014'
AND xe.event_id = xae.event_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_idAND xte.entity_id = xe.entity_id;
---------- Matching this query data with GL Trail Balance Report -------------------
gcc.concatenated_segments,
gcc.segment7,
bal_table.period_dr,
bal_table.period_cr,
bal_table.period_balances,
bal_table.year_to_date_balance,
SUM(nvl(gjl.accounted_dr,0)) line_dr,
SUM(nvl(accounted_cr,0)) line_cr,
sum(nvl(gjl.accounted_dr,0)) - sum(nvl(accounted_cr,0)) line_bal
--,gjh.JE_BATCH_ID
FROM
(
SELECT
bal.code_combination_id,
bal.currency_code,
bal.ledger_id,
bal.period_name,
SUM(begin_balance_dr) begin_dr,
SUM(begin_balance_cr) begin_cr,
SUM(period_net_dr) period_dr,
SUM(period_net_cr) period_cr,
SUM(period_net_dr) - SUM(period_net_cr) period_balances,
SUM(begin_balance_dr) + SUM(period_net_dr) end_dr,
SUM(begin_balance_cr) + SUM(period_net_cr) end_cr,
( SUM(begin_balance_dr) + SUM(period_net_dr) ) - ( SUM(begin_balance_cr) + SUM(period_net_cr) ) year_to_date_balance
FROM
gl_balances bal
GROUP BY
bal.code_combination_id,
bal.currency_code,
bal.ledger_id,
bal.period_name
) bal_table,
gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations_kfv gcc
WHERE bal_table.code_combination_id = gjl.code_combination_id
AND bal_table.code_combination_id = gcc.code_combination_id
AND bal_table.period_name = gjl.period_name
AND bal_table.ledger_id = gjl.ledger_id
and gjl.je_header_id = gjh.je_header_id
--AND gcc.segment1 = '51'
--AND bal_table.code_combination_id = 719953
--AND bal_table.period_name = '12-2016'
--AND bal_table.currency_code = 'USD'
and bal_table.ledger_id = 2021
And Gjl.Ledger_Id <> 2101
AND gjh.je_source = 'Payables'
AND gjh.je_source NOT LIKE '%SRIIGIGBMJL%'
AND UPPER(gjh.je_category) NOT LIKE '%SRIBUDGET%'
AND gjh.je_category = 'Purchase Invoices'
AND gcc.code_combination_id = gjl.code_combination_id
AND UPPER(gjl.period_name) IN (
SELECT UPPER(period_name)
FROM gl_periods
WHERE 1 = 1
AND period_set_name = 'SRI-USA CAL'
AND start_date >= (SELECT start_date
From Gl_Periods
WHERE UPPER(period_name) = UPPER(:p_starting_period)
AND period_set_name = 'SRI-USA CAL')
AND end_date <= (SELECT end_date
From Gl_Periods
WHERE UPPER(period_name) = UPPER(:p_ending_period)
And Period_Set_Name ='SRI-USA CAL'))
and gcc.segment6 >= nvl (:p_account_from, gcc.segment6)
AND gcc.segment6 <= NVL (:p_account_to, gcc.segment6)
GROUP BY
gcc.concatenated_segments,
gcc.segment7,
bal_table.period_dr,
bal_table.period_cr,
bal_table.period_balances,
bal_table.year_to_date_balance
--,gjh.JE_BATCH_ID
order by 2 Asc;
l_ledger_id = 15
end_period = JAN-22
Account_to = 153107
P_account_from = 153107
Start_period = JAN-22
0 comments:
Post a Comment