SQL Query to get tax details in Oracle Fusion

SQL Query to get tax details in Oracle Fusion

Hi friends, we are going to discuss about the SQL Query to get tax details in Oracle Fusion. We will share the complete sql query to extract the Tax Master details as well the oracle fusion transactional taxes too. This is one of the important sql query using this we can find out the Payables and receivables transaction taxes. We will also try to share the Important tables too which helps to store the Oracle Fusion taxes details. These tax details queries are very important if you need to develop the tax report in oracle fusion. In Oracle fusion tax tables are completely changed as compared to Oracle Apps ebs taxes tables. Please find below the complete detail about SQL Query to get tax details in Oracle Fusion.

SQL Query to get tax details in Oracle Fusion

Detail SQL Query to get tax details in Oracle Fusion

Query 1:- For Customer and Supplier Tax registrations.

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

Query 2:-  For AR Invoice applied tax details.

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  --- AR INVOICE ID------

   --and zl.LINE_TYPE='LINE'

   and zl.rounding_level_code = 'LINE'

Query 3:- For AP Invoice applied tax details.

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

5 Most Important Tables to get the tax details in Oracle Fusion

1.ZX_REGISTRATIONS

2.ZX_LINES_DET_FACTORS

3.ZX_RATES_B

4.ZX_PARTY_TAX_PROFILE

5.ZX_LINES

Thanks !!

SQL Query to get tax details in Oracle Fusion

SQL Query to get tax details in Oracle Fusion