JL Colombian Payables Withholding Report- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Colombian Payables Withholding Report
Application: Latin America Localizations
Source: Colombian Payables Withholding Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOPWHR_XML
DB package: JL_JLCOPWHR_XMLP_PKG
Description: Colombian Payables Withholding Report
Application: Latin America Localizations
Source: Colombian Payables Withholding Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOPWHR_XML
DB package: JL_JLCOPWHR_XMLP_PKG
Run
JL Colombian Payables Withholding Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT 'Non Vat' certificate_type, substr(jlz.awt_type_code,1,20) withholding_type, substr(jlz.description,1,30) description, PV_1.vendor_id vendor_id, PV_1.segment1 vendor_number, PV_1.vendor_name supplier_name, PV_1.num_1099||'-'||PV_1.global_attribute12 NIT, ai.invoice_num Document_number, aid.accounting_date Accounting_date, sum(nvl(nvl(aid.base_amount,aid.amount) * (-1),0)) withholding_amount, sum(decode(aid.reversal_flag,'Y', (decode(ABS(nvl(aid.base_amount,aid.amount)* aid.awt_gross_amount), (nvl(aid.base_amount,aid.amount)*aid.awt_gross_amount), ((-1)*aid.awt_gross_amount), aid.awt_gross_amount)), decode(aid.base_amount,NULL, aid.awt_gross_amount, aid.awt_gross_amount*aid.exchange_rate))) Taxable_base_amount, '' Tax_name, hr.global_attribute8 company_name, aid.dist_code_combination_id dist_ccid, cc.chart_of_accounts_id chart_of_accounts, ai.set_of_books_id set_of_books, null C_FLEXDATA, &company_name CF_1, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_desc_all, JL_JLCOPWHR_XMLP_PKG.cf_vat_amountformula('Non Vat', '', PV_1.vendor_id, PV_1.segment1,ai.invoice_num) CF_VAT_AMOUNT FROM po_vendors pv_1, ap_invoices ai, ap_invoice_distributions aid, ap_tax_codes atc, hr_locations hr, gl_code_combinations cc, gl_sets_of_books gl , jl_zz_ap_awt_types jlz WHERE ((aid.global_attribute2 is null and aid.invoice_id = ai.invoice_id and PV_1.vendor_id = ai.vendor_id) or (aid.global_attribute2 = PV_1.segment1 and aid.invoice_id = ai.invoice_id)) AND ai.cancelled_date is NULL AND aid.withholding_tax_code_id = atc.tax_id AND atc.global_attribute4 = jlz.awt_type_code AND gl.set_of_books_id = ai.set_of_books_id AND aid.dist_code_combination_id = cc.code_combination_id AND gl.chart_of_accounts_id = cc.chart_of_accounts_id AND aid.match_status_flag = 'A' AND aid.line_type_lookup_code = 'AWT' AND aid.accounting_date BETWEEN :P_ACCOUNTING_DATE_FROM AND :P_ACCOUNTING_DATE_TO AND atc.global_attribute19 is NULL AND atc.global_attribute4 BETWEEN (nvl(:p_withholding_type_from, atc.global_attribute4)) and (nvl(:p_withholding_type_to, atc.global_attribute4)) &P_Selected_TaxPayerId AND hr.location_id = :C_LOCATION_ID AND &C_WHERE_FLEX GROUP BY jlz.awt_type_code, jlz.description, &P_FLEXDATA, aid.dist_code_combination_id, cc.chart_of_accounts_id, ai.set_of_books_id, PV_1.num_1099||'-'||PV_1.global_attribute12, PV_1.vendor_id, PV_1.vendor_name, ai.invoice_num, aid.accounting_date, hr.global_attribute8, PV_1.segment1 --added ,&company_name ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') UNION ALL SELECT 'VAT' certificate_type, substr(jlz.awt_type_code,1,20) withholding_type, substr(jlz.description,1,30) description, PV_1.vendor_id vendor_id, PV_1.segment1 vendor_number, PV_1.vendor_name supplier_name, PV_1.num_1099||'-'||PV_1.global_attribute12 NIT, ai.invoice_num Document_number, aid.accounting_date Accounting_date, sum(nvl(nvl(aid.base_amount,aid.amount)*(-1),0)) withholding_amount, sum(decode(aid.reversal_flag,'Y', (decode(ABS(nvl(aid.base_amount,aid.amount)* aid.awt_gross_amount), (nvl(aid.base_amount,aid.amount)*aid.awt_gross_amount), ((-1)*aid.awt_gross_amount), aid.awt_gross_amount)), decode(aid.base_amount,NULL, aid.awt_gross_amount, aid.awt_gross_amount*aid.exchange_rate))) Taxable_base_amount, atc.name Tax_name, hr.global_attribute8 company_name, aid.dist_code_combination_id dist_ccid, cc.chart_of_accounts_id chart_of_accounts, ai.set_of_books_id set_of_books, &P_FLEXDATA C_FLEXDATA, &company_name CF_1, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_desc_all, JL_JLCOPWHR_XMLP_PKG.cf_vat_amountformula( 'VAT', atc.name, PV_1.vendor_id, PV_1.segment1,ai.invoice_num) CF_VAT_AMOUNT FROM po_vendors pv_1, ap_invoices ai, ap_invoice_distributions aid, ap_tax_codes atc, hr_locations hr, gl_code_combinations cc, gl_sets_of_books gl , jl_zz_ap_awt_types jlz WHERE ((aid.global_attribute2 is null and aid.invoice_id = ai.invoice_id and PV_1.vendor_id = ai.vendor_id) or (aid.global_attribute2 = PV_1.segment1 and aid.invoice_id = ai.invoice_id)) AND ai.cancelled_date is NULL AND aid.withholding_tax_code_id = atc.tax_id AND atc.global_attribute4 = jlz.awt_type_code AND gl.set_of_books_id = ai.set_of_books_id AND aid.dist_code_combination_id = cc.code_combination_id AND gl.chart_of_accounts_id = cc.chart_of_accounts_id AND aid.match_status_flag = 'A' AND aid.line_type_lookup_code = 'AWT' AND aid.accounting_date BETWEEN :P_ACCOUNTING_DATE_FROM AND :P_ACCOUNTING_DATE_TO AND atc.global_attribute19 is NOT NULL AND atc.global_attribute4 BETWEEN (nvl(:p_withholding_type_from, atc.global_attribute4)) and (nvl(:p_withholding_type_to, atc.global_attribute4)) &P_Selected_TaxPayerId AND hr.location_id = :C_LOCATION_ID AND &C_WHERE_FLEX GROUP BY jlz.awt_type_code, jlz.description, &P_FLEXDATA, aid.dist_code_combination_id, cc.chart_of_accounts_id, ai.set_of_books_id, PV_1.num_1099||'-'||PV_1.global_attribute12, PV_1.vendor_id, PV_1.vendor_name, ai.invoice_num, aid.accounting_date, atc.name, hr.global_attribute8, PV_1.segment1 --added ,&company_name ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') ORDER BY 13 ASC, 16 ASC, 2 ASC, 1 ASC, 3 ASC, 17 ASC, 15 ASC, 14 ASC, 6 ASC, 4 ASC, 7 ASC, 5 ASC, 8 ASC, 2, 17, 7 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Period From |
|
LOV Oracle | |
Period To |
|
LOV Oracle | |
Taxpayer ID To |
|
LOV Oracle | |
Taxpayer ID From |
|
LOV Oracle | |
Withholding Type From |
|
LOV Oracle | |
Withholding Type To |
|
LOV Oracle | |
Flexfield From |
|
Char | |
Flexfield To |
|
Char |