Withholding Tax Tables in Oracle Fusion

Withholding Tax Tables in Oracle Fusion

Hi friends, we are going to discuss about the Withholding Tax Tables in Oracle Fusion. We will share some of the important tax tables which helps to store the withholding tax details in oracle fusion. In Oracle Fusion, withholding tax tables is quite different as compared to withholding tax tables in oracle apps r12. We will share all these tables list and the detail sql query which helps to extract the complete withholding transaction tax details in oracle fusion. Using these Withholding tax tables , you can develop the custom withholding BIP reports in oracle fusion. Here below is the complete detail about Withholding Tax Tables in Oracle Fusion. 

Withholding Tax Tables in Oracle Fusion

8 Most Important Withholding Tax tables in Fusion.

Here below is the some of the important tables helps to develop the withholding report in oracle fusion.

1.POZ_SUPPLIERS_V 
2.POZ_SUPPLIER_SITES_V 
3.AP_INVOICES_ALL 
4.AP_INVOICE_DISTRIBUTIONS_ALL
5.ZX_RATES_B 
6.ZX_TAXES_VL 
7.ZX_PARTY_TAX_PROFILE 
8.fun_all_business_units_v 

Complete SQL Query to develop the Withholding Tax Reports in Oracle Fusion

Here below is the detail sql query for Withholding tax report in oracle fusion.

SELECT
SUPPLIER_NAME as "Vendor Name",
INVOICE_NUM as "Invoice Number",
(INVOICE_AMOUNT*EXCHANGE_RATE) as "Invoice Amount",
to_char(INVOICE_DATE,'DD-MON-YYYY') as "Invoice Date",
to_char(AWT_GL_DATE,'DD-MON-YYYY') as "GL Date",
INVOICE_NUM as "TDS Invoice Number",
AWT_CODE as "Section Code",
tax_name,
SUPPLIER_TYPE as "Vendor Type",
REP_REGISTRATION_NUMBER as "PAN",
AWT_RATE as "Tax Rate",
INVOICE_CURRENCY_CODE   INV_CURRENCY,
sum(nvl((AWT_AMOUNT*EXCHANGE_RATE),0)) as "TDS Amount",
SUM((nvl((AWT_AMOUNT),0)*NVL(decode(awt_rate_new,NULL,100,0,100,awt_rate_new),0))*EXCHANGE_RATE) PAYABLE_AMOUNT,
sum(nvl(prepay_on,0)) prepay_on,
sum(DECODE(prepay_on,0,0,0)) PREPAY_TAX_AMT
FROM (
     SELECT
          PV2.VENDOR_TYPE_LOOKUP_CODE                 SUPPLIER_TYPE,
          PV2.VENDOR_NAME                             SUPPLIER_NAME,
          PV2.SEGMENT1                                SUPPLIER_NUMBER,
          PVS.VENDOR_SITE_CODE                        SUPPLIER_SITE_CODE,
          PVS.ADDRESS_LINE1                           SUPPLIER_ADDRESS_LINE1,
          PVS.ADDRESS_LINE2                           SUPPLIER_ADDRESS_LINE2,
          PVS.ADDRESS_LINE3                           SUPPLIER_ADDRESS_LINE3,
          PVS.CITY                                    SUPPLIER_CITY,
          PVS.STATE                                   SUPPLIER_STATE,
          PVS.ZIP                                     SUPPLIER_ZIP,
          PVS.PROVINCE                                SUPPLIER_PROVINCE,
          PVS.COUNTRY                                 SUPPLIER_COUNTRY,
          AI.INVOICE_NUM                              INVOICE_NUM,
          AI.INVOICE_AMOUNT                           INVOICE_AMOUNT,
          DECODE(AI.EXCHANGE_RATE,NULL,1,AI.EXCHANGE_RATE)  EXCHANGE_RATE,
          AI.INVOICE_CURRENCY_CODE                    INVOICE_CURRENCY_CODE,
          trunc(AI.INVOICE_DATE)                      INVOICE_DATE,
          ZTV.TAX_FULL_NAME                           AWT_CODE,
          zrb.tax                                       tax_name,
          100/(SELECT ZWL.TAX_RATE
          from ZX_WITHHOLDING_LINES zwl
          WHERE AID.DETAIL_TAX_LINE_ID = ZWL.TAX_LINE_ID) awt_rate_new,
          (SELECT ZWL.TAX_RATE
          from ZX_WITHHOLDING_LINES zwl
          WHERE AID.DETAIL_TAX_LINE_ID = ZWL.TAX_LINE_ID) AWT_RATE,
          ---------
          (SELECT ABS (SUM (aa.amount)) prepay_amt
               FROM ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ai.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND ai.org_id = aa.org_id) prepay_on,
          ---------
          NVL(AID.AMOUNT*(-1),0)                      AWT_AMOUNT,
          NVL(AID.BASE_AMOUNT*(-1),AID.AMOUNT*(-1))   AWT_BASE_AMOUNT,
          NULL                                        AWT_GROUP_NAME,
          trunc(AID.ACCOUNTING_DATE)                  AWT_GL_DATE,
          AID.awt_gross_amount                        AMOUNT_SUBJECT_TO_TAX,
          zptp.REP_REGISTRATION_NUMBER,
          fabuv.bu_name

    FROM
          POZ_SUPPLIERS_V PV2,
          POZ_SUPPLIER_SITES_V PVS,
          AP_INVOICES_ALL AI,
          AP_INVOICE_DISTRIBUTIONS_ALL AID,
          ZX_RATES_B zrb,
          ZX_TAXES_VL ztv,
          ZX_PARTY_TAX_PROFILE zptp,
          fun_all_business_units_v fabuv
    WHERE 1=1
       and AI.ORG_ID = :P_ORG_ID
    AND   PV2.VENDOR_ID = AI.VENDOR_ID
    AND   PVS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID
    AND   AI.INVOICE_ID = AID.INVOICE_ID
    AND   AID.LINE_TYPE_LOOKUP_CODE='AWT'
    AND   (AID.withholding_tax_code_id = zrb.tax_rate_id
           OR
           (AID.withholding_tax_code_id = zrb.source_id 
            AND zrb.effective_from <= AID.accounting_date       
            AND NVL(zrb.effective_to, AID.accounting_date)
                >= AID.accounting_date
           )
          )
    AND   ztv.tax_regime_code = zrb.tax_regime_code 
    AND   ztv.tax = zrb.tax
    AND   ztv.content_owner_id = zrb.content_owner_id
    AND   PV2.party_id=zptp.party_id(+)
    and   fabuv.bu_id=AI.org_id
   and   AID.ACCOUNTING_DATE >= :P_FROM_DATE
    and   AID.ACCOUNTING_DATE <= :P_TO_DATE
      )
      group by
     SUPPLIER_NAME ,
INVOICE_NUM,
(INVOICE_AMOUNT*EXCHANGE_RATE) ,
to_char(INVOICE_DATE,'DD-MON-YYYY'),
to_char(AWT_GL_DATE,'DD-MON-YYYY'),
INVOICE_NUM ,
AWT_CODE,
tax_name,
SUPPLIER_TYPE,
REP_REGISTRATION_NUMBER,
AWT_RATE ,
INVOICE_CURRENCY_CODE

Withholding Tax Tables in Oracle Fusion