CREATE OR REPLACE PROCEDURE MNJ_UPDATE_LEAVE_HOURS( ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)
AS
L_EFFECTIVE_START_DATE DATE := NULL;
L_EFFECTIVE_END_DATE DATE := NULL;
L_UPDATE_WARNING BOOLEAN;
L_ELEMENT_TYPE_ID NUMBER := NULL;
L_BUSINESS_GROUP_ID NUMBER := 81;
CURSOR DAYS_CUR
IS
SELECT
PAAF.ASSIGNMENT_NUMBER,
PEEF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID,
PEEF.ELEMENT_TYPE_ID,
PEEF.EFFECTIVE_START_DATE,
PEEF.EFFECTIVE_END_DATE,
PEEF.OBJECT_VERSION_NUMBER,
PIVF.INPUT_VALUE_ID,
PETF.ELEMENT_NAME,
VAL.SCREEN_ENTRY_VALUE,
UPPER(PIVF.NAME) INPUT_VALUE
FROM
PAY_INPUT_VALUES_F PIVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_ENTRY_VALUES_F VAL,
PER_ALL_ASSIGNMENTS_F PAAF,
PAY_ELEMENT_TYPES_F PETF
WHERE
PEEF.ELEMENT_ENTRY_ID = VAL.ELEMENT_ENTRY_ID
AND PEEF.EFFECTIVE_START_DATE = VAL.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE = VAL.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = PEEF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = VAL.INPUT_VALUE_ID
AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
AND PETF.ELEMENT_TYPE_ID = PEEF.ELEMENT_TYPE_ID
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PRIMARY_FLAG = 'Y'
AND NVL(PEEF.ATTRIBUTE1,'N') = 'N'
AND UPPER(PIVF.NAME) = 'DAYS'
AND PEEF.ELEMENT_TYPE_ID IN ( '676' , '677' ); --Casual Leave First Half , Casual Leave Second Half
CURSOR DATE_CUR ( P_ASSIGNMENT_ID NUMBER , P_ELEMENT_TYPE_ID NUMBER )
IS
SELECT
PEEF.ASSIGNMENT_ID,
PEEF.ELEMENT_ENTRY_ID,
PEEF.ELEMENT_TYPE_ID,
PEEF.EFFECTIVE_START_DATE,
PEEF.EFFECTIVE_END_DATE,
PEEF.OBJECT_VERSION_NUMBER,
PIVF.INPUT_VALUE_ID,
VAL.SCREEN_ENTRY_VALUE,
UPPER(PIVF.NAME) INPUT_VALUE
FROM
PAY_INPUT_VALUES_F PIVF,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_ENTRY_VALUES_F VAL
WHERE
PEEF.ELEMENT_ENTRY_ID = VAL.ELEMENT_ENTRY_ID
AND PEEF.EFFECTIVE_START_DATE = VAL.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE = VAL.EFFECTIVE_END_DATE
AND PIVF.ELEMENT_TYPE_ID = PEEF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = VAL.INPUT_VALUE_ID
AND NVL(PEEF.ATTRIBUTE1,'N') = 'N'
AND UPPER(PIVF.NAME) = 'EFFECTIVE DATE'
AND PEEF.ELEMENT_TYPE_ID IN ('676' , '677' ) --Casual Leave First Half , Casual Leave Second Half
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PEEF.ELEMENT_TYPE_ID = P_ELEMENT_TYPE_ID;
BEGIN
fnd_file.put_line(fnd_file.output, 'Assignment Number | Element Name | Status' );
FOR REC IN DAYS_CUR LOOP
BEGIN
PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
P_VALIDATE => FALSE,
P_DATETRACK_UPDATE_MODE => 'CORRECTION',
P_EFFECTIVE_DATE => SYSDATE, --L_EFFECTIVE_DATE,
P_BUSINESS_GROUP_ID => L_BUSINESS_GROUP_ID,
P_ELEMENT_ENTRY_ID => REC.ELEMENT_ENTRY_ID,
P_OBJECT_VERSION_NUMBER => REC.OBJECT_VERSION_NUMBER,
P_INPUT_VALUE_ID1 => REC.INPUT_VALUE_ID,
P_ENTRY_VALUE1 => NVL(REC.SCREEN_ENTRY_VALUE,0)/2,
P_EFFECTIVE_START_DATE => REC.EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => REC.EFFECTIVE_END_DATE,
P_UPDATE_WARNING => L_UPDATE_WARNING);
FOR D_REC IN DATE_CUR(REC.ASSIGNMENT_ID , REC.ELEMENT_TYPE_ID) LOOP
BEGIN
PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
P_VALIDATE => FALSE,
P_DATETRACK_UPDATE_MODE => 'CORRECTION',
P_EFFECTIVE_DATE => SYSDATE, --L_EFFECTIVE_DATE,
P_BUSINESS_GROUP_ID => L_BUSINESS_GROUP_ID,
P_ELEMENT_ENTRY_ID => D_REC.ELEMENT_ENTRY_ID,
P_OBJECT_VERSION_NUMBER => D_REC.OBJECT_VERSION_NUMBER,
P_INPUT_VALUE_ID1 => D_REC.INPUT_VALUE_ID,
P_ENTRY_VALUE1 => SYSDATE,
P_EFFECTIVE_START_DATE => D_REC.EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => D_REC.EFFECTIVE_END_DATE,
P_UPDATE_WARNING => L_UPDATE_WARNING);
END;
END LOOP;
UPDATE
PAY_ELEMENT_ENTRIES_F PEEF
SET
PEEF.ATTRIBUTE1 = 'Y'
WHERE
PEEF.ASSIGNMENT_ID = REC.ASSIGNMENT_ID
AND PEEF.ELEMENT_ENTRY_ID = REC.ELEMENT_ENTRY_ID
AND PEEF.ELEMENT_TYPE_ID = REC.ELEMENT_TYPE_ID;
COMMIT;
fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME || ' | Successfully Updated.' );
DBMS_OUTPUT.PUT_LINE ('Element Entry has been Updated: ' || REC.ELEMENT_ENTRY_ID);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME || ' | Error Occured.'||sqlerrm );
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.output,'Error Occured While Executing Procedure : ' || SQLERRM);
END;
