JA India - TDS at Lower Rates - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - TDS at Lower Rates Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINITCL_XML
DB package: JA_JAINITCL_XMLP_PKG
Source: India - TDS at Lower Rates Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINITCL_XML
DB package: JA_JAINITCL_XMLP_PKG
Run
JA India - TDS at Lower Rates - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
ORG_TAN_NUM |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
TDS Payment Type |
|
LOV Oracle | |
Supplier Organization Type |
|
LOV Oracle | |
From Date |
|
Char | |
To Date |
|
Char |