JL Argentine Payables Withholding Flat File - draft

Description
Categories: BI Publisher
Columns: Document Date, Document Number, Document Amount, Dgi Tax Type Code, Dgi Tax Regime Code, Withholding Code, Supplier Condition Code, Withholding Amount, Exemption Percentage, Bulletin Issue Date ...
Application: Latin America Localizations
Source: Argentine Payables Withholding Flat File (XML) - Not Supported: Reserved For Future Use
Short Name: JLARPWFF_XML
DB package: JL_JLARPWFF_XMLP_PKG
SELECT	jlc.awt_date document_date,
substr(lpad(jlc.bank_account_num,30,'0'),length(lpad(jlc.bank_account_num,30,'0'))-4,5)||'#'||substr(lpad(jlc.check_number,15,'0'),length(lpad(jlc.check_number,15,'0'))-5,6) document_number,
abs(nvl(jlc.taxable_base_amount,0))  document_amount,
lpad(substr(nvl(jat.dgi_tax_type_code,'0'),1,3),3,'0') dgi_tax_type_code,
lpad(substr(nvl(atc.global_attribute5,'0'),1,3),3,'0') dgi_tax_regime_code,
'1' withholding_code,
lpad(substr(nvl(atc.global_attribute20,'0'),1,2),2,'0') supplier_condition_code,
abs(jlc.withholding_amount)  withholding_amount,
nvl(jst.exemption_rate,0) exemption_percentage,
lpad(nvl(to_char(jst.exemption_start_date,'DD/MM/RRRR'),' '),10, ' ') bulletin_issue_date,
lpad(substr(nvl(pov.global_attribute10,'0'),1,2),2,'0')  supp_tax_identification_type,
rtrim(substr(replace(pov.num_1099,'-'),1,10))||substr(pov.global_attribute12,1,1) cuit_number,
jlc.certificate_number certificate_number,
hrl.registration_number comp_primary_id_number,
jat.tax_authority_id comp_tax_authority_id,
jct.tax_authority_type comp_tax_authority_type,
pov.num_1099 supp_primary_id_number,
jat.tax_authority_id supp_tax_authority_id,
jst.tax_authority_type supp_tax_authority_type, 
	JL_JLARPWFF_XMLP_PKG.cf_wht_agent_numformula(hrl.registration_number, jat.tax_authority_id, jct.tax_authority_type) CF_WHT_AGENT_NUM, 
	JL_JLARPWFF_XMLP_PKG.cf_supp_inscriptionformula(pov.num_1099, jat.tax_authority_id, jst.tax_authority_type) CF_SUPP_INSCRIPTION, 
	JL_JLARPWFF_XMLP_PKG.cf_amt_in_excessformula() CF_AMT_IN_EXCESS, 
	JL_JLARPWFF_XMLP_PKG.cf_document_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )) CF_DOCUMENT_AMT, 
	JL_JLARPWFF_XMLP_PKG.cf_federal_recformula(jlc.awt_date, substr ( lpad ( jlc.bank_account_num , 30 , '0' ) , length ( lpad ( jlc.bank_account_num , 30 , '0' ) ) - 4 , 5 ) || '#' || substr ( lpad ( jlc.check_number , 15 , '0' ) , length ( lpad ( jlc.check_number , 15 , '0' ) ) - 5 , 6 ), JL_JLARPWFF_XMLP_PKG.cf_document_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )), lpad ( substr ( nvl ( jat.dgi_tax_type_code , '0' ) , 1 , 3 ) , 3 , '0' ), lpad ( substr ( nvl ( atc.global_attribute5 , '0' ) , 1 , 3 ) , 3 , '0' ), '1', JL_JLARPWFF_XMLP_PKG.cf_taxable_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )), lpad ( substr ( nvl ( atc.global_attribute20 , '0' ) , 1 , 2 ) , 2 , '0' ), JL_JLARPWFF_XMLP_PKG.cf_wh_amtformula(abs ( jlc.withholding_amount )), JL_JLARPWFF_XMLP_PKG.cf_exempt_percformula(nvl ( jst.exemption_rate , 0 )), lpad ( nvl ( to_char ( jst.exemption_start_date , 'DD/MM/RRRR' ) , ' ' ) , 10 , ' ' ), lpad ( substr ( nvl ( pov.global_attribute10 , '0' ) , 1 , 2 ) , 2 , '0' ), rtrim ( substr ( replace ( pov.num_1099 , '-' ) , 1 , 10 ) ) || substr ( pov.global_attribute12 , 1 , 1 ), JL_JLARPWFF_XMLP_PKG.cf_cert_numformula(jlc.certificate_number)) CF_FEDERAL_REC, 
	JL_JLARPWFF_XMLP_PKG.cf_prov_recformula(jlc.awt_date, substr ( lpad ( jlc.bank_account_num , 30 , '0' ) , length ( lpad ( jlc.bank_account_num , 30 , '0' ) ) - 4 , 5 ) || '#' || substr ( lpad ( jlc.check_number , 15 , '0' ) , length ( lpad ( jlc.check_number , 15 , '0' ) ) - 5 , 6 ), JL_JLARPWFF_XMLP_PKG.cf_document_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )), lpad ( substr ( nvl ( jat.dgi_tax_type_code , '0' ) , 1 , 3 ) , 3 , '0' ), lpad ( substr ( nvl ( atc.global_attribute5 , '0' ) , 1 , 3 ) , 3 , '0' ), '1', JL_JLARPWFF_XMLP_PKG.cf_taxable_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )), lpad ( substr ( nvl ( atc.global_attribute20 , '0' ) , 1 , 2 ) , 2 , '0' ), JL_JLARPWFF_XMLP_PKG.cf_wh_amtformula(abs ( jlc.withholding_amount )), JL_JLARPWFF_XMLP_PKG.cf_exempt_percformula(nvl ( jst.exemption_rate , 0 )), lpad ( nvl ( to_char ( jst.exemption_start_date , 'DD/MM/RRRR' ) , ' ' ) , 10 , ' ' ), lpad ( substr ( nvl ( pov.global_attribute10 , '0' ) , 1 , 2 ) , 2 , '0' ), rtrim ( substr ( replace ( pov.num_1099 , '-' ) , 1 , 10 ) ) || substr ( pov.global_attribute12 , 1 , 1 ), JL_JLARPWFF_XMLP_PKG.cf_cert_numformula(jlc.certificate_number), :CF_WHT_AGENT_NUM, :CF_SUPP_INSCRIPTION) CF_PROV_REC, 
	JL_JLARPWFF_XMLP_PKG.cf_zonal_recformula(lpad ( substr ( nvl ( atc.global_attribute5 , '0' ) , 1 , 3 ) , 3 , '0' ), rtrim ( substr ( replace ( pov.num_1099 , '-' ) , 1 , 10 ) ) || substr ( pov.global_attribute12 , 1 , 1 ), JL_JLARPWFF_XMLP_PKG.cf_amt_in_excessformula(), jlc.awt_date, JL_JLARPWFF_XMLP_PKG.cf_wh_amtformula(abs ( jlc.withholding_amount )), JL_JLARPWFF_XMLP_PKG.cf_cert_numformula(jlc.certificate_number)) CF_ZONAL_REC, 
	JL_JLARPWFF_XMLP_PKG.cf_taxable_amtformula(abs ( nvl ( jlc.taxable_base_amount , 0 ) )) CF_TAXABLE_AMT, 
	JL_JLARPWFF_XMLP_PKG.cf_wh_amtformula(abs ( jlc.withholding_amount )) CF_WH_AMT, 
	JL_JLARPWFF_XMLP_PKG.cf_spaceformula() CF_SPACE, 
	JL_JLARPWFF_XMLP_PKG.cf_cert_numformula(jlc.certificate_number) CF_CERT_NUM, 
	JL_JLARPWFF_XMLP_PKG.cf_exempt_percformula(nvl ( jst.exemption_rate , 0 )) CF_EXEMPT_PERC
FROM
	JL_AR_AP_AWT_CERTIF 		jlc,
	JL_ZZ_AP_AWT_TYPES 		jat,
	AP_TAX_CODES			atc,
	JL_ZZ_AP_SUPP_AWT_TYPES		jst,
	PO_VENDORS			pov,
	XLE_FIRSTPARTY_INFORMATION_V         hrl,
	JL_ZZ_AP_COMP_AWT_TYPES		jct
WHERE	jlc.status<>'VOID'
AND	atc.name = jlc.tax_name
AND	pov.vendor_id = jlc.vendor_id
AND	jat.awt_type_code = jlc.awt_type_code
AND	hrl.legal_entity_id = jlc.legal_entity_id
AND	jct.legal_entity_id = hrl.legal_entity_id
AND	jct.awt_type_code = jat.awt_type_code
AND	jst.vendor_id = pov.vendor_id
AND	jst.awt_type_code = jat.awt_type_code
AND	jlc.awt_date between :p_start_date and :p_end_date
AND	jat.jurisdiction_type = :p_jurisdiction_type
AND	jat.awt_type_code >= nvl(:p_withholding_type_from,jat.awt_type_code)
AND	jat.awt_type_code <= nvl(:p_withholding_type_to,jat.awt_type_code)
AND 	jat.foreign_supplier_flag = decode(:p_jurisdiction_type, 'FEDERAL' , decode(:p_include_foreign_supp,'Y', jat.foreign_supplier_flag,:p_include_foreign_supp ), jat.foreign_supplier_flag)
ORDER BY document_date,
	CUIT_number
Parameter Name SQL text Validation
Start Date
 
Date
End Date
 
Date
Jurisdiction Type
 
LOV Oracle
Withholding Type From
 
LOV Oracle
Withholding Type To
 
LOV Oracle
Include Foreign Supplier Tax Types
 
LOV Oracle
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: