Requirement :
Updating the assignment status as per the leave applied to custom status(ex: Annual Leave, Unpaid Leave status), and once employee joined again updating status to 'Active'.
This is just part of the whole procedure, but the main api's, syntax can be derived from it.
Package: dt_api.find_dt_upd_modes is used find the update modes in HRMS.
Package: HR_ASSIGNMENT_API.SUSPEND_EMP_ASG is used to update the status (Unpaid Leave status or Annual Leave status, this is custom statuses)
Package : HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG is used to update the status to active.
Above 3 packages examples are given below as syntax.....
PROCEDURE UPDATE_EMP_ASSIG_ST_2(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_BUSINESS_GROUP_ID IN NUMBER,
P_PERSON_ID IN NUMBER,
P_ABSENCE_ATTENDANCE_ID IN NUMBER) IS
--Commented the Session month (before and after) so to trigger particular absence_attendance_id and added as of sysdate
/*
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Long Unpaid Leave
-- 26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
-- 26 dec 2014 Suspend
*/
------------------------------------------------------------------------------------
------------CURSOR TO GET 'Advanced Pay Information' DETAILS.......................
--local variablese
--declare
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
lc_dt_ud_mode varchar2(200);
--parameter vairables
-- P_BUSINESS_GROUP_ID number := 81;
-- EVENT_TYPE varchar2(200) := '';--'insert';--'';
-- P_PERSON_ID number :=null;--26876;
-- P_ABSENCE_ATTENDANCE_ID number :=null;--3700945;
--cursor to bring the advance leave employee details
CURSOR GET_CHANGED_EMP IS
SELECT ASG.ASSIGNMENT_ID
,PPL.LAST_NAME NAME
,PPL.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
,PPL.FULL_NAME FULL_NAME
,ENTS.ELEMENT_ENTRY_ID X_ID
,FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE) DATE_START
,FND_DATE.canonical_to_date(vals2.SCREEN_ENTRY_VALUE) DATE_END
,FND_DATE.canonical_to_date(vals3.SCREEN_ENTRY_VALUE) UNPAID_DATE
,VALS4.SCREEN_ENTRY_VALUE ADV_FLAG
,ENTS.EFFECTIVE_START_DATE X_S_D
,ENTS.EFFECTIVE_END_DATE X_E_D
FROM PER_ALL_PEOPLE_F PPL
,PER_ALL_ASSIGNMENTS_F ASG
,PAY_ELEMENT_TYPES_F TYP
,PAY_INPUT_VALUES_F INP1
,PAY_INPUT_VALUES_F INP2
,PAY_INPUT_VALUES_F INP3
,PAY_INPUT_VALUES_F INP4
,PAY_ELEMENT_ENTRIES_F ENTS
,PAY_ELEMENT_ENTRY_VALUES_F VALS1
,PAY_ELEMENT_ENTRY_VALUES_F VALS2
,PAY_ELEMENT_ENTRY_VALUES_F VALS3
,PAY_ELEMENT_ENTRY_VALUES_F VALS4
WHERE inp1.element_type_id = typ.element_type_id
AND INP2.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
AND INP3.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
AND INP4.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADded by engy
AND ents.element_type_id = typ.element_type_id
AND vals1.element_entry_id = ents.element_entry_id --added by engy
AND vals2.element_entry_id = ents.element_entry_id --added by engy
AND vals3.element_entry_id = ents.element_entry_id --added by engy
AND vals4.element_entry_id = ents.element_entry_id --added by engy
AND ppl.person_id = asg.person_id
AND ents.assignment_id = asg.assignment_id
AND ASG.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND vals1.input_value_id =inp1.input_value_id
and vals2.input_value_id=inp2.input_value_id --added by engy
and vals3.input_value_id=inp3.input_value_id --added by engy
and vals4.input_value_id=inp4.input_value_id --added by engy
AND ents.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ents.effective_start_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
AND ents.effective_start_date BETWEEN typ.effective_start_date AND typ.effective_end_date
AND ents.effective_start_date BETWEEN inp1.effective_start_date AND inp1.effective_end_date
AND ents.effective_start_date between ents.effective_start_date and ents.effective_end_date
and ents.effective_start_date between vals1.effective_start_date and vals1.effective_end_date--added by engy
and ents.effective_start_date between vals2.effective_start_date and vals2.effective_end_date--added by engy
and ents.effective_start_date between vals3.effective_start_date and vals3.effective_end_date --added by engy
and ents.effective_start_date between vals4.effective_start_date and vals4.effective_end_date --added by engy
--AND (UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID, P_DATE => FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE)))
--NOT IN ('ANNUAL LEAVE ADVANCE PAY','ADVANCED PAID SALARY'))
--OR UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID, P_DATE => FND_DATE.canonical_to_date(VALS2.SCREEN_ENTRY_VALUE))) <> 'ANNUAL LEAVE ADVANCE PAY')
--AND ASG.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14
--TO GET ALL EMPLOYEES IN GIVEN DATE IF PERSON_ID IS NOT ENTERED IN A GIVEN PERIOD.
AND PPL.PERSON_ID = NVL(P_PERSON_ID,PPL.PERSON_ID)
AND typ.element_name = 'Advanced Pay Information'
AND inp1.name = 'Annual Start Date'
AND inp2.name = 'Annual End Date'
AND inp3.name = 'Unpaid End Date'
AND INP4.NAME = 'Pay Advanced Period'
-- AND FND_DATE.CANONICAL_TO_DATE(VALS1.SCREEN_ENTRY_VALUE) BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
-- AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND (TRUNC(ENTS.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ENTS.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
;
-------------------------- CURSOR OF LEAVE SCREEN ----------------------------
CURSOR GET_ABSENCE IS
SELECT ABA.DATE_START DATE_START, ABA.DATE_END DATE_END,PER.PERSON_ID,
ABA.ABSENCE_DAYS,PAA.ASSIGNMENT_ID,ATA.NAME,PER.EMPLOYEE_NUMBER,PER.FULL_NAME
FROM PER_ABSENCE_ATTENDANCES ABA,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F PAA,
PER_ABSENCE_ATTENDANCE_TYPES ATA
WHERE PER.PERSON_ID = PAA.PERSON_ID
AND PER.PERSON_ID = ABA.PERSON_ID
--AND DECODE(P_PERSON_ID,null
--,( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))
--,DECODE(EVENT_TYPE,'insert',UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))))
--AND XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)
--NOT IN ('Annual Leave','Maternity Leave')
AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
AND UPPER(ATA.NAME) IN ('ANNUAL LEAVE','DELIVERY MATERNITY LEAVE','HAJJ LEAVE')
--ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
AND( ABA.ABSENCE_DAYS > XXPY_CONSALIDATIONS.GET_GLOBAL_VALUE(P_BGID => P_BUSINESS_GROUP_ID,
P_GLOBAL_NAME => 'MIN_ANN_LEAVE_DAYS',
P_DATE => ABA.DATE_START)
OR PAA.PAYROLL_ID IN (61,62,63,64,81))--afzal 17sep14 adding 'or' condition for govt payrolls
AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
P_PAYROLL_ID => PAA.PAYROLL_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_ELEMENT_NAME => 'Advanced Pay Information',
P_INPUT_NAME => 'Annual Start Date',
P_DATE => ABA.DATE_START) = 'N'
AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
-- AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
-- AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')--on 17-6
--AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14
UNION ALL
SELECT ABA.DATE_START DATE_START,
ABA.DATE_END DATE_END,
PER.PERSON_ID,
ABA.ABSENCE_DAYS,
PAA.ASSIGNMENT_ID,
ATA.NAME,
PER.EMPLOYEE_NUMBER,
PER.FULL_NAME
FROM PER_ABSENCE_ATTENDANCES ABA,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F PAA,
PER_ABSENCE_ATTENDANCE_TYPES ATA
WHERE PER.PERSON_ID = PAA.PERSON_ID
AND PER.PERSON_ID = ABA.PERSON_ID
--AND XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)<> 'Long Unpaid Leave'
AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
AND UPPER(ATA.NAME) = 'UNPAID LEAVE'
/* AND XXPY_ABSENCE_CALC.GET_ABSENCE_ID(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_DATE =>FFFUNC.ADD_DAYS(ABA.DATE_START,-1)) = 62*/
AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
P_PAYROLL_ID => PAA.PAYROLL_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_ELEMENT_NAME => 'Advanced Pay Information',
P_INPUT_NAME => 'Unpaid Start Date',
P_DATE => ABA.DATE_START) = 'N'
AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
;
--AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET);
------------------------- GET ASSIGNMENT PERIODS -----------------------------
CURSOR C_Asg(P_ASSIGNMENT_ID NUMBER,P_DATE DATE) IS
/*SELECT ASG.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE
, ASG.OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_DATE < ASG.EFFECTIVE_END_DATE;
*/
SELECT MAX(ASG.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_DATE >= ASG.EFFECTIVE_START_DATE
AND P_DATE <= ASG.EFFECTIVE_END_DATE;
---------------------------- PARAMETERS ----------------------------------
V_EFFECTIVE_START_DATE DATE;
V_EFFECTIVE_END_DATE DATE;
V_DATE DATE;
V_TRACK VARCHAR2(100);
V_DATE_U DATE;
V_OBJECT_VERSION_NUMBER PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
ERROR_FLAG VARCHAR2(5) := 'N';
ADVANCED_START_DATE DATE;
UNPAID_START DATE;
V_DATE_UNPAID DATE;
--BEGIN
BEGIN
FOR I IN GET_CHANGED_EMP LOOP
ADVANCED_START_DATE := FFFUNC.ADD_DAYS(I.X_E_D,1);
--28-02-2015 + 1
------------------------------------------------------------------------------------
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
IF I.ADV_FLAG = 'Y' AND I.DATE_START <> ADVANCED_START_DATE THEN
FOR C IN C_Asg(I.ASSIGNMENT_ID,ADVANCED_START_DATE) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => ADVANCED_START_DATE,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>ADVANCED_START_DATE,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 7096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ADVANCED_START_DATE := I.DATE_START;
END;
END LOOP;
END IF;
FOR C IN C_Asg(I.ASSIGNMENT_ID,I.DATE_START) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => I.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
----------------------------- ADVANCED ANNUAL LEAVE ASSIGNMENT STATUS ----------------------
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>I.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 4097,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
------------------------------------ UNPAID ASSIGNMENT STATUS UPDATE -------------------------
EXCEPTION
WHEN OTHERS THEN
ERROR_FLAG := 'Y';
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
IF ERROR_FLAG = 'N' THEN
BEGIN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END LOOP;
BEGIN
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO V_OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE
ASG.ASSIGNMENT_ID = I.ASSIGNMENT_ID
AND FFFUNC.ADD_DAYS(I.DATE_END,1) >= ASG.EFFECTIVE_START_DATE
AND FFFUNC.ADD_DAYS(I.DATE_END,1) <= ASG.EFFECTIVE_END_DATE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_NUMBER||'-'||SQLERRM);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
BEGIN
dt_api.find_dt_upd_modes
( p_effective_date => FFFUNC.ADD_DAYS(I.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(I.DATE_END,1),--TRUNC(V_DATE_U),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 2,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
---------------------------------- ABSENCE SCREEN ASSIGNMENT UPDATE -------------------------------
FOR X IN GET_ABSENCE LOOP
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Long Unpaid Leave
-- 26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
-- 26 dec 2014 Suspend
FOR C IN C_Asg(X.ASSIGNMENT_ID,X.DATE_START) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => X.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => X.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
IF UPPER(X.NAME) in ('ANNUAL LEAVE' ,'HAJJ LEAVE') THEN
--ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 4096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
ELSIF UPPER(X.NAME) = 'DELIVERY MATERNITY LEAVE'THEN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 9096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
ELSIF UPPER(X.NAME) = UPPER('UNPAID LEAVE') THEN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 5096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERROR_FLAG := 'Y';
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS UPDATE================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
--END;
IF ERROR_FLAG = 'N' THEN
BEGIN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END;
END LOOP;
BEGIN
--SELECT ASG.OBJECT_VERSION_NUMBER
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO V_OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = X.ASSIGNMENT_ID
AND FFFUNC.ADD_DAYS(X.DATE_END,1)< ASG.EFFECTIVE_END_DATE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES_HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
BEGIN
dt_api.find_dt_upd_modes
( p_effective_date => FFFUNC.ADD_DAYS(X.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => X.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(X.DATE_END,1),--X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',--lc_dt_ud_mode,--lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 2,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE_ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
END;
END;
--------------------------Procedure ends here-----------------------------------------
/*--------------------------------------------------*/
Another package to activate the Assignment status.
HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG(P_EFFECTIVE_DATE
=>V_RETURN_DATE,--TRUNC(V_DATE), --V_RETURN_DATE modified by afzal 18 SEP 2014
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => REC.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 3095,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
Updating the assignment status as per the leave applied to custom status(ex: Annual Leave, Unpaid Leave status), and once employee joined again updating status to 'Active'.
This is just part of the whole procedure, but the main api's, syntax can be derived from it.
Package: dt_api.find_dt_upd_modes is used find the update modes in HRMS.
Package: HR_ASSIGNMENT_API.SUSPEND_EMP_ASG is used to update the status (Unpaid Leave status or Annual Leave status, this is custom statuses)
Package : HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG is used to update the status to active.
Above 3 packages examples are given below as syntax.....
PROCEDURE UPDATE_EMP_ASSIG_ST_2(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_BUSINESS_GROUP_ID IN NUMBER,
P_PERSON_ID IN NUMBER,
P_ABSENCE_ATTENDANCE_ID IN NUMBER) IS
--Commented the Session month (before and after) so to trigger particular absence_attendance_id and added as of sysdate
/*
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Long Unpaid Leave
-- 26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
-- 26 dec 2014 Suspend
*/
------------------------------------------------------------------------------------
------------CURSOR TO GET 'Advanced Pay Information' DETAILS.......................
--local variablese
--declare
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
lc_dt_ud_mode varchar2(200);
--parameter vairables
-- P_BUSINESS_GROUP_ID number := 81;
-- EVENT_TYPE varchar2(200) := '';--'insert';--'';
-- P_PERSON_ID number :=null;--26876;
-- P_ABSENCE_ATTENDANCE_ID number :=null;--3700945;
--cursor to bring the advance leave employee details
CURSOR GET_CHANGED_EMP IS
SELECT ASG.ASSIGNMENT_ID
,PPL.LAST_NAME NAME
,PPL.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
,PPL.FULL_NAME FULL_NAME
,ENTS.ELEMENT_ENTRY_ID X_ID
,FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE) DATE_START
,FND_DATE.canonical_to_date(vals2.SCREEN_ENTRY_VALUE) DATE_END
,FND_DATE.canonical_to_date(vals3.SCREEN_ENTRY_VALUE) UNPAID_DATE
,VALS4.SCREEN_ENTRY_VALUE ADV_FLAG
,ENTS.EFFECTIVE_START_DATE X_S_D
,ENTS.EFFECTIVE_END_DATE X_E_D
FROM PER_ALL_PEOPLE_F PPL
,PER_ALL_ASSIGNMENTS_F ASG
,PAY_ELEMENT_TYPES_F TYP
,PAY_INPUT_VALUES_F INP1
,PAY_INPUT_VALUES_F INP2
,PAY_INPUT_VALUES_F INP3
,PAY_INPUT_VALUES_F INP4
,PAY_ELEMENT_ENTRIES_F ENTS
,PAY_ELEMENT_ENTRY_VALUES_F VALS1
,PAY_ELEMENT_ENTRY_VALUES_F VALS2
,PAY_ELEMENT_ENTRY_VALUES_F VALS3
,PAY_ELEMENT_ENTRY_VALUES_F VALS4
WHERE inp1.element_type_id = typ.element_type_id
AND INP2.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
AND INP3.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
AND INP4.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADded by engy
AND ents.element_type_id = typ.element_type_id
AND vals1.element_entry_id = ents.element_entry_id --added by engy
AND vals2.element_entry_id = ents.element_entry_id --added by engy
AND vals3.element_entry_id = ents.element_entry_id --added by engy
AND vals4.element_entry_id = ents.element_entry_id --added by engy
AND ppl.person_id = asg.person_id
AND ents.assignment_id = asg.assignment_id
AND ASG.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND vals1.input_value_id =inp1.input_value_id
and vals2.input_value_id=inp2.input_value_id --added by engy
and vals3.input_value_id=inp3.input_value_id --added by engy
and vals4.input_value_id=inp4.input_value_id --added by engy
AND ents.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND ents.effective_start_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
AND ents.effective_start_date BETWEEN typ.effective_start_date AND typ.effective_end_date
AND ents.effective_start_date BETWEEN inp1.effective_start_date AND inp1.effective_end_date
AND ents.effective_start_date between ents.effective_start_date and ents.effective_end_date
and ents.effective_start_date between vals1.effective_start_date and vals1.effective_end_date--added by engy
and ents.effective_start_date between vals2.effective_start_date and vals2.effective_end_date--added by engy
and ents.effective_start_date between vals3.effective_start_date and vals3.effective_end_date --added by engy
and ents.effective_start_date between vals4.effective_start_date and vals4.effective_end_date --added by engy
--AND (UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID, P_DATE => FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE)))
--NOT IN ('ANNUAL LEAVE ADVANCE PAY','ADVANCED PAID SALARY'))
--OR UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID, P_DATE => FND_DATE.canonical_to_date(VALS2.SCREEN_ENTRY_VALUE))) <> 'ANNUAL LEAVE ADVANCE PAY')
--AND ASG.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14
--TO GET ALL EMPLOYEES IN GIVEN DATE IF PERSON_ID IS NOT ENTERED IN A GIVEN PERIOD.
AND PPL.PERSON_ID = NVL(P_PERSON_ID,PPL.PERSON_ID)
AND typ.element_name = 'Advanced Pay Information'
AND inp1.name = 'Annual Start Date'
AND inp2.name = 'Annual End Date'
AND inp3.name = 'Unpaid End Date'
AND INP4.NAME = 'Pay Advanced Period'
-- AND FND_DATE.CANONICAL_TO_DATE(VALS1.SCREEN_ENTRY_VALUE) BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
-- AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND (TRUNC(ENTS.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ENTS.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
;
-------------------------- CURSOR OF LEAVE SCREEN ----------------------------
CURSOR GET_ABSENCE IS
SELECT ABA.DATE_START DATE_START, ABA.DATE_END DATE_END,PER.PERSON_ID,
ABA.ABSENCE_DAYS,PAA.ASSIGNMENT_ID,ATA.NAME,PER.EMPLOYEE_NUMBER,PER.FULL_NAME
FROM PER_ABSENCE_ATTENDANCES ABA,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F PAA,
PER_ABSENCE_ATTENDANCE_TYPES ATA
WHERE PER.PERSON_ID = PAA.PERSON_ID
AND PER.PERSON_ID = ABA.PERSON_ID
--AND DECODE(P_PERSON_ID,null
--,( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))
--,DECODE(EVENT_TYPE,'insert',UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))))
--AND XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)
--NOT IN ('Annual Leave','Maternity Leave')
AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
AND UPPER(ATA.NAME) IN ('ANNUAL LEAVE','DELIVERY MATERNITY LEAVE','HAJJ LEAVE')
--ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
AND( ABA.ABSENCE_DAYS > XXPY_CONSALIDATIONS.GET_GLOBAL_VALUE(P_BGID => P_BUSINESS_GROUP_ID,
P_GLOBAL_NAME => 'MIN_ANN_LEAVE_DAYS',
P_DATE => ABA.DATE_START)
OR PAA.PAYROLL_ID IN (61,62,63,64,81))--afzal 17sep14 adding 'or' condition for govt payrolls
AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
P_PAYROLL_ID => PAA.PAYROLL_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_ELEMENT_NAME => 'Advanced Pay Information',
P_INPUT_NAME => 'Annual Start Date',
P_DATE => ABA.DATE_START) = 'N'
AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
-- AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
-- AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')--on 17-6
--AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14
UNION ALL
SELECT ABA.DATE_START DATE_START,
ABA.DATE_END DATE_END,
PER.PERSON_ID,
ABA.ABSENCE_DAYS,
PAA.ASSIGNMENT_ID,
ATA.NAME,
PER.EMPLOYEE_NUMBER,
PER.FULL_NAME
FROM PER_ABSENCE_ATTENDANCES ABA,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F PAA,
PER_ABSENCE_ATTENDANCE_TYPES ATA
WHERE PER.PERSON_ID = PAA.PERSON_ID
AND PER.PERSON_ID = ABA.PERSON_ID
--AND XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)<> 'Long Unpaid Leave'
AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
AND UPPER(ATA.NAME) = 'UNPAID LEAVE'
/* AND XXPY_ABSENCE_CALC.GET_ABSENCE_ID(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_DATE =>FFFUNC.ADD_DAYS(ABA.DATE_START,-1)) = 62*/
AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
P_PAYROLL_ID => PAA.PAYROLL_ID,
P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
P_ELEMENT_NAME => 'Advanced Pay Information',
P_INPUT_NAME => 'Unpaid Start Date',
P_DATE => ABA.DATE_START) = 'N'
AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
--AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
;
--AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET);
------------------------- GET ASSIGNMENT PERIODS -----------------------------
CURSOR C_Asg(P_ASSIGNMENT_ID NUMBER,P_DATE DATE) IS
/*SELECT ASG.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE
, ASG.OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_DATE < ASG.EFFECTIVE_END_DATE;
*/
SELECT MAX(ASG.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_DATE >= ASG.EFFECTIVE_START_DATE
AND P_DATE <= ASG.EFFECTIVE_END_DATE;
---------------------------- PARAMETERS ----------------------------------
V_EFFECTIVE_START_DATE DATE;
V_EFFECTIVE_END_DATE DATE;
V_DATE DATE;
V_TRACK VARCHAR2(100);
V_DATE_U DATE;
V_OBJECT_VERSION_NUMBER PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
ERROR_FLAG VARCHAR2(5) := 'N';
ADVANCED_START_DATE DATE;
UNPAID_START DATE;
V_DATE_UNPAID DATE;
--BEGIN
BEGIN
FOR I IN GET_CHANGED_EMP LOOP
ADVANCED_START_DATE := FFFUNC.ADD_DAYS(I.X_E_D,1);
--28-02-2015 + 1
------------------------------------------------------------------------------------
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
-- 26 dec 2014 Suspend
IF I.ADV_FLAG = 'Y' AND I.DATE_START <> ADVANCED_START_DATE THEN
FOR C IN C_Asg(I.ASSIGNMENT_ID,ADVANCED_START_DATE) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => ADVANCED_START_DATE,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>ADVANCED_START_DATE,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 7096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION WHEN OTHERS THEN
ADVANCED_START_DATE := I.DATE_START;
END;
END LOOP;
END IF;
FOR C IN C_Asg(I.ASSIGNMENT_ID,I.DATE_START) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => I.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
----------------------------- ADVANCED ANNUAL LEAVE ASSIGNMENT STATUS ----------------------
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>I.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 4097,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
------------------------------------ UNPAID ASSIGNMENT STATUS UPDATE -------------------------
EXCEPTION
WHEN OTHERS THEN
ERROR_FLAG := 'Y';
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
IF ERROR_FLAG = 'N' THEN
BEGIN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END LOOP;
BEGIN
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO V_OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE
ASG.ASSIGNMENT_ID = I.ASSIGNMENT_ID
AND FFFUNC.ADD_DAYS(I.DATE_END,1) >= ASG.EFFECTIVE_START_DATE
AND FFFUNC.ADD_DAYS(I.DATE_END,1) <= ASG.EFFECTIVE_END_DATE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_NUMBER||'-'||SQLERRM);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
BEGIN
dt_api.find_dt_upd_modes
( p_effective_date => FFFUNC.ADD_DAYS(I.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => I.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(I.DATE_END,1),--TRUNC(V_DATE_U),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',
P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 2,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || I.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || I.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' ||'Annual Leave' );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
---------------------------------- ABSENCE SCREEN ASSIGNMENT UPDATE -------------------------------
FOR X IN GET_ABSENCE LOOP
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Annual Leave
-- 26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as Long Unpaid Leave
-- 26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
-- 05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
-- 26 dec 2014 Suspend
FOR C IN C_Asg(X.ASSIGNMENT_ID,X.DATE_START) LOOP
dt_api.find_dt_upd_modes
( p_effective_date => X.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => X.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
BEGIN
IF UPPER(X.NAME) in ('ANNUAL LEAVE' ,'HAJJ LEAVE') THEN
--ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 4096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
ELSIF UPPER(X.NAME) = 'DELIVERY MATERNITY LEAVE'THEN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 9096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
ELSIF UPPER(X.NAME) = UPPER('UNPAID LEAVE') THEN
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 5096,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERROR_FLAG := 'Y';
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS UPDATE================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
--END;
IF ERROR_FLAG = 'N' THEN
BEGIN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END;
END LOOP;
BEGIN
--SELECT ASG.OBJECT_VERSION_NUMBER
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO V_OBJECT_VERSION_NUMBER
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE='E'
AND ASG.ASSIGNMENT_ID = X.ASSIGNMENT_ID
AND FFFUNC.ADD_DAYS(X.DATE_END,1)< ASG.EFFECTIVE_END_DATE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES_HAVE ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
BEGIN
dt_api.find_dt_upd_modes
( p_effective_date => FFFUNC.ADD_DAYS(X.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
p_base_table_name => 'PER_ALL_ASSIGNMENTS_F',
p_base_key_column => 'ASSIGNMENT_ID',
p_base_key_value => X.ASSIGNMENT_ID,--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 )
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;
IF ( lb_correction = TRUE )
THEN lc_dt_ud_mode := 'CORRECTION';
END IF;
IF ( lb_update = TRUE )
THEN lc_dt_ud_mode := 'UPDATE';
END IF;
HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(X.DATE_END,1),--X.DATE_START,--TRUNC(V_DATE),
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',--lc_dt_ud_mode,--lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 2,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE_ERRORS================');
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number :' || X.EMPLOYEE_NUMBER);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name :' || X.FULL_NAME);
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type :' || X.NAME );
fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
END;
END;
--------------------------Procedure ends here-----------------------------------------
/*--------------------------------------------------*/
Another package to activate the Assignment status.
HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG(P_EFFECTIVE_DATE
=>V_RETURN_DATE,--TRUNC(V_DATE), --V_RETURN_DATE modified by afzal 18 SEP 2014
P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
P_ASSIGNMENT_ID => REC.ASSIGNMENT_ID ,
P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
P_ASSIGNMENT_STATUS_TYPE_ID => 3095,
P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
No comments:
Post a Comment