PER HRMS - Mouvement de Main d'Oeuvre
Description
- Categories
- BI Publisher
- Application
- Human Resources
- Source
- HRMS - Mouvement de Main d'Oeuvre Report (XML)
- Short Name
- PERFRMMO_XML
- DB package
- PER_PERFRMMO_XMLP_PKG
Imported from BI Publisher
HRMS - Mouvement de Main d'Oeuvre Report
HRMS - Mouvement de Main d'Oeuvre Report
SQL 200 lines
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 |
Parameters 5
| Parameter | Type | List of values SQL |
|---|---|---|
| Establishment | LOV Oracle | |
| Period Start Date | Date | |
| Period End Date | Date | |
| Include Suspended | LOV Oracle | |
| Session Date | Date |