TDS tables in oracle apps r12

TDS tables in oracle apps r12

In this post , We will be discuss about the TDS tables in oracle apps r12. Oracle application stores the Payables Invoice TDS tax amount information's in these TDS related tables. TDS tables help to stores the TDS amount deducted from the supplier against supplier invoices in oracle apps r12. TDS tables help to play the important role to find out the total tds amount which we have deducted from the supplier and need to pay back to the government. TDS tables also help to build the custom TDS reports in oracle application. Here below , i will share the detail TDS tables and the complete sql query using these tds tables in oracle apps.

Important TDS tables in oracle apps r12

1.jai_ap_tds_invoices

 2.ap_invoices_all

 3jai_tax_categories

 4.jai_cmn_taxes_all 

 5.jai_ap_tds_vendor_hdrs

 6.jai_tax_rates

 7.jai_tax_rate_details

Detail sql Query Using TDS tables in oracle apps r12 (

Oracle Apps SQL Query to Fetch GST TDS Calculation Report)

SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
            ap_inv_all.invoice_amount,
                               base_amt_tds tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
            tax_tds.gl_date tds_inv_gl_date,
            tax_tds.doc_sequence_value tds_voucher_no,
            tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, jit.tax_name,
            jit.tax_rate, jath.pan_no, jit.section_code,
            jath.tds_vendor_type_lookup_code, jit.section_type
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
            APPS.jai_cmn_taxes_all jit,
            APPS.jai_ap_tds_vendor_hdrs jath,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds
                        FROM APPS.jai_ap_tds_invoices jti, APPS.ap_invoices_all ap_inv_all
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND ap_inv_all.attribute1 IS NOT NULL
                         AND vendor_id = '2'
            ) tax_tds
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_ida.global_attribute1 IS NOT NULL
        AND ap_inv_all.org_id = ap_ida.org_id
        AND tax_tds.attribute1 = to_char(ap_inv_all.invoice_id)
        AND NVL (TO_CHAR (tax_tds.tds_tax_id), ap_ida.global_attribute1) =
                                                        ap_ida.global_attribute1
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jath.vendor_site_id = ap_inv_all.vendor_site_id
        AND jit.tax_id = ap_ida.global_attribute1
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            tax_tds.invoice_num,
            tax_tds.gl_date,
            tax_tds.doc_sequence_value,
            tax_tds.invoice_amount,
            tax_tds.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            jit.tax_name,
            jit.tax_rate,
            jath.pan_no,
            jit.section_code,
            jath.tds_vendor_type_lookup_code,
            jit.section_type
            ap_inv_all.org_id
   UNION ALL
   SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')) gl_date,
            ap_inv_all.invoice_amount,
                               base_amt_tds tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.line_type_lookup_code = 'ITEM'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute1) tax_id, tax_tds.invoice_num tds_inv_no,
           tax_tds.gl_date tds_inv_gl_date,
            tax_tds.doc_sequence_value tds_voucher_no,
            tax_tds.invoice_amount tds_amount, tax_tds.base_amt_tds, NULL tax_name,
            tax_tds.tax_rate, jpl.registration_number, tax_tds.section_code,
           (select REPORTING_CODE from  APPS.JAI_reporting_associations_v JRA,APPS.JAI_PARTY_REGS_V JPR
where REGIME_CODE in ('TDS','GST')
AND ENTITY_ID=PARTY_REG_ID
AND PARTY_ID=pov.vendor_id
AND ORG_ID=ap_inv_all.org_id
AND REPORTING_TYPE_NAME='Vendor Type'
AND EFFECTIVE_TO IS NULL
AND PARTY_SITE_ID=ap_inv_all.VENDOR_SITE_ID
AND ROWNUM=1)  tds_vendor_type_lookup_code, NULL section_code
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
            APPS.jai_party_reg_lines jpl,
            APPS.jai_party_regs jpr,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds,
                             jti.invoice_id tds_invoice_id,
                             jtrl.tax_rate_percentage tax_rate,
                             jti.tds_section section_code
                        FROM APPS.jai_ap_tds_invoices jti,
                             APPS.ap_invoices_all ap_inv_all,
                             APPS.jai_tax_categories jtc,

                             APPS.jai_tax_category_lines jtl,
                             APPS.jai_tax_rates jtr,
                             APPS.jai_tax_rate_details jtrl
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND jtc.tax_category_id = jtl.tax_category_id
                         AND jtc.tax_category_id = jti.tax_category_id
                         AND jtc.org_id = ap_inv_all.org_id
                         AND jtc.effective_to IS NULL
                         AND jtrl.effective_to IS NULL
                         AND jtrl.tax_rate_detail_code = 'RATE_DETAILS'
                         AND jtl.tax_rate_id = jtr.tax_rate_id
                         AND jtl.tax_type_id = jtr.tax_type_id
                         AND jtr.tax_rate_id = jtrl.tax_rate_id
                         AND vendor_id = '2'
            ) tax_tds
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_inv_all.org_id = ap_ida.org_id
        AND tax_tds.tds_invoice_id = ap_inv_all.invoice_id
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jpr.party_reg_id = jpl.party_reg_id
        AND ap_inv_all.org_id = jpr.org_id
        AND jpl.registration_type_code = 'PAN'
        AND jpl.effective_to IS NULL
        AND ap_inv_all.vendor_id = jpr.party_id
        AND ap_inv_all.vendor_site_id = jpr.party_site_id
        AND jpr.party_type_code = 'THIRD_PARTY_SITE'
        AND jpr.supplier_flag = 'Y'
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (tax_tds.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR'),
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            tax_tds.invoice_num,
            tax_tds.gl_date,
            tax_tds.doc_sequence_value,
            tax_tds.invoice_amount,
            tax_tds.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            tax_tds.tax_rate,
            jpl.registration_number,
            tax_tds.section_code,
            ap_inv_all.org_id,
            pov.vendor_id,
            ap_inv_all.VENDOR_SITE_ID
   UNION ALL
   SELECT   ap_inv_all.org_id, pov.vendor_name, hp.address1, hp.address2,
            hp.address3, hp.address4, hp.city, hp.postal_code, hp.state,
            pov.segment1, ap_inv_all.invoice_id, ap_inv_all.invoice_num, ap_inv_all.invoice_date,
            MAX (TO_DATE (ap_ida.accounting_date, 'DD-MON-RRRR')),
            ap_inv_all.invoice_amount,
            (SELECT SUM (aa.amount)
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code != 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) tax_on,
            (SELECT ABS (SUM (aa.amount))
               FROM APPS.ap_invoice_distributions_all aa
              WHERE 1 = 1
                AND ap_inv_all.invoice_id = aa.invoice_id
                AND aa.line_type_lookup_code = 'PREPAY'
                AND aa.global_attribute1 IS NOT NULL
                AND ap_inv_all.org_id = aa.org_id) prepay_on,
            MAX (ap_ida.global_attribute2) tax_id, wct.invoice_num tds_inv_no,
            wct.gl_date tds_inv_gl_date,
            wct.doc_sequence_value tds_voucher_no,
            wct.invoice_amount tds_amount, wct.base_amt_tds, NULL tax_name,
            jit.tax_rate, jath.pan_no, jit.section_code,
            jath.tds_vendor_type_lookup_code, jit.section_type
       FROM APPS.ap_invoices_all ap_inv_all,
            APPS.ap_invoice_distributions_all ap_ida,
            APPS.hz_parties hp,
            APPS.fnd_user fu,
            APPS.po_vendors pov,
--            PO_VENDOR_SITES_ALL POVS,
            APPS.jai_cmn_taxes_all jit,
            APPS.jai_ap_tds_vendor_hdrs jath,
            (SELECT DISTINCT ap_inv_all.attribute1, ap_inv_all.invoice_num,
                             ap_inv_all.doc_sequence_value, ap_inv_all.invoice_amount,
                             ap_inv_all.gl_date, ap_inv_all.invoice_id, jti.tds_tax_id,
                             jti.invoice_amount base_amt_tds
                        FROM APPS.jai_ap_tds_invoices jti,APPS.ap_invoices_all ap_inv_all
                       WHERE jti.tds_invoice_num(+) = ap_inv_all.invoice_num
                         AND ap_inv_all.attribute1 IS NOT NULL
                         AND vendor_id = '2'
            ) wct
      WHERE 1 = 1
        AND ap_inv_all.invoice_id = ap_ida.invoice_id
        AND ap_ida.line_type_lookup_code != 'PREPAY'
        AND ap_ida.global_attribute2 IS NOT NULL
        AND ap_inv_all.org_id = ap_ida.org_id
        AND wct.attribute1 = to_char(ap_inv_all.invoice_id)
        AND NVL (TO_CHAR (wct.tds_tax_id), ap_ida.global_attribute1) =
                                                        ap_ida.global_attribute1
        AND pov.vendor_id = ap_inv_all.vendor_id
        AND fu.user_id = ap_inv_all.created_by
        AND pov.party_id = hp.party_id
        AND ap_inv_all.org_id = ap_ida.org_id
        AND ap_inv_all.cancelled_date IS NULL
        AND jath.vendor_site_id = ap_inv_all.vendor_site_id
        AND jit.tax_id = ap_ida.global_attribute2
        AND ap_ida.match_status_flag = 'A'
        AND UPPER (wct.invoice_num) NOT LIKE '%RTN%'
   GROUP BY ap_inv_all.invoice_id,
            ap_inv_all.invoice_num,
            ap_inv_all.invoice_date,
            ap_inv_all.invoice_amount,
            ap_ida.global_attribute1,
            wct.invoice_num,
            wct.gl_date,
            wct.doc_sequence_value,
            wct.invoice_amount,
            wct.base_amt_tds,
            pov.vendor_name,
            hp.address1,
            hp.address2,
            hp.address3,
            hp.address4,
            hp.city,
            hp.postal_code,
            hp.state,
            pov.segment1,
            jit.tax_name,
            jit.tax_rate,
            jath.pan_no,
            jit.section_code,
            jath.tds_vendor_type_lookup_code,
            jit.section_type,
            ap_inv_all.org_id