JL Colombian Payables Withholding Report- Not Supported: Reserved For Future Use

➜ Back to Library
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
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
Download
Related Blitz Reports
CST Miscellaneous Accrual Reconciliation CST AP and PO Accrual Reconciliation AP Supplier Upload JL Colombian Payables Withholding Certificate - draft AP Invoice on Hold AP Invoices and Lines AP Withholding Tax by Invoice AP Withholding Tax by Payment Report- Not Supported: Reserved For Future Use FA Asset Register
Blitz Report™

Blitz Report™ provides multiple benefits: