PROCEDURE
PRC_EMPSUPERVISOR_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
--DECLARATION
L_PERSON_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_EFFECTIVE_DATE DATE:= NULL;
L_SUPERVISOR_ID NUMBER;
LB_CORRECTION BOOLEAN;
LB_UPDATE BOOLEAN;
LB_UPDATE_OVERRIDE BOOLEAN;
LB_UPDATE_CHANGE_INSERT BOOLEAN;
LC_DT_UD_MODE VARCHAR2(100):= NULL;
L_OBJ_VERSION_NUM NUMBER;
L_SOFT_CODING_KEYFLEX_ID HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
L_CONCATENATED_SEGMENTS VARCHAR2(2000);
L_COMMENT_ID PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
L_EFFECTIVE_START_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
L_EFFECTIVE_END_DATE PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
L_NO_MANAGERS_WARNING BOOLEAN;
L_OTHER_MANAGER_WARNING BOOLEAN;
ERROR_MESSAGE VARCHAR2(4000):= NULL;
CURRENT_RECORDS NUMBER;
TOTAL_RECORDS NUMBER;
ERROR_RECORDS NUMBER;
L_EFFECTIVE_DATE_VALID NUMBER;
ERROR_MESSAGE1 VARCHAR2(4000):= NULL;
CURSOR
STAGING_RECORDS IS
SELECT A.*,ROWID FROM EMP_SUPERVISOR_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
BEGIN
SELECT COUNT(*) INTO CURRENT_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
FOR I IN STAGING_RECORDS
LOOP
--INITIATING VARIABLES TO NULL
L_SOFT_CODING_KEYFLEX_ID := NULL;
L_OBJ_VERSION_NUM:= NULL;
-- TO FETCH THE PERSON_ID BASED ON EMPLOYEE_NUMBER
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
FROM
PER_ALL_PEOPLE_F
WHERE
EMPLOYEE_NUMBER =I.EMPLOYEE_ID --
ENTER EMPLOYEE NUMBER HERE TO FETCH PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE(L_PERSON_ID);
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
END;
BEGIN
SELECT MAX(OBJECT_VERSION_NUMBER) INTO
L_OBJ_VERSION_NUM
FROM
PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
IF
L_OBJ_VERSION_NUM IS NULL THEN
DBMS_OUTPUT.PUT_LINE(L_OBJ_VERSION_NUM);
END IF;
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
END;
-- TO FETCH THE ASSIGNMENT_ID BASED ON PERSON_ID
BEGIN
SELECT ASSIGNMENT_ID,EFFECTIVE_START_DATE
INTO L_ASSIGNMENT_ID,
L_EFFECTIVE_DATE --
TO FETCH ASSIGNMENT_ID BASED ON PERSON_ID WHICH IS GENERATED FROM ABOVE QUERY
FROM
PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND OBJECT_VERSION_NUMBER = L_OBJ_VERSION_NUM;
DBMS_OUTPUT.PUT_LINE(L_ASSIGNMENT_ID);
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
END;
-- TO VALIDATE EFFECTIVE_DATE
IF I.EFFECTIVE_DATE >=
L_EFFECTIVE_DATE THEN
L_EFFECTIVE_DATE_VALID := 1;
ELSE
L_EFFECTIVE_DATE_VALID := NULL;
END IF;
-- TO FETCH SUPERVISOR_ID THROUGH SUPERVISOR_NAME FROM STAGING TABLE
BEGIN
SELECT PERSON_ID INTO
L_SUPERVISOR_ID -- TO GET SUPERVISOR_ID FROM EMPLOYEE_NUMBER OF SUPERVISOR
FROM STAGING TABLE
FROM
PER_ALL_PEOPLE_F
WHERE
EMPLOYEE_NUMBER = I.NEW_MANAGER_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE(L_SUPERVISOR_ID);
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
END;
IF L_PERSON_ID IS NOT NULL AND
L_ASSIGNMENT_ID IS NOT NULL AND I.EFFECTIVE_DATE IS NOT NULL AND L_SUPERVISOR_ID IS NOT NULL AND L_OBJ_VERSION_NUM IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL THEN
BEGIN
DT_API.FIND_DT_UPD_MODES
( P_EFFECTIVE_DATE => (I.EFFECTIVE_DATE),
P_BASE_TABLE_NAME => 'PER_ALL_ASSIGNMENTS_F',
P_BASE_KEY_COLUMN => 'ASSIGNMENT_ID',
P_BASE_KEY_VALUE =>
L_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 OR LB_UPDATE_CHANGE_INSERT = TRUE )
THEN
-- UPDATE_OVERRIDE
LC_DT_UD_MODE := 'UPDATE_OVERRIDE';
END IF;
IF ( LB_CORRECTION = TRUE )
THEN
-- CORRECTION
LC_DT_UD_MODE := 'CORRECTION';
END IF;
IF ( LB_UPDATE = TRUE )
THEN
-- UPDATE
LC_DT_UD_MODE := 'UPDATE';
END IF;
HR_ASSIGNMENT_API.UPDATE_EMP_ASG
(
-- INPUT DATA ELEMENTS
P_EFFECTIVE_DATE => I.EFFECTIVE_DATE,
P_DATETRACK_UPDATE_MODE => LC_DT_UD_MODE,
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_SUPERVISOR_ID =>
L_SUPERVISOR_ID, --
ENTER SUPERVISOR_ID HERE TO CHANGE THE EXISTING SUPERVISOR
P_CHANGE_REASON => NULL,
--IN OUT ELEMENT
P_OBJECT_VERSION_NUMBER =>
L_OBJ_VERSION_NUM,
P_SOFT_CODING_KEYFLEX_ID =>
L_SOFT_CODING_KEYFLEX_ID,
--OUTPUT DATA ELEMENTS
P_CONCATENATED_SEGMENTS =>
L_CONCATENATED_SEGMENTS,
P_COMMENT_ID => L_COMMENT_ID,
P_EFFECTIVE_START_DATE => L_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => L_EFFECTIVE_END_DATE,
P_NO_MANAGERS_WARNING =>
L_NO_MANAGERS_WARNING,
P_OTHER_MANAGER_WARNING =>
L_OTHER_MANAGER_WARNING
);
IF
L_EFFECTIVE_START_DATE IS NOT NULL THEN
UPDATE
EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||I.EMPLOYEE_ID||' START DATE'||L_EFFECTIVE_START_DATE);
COMMIT;
ELSE
UPDATE
EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = 'RECORD NOT INSERTED DUE TO API ISSUE *' WHERE ROWID = I.ROWID;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||I.EMPLOYEE_ID||' HAS FAILED TO UPDATE');
END IF;
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
UPDATE
EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1
WHERE ROWID = I.ROWID;
COMMIT;
END;
ELSE
IF L_PERSON_ID IS NULL THEN
ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH
EMPLOYEE EXISTS *';
END IF;
IF
L_ASSIGNMENT_ID IS NULL THEN
ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH
ASSIGNMENT EXISTS *';
END IF;
IF I.EFFECTIVE_DATE IS NULL THEN
ERROR_MESSAGE:= ERROR_MESSAGE||'PLEASE PROVIDE
CORRECT EFFECTIVE DATE *';
END IF;
IF
L_SUPERVISOR_ID IS NULL THEN
ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH
SUPERVISOR EXISTS *';
END IF;
UPDATE EMP_SUPERVISOR_UPDATE_STG
SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
SELECT COUNT(1) INTO TOTAL_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG;
SELECT COUNT(1) INTO ERROR_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG WHERE UPDATE_STATUS = 'E';
--VIEW LOG FOR THIS ERROR DATA
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------RECORD
VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CURRENT NO. OF RECORDS TO INSERT : '||CURRENT_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------');
--VIEW OUTPUT FILE FOR THIS DATA
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------RECORD
VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CURRENT NO. OF RECORDS TO INSERT : '||(TOTAL_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------------------------------------------------');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
No comments:
Post a Comment