JL Argentine Payables Withholding Flat File - draft

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