JA India - TDS Prescribed Rate - draft

SELECT  distinct api1.invoice_id invoice_id,
      hou.name ORG,
      jrg.attribute_value   pan_no1,
      jap.org_tan_num  tan_no1,
      nvl(hou.internal_address_line,'NIL')   internal_address_line,
      loc.address_line_1 ||','||  loc.address_line_2 ||','|| loc.address_line_3 ||','|| loc.town_or_city ||'-'|| loc.postal_code   CITY,
      nvl(loc.country,'NIL')   loc_country,
      hou.location_id   location_id,
      pov2.vendor_name  tax_auth_vendor_name,
      pov1.vat_registration_num  vat_registration_num,
      pov1.vendor_name  vendor_name,
      povs.address_line1 ||','|| povs.address_line2 ||','||  povs.address_line3 ||','|| povs.city||','||povs.state||','||povs.zip||','|| povs.country VENDOR_SITE_ADDRESS,
      frmd.invoice_amount  invoice_amount,
      api1.invoice_date  invoice_date,
      ROUND( ( nvl(frmd.tds_invoice_amt,0) * (NVL(jtc.tax_rate, 0)-NVL(jtc.surcharge_rate, 0)) ) / NVL(jtc.tax_rate, 1), 2 )  tds_inv_amt,
      ROUND(  nvl(frmd.tds_invoice_amt,0) * NVL(jtc.surcharge_rate, 0) / NVL(jtc.tax_rate, 0), 2 )  sur_amt,
      ROUND( nvl(frmd.tds_invoice_amt,0) , 2 )   Tot_Tax_Amt,
      api2.invoice_date  TDS_INVOICE_DATE,
      apip.accounting_date  accounting_date,
      frmd.certificate_num  certificate_id,
      afrm.issue_date   issue_date,
      frmd.tds_invoice_id,
      povs.vendor_site_id
      ,povs.vendor_id
      , jtc.tax_id,
      nvl(jtc.tax_rate,1) tax_rate,
      nvl(jtc.surcharge_rate, 0) surcharge_rate,
       apc.attribute3 challan_number,
	JA_JAINITCP_XMLP_PKG.cf_vendor_site_addressformula(povs.vendor_site_id) CF_Vendor_site_address,
	JA_JAINITCP_XMLP_PKG.cf_pan_noformula(povs.vendor_id, povs.vendor_site_id) CF_PAN_NO,
	JA_JAINITCP_XMLP_PKG.cf_gross_inv_amtformula(api1.invoice_id, frmd.invoice_amount) CF_GROSS_INV_AMT,
	JA_JAINITCP_XMLP_PKG.cf_accounting_dateformula(api1.invoice_id) CF_Accounting_date,
	JA_JAINITCP_XMLP_PKG.cf_invoice_amountformula(api1.invoice_id, frmd.invoice_amount) CF_INVOICE_AMOUNT,
	JA_JAINITCP_XMLP_PKG.cf_tds_amountformula(api1.invoice_id, nvl ( jtc.tax_rate , 1 ), nvl ( jtc.surcharge_rate , 0 ), frmd.invoice_amount) cf_TDS_AMOUNT,
	JA_JAINITCP_XMLP_PKG.cf_tot_tds_tax_amountformula(ROUND ( ( nvl ( frmd.tds_invoice_amt , 0 ) * ( NVL ( jtc.tax_rate , 0 ) - NVL ( jtc.surcharge_rate , 0 ) ) ) / NVL ( jtc.tax_rate , 1 ) , 2 ), ROUND ( nvl ( frmd.tds_invoice_amt , 0 ) * NVL ( jtc.surcharge_rate , 0 ) / NVL ( jtc.tax_rate , 0 ) , 2 )) CF_tot_tds_tax_amount,
	JA_JAINITCP_XMLP_PKG.cf_amountsformula(api1.invoice_id, jtc.tax_id, ROUND ( nvl ( frmd.tds_invoice_amt , 0 ) , 2 )) CF_inv_amounts,
	JA_JAINITCP_XMLP_PKG.CP_cess_amount_p CP_cess_amount,
	JA_JAINITCP_XMLP_PKG.CP_surcharge_amount_p CP_surcharge_amount,
	JA_JAINITCP_XMLP_PKG.CP_tds_amount_p CP_tds_amount,
	JA_JAINITCP_XMLP_PKG.CP_sh_cess_amount_p CP_sh_cess_amount
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_vendors POV3,
     po_vendor_sites_all POVS,
     jai_ap_tds_org_tan_v jap,
     JAI_RGM_ORG_REGNS_V  jrg,
     JAI_AP_TDS_CERT_NUMS jatcn  ,
      ap_checks_all apc
WHERE
           ( DECODE(afrm.org_id,null,0,afrm.org_id)   = nvl(:P_ORGANIZATION_ID, DECODE(afrm.org_id,null,0,afrm.org_id)) OR afrm.org_id = 0)
AND  afrm.tds_tax_section = NVL(:P_TDS_PAYMENT_TYPE, afrm.tds_tax_section)
AND   afrm.org_id         = hou.organization_id (+)
AND   jap.organization_id = afrm.org_id
AND   jatcn.organization_id = afrm.org_id
AND   jrg.organization_id = afrm.org_id
AND   jrg.regime_code     = 'TDS'
AND   jrg.registration_type  = 'OTHERS'
AND   jrg.attribute_type_code = 'OTHERS'
AND   jrg.attribute_code      = 'PAN NO'
AND   frmd.certificate_num            = afrm.certificate_num
and    afrm.tds_tax_id = jtc.tax_id
AND  (
             (:P_SUPPLIER_ORG_TYPE_T IS NULL AND (pov1.organization_type_lookup_code IS NULL OR pov1.organization_type_lookup_code <> 'Company') )
             OR
             (:P_SUPPLIER_ORG_TYPE_T = 'COMPANY' AND pov1.organization_type_lookup_code = 'Company')
         )
AND  apc.check_id = apip.check_id
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   TRUNC(api2.invoice_date) >= TRUNC(nvl(:P_FROM_DATE_T,api2.invoice_date) )
AND   TRUNC(api2.invoice_date) <= TRUNC(nvl(:P_TO_DATE_T,sysdate))
AND   pov3.vendor_id (+)                 = api2.vendor_id
AND   api1.invoice_id (+)                = frmd.invoice_id
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 afrm.fin_yr  = :P_FIN_YEAR
AND afrm.org_tan_num = NVL(:P_ORG_TAN_NUM,afrm.org_tan_num)
AND  AFRM.vendor_id = NVL(:p_vendor_id,AFRM.vendor_id)
AND  AFRM.Vendor_site_id =NVL (:p_vendor_site_id,AFRM.Vendor_site_id)
AND  POV1.VENDOR_ID = NVL(:p_vendor_id,POV1.VENDOR_ID)
AND  POVS.VENDOR_SITE_ID = NVL(:P_VENDOR_SITE_ID , POVS.VENDOR_SITE_ID)
AND  POVS.vendor_id = POV1.vendor_id
AND FRMD.tds_invoice_amt > 0
ORDER BY 9,7,8,6,2,3,4,12,11,10,1,13,14,28,24,22,23,5,18,19,20,21,25,27,26