Employee Absence uploading in Oracle HRMS
How to find correct API for Data Uploading
Please find the table definition and procedure below.
TABLE
CREATION PROCEDURE
UPDATE PROCEDURE
DELETE PROCEDURE
I hope this post is helpful. Feedback is much appreciated.!
Please find the table definition and procedure below.
TABLE
CREATE TABLE DEV_ABSENCE_ATTENDANCES
(
LINE_ID NUMBER(10),
ABSENCE_ATTENDANCE_ID NUMBER(10),
BUSINESS_GROUP_ID NUMBER(15),
ABSENCE_ATTENDANCE_TYPE VARCHAR2(200 BYTE),
ABS_ATTENDANCE_REASON VARCHAR2(200 BYTE),
EMPLOYEE_NUMBER VARCHAR2(200 BYTE),
AUTHORISING_PERSON VARCHAR2(200 BYTE),
REPLACEMENT_PERSON VARCHAR2(200 BYTE),
PERIOD_OF_INCAPACITY NUMBER(9),
ABSENCE_DAYS NUMBER(9,4),
ABSENCE_HOURS NUMBER(9,4),
DATE_END DATE,
DATE_NOTIFICATION DATE,
DATE_PROJECTED_END DATE,
DATE_PROJECTED_START DATE,
DATE_START DATE,
OCCURRENCE NUMBER(15),
SSP1_ISSUED VARCHAR2(30 BYTE),
TIME_END VARCHAR2(5 BYTE),
TIME_PROJECTED_END VARCHAR2(5 BYTE),
TIME_PROJECTED_START VARCHAR2(5 BYTE),
TIME_START VARCHAR2(5 BYTE),
REQUEST_ID NUMBER(15),
PROGRAM_APPLICATION_ID NUMBER(15),
PROGRAM_ID NUMBER(15),
PROGRAM_UPDATE_DATE DATE,
ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE),
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150
BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
ATTRIBUTE16 VARCHAR2(150 BYTE),
ATTRIBUTE17 VARCHAR2(150 BYTE),
ATTRIBUTE18 VARCHAR2(150 BYTE),
ATTRIBUTE19 VARCHAR2(150 BYTE),
ATTRIBUTE20 VARCHAR2(150 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_LOGIN NUMBER(15),
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
MATERNITY_ID NUMBER,
SICKNESS_START_DATE DATE,
SICKNESS_END_DATE DATE,
PREGNANCY_RELATED_ILLNESS VARCHAR2(30 BYTE),
REASON_FOR_NOTIFICATION_DELAY VARCHAR2(2000 BYTE),
ACCEPT_LATE_NOTIFICATION_FLAG VARCHAR2(30 BYTE) DEFAULT 'Y',
LINKED_ABSENCE_ID NUMBER,
BATCH_ID NUMBER(9),
ABS_INFORMATION_CATEGORY VARCHAR2(30 BYTE),
ABS_INFORMATION1 VARCHAR2(150 BYTE),
ABS_INFORMATION2 VARCHAR2(150 BYTE),
ABS_INFORMATION3 VARCHAR2(150 BYTE),
ABS_INFORMATION4 VARCHAR2(150 BYTE),
ABS_INFORMATION5 VARCHAR2(150 BYTE),
ABS_INFORMATION6 VARCHAR2(150 BYTE),
ABS_INFORMATION7 VARCHAR2(150 BYTE),
ABS_INFORMATION8 VARCHAR2(150 BYTE),
ABS_INFORMATION9 VARCHAR2(150 BYTE),
ABS_INFORMATION10 VARCHAR2(150 BYTE),
ABS_INFORMATION11 VARCHAR2(150 BYTE),
ABS_INFORMATION12 VARCHAR2(150 BYTE),
ABS_INFORMATION13 VARCHAR2(150 BYTE),
ABS_INFORMATION14 VARCHAR2(150 BYTE),
ABS_INFORMATION15 VARCHAR2(150 BYTE),
ABS_INFORMATION16 VARCHAR2(150 BYTE),
ABS_INFORMATION17 VARCHAR2(150 BYTE),
ABS_INFORMATION18 VARCHAR2(150 BYTE),
ABS_INFORMATION19 VARCHAR2(150 BYTE),
ABS_INFORMATION20 VARCHAR2(150 BYTE),
ABS_INFORMATION21 VARCHAR2(150 BYTE),
ABS_INFORMATION22 VARCHAR2(150 BYTE),
ABS_INFORMATION23 VARCHAR2(150 BYTE),
ABS_INFORMATION24 VARCHAR2(150 BYTE),
ABS_INFORMATION25 VARCHAR2(150 BYTE),
ABS_INFORMATION26 VARCHAR2(150 BYTE),
ABS_INFORMATION27 VARCHAR2(150 BYTE),
ABS_INFORMATION28 VARCHAR2(150 BYTE),
ABS_INFORMATION29 VARCHAR2(150 BYTE),
ABS_INFORMATION30 VARCHAR2(150 BYTE),
OBJECT_VERSION_NUMBER NUMBER(9),
COMMENTS LONG,
ABSENCE_CASE_ID NUMBER(10),
APPROVAL_STATUS VARCHAR2(30 BYTE),
CONFIRMED_UNTIL DATE,
SOURCE VARCHAR2(30 BYTE),
ERROR_DESCRIPTION VARCHAR2(2000 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.DEV_UPLOAD_PERSON_ABSENCE
IS
CURSOR C_ABSENCE
IS
SELECT LINE_ID,
ABSENCE_ATTENDANCE_ID, BUSINESS_GROUP_ID,
ABSENCE_ATTENDANCE_TYPE,
ABS_ATTENDANCE_REASON,
EMPLOYEE_NUMBER, AUTHORISING_PERSON, REPLACEMENT_PERSON,
PERIOD_OF_INCAPACITY, ABSENCE_DAYS, ABSENCE_HOURS, DATE_END,
DATE_NOTIFICATION, DATE_PROJECTED_END, DATE_PROJECTED_START,
DATE_START, OCCURRENCE, SSP1_ISSUED, TIME_END,
TIME_PROJECTED_END, TIME_PROJECTED_START, TIME_START,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19,
ATTRIBUTE20, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, MATERNITY_ID,
SICKNESS_START_DATE, SICKNESS_END_DATE,
PREGNANCY_RELATED_ILLNESS,
REASON_FOR_NOTIFICATION_DELAY,
ACCEPT_LATE_NOTIFICATION_FLAG,
LINKED_ABSENCE_ID, BATCH_ID,
ABS_INFORMATION_CATEGORY, ABS_INFORMATION1, ABS_INFORMATION2,
ABS_INFORMATION3, ABS_INFORMATION4, ABS_INFORMATION5,
ABS_INFORMATION6, ABS_INFORMATION7, ABS_INFORMATION8,
ABS_INFORMATION9, ABS_INFORMATION10, ABS_INFORMATION11,
ABS_INFORMATION12, ABS_INFORMATION13, ABS_INFORMATION14,
ABS_INFORMATION15, ABS_INFORMATION16, ABS_INFORMATION17,
ABS_INFORMATION18, ABS_INFORMATION19, ABS_INFORMATION20,
ABS_INFORMATION21, ABS_INFORMATION22, ABS_INFORMATION23,
ABS_INFORMATION24, ABS_INFORMATION25, ABS_INFORMATION26,
ABS_INFORMATION27, ABS_INFORMATION28, ABS_INFORMATION29,
ABS_INFORMATION30, OBJECT_VERSION_NUMBER, COMMENTS,
ABSENCE_CASE_ID, APPROVAL_STATUS, CONFIRMED_UNTIL, SOURCE,
ERROR_DESCRIPTION
FROM DEV_ABSENCE_ATTENDANCES
ORDER BY 1 ASC;
-- VARIABLES
LC_C_ABSENCE
C_ABSENCE%ROWTYPE;
L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_ABS_ATTENDANCE_REASON_ID NUMBER;
L_PERSON_ID NUMBER;
L_AUTHORISING_PERSON_ID NUMBER;
L_REPLACEMENT_PERSON_ID NUMBER;
L_ABSENCE_CASE_ID NUMBER;
L_ABSENCE_ATTENDANCE_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_OCCURRENCE NUMBER;
L_DUR_DYS_LESS_WARNING BOOLEAN;
L_DUR_HRS_LESS_WARNING BOOLEAN;
L_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN;
L_EXCEEDS_RUN_TOTAL_WARNING BOOLEAN;
L_ABS_OVERLAP_WARNING BOOLEAN;
L_ABS_DAY_AFTER_WARNING BOOLEAN;
L_DUR_OVERWRITTEN_WARNING BOOLEAN;
ERROR_DESC VARCHAR2 (2000);
BEGIN
OPEN C_ABSENCE;
LOOP
FETCH C_ABSENCE
INTO LC_C_ABSENCE;
BEGIN
SELECT DISTINCT PPF.PERSON_ID
INTO L_PERSON_ID
FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.EMPLOYEE_NUMBER;
EXCEPTION
WHEN OTHERS
THEN
L_PERSON_ID := NULL;
END;
BEGIN
SELECT DISTINCT PPF.PERSON_ID
INTO L_AUTHORISING_PERSON_ID
FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.AUTHORISING_PERSON;
EXCEPTION
WHEN OTHERS
THEN
L_AUTHORISING_PERSON_ID := NULL;
END;
BEGIN
SELECT DISTINCT PPF.PERSON_ID
INTO L_REPLACEMENT_PERSON_ID
FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.REPLACEMENT_PERSON;
EXCEPTION
WHEN OTHERS
THEN
L_REPLACEMENT_PERSON_ID := NULL;
END;
BEGIN
SELECT ABSENCE_ATTENDANCE_TYPE_ID
INTO L_ABSENCE_ATTENDANCE_TYPE_ID
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE UPPER (NAME) = UPPER (LC_C_ABSENCE.ABSENCE_ATTENDANCE_TYPE);
EXCEPTION
WHEN OTHERS
THEN
L_ABSENCE_ATTENDANCE_TYPE_ID :=
NULL;
END;
BEGIN
SELECT PAAR.ABS_ATTENDANCE_REASON_ID
INTO L_ABS_ATTENDANCE_REASON_ID
FROM PER_ABS_ATTENDANCE_REASONS
PAAR, HR_LOOKUPS HL
WHERE HL.LOOKUP_TYPE = 'ABSENCE_REASON'
AND PAAR.NAME = HL.LOOKUP_CODE
AND UPPER (HL.MEANING) =
UPPER (LC_C_ABSENCE.ABS_ATTENDANCE_REASON);
EXCEPTION
WHEN OTHERS
THEN
L_ABS_ATTENDANCE_REASON_ID :=
NULL;
END;
EXIT WHEN C_ABSENCE%NOTFOUND;
BEGIN
HR_PERSON_ABSENCE_API.CREATE_PERSON_ABSENCE
(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE
=> SYSDATE,
P_PERSON_ID
=> L_PERSON_ID,
P_BUSINESS_GROUP_ID
=> 0,
P_ABSENCE_ATTENDANCE_TYPE_ID
=> L_ABSENCE_ATTENDANCE_TYPE_ID,
P_ABS_ATTENDANCE_REASON_ID
=> L_ABS_ATTENDANCE_REASON_ID,
P_COMMENTS
=> LC_C_ABSENCE.COMMENTS,
P_DATE_NOTIFICATION
=> LC_C_ABSENCE.DATE_NOTIFICATION,
P_DATE_PROJECTED_START => LC_C_ABSENCE.DATE_PROJECTED_START,
P_TIME_PROJECTED_START
=> LC_C_ABSENCE.TIME_PROJECTED_START,
P_DATE_PROJECTED_END
=> LC_C_ABSENCE.DATE_PROJECTED_END,
P_TIME_PROJECTED_END
=> LC_C_ABSENCE.TIME_PROJECTED_END,
P_DATE_START
=> LC_C_ABSENCE.DATE_START,
P_TIME_START
=> LC_C_ABSENCE.TIME_START,
P_DATE_END
=> LC_C_ABSENCE.DATE_END,
P_TIME_END
=> LC_C_ABSENCE.TIME_END,
P_ABSENCE_DAYS
=> LC_C_ABSENCE.ABSENCE_DAYS,
P_ABSENCE_HOURS
=> LC_C_ABSENCE.ABSENCE_HOURS,
P_AUTHORISING_PERSON_ID
=> L_AUTHORISING_PERSON_ID,
P_REPLACEMENT_PERSON_ID
=> L_REPLACEMENT_PERSON_ID,
P_ATTRIBUTE_CATEGORY
=> LC_C_ABSENCE.ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1
=> LC_C_ABSENCE.ATTRIBUTE1,
P_ATTRIBUTE2
=> LC_C_ABSENCE.ATTRIBUTE2,
P_ATTRIBUTE3
=> LC_C_ABSENCE.ATTRIBUTE3,
P_ATTRIBUTE4
=> LC_C_ABSENCE.ATTRIBUTE4,
P_ATTRIBUTE5
=> LC_C_ABSENCE.ATTRIBUTE5,
P_ATTRIBUTE6
=> LC_C_ABSENCE.ATTRIBUTE6,
P_ATTRIBUTE7 => LC_C_ABSENCE.ATTRIBUTE7,
P_ATTRIBUTE8
=> LC_C_ABSENCE.ATTRIBUTE8,
P_ATTRIBUTE9
=> LC_C_ABSENCE.ATTRIBUTE9,
P_ATTRIBUTE10
=> LC_C_ABSENCE.ATTRIBUTE10,
P_ATTRIBUTE11
=> LC_C_ABSENCE.ATTRIBUTE11,
P_ATTRIBUTE12
=> LC_C_ABSENCE.ATTRIBUTE12,
P_ATTRIBUTE13
=> LC_C_ABSENCE.ATTRIBUTE13,
P_ATTRIBUTE14
=> LC_C_ABSENCE.ATTRIBUTE14,
P_ATTRIBUTE15
=> LC_C_ABSENCE.ATTRIBUTE15,
P_ATTRIBUTE16 => LC_C_ABSENCE.ATTRIBUTE16,
P_ATTRIBUTE17
=> LC_C_ABSENCE.ATTRIBUTE17,
P_ATTRIBUTE18
=> LC_C_ABSENCE.ATTRIBUTE18,
P_ATTRIBUTE19
=> LC_C_ABSENCE.ATTRIBUTE19,
P_ATTRIBUTE20
=> LC_C_ABSENCE.ATTRIBUTE20,
P_PERIOD_OF_INCAPACITY_ID
=> LC_C_ABSENCE.PERIOD_OF_INCAPACITY,
P_SSP1_ISSUED
=> 'N',
P_MATERNITY_ID
=> LC_C_ABSENCE.MATERNITY_ID,
P_SICKNESS_START_DATE
=> LC_C_ABSENCE.SICKNESS_START_DATE,
P_SICKNESS_END_DATE
=> LC_C_ABSENCE.SICKNESS_END_DATE,
P_PREGNANCY_RELATED_ILLNESS => 'N',
P_REASON_FOR_NOTIFICATION_DELA
=> LC_C_ABSENCE.REASON_FOR_NOTIFICATION_DELAY,
P_ACCEPT_LATE_NOTIFICATION_FLA
=> 'N',
P_LINKED_ABSENCE_ID
=> LC_C_ABSENCE.LINKED_ABSENCE_ID,
P_BATCH_ID
=> LC_C_ABSENCE.BATCH_ID,
P_CREATE_ELEMENT_ENTRY => TRUE,
P_ABS_INFORMATION_CATEGORY
=> LC_C_ABSENCE.ABS_INFORMATION_CATEGORY,
P_ABS_INFORMATION1 => LC_C_ABSENCE.ABS_INFORMATION1,
P_ABS_INFORMATION2
=> LC_C_ABSENCE.ABS_INFORMATION2,
P_ABS_INFORMATION3
=> LC_C_ABSENCE.ABS_INFORMATION3,
P_ABS_INFORMATION4
=> LC_C_ABSENCE.ABS_INFORMATION4,
P_ABS_INFORMATION5
=> LC_C_ABSENCE.ABS_INFORMATION5,
P_ABS_INFORMATION6
=> LC_C_ABSENCE.ABS_INFORMATION6,
P_ABS_INFORMATION7 => LC_C_ABSENCE.ABS_INFORMATION7,
P_ABS_INFORMATION8
=> LC_C_ABSENCE.ABS_INFORMATION8,
P_ABS_INFORMATION9
=> LC_C_ABSENCE.ABS_INFORMATION9,
P_ABS_INFORMATION10 => LC_C_ABSENCE.ABS_INFORMATION10,
P_ABS_INFORMATION11
=> LC_C_ABSENCE.ABS_INFORMATION11,
P_ABS_INFORMATION12
=> LC_C_ABSENCE.ABS_INFORMATION12,
P_ABS_INFORMATION13
=> LC_C_ABSENCE.ABS_INFORMATION13,
P_ABS_INFORMATION14
=> LC_C_ABSENCE.ABS_INFORMATION14,
P_ABS_INFORMATION15
=> LC_C_ABSENCE.ABS_INFORMATION15,
P_ABS_INFORMATION16 => LC_C_ABSENCE.ABS_INFORMATION16,
P_ABS_INFORMATION17
=> LC_C_ABSENCE.ABS_INFORMATION17,
P_ABS_INFORMATION18
=> LC_C_ABSENCE.ABS_INFORMATION18,
P_ABS_INFORMATION19
=> LC_C_ABSENCE.ABS_INFORMATION19,
P_ABS_INFORMATION20
=> LC_C_ABSENCE.ABS_INFORMATION20,
P_ABS_INFORMATION21
=> LC_C_ABSENCE.ABS_INFORMATION21,
P_ABS_INFORMATION22 => LC_C_ABSENCE.ABS_INFORMATION22,
P_ABS_INFORMATION23
=> LC_C_ABSENCE.ABS_INFORMATION23,
P_ABS_INFORMATION24
=> LC_C_ABSENCE.ABS_INFORMATION24,
P_ABS_INFORMATION25 => LC_C_ABSENCE.ABS_INFORMATION25,
P_ABS_INFORMATION26
=> LC_C_ABSENCE.ABS_INFORMATION26,
P_ABS_INFORMATION27
=> LC_C_ABSENCE.ABS_INFORMATION27,
P_ABS_INFORMATION28 => LC_C_ABSENCE.ABS_INFORMATION28,
P_ABS_INFORMATION29
=> LC_C_ABSENCE.ABS_INFORMATION29,
P_ABS_INFORMATION30
=> LC_C_ABSENCE.ABS_INFORMATION30,
P_ABSENCE_CASE_ID
=> L_ABSENCE_CASE_ID,
P_ABSENCE_ATTENDANCE_ID
=> L_ABSENCE_ATTENDANCE_ID,
P_OBJECT_VERSION_NUMBER
=> L_OBJECT_VERSION_NUMBER,
P_OCCURRENCE => L_OCCURRENCE,
P_DUR_DYS_LESS_WARNING
=> L_DUR_DYS_LESS_WARNING,
P_DUR_HRS_LESS_WARNING
=> L_DUR_HRS_LESS_WARNING,
P_EXCEEDS_PTO_ENTIT_WARNING
=> L_EXCEEDS_PTO_ENTIT_WARNING,
P_EXCEEDS_RUN_TOTAL_WARNING
=> L_EXCEEDS_RUN_TOTAL_WARNING,
P_ABS_OVERLAP_WARNING
=> L_ABS_OVERLAP_WARNING,
P_ABS_DAY_AFTER_WARNING
=> L_ABS_DAY_AFTER_WARNING,
P_DUR_OVERWRITTEN_WARNING
=> L_DUR_OVERWRITTEN_WARNING
);
IF L_ABSENCE_ATTENDANCE_ID IS NOT NULL
THEN
ERROR_DESC := 'NO ERROR';
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
END IF;
IF L_ABSENCE_ATTENDANCE_ID IS NULL
THEN
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
END IF;
-- DBMS_OUTPUT.PUT_LINE ('ELEMENT TYPE
ID : ' || L_ELEMENT_TYPE_ID);*/
EXCEPTION
WHEN OTHERS
THEN
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
--DBMS_OUTPUT.PUT_LINE
(SQLERRM);
END;
--COMMIT;
END LOOP;
CLOSE C_ABSENCE;
END;
/
UPDATE PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_UPDATE_PERSON_ABSENCE
IS
CURSOR C_ABSENCE
IS
SELECT LINE_ID,
ABSENCE_ATTENDANCE_ID, BUSINESS_GROUP_ID,
ABSENCE_ATTENDANCE_TYPE,
ABS_ATTENDANCE_REASON, EMPLOYEE_NUMBER,
AUTHORISING_PERSON, REPLACEMENT_PERSON, PERIOD_OF_INCAPACITY,
ABSENCE_DAYS, ABSENCE_HOURS, DATE_END, DATE_NOTIFICATION,
DATE_PROJECTED_END, DATE_PROJECTED_START, DATE_START, OCCURRENCE,
SSP1_ISSUED, TIME_END, TIME_PROJECTED_END, TIME_PROJECTED_START,
TIME_START, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
ATTRIBUTE19, ATTRIBUTE20, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, MATERNITY_ID,
SICKNESS_START_DATE, SICKNESS_END_DATE, PREGNANCY_RELATED_ILLNESS,
REASON_FOR_NOTIFICATION_DELAY,
ACCEPT_LATE_NOTIFICATION_FLAG,
LINKED_ABSENCE_ID, BATCH_ID, ABS_INFORMATION_CATEGORY,
ABS_INFORMATION1, ABS_INFORMATION2, ABS_INFORMATION3, ABS_INFORMATION4,
ABS_INFORMATION5, ABS_INFORMATION6, ABS_INFORMATION7, ABS_INFORMATION8,
ABS_INFORMATION9, ABS_INFORMATION10, ABS_INFORMATION11,
ABS_INFORMATION12, ABS_INFORMATION13, ABS_INFORMATION14,
ABS_INFORMATION15, ABS_INFORMATION16, ABS_INFORMATION17,
ABS_INFORMATION18, ABS_INFORMATION19, ABS_INFORMATION20,
ABS_INFORMATION21, ABS_INFORMATION22, ABS_INFORMATION23,
ABS_INFORMATION24, ABS_INFORMATION25, ABS_INFORMATION26,
ABS_INFORMATION27, ABS_INFORMATION28, ABS_INFORMATION29,
ABS_INFORMATION30, OBJECT_VERSION_NUMBER, COMMENTS, ABSENCE_CASE_ID,
APPROVAL_STATUS, CONFIRMED_UNTIL, SOURCE, ERROR_DESCRIPTION
FROM DEV_ABSENCE_ATTENDANCES
ORDER BY 1 ASC;
-- VARIABLES
LC_C_ABSENCE
C_ABSENCE%ROWTYPE;
L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_ABS_ATTENDANCE_REASON_ID NUMBER;
L_PERSON_ID NUMBER;
L_AUTHORISING_PERSON_ID NUMBER;
L_REPLACEMENT_PERSON_ID NUMBER;
L_ABSENCE_CASE_ID NUMBER;
L_ABSENCE_ATTENDANCE_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_OCCURRENCE NUMBER;
L_DUR_DYS_LESS_WARNING BOOLEAN;
L_DUR_HRS_LESS_WARNING BOOLEAN;
L_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN;
L_EXCEEDS_RUN_TOTAL_WARNING BOOLEAN;
L_ABS_OVERLAP_WARNING BOOLEAN;
L_ABS_DAY_AFTER_WARNING BOOLEAN;
L_DUR_OVERWRITTEN_WARNING BOOLEAN;
L_DEL_ELEMENT_ENTRY_WARNING BOOLEAN;
ERROR_DESC VARCHAR2(2000);
BEGIN
OPEN C_ABSENCE;
LOOP
FETCH C_ABSENCE
INTO LC_C_ABSENCE;
BEGIN
SELECT DISTINCT PPF.PERSON_ID INTO L_PERSON_ID FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.EMPLOYEE_NUMBER;
EXCEPTION WHEN OTHERS THEN
L_PERSON_ID := NULL;
END;
BEGIN
SELECT DISTINCT PPF.PERSON_ID INTO L_AUTHORISING_PERSON_ID FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.AUTHORISING_PERSON;
EXCEPTION WHEN OTHERS THEN
L_AUTHORISING_PERSON_ID := NULL;
END;
BEGIN
SELECT DISTINCT PPF.PERSON_ID INTO L_REPLACEMENT_PERSON_ID FROM PER_PEOPLE_F PPF
WHERE PPF.EMPLOYEE_NUMBER = LC_C_ABSENCE.REPLACEMENT_PERSON;
EXCEPTION WHEN OTHERS THEN
L_REPLACEMENT_PERSON_ID := NULL;
END;
BEGIN
SELECT ABSENCE_ATTENDANCE_TYPE_ID INTO L_ABSENCE_ATTENDANCE_TYPE_ID
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE UPPER(NAME) = UPPER(LC_C_ABSENCE.ABSENCE_ATTENDANCE_TYPE);
EXCEPTION WHEN OTHERS THEN
L_ABSENCE_ATTENDANCE_TYPE_ID := NULL;
END;
BEGIN
SELECT PAAR.ABS_ATTENDANCE_REASON_ID INTO L_ABS_ATTENDANCE_REASON_ID FROM PER_ABS_ATTENDANCE_REASONS
PAAR , HR_LOOKUPS HL
WHERE HL.LOOKUP_TYPE = 'ABSENCE_REASON'
AND PAAR.NAME = HL.LOOKUP_CODE
AND UPPER(HL.MEANING) = UPPER(LC_C_ABSENCE.ABS_ATTENDANCE_REASON);
EXCEPTION WHEN OTHERS THEN
L_ABS_ATTENDANCE_REASON_ID := NULL;
END;
EXIT WHEN C_ABSENCE%NOTFOUND;
BEGIN
hr_person_absence_api.update_person_absence
(P_VALIDATE => FALSE
,P_EFFECTIVE_DATE => SYSDATE
,P_ABSENCE_ATTENDANCE_ID => LC_C_ABSENCE.ABSENCE_ATTENDANCE_ID
,P_ABS_ATTENDANCE_REASON_ID => L_ABS_ATTENDANCE_REASON_ID
,P_COMMENTS => LC_C_ABSENCE.COMMENTS
,P_DATE_NOTIFICATION => LC_C_ABSENCE.DATE_NOTIFICATION
,P_DATE_PROJECTED_START => LC_C_ABSENCE.DATE_PROJECTED_START
,P_TIME_PROJECTED_START => LC_C_ABSENCE.TIME_PROJECTED_START
,P_DATE_PROJECTED_END => LC_C_ABSENCE.DATE_PROJECTED_END
,P_TIME_PROJECTED_END => LC_C_ABSENCE.TIME_PROJECTED_END
,P_DATE_START => LC_C_ABSENCE.DATE_START
,P_TIME_START => LC_C_ABSENCE.TIME_START
,P_DATE_END => LC_C_ABSENCE.DATE_END
,P_TIME_END => LC_C_ABSENCE.TIME_END
,P_ABSENCE_DAYS => LC_C_ABSENCE.ABSENCE_DAYS
,P_ABSENCE_HOURS => LC_C_ABSENCE.ABSENCE_HOURS
,P_AUTHORISING_PERSON_ID => L_AUTHORISING_PERSON_ID
,P_REPLACEMENT_PERSON_ID => L_REPLACEMENT_PERSON_ID
,P_ATTRIBUTE_CATEGORY => LC_C_ABSENCE.ATTRIBUTE_CATEGORY
,P_ATTRIBUTE1 => LC_C_ABSENCE.ATTRIBUTE1
,P_ATTRIBUTE2 => LC_C_ABSENCE.ATTRIBUTE2
,P_ATTRIBUTE3 => LC_C_ABSENCE.ATTRIBUTE3
,P_ATTRIBUTE4 => LC_C_ABSENCE.ATTRIBUTE4
,P_ATTRIBUTE5 => LC_C_ABSENCE.ATTRIBUTE5
,P_ATTRIBUTE6 => LC_C_ABSENCE.ATTRIBUTE6
,P_ATTRIBUTE7 => LC_C_ABSENCE.ATTRIBUTE7
,P_ATTRIBUTE8 => LC_C_ABSENCE.ATTRIBUTE8
,P_ATTRIBUTE9 => LC_C_ABSENCE.ATTRIBUTE9
,P_ATTRIBUTE10 => LC_C_ABSENCE.ATTRIBUTE10
,P_ATTRIBUTE11 => LC_C_ABSENCE.ATTRIBUTE11
,P_ATTRIBUTE12 => LC_C_ABSENCE.ATTRIBUTE12
,P_ATTRIBUTE13 => LC_C_ABSENCE.ATTRIBUTE13
,P_ATTRIBUTE14 => LC_C_ABSENCE.ATTRIBUTE14
,P_ATTRIBUTE15 => LC_C_ABSENCE.ATTRIBUTE15
,P_ATTRIBUTE16 => LC_C_ABSENCE.ATTRIBUTE16
,P_ATTRIBUTE17 => LC_C_ABSENCE.ATTRIBUTE17
,P_ATTRIBUTE18 => LC_C_ABSENCE.ATTRIBUTE18
,P_ATTRIBUTE19 => LC_C_ABSENCE.ATTRIBUTE19
,P_ATTRIBUTE20 => LC_C_ABSENCE.ATTRIBUTE20
,P_PERIOD_OF_INCAPACITY_ID => LC_C_ABSENCE.PERIOD_OF_INCAPACITY
,P_SSP1_ISSUED => 'N'
,P_MATERNITY_ID => LC_C_ABSENCE.MATERNITY_ID
,P_SICKNESS_START_DATE => LC_C_ABSENCE.SICKNESS_START_DATE
,P_SICKNESS_END_DATE => LC_C_ABSENCE.SICKNESS_END_DATE
,P_PREGNANCY_RELATED_ILLNESS => 'N'
,P_REASON_FOR_NOTIFICATION_DELA => LC_C_ABSENCE.REASON_FOR_NOTIFICATION_DELAY
,P_ACCEPT_LATE_NOTIFICATION_FLA => 'N'
,P_LINKED_ABSENCE_ID => LC_C_ABSENCE.LINKED_ABSENCE_ID
,P_BATCH_ID => LC_C_ABSENCE.BATCH_ID
,P_ABS_INFORMATION_CATEGORY => LC_C_ABSENCE.ABS_INFORMATION_CATEGORY
,P_ABS_INFORMATION1 => LC_C_ABSENCE.ABS_INFORMATION1
,P_ABS_INFORMATION2 => LC_C_ABSENCE.ABS_INFORMATION2
,P_ABS_INFORMATION3 => LC_C_ABSENCE.ABS_INFORMATION3
,P_ABS_INFORMATION4 => LC_C_ABSENCE.ABS_INFORMATION4
,P_ABS_INFORMATION5 => LC_C_ABSENCE.ABS_INFORMATION5
,P_ABS_INFORMATION6 => LC_C_ABSENCE.ABS_INFORMATION6
,P_ABS_INFORMATION7 => LC_C_ABSENCE.ABS_INFORMATION7
,P_ABS_INFORMATION8 => LC_C_ABSENCE.ABS_INFORMATION8
,P_ABS_INFORMATION9 => LC_C_ABSENCE.ABS_INFORMATION9
,P_ABS_INFORMATION10 => LC_C_ABSENCE.ABS_INFORMATION10
,P_ABS_INFORMATION11 => LC_C_ABSENCE.ABS_INFORMATION11
,P_ABS_INFORMATION12 => LC_C_ABSENCE.ABS_INFORMATION12
,P_ABS_INFORMATION13 => LC_C_ABSENCE.ABS_INFORMATION13
,P_ABS_INFORMATION14 => LC_C_ABSENCE.ABS_INFORMATION14
,P_ABS_INFORMATION15 => LC_C_ABSENCE.ABS_INFORMATION15
,P_ABS_INFORMATION16 => LC_C_ABSENCE.ABS_INFORMATION16
,P_ABS_INFORMATION17 => LC_C_ABSENCE.ABS_INFORMATION17
,P_ABS_INFORMATION18 => LC_C_ABSENCE.ABS_INFORMATION18
,P_ABS_INFORMATION19 => LC_C_ABSENCE.ABS_INFORMATION19
,P_ABS_INFORMATION20 => LC_C_ABSENCE.ABS_INFORMATION20
,P_ABS_INFORMATION21 => LC_C_ABSENCE.ABS_INFORMATION21
,P_ABS_INFORMATION22 => LC_C_ABSENCE.ABS_INFORMATION22
,P_ABS_INFORMATION23 => LC_C_ABSENCE.ABS_INFORMATION23
,P_ABS_INFORMATION24 => LC_C_ABSENCE.ABS_INFORMATION24
,P_ABS_INFORMATION25 => LC_C_ABSENCE.ABS_INFORMATION25
,P_ABS_INFORMATION26 => LC_C_ABSENCE.ABS_INFORMATION26
,P_ABS_INFORMATION27 => LC_C_ABSENCE.ABS_INFORMATION27
,P_ABS_INFORMATION28 => LC_C_ABSENCE.ABS_INFORMATION28
,P_ABS_INFORMATION29 => LC_C_ABSENCE.ABS_INFORMATION29
,P_ABS_INFORMATION30 => LC_C_ABSENCE.ABS_INFORMATION30
,P_ABSENCE_CASE_ID => L_ABSENCE_CASE_ID
,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
,P_DUR_DYS_LESS_WARNING => L_DUR_DYS_LESS_WARNING
,P_DUR_HRS_LESS_WARNING => L_DUR_HRS_LESS_WARNING
,P_EXCEEDS_PTO_ENTIT_WARNING => L_EXCEEDS_PTO_ENTIT_WARNING
,P_EXCEEDS_RUN_TOTAL_WARNING => L_EXCEEDS_RUN_TOTAL_WARNING
,P_ABS_OVERLAP_WARNING => L_ABS_OVERLAP_WARNING
,P_ABS_DAY_AFTER_WARNING => L_ABS_DAY_AFTER_WARNING
,P_DUR_OVERWRITTEN_WARNING => L_DUR_OVERWRITTEN_WARNING
,P_DEL_ELEMENT_ENTRY_WARNING => L_DEL_ELEMENT_ENTRY_WARNING
);
IF L_OBJECT_VERSION_NUMBER IS NOT NULL
THEN
ERROR_DESC := 'NO ERROR';
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
END IF;
IF L_OBJECT_VERSION_NUMBER IS NULL
THEN
ERROR_DESC := ERROR_DESC || SQLERRM;
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
END IF;
-- DBMS_OUTPUT.PUT_LINE ('ELEMENT TYPE
ID : ' || L_ELEMENT_TYPE_ID);*/
EXCEPTION
WHEN OTHERS
THEN
UPDATE DEV_ABSENCE_ATTENDANCES
SET ERROR_DESCRIPTION = ERROR_DESC
WHERE LINE_ID = LC_C_ABSENCE.LINE_ID;
--DBMS_OUTPUT.PUT_LINE
(SQLERRM);
END;
--COMMIT;
END LOOP;
CLOSE C_ABSENCE;
END;
/
DELETE PROCEDURE
CREATE OR REPLACE PROCEDURE APPS.DEV_DELETE_ABSENCES
IS
CURSOR C_COMP
IS
SELECT PPF.EMPLOYEE_NUMBER, PAA.ABSENCE_DAYS,
PAA.ABSENCE_ATTENDANCE_ID, PAA.OBJECT_VERSION_NUMBER,
--DISTINCT
FU.USER_NAME, PAAT.NAME, PAA.DATE_START
FROM PER_PEOPLE_F PPF,
PER_ASSIGNMENTS_F PAF,
PER_ABSENCE_ATTENDANCES PAA,
FND_USER FU,
PER_ABSENCE_ATTENDANCE_TYPES PAAT
WHERE PPF.PERSON_ID = PAF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = PAA.PERSON_ID
AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.CREATED_BY = FU.USER_ID
AND PAA.ABSENCE_ATTENDANCE_TYPE_ID <> 61
AND FU.USER_NAME IN ('AFF.PAYROLL', 'ANONYMOUS');
LC_C_COMP C_COMP%ROWTYPE;
L_COMP_ID NUMBER;
L_EFFECTIVE_START_DATE DATE;
L_EFFECTIVE_END_DATE DATE;
L_ORG_ID NUMBER;
L_LOC_ID NUMBER;
L_BASIS_ID NUMBER;
L_PPL_GROUP_ID NUMBER;
L_ENTRIES_WARNING BOOLEAN;
BEGIN
OPEN C_COMP;
LOOP
FETCH C_COMP
INTO LC_C_COMP;
EXIT WHEN C_COMP%NOTFOUND;
BEGIN
DBMS_OUTPUT.PUT_LINE
('RAN');
HR_PERSON_ABSENCE_API.DELETE_PERSON_ABSENCE
(P_VALIDATE => FALSE,
P_ABSENCE_ATTENDANCE_ID => LC_C_COMP.ABSENCE_ATTENDANCE_ID,
P_OBJECT_VERSION_NUMBER => LC_C_COMP.OBJECT_VERSION_NUMBER
);
--DBMS_OUTPUT.PUT_LINE(L_EFFECTIVE_START_DATE);
END;
END LOOP;
CLOSE C_COMP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 254));
END;
/
I hope this post is helpful. Feedback is much appreciated.!
0 comments: