PER Saudi Iqama Expiry

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Saudi Iqama Expiry Report (XML)
Short Name: PERSAIQM_XML
DB package: PER_PERSAIQE_XMLP_PKG
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
Type
 
Business Group Id
 
Number
Date
 
Date
Expires In Units
 
LOV Oracle
Expires In
 
Number
Organization
 
LOV Oracle
Version
 
LOV Oracle
Organization Hierarchy
 
LOV Oracle