JA India - TDS Prescribed Rate - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - TDS Prescribed Rate Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINITCP_XML
DB package: JA_JAINITCP_XMLP_PKG
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
Parameter Name SQL text Validation
To Date
 
Date
From Date
 
Date
Supplier Organization Type
 
LOV Oracle
TDS Payment Type
 
LOV Oracle
Fin Year
 
LOV Oracle
Organization
 
LOV Oracle
Organization TAN Number
 
LOV Oracle