Query to get HR Contingent Worker – Oracle EBS to Fusion

Introduction:

This blog has the SQL query that can be used to pull the HR Contingent Worker details per country for EBS to Cloud data migration.

SQL Query :

SELECT   DISTINCT

          papf.employee_number             personnumber,

          fu.user_id,

          FU.USER_NAME,

          papf.start_date                  startdate,

 

        (SELECT   ffv.description

            FROM   apps.FND_ID_FLEX_SEGMENTS S,

                   apps.FND_FLEX_VALUE_SETS VS , apps.FND_FLEX_VALUES_VL ffv

            WHERE  S.id_flex_num = gcc.chart_of_accounts_id

            AND            S.application_id = 101

            AND            S.id_flex_code = ‘GL#’

            AND            S.segment_num = ‘1’

            AND            S.enabled_flag = ‘Y’  AND ffv.flex_value =gcc.segment1

            AND            VS.flex_value_set_id = S.flex_value_set_id

            AND s.flex_value_set_id =ffv.flex_value_set_id)               LegalEmployerName,

 

 

        ‘E’                              WorkerType,

        NULL                             JobCode,

       ‘HIRE’                            ActionCode,

        hl.country       BUSINESSUNITSHORTCODE,

        papf.last_name                   lastname,

        papf.first_name                  firstname,

        papf.middle_names                middlenames,

        paaf.organization            DEPARTMENT,

       mgr.employee_number              MANAGER ,

        gcc.segment1 ||’.’|| gcc.segment2 ||’.’|| gcc.segment3 ||’.’|| gcc.segment4 ||’.’||gcc.segment5 ||’.’|| gcc.segment6||’.’|| gcc.segment7 ||’.’|| gcc.segment8||’.’|| gcc.segment9 DefaultExpenseAccount,

        papf.effective_start_date        effectivestartdate,

        TO_CHAR(papf.effective_end_date, ‘DD-MON-YYYY’)              EFFECTIVEENDDATE,

        NVL(papf.email_address,’noemail@XX.com’)    PersonEmail,

        paaf.LOCATION_CODE              LOCATIONCODE,

        hl.country                       LegislationCode

 

   FROM apps.PER_ALL_PEOPLE_F papf,

              apps.PER_ASSIGNMENTS_V2 paaf,

              apps.per_all_people_f mgr,

             apps.GL_CODE_COMBINATIONS gcc,

             apps.hr_locations hl,

           apps.per_all_assignments_f paa ,

           FNd_USER fu

 

  WHERE 1=1

    AND papf.person_id = paaf.person_id

    AND paaf.default_code_comb_id = gcc.code_combination_id(+)

    AND mgr.person_id(+) = paaf.supervisor_id

    AND sysdate between mgr.effective_start_date(+) AND NVL(mgr.effective_end_date, SYSDATE+1)

    AND sysdate between papf.effective_start_date AND NVL(papf.effective_end_date, SYSDATE+1)

    AND paaf.location_id = hl.location_id(+)

    AND paaf.person_id = paa.person_id

    AND HL.COUNTRY not in (‘xx’)

    AND FU.employee_id=papf.person_id

    and (upper(fu.user_name) like ‘%.PRT’ or UPPER(fu.user_name) like ‘%.CTR’ or

    upper(fu.user_name) like ‘%.PTR’ or UPPER(fu.user_name) like ‘%.CONTR’ or

    upper(fu.user_name) like ‘%.PRTNR’ or UPPER(fu.user_name) like ‘%.C’ or UPPER(fu.user_name) like ‘%.P’ or papf.email_address like ‘%.contr@XX.com’

    or papf.email_address like ‘%.prtnr@XX.com’);