TDS Register Report

Below query will display all TDS related details for GST implementations

SELECT aia.invoice_date,
  aia.doc_sequence_value voucher_num,
  aia.invoice_num,
  aia.invoice_amount,
  aps.vendor_name party_name,
  (SELECT t.description
  FROM apps.ap_invoice_distributions_all apd,
    apps.gl_code_combinations gcc,
    apps.fnd_flex_values v,
    apps.fnd_flex_value_sets s,
    apps.fnd_flex_values_tl t
  WHERE apd.invoice_id             = aia.invoice_id
  AND apd.dist_code_combination_id = gcc.code_combination_id
  AND apd.line_type_lookup_code    = 'ITEM'
  AND gcc.segment2                 = v.flex_value
  AND s.flex_value_set_id          = v.flex_value_set_id
  AND t.flex_value_id              = v.flex_value_id
  AND v.enabled_flag               = 'Y'
  AND v.summary_flag               = 'N'
  AND flex_value_set_name LIKE 'KJIPL_ACCOUNT_VALUESET'
  AND ROWNUM = 1
  ) expense_account,
  jgrp.section_code tds_section,
  DECODE(jtrx.tax_rate,NULL,
  (SELECT Distinct jtrd.tax_rate_percentage
  FROM apps.jai_ap_wthld_inv_taxes jaw,
    apps.jai_tax_rates jtr,
    apps.jai_tax_rate_details jtrd
  WHERE jaw.actual_tax_id=jtr.tax_rate_id
  AND jtr.tax_rate_id    =jtrd.tax_rate_id
  AND jaw.invoice_id     =aia.invoice_id
  ),jtrx.tax_rate) tds_percentage,
  DECODE(
  (SELECT SUM (aia1.invoice_amount) FROM apps.ap_invoices_all aia1,
    apps.ap_suppliers asp1 WHERE aia1.vendor_id = asp1.vendor_id
  AND UPPER(asp1.vendor_name)                   = UPPER('Income Tax Authority')
  AND aia1.attribute1                           = TO_CHAR(aia.invoice_id)
  ) ,NULL,
  (Select sum(tds_amount)
From apps.jai_ap_tds_invoices
where invoice_id=aia.invoice_id),
  (SELECT SUM (aia1.invoice_amount)
  FROM apps.ap_invoices_all aia1,
    apps.ap_suppliers asp1
  WHERE aia1.vendor_id        = asp1.vendor_id
  AND UPPER(asp1.vendor_name) = UPPER('Income Tax Authority')
  AND aia1.attribute1         = TO_CHAR(aia.invoice_id)
  )) tds_amount,
  (SELECT jatt.pan_no
  FROM apps.jai_ap_tds_th_vsite_v jatt
  WHERE jatt.vendor_id    = aia.vendor_id
  AND jatt.vendor_site_id = aia.vendor_site_id
  AND ROWNUM              = 1
  ) pan_number,
  0 int_per,
  (Select sum(tds_amount)
From apps.jai_ap_tds_invoices
where invoice_id=aia.invoice_id) tds_payable,
  aia.vendor_id,
  aia.vendor_site_id,
  (SELECT t.description
  FROM apps.ap_invoice_distributions_all apd,
    apps.gl_code_combinations gcc,
    apps.fnd_flex_values v,
    apps.fnd_flex_value_sets s,
    apps.fnd_flex_values_tl t
  WHERE apd.invoice_id             = aia.invoice_id
  AND apd.dist_code_combination_id = gcc.code_combination_id
  AND apd.line_type_lookup_code    = 'ITEM'
  AND gcc.segment1                 = v.flex_value
  AND s.flex_value_set_id          = v.flex_value_set_id
  AND t.flex_value_id              = v.flex_value_id
  AND v.enabled_flag               = 'Y'
  AND v.summary_flag               = 'N'
  AND flex_value_set_name LIKE 'KJIPL_SHOWROOM_VALUESET'
  AND ROWNUM = 1
  ) LOCATION,
  aia.invoice_id,
  xxv.bank_name bank_name,
  xxv.check_number check_number,
  xxv.challan_num challan_num,
  xxv.challan_date challan_date, 
  xxv.bsr_code bsr_code
FROM apps.ap_invoices_all aia,
  apps.ap_suppliers aps,
  apps.jai_ap_tds_thhold_trxs jtrx,
  apps.jai_ap_tds_thhold_grps jgrp,
  apps.xxkly_tds_report_v xxv
WHERE 1                      = 1
AND UPPER (aps.vendor_name) <> UPPER ('Income Tax Authority')
AND aia.vendor_id            = aps.vendor_id
AND ( aia.invoice_id         = jtrx.invoice_to_tds_authority_id
OR aia.invoice_id            = jtrx.invoice_id )
AND jtrx.threshold_grp_id    = jgrp.threshold_grp_id
AND xxv.tds_invoice_id(+)    = aia.invoice_id
AND aps.vendor_id            = NVL (:p_party_name, aps.vendor_id)
AND jgrp.section_code        = NVL (:p_tds_section, jgrp.section_code)
AND TRUNC (aia.invoice_date) BETWEEN NVL (:p_invoice_from, TRUNC (aia.invoice_date) ) AND NVL (:p_invoice_to, TRUNC (aia.invoice_date) )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid1,
    apps.gl_code_combinations gcc1
  WHERE 1                           = 1
  AND aid1.dist_code_combination_id = gcc1.code_combination_id
  AND gcc1.segment1                 = NVL (:p_branchwise, gcc1.segment1)
  AND aid1.invoice_id               = aia.invoice_id
  )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid2,
    apps.gl_code_combinations gcc2
  WHERE 1                           = 1
  AND aid2.dist_code_combination_id = gcc2.code_combination_id
  AND gcc2.segment2                 = NVL (:p_headwise, gcc2.segment2)
  AND aid2.invoice_id               = aia.invoice_id
  )
UNION ALL
SELECT aia.invoice_date,
  aia.doc_sequence_value voucher_num,
  aia.invoice_num,
  NULL invoice_amount --aia.invoice_amount
  ,
  NULL party_name -- aps.vendor_name party_name
  ,
  NULL expense_account,
  NULL tds_section --jgrp.section_code TDS_Section
  ,
  NULL tds_percentage, --jtrx.tax_rate TDS_Percentage
  aia.invoice_amount tds_amount,
  (SELECT jatt.pan_no
  FROM apps.jai_ap_tds_th_vsite_v jatt
  WHERE jatt.vendor_id    = aia.vendor_id
  AND jatt.vendor_site_id = aia.vendor_site_id
  AND ROWNUM              = 1
  ) pan_number,
  0 int_per,
  aia.invoice_amount tds_payable
  -- Invoice Amount + Interest Amount -- Here Interest Zero
  ,
  NULL vendor_id --aia.vendor_id
  ,
  NULL vendor_site_id , --aia.vendor_site_id                            ,
  NULL LOCATION,
  aia.invoice_id,
  (SELECT tdsp.bank_name
  FROM apps.ap_invoice_payments_all aipa,
    apps.jai_ap_tds_payments tdsp
  WHERE aipa.invoice_id = aia.invoice_id
  AND aipa.check_id     = tdsp.check_id
  AND aipa.payment_num  > 0
  AND ROWNUM            = 1
  ) bank_name,
  (SELECT tdsp.check_number
  FROM apps.ap_invoice_payments_all aipa,
    apps.jai_ap_tds_payments tdsp
  WHERE aipa.invoice_id = aia.invoice_id
  AND aipa.payment_num  > 0
  AND aipa.check_id     = tdsp.check_id
  AND ROWNUM            = 1
  ) check_number,
  (SELECT tdsp.challan_no
  FROM apps.ap_invoice_payments_all aipa,
    apps.jai_ap_tds_payments tdsp
  WHERE aipa.invoice_id = aia.invoice_id
  AND aipa.check_id     = tdsp.check_id
  AND aipa.payment_num  > 0
  AND ROWNUM            = 1
  ) challan_num,
  (SELECT tdsp.check_deposit_date
  FROM apps.ap_invoice_payments_all aipa,
    apps.jai_ap_tds_payments tdsp
  WHERE aipa.invoice_id = aia.invoice_id
  AND aipa.check_id     = tdsp.check_id
  AND aipa.payment_num  > 0
  AND ROWNUM            = 1
  ) challan_date,
  (SELECT tdsp.bsr_code
  FROM apps.ap_invoices_all aia1,
    apps.ap_suppliers asp1,
    apps.ap_invoice_payments_all aipa,
    apps.jai_ap_tds_payments tdsp
  WHERE aia1.vendor_id         = asp1.vendor_id
  AND UPPER (asp1.vendor_name) = UPPER ('Income Tax Authority')
  AND aipa.payment_num         > 0
  AND aipa.invoice_id          = aia.invoice_id
  AND aia1.invoice_id          = aipa.invoice_id
  AND aipa.check_id            = tdsp.check_id
  AND ROWNUM                   = 1
  ) bsr_code
FROM apps.ap_invoices_all aia,
  apps.ap_suppliers aps
WHERE 1                     = 1
AND UPPER (aps.vendor_name) = UPPER ('Income Tax Authority')
AND aia.vendor_id           = aps.vendor_id
AND aia.attribute1         IS NULL
AND aia.attribute15        IS NULL
AND aps.vendor_id           = NVL (:p_party_name, aps.vendor_id)
AND TRUNC (aia.invoice_date) BETWEEN NVL (:p_invoice_from, TRUNC (aia.invoice_date) ) AND NVL (:p_invoice_to, TRUNC (aia.invoice_date) )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid3,
    apps.gl_code_combinations gcc3
  WHERE 1                           = 1
  AND aid3.dist_code_combination_id = gcc3.code_combination_id
  AND gcc3.segment1                 = NVL (:p_branchwise, gcc3.segment1)
  AND aid3.invoice_id               = aia.invoice_id
  )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid4,
    apps.gl_code_combinations gcc4
  WHERE 1                           = 1
  AND aid4.dist_code_combination_id = gcc4.code_combination_id
  AND gcc4.segment2                 = NVL (:p_headwise, gcc4.segment2)
  AND aid4.invoice_id               = aia.invoice_id
  )
UNION ALL
SELECT aia.invoice_date,
  aia.doc_sequence_value voucher_num,
  aia.invoice_num,
  aia.invoice_amount,
  aps.vendor_name party_name,
  (SELECT t.description
  FROM apps.ap_invoice_distributions_all apd,
    apps.gl_code_combinations gcc,
    apps.fnd_flex_values v,
    apps.fnd_flex_value_sets s,
    apps.fnd_flex_values_tl t
  WHERE apd.invoice_id             = aia.invoice_id
  AND apd.dist_code_combination_id = gcc.code_combination_id
  AND apd.line_type_lookup_code    = 'ITEM'
  AND gcc.segment2                 = v.flex_value
  AND s.flex_value_set_id          = v.flex_value_set_id
  AND t.flex_value_id              = v.flex_value_id
  AND v.enabled_flag               = 'Y'
  AND v.summary_flag               = 'N'
  AND flex_value_set_name LIKE 'KJIPL_ACCOUNT_VALUESET'
  AND ROWNUM = 1
  ) expense_account,
  NULL tds_section,    --,jgrp.section_code TDS_Section
  NULL tds_percentage, --,jtrx.tax_rate TDS_Percentage
  (SELECT SUM (aia1.invoice_amount)
  FROM apps.ap_invoices_all aia1,
    apps.ap_suppliers asp1
  WHERE aia1.vendor_id        = asp1.vendor_id
  AND UPPER(asp1.vendor_name) = UPPER('Income Tax Authority')
  AND aia1.attribute15        = aia.invoice_num
  ) tds_amount,
  (SELECT jatt.pan_no
  FROM apps.jai_ap_tds_th_vsite_v jatt
  WHERE jatt.vendor_id    = aia.vendor_id
  AND jatt.vendor_site_id = aia.vendor_site_id
  AND ROWNUM              = 1
  ) pan_number,
  0 int_per,
  aia.invoice_amount tds_payable
  -- Invoice Amount + Interest Amount -- Here Interest Zero
  ,
  aia.vendor_id,
  aia.vendor_site_id,
  (SELECT t.description
  FROM apps.ap_invoice_distributions_all apd,
    apps.gl_code_combinations gcc,
    apps.fnd_flex_values v,
    apps.fnd_flex_value_sets s,
    apps.fnd_flex_values_tl t
  WHERE apd.invoice_id             = aia.invoice_id
  AND apd.dist_code_combination_id = gcc.code_combination_id
  AND apd.line_type_lookup_code    = 'ITEM'
  AND gcc.segment1                 = v.flex_value
  AND s.flex_value_set_id          = v.flex_value_set_id
  AND t.flex_value_id              = v.flex_value_id
  AND v.enabled_flag               = 'Y'
  AND v.summary_flag               = 'N'
  AND flex_value_set_name LIKE 'KJIPL_SHOWROOM_VALUESET'
  AND ROWNUM = 1
  ) LOCATION,
  aia.invoice_id,
  xxv1.bank_name bank_name,
  xxv1.check_number check_number,
  xxv1.challan_num challan_num,
  xxv1.challan_date challan_date,
  xxv1.bsr_code bsr_code
FROM apps.ap_invoices_all aia,
  apps.ap_suppliers aps,
  apps.xxkly_tds_report_v xxv1
WHERE 1                      = 1
AND UPPER (aps.vendor_name) <> UPPER ('Income Tax Authority')
AND aia.vendor_id            = aps.vendor_id
AND xxv1.invoice_num(+)      = aia.invoice_num
AND aps.vendor_id            = NVL (:p_party_name, aps.vendor_id)
AND TRUNC (aia.invoice_date) BETWEEN NVL (:p_invoice_from, TRUNC (aia.invoice_date) ) AND NVL (:p_invoice_to, TRUNC (aia.invoice_date) )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid5,
    apps.gl_code_combinations gcc5
  WHERE 1                           = 1
  AND aid5.dist_code_combination_id = gcc5.code_combination_id
  AND gcc5.segment1                 = NVL (:p_branchwise, gcc5.segment1)
  AND aid5.invoice_id               = aia.invoice_id
  )
AND EXISTS
  (SELECT '1'
  FROM apps.ap_invoice_distributions aid6,
    apps.gl_code_combinations gcc6
  WHERE 1                           = 1
  AND aid6.dist_code_combination_id = gcc6.code_combination_id
  AND gcc6.segment2                 = NVL (:p_headwise, gcc6.segment2)
  AND aid6.invoice_id               = aia.invoice_id
  )
AND aia.attribute15 = 'MANUAL'
ORDER BY invoice_date