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 |
