PROCEDURE
PRC_EMP_ORG_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
L_EFFECTIVE_DATE DATE:= NULL;
L_ASSIGNMENT_ID NUMBER:= NULL;
L_CORRECTION BOOLEAN;
L_UPDATE BOOLEAN;
L_UPDATE_OVERRIDE BOOLEAN;
L_UPDATE_CHANGE_INSERT BOOLEAN;
LC_DT_UD_MODE VARCHAR2(100):= NULL;
L_LOCATION_ID NUMBER:= NULL;
L_GRADE_ID NUMBER:= NULL;
L_JOB_ID NUMBER:= NULL;
L_PAYROLL_ID NUMBER:= NULL;
L_ORGANIZATION_ID NUMBER:= NULL;
L_EMPLOYEE_CATEGORY VARCHAR2(100):= NULL;
L_POSITION_ID NUMBER:= NULL;
L_PEOPLE_GROUP_ID NUMBER:= NULL;
L_OBJ_VERSION_NUM NUMBER:= NULL;
L_SPECIAL_CEILING_STEP_ID PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
L_GROUP_NAME VARCHAR2(30);
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_ORG_NOW_NO_MANAGER_WARNING BOOLEAN;
L_OTHER_MANAGER_WARNING BOOLEAN;
L_SPP_DELETE_WARNING BOOLEAN;
L_ENTRIES_CHANGED_WARNING VARCHAR2(30);
L_TAX_DISTRICT_CHANGED_WARN BOOLEAN;
L_PERSON_ID NUMBER:= NULL;
L_EFFECTIVE_DATE_VALID NUMBER:= NULL;
ERROR_MESSAGE1 VARCHAR2(4000):=NULL;
TOTAL_RECORDS NUMBER;
CURRENT_RECORDS NUMBER;
ERROR_RECORDS NUMBER;
CURSOR
STAGING_RECORDS IS
SELECT A.*,ROWID FROM EMP_ORG_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
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID);
--INITIATION OF VARIABLES
L_PEOPLE_GROUP_ID := NULL;
L_OBJ_VERSION_NUM := NULL;
L_SPECIAL_CEILING_STEP_ID := NULL;
ERROR_MESSAGE1 := NULL;
-- TO GET PERSON_ID BASED ON EMPLOYEE_NUMBER FROM STAGING TABLE
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
FROM
PER_ALL_PEOPLE_F
WHERE
EMPLOYEE_NUMBER =I.EMPLOYEE_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE('L_PERSON_ID:'||L_PERSON_ID);
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;
-- TO GET OBJECT_VERSION_NUMBER BASED ON PERSON_ID AND ACTIVE PERSON
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 NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('L_OBJ_VERSION_NUM:'||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,LOCATION_ID,GRADE_ID,JOB_ID,PAYROLL_ID,EMPLOYMENT_CATEGORY,POSITION_ID,
PEOPLE_GROUP_ID
INTO L_ASSIGNMENT_ID,
L_EFFECTIVE_DATE, L_LOCATION_ID, L_GRADE_ID, L_JOB_ID, L_PAYROLL_ID, L_EMPLOYEE_CATEGORY, L_POSITION_ID, L_PEOPLE_GROUP_ID
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:'||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 VALIDATE THE ORGANIZATION WHICH IS IN STAGING TABLE
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE NAME = I.NEW_ORGANIZATION;
DBMS_OUTPUT.PUT_LINE('L_ORGANIZATION_ID:'||L_ORGANIZATION_ID);
EXCEPTION
WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;
-- AND BUSINESS_GROUP_ID = 202;
IF L_PERSON_ID IS NOT NULL AND
L_OBJ_VERSION_NUM IS NOT NULL AND L_ASSIGNMENT_ID IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL AND
L_ORGANIZATION_ID 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 => L_CORRECTION,
P_UPDATE => L_UPDATE,
P_UPDATE_OVERRIDE =>
L_UPDATE_OVERRIDE,
P_UPDATE_CHANGE_INSERT =>
L_UPDATE_CHANGE_INSERT
);
IF (
L_UPDATE_OVERRIDE = TRUE OR L_UPDATE_CHANGE_INSERT = TRUE )
THEN
-- UPDATE_OVERRIDE
LC_DT_UD_MODE := 'UPDATE_OVERRIDE';
END IF;
IF ( L_CORRECTION = TRUE )
THEN
-- CORRECTION
LC_DT_UD_MODE := 'CORRECTION';
END IF;
IF ( L_UPDATE = TRUE )
THEN
-- UPDATE
LC_DT_UD_MODE := 'UPDATE';
END IF;
DBMS_OUTPUT.PUT_LINE('MODE: '||LC_DT_UD_MODE);
--WE NEED TO PROVIDE COMBINATIONS OF POSITION VALUES, JOB VALUES AND
ORGANIZATION VALUES WHICH ARE LOCATED IN PER_POSITIONS.
HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
(
-- INPUT DATA ELEMENTS
P_EFFECTIVE_DATE => I.EFFECTIVE_DATE,
P_DATETRACK_UPDATE_MODE => LC_DT_UD_MODE,
P_ASSIGNMENT_ID =>
L_ASSIGNMENT_ID,
P_LOCATION_ID => L_LOCATION_ID,
P_GRADE_ID => L_GRADE_ID,
P_JOB_ID => L_JOB_ID,
P_PAYROLL_ID => L_PAYROLL_ID,
P_ORGANIZATION_ID =>
L_ORGANIZATION_ID, --NEW ORG_ID
P_EMPLOYMENT_CATEGORY =>
L_EMPLOYEE_CATEGORY,
P_POSITION_ID => L_POSITION_ID,
-- OUTPUT DATA ELEMENTS
P_PEOPLE_GROUP_ID =>
L_PEOPLE_GROUP_ID,
P_OBJECT_VERSION_NUMBER => L_OBJ_VERSION_NUM,
P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_STEP_ID,
P_GROUP_NAME => L_GROUP_NAME,
P_EFFECTIVE_START_DATE =>
L_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE => L_EFFECTIVE_END_DATE,
P_ORG_NOW_NO_MANAGER_WARNING => L_ORG_NOW_NO_MANAGER_WARNING,
P_OTHER_MANAGER_WARNING => L_OTHER_MANAGER_WARNING,
P_SPP_DELETE_WARNING =>
L_SPP_DELETE_WARNING,
P_ENTRIES_CHANGED_WARNING =>
L_ENTRIES_CHANGED_WARNING,
P_TAX_DISTRICT_CHANGED_WARNING
=>
L_TAX_DISTRICT_CHANGED_WARN
);
DBMS_OUTPUT.PUT_LINE(L_EFFECTIVE_START_DATE);
IF L_EFFECTIVE_START_DATE IS NOT NULL THEN
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
COMMIT;
ELSE
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1||' UNKNOWN ERROR
FROM API *' WHERE ROWID = I.ROWID;
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
UPDATE
EMP_ORG_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_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH
EMPLOYEE EXISTS *';
END IF;
IF
L_ASSIGNMENT_ID IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH
ASSIGNMENT EXISTS *';
END IF;
IF I.EFFECTIVE_DATE IS NULL OR L_EFFECTIVE_DATE_VALID
IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'PLEASE PROVIDE
CORRECT EFFECTIVE DATE *';
END IF;
IF
L_OBJ_VERSION_NUM IS NULL THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO ACTIVE
ASSIGNMENTS AVAILABLE FOR THIS EMPLOYEE *';
END IF;
UPDATE
EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1
WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
SELECT COUNT(1) INTO TOTAL_RECORDS FROM EMP_ORG_UPDATE_STG;
SELECT COUNT(1) INTO ERROR_RECORDS FROM EMP_ORG_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
PRC_EMP_ORG_UPDATE;
/
No comments:
Post a Comment