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 |