PER HRMS - Mouvement de Main d'Oeuvre

Description
Categories: BI Publisher
Columns: Person Id, Last Name, First Name, Date Of Birth, Sex, Nationality, Start Date, End Date, C Get Asg, Get Nationality ...
Application: Human Resources
Source: HRMS - Mouvement de Main d'Oeuvre Report (XML)
Short Name: PERFRMMO_XML
DB package: PER_PERFRMMO_XMLP_PKG
            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
Parameter Name SQL text Validation
Establishment
 
LOV Oracle
Period Start Date
 
Date
Period End Date
 
Date
Include Suspended
 
LOV Oracle
Session Date
 
Date
Business Group
 
Number