ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F1793869E053B46B63588994 SELECT Decode(atc.global_attribute19, NULL,'Non-VAT','VAT') Certificate_Type ,atc.global_attribute4 Withholding_Type ,flo.meaning concept ,decode(atc.global_attribute19, NULL , '',atc.name) Tax_name ,decode (atc.global_attribute19, NULL, sum(decode(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))), 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*nvl(aid.exchange_rate,1)))) ) total_amount ,nvl(atc.global_attribute19,'') VAT_Rate ,decode(atc.global_attribute19, NULL, sum(nvl(aid.base_amount, aid.amount) *(-1)), sum(nvl(nvl(aid.base_amount, aid.amount) * (-1),0))) withholding_amount ,nvl(pv2.vendor_id,pv1.vendor_id) vendor_id ,nvl(pv2.segment1,pv1.segment1) vendor_number ,nvl(pv2.vendor_name,pv1.vendor_name) vendor_name ,pvs.address_line1 address_line1 ,pvs.address_line2 address_line2 ,pvs.address_line3 address_line3 ,pvs.city||'-'||pvs.zip address_line4 ,nvl(nvl(papf2.national_identifier,nvl(pv2.individual_1099,pv2.num_1099))||'-'||pv2.global_attribute12, nvl(papf.national_identifier,nvl(pv1.individual_1099,pv1.num_1099))||'-'||pv1.global_attribute12) vendor_NIT ,xle.name company_name ,xle.address_line_1 Company_address_line1 ,xle.address_line_2 Company_address_line_2 ,xle.address_line_3 Company_address_line_3 ,xle.town_or_city ||'-'||xle.postal_code Company_address_line_4 ,xle.registration_number Company_NIT_number ,fnd3.Geography_name city_done ,fnd2.geography_name city_paid ,jat.certificate_header certificate_header, JL_JLCOPCWT_XMLP_PKG.cf_date_fromformula() CF_DATE_FROM, JL_JLCOPCWT_XMLP_PKG.cf_date_toformula() CF_DATE_TO, JL_JLCOPCWT_XMLP_PKG.cf_vat_amountformula(Decode ( atc.global_attribute19 , NULL , 'Non-VAT' , 'VAT' ), decode ( atc.global_attribute19 , NULL , '' , atc.name ), nvl ( pv2.vendor_id , pv1.vendor_id ), nvl ( pv2.segment1 , pv1.segment1 )) CF_VAT_AMOUNT FROM hz_geographies fnd3 ,hz_geographies fnd2 ,hr_locations_all hr2 ,hr_locations_all hr ,xle_firstparty_information_v xle ,jl_zz_ap_comp_awt_types jcat ,jl_zz_ap_awt_types jat ,ap_tax_codes_all atc ,fnd_lookups flo ,ap_awt_tax_rates_all atr ,ap_suppliers pv1 ,ap_suppliers pv2 ,ap_supplier_sites_all pvs ,ap_invoice_distributions_all aid ,ap_invoices_all inv1 ,(select distinct person_id ,national_identifier from per_all_people_f) papf ,(select distinct person_id ,national_identifier from per_all_people_f) papf2 WHERE &P_Selected_Dates aid.match_status_flag = 'A' and nvl(pv1.employee_id, -99) = papf.person_id (+) and nvl(pv2.employee_id, -99) = papf2.person_id (+) and aid.line_type_lookup_code = 'AWT' and atr.tax_rate_id (+) = aid.awt_tax_rate_id and aid.withholding_tax_code_id = atc.tax_id and flo.lookup_type = 'JLCO_TAX_AUTHORITY_CATEGORY' and atc.global_attribute5 = flo.lookup_code and pvs.vendor_id = nvl(pv2.vendor_id,pv1.vendor_id) and pvs.global_attribute17 = 'Y' and jcat.location_id = hr.location_id and jcat.awt_type_code = jat.awt_type_code and jat.awt_type_code = atc.global_attribute4 &lp_ap_invoices_wh and inv1.legal_entity_id = xle.legal_entity_id and jcat.legal_entity_id = xle.legal_entity_id &P_Awt_Type_Code and hr2.location_id = pvs.bill_to_location_id and fnd3.geography_name = upper( hr2.town_or_city) and fnd3.geography_element2_code = hr2.region_2 and fnd3.country_code = hr2.country and fnd2.geography_name = upper( jcat.payment_city) and fnd2.geography_element2_code = hr.region_2 and fnd2.country_code = hr.country and fnd2.geography_type = 'CITY' and fnd3.geography_type = 'CITY' and aid.invoice_id = inv1.invoice_id and inv1.cancelled_date is NULL and inv1.vendor_id = pv1.vendor_id and aid.global_attribute2 = pv2.segment1 (+) &P_Selected_Suppliers GROUP BY atc.global_attribute4 ,flo.meaning ,decode(atc.global_attribute19, NULL,'', atc.name) ,atc.global_attribute19 ,nvl(pv2.vendor_id,pv1.vendor_id) ,nvl(pv2.segment1,pv1.segment1) ,nvl(pv2.vendor_name,pv1.vendor_name) ,pvs.address_line1 ,pvs.address_line2 ,pvs.address_line3 ,pvs.city ,pvs.zip ,nvl(nvl(papf2.national_identifier,nvl(pv2.individual_1099,pv2.num_1099))||'-'||pv2.global_attribute12, nvl(papf.national_identifier,nvl(pv1.individual_1099,pv1.num_1099))||'-'||pv1.global_attribute12) , xle.name , xle.address_line_1 , xle.address_line_2 , xle.address_line_3 , xle.town_or_city ||'-'|| xle.postal_code , xle.registration_number ,fnd3.Geography_name ,fnd2.geography_name ,jat.certificate_header ORDER BY 24 ASC,22 ASC,2 ASC,23 ASC,16 ASC,17 ASC,18 ASC,19 ASC,20 ASC,21 ASC,10 ASC,11 ASC,12 ASC,13 ASC,14 ASC,15 ASC,1 ASC,8 ASC,9 ASC JL JLCOPCWT_XML US JL Colombian Payables Withholding Certificate - draft Application: Latin America Localizations Source: Colombian Payables Withholding Certificate (XML) - Not Supported: Reserved For Future Use Short Name: JLCOPCWT_XML DB package: JL_JLCOPCWT_XMLP_PKG BI Publisher &lp_ap_invoices_wh &p_awt_type_code &p_selected_dates &p_selected_suppliers :c_base_currency_code :c_base_description :c_base_min_acct_unit :c_base_precision :c_chart_of_accounts_id :c_company_name_header :c_location_id :c_nls_all :c_nls_end_of_report :c_nls_no :c_nls_no_data_exists :c_nls_yes :c_profile_country_code :c_report_run_time :c_report_start_date :lp_ap_invoice_distributions_wh :lp_ap_invoices_wh :p_awt_type :p_awt_type_code :p_conc_request_id :p_date_from :p_date_to :p_debug_switch :p_legal_entity_id :p_location_id :p_min_precision :p_orgs :p_reporting_context :p_reporting_level :p_selected_dates :p_selected_suppliers :p_supp_num_from :p_supp_num_from_1 :p_supp_num_to :p_supp_num_to_1 :p_supplier_from :p_supplier_from_1 :p_supplier_id :p_supplier_to :p_supplier_to_1 1 10 :p_reporting_level LOV Oracle XLE_LEGAL_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' order by value,description Y US Reporting Level 2 20 :p_reporting_context LOV Oracle XLE_LEGAL_REPORTING_CONTEXT Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.xle_legal_reporting_level order by value,description Y US Reporting Context 3 30 :p_legal_entity_id LOV Oracle ZX_COMPANY_NAME Y select legal_entity_id id, legal_entity_name value, null description from gl_ledger_le_v where :$flex$.xle_legal_reporting_level='1000' and ledger_id=:$flex$.xle_legal_reporting_context order by value,description US Legal Entity Name 4 40 :p_date_from Date Y US Start Date 5 50 :p_date_to Date Y US End Date 6 60 :p_awt_type LOV Oracle JLZZ_AP_WITHHOLDING_TYPE_REP Y select awt_type_code id, awt_type_code value, description description from jl_zz_ap_awt_types order by awt_type_code US Withholding Tax Type 7 70 :p_supplier_id LOV Oracle AP_SRS_VENDOR_NAME_NUM Y Y select vendor_id id, vendor_name value, null description from po_vendors order by value,description US Supplier Name 8 80 :p_supplier_from LOV Oracle AP_SRS_VENDOR_NAME_CHAR Y select po.vendor_name id, po.vendor_name value, null description from po_vendors po where po.vendor_id= (select max(po2.vendor_id) from po_vendors po2 where po2.vendor_name=po.vendor_name) order by po.vendor_name US Supplier Name Range From 9 90 :p_supplier_to LOV Oracle AP_SRS_VENDOR_NAME_CHAR Y select po.vendor_name id, po.vendor_name value, null description from po_vendors po where po.vendor_id= (select max(po2.vendor_id) from po_vendors po2 where po2.vendor_name=po.vendor_name) order by po.vendor_name US Supplier Name Range To 10 100 :p_supp_num_from LOV Oracle AP_SRS_VENDOR_NUMBER Y select segment1 id, segment1 value, null description from po_vendors order by segment1 US Supplier Number Range From 11 110 :p_supp_num_to LOV Oracle AP_SRS_VENDOR_NUMBER Y select segment1 id, segment1 value, null description from po_vendors order by segment1 US Supplier Number Range To :$flex$.xle_legal_reporting_context Reporting Context Legal Entity Name :$flex$.xle_legal_reporting_level Reporting Level Legal Entity Name :$flex$.xle_legal_reporting_level Reporting Level Reporting Context