APPS.PER_PERUSEO1_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 34

    SELECT
      PGH.NAME,
      PGV.VERSION_NUMBER,
      PGN.ENTITY_ID,
      PGN.HIERARCHY_NODE_ID
    INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
    FROM
      PER_GEN_HIERARCHY PGH,
      PER_GEN_HIERARCHY_VERSIONS PGV,
      PER_GEN_HIERARCHY_NODES PGN
    WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
      AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
      AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
      AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
      AND PGN.NODE_TYPE = 'PAR';

Line: 49

    SELECT
      COUNT('h_node')
    INTO C_NO_OF_ESTABLISHMENTS
    FROM
      PER_GEN_HIERARCHY_NODES PGHN
    WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
      AND PGHN.NODE_TYPE = 'EST';

Line: 59

      SELECT
        null
      INTO L_DUMMY
      FROM
        HR_ALL_ORGANIZATION_UNITS
      WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
        AND LOCATION_ID is not null;

Line: 75

      SELECT
        null
      INTO L_DUMMY
      FROM
        HR_ORGANIZATION_INFORMATION
      WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
        AND ORG_INFORMATION_CONTEXT = 'EEO_Spec';

Line: 91

      SELECT
        null
      INTO L_DUMMY
      FROM
        HR_LOCATION_EXTRA_INFO HLEI1,
        HR_LOCATION_EXTRA_INFO HLEI2,
        PER_GEN_HIERARCHY_NODES PGN,
        HR_LOCATIONS_ALL ELOC
      WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
        AND PGN.NODE_TYPE = 'EST'
        AND ELOC.LOCATION_ID = PGN.ENTITY_ID
        AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
        AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
        AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
        AND HLEI2.LOCATION_ID = ELOC.LOCATION_ID
        AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
        AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information';

Line: 119

      SELECT
        ELOC.LOCATION_ID,
        ELOC.LOCATION_CODE
      INTO L_LOCATION_ID,L_LOCATION_CODE
      FROM
        HR_LOCATION_EXTRA_INFO HLEI1,
        PER_GEN_HIERARCHY_NODES PGN,
        HR_LOCATIONS_ALL ELOC
      WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
        AND PGN.NODE_TYPE = 'EST'
        AND ELOC.LOCATION_ID = PGN.ENTITY_ID
        AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
        AND HLEI1.INFORMATION_TYPE = 'EEO-1 Archive Information'
        AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';

Line: 158

        SELECT
          '1',
          ELOC.LOCATION_ID,
          ELOC.LOCATION_CODE
        INTO L_DUMMY,L_LOCATION_ID,L_LOCATION_CODE
        FROM
          HR_LOCATION_EXTRA_INFO HLEI1,
          PER_GEN_HIERARCHY_NODES PGN,
          HR_LOCATIONS_ALL ELOC
        WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
          AND PGN.NODE_TYPE = 'EST'
          AND ELOC.LOCATION_ID = PGN.ENTITY_ID
          AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
          AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
          AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
          AND HLEI1.LEI_INFORMATION9 = 'Y';

Line: 184

          SELECT
            2
          INTO L_DUMMY
          FROM
            HR_LOCATION_EXTRA_INFO
          WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
            AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';

Line: 269

      SELECT
        ORG_INFORMATION1,
        ORG_INFORMATION2,
        ORG_INFORMATION3,
        ORG_INFORMATION4,
        ORG_INFORMATION5,
        ORG_INFORMATION6,
        ORG_INFORMATION8,
        ORG_INFORMATION9,
        ORG_INFORMATION11,
        ORG_INFORMATION12
      FROM
        HR_ORGANIZATION_INFORMATION
      WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
        AND ORG_INFORMATION_CONTEXT = 'VETS_EEO_Dup';

Line: 313

    SELECT
      COUNT(PEO.PERSON_ID)
    INTO L_COUNT_EMPS
    FROM
      PER_ALL_ASSIGNMENTS_F ASS,
      PER_ALL_PEOPLE_F PEO,
      PER_JOBS_VL JOB
    WHERE PEO.PERSON_ID = ASS.PERSON_ID
      AND PEO.PER_INFORMATION1 is not NULL
      AND JOB.JOB_INFORMATION_CATEGORY = 'US'
      AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
       ,P_PAYROLL_PERIOD_DATE_END_T)
      AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
      AND JOB.JOB_INFORMATION1 is not NULL
      AND ASS.JOB_ID = JOB.JOB_ID
      AND PEO.EFFECTIVE_START_DATE = (
      SELECT
        MAX(PEO1.EFFECTIVE_START_DATE)
      FROM
        PER_PEOPLE_F PEO1
      WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
        AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
        AND PEO.PERSON_ID = PEO1.PERSON_ID
        AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
      AND ASS.EFFECTIVE_START_DATE = (
      SELECT
        MAX(ASS1.EFFECTIVE_START_DATE)
      FROM
        PER_ALL_ASSIGNMENTS_F ASS1
      WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
        AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
        AND ASS.PERSON_ID = ASS1.PERSON_ID
        AND ASS1.ASSIGNMENT_TYPE = 'E'
        AND ASS1.PRIMARY_FLAG = 'Y' )
      AND ASS.ASSIGNMENT_TYPE = 'E'
      AND ASS.PRIMARY_FLAG = 'Y'
      AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND EXISTS (
      SELECT
        'X'
      FROM
        HR_ORGANIZATION_INFORMATION HOI1,
        HR_ORGANIZATION_INFORMATION HOI2
      WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
        AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
        AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
        AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
        AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
        AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
      AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
      AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
      AND ASS.LOCATION_ID in (
      SELECT
        DISTINCT
        PGN.ENTITY_ID
      FROM
        PER_GEN_HIERARCHY_NODES PGN
      WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
        AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
      OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
        AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );

Line: 404

      SELECT
        NVL(HLEI.LEI_INFORMATION5
           ,C_DEF_ACTIV_1),
        NVL(HLEI.LEI_INFORMATION6
           ,C_DEF_ACTIV_2),
        NVL(HLEI.LEI_INFORMATION7
           ,C_DEF_ACTIV_3),
        NVL(HLEI.LEI_INFORMATION8
           ,C_DEF_ACTIV_4)
      FROM
        HR_LOCATION_EXTRA_INFO HLEI,
        PER_GEN_HIERARCHY_NODES PGHN
      WHERE HLEI.INFORMATION_TYPE = 'EEO-1 Specific Information'
        AND HLEI.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
        AND HLEI.LOCATION_ID = PGHN.ENTITY_ID
        AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
        AND PGHN.HIERARCHY_NODE_ID = EST_NODE_ID
        AND PGHN.NODE_TYPE = 'EST';

Line: 479

      SELECT
        COUNT('person'),
        COUNT(DECODE(PEO.SEX
                    ,'M'
                    ,1
                    ,NULL)),
        COUNT(DECODE(PEO.SEX
                    ,'F'
                    ,1
                    ,NULL))
      INTO CP_HW_CAT,CP_HW_MALE,CP_HW_FEMALE
      FROM
        PER_ALL_PEOPLE_F PEO,
        PER_ALL_ASSIGNMENTS_F ASS,
        PER_JOBS_VL JOB
      WHERE PEO.PERSON_ID = ASS.PERSON_ID
        AND PEO.PER_INFORMATION1 is not NULL
        AND JOB.JOB_INFORMATION_CATEGORY = 'US'
        AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
         ,P_PAYROLL_PERIOD_DATE_END_T)
        AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
        AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
        AND ASS.JOB_ID = JOB.JOB_ID
        AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND PEO.EFFECTIVE_START_DATE = (
        SELECT
          MAX(PEO1.EFFECTIVE_START_DATE)
        FROM
          PER_ALL_PEOPLE_F PEO1
        WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
          AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
          AND PEO.PERSON_ID = PEO1.PERSON_ID
          AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
        AND ASS.EFFECTIVE_START_DATE = (
        SELECT
          MAX(ASS1.EFFECTIVE_START_DATE)
        FROM
          PER_ALL_ASSIGNMENTS_F ASS1
        WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
          AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
          AND ASS.PERSON_ID = ASS1.PERSON_ID
          AND ASS1.ASSIGNMENT_TYPE = 'E'
          AND ASS1.PRIMARY_FLAG = 'Y' )
        AND ASS.ASSIGNMENT_TYPE = 'E'
        AND ASS.PRIMARY_FLAG = 'Y'
        AND EXISTS (
        SELECT
          'X'
        FROM
          HR_ORGANIZATION_INFORMATION HOI1,
          HR_ORGANIZATION_INFORMATION HOI2
        WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
          AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
          AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
          AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
          AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
          AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
        AND ASS.LOCATION_ID in (
        SELECT
          DISTINCT
          PGN.ENTITY_ID
        FROM
          PER_GEN_HIERARCHY_NODES PGN
        WHERE ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
        OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
          AND PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
          AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );

Line: 562

        SELECT
          LEI_INFORMATION3 P_TOTAL,
          TO_NUMBER((LEI_INFORMATION4 + LEI_INFORMATION5 + LEI_INFORMATION6 + LEI_INFORMATION7 + LEI_INFORMATION8 + LEI_INFORMATION14 + LEI_INFORMATION16)) TOTALMALE,
          TO_NUMBER((LEI_INFORMATION9 + LEI_INFORMATION10 + LEI_INFORMATION11 + LEI_INFORMATION12 + LEI_INFORMATION13 + LEI_INFORMATION15 + LEI_INFORMATION17)) TOTALFEM
        INTO HW_AUD_TOT,HW_AUD_MALE,HW_AUD_FEMALE
        FROM
          HR_LOCATION_EXTRA_INFO LEI
        WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
          AND TO_CHAR(LEI.LOCATION_ID) in (
          SELECT
            DISTINCT
            PGN.ENTITY_ID
          FROM
            PER_GEN_HIERARCHY_NODES PGN
          WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
            AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
          OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
            AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );

Line: 603

    SELECT
      COUNT('ass')
    INTO L_COUNT_EMPS
    FROM
      PER_ALL_ASSIGNMENTS_F ASS,
      PER_ALL_PEOPLE_F PEO,
      PER_JOBS_VL JOB
    WHERE PEO.PERSON_ID = ASS.PERSON_ID
      AND PEO.PER_INFORMATION1 is not NULL
      AND JOB.JOB_INFORMATION_CATEGORY = 'US'
      AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
       ,P_PAYROLL_PERIOD_DATE_END_T)
      AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
      AND JOB.JOB_INFORMATION1 is not null
      AND ASS.JOB_ID = JOB.JOB_ID
      AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND ASS.ASSIGNMENT_TYPE = 'E'
      AND ASS.PRIMARY_FLAG = 'Y'
      AND ASS.EFFECTIVE_START_DATE = (
      SELECT
        MAX(ASS1.EFFECTIVE_START_DATE)
      FROM
        PER_ALL_ASSIGNMENTS_F ASS1
      WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
        AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
        AND ASS.PERSON_ID = ASS1.PERSON_ID
        AND ASS1.ASSIGNMENT_TYPE = 'E'
        AND ASS1.PRIMARY_FLAG = 'Y' )
      AND EXISTS (
      SELECT
        'X'
      FROM
        HR_ORGANIZATION_INFORMATION HOI1,
        HR_ORGANIZATION_INFORMATION HOI2
      WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
        AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
        AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
        AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
        AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
        AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
      AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
      AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
      AND ASS.LOCATION_ID in (
      SELECT
        DISTINCT
        ENTITY_ID
      FROM
        PER_GEN_HIERARCHY_NODES
      WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID );

Line: 675

    P_UPDATE VARCHAR2(1) := 'C';

Line: 688

          SELECT
            ELOC.LOCATION_ID,
            ELOC.LOCATION_CODE
          INTO L_LOCATION_ID,L_LOCATION_CODE
          FROM
            PER_GEN_HIERARCHY_NODES PGN,
            HR_LOCATIONS_ALL ELOC
          WHERE ( HIERARCHY_NODE_ID = EST_NODE_ID
          OR PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
            AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
            AND PGN.NODE_TYPE = 'EST'
            AND ELOC.LOCATION_ID = PGN.ENTITY_ID;

Line: 702

          SELECT
            'U',
            LOCATION_EXTRA_INFO_ID
          INTO P_UPDATE,L_LOCATION_EXTRA_INFO_ID
          FROM
            HR_LOCATION_EXTRA_INFO
          WHERE LEI_INFORMATION1 = P_REPORT_YEAR
            AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information'
            AND LOCATION_ID = L_LOCATION_ID;

Line: 713

            P_UPDATE := 'C';

Line: 721

        IF P_UPDATE = 'U' THEN
          /*SRW.MESSAGE('10'
                     ,'p_update ' || P_UPDATE || ' location_id to update is ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;

Line: 725

                     ,' p_location_extra_info_id to delete for update is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;

Line: 727

            SELECT
              OBJECT_VERSION_NUMBER
            INTO L_OBJECT_VERSION_NUMBER
            FROM
              HR_LOCATION_EXTRA_INFO
            WHERE LOCATION_EXTRA_INFO_ID = L_LOCATION_EXTRA_INFO_ID;

Line: 735

                     ,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;

Line: 737

            HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
                                                                 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
                                                                 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);

Line: 742

          P_UPDATE := 'C';

Line: 744

        IF P_UPDATE = 'C' THEN
          /*SRW.MESSAGE('21'
                     ,'p_update ' || P_UPDATE || ' location_id ' || L_LOCATION_ID)*/NULL;

Line: 790

            SELECT
              count(*)
            INTO L_EIT_COUNT
            FROM
              HR_LOCATION_EXTRA_INFO LEI
            WHERE LOCATION_ID = L_LOCATION_ID
              AND INFORMATION_TYPE = 'EEO-1 Archive Information';

Line: 805

              SELECT
                MIN(LEI_INFORMATION1)
              INTO L_MIN_YEAR
              FROM
                HR_LOCATION_EXTRA_INFO LEI
              WHERE LOCATION_ID = L_LOCATION_ID
                AND INFORMATION_TYPE = 'EEO-1 Archive Information';

Line: 814

              SELECT
                LOCATION_EXTRA_INFO_ID,
                OBJECT_VERSION_NUMBER
              INTO L_LOCATION_EXTRA_INFO_ID,L_OBJECT_VERSION_NUMBER
              FROM
                HR_LOCATION_EXTRA_INFO LEI
              WHERE LEI_INFORMATION1 = L_MIN_YEAR
                AND INFORMATION_TYPE = 'EEO-1 Archive Information'
                AND LOCATION_ID = L_LOCATION_ID;

Line: 825

              HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
                                                                   ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
                                                                   ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);

Line: 841

      SELECT
        PEO.PERSON_ID,
        PEO.FIRST_NAME,
        PEO.LAST_NAME,
        PEO.SEX,
        PEO.PER_INFORMATION1 ETHNIC,
        PEO.EMPLOYEE_NUMBER,
        ASS.ASSIGNMENT_ID,
        ASS.LOCATION_ID,
        HL.LOCATION_CODE,
        JOB.NAME JOB_NAME,
        ASS.JOB_ID
      FROM
        PER_ALL_PEOPLE_F PEO,
        PER_ALL_ASSIGNMENTS_F ASS,
        PER_JOBS_VL JOB,
        HR_LOCATIONS_ALL HL
      WHERE PEO.PERSON_ID = ASS.PERSON_ID
        AND PEO.PER_INFORMATION1 is not null
        AND PEO.PER_INFORMATION_CATEGORY = 'US'
        AND JOB.JOB_INFORMATION_CATEGORY = 'US'
        AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
         ,P_PAYROLL_PERIOD_DATE_END_T)
        AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
        AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
        AND ASS.JOB_ID = JOB.JOB_ID
        AND PEO.EFFECTIVE_START_DATE = (
        SELECT
          MAX(PEO1.EFFECTIVE_START_DATE)
        FROM
          PER_PEOPLE_F PEO1
        WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
          AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
          AND PEO.PERSON_ID = PEO1.PERSON_ID
          AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
        AND ASS.EFFECTIVE_START_DATE = (
        SELECT
          MAX(ASS1.EFFECTIVE_START_DATE)
        FROM
          PER_ASSIGNMENTS_F ASS1
        WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
          AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
          AND ASS.PERSON_ID = ASS1.PERSON_ID
          AND ASS1.ASSIGNMENT_TYPE = 'E'
          AND ASS1.PRIMARY_FLAG = 'Y' )
        AND ASS.ASSIGNMENT_TYPE = 'E'
        AND ASS.PRIMARY_FLAG = 'Y'
        AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
        AND EXISTS (
        SELECT
          'X'
        FROM
          HR_ORGANIZATION_INFORMATION HOI1,
          HR_ORGANIZATION_INFORMATION HOI2
        WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
          AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
          AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
          AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
          AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
          AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
        AND HL.LOCATION_ID = ASS.LOCATION_ID
        AND ASS.LOCATION_ID in (
        SELECT
          DISTINCT
          PGN.ENTITY_ID
        FROM
          PER_GEN_HIERARCHY_NODES PGN
        WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
          AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
        OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
          AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
      ORDER BY
        LAST_NAME;