AP New TDS Query - Oracle Apps

AP TDS Query :-

This is new AP TDS query for new GST Regime :-

 SELECT   vendor_num, supplier_invoice_no, gl_date,
                  tds_auth_inv_gl_date, tds_inv_amt, tds_section,
                  default_tds_section, tds_tax_code, inv_doc_num,
                  supp_inv_date, supp_cm_inv_num, supp_cm_no_date,
                  supp_gl_date, description, vendor_site, vendor_name,
                  tds_auth_ven_name, tds_auth_ven_site, tds_auth_ven_no,
                  tds_auth_inv_date, tds_tax_name, tds_tax_rate, supp_pan_no,
                  currency, supp_hdr_inv_amt,sup_tax_inv_no,
                  SUM (supp_line_amt) supp_line_amt,
                  SUM (total_tds_cm_amt) total_tds_cm_amt,
                  SUM (tds_std_inv_amt) tds_std_inv_amt,
                  SUM (net_pay_inv_amt) net_pay_inv_amt,
                  CASE
                     WHEN tds_pay_date IS NULL
                     AND tds_prepay_date IS NULL
                        THEN NULL
                     WHEN tds_pay_date IS NOT NULL AND tds_prepay_date IS NULL
                        THEN TO_CHAR (tds_pay_date, 'DD/MM/YYYY')
                     WHEN tds_pay_date IS NULL AND tds_prepay_date IS NOT NULL
                        THEN TO_CHAR (tds_prepay_date, 'DD/MM/YYYY')
                     WHEN tds_pay_date IS NOT NULL
                     AND tds_prepay_date IS NOT NULL
                        THEN    TO_CHAR (tds_pay_date, 'DD/MM/YYYY')
                             || ' - '
                             || TO_CHAR (tds_prepay_date, 'DD/MM/YYYY')
                  END AS tds_pay_date,
                  std_ven_site_code,
                  SUM (tds_std_inv_unp_paid) tds_std_inv_unp_paid, status,
                 vendor_type,
                  gl_account_tds, gl_account_dm
             FROM
             (SELECT   aid.invoice_distribution_id,
         pv.segment1 vendor_num,
         ai.invoice_num supplier_invoice_no,
         TO_CHAR (ai.gl_date, 'DD/MM/YYYY') gl_date,
         TO_CHAR (ai_tds.gl_date, 'DD/MM/YYYY') tds_auth_inv_gl_date,
         (ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1)) tds_inv_amt,
         jti.tds_section tds_section,
         ( SELECT   jprl.DEFAULT_SECTION_CODE
            FROM   apps.jai_party_regs jpr, apps.jai_party_reg_lines jprl
           WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID
                   AND jpr.party_id = pv.vendor_id
                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE
                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE
                   AND REGISTRATION_TYPE_CODE IN ('PAN')
                   AND jpr.party_type_code IN ('THIRD_PARTY')
                   and rownum=1)default_tds_section,
         jai.ACTUAL_TAX_ID tds_tax_code,
         ai.doc_sequence_value inv_doc_num,
         TO_CHAR (ai.invoice_date, 'DD/MM/YYYY') supp_inv_date,
         jti.dm_invoice_num supp_cm_inv_num,
         TO_CHAR (ai_dm.invoice_date, 'DD/MM/YYYY') supp_cm_no_date,
         TO_CHAR (ai_dm.gl_date, 'DD/MM/YYYY') supp_gl_date,
         ai.description description,
         (SELECT   vendor_site_code
            FROM   apps.po_vendor_sites_all
           WHERE   vendor_site_id = ai.vendor_site_id AND ROWNUM = 1)
            vendor_site,
         pv.vendor_name vendor_name,
         pv_tds.vendor_name tds_auth_ven_name,
         (SELECT   vendor_site_code
            FROM   apps.po_vendor_sites_all
           WHERE   vendor_site_id = ai_tds.vendor_site_id AND ROWNUM = 1)
            tds_auth_ven_site,
         jti.tds_invoice_num tds_auth_ven_no,
         TO_CHAR (ai_tds.invoice_date, 'DD/MM/YYYY') tds_auth_inv_date,
         (SELECT   jtr.tax_rate_name
            FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
           WHERE       jtr.tax_rate_id = jtrd.tax_rate_id
                   AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                   AND TRUNC (jtrd.effective_from) <= aid.accounting_date
                   AND (aid.accounting_date) <=
                         NVL (jtrd.effective_to, aid.accounting_date))
            tds_tax_name,
         (SELECT   jtrd.tax_rate_percentage
            FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
           WHERE       jtr.tax_rate_id = jtrd.tax_rate_id
                   AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                   AND TRUNC (jtrd.effective_from) <= aid.accounting_date
                   AND (aid.accounting_date) <=
                         NVL (jtrd.effective_to, aid.accounting_date))
            tds_tax_rate,
           ( SELECT   jprl.REGISTRATION_NUMBER
            FROM   jai_party_regs jpr, jai_party_reg_lines jprl
           WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID
                   AND jpr.party_id = pv.vendor_id
                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE
                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE
                   AND REGISTRATION_TYPE_CODE IN ('PAN')
                   AND jpr.party_type_code IN ('THIRD_PARTY')
                   and rownum=1) supp_pan_no,
         ai_tds.invoice_currency_code currency,
         (ai.invoice_amount * NVL (ai.exchange_rate, 1)) supp_hdr_inv_amt,
         ( (aid.amount) * NVL (                          /*aid.exchange_rate*/
                               ai.exchange_rate, 1))
            supp_line_amt,
         - (SELECT   CASE
                        WHEN jtrd.tax_rate_percentage <> 0
                        THEN
                           ( ( (aid.amount) * NVL (/*aid.exchange_rate*/
                                                   ai.exchange_rate, 1))
                            * jtrd.tax_rate_percentage)
                           / 100
                        WHEN jtrd.tax_rate_percentage = 0
                        THEN
                           0
                     END
                        AS tds_tax
              FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
             WHERE       jtr.tax_rate_id = jtrd.tax_rate_id
                     AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                     AND TRUNC (jtrd.effective_from) <= aid.accounting_date
                     AND (aid.accounting_date) <=
                           NVL (jtrd.effective_to, aid.accounting_date))
            total_tds_cm_amt,
         (SELECT   CASE
                      WHEN jtrd.tax_rate_percentage <> 0
                      THEN
                         ( (aid.amount * NVL (/*aid.exchange_rate*/
                                              ai.exchange_rate, 1))
                          * jtrd.tax_rate_percentage)
                         / 100
                      WHEN jtrd.tax_rate_percentage = 0
                      THEN
                         0
                   END
                      AS tds_tax
            FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
           WHERE       jtr.tax_rate_id = jtrd.tax_rate_id
                   AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                   AND TRUNC (jtrd.effective_from) <= aid.accounting_date
                   AND (aid.accounting_date) <=
                         NVL (jtrd.effective_to, aid.accounting_date))
            tds_std_inv_amt,
         CASE
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <>
                    (ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
            THEN
               0
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) =
                    (ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
            THEN
               (SELECT   CASE
                            WHEN jtrd.tax_rate_percentage <> 0
                            THEN
                               ( (aid.amount * NVL (/*aid.exchange_rate*/
                                                    ai.exchange_rate, 1))
                                * jtrd.tax_rate_percentage)
                               / 100
                            WHEN jtrd.tax_rate_percentage = 0
                            THEN
                               0
                         END
                            AS tds_tax
                  FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
                 WHERE   jtr.tax_rate_id = jtrd.tax_rate_id
                         AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                         AND TRUNC (jtrd.effective_from) <=
                               aid.accounting_date
                         AND (aid.accounting_date) <=
                               NVL (jtrd.effective_to, aid.accounting_date))
         END
            AS net_pay_inv_amt,
         CASE
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <> 0
            THEN
               (SELECT   DISTINCT TRUNC (MAX (saipa.accounting_date))
                  FROM   ap_invoice_payments_all saipa
                 WHERE   saipa.invoice_id = ai_tds.invoice_id)
            WHEN ai_tds.amount_paid = 0
            THEN
               NULL
         END
            AS tds_pay_date,
         CASE
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <> 0
            THEN
               (SELECT   DISTINCT TRUNC (MAX (saia_prepay.gl_date))
                  FROM   apps.ap_invoice_distributions_all saida,
                         apps.ap_invoice_distributions_all saida_prepay,
                         ap_invoices_all saia_prepay
                 WHERE       saida.invoice_id = ai_tds.invoice_id
                         AND saida.line_type_lookup_code = 'PREPAY'
                         AND NVL (saida.reversal_flag, 'N') != 'Y'
                         AND saida_prepay.invoice_distribution_id =
                               saida.prepay_distribution_id
                         AND saia_prepay.invoice_id = saida_prepay.invoice_id)
            WHEN ai_tds.amount_paid = 0
            THEN
               NULL
         END
            AS tds_prepay_date,
         (SELECT   vendor_site_code
            FROM   apps.po_vendor_sites_all
           WHERE   vendor_site_id = ai.vendor_site_id)
            std_ven_site_code,
         CASE
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) =
                    (ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
            THEN
               0
            WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <>
                    (ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
            THEN
               (SELECT   CASE
                            WHEN jtrd.tax_rate_percentage <> 0
                            THEN
                               ( (aid.amount * NVL (/*aid.exchange_rate*/
                                                    ai.exchange_rate, 1))
                                * jtrd.tax_rate_percentage)
                               / 100
                            WHEN jtrd.tax_rate_percentage = 0
                            THEN
                               0
                         END
                            AS tds_tax
                  FROM   jai_tax_rates jtr, jai_tax_rate_details jtrd
                 WHERE   jtr.tax_rate_id = jtrd.tax_rate_id
                         AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
                         AND TRUNC (jtrd.effective_from) <=
                               aid.accounting_date
                         AND (aid.accounting_date) <=
                               NVL (jtrd.effective_to, aid.accounting_date))
         END
            AS tds_std_inv_unp_paid,
         DECODE (ai_tds.amount_paid, 0, 'NOT PAID', 'PAID') status,
         ai.attribute1 sup_tax_inv_no,
         (   gcc_tds.segment1
          || '.'
          || gcc_tds.segment2
          || '.'
          || gcc_tds.segment3
          || '.'
          || gcc_tds.segment4
          || '.'
          || gcc_tds.segment5
          || '.'
          || gcc_tds.segment6
          || '.'
          || gcc_tds.segment7)
            gl_account_tds,
              (   gcc_dm.segment1
          || '.'
          || gcc_dm.segment2
          || '.'
          || gcc_dm.segment3
          || '.'
          || gcc_dm.segment4
          || '.'
          || gcc_dm.segment5
          || '.'
          || gcc_dm.segment6
          || '.'
          || gcc_dm.segment7)
            gl_account_dm,
          pv.VENDOR_TYPE_LOOKUP_CODE vendor_type
  FROM   apps.ap_invoices_all ai_tds,
         apps.ap_invoice_distributions_all aid_tds,
         apps.po_vendors pv_tds,
         apps.JAI_AP_TDS_INVOICES jti,
         apps.JAI_AP_TDS_THHOLD_TRXS TRSH,
         apps.ap_invoices_all ai,
         apps.ap_invoice_distributions_all aid,
         apps.po_vendors pv,
         apps.JAI_AP_WTHLD_INV_TAXES jai,
         apps.ap_invoices_all ai_dm,
         apps.ap_invoice_distributions_all aid_dm,
         apps.gl_code_combinations_kfv gcc_tds,
         apps.gl_code_combinations_kfv gcc_dm
 WHERE       1 = 1
         --AND jti.invoice_id = 3078412
      --   and ai_tds.invoice_num='37-TDS-SI-253737'
         AND ai_tds.cancelled_date IS NULL
         AND jti.tds_invoice_num = ai_tds.invoice_num
         and ai_tds.invoice_id=aid_tds.invoice_id
         AND pv_tds.vendor_id = ai_tds.vendor_id
         AND ai.invoice_id = jti.invoice_id
            and  ai.invoice_id = TRSH.invoice_id
         and jti.tds_invoice_num=TRSH.INVOICE_TO_TDS_AUTHORITY_NUM
         AND ai.invoice_num NOT LIKE '%ESIC%'
         AND ai.invoice_id = aid.invoice_id
         AND aid.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS'
         AND AID.LINE_TYPE_LOOKUP_CODE NOT IN
                  ('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
         AND AID.AMOUNT > 0
         AND NVL (AID.REVERSAL_FLAG, 'N') <> 'Y'
         AND NOT EXISTS
               (SELECT   '1'
                  FROM   AP_INVOICES_ALL
                 WHERE   INVOICE_ID = AID.INVOICE_ID AND SOURCE = 'INDIA TDS')
         AND jai.invoice_id = ai.invoice_id
         AND aid.INVOICE_DISTRIBUTION_ID = jai.INVOICE_DISTRIBUTION_ID
         AND pv.vendor_id = ai.vendor_id
         and TRSH.Threshold_Trx_Id=jai.Threshold_Trx_Id
         AND ai_dm.invoice_num = jti.dm_invoice_num
         and ai_dm.invoice_id=aid_dm.invoice_id
         AND ai_dm.org_id = ai_tds.org_id
         AND gcc_tds.code_combination_id = aid_tds.dist_code_combination_id

          AND gcc_dm.code_combination_id = aid_dm.dist_code_combination_id);