Collecting the sub ledger AP information
(Invoice Information)
Here
we collect all the data about the Invoice number from sub-ledger (AP) tables.
The most important information and used to link with GL tables is AP_INVOICES_ALL.invoice_id
SELECT aia.invoice_id --
This will form a link with SLA Tables
,aia.invoice_num
,aia.invoice_amount
,aia.payment_status_flag
,aia.doc_category_code
,aila.line_type_lookup_code
,aila.line_source
,aila.match_type
FROM ap_invoices_all aia
,ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.invoice_id = 11564
AND aia.org_id = 205;
Checking the sub-ledger Tables to General-ledger link tables for
GL information
In
R12, we have tables that holds link between any sub ledger data to General
ledger data.
The
below query gives you the important columns available in the XLA tables
and GL linking columns
SELECT xte.legal_entity_id
,xte.source_id_int_1 Invoice_id
,xte.transaction_number
,xal.gl_sl_link_id --
Connects to GL tables
,xal.gl_sl_link_table --
Connects to GL tables
,xah.ae_header_id
,xah.event_id
,xah.entity_id
,xah.event_type_code
,xah.description
,xal.ae_line_num
,xal.code_combination_id
,xal.accounting_class_code
,xal.party_id
,xal.party_site_id
,xal.party_type_code
FROM xla_transaction_entities xte
,xla_events
xev
,xla_ae_headers
xah
,xla_ae_lines
xal
WHERE xte.application_id = 401
AND xte.source_id_int_1 = 11564
AND xte.transaction_number= '10501'
AND xev.entity_id = xte.entity_id
AND xev.application_id = xte.application_id
AND xah.application_id = xev.application_id
AND xev.event_id = xah.event_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id;
Collecting the data from GL
Below
query collects the data from GL.
In
the below query, you the see the linking columns in the first five places and
others were other important ones.
SELECT glimp.reference_5 ENTITY_ID
,glimp.reference_6 EVENT_ID
,glimp.reference_7 AE_HEADER_ID
,glimp.gl_sl_link_id
,glimp.gl_sl_link_table
,glb.je_batch_id
,glb.name
,glb.status
,glb.description
,glh.je_header_id
,glh.je_category
,glh.je_source
,glh.name
,glh.description
,glh.running_total_accounted_cr
,glh.running_total_accounted_dr
,gll.je_line_num
,gll.code_combination_id
FROM gl_import_references glimp
,gl_je_batches glb
,gl_je_headers glh
,gl_je_lines gll
WHERE glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glimp.je_batch_id = glb.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND glh.je_batch_id = glb.je_batch_id
Collecting the sub ledger AP information
(Invoice Information)
Here
we collect all the data about the Invoice number from sub-ledger (AP) tables.
The most important information and used to link with GL tables is AP_INVOICES_ALL.invoice_id
SELECT aia.invoice_id --
This will form a link with SLA Tables
,aia.invoice_num
,aia.invoice_amount
,aia.payment_status_flag
,aia.doc_category_code
,aila.line_type_lookup_code
,aila.line_source
,aila.match_type
FROM ap_invoices_all aia
,ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.invoice_id = 11564
AND aia.org_id = 205;
Checking the sub-ledger Tables to General-ledger link tables for
GL information
In
R12, we have tables that holds link between any sub ledger data to General
ledger data.
The
below query gives you the important columns available in the XLA tables
and GL linking columns
SELECT xte.legal_entity_id
,xte.source_id_int_1 Invoice_id
,xte.transaction_number
,xal.gl_sl_link_id --
Connects to GL tables
,xal.gl_sl_link_table --
Connects to GL tables
,xah.ae_header_id
,xah.event_id
,xah.entity_id
,xah.event_type_code
,xah.description
,xal.ae_line_num
,xal.code_combination_id
,xal.accounting_class_code
,xal.party_id
,xal.party_site_id
,xal.party_type_code
FROM xla_transaction_entities xte
,xla_events
xev
,xla_ae_headers
xah
,xla_ae_lines
xal
WHERE xte.application_id = 401
AND xte.source_id_int_1 = 11564
AND xte.transaction_number= '10501'
AND xev.entity_id = xte.entity_id
AND xev.application_id = xte.application_id
AND xah.application_id = xev.application_id
AND xev.event_id = xah.event_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id;
Collecting the data from GL
Below
query collects the data from GL.
In
the below query, you the see the linking columns in the first five places and
others were other important ones.
SELECT glimp.reference_5 ENTITY_ID
,glimp.reference_6 EVENT_ID
,glimp.reference_7 AE_HEADER_ID
,glimp.gl_sl_link_id
,glimp.gl_sl_link_table
,glb.je_batch_id
,glb.name
,glb.status
,glb.description
,glh.je_header_id
,glh.je_category
,glh.je_source
,glh.name
,glh.description
,glh.running_total_accounted_cr
,glh.running_total_accounted_dr
,gll.je_line_num
,gll.code_combination_id
FROM gl_import_references glimp
,gl_je_batches glb
,gl_je_headers glh
,gl_je_lines gll
WHERE glimp.je_header_id = glh.je_header_id
AND glimp.je_line_num = gll.je_line_num
AND glimp.je_batch_id = glb.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND glh.je_batch_id = glb.je_batch_id
calceVpersmi Nathan White https://wakelet.com/wake/wn3DZvjTfCjA-nBhgBcd3
ReplyDeletediaperreno
planadtrinbi Kim Romero https://nl.cleanenergysolutions.es/profile/quigliesadiyeelvain/profile
ReplyDeletestochorileth
myrrven0is_ra Dana Jones Everest
ReplyDeleteInternet Download Manager
Vysor
nyatilimna