PER HRMS - Mouvement de Main d'Oeuvre

➜ Back to Library
Description
Categories: BI Publisher
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
Run PER HRMS - Mouvement de Main d'Oeuvre and other Oracle EBS reports with Blitz Report™ on our demo environment
            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
Download
Related Blitz Reports
PA Missing Timecards SSP ShPP Birth Element Results PSP Encumbrance History by Award PQH EEO5 Reporting PER Payroll Element Entries Upload PER COBRA Coverage BEN Back-out Life Events Summary PER EEO Individual Establishment PAY GB BIK Audit BEN Back-out Life Events Audit Log
Blitz Report™

Blitz Report™ provides multiple benefits: