Fusion ERP Analytics

Products Banner

Accounted VAT query

Received Response
193
Views
1
Comments

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.

Answers