JE Italian Payables Exemption Letter Process - draft

Description
Categories: BI Publisher
Application: European Localizations
Source: Italian Payables Exemption Letter Process (XML) - Not Supported: Reserved For Future Use
Short Name: JEITAPEX_XML
DB package: JE_JEITAPEX_XMLP_PKG
SELECT 	jiel.vendor_id                                                   vendor_id,
                jiel.exemption_letter_id					letter_id,
	jiel.year							year,
	decode(jiel.clause_ref,'9',jiel.clause_ref||', 2^ comma,',jiel.clause_ref)	clause_ref,
	to_char(jiel.effective_from,'DD-MON-RRRR')				effective_from,
	to_char(jiel.effective_to,'DD-MON-RRRR')                        			effective_to,
	substr(pv.vendor_name,1,40)                                			envelope_name,
	decode(jiel.custom_flag,'Y',substr(pv.vendor_name,1,40), null)		custom_name,
	decode(jiel.custom_flag,'Y',null,substr(pv.vendor_name,1,40))		vendor_name,
	pvs.address_line1						vendor_street,
	pvs.zip||' '||pvs.city							vendor_zip,
    pv.party_id PartyId,
    hp.party_type ptype,
    pv.vat_registration_num regnum,
    nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)) taxpay,
(select geography_name
                          from hz_geographies
                         where geography_code = pvs.country
                           and   geography_type='COUNTRY') Country,
	substr(pvs.city||' '||pvs.province,1,30)				vendor_citycntry,
	jiel.rowid							print_rowid,
	jiel.limit_amount						exempt_amount,
	jiel.letter_type						letter_type,
	JE_JEITAPEX_XMLP_PKG.print_commitformula(jiel.rowid) print_commit,
	JE_JEITAPEX_XMLP_PKG.cf_person_profileformula(hp.party_type, pv.party_id) CF_person_profile
FROM	je_it_exempt_letters   	jiel,
	po_vendors      		pv,
	hz_parties hp,
	po_vendor_sites_all  		pvs,
              (SELECT distinct person_id
               ,national_identifier
              FROM PER_ALL_PEOPLE_F) papf
WHERE
	jiel.vendor_id   		=   pv.vendor_id
 AND      nvl(pv.employee_id,-99)  = papf.person_id (+)
AND	pv.vendor_id  	 	=   pvs.vendor_id
AND         pv.party_id = hp.party_id
AND	pvs.tax_reporting_site_flag	=  'Y'
AND         pvs.country = :C_COUNTRY_CODE
AND	jiel.print_flag  		=  'Y'
AND	100 = :force_order
AND         jiel.legal_entity_id = :P_LEGAL_ENTITY_ID_V
AND	trunc(jiel.issue_date) between trunc(:p_first_day) and trunc(:p_last_day)
ORDER BY substr(pv.vendor_name,1,40),
	jiel.exemption_letter_id
Parameter Name SQL text Validation
Period_Set_Name
 
Province
 
Ledger ID
 
Number
Period To
 
LOV Oracle
Period From
 
LOV Oracle
Reporting Identifier
 
LOV Oracle
Legal Entity
 
LOV Oracle