ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718051A9E05362FB09050512Y select a.person_id, p.last_name, p.first_name, p.date_of_birth, p.sex, p.nationality, a.effective_start_date start_date, hr_fr_mmo.Get_end_date (a.person_id, a.establishment_id, a.effective_start_date, :P_INCLUDE_SUSPENDED) end_date, PER_PERFRMMO_XMLP_PKG.c_get_asgformula(a.person_id, a.effective_start_date, hr_fr_mmo.Get_end_date ( a.person_id , a.establishment_id , a.effective_start_date , :P_INCLUDE_SUSPENDED )) C_GET_ASG, PER_PERFRMMO_XMLP_PKG.c_get_nationalityformula(p.nationality) GET_NATIONALITY, PER_PERFRMMO_XMLP_PKG.C_JOB_p C_JOB, PER_PERFRMMO_XMLP_PKG.C_JOB_PCS_p C_JOB_PCS, PER_PERFRMMO_XMLP_PKG.C_START_REASON_p C_START_REASON, PER_PERFRMMO_XMLP_PKG.C_END_REASON_p C_END_REASON, PER_PERFRMMO_XMLP_PKG.C_CHECK_STARTED_p C_CHECK_STARTED, PER_PERFRMMO_XMLP_PKG.C_CHECK_LEFT_p C_CHECK_LEFT, PER_PERFRMMO_XMLP_PKG.C_EFFECTIVE_START_DATE_p C_EFFECTIVE_START_DATE from per_all_assignments_f a, per_all_people_f p, per_assignment_status_types s, per_person_types pt where a.business_group_id = :P_BUSINESS_GROUP_ID and a.establishment_id = :P_ESTABLISHMENT_ID and a.primary_flag = 'Y' and p.person_id = a.person_id and (a.effective_start_date between p.effective_start_date and p.effective_end_date) and (hr_fr_mmo.get_start_date (a.person_id, a.establishment_id, a.effective_end_date, :P_INCLUDE_SUSPENDED) = a.effective_start_date) and a.effective_start_date between :P_PERIOD_START_DATE and :P_PERIOD_END_DATE and (hr_fr_mmo.Get_end_date (a.person_id, a.establishment_id, a.effective_start_date, :P_INCLUDE_SUSPENDED) > :P_PERIOD_END_DATE) and a.assignment_status_type_id = s.assignment_status_type_id and ((:P_INCLUDE_SUSPENDED = 'N' and s.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s.per_system_status = 'SUSP_ASSIGN' or s.per_system_status = 'ACTIVE_ASSIGN')) and not exists (select null from per_all_assignments_f b, per_assignment_status_types s2 where b.person_id = a.person_id and b.establishment_id = :P_ESTABLISHMENT_ID and b.primary_flag = 'Y' and b.assignment_status_type_id = s2.assignment_status_type_id and s2.per_system_status = 'ACTIVE_ASSIGN' and ((:P_INCLUDE_SUSPENDED = 'N' and s2.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s2.per_system_status = 'SUSP_ASSIGN' or s2.per_system_status = 'ACTIVE_ASSIGN')) and b.effective_end_date = a.effective_start_date - 1 ) and pt.person_type_id = p.person_type_id and pt.business_group_id = p.business_group_id and pt.active_flag = 'Y' and 'Y' = pefrusdt.GET_TABLE_VALUE(:P_BUSINESS_GROUP_ID, 'FR_USER_PERSON_TYPE', 'INCLUDE_MMO_HEADCOUNT', pt.user_person_type, :P_SESSION_DATE) UNION select a.person_id, p.last_name, p.first_name, p.date_of_birth, p.sex, p.nationality, hr_fr_mmo.get_start_date (a.person_id, a.establishment_id, a.effective_end_date, :P_INCLUDE_SUSPENDED) start_date, a.effective_end_date end_date, PER_PERFRMMO_XMLP_PKG.c_get_asgformula(a.person_id, a.effective_start_date, hr_fr_mmo.Get_end_date ( a.person_id , a.establishment_id , a.effective_start_date , :P_INCLUDE_SUSPENDED )) C_GET_ASG, PER_PERFRMMO_XMLP_PKG.c_get_nationalityformula(p.nationality) GET_NATIONALITY, PER_PERFRMMO_XMLP_PKG.C_JOB_p C_JOB, PER_PERFRMMO_XMLP_PKG.C_JOB_PCS_p C_JOB_PCS, PER_PERFRMMO_XMLP_PKG.C_START_REASON_p C_START_REASON, PER_PERFRMMO_XMLP_PKG.C_END_REASON_p C_END_REASON, PER_PERFRMMO_XMLP_PKG.C_CHECK_STARTED_p C_CHECK_STARTED, PER_PERFRMMO_XMLP_PKG.C_CHECK_LEFT_p C_CHECK_LEFT, PER_PERFRMMO_XMLP_PKG.C_EFFECTIVE_START_DATE_p C_EFFECTIVE_START_DATE from per_all_assignments_f a, per_all_people_f p, per_assignment_status_types s, per_person_types pt where a.business_group_id = :P_BUSINESS_GROUP_ID and a.establishment_id = :P_ESTABLISHMENT_ID and a.primary_flag = 'Y' and p.person_id = a.person_id and (a.effective_end_date between p.effective_start_date and p.effective_end_date) and (hr_fr_mmo.Get_end_date (a.person_id, a.establishment_id, a.effective_start_date, :P_INCLUDE_SUSPENDED) = a.effective_end_date) and a.effective_end_date Between :P_PERIOD_START_DATE and :P_PERIOD_END_DATE and (hr_fr_mmo.get_start_date (a.person_id, a.establishment_id, a.effective_end_date, :P_INCLUDE_SUSPENDED) <:P_PERIOD_START_DATE) and a.assignment_status_type_id = s.assignment_status_type_id and (((:P_INCLUDE_SUSPENDED = 'N' and s.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s.per_system_status = 'SUSP_ASSIGN' or s.per_system_status = 'ACTIVE_ASSIGN'))) and pt.person_type_id = p.person_type_id and pt.business_group_id = p.business_group_id and pt.active_flag = 'Y' and 'Y' = pefrusdt.GET_TABLE_VALUE(p.business_group_id, 'FR_USER_PERSON_TYPE', 'INCLUDE_MMO_HEADCOUNT', pt.user_person_type, :P_SESSION_DATE) and not exists (select null from per_all_assignments_f b, per_assignment_status_types s2 where b.person_id = a.person_id and b.establishment_id = :P_ESTABLISHMENT_ID and b.primary_flag = 'Y' and b.assignment_status_type_id = s2.assignment_status_type_id and (((:P_INCLUDE_SUSPENDED = 'N' and s2.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s2.per_system_status = 'SUSP_ASSIGN' or s2.per_system_status = 'ACTIVE_ASSIGN'))) and b.effective_start_date - 1 = a.effective_end_date ) UNION ( select a.person_id, p.last_name, p.first_name, p.date_of_birth, p.sex, p.nationality, a.effective_start_date start_date, hr_fr_mmo.Get_end_date (a.person_id, a.establishment_id, a.effective_start_date, :P_INCLUDE_SUSPENDED) end_date, PER_PERFRMMO_XMLP_PKG.c_get_asgformula(a.person_id, a.effective_start_date, hr_fr_mmo.Get_end_date ( a.person_id , a.establishment_id , a.effective_start_date , :P_INCLUDE_SUSPENDED )) C_GET_ASG, PER_PERFRMMO_XMLP_PKG.c_get_nationalityformula(p.nationality) GET_NATIONALITY, PER_PERFRMMO_XMLP_PKG.C_JOB_p C_JOB, PER_PERFRMMO_XMLP_PKG.C_JOB_PCS_p C_JOB_PCS, PER_PERFRMMO_XMLP_PKG.C_START_REASON_p C_START_REASON, PER_PERFRMMO_XMLP_PKG.C_END_REASON_p C_END_REASON, PER_PERFRMMO_XMLP_PKG.C_CHECK_STARTED_p C_CHECK_STARTED, PER_PERFRMMO_XMLP_PKG.C_CHECK_LEFT_p C_CHECK_LEFT, PER_PERFRMMO_XMLP_PKG.C_EFFECTIVE_START_DATE_p C_EFFECTIVE_START_DATE from per_all_assignments_f a, per_all_people_f p, per_assignment_status_types s, per_person_types pt where a.business_group_id = :P_BUSINESS_GROUP_ID and a.establishment_id = :P_ESTABLISHMENT_ID and a.primary_flag = 'Y' and p.person_id = a.person_id and (a.effective_start_date between p.effective_start_date and p.effective_end_date) and (hr_fr_mmo.get_start_date (a.person_id, a.establishment_id, a.effective_end_date, :P_INCLUDE_SUSPENDED) = a.effective_start_date) and a.effective_start_date between:P_PERIOD_START_DATE and :P_PERIOD_END_DATE and (hr_fr_mmo.Get_end_date (a.person_id, a.establishment_id, a.effective_start_date, :P_INCLUDE_SUSPENDED) between :P_PERIOD_START_DATE and :P_PERIOD_END_DATE) and a.assignment_status_type_id = s.assignment_status_type_id and (((:P_INCLUDE_SUSPENDED = 'N' and s.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s.per_system_status = 'SUSP_ASSIGN' or s.per_system_status = 'ACTIVE_ASSIGN'))) and not exists (select null from per_all_assignments_f b, per_assignment_status_types s2 where b.person_id = a.person_id and b.establishment_id = :P_ESTABLISHMENT_ID and b.primary_flag = 'Y' and b.assignment_status_type_id = s2.assignment_status_type_id and s2.per_system_status = 'ACTIVE_ASSIGN' and (((:P_INCLUDE_SUSPENDED = 'N' and s2.per_system_status = 'ACTIVE_ASSIGN') or (:P_INCLUDE_SUSPENDED = 'Y' and s2.per_system_status = 'SUSP_ASSIGN' or s2.per_system_status = 'ACTIVE_ASSIGN'))) and b.effective_end_date = a.effective_start_date - 1 ) and pt.person_type_id = p.person_type_id and pt.business_group_id = p.business_group_id and pt.active_flag = 'Y' and 'Y' = pefrusdt.GET_TABLE_VALUE(p.business_group_id, 'FR_USER_PERSON_TYPE', 'INCLUDE_MMO_HEADCOUNT', pt.user_person_type, :P_SESSION_DATE) ) order by 2,3,7 DESC, 8 PER PERFRMMO_XML F PER HRMS - Etat Mouvement de main-d'oeuvre Imported from BI Publisher Description: HRMS - Etat Mouvement de main-d'oeuvre Application: Human Resources Source: HRMS - Etat Mouvement de main-d'oeuvre (XML) Short Name: PERFRMMO_XML DB package: PER_PERFRMMO_XMLP_PKG US PER HRMS - Mouvement de Main d'Oeuvre Imported from BI Publisher Description: HRMS - Mouvement de Main d'Oeuvre Report Application: Human Resources Source: HRMS - Mouvement de Main d'Oeuvre Report (XML) Short Name: PERFRMMO_XML DB package: PER_PERFRMMO_XMLP_PKG BI Publisher :c_check_left :c_check_started :c_count_men :c_count_period :c_count_previous_period :c_count_women :c_effective_start_date :c_end_reason :c_job :c_job_pcs :c_start_reason :c_temporary :p_business_group_id :p_conc_request_id :p_establishment_id :p_formula_id :p_include_suspended :p_period_end_date :p_period_start_date :p_session_date 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y F Entité professionnelle ID entité professionnelle US Business Group Business Group ID 2 10 :p_establishment_id LOV Oracle HR_FR_ESTABLISHMENTS Y select organization_id id, name value, null description from hr_organization_units where business_group_id=fnd_global.per_business_group_id and organization_id in (select organization_id from hr_organization_information where org_information1='FR_ETABLISSEMENT' and org_information_context='CLASS') order by value,description Y F Etablissement ID établissement US Establishment Establishment Id 3 20 :p_period_start_date Date select fnd_date.date_to_displaydate(to_date('01-'||to_char(sysdate,'MM-YYYY'),'DD-MM-YYYY')) from dual Y F Date de début de période Date de début de période US Period Start Date Period Start Date 4 30 :p_period_end_date Date select fnd_date.date_to_displaydate(last_day(sysdate)) from dual Y F Date de fin de période Date de fin de période US Period End Date Period End Date 5 40 :p_include_suspended LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_code='Y' and lookup_type ='YES_NO' Y F Inclure en attente Inclure en attente US Include Suspended Include suspended 6 50 :p_session_date Date select fnd_date.date_to_displaydate(sysdate) from dual Y F Date de la session Date de la session US Session Date Session date