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 |
