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
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