JL Colombian Payables Withholding - draft

Description
Categories: BI Publisher
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
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
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
 
Flexfield To
 
Ledger ID
 
Chart of Accounts ID
 
LOV Oracle