PER Saudi Iqama Expiry

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Saudi Iqama Expiry Report
Application: Human Resources
Source: Saudi Iqama Expiry Report (XML)
Short Name: PERSAIQM_XML
DB package: PER_PERSAIQE_XMLP_PKG
Run PER Saudi Iqama Expiry and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT org.name
              ,people.employee_number
              ,people.full_name
              ,pei.pei_information1 ip_number
              ,pei.pei_information2 profession
              ,pei.pei_information5 place_of_issue
              ,pei.pei_information4 expiry_date
              , fnd_date.canonical_to_date(pei.pei_information7) greg_expiry_date,
	PER_PERSAIQE_XMLP_PKG.cf_1formula() CF_1
FROM   per_all_assignments_f assg
             ,per_all_people_f     people
            ,per_people_extra_info pei
             ,hr_all_organization_units   org
WHERE assg.person_id = people.person_id
AND   assg.ASSIGNMENT_TYPE='E'
AND     NVL(:p_r_date,sysdate) BETWEEN assg.effective_start_date
                  AND   assg.effective_end_date
AND     NVL(:p_r_date,sysdate) between people.effective_start_date
                  AND   people.effective_end_date
AND    pei.person_id = people.person_id
AND    pei.information_type = :p_type
and pei.pei_information_category =:p_type
AND    assg.organization_id = org.organization_id
&l_org_condition
AND org.business_group_id = :p_business_group_id
AND pei.pei_information4 is not null
AND  (fnd_date.canonical_to_date(pei_information7))  BETWEEN NVL(:p_r_date,sysdate) AND DECODE(:P_EXP_UNITS,'D',(nvl(:P_DATE,sysdate)+:P_EXP),
					'W',(nvl(:P_DATE,sysdate)+(:P_EXP*7)),
					'M',(add_months(nvl(:P_DATE,sysdate),:P_EXP)),
					'Y',(add_months(nvl(:P_DATE,sysdate),(:P_EXP*12))))
ORDER BY org.name, greg_expiry_date, full_name
Parameter Name SQL text Validation
Organization Hierarchy
 
LOV Oracle
Version
 
LOV Oracle
Organization
 
LOV Oracle
Expires In
 
Number
Expires In Units
 
LOV Oracle
Date
 
Date
Blitz Report™