ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471A051A9E05362FB09050512Y SELECT org.name ,people.employee_number ,people.full_name ,con.reference ,hr1.meaning type ,decode(con.ctr_information1,null,null,hr2.meaning) status ,con.active_start_date ,con.duration ,con.duration_units /*,NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration), 'W', (active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')) expiry_date,*/ ,fnd_date.date_to_displaydate(NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration), 'W', (active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')), calendar_aware=>1) expiry_date, PER_PERSACTR_XMLP_PKG.cf_1formula() CF_1 FROM per_all_assignments_f assg ,per_all_people people ,per_contracts con ,hr_lookups hr1 ,hr_lookups hr2 ,hr_all_organization_units org WHERE assg.person_id = people.person_id AND assg.ASSIGNMENT_TYPE='E' AND NVL(:P_DATE,sysdate) BETWEEN assg.effective_start_date AND assg.effective_end_date AND NVL(:P_DATE,sysdate) between people.effective_start_date AND people.effective_end_date AND assg.contract_id = con.contract_id AND hr1.lookup_type = 'CONTRACT_TYPE' AND hr1.lookup_code = con.type AND assg.organization_id = org.organization_id AND con.ctr_information_category = 'SA' AND nvl(con.ctr_information1,'BACHELOR_STATUS') = hr2.lookup_code (+) AND decode(:P_EMP_STATUS,null,nvl(con.ctr_information1,'0000'), :P_EMP_STATUS) = nvl(con.ctr_information1,'0000') AND hr2.lookup_type(+) = 'SA_EMPLOYMENT_STATUS' AND con.status like 'A-%' &l_org_condition &l_type_condition &l_status_condition AND org.business_group_id = :p_business_group_id AND NVL(to_date(ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration),'W',(active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')) BETWEEN NVL(:P_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, expiry_date, full_name PER PERSACTR_XML AR PER تقرير انتهاء صلاحية العقد في السعودية (بتنسيق XML) Imported from BI Publisher Description: تقرير انتهاء صلاحية العقد في السعودية Application: الموارد البشرية Source: تقرير انتهاء صلاحية العقد في السعودية (بتنسيق XML) Short Name: PERSACTR_XML DB package: PER_PERSACTR_XMLP_PKG US PER Saudi Contract Expiry Imported from BI Publisher Description: Saudi Contract Expiry Report Application: Human Resources Source: Saudi Contract Expiry Report (XML) Short Name: PERSACTR_XML DB package: PER_PERSACTR_XMLP_PKG BI Publisher &l_org_condition &l_status_condition &l_type_condition :cp_1 :l_org_condition :l_status_condition :l_type_condition :lp_date :p_business_group_id :p_con_type :p_conc_request_id :p_date :p_date_disp :p_emp_status :p_exp :p_exp_units :p_org_id :p_org_structure_id :p_org_structure_version_id 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y AR معرف مجموعة الأعمال US Business Group Id 2 10 :p_org_structure_id LOV Oracle HR_SA_ORG_STRUCTURES Y select organization_structure_id id, name||decode(business_group_id,null,' (*)',null) value, null description from per_organization_structures where business_group_id=fnd_global.per_business_group_id or (business_group_id is null and (select p.business_group_id from per_security_profiles p where p.security_profile_id=fnd_global.per_security_profile_id) is null) order by name AR هيكل المنشأة US Organization Structure 3 20 :p_org_structure_version_id LOV Oracle HR_SA_ORG_STRUCT_VERSIONS Y select org_structure_version_id id, to_char(version_number) value, fnd_date.date_to_displaydate(date_to,calendar_aware=>2) description from per_org_structure_versions where organization_structure_id=:$flex$.hr_sa_org_structures order by date_from desc Y AR النسخة US Version 4 30 :p_org_id LOV Oracle HR_SA_ORG_STRUCT_ELEMENTS Y select organization_id id, name value, null description from hr_organization_units where (:$flex$.hr_sa_org_structures:null is not null and exists (select 1 from per_org_structure_elements e where e.org_structure_version_id=:$flex$.hr_sa_org_struct_versions:null and (e.organization_id_parent=organization_id or e.organization_id_child=organization_id))) or (:$flex$.hr_sa_org_structures:null is null) and business_group_id=fnd_global.per_business_group_id order by name AR المنشأة US Organization 5 40 :p_con_type LOV Oracle HR_SA_CONTRACT_TYPE Y select lookup_code id, meaning value, null description from hr_lookups h,fnd_sessions s where lookup_type='CONTRACT_TYPE' and enabled_flag='Y' and session_id=userenv('SESSIONID') and s.effective_date between nvl(h.start_date_active,s.effective_date) and nvl(end_date_active,s.effective_date) order by meaning AR نوع العقد US Contract Type 6 50 :p_emp_status LOV Oracle HR_SA_EMPLOYMENT_STATUS Y select lookup_code id, meaning value, null description from hr_lookups h,fnd_sessions s where lookup_type='SA_EMPLOYMENT_STATUS' and enabled_flag='Y' and session_id=userenv('SESSIONID') and s.effective_date between nvl(h.start_date_active,s.effective_date) and nvl(end_date_active,s.effective_date) order by meaning AR حالة التوظيف US Employment Status 7 60 :p_exp Number Y AR انتهاء الصلاحية في US Expires In 8 70 :p_exp_units LOV Oracle HR_SA_DURATION_UNITS Y select lookup_code id, meaning value, null description from hr_lookups h,fnd_sessions s where lookup_type='QUALIFYING_UNITS' and enabled_flag='Y' and session_id=userenv('SESSIONID') and s.effective_date between nvl(h.start_date_active,s.effective_date) and nvl(end_date_active,s.effective_date) order by meaning Y AR وحدات انتهاء الصلاحية في US Expires In Units 9 80 :p_date Date select fnd_date.date_to_displaydate(effective_date) from fnd_sessions where session_id = userenv('SESSIONID') AR التاريخ US Date