JL Colombian Payables Withholding Certificate - draft
Description
Categories: BI Publisher
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
Source: Colombian Payables Withholding Certificate (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOPCWT_XML
DB package: JL_JLCOPCWT_XMLP_PKG
Run
JL Colombian Payables Withholding Certificate - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Reporting Level |
|
LOV Oracle | |
| Reporting Context |
|
LOV Oracle | |
| Legal Entity Name |
|
LOV Oracle | |
| Start Date |
|
Date | |
| End Date |
|
Date | |
| Withholding Tax Type |
|
LOV Oracle | |
| Supplier Name |
|
LOV Oracle | |
| Supplier Name Range From |
|
LOV Oracle | |
| Supplier Name Range To |
|
LOV Oracle | |
| Supplier Number Range From |
|
LOV Oracle | |
| Supplier Number Range To |
|
LOV Oracle |
