Thursday 13 March 2014

Oracle HRMS API – Update Employee Assignment & Supervisor Adding

To Update Employee
-------------------------
Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id
API -- hr_assignment_api.update_emp_asg 

To Update Employee
--------------------------
Grade, Location, Job, Payroll, Organization, Employee Category, People Group
API -- hr_assignment_api.update_emp_asg_criteria

Staging Table
-----------------
CREATE TABLE APPS.XXAT_EMP_ASSIGNMENTS
(
  EMP_NUM         NUMBER,
  ORG_NAME        VARCHAR2(2000 BYTE),
  GCC_GROUP       VARCHAR2(1000 BYTE),
  POSITION        VARCHAR2(2000 BYTE),
  JOB             VARCHAR2(2000 BYTE),
  PAYROLL         VARCHAR2(2000 BYTE),
  LOCATION        VARCHAR2(2000 BYTE),
  STATUS          VARCHAR2(2000 BYTE),
  SAL_BASIS       VARCHAR2(2000 BYTE),
  SUPERVISOR_NUM  NUMBER,
  GOSI_EMP        VARCHAR2(1000 BYTE),
  PROCESS_FLAG    VARCHAR2(1 BYTE),
  ERROR_MSG       VARCHAR2(3000 BYTE)
); 

Flat File Format
------------------------------
Emp Num Organization GCC Group Position Job Payroll Location Status Salary Basis Supervisor Number GOSI - Employer Process Flag Error Message
3 Chief Business Support Office No.No.Yes Chief Business Support Officer.Chief Business Support Office Chief Business Support Officer EATC Payroll Riyadh Head Office Active Assignment EATC Monthly Salary  911 Etihad Atheeb Telecommunication Company  N  

Sample Code - Example
------------------------------
Package Specification
--------------------------

CREATE OR REPLACE PACKAGE APPS.xxat_emp_assign_pkg
AS
   PROCEDURE xxxat_emp_assign_update;
END xxat_emp_assign_pkg; 

Package Body
----------------

CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_assign_pkg
AS
   PROCEDURE xxxat_emp_assign_update
   IS
      --  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_no_managers_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);
      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);
      l_person_id                      NUMBER;
--
      l_error                          NUMBER;
      l_error_msg                      VARCHAR2 (3000);
      v_emp_num                        VARCHAR2 (100);
      l_assign_date                    VARCHAR2 (100);
      l_sup_hire_date                  DATE;
      l_emp_hire_date                  DATE;
      l_payroll_id                     NUMBER;
      l_paybasis_id                    NUMBER;
      l_comment_id                     NUMBER;

      CURSOR c1
      IS
         SELECT *
           FROM xxat_emp_assignments
          WHERE process_flag = 'N'
            AND emp_num = 82;
   BEGIN
      DBMS_OUTPUT.put_line (' ******     Process Validation Start    ******');

      FOR i IN c1
      LOOP
         l_error                    := 0;
         l_person_id                := NULL;

         IF    i.emp_num IS NULL
            -- OR i.assign_start_date IS NULL
            OR i.org_name IS NULL
            OR i.gcc_group IS NULL
            OR i.POSITION IS NULL
            OR i.job IS NULL
            OR i.payroll IS NULL
            OR i.LOCATION IS NULL
            OR i.status IS NULL
            OR i.sal_basis IS NULL
            OR i.supervisor_num IS NULL
            OR i.gosi_emp IS NULL
         THEN
            l_error_msg                :=
                              'All Fields are mandatory & should not be null';
            l_error                    := 1;
            --  p_retcode                  := 2;
              --fnd_file.put_line (fnd_file.LOG,i.relationship_id || '-' || l_error_msg);
            DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
         --  p_errbuf                   := l_error_msg;
         ELSE
            l_object_version_number    := NULL;
            l_special_ceiling_step_id  := NULL;
            l_people_group_id          := NULL;
            l_soft_coding_keyflex_id   := NULL;
            l_group_name               := NULL;
            l_effective_start_date     := NULL;
            l_effective_end_date       := NULL;
            l_org_now_no_manager_warning := FALSE;
            l_other_manager_warning    := FALSE;
            l_spp_delete_warning       := FALSE;
            l_entries_changed_warning  := NULL;
            l_tax_district_changed_warning := FALSE;
            l_concatenated_segments    := NULL;
            l_gsp_post_process_warning := NULL;
            -- error_desc                 := '';
            -- lv_assg_flag               := '';
            l_assg_cat                 := NULL;
            l_job_id                   := NULL;
            l_position_id              := NULL;
            l_grade_id                 := NULL;
            l_org_id                   := NULL;
            l_assign_date              := NULL;
            l_sup_hire_date            := NULL;
            l_emp_hire_date            := NULL;

---------------------------
-- AssignmentID
---------------------------
            BEGIN
               SELECT paf.assignment_id
                 INTO l_assg_id
                 FROM per_assignments_f paf
                WHERE paf.assignment_number = i.emp_num
                  AND SYSDATE BETWEEN paf.effective_start_date
                                  AND paf.effective_end_date;
            EXCEPTION
               WHEN OTHERS
               THEN
                  -- l_assg_id                  := NULL;
                  l_error_msg                := 'Assignment not found';
                  l_error                    := 1;
            END;

---------------------------------------
-- Employee Hire Date / Effective Start Date
---------------------------------------
            BEGIN
               SELECT paf.start_date
                 INTO l_emp_hire_date
                 FROM per_all_people_f paf
                WHERE paf.employee_number = i.emp_num
                  AND SYSDATE BETWEEN paf.effective_start_date
                                  AND paf.effective_end_date;

               SELECT TO_DATE (TO_CHAR (l_assign_date,
                                        'RRRR/MM/DD HH24:MI:SS'),
                               'RRRR/MM/DD HH24:MI:SS'
                              )
                 INTO l_assign_date
                 FROM DUAL;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                :=
                                              'Employee start date not found';
                  l_error                    := 1;
            END;

-------------------------------------------------
-- Supervisor Hire Date / Effective Start Date
--------------------------------------------------
            BEGIN
               SELECT paf.start_date
                 INTO l_sup_hire_date
                 FROM per_all_people_f paf
                WHERE paf.employee_number = i.supervisor_num
                  AND SYSDATE BETWEEN paf.effective_start_date
                                  AND paf.effective_end_date;

               SELECT TO_DATE (TO_CHAR (l_assign_date,
                                        'RRRR/MM/DD HH24:MI:SS'),
                               'RRRR/MM/DD HH24:MI:SS'
                              )
                 INTO l_assign_date
                 FROM DUAL;
            EXCEPTION
               WHEN OTHERS
               THEN

                  l_error_msg                :=
                                              'Employee start date not found';
                  l_error                    := 1;
            END;

            IF l_emp_hire_date < l_sup_hire_date
            THEN
               l_assign_date              := l_sup_hire_date;
            ELSE
               l_assign_date              := l_emp_hire_date;
            END IF;
            
--dbms_output.put_line('emp date'||l_emp_hire_date||'super date'||l_sup_hire_date||'final'||l_assign_date);
----------------------------------
-- Organization ID
----------------------------------
            BEGIN
               SELECT organization_id
                 INTO l_org_id
                 FROM hr_all_organization_units
                WHERE UPPER (NAME) LIKE UPPER (i.org_name)
                  AND business_group_id = 81;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Organization not found';
                  l_error                    := 1;
            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 pj.NAME = i.job;
            EXCEPTION
               WHEN OTHERS
               THEN
                  --  l_job_id                   := NULL;
                  l_error_msg                := 'Job not found';
                  l_error                    := 1;
            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 pap.status = 'VALID'
                  AND pap.NAME LIKE i.POSITION || '%';
            EXCEPTION
               WHEN OTHERS
               THEN
                  -- l_position_id              := NULL;
                  l_error_msg                := 'Position not found';
                  l_error                    := 1;
            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 OTHERS
     THEN
        l_grade_id                 := NULL;
  END;*/

            ---------------------------------
-- Payroll ID
---------------------------------
            BEGIN
               SELECT payroll_id
                 INTO l_payroll_id
                 FROM pay_all_payrolls_f
                WHERE 1 = 1
                  AND UPPER (payroll_name) LIKE UPPER (i.payroll)
                  AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                          AND effective_end_date;
            EXCEPTION
               WHEN OTHERS
               THEN
                  -- l_pay_id                   := NULL;
                  l_error_msg                := 'Payroll ID not found';
                  l_error                    := 1;
            END;

---------------------------------
-- Location ID
---------------------------------
            BEGIN
               SELECT location_id
                 INTO l_loc_id
                 FROM hr_locations_all
                WHERE UPPER (location_code) LIKE UPPER (i.LOCATION);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Location ID not found';
                  l_error                    := 1;
            END;

-----------------------------------------------
-- Salary Information (T) > Salary Basis ID
-----------------------------------------------
            BEGIN
               SELECT pay_basis_id
                 INTO l_paybasis_id
                 FROM per_pay_bases
                WHERE 1 = 1
                  AND UPPER (NAME) LIKE UPPER (i.sal_basis);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Pay/Salary basis not found';
                  l_error                    := 1;
            END;

---------------------------------
-- Supervisor ID
---------------------------------
            BEGIN
               SELECT person_id
                 INTO l_supervisor_id
                 FROM per_all_people_f ppf
                WHERE employee_number = i.supervisor_num
                  AND SYSDATE BETWEEN ppf.effective_start_date
                                  AND ppf.effective_end_date;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Supervisor ID not found';
                  l_error                    := 1;
            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 OTHERS
            --            THEN
            --               l_assg_cat := NULL;
            --        END;

            --------------------------
-- People Group ID & NAME Number
--------------------------
            BEGIN
               SELECT people_group_id,
                      group_name
                 INTO l_people_group_id,
                      l_group_name
                 FROM pay_people_groups a
                WHERE segment1 || '.' || segment2 || '.' || segment3 IN (
                         SELECT segment1 || '.' || segment2 || '.' || segment3
                           FROM pay_people_groups a
                          WHERE 1 = 1
                            -- AND people_group_id = 'No.No.Yes'
                            AND group_name = i.gcc_group);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Group ID/Name not found';
                  l_error                    := 1;
            END;

            --------------------------
-- Object Version Number
--------------------------
            BEGIN
               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;

--               DBMS_OUTPUT.put_line (   'object version no.'
--                                     || l_object_version_number
--                                    );
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_msg                := 'Object Version not found';
                  l_error                    := 1;
            END;

            IF l_error = 0
            THEN
               BEGIN

 -- Update Employee Assgment Criteria
 -- ----------------------------------------------------- 

                  hr_assignment_api.update_emp_asg_criteria
                     (p_validate                          => FALSE,
                      p_effective_date                    => TO_DATE
                                                                (l_assign_date,
                                                                 'DD-MON-RRRR'
                                                                ),
                                                           -- TRUNC (SYSDATE),
                      --l_assg_start_date,
                      p_datetrack_update_mode             => 'CORRECTION',
                      --'UPDATE',
                      p_assignment_id                     => l_assg_id,
                      p_position_id                       => l_position_id,
                      p_job_id                            => l_job_id,
                      p_payroll_id                        => l_payroll_id,
                      --  p_grade_id                          => l_grade_id,
                      p_location_id                       => l_loc_id,
                      p_organization_id                   => l_org_id,
                      p_pay_basis_id                      => l_paybasis_id,
                      --p_employment_category               => l_assg_cat ,
                      p_segment1                          => 81,
                                                    -- GOSI Tab - Emloyer Name
--                      p_segment2                          => lc_c_emp_assg.pg_segment2,
--                      p_segment3                          => lc_c_emp_assg.pg_segment3,
                   --   p_supervisor_assignment_id          => l_supervisor_id,
                      --Outparatmers
                      p_people_group_id                   => l_people_group_id,
                      p_object_version_number             => l_object_version_number,
                      p_special_ceiling_step_id           => l_special_ceiling_step_id,
                      p_group_name                        => l_group_name,
                      p_effective_start_date              => l_effective_start_date,
                      p_effective_end_date                => l_effective_end_date,
                      --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
                     );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error                    := 1;
                     l_error_msg                :=
                                                  'Api Exception ' || SQLERRM;
               END;
               -- dbms_output.put_line(TO_DATE (l_assign_date,'DD-MON-RRRR' ));
               BEGIN

  -- Update Employee Assignment
  -- --------------------------------------------- 

                  hr_assignment_api.update_emp_asg
                     (
                      -- Input data elements
                      p_effective_date              => TO_DATE (l_assign_date,
                                                                'DD-MON-RRRR'
                                                               ),
                                                          --  TRUNC (SYSDATE),
                      p_datetrack_update_mode       => 'UPDATE',
                      p_assignment_id               => l_assg_id,
                      p_supervisor_id               => l_supervisor_id,
                      p_change_reason               => NULL,
                      p_manager_flag                => 'Y',
                    --  p_bargaining_unit_code              => NULL,
                    --  p_labour_union_member_flag   => NULL,
                    --  p_segment1                                       => 204,
                    --  p_segment3                                       => 'N',
                    --  p_normal_hours                              => null,
                    --  p_frequency                                       => NULL,
                    --  Output data elements
                    -- -------------------------------
                      p_object_version_number       => l_object_version_number,
                      p_soft_coding_keyflex_id      => l_soft_coding_keyflex_id,
                      p_concatenated_segments       => l_concatenated_segments,
                      p_comment_id                  => l_comment_id,
                      p_effective_start_date        => l_effective_start_date,
                      p_effective_end_date          => l_effective_end_date,
                      p_no_managers_warning         => l_no_managers_warning,
                      p_other_manager_warning       => l_other_manager_warning
                     );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error                    := 1;
                     l_error_msg                :=
                                       'Error In Supervisors API ' || SQLERRM;
               END;

               IF l_error = 1
               THEN
                  UPDATE xxat_emp_assignments
                     SET process_flag = 'E',
                         error_msg = l_error_msg
                   WHERE emp_num = i.emp_num;
               ELSE
                  UPDATE xxat_emp_assignments
                     SET process_flag = 'Y'
                   WHERE emp_num = i.emp_num;
               END IF;
            ELSIF l_error = 1
            THEN
               UPDATE xxat_emp_assignments
                  SET process_flag = 'E',
                      error_msg = l_error_msg
                WHERE emp_num = i.emp_num;
            END IF;

            COMMIT;
         --fnd_file.put_line (fnd_file.LOG, '******      Process Validation End      ******');
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('******      Process Validation End      ******');
      COMMIT;
   END xxxat_emp_assign_update;
END xxat_emp_assign_pkg; 
/

3 comments:

  1. Thank you so much, very useful :-)

    ReplyDelete
  2. what if we have multiple assignment then how should we tell the api that we need supervisor on active one. becasue the active assignment flag of both assignments are Y.

    ReplyDelete