Oracle HRMS API – Update Employee Assignment

/*
To Update: Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, 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: Grade, Location, Job, Payroll, Organization, Employee Category, People Group

API -- hr_assignment_api.update_emp_asg_criteria
*/

--Example --

DECLARE
   -- Local Variables
   -- -----------------------
   LC_DT_UD_MODE          VARCHAR2(100)     := NULL;
   LN_ASSIGNMENT_ID       NUMBER            := 9375;
   LN_SUPERVISOR_ID       NUMBER            := NULL;
   LN_OBJECT_NUMBER       NUMBER            := 1;
   ln_people_group_id  NUMBER               := 1;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                               BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id      HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   LN_COMMENT_ID                  PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning         BOOLEAN;

 -- Out Variables for Update Employee Assgment Criteria
 -- -------------------------------------------------------------------------------
 ln_special_ceiling_step_id           PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                         VARCHAR2(30);
 ld_effective_start_date               PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 LD_EFFECTIVE_END_DATE                 PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning           BOOLEAN;
 lb_other_manager_warning                BOOLEAN;
 lb_spp_delete_warning                   BOOLEAN;
 lc_entries_changed_warning              VARCHAR2(30);
 lb_tax_district_changed_warn            BOOLEAN;



BEGIN
   -- Find Date Track Mode
   -- --------------------------------
   dt_api.find_dt_upd_modes
   (    p_effective_date                  => TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value            => ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert
     );


   IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       -- ---------------------------------
       lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;



  IF ( lb_correction = TRUE )
  THEN
      -- CORRECTION
      -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



  IF ( lb_update = TRUE )
  THEN
      -- UPDATE
      -- --------------
      lc_dt_ud_mode := 'UPDATE';
   END IF;



  -- Update Employee Assignment
  -- ---------------------------------------------
 hr_assignment_api.update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => NULL,
  p_change_reason                           => NULL,
  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_segment1                                       => 204,
  p_segment3                                       => 'N',
  p_normal_hours                              => 10,
  p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );

 -- Find Date Track Mode for Second API
 -- ------------------------------------------------------
  dt_api.find_dt_upd_modes
  (  p_effective_date                   => TO_DATE('12-JUN-2011'),
     p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
     p_base_key_column           => 'ASSIGNMENT_ID',
     p_base_key_value               => ln_assignment_id,
     -- Output data elements
     -- -------------------------------
     p_correction                           => lb_correction,
     p_update                                 => lb_update,
     p_update_override               => lb_update_override,
     p_update_change_insert    => lb_update_change_insert
  );



  IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
  THEN
    -- UPDATE_OVERRIDE
    -- --------------------------------
    lc_dt_ud_mode := 'UPDATE_OVERRIDE';
  END IF;



   IF ( lb_correction = TRUE )
   THEN
     -- CORRECTION
     -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



   IF ( lb_update = TRUE )
   THEN
     -- UPDATE
     -- --------------
     lc_dt_ud_mode := 'UPDATE';
   END IF;



 -- Update Employee Assgment Criteria
 -- -----------------------------------------------------
 hr_assignment_api.update_emp_asg_criteria
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                                   => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode               => lc_dt_ud_mode,
  p_assignment_id                                 => ln_assignment_id,
  p_location_id                                        => 204,
  p_grade_id                                             => 29,
  p_job_id                                                  => 16,
  p_payroll_id                                          => 52,
  p_organization_id                               => 239,
  p_employment_category                    => 'FR',
  -- Output data elements
  -- -------------------------------
  p_people_group_id                              => ln_people_group_id,
  p_object_version_number                   => ln_object_number,
  p_special_ceiling_step_id                  => ln_special_ceiling_step_id,
  p_group_name                                        => lc_group_name,
  p_effective_start_date                           => ld_effective_start_date,
  p_effective_end_date                             => ld_effective_end_date,
  p_org_now_no_manager_warning  => lb_org_now_no_manager_warning,
  p_other_manager_warning                 => lb_other_manager_warning,
  p_spp_delete_warning                         => lb_spp_delete_warning,
  p_entries_changed_warning              => lc_entries_changed_warning,
  p_tax_district_changed_warning     => lb_tax_district_changed_warn
 );

 COMMIT;

EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;
/