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 |
