ZX tables in oracle fusion

ZX tables in oracle fusion

In this post , We will be discuss about the ZX tables in oracle fusion. ZX tables basically belongs the tax related tables in oracle fusion which do store the different types of Tax related information in oracle fusion application. I will try to explain most commonly used ZX tables which we do commonly uses in our day to day queries and in our custom reports to extract the taxation information's in oracle fusion. First i will try to share these ZX tables list and then i will explain each of them like what information's they do store and how we do join these tables with each other.

5 Most Important ZX tables in oracle fusion

1.ZX_REGISTRATIONS

2.ZX_PARTY_TAX_PROFILE

3.ZX_LINES

4.ZX_LINES_DET_FACTORS

5.ZX_RATES_B

Detail Explanation of ZX tables in Oracle Fusion 

ZX_REGISTRATIONS :- 

This table help to store the Supplier and Customers Tax related information like Tax Registration Number Like PAN , GST and Other Unique Tax related registrations information's.

ZX_PARTY_TAX_PROFILE :- 

This table also helps to store the Supplier and Customers Tax Registration information's.

ZX_LINES :- 

This is one of the most important tables , which do stores the Transaction tax amounts in oracle fusion. If we have applied any tax in the AR Invoices or AP invoices , then this table helps to store the tax amount calculated for each invoice lines for both AP and AR.

ZX_RATES_B:- 

This table stores tax rates and the related attributes for multiple configuration owners. Each row stores a rate that is effective for a specific period and is applicable to a specific tax status or tax jurisdiction.

ZX_LINES_DET_FACTORS :-

This table stores Lines Determining Factors.

Important Joins to connect ZX tables in oracle Fusion

1. To Extract the customer and Supplier Tax registrations we need to use this below SQL join

 (SELECT A1.REGISTRATION_NUMBER FROM zx_registrations A1,ZX_PARTY_TAX_PROFILE A2

WHERE A1.TAX_REGIME_CODE='GST'

AND A1.EFFECTIVE_TO IS NULL

AND ROWNUM=1

AND A1.PARTY_TAX_PROFILE_ID=A2.PARTY_TAX_PROFILE_ID

AND A2.party_id=hps.party_site_id) 

2. To extract the Tax amount calculated for each AR invoice , we can use this below join.

 select sum(nvl(zl.UNROUNDED_TAX_AMT,0)) 

  from zx_lines zl

where --zl.CUSTOMER_TRX_ID=tr.customer_trx_id

       zl.TRX_ID=tr.customer_trx_id

   --and zl.LINE_TYPE='LINE'

   and zl.rounding_level_code = 'LINE'

3. To extract the Tax amount calculated for each AP invoice , we can use this below join.

select sum(nvl(zl.UNROUNDED_TAX_AMT,0)) 

zx_lines_det_factors jtdfl,

        zx_lines zl,

ap_invoices_all aia

             AND jtdfl.TRX_LINE_ID=zl.TRX_LINE_ID

         AND aia.CANCELLED_DATE IS NULL

         AND aia.invoice_id=jtdfl.trx_id

            AND aia.invoice_id=zl.trx_id