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