SELECT 
a.invoice_num, 
b.vendor_name, 
SUM(a.invoice_amount) 
invoice_amount, 
SUM(c.amount) 
taxable_amt, 
f.description, 
g.section_code 
TDS_SECTION, 
d.code_combination_id, 
SUM( 
( c.amount * g.tax_rate / 100 ) - ( 
( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate ) - ( ( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate ) - (
( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate ))AMT_OF_TDS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate) 
AMT_OF_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate) 
AMT_OF_SH_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate) 
AMT_OF_SURCHARGE, 
SUM(c.amount * g.tax_rate / 100) 
tds_total, 
( SUM(c.amount) - SUM(c.amount * g.tax_rate / 100) ) 
payable_amt, 
h.tds_vendor_type_lookup_code 
FROM   ap_invoices_all a, 
       po_vendors b, 
       ap_invoice_distributions_all c, 
       gl_code_combinations d, 
       fnd_flex_value_sets e, 
       fnd_flex_values_vl f, 
       jai_cmn_taxes_all g, 
       jai_ap_tds_vendor_hdrs h 
WHERE  1 = 1 
       AND a.invoice_id = c.invoice_id 
       AND a.invoice_num LIKE '10 lakh%' 
       AND a.vendor_id = b.vendor_id 
       AND c.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' 
       AND c.dist_code_combination_id = d.code_combination_id 
       AND d.segment5 = f.flex_value 
       AND e.flex_value_set_id = f.flex_value_set_id 
       AND e.flex_value_set_name = 'STL_GL_NAT_ACCOUNT_VS' 
       AND a.invoice_id = c.invoice_id 
       AND a.org_id = c.org_id 
       AND a.org_id = :p_org_id 
       AND a.gl_date BETWEEN :p_from_date AND :p_to_date 
       AND g.tax_id = c.global_attribute1 
       AND a.vendor_id = h.vendor_id 
       AND a.vendor_site_id = h.vendor_site_id(+) 
       AND invoice_type_lookup_code NOT IN( 'CREDIT', 'DEBIT' ) 
GROUP  BY a.invoice_num, 
          b.vendor_name, 
          f.description, 
          g.section_code, 
          h.tds_vendor_type_lookup_code, 
          d.code_combination_id 
UNION 
SELECT 
b.vendor_name, 
a.invoice_num, 
SUM(a.invoice_amount) 
invoice_amount, 
SUM(c.amount) 
taxable_amt, 
f.description, 
g.section_code 
TDS_SECTION, 
d.code_combination_id, 
SUM( 
( c.amount * g.tax_rate / 100 ) - ( 
( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate ) - ( ( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate ) - (
( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate ))AMT_OF_TDS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.cess_rate, 0) / g.tax_rate) 
AMT_OF_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.sh_cess_rate, 0) / g.tax_rate) 
AMT_OF_SH_CESS, 
SUM(( c.amount * g.tax_rate / 100 ) * Nvl(g.surcharge_rate, 0) / g.tax_rate) 
AMT_OF_SURCHARGE, 
SUM(c.amount * g.tax_rate / 100) 
tds_total, 
( SUM(c.amount) - SUM(c.amount * g.tax_rate / 100) ) 
payable_amt, 
h.tds_vendor_type_lookup_code 
FROM   ap_invoices_all a, 
       po_vendors b, 
       ap_invoice_distributions_all c, 
       gl_code_combinations d, 
       fnd_flex_value_sets e, 
       fnd_flex_values_vl f, 
       jai_cmn_taxes_all g, 
       jai_ap_tds_invoices p, 
       jai_ap_tds_vendor_hdrs h 
WHERE  1 = 1 
       AND a.invoice_id = c.invoice_id 
       AND a.vendor_id = b.vendor_id 
       AND c.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS' 
       AND c.dist_code_combination_id = d.code_combination_id 
       AND d.segment5 = f.flex_value 
       AND e.flex_value_set_id = f.flex_value_set_id 
       AND e.flex_value_set_name = 'STL_GL_NAT_ACCOUNT_VS' 
       AND a.invoice_id = c.invoice_id 
       AND a.org_id = c.org_id 
       AND a.org_id = :p_org_id 
       AND a.gl_date BETWEEN :p_from_date AND :p_to_date 
       AND c. global_attribute1 IS NULL 
       AND p.invoice_id = a.invoice_id 
       AND g.tax_id = p.tds_tax_id 
       AND a.vendor_id = h.vendor_id 
       AND a.vendor_site_id = h.vendor_site_id(+) 
       AND invoice_type_lookup_code NOT IN( 'CREDIT', 'DEBIT' ) 
       AND a.invoice_num LIKE '10 lakh%' 
GROUP  BY b.vendor_name, 
          a.invoice_num, 
          f.description, 
          g.section_code, 
          h.tds_vendor_type_lookup_code, 
          d.code_combination_id