JA India - TDS at Lower Rates - draft

SELECT distinct
       jap.org_tan_num  TDS_VENDOR,
       hou.name ORG,
       jrg.attribute_value  TDS_PREFIX,
       nvl(hou.internal_address_line,'NIL') internal_address_line,
       nvl(loc.address_line_1 ||','|| loc.address_line_2 ||','|| loc.address_line_3 ||','|| loc.town_or_city, ' ')  CITY,
       nvl(loc.country,'NIL') hr_loc_country, 
       hou.location_id,
       pov2.vendor_name tax_vendor,
       pov1.vat_registration_num,
       pov1.vendor_name,
       povs.address_line1 ||','|| povs.address_line2 ||','|| povs.address_line3 ||','||povs.country vendor_site_address,
       frmd.invoice_amount,
       api1.invoice_date,
    frmd.invoice_id   invoice_id,
       FRMD.tds_tax_rate,
       NVL(  ROUND( ( frmd.tds_invoice_amt * (jtc.tax_rate-jtc.surcharge_rate) ) / jtc.tax_rate, 2 ), 0 )  tds_invoice_amt,
       NVL(  ROUND(  frmd.tds_invoice_amt * jtc.surcharge_rate / jtc.tax_rate, 2 ), 0 ) sur_amt,
       NVL(  ROUND( ( frmd.tds_invoice_amt * (jtc.tax_rate-jtc.surcharge_rate) ) / jtc.tax_rate, 2 ) + ROUND(  frmd.tds_invoice_amt * jtc.surcharge_rate / jtc.tax_rate, 2 ), 0 ) Tot_Tax_Amt,
       api2.invoice_date tds_invoice_date,
       apip.accounting_date,
       frmd.it_certificate_number,
       frmd.certificate_num certificate_id, 
       to_char(afrm.issue_date,'DD-MM-YYYY') issue_date, 
	JA_JAINITCL_XMLP_PKG.cf_gross_inv_amtformula(frmd.invoice_id,frmd.invoice_amount) CF_GROSS_INV_AMT
FROM
      JAI_AP_TDS_F16_HDRS_ALL AFRM,              
      JAI_AP_TDS_F16_DTLS_ALL FRMD , 
      JAI_CMN_TAXES_ALL jtc,
      hr_organization_units HOU,
      hr_locations LOC,
      ap_invoices_all API1,
      ap_invoices_all API2,
      ap_invoice_payments_all APIP,
      po_vendors POV1,   
      po_vendors POV2,
      po_vendor_sites_all POVS,
      jai_ap_tds_org_tan_v jap,
      JAI_RGM_ORG_REGNS_V  jrg,
      JAI_AP_TDS_CERT_NUMS jatcn  
 WHERE
       decode(afrm.org_id,null,0,afrm.org_id) = NVL(:P_ORGANIZATION_ID,decode(afrm.org_id,null,0,afrm.org_id))
 AND   afrm.tds_tax_section = NVL(:P_TDS_PAYMENT_TYPE, afrm.tds_tax_section)
 AND   frmd.certificate_num = afrm.certificate_num
 AND   afrm.org_id         = hou.organization_id (+)
 and   afrm.tds_tax_id     = jtc.tax_id        
 AND   jap.organization_id = afrm.org_id
 AND   jrg.organization_id = afrm.org_id
 AND   jatcn.organization_id = afrm.org_id 
AND    jatcn.org_tan_num = nvl(:P_ORG_TAN_NUM,jatcn.org_tan_num) 
 AND   jrg.regime_code     = 'TDS'
 AND   jrg.registration_type  = 'OTHERS'
 AND   jrg.attribute_type_code = 'OTHERS'
 AND   jrg.attribute_code      = 'PAN NO'
 AND   TRUNC(api2.invoice_date) >= TRUNC(nvl(:LP_FROM_DATE,api2.invoice_date))
 AND    TRUNC(api2.invoice_date) <= TRUNC(nvl(:P_TO_DATE,sysdate))
 AND   ( (UPPER(NVL(pov1.organization_type_lookup_code,'OTHERS'))   = NVL(:P_SUPPLIER_ORG_TYPE,'OTHERS')
      AND UPPER(NVL(:P_SUPPLIER_ORG_TYPE,'OTHERS')) = 'COMPANY' )
   OR ( UPPER(NVL(:P_SUPPLIER_ORG_TYPE,'OTHERS')) <> 'COMPANY'   
 AND UPPER(NVL(pov1.organization_type_lookup_code,'OTHERS'))
  <> 'COMPANY'   )   )  
 AND   pov1.vendor_id  (+)            = afrm.vendor_id
 AND   povs.vendor_site_id (+)        = afrm.vendor_site_id
 AND   api2.invoice_id  (+)           = frmd.tds_invoice_id
 AND   api1.invoice_id (+)               = frmd.invoice_id
AND  (frmd.invoice_amount +
	(SELECT nvl(sum(amount),0) FROM ap_invoice_lines_all a 
	WHERE invoice_id = frmd.invoice_id 
	AND LINE_TYPE_LOOKUP_CODE = 'PREPAY') ) != 0
 AND   apip.invoice_payment_id (+) = frmd.invoice_payment_id
 AND   hou.location_id             = loc.location_id (+)
 AND   pov2.vendor_id  (+)         = afrm.tax_authority_id
AND   frmd.it_certificate_number IS NOT NULL 
order by pov2.vendor_name