Employee Assignment update procedure in Oracle HRMS Payroll

NOTE:

Kindly change the number of segments of position, job, grades, people groups etc according to your setup.

1) TABLE

CREATE TABLE DEV_UPDATE_ASSIGNMENT

(

  LINE_ID                 NUMBER,

  EMP_CODE                VARCHAR2(10 BYTE),

  ASSG_START_DATE         DATE,

  POS_SEGMENT1            VARCHAR2(240 BYTE),

  POS_SEGMENT2            VARCHAR2(240 BYTE),

  POS_SEGMENT3            VARCHAR2(240 BYTE),

  POS_SEGMENT4            VARCHAR2(240 BYTE),

  JOB_SEGMENT1            VARCHAR2(240 BYTE),

  JOB_SEGMENT2            VARCHAR2(240 BYTE),

  EMPLOYMENT_STATUS       VARCHAR2(20 BYTE),

  PERSON_TYPE             VARCHAR2(100 BYTE),

  ORG_NAME                VARCHAR2(240 BYTE),

  LOCATION_NAME           VARCHAR2(240 BYTE),

  GRAD_SEGMENT1           VARCHAR2(240 BYTE),

  GRAD_SEGMENT2           VARCHAR2(240 BYTE),

  GRAD_SEGMENT3           VARCHAR2(240 BYTE),

  PEOPLE_GROUP            VARCHAR2(240 BYTE),

  SUPERVISOR_NAME         VARCHAR2(240 BYTE),

  SUPERVISOR_EMP_NO       VARCHAR2(20 BYTE),

  ASSG_CATG               VARCHAR2(240 BYTE),

  ERROR_DESCRIPTION       VARCHAR2(240 BYTE),

  PERSON_ID               NUMBER,

  ASSIGNMENT_ID           NUMBER,

  PERSON_OBJ_VER_NUMBER   NUMBER,

  ASG_OBJ_VER_NUMBER      NUMBER,

  EFFECTIVE_START_DATE    DATE,

  EFFECTIVE_END_DATE      DATE,

  FULL_NAME               VARCHAR2(240 BYTE),

  COMMENT_ID              NUMBER,

  ASSG_SEQUENCE           NUMBER,

  ASSG_NUMBER             VARCHAR2(24 BYTE),

  NAME_COMB_WARNING       VARCHAR2(10 BYTE),

  ASSG_PAYROLL_WARNING    VARCHAR2(10 BYTE),

  ORIG_HIRE_WARNING       VARCHAR2(10 BYTE),

  ASSG_PROCESS_FLAG       CHAR(1 BYTE),

  ASSG_ERROR_DESCRIPTION  VARCHAR2(240 BYTE),

  ASSG_OBJ_VNUMBER        NUMBER,

  ASSG_EFF_SD             DATE,

  ASSG_EFF_ED             DATE,

  ASSG_SP_CEIL_STEP_ID    NUMBER,

  ASSG_PEOPLE_GROUP_ID    NUMBER,

  ASSG_GROUP_NAME         VARCHAR2(240 BYTE),

  ASSG_SCODE_FLEX_ID      NUMBER,

  ASSG_CONC_SEGMENTS      VARCHAR2(240 BYTE),

  PG_SEGMENT1             VARCHAR2(50 BYTE),

  PG_SEGMENT2             VARCHAR2(50 BYTE),

  PG_SEGMENT3             VARCHAR2(50 BYTE)

)

2) PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.dev_asg_update_correct

IS

   CURSOR c_emp_assg

   IS     

SELECT LINE_ID, EMP_CODE, ASSG_START_DATE, POS_SEGMENT1, POS_SEGMENT2,

       POS_SEGMENT3, POS_SEGMENT4, JOB_SEGMENT1, JOB_SEGMENT2,

       EMPLOYMENT_STATUS, PERSON_TYPE, ORG_NAME, LOCATION_NAME, GRAD_SEGMENT1,

       GRAD_SEGMENT2, GRAD_SEGMENT3, PEOPLE_GROUP, SUPERVISOR_NAME,

       SUPERVISOR_EMP_NO, ASSG_CATG,PG_SEGMENT1,PG_SEGMENT2,PG_SEGMENT3

  FROM DEV_UPDATE_ASSIGNMENT;

        

   lc_c_emp_assg                    c_emp_assg%ROWTYPE;

   l_object_version_number          NUMBER;

   l_special_ceiling_step_id        NUMBER;

   l_people_group_id                NUMBER;

   l_soft_coding_keyflex_id         NUMBER;

   l_supervisor_id                  NUMBER;

   l_assg_id                        NUMBER;

   l_group_name                     VARCHAR2 (100);

   l_effective_start_date           DATE;

   l_effective_end_date             DATE;

   l_org_now_no_manager_warning     BOOLEAN;

   l_other_manager_warning          BOOLEAN;

   l_spp_delete_warning             BOOLEAN;

   l_entries_changed_warning        VARCHAR2 (100);

   l_tax_district_changed_warning   BOOLEAN;

   l_concatenated_segments          VARCHAR2 (100);

   l_gsp_post_process_warning       VARCHAR2 (100);

   error_desc                       VARCHAR2 (240);

   lv_assg_flag                     CHAR (1);

   l_job_id                         VARCHAR2 (20);

   l_position_id                    VARCHAR2 (20);

   l_grade_id                       VARCHAR2 (20);

   l_org_id                         VARCHAR2 (20);

   l_loc_id                         VARCHAR2 (20);

   l_assg_cat                       VARCHAR2 (50);

   l_assg_start_date                DATE;

   l_pf_flag                        CHAR (1);

   l_segment1                       VARCHAR2 (50);

   l_segment2                       VARCHAR2 (50);

   l_segment3                       VARCHAR2 (50);

  

  

BEGIN

   OPEN c_emp_assg;

   LOOP

      FETCH c_emp_assg

       INTO lc_c_emp_assg;

      EXIT WHEN c_emp_assg%NOTFOUND;

      BEGIN

     

---------------------------------------------------------------

-- AssignmentID

---------------------------------------------------------------

         BEGIN

           

            SELECT PAF.ASSIGNMENT_ID

              INTO L_ASSG_ID

              FROM PER_ASSIGNMENTS_F PAF

             WHERE PAF.ASSIGNMENT_NUMBER = LC_C_EMP_ASSG.EMP_CODE

             and sysdate between paf.effective_start_date and paf.effective_end_date ;

                          

         EXCEPTION

           WHEN NO_DATA_FOUND

            THEN

               L_ASSG_ID := NULL;

         END;

     

     

---------------------------------------------------------------

-- Job ID

---------------------------------------------------------------

         BEGIN

            SELECT pj.job_id

              INTO l_job_id

              FROM per_jobs pj, per_job_definitions pjd

             WHERE pj.job_definition_id = pjd.job_definition_id

               AND UPPER (pjd.segment1) = UPPER (lc_c_emp_assg.job_segment1)

               AND UPPER (pjd.segment2) = UPPER (lc_c_emp_assg.job_segment2);              

         EXCEPTION

           WHEN NO_DATA_FOUND

            THEN

               l_job_id := NULL;

         END;

----------------------------------------

-- Position ID

----------------------------------------

         BEGIN

            SELECT pap.position_id

              INTO l_position_id

              FROM per_all_positions pap, per_position_definitions ppd

             WHERE pap.position_definition_id = ppd.position_definition_id

               AND UPPER (ppd.segment1) = UPPER (lc_c_emp_assg.pos_segment1)

               AND UPPER (ppd.segment2) = UPPER (lc_c_emp_assg.pos_segment2)

               AND UPPER (ppd.segment3) = UPPER (lc_c_emp_assg.pos_segment3)

               AND UPPER (ppd.segment4) = UPPER (lc_c_emp_assg.pos_segment4);             

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               l_position_id := NULL;

         END;

---------------------------

-- Grade ID

---------------------------

         BEGIN

            SELECT pg.grade_id

              INTO l_grade_id

              FROM per_grades pg, per_grade_definitions pgd

             WHERE pg.grade_definition_id = pgd.grade_definition_id

               AND UPPER (pgd.segment1) = UPPER (lc_c_emp_assg.grad_segment1)

               AND UPPER (pgd.segment2) = UPPER (lc_c_emp_assg.grad_segment2)

               AND UPPER (pgd.segment3) = UPPER (lc_c_emp_assg.grad_segment3);              

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               l_grade_id := NULL;

         END;

---------------------------------------------------------------

-- Organization ID

---------------------------------------------------------------

         BEGIN

            SELECT organization_id

              INTO l_org_id

              FROM hr_all_organization_units

             WHERE UPPER (NAME) LIKE UPPER (lc_c_emp_assg.org_name)

             AND BUSINESS_GROUP_ID = 2217;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               l_org_id := NULL;

         END;

---------------------------------------------------------------

-- Location ID

---------------------------------------------------------------

         BEGIN

            SELECT location_id

              INTO l_loc_id

              FROM hr_locations_all

             WHERE UPPER (location_code) LIKE

                                           UPPER (lc_c_emp_assg.location_name);

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               l_loc_id := NULL;

         END;

        

---------------------------------------------------------------

-- Supervisor ID

---------------------------------------------------------------        

        

--         BEGIN

--             SELECT person_id

--                 INTO l_supervisor_id

--                  FROM per_all_people_f ppf

--                   WHERE employee_number = lc_c_emp_assg.SUPERVISOR_EMP_NO

--                   and sysdate between ppf.EFFECTIVE_START_DATE and ppf.EFFECTIVE_END_DATE ;

--          EXCEPTION

--            WHEN NO_DATA_FOUND

--            THEN

--               l_supervisor_id := NULL;

--         END;        

       

---------------------------------------------------------------

-- Assignment Category

---------------------------------------------------------------

--        BEGIN

--            SELECT distinct lookup_code

--              INTO l_assg_cat

--              FROM fnd_lookup_values_vl

--             WHERE lookup_type = 'EMP_CAT'

--               AND UPPER (meaning) LIKE UPPER (lc_c_emp_assg.ASSG_CATG)

--               AND DESCRIPTION IS NULL;

--               

--        EXCEPTION

--            WHEN NO_DATA_FOUND

--            THEN

--               l_assg_cat := NULL;

--        END;        

        

----------------------

-- Object Version Number

----------------------

         SELECT paaf.object_version_number

           INTO l_object_version_number

           FROM per_all_assignments_f paaf

          WHERE SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

            AND paaf.assignment_id = l_assg_id;--lc_c_emp_assg.assignment_id;

         IF lc_c_emp_assg.assg_start_date IS NOT NULL  THEN

            l_assg_start_date := lc_c_emp_assg.assg_start_date;

         END IF;

   

-----------------------------------------------------------------------------

         hr_assignment_api.update_emp_asg_criteria

            (p_validate                          => FALSE,

             p_effective_date                    => l_assg_start_date,

             p_datetrack_update_mode             => 'CORRECTION',--'UPDATE',   

             p_assignment_id                     => l_assg_id,

             p_job_id                            => l_job_id,

             p_position_id                       => l_position_id,

             p_grade_id                          => l_grade_id,

             p_location_id                       => l_loc_id,

             p_organization_id                   => l_org_id,

             --p_employment_category               => l_assg_cat ,

             p_segment1                          => lc_c_emp_assg.pg_segment1,

             p_segment2                          => lc_c_emp_assg.pg_segment2,

             p_segment3                          => lc_c_emp_assg.pg_segment3,

             --p_supervisor_assignment_id        => l_supervisor_id ,

             p_object_version_number             => l_object_version_number,

             p_effective_start_date              => l_effective_start_date,

             p_effective_end_date                => l_effective_end_date,

             p_special_ceiling_step_id           => l_special_ceiling_step_id,

             p_people_group_id                   => l_people_group_id,

             p_group_name                        => l_group_name,--lc_c_emp_assg.people_group,

             p_org_now_no_manager_warning        => l_org_now_no_manager_warning,

             p_other_manager_warning             => l_other_manager_warning,

             p_spp_delete_warning                => l_spp_delete_warning,

             p_entries_changed_warning           => l_entries_changed_warning,

             p_tax_district_changed_warning      => l_tax_district_changed_warning,

             p_soft_coding_keyflex_id            => l_soft_coding_keyflex_id,

             p_concatenated_segments             => l_concatenated_segments,

             p_gsp_post_process_warning          => l_gsp_post_process_warning

            );

       

        

         IF l_effective_start_date IS NOT NULL

         THEN

            lv_assg_flag := 'Y';

            error_desc := 'No Error';

         END IF;

         IF lv_assg_flag = 'Y'

         THEN

            UPDATE DEV_UPDATE_ASSIGNMENT

               SET assg_error_description = error_desc,

                   assg_obj_vnumber = l_object_version_number,

                   assg_eff_sd = l_effective_start_date,

                   assg_eff_ed = l_effective_end_date,

                   --assg_sp_ceil_step_id = l_special_ceiling_step_id,

                   assg_people_group_id = l_people_group_id,

                   assg_group_name = l_group_name,

                   assg_scode_flex_id = l_soft_coding_keyflex_id,

                   assg_conc_segments = l_concatenated_segments

             WHERE emp_code = lc_c_emp_assg.emp_code

               AND line_id = lc_c_emp_assg.line_id;

         ELSE

            error_desc := error_desc || SQLERRM;

           

        

         END IF;

        

      EXCEPTION

         WHEN OTHERS

         THEN

            lv_assg_flag := 'N';

            error_desc := error_desc || SQLERRM;

           

            UPDATE DEV_UPDATE_ASSIGNMENT

               SET error_description = error_desc         

             WHERE emp_code = lc_c_emp_assg.emp_code

               AND line_id = lc_c_emp_assg.line_id;

       

      END;

      l_object_version_number := '';

      l_special_ceiling_step_id := '';

      l_people_group_id := '';

      l_soft_coding_keyflex_id := '';

      l_group_name := '';

      l_effective_start_date := '';

      l_effective_end_date := '';

      l_org_now_no_manager_warning := FALSE;

      l_other_manager_warning := FALSE;

      l_spp_delete_warning := FALSE;

      l_entries_changed_warning := '';

      l_tax_district_changed_warning := FALSE;

      l_concatenated_segments := '';

      l_gsp_post_process_warning := '';

      error_desc := '';

      lv_assg_flag := '';

      l_assg_cat   := '';

      l_job_id := '';

      l_position_id := '';

      l_grade_id := '';

      l_org_id := '';

     

     

   END LOOP;

   CLOSE c_emp_assg;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/

Employee Creation Procedure in Oracle

Leave a comment for any query.