Search This Blog

Wednesday, February 8, 2023

EBS R12 API to Update Supervisor Assignment using loop for future assignment actions if available

 --begin

--fnd_global.apps_initialize(8952,52032,800);

--end;



DECLARE

P_NEW_SUPERVISOR_ID NUMBER := 1130;

P_PERSON_ID NUMBER := 211716;

P_DATE DATE := TO_DATE('01-FEB-2023');

V_OBJECT_VERSION_NUMBER NUMBER;

V_DATE DATE;

V_ERROR_MSG                          VARCHAR2(2500) := NULL;

V_API_ERROR                          VARCHAR2(500); 

--p_person_id number;

   -- Local Variables

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

   lc_dt_ud_mode           VARCHAR2(100)    := NULL;

  -- ln_assignment_id       NUMBER                  := 37392;

   --ln_supervisor_id        NUMBER                  := 4485;

   ln_object_number       NUMBER                  ;

   --ln_people_group_id  NUMBER                  := 105;

 

   -- 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;

 p_cagr_grade_def_id NUMBER;

 p_cagr_concatenated_segments number;

 


 cursor C1 is

 

SELECT PAAF.ASSIGNMENT_ID,PAAF.EFFECTIVE_START_DATE, PAAF.EFFECTIVE_END_DATE

,PAAF.ASSIGNMENT_NUMBER

FROM PER_ALL_ASSIGNMENTS_F PAAF

WHERE PAAF.PERSON_ID = P_PERSON_ID

AND (P_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND  PAAF.EFFECTIVE_END_DATE

OR PAAF.EFFECTIVE_START_DATE  >= P_DATE  )

AND P_NEW_SUPERVISOR_ID IS NOT NULL

AND SUPERVISOR_ID <> P_NEW_SUPERVISOR_ID

ORDER BY effective_start_date

;



    

BEGIN

 DBMS_OUTPUT.PUT_LINE('hi1' );

FOR I IN C1 LOOP

 

V_DATE := GREATEST(P_DATE , I.EFFECTIVE_START_DATE);


   V_OBJECT_VERSION_NUMBER := 0;

   V_OBJECT_VERSION_NUMBER := XXPY_UPDATE_EMP_ASSIGNMENT.GET_OVN_F(I.ASSIGNMENT_ID,V_DATE);

DBMS_OUTPUT.PUT_LINE('V_DATE is '||V_DATE);

begin

  -- Update Employee Assignment

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

 hr_assignment_api.update_emp_asg

 ( -- Input data elements

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

  p_effective_date                              =>V_DATE,

  p_datetrack_update_mode         => XXPY_UPDATE_EMP_ASSIGNMENT.GET_MODE_ASSGINMENT_F(I.ASSIGNMENT_ID,V_DATE),

  p_assignment_id                            => TO_NUMBER(I.assignment_id),

  p_supervisor_id                              => TO_NUMBER(P_NEW_SUPERVISOR_ID),

  p_change_reason                           => NULL,

--  p_manager_flag                              => 'N',

  p_bargaining_unit_code              => NULL,

  p_labour_union_member_flag   => NULL,

--  p_segment1                                       => TO_NUMBER(HIRING.LEGAL_EMP),--108,

--  p_normal_hours                              => 35,

--  p_time_normal_start                         => '07:30',

--  p_time_normal_finish                         => '14:30',

--  p_frequency                                       => 'W',

--  p_employee_category                               => HIRING.employee_category,--'EMP',

  -- Output data elements

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

  p_cagr_grade_def_id             => p_cagr_grade_def_id,-- bug 2359997

  p_cagr_concatenated_segments   => p_cagr_concatenated_segments,

  p_object_version_number             => V_OBJECT_VERSION_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

 );

 commit;

 EXCEPTION WHEN OTHERS THEN

 DBMS_OUTPUT.PUT_LINE(SQLERRM );--||'-'||I.ASSIGNMENT_NUMBER);

END;

END LOOP;

EXCEPTION WHEN OTHERS THEN

 DBMS_OUTPUT.PUT_LINE(SQLERRM );

END;


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


FUNCTION GET_MODE_ASSGINMENT_F(P_ASSIGNMENT_ID NUMBER, P_DATE DATE)

RETURN VARCHAR2 AS

   lb_correction                  BOOLEAN;

   lb_update                      BOOLEAN;

   lb_update_override             BOOLEAN;

   lb_update_change_insert        BOOLEAN;

   lc_dt_ud_mode varchar2(200); 

   L_ERROR varchar2(2000); 

   BEGIN

dt_api.find_dt_upd_modes

   (    p_effective_date                  => P_DATE,

        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',

        p_base_key_column            => 'ASSIGNMENT_ID',

        p_base_key_value                => P_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_correction = TRUE )

   THEN lc_dt_ud_mode := 'CORRECTION';

   end if;

   IF ( lb_update = TRUE )

   THEN lc_dt_ud_mode := 'UPDATE';

   END IF; 

   IF ( lb_update_override = TRUE  )

   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';

   end if;

   IF (lb_update_change_insert = TRUE)

   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';

   end if;

   --INSERT INTO GET_MODE_ASSGINMENT_F_T(ASSIGNMENT_ID,P_DATE,ERROR_MSG,CREATION_DATE) VALUES(P_ASSIGNMENT_ID, SYSDATE,lc_dt_ud_mode,SYSDATE);

   RETURN lc_dt_ud_mode;

   EXCEPTION WHEN OTHERS THEN 

   L_ERROR := SQLERRM;

   --INSERT INTO GET_MODE_ASSGINMENT_F_T(ASSIGNMENT_ID,P_DATE,ERROR_MSG,CREATION_DATE) VALUES(P_ASSIGNMENT_ID, SYSDATE,L_ERROR,SYSDATE);

END;




FUNCTION GET_OVN_F (P_ASSIGNMENT_ID NUMBER, P_DATE DATE) RETURN NUMBER IS

L_RETURN NUMBER;

BEGIN

SELECT OBJECT_VERSION_NUMBER 

INTO L_RETURN

FROM PER_ALL_ASSIGNMENTS_F PAAF

WHERE ASSIGNMENT_ID = P_ASSIGNMENT_ID

AND P_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

AND PRIMARY_FLAG = 'Y'

;

RETURN L_RETURN;

EXCEPTION WHEN OTHERS THEN RETURN 0;

END; 

No comments:

Post a Comment