Step1:
AP --> XLA --> GL links
SELECT invoice_id
FROM ap_invoices_all
WHERE invoice_num = <invoice_number>;
SELECT amount,
period_name,
accounting_date,
posted_flag,
accrual_posted_flag,
accounting_event_id,
dist_code_combination_id,
invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = < invoice_id >;
SELECT entity_id
FROM xla_events
WHERE event_id = <accounting_event_id >;
SELECT ae_header_id
FROM xla_ae_headers
WHERE entity_id = <entity_id>;
SELECT gl_sl_link_id
FROM xla_ae_lines
WHERE ae_header_id = < ae_header_id >
AND code_combination_id = < dist_code_combination_id >;
SELECT je_batch_id,
je_header_id
FROM gl_import_references
WHERE gl_sl_link_id = <gl_sl_link_id>;
By
using the below query we can view journal entry batches from which the invoice
is posted.
SELECT name Batch_name, Status,
Default_period_name,
Posted_date, Posting_run_id
FROM gl_je_batches
WHERE je_batch_id = < je_batch_id >;
By
using the below query we can view the Journal entry headers.
SELECT name Journal_name, Je_category, Je_source,
Ledger_id, Period_name, Je_from_SLA_flag,
Status
FROM gl_je_headers
WHERE je_header_id = < je_header_id >;
By
using the below query we can view the Journal entry lines of a particular
Journal entry header
SELECT Je_line_num,
code_combination_id, Period_name,
Entered_dr, Entered_cr, Ledger_id
FROM gl_je_lines
WHERE je_header_id = < je_header_id
>;
If
we want to see the Journal entry lines of a particular invoice distribution
line, use the below query as shown:
SELECT Je_line_num, code_combination_id,
Period_name,
Entered_dr, Entered_cr, Ledger_id
FROM gl_je_lines
WHERE je_header_id = < je_header_id
>
AND code_combination_id = <
dist_code_combination_id from Ap_invoice_distributions_all >
AND Period_name = < Period_name from Ap_invoice_distributions_all >;
XLA_DISTRIBUTION_LINKS
stores the link between transactions and Subledger journal entry lines.
This is the
SLA Distributions table. It captures the lowest level granularity of a
transaction’s accounting. It is important to understand the information
captured in XDL. This table stores the details at the distribution level for
both source and applied transactions because AP utilizes the business flow
feature of SLA . Applied Transaction means a
transaction such as Payments, which is applied to an Invoice, or it could also
mean a transaction, such as a Prepayment.
Xla_distribution_links Table Column Name |
Description
|
Reference to AP Table
Column Name
|
Source_distribution_id_num_1
|
DIST_ID of the current transaction
(For invoice –
invoice_distribution_id column of
Ap_invoice_distributions_all table.
For payment – payment_hist_dist_id column
of ap_payment_hist_dists table)
|
|
Source_distribution_type
|
AP_PMT_DIST (For Payments), AP_INV_DIST
(For Invoice), AP_PREPAY (For Prepayment)
|
|
Applied_to_entity_id
|
Entity_id of the invoice/upstream
transaction
|
|
Applied_to_source_id_num_1
|
Invoice_id in case of LIABILITY line
of the payment
|
|
Applied_to_dist_id_num_1
|
Invoice_distribution_id against which this payment_distribution_id is
applied
|
Ap_invoice_payments_all
&
Ap_payment_hist_dists tables
|
Ref_ae_header_id
|
Invoice_id of the invoice/upstream
transaction
|
|
Ref_temp_line_num
|
Temp_line_num of the invoice/upstream
transaction
|
|
Ref_event_id
|
Event_id of the invoice/upstream
transaction
|
SELECT Source_distribution_id_num_1,
Source_distribution_type,
Applied_to_entity_id,
Applied_to_source_id_num_1,
Applied_to_dist_id_num_1,
Ref_ae_header_id,
Ref_temp_line_num,
Ref_event_id
FROM xla_distribution_links
WHERE ae_header_id = < ae_header_id >;
The tables’ xla_distribution_links and
Ap_invoice_distributions_all are linked by
Source_distribution_id_num_1 =
Invoice_distribution_id
Source_distribution_type =
‘AP_INV_DIST’
SELECT Source_distribution_id_num_1,
Source_distribution_type,
Applied_to_entity_id,
Applied_to_source_id_num_1,
Applied_to_dist_id_num_1,
Ref_ae_header_id,
Ref_temp_line_num,
Ref_event_id
FROM xla_distribution_links
WHERE ae_header_id = < ae_header_id >
AND source_distribution_type = 'AP_INV_DIST'
AND source_distribution_id_num_1 = (SELECT invoice_distribution_id
FROM Ap_invoice_distributions_all
WHERE invoice_id
= < Invoice_id >);
Step2: GL --> SLA
--> PA Links
SELECT *
FROM gl_je_lines
FROM gl_je_lines
WHERE je_header_id = < je_header_id
>
AND Je_line_num = < Je_line_num >;
SELECT *
FROM gl_import_references
WHERE je_header_id = < je_header_id
>
AND Je_line_num = < Je_line_num >;
AND Je_line_num = < Je_line_num >;
SELECT *
FROM xla_ae_lines
WHERE gl_sl_link_id in (SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = < je_header_id >
AND Je_line_num = < Je_line_num >;
SELECT *
FROM xla_distribution_links
WHERE ae_header_id in (SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id in (SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = <
je_header_id >
AND Je_line_num = < Je_line_num >))
AND ae_line_num = < ae_line_num>;
SELECT source_distribution_id_num_1
FROM xla_distribution_links
WHERE ae_header_id in (SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id in (SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id
AND
Je_line_num = < Je_line_num >) )
SELECT *
FROM pa_cost_distribution_lines_all
FROM pa_cost_distribution_lines_all
WHERE expenditure_item_id in
( SELECT source_distribution_id_num_1
FROM xla_distribution_links
WHERE ae_header_id in (SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id in (SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id
AND Je_line_num = < Je_line_num >) )
);
);
Step3: GL --> SLA --> FA Links
SELECT *
FROM gl_je_headers
WHERE NAME = <JOURNAL_NAME>;
SELECT *
FROM gl_je_lines
WHERE je_header_id = <JE_HEADER_ID>
AND code_combination_iD = <CCID>;
SELECT *
FROM gl_import_references
WHERE je_header_id = <JE_HEADER_ID>
AND je_line_num IN (1,2);
SELECT *
FROM xla_ae_lines
WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = <JE_HEADER_ID>
AND je_line_num IN (1,2));
SELECT *
FROM xla_distribution_links
WHERE ae_header_id IN (SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id IN
(SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = <JE_HEADER_ID>
AND je_line_num IN (1,2)));
SELECT *
FROM fa_deprn_detail
WHERE event_id IN
( SELECT event_id
FROM xla_distribution_links
WHERE ae_header_id IN (SELECT ae_header_id
FROM xla_ae_lines
WHERE gl_sl_link_id IN
(SELECT gl_sl_link_id
FROM gl_import_references
WHERE je_header_id = <JE_HEADER_ID>
AND je_line_num IN (1,2)))
);
Excellent information of the blog posting I liked it
ReplyDeleteSanjary Academy provide pressure vessel design, quality management system course piping design course, qa/qc course and document controller course.
Welding Inspector Course
Safety officer course
Quality Management Course
Quality Management Course in India