Invoice loses rows on join to gl_import_references

Robert of STAU

Hi, can anyone advise if this looks like a data corruption issue, or if there is an alternative way to do this.

I have used gl subledger data to create a complex data model for BIP for VAT reporting purposes but some isolated tax lines the tax lines do not appear in the report. Taking apart a more complex analysis I find the point of failure is at gl_import references such that if I run query 1 below I see the tax lines, but if I run query 2 then I lose the data which I would not expect. Note data is accounted and posted and tax data is also visible in summary view in the invoice workbench.

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

--, 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

Query 2 (additional lines to the above 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 = 'MyInvoiceNumber'

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

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 FA-ERP.

Please note that this Question is for Fusion Apps Cloud Service - ERP and it is managed in our sister community, which can be found at the below links:

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

Please make a note of the above URL and visit those Fusion Apps forums to submit ideas, receive support from our FA Cloud experts and check out the resources they have available for you.

Regards,

Sinduja