Thursday, April 5, 2012

Queries to Check All SL Entries for an AP Invoices


--XLA_EVENTS
SELECT 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 = '188101'
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;


--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 = '188101'
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;

--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 = '188101'
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 = '188101'
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 XLA_EVENTS
SELECT 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 = '188101'
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;


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 = '188101'
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;

--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 = '188101'
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 = '188101'
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 = '188101'
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 = '188101'
AND xe.event_id = xae.event_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xe.entity_id;
SELECT DISTINCT xte.*
FROM ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND ai.invoice_id = '188101'
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 = '188101'
AND xe.event_id = xae.event_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id

AND xte.entity_id = xe.entity_id;

1 comment:

Ravi Maram said...

How AR invoice information will store in XLA tables ? is it same as AP invoice ?
entity_code will be AR INVOICES and invoice_id will be AR TRX id ?

correct me if am wrong

Post a Comment

Best Blogger TipsGet Flower Effect