Tax Tables in oracle fusion

Tax Tables in oracle fusion

In this post , We will be discuss about the Tax tables in oracle fusion. TAX tables basically belongs the tax related tables in oracle fusion which do store the different types of Tax related information in oracle fusion . I will try to explain most commonly used Oracle Fusion tax tables which we do commonly uses to extract the taxation information's in oracle fusion. First i will try to share these Tax tables in Oracle fusion and all information's about tables like what they do store and how we do join these tables with each other.

5 Most Important Tax tables in oracle fusion

1.ZX_REGISTRATIONS

2.ZX_PARTY_TAX_PROFILE

3.ZX_LINES

4.ZX_LINES_DET_FACTORS

5.ZX_RATES_B

Information about Tax Tables in oracle fusion

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_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_DET_FACTORS :-

This table stores Lines Determining Factors.

Important Joins to connect Tax tables in oracle Fusion

1. 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

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