Accounted VAT query

Robert of STAU

Hi,

I have a data model which works for the majority of Accounts Payable Invoices in Fusion Financials, but I have isolated tax lines which do not appear. Taking my more complex down to its basics I find that I lose tax lines when I try to join to gl_import_references, so query 1 shows the tax lines that I expect but query 2 does not.

Can anyone tell me if this is indicative of a problem with the AP subledger transfer and / or if there is an alternative way to get the data for the tax lines with gl code combinations.

Query 1

select xdl.*

from ap_invoices_all inv

, ap_invoice_distributions_all dis

, XLA_DISTRIBUTION_LINKS XDL

, XLA_AE_LINES XAL

, XLA_AE_HEADERS XAH

, XLA_TRANSACTION_ENTITIES XTE

where inv.invoice_id = dis.invoice_id

and inv.invoice_num = 'Test_138_Credit Memo'

and dis.invoice_distribution_id = xdl.SOURCE_DISTRIBUTION_ID_NUM_1

AND XAL.ae_line_num = XDL.ae_line_num

AND XAL.application_id = XDL.application_id

AND XAL.ae_header_id = XDL.ae_header_id

AND XAL.ae_line_num = XDL.ae_line_num

AND XAL.application_id = XDL.application_id

AND XAH.ae_header_id = XAL.ae_header_id

AND XAH.application_id = XAL.application_id

AND XTE.entity_id = XAH.entity_id

Query 2 - additional lines that cause the data loss in bold

select xdl.*

from ap_invoices_all inv

, ap_invoice_distributions_all dis

, XLA_DISTRIBUTION_LINKS XDL

, XLA_AE_LINES XAL

, XLA_AE_HEADERS XAH

, XLA_TRANSACTION_ENTITIES XTE

, gl_import_references GIR

where inv.invoice_id = dis.invoice_id

and inv.invoice_num = 'Test_138_Credit Memo'

and dis.invoice_distribution_id = xdl.SOURCE_DISTRIBUTION_ID_NUM_1

AND XAL.ae_line_num = XDL.ae_line_num

AND XAL.application_id = XDL.application_id

AND XAL.ae_header_id = XDL.ae_header_id

AND XAL.ae_line_num = XDL.ae_line_num

AND XAL.application_id = XDL.application_id

AND XAH.ae_header_id = XAL.ae_header_id

AND XAH.application_id = XAL.application_id

AND XTE.entity_id = XAH.entity_id

AND GIR.gl_sl_link_id = XAL.gl_sl_link_id

AND GIR.gl_sl_link_table = XAL.gl_sl_link_table

Note - please do not suggest an outer join, I need to join to gl_je_lines etc to ensure that my taxation entries are posted into general ledger.

Sinduja Sekar-Oracle

Hi @Robert of STAU ,

Thank you for visiting and posting in Fusion Analytics Warehouse (FAW) Product Community Page.

This forum is for FAW product and looks like your question is for Fusion Applications - BI Publisher.

Kindly use the below URLs to post Fusion Apps / BIP questions, submit ideas, receive support from our experts and check out the resources they have available for you.

Oracle Cloud Customer Connect ( https://community.oracle.com/customerconnect ).

https://community.oracle.com/products/oracleanalytics/categories/bi-publisher

Regards,

Sinduja