Oracle Fusion TDS Report SQL Query : SQL query to Extract TDS Information in Oracle Fusion

Oracle Fusion TDS Report SQL Query : SQL query to Extract TDS Information in Oracle Fusion

In this post , We will be discuss about Oracle Fusion TDS report SQL query. I will share the SQL query to extract the TDS Detail for each AP invoices in Oracle fusion. We can use this sql query to develop the TDS report in oracle fusion.  This will help to extract the complete AP invoice TDS Details in oracle fusion. We can get the TDS related details like , TDS Section code, TDS rate , TDS Amount using the SQL query in oracle fusion.

Oracle Fusion TDS Report SQL Query

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