Select jaac.awt_type_code awt_type_code,
rtrim(substr(jaac.bank_account_num,1,15),' ')||
' - '||rtrim(substr(jaac.payment_document_name,1,15),' ')||
' - '||rtrim(substr(jaac.check_number,1,15),' ') payment_reference,
jaac.payment_document_name payment_document_name,
jaac.check_number check_number,
jaac.certificate_number certificate_number,
jaac.certificate_number certificate_number_NUM,
jaac.awt_date withholding_date,
jaac.tax_name tax_name,
(jaac.withholding_amount*(-1) + nvl(jaac.credit_amount,0)) withholding_amount,
jaac.credit_amount * (-1) credit_amount,
abs(jaac.taxable_base_amount) taxable_base_amount,
(jaac.withholding_amount* (-1)) total_awt_amount,
jzat.jurisdiction_type jurisdiction_type,
jzat.description description,
jzat.taxable_base_amount_basis taxable_base_amount_basis,
jzat.certificate_header certificate_header,
jzat.credit_letter_flag credit_letter_flag,
atc.global_attribute7 zone,
atc.description description_tax_name,
jzat.description withholding_type_description,
xle.name Company_name,
xle.address_line_1 Company_address_line_1,
xle.address_line_2 Company_address_line_2,
xle.address_line_3 Company_address_line_3,
xle.country||'-'||xle.postal_code||'-'||fl.meaning Company_address_line_4,
xle.registration_number CUIT_or_Tax_Id_Number_13,
pv1.vendor_name Supplier_Name,
pvs.address_line1 Supplier_address_line_1,
pvs.address_line2 Supplier_address_line_2,
pvs.address_line3 Supplier_address_line_3,
pvs.city||' - '||pvs.zip||' - '||pvs.country Supplier_address_line_4,
substr(replace(pv1.num_1099,'-'),1,2)||'-'||substr(replace(pv1.num_1099,'-'),3)||'-'||pv1.global_attribute12 Supplier_CUIT,
jzat.province_code province_code,
xle.registration_number comp_primary_id_number,
jzat.tax_authority_id comp_tax_authority_id,
jcat.tax_authority_type comp_tax_authority_type,
pv1.num_1099 supp_primary_id_number,
jzat.tax_authority_id supp_tax_authority_id,
jsat.tax_authority_type supp_tax_authority_type,
JL_JLARPCWT_XMLP_PKG.cf_payment_amountformula(jaac.check_number) CF_Payment_amount,
JL_JLARPCWT_XMLP_PKG.cf_1formula(jzat.jurisdiction_type, jzat.taxable_base_amount_basis, jaac.awt_type_code, jzat.credit_letter_flag) CF_Certificate_type,
JL_JLARPCWT_XMLP_PKG.cf_comp_num_agent_retencion7fo(:CF_Certificate_type, xle.registration_number, jzat.tax_authority_id, jcat.tax_authority_type) CF_comp_num_agent_ret,
JL_JLARPCWT_XMLP_PKG.cf_supp_province_numberformula(:CF_Certificate_type, pv1.num_1099, jzat.tax_authority_id, jsat.tax_authority_type, jaac.awt_date) CF_supp_province_number,
JL_JLARPCWT_XMLP_PKG.province_nameformula(:CF_Certificate_type, jzat.province_code) CF_province_name
, JL_JLARPCWT_XMLP_PKG.cf_1formula0039(jaac.awt_type_code, :CERTIFICATE_NUMBER_NUM) CF_UPDATE_CERTIFICATES
From
jl_zz_ap_supp_awt_types jsat ,
po_vendor_sites_all pvs,
po_vendors pv1,
jl_zz_ap_awt_types jzat,
jl_ZZ_ap_comp_awt_types jcat,
xle_firstparty_information_v xle,
ap_tax_codes_all atc,
jl_ar_ap_awt_certif_all jaac,
fnd_lookups fl
where
jaac.awt_date >= nvl(:P_DATE_FROM,jaac.awt_date)
and jaac.awt_date < = nvl(:P_DATE_TO+1,jaac.awt_date +1)
and jaac.status <> 'VOID'
and jaac.checkrun_name = nvl(:P_CHECKRUN_NAME,jaac.checkrun_name)
and jaac.awt_type_code = nvl(:P_AWT_TYPE_CODE,jaac.awt_type_code)
and NVL(jaac.payment_instruction_id,-99) = nvl(:p_payment_instruction, nvl(jaac.payment_instruction_id,-99))
and atc.name = jaac.tax_name
and jaac.awt_type_code = jzat.awt_type_code
and jcat.awt_type_code = jzat.awt_type_code
and pvs.global_attribute17 = 'Y'
and pvs.vendor_id = pv1.vendor_id
and jsat.awt_type_code = jzat.awt_type_code
and jsat.vendor_id = pv1.vendor_id
and pv1.vendor_id = jaac.vendor_id
and :p_ok_parameter_flag = 'Y'
and xle.legal_entity_id = jcat.legal_entity_id(+)
and jaac.legal_entity_id = jcat.legal_entity_id
&lp_jl_ar_ap_awt_certif_all_wh
and fl.lookup_type = 'JLZZ_CITY'
and NVL(fl.start_date_active, SYSDATE) <= SYSDATE
and NVL(fl.end_date_active, SYSDATE) >= SYSDATE
and fl.enabled_flag = 'Y'
and fl.lookup_code = xle.town_or_city
&P_Selected_Suppliers
ORDER BY pv1.vendor_name,
jzat.awt_type_code,
jaac.certificate_number |