CREATE OR REPLACE PACKAGE BODY RTVQ_PA_INTERNAL_INTERFACE AS PROCEDURE RTVQ_PR_UPL_QUALIFICATION ( IN_UPLOAD_INDEX IN NUMBER, IN_NOKIA_ID IN VARCHAR2, IN_BLOB_UPLOAD_DATA IN BLOB, IN_MODULE_NAME IN VARCHAR2, IN_CREATE_DATE IN DATE ) AS LANGUAGE JAVA NAME 'com.nokia.rtvq.upload.RTVQUploadQualification.uploadQualificationFromExcel(int,java.lang.String,oracle.sql.BLOB,java.lang.String,java.sql.Date)'; PROCEDURE RTVQ_PR_UPL_PERSON ( IN_UPLOAD_INDEX IN NUMBER, IN_NOKIA_ID IN VARCHAR2, IN_BLOB_UPLOAD_DATA IN BLOB, IN_MODULE_NAME IN VARCHAR2, IN_CREATE_DATE IN DATE ) AS LANGUAGE JAVA NAME 'com.nokia.rtvq.upload.RTVQUploadPerson.uploadPersonFromExcel(int,java.lang.String,oracle.sql.BLOB,java.lang.String,java.sql.Date)'; PROCEDURE RTVQ_PR_HANDLE_PERSON_LOGIN(IN_TAB_PERSON_LOGIN IN RTVQ_TY_TAB_PERSON_LOGIN,IN_FACTORY_NAME IN VARCHAR2,OUT_UNKNOWN_PERSONS OUT RTVQ_TY_TAB_UNKNOWN_PERSONS,OUT_MESSAGE OUT VARCHAR2) AS -- DECLARATIONS V_FACTORY_ID RTVQ_MA_FACTORY.FACTORY_ID%TYPE; V_WORKPLACE_ID RTVQ_MA_WORKPLACE.WORKPLACE_ID%TYPE; V_PERSON_LOGIN RTVQ_TY_OBJ_PERSON_LOGIN; EX_INVALID_INPUT EXCEPTION; EX_INVALID_RECORD EXCEPTION; EX_ERROR EXCEPTION; ERR_COUNT NUMBER; V_INSERT_OR_UPDATE NUMBER; OUT_MSG VARCHAR2(50); V_ELSE_WHERE NUMBER; BEGIN OUT_UNKNOWN_PERSONS := RTVQ_TY_TAB_UNKNOWN_PERSONS(); -- GET FACTORY ID FROM NAME BEGIN SELECT FACTORY_ID INTO V_FACTORY_ID FROM RTVQ_MA_FACTORY WHERE LOWER(IN_FACTORY_NAME) = LOWER(FACTORY_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_FACTORY_ID = '||V_FACTORY_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_FACTORY_ID:=NULL; OUT_MSG := 'UNABLE TO FIND FACTORY ID'; WHEN OTHERS THEN V_FACTORY_ID:=NULL; OUT_MSG := SQLERRM(SQLCODE); END; IF V_FACTORY_ID IS NULL THEN RAISE EX_INVALID_INPUT; END IF; ERR_COUNT := 0; FOR I IN IN_TAB_PERSON_LOGIN.FIRST .. IN_TAB_PERSON_LOGIN.LAST LOOP BEGIN V_PERSON_LOGIN := IN_TAB_PERSON_LOGIN(I); IF V_PERSON_LOGIN IS NOT NULL THEN --DBMS_OUTPUT.PUT_LINE('RFID_NOKIA_ID = ' || V_PERSON_LOGIN.RFID_NOKIA_ID); --DBMS_OUTPUT.PUT_LINE('RFID_WORKPLACE_NAME = ' || V_PERSON_LOGIN.RFID_WORKPLACE_NAME); --DBMS_OUTPUT.PUT_LINE('RFID_LOGIN_TIME = ' || V_PERSON_LOGIN.RFID_LOGIN_TIME); --DBMS_OUTPUT.PUT_LINE('RFID_LOGOFF_TIME = ' || V_PERSON_LOGIN.RFID_LOGOFF_TIME); --DBMS_OUTPUT.PUT_LINE('RFID_STATUS = ' || V_PERSON_LOGIN.RFID_STATUS); IF V_PERSON_LOGIN.RFID_NOKIA_ID IS NULL THEN OUT_MSG:= 'INVALID NOKIA ID'; ERR_COUNT := ERR_COUNT + 1; RAISE EX_INVALID_RECORD; END IF; IF V_PERSON_LOGIN.RFID_WORKPLACE_NAME IS NULL THEN OUT_MSG:= 'INVALID RFID_WORKPLACE_NAME'; ERR_COUNT := ERR_COUNT + 1; RAISE EX_INVALID_RECORD; END IF; V_WORKPLACE_ID := NULL; -- GET WORKPLACE ID FROM NAME BEGIN SELECT WORKPLACE_ID INTO V_WORKPLACE_ID FROM RTVQ_MA_WORKPLACE WHERE LOWER(V_PERSON_LOGIN.RFID_WORKPLACE_NAME) = LOWER(WORKPLACE_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_WORKPLACE_ID = '||V_WORKPLACE_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_WORKPLACE_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND WORKPLACE ID'; WHEN OTHERS THEN V_WORKPLACE_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_WORKPLACE_ID IS NOT NULL THEN -- CONTINUE ONLY IF U HAVE A VALID INPUT V_INSERT_OR_UPDATE := 0; BEGIN SELECT 1 INTO V_INSERT_OR_UPDATE FROM RTVQ_TR_PERSON_LOGIN WHERE UPPER(PERSON_ID) = UPPER(V_PERSON_LOGIN.RFID_NOKIA_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_INSERT_OR_UPDATE := 0; WHEN OTHERS THEN OUT_MSG := SQLERRM(SQLCODE); END; IF V_INSERT_OR_UPDATE = 0 THEN -- INSERT INTO THE TABLE HERE BEGIN INSERT INTO RTVQ_TR_PERSON_LOGIN(ROW_ID,FACTORY_ID,PERSON_ID,WORKPLACE_ID,LOGON_TIME,LOGOFF_TIME,LOGON_STATUS) VALUES((SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_PERSON_LOGIN),V_FACTORY_ID,UPPER(V_PERSON_LOGIN.RFID_NOKIA_ID),V_WORKPLACE_ID,V_PERSON_LOGIN.RFID_LOGIN_TIME,V_PERSON_LOGIN.RFID_LOGOFF_TIME,V_PERSON_LOGIN.RFID_STATUS); EXCEPTION -- INCREMENT ERROR COUNT AND CONTINUE; WHEN OTHERS THEN OUT_MESSAGE := SQLERRM(SQLCODE); ERR_COUNT := ERR_COUNT + 1; IF SQLCODE = -02291 THEN -- THIS USER IS NOT RECOGNIZED BY RTVQ -- SAVE THIS NOKIA ID IN OUTPUT ARRAY OUT_UNKNOWN_PERSONS.EXTEND; OUT_UNKNOWN_PERSONS(OUT_UNKNOWN_PERSONS.LAST) := V_PERSON_LOGIN.RFID_NOKIA_ID; ELSE BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PERSON_LOGIN_INSERT', 'RFID_NOKIA_ID = '||V_PERSON_LOGIN.RFID_NOKIA_ID||' RFID_WORKPLACE_NAME = ' || V_PERSON_LOGIN.RFID_WORKPLACE_NAME||' RFID_LOGIN_TIME = ' || V_PERSON_LOGIN.RFID_LOGIN_TIME||' RFID_LOGOFF_TIME = ' || V_PERSON_LOGIN.RFID_LOGOFF_TIME||' RFID_STATUS = ' || V_PERSON_LOGIN.RFID_STATUS, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END; END IF; IF V_INSERT_OR_UPDATE = 1 THEN -- UPDATE THE EXISTING RECORD WITH NEW VALUES BEGIN UPDATE RTVQ_TR_PERSON_LOGIN SET LOGON_TIME = V_PERSON_LOGIN.RFID_LOGIN_TIME, LOGOFF_TIME = V_PERSON_LOGIN.RFID_LOGOFF_TIME, LOGON_STATUS = V_PERSON_LOGIN.RFID_STATUS, WORKPLACE_ID = V_WORKPLACE_ID WHERE UPPER(PERSON_ID) = UPPER(V_PERSON_LOGIN.RFID_NOKIA_ID); EXCEPTION -- INCREMENT ERROR COUNT AND CONTINUE; WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PERSON_LOGIN_UPDATE', 'RFID_NOKIA_ID = '||V_PERSON_LOGIN.RFID_NOKIA_ID||' RFID_WORKPLACE_NAME = ' || V_PERSON_LOGIN.RFID_WORKPLACE_NAME||' RFID_LOGIN_TIME = ' || V_PERSON_LOGIN.RFID_LOGIN_TIME||' RFID_LOGOFF_TIME = ' || V_PERSON_LOGIN.RFID_LOGOFF_TIME||' RFID_STATUS = ' || V_PERSON_LOGIN.RFID_STATUS, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; -- UPDATE THE LAST PRACTICED DATE FOR THE CURRENT PERSON BEGIN UPDATE RTVQ_TR_TRAINING_TRACKER SET LAST_PRACTICED_DATE = NVL(V_PERSON_LOGIN.RFID_LOGIN_TIME,V_PERSON_LOGIN.RFID_LOGOFF_TIME) WHERE PERSON_ID = V_PERSON_LOGIN.RFID_NOKIA_ID AND SKILL_ID IN (SELECT DISTINCT SKILL.SKILL_ID FROM RTVQ_MA_WORKPLACE WORKPLACE, RTVQ_TR_LAYOUT_SETUP LAYOUT, RTVQ_TR_LAYOUT_SKILL_XREF XREF, RTVQ_MA_SKILL SKILL, RTVQ_MA_TEST_TYPE TESTTYPE, RTVQ_MA_PRODUCTION_DATA PRODUCTION, RTVQ_MA_SKILL_TESTTYPE_XREF STX WHERE WORKPLACE.WORKPLACE_ID = PRODUCTION.WORKPLACE_ID(+) AND WORKPLACE.WORKPLACE_ID = LAYOUT.WORKPLACE_ID AND LAYOUT.SETUP_ID = XREF.SETUP_ID AND XREF.SKILL_ID = SKILL.SKILL_ID AND SKILL.SKILL_ID = STX.SKILL_ID AND STX.TESTTYPE_ID = TESTTYPE.TESTTYPE_ID AND LAYOUT.STATUS = 'Active' AND LAYOUT.START_DATE <= SYSDATE AND LAYOUT.END_DATE >= SYSDATE AND WORKPLACE.WORKPLACE_ID = V_WORKPLACE_ID GROUP BY SKILL.SKILL_ID HAVING MAX(STX.PRODUCT_ID) = MAX(PRODUCTION.PRODUCT_ID) OR MAX(STX.PRODUCT_ID) IS NULL); EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; END; ELSE IF V_PERSON_LOGIN.RFID_STATUS = 'OUT' THEN -- IF V_WORKPLACE_ID IS NULL AND THE INCOMING STATUS IS OUT. BEGIN UPDATE RTVQ_TR_PERSON_LOGIN SET LOGON_TIME = V_PERSON_LOGIN.RFID_LOGIN_TIME, LOGOFF_TIME = V_PERSON_LOGIN.RFID_LOGOFF_TIME, LOGON_STATUS = V_PERSON_LOGIN.RFID_STATUS WHERE UPPER(PERSON_ID) = UPPER(V_PERSON_LOGIN.RFID_NOKIA_ID); EXCEPTION WHEN OTHERS THEN NULL; END; ELSE -- IF V_WORKPLACE_ID IS NULL ERR_COUNT := ERR_COUNT + 1; BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PERSON_LOGIN_NO_WORKPLACE', 'RFID_NOKIA_ID = '||V_PERSON_LOGIN.RFID_NOKIA_ID||' RFID_WORKPLACE_NAME = ' || V_PERSON_LOGIN.RFID_WORKPLACE_NAME||' RFID_LOGIN_TIME = ' || V_PERSON_LOGIN.RFID_LOGIN_TIME||' RFID_LOGOFF_TIME = ' || V_PERSON_LOGIN.RFID_LOGOFF_TIME||' RFID_STATUS = ' || V_PERSON_LOGIN.RFID_STATUS, 'WORKPLACE NOT FOUND', SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END IF; END IF; EXCEPTION WHEN EX_INVALID_RECORD THEN OUT_MESSAGE := OUT_MESSAGE||' - NOT DONE : ERROR_COUNT = '|| ERR_COUNT; WHEN OTHERS THEN OUT_MESSAGE := 'NOT DONE : ERROR_COUNT = '|| ERR_COUNT; END; END LOOP; --IF ERR_COUNT <> 0 THEN -- OUT_MESSAGE := 'NOT DONE : ERROR_COUNT = '|| ERR_COUNT; --END IF; OUT_MESSAGE := 'EXECUTION COMPLETE : ERROR COUNT = '||ERR_COUNT; END RTVQ_PR_HANDLE_PERSON_LOGIN; -- PROCEDURE TO INSERT/UPDATE PRODUCTS PROCEDURE RTVQ_PR_PMT_PRODUCT_SYNC(IN_TAB_PRODUCT IN RTVQ_TY_TAB_PMT_PRODUCT,IN_FACTORY_NAME IN VARCHAR2,OUT_MESSAGE OUT VARCHAR2) AS -- DECLARATION SECTION V_FACTORY_ID RTVQ_MA_FACTORY.FACTORY_ID%TYPE; V_ROW_ID RTVQ_MA_PRODUCT.ROW_ID%TYPE; EX_INVALID_RECORD EXCEPTION; EX_ERROR EXCEPTION; PRODUCT_COUNT NUMBER; ERR_COUNT NUMBER; V_INSERT_OR_UPDATE NUMBER; V_PRODUCT RTVQ_TY_OBJ_PMT_PRODUCT; BEGIN -- GET FACTORY ID FROM NAME BEGIN SELECT FACTORY_ID INTO V_FACTORY_ID FROM RTVQ_MA_FACTORY WHERE LOWER(IN_FACTORY_NAME) = LOWER(FACTORY_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_FACTORY_ID = '||V_FACTORY_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND FACTORY ID'; WHEN OTHERS THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_FACTORY_ID IS NOT NULL THEN PRODUCT_COUNT := 0; ERR_COUNT := 0; -- LOOP THRU THE INCOMING PRODUCT RECORDS FOR I IN IN_TAB_PRODUCT.FIRST .. IN_TAB_PRODUCT.LAST LOOP BEGIN V_PRODUCT := IN_TAB_PRODUCT(I); -- CHECK IF THERE IS ALREADY A RECORD IN RTVQ -- WITH THE INCOMING PRODUCT NAME BEGIN SELECT ROW_ID INTO V_ROW_ID FROM RTVQ_MA_PRODUCT WHERE PRODUCT_NICK_NAME = V_PRODUCT.PRODUCT_NICK_NAME; EXCEPTION WHEN NO_DATA_FOUND THEN V_ROW_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); WHEN OTHERS THEN V_ROW_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_ROW_ID IS NOT NULL THEN -- UPDATE THE EXISTING RECORD BEGIN UPDATE RTVQ_MA_PRODUCT SET PRODUCT_NICK_NAME = V_PRODUCT.PRODUCT_NICK_NAME, PRODUCT_TYPE_DESIGNATION = V_PRODUCT.PRODUCT_TYPE_DESIGNATION, PRODUCT_DESC = V_PRODUCT.PRODUCT_DESC, MODIFY_DATE = SYSDATE WHERE ROW_ID = V_ROW_ID; PRODUCT_COUNT := PRODUCT_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PRODUCT_UPDATE', 'PRODUCT_NICK_NAME = '||V_PRODUCT.PRODUCT_NICK_NAME||' PRODUCT_TYPE_DESIGNATION = '||V_PRODUCT.PRODUCT_TYPE_DESIGNATION||' ROW_ID ='|| V_ROW_ID ||' PRODUCT_DESC = ' || V_PRODUCT.PRODUCT_DESC, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; ELSE -- INSERT THE RECORD BEGIN INSERT INTO RTVQ_MA_PRODUCT(ROW_ID,PRODUCT_ID,PRODUCT_NICK_NAME,PRODUCT_TYPE_DESIGNATION,PRODUCT_DESC,FACTORY_ID,CREATE_DATE) VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_MA_PRODUCT), (SELECT TO_CHAR(NVL(MAX(ROW_ID),0)+1) FROM RTVQ_MA_PRODUCT), V_PRODUCT.PRODUCT_NICK_NAME, V_PRODUCT.PRODUCT_TYPE_DESIGNATION, V_PRODUCT.PRODUCT_DESC, V_FACTORY_ID, SYSDATE); PRODUCT_COUNT := PRODUCT_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PRODUCT_INSERT', 'PRODUCT_NICK_NAME = '||V_PRODUCT.PRODUCT_NICK_NAME||' PRODUCT_TYPE_DESIGNATION = '||V_PRODUCT.PRODUCT_TYPE_DESIGNATION||' ROW_ID ='|| V_ROW_ID ||' PRODUCT_DESC = ' || V_PRODUCT.PRODUCT_DESC, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; END; END LOOP; BEGIN INSERT INTO RTVQ_TR_REFRESH_AUDIT_LOG VALUES( (SELECT NVL(MAX(ROW_ID + 1),0) FROM RTVQ_TR_REFRESH_AUDIT_LOG), 'PRODUCT', SYSDATE, SYSDATE, ERR_COUNT, PRODUCT_COUNT-ERR_COUNT ); EXCEPTION WHEN OTHERS THEN NULL; END; OUT_MESSAGE := OUT_MESSAGE || 'PROCESSED '||PRODUCT_COUNT||' RECORDS - '||ERR_COUNT||' RECORDS FAILED'; END IF; END RTVQ_PR_PMT_PRODUCT_SYNC; -- PROCEDURE TO INSERT/UPDATE WORKPLACES PROCEDURE RTVQ_PR_PMT_WORKPLACE_SYNC(IN_TAB_WORKPLACE IN RTVQ_TY_TAB_PMT_WORKPLACE,IN_FACTORY_NAME IN VARCHAR2,IN_SOURCE_SYS IN VARCHAR2,OUT_MESSAGE OUT VARCHAR2) AS -- DECLARATION SECTION V_FACTORY_ID RTVQ_MA_FACTORY.FACTORY_ID%TYPE; V_WORKPLACE_ID RTVQ_MA_WORKPLACE.WORKPLACE_ID%TYPE; EX_INVALID_RECORD EXCEPTION; EX_ERROR EXCEPTION; WORKPLACE_COUNT NUMBER; ERR_COUNT NUMBER; V_INSERT_OR_UPDATE NUMBER; V_WORKPLACE RTVQ_TY_OBJ_PMT_WORKPLACE; BEGIN -- GET FACTORY ID FROM NAME BEGIN SELECT FACTORY_ID INTO V_FACTORY_ID FROM RTVQ_MA_FACTORY WHERE LOWER(IN_FACTORY_NAME) = LOWER(FACTORY_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_FACTORY_ID = '||V_FACTORY_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND FACTORY ID'; WHEN OTHERS THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_FACTORY_ID IS NOT NULL THEN WORKPLACE_COUNT := 0; ERR_COUNT := 0; -- LOOP THRU THE INCOMING WORKPLACE RECORDS FOR I IN IN_TAB_WORKPLACE.FIRST .. IN_TAB_WORKPLACE.LAST LOOP BEGIN V_WORKPLACE := IN_TAB_WORKPLACE(I); -- CHECK IF THERE IS ALREADY A RECORD IN RTVQ -- WITH THE INCOMING PRIMARY KEY BEGIN SELECT WORKPLACE_ID INTO V_WORKPLACE_ID FROM RTVQ_MA_WORKPLACE WHERE SOURCE_SYS_ID = V_WORKPLACE.ID; EXCEPTION WHEN NO_DATA_FOUND THEN V_WORKPLACE_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); WHEN OTHERS THEN V_WORKPLACE_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_WORKPLACE_ID IS NOT NULL THEN -- UPDATE THE EXISTING RECORD BEGIN UPDATE RTVQ_MA_WORKPLACE SET WORKPLACE_NAME = V_WORKPLACE.DESCRIPTION, MODIFY_DATE = SYSDATE WHERE WORKPLACE_ID = V_WORKPLACE_ID; WORKPLACE_COUNT := WORKPLACE_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'WORKPLACE_UPDATE', 'WORKPLACE_NAME = '||V_WORKPLACE.DESCRIPTION||' SOURCE_SYS ='||IN_SOURCE_SYS||' SOURCE_SYS_ID = '|| V_WORKPLACE.ID, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; ELSE -- INSERT THE RECORD BEGIN INSERT INTO RTVQ_MA_WORKPLACE(WORKPLACE_ID,WORKPLACE_NAME,SOURCE_SYS,SOURCE_SYS_ID,FACTORY_ID,CREATE_DATE) VALUES( RTVQ_SEQ_WORKPLACE_ID.NEXTVAL, V_WORKPLACE.DESCRIPTION, IN_SOURCE_SYS, V_WORKPLACE.ID, V_FACTORY_ID, SYSDATE); WORKPLACE_COUNT := WORKPLACE_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'WORKPLACE_INSERT', 'WORKPLACE_NAME = '||V_WORKPLACE.DESCRIPTION||' SOURCE_SYS ='||IN_SOURCE_SYS||' SOURCE_SYS_ID = '|| V_WORKPLACE.ID, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; END; END LOOP; BEGIN INSERT INTO RTVQ_TR_REFRESH_AUDIT_LOG VALUES( (SELECT NVL(MAX(ROW_ID + 1),0) FROM RTVQ_TR_REFRESH_AUDIT_LOG), 'WORKPLACE', SYSDATE, SYSDATE, ERR_COUNT, WORKPLACE_COUNT - ERR_COUNT); EXCEPTION WHEN OTHERS THEN NULL; END; OUT_MESSAGE := 'PROCESSED '||WORKPLACE_COUNT||' RECORDS - '||ERR_COUNT||' RECORDS FAILED'; END IF; END RTVQ_PR_PMT_WORKPLACE_SYNC; -- PROCEDURE TO INSERT OR UPDATE PRODUCT ON WORKPLACE RECORDS PROCEDURE RTVQ_PR_PMT_PROD_DATA_SYNC(IN_TAB_PROD_DATA IN RTVQ_TY_TAB_PMT_PROD_DATA,IN_FACTORY_NAME IN VARCHAR2,OUT_MESSAGE OUT VARCHAR2) AS -- DECLARATION SECTION V_FACTORY_ID RTVQ_MA_FACTORY.FACTORY_ID%TYPE; V_ROW_ID RTVQ_MA_PRODUCTION_DATA.ROW_ID%TYPE; V_PRODUCT_ID RTVQ_MA_PRODUCT.PRODUCT_ID%TYPE; V_WORKPLACE_ID RTVQ_MA_WORKPLACE.WORKPLACE_ID%TYPE; EX_INVALID_RECORD EXCEPTION; EX_ERROR EXCEPTION; PROD_DATA_COUNT NUMBER; ERR_COUNT NUMBER; V_INSERT_OR_UPDATE NUMBER; V_PROD_DATA RTVQ_TY_OBJ_PMT_PROD_DATA; BEGIN -- GET FACTORY ID FROM NAME BEGIN SELECT FACTORY_ID INTO V_FACTORY_ID FROM RTVQ_MA_FACTORY WHERE LOWER(IN_FACTORY_NAME) = LOWER(FACTORY_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_FACTORY_ID = '||V_FACTORY_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND FACTORY ID'; WHEN OTHERS THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_FACTORY_ID IS NOT NULL THEN PROD_DATA_COUNT := 0; ERR_COUNT := 0; BEGIN -- LOOP THRU THE INCOMING PROD_DATA RECORDS FOR I IN IN_TAB_PROD_DATA.FIRST .. IN_TAB_PROD_DATA.LAST LOOP BEGIN V_PROD_DATA := IN_TAB_PROD_DATA(I); -- GET THE PRODUCT ID FOR THE INCOMING PRODUCT NAME BEGIN SELECT PRODUCT_ID INTO V_PRODUCT_ID FROM RTVQ_MA_PRODUCT WHERE PRODUCT_NICK_NAME = V_PROD_DATA.PRODUCT_NAME; EXCEPTION WHEN NO_DATA_FOUND THEN V_PRODUCT_ID := NULL; OUT_MESSAGE := SQLERRM(SQLCODE); WHEN OTHERS THEN V_PRODUCT_ID := NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_PRODUCT_ID IS NOT NULL THEN -- CHECK IF THERE IS ALREADY A PRODUCT RECORD IN RTVQ -- FOR THE INCOMING WORKPLACE BEGIN SELECT PD.ROW_ID INTO V_ROW_ID FROM RTVQ_MA_PRODUCTION_DATA PD,RTVQ_MA_WORKPLACE W WHERE PD.WORKPLACE_ID = W.WORKPLACE_ID AND W.WORKPLACE_NAME = V_PROD_DATA.WORKPLACE_NAME; EXCEPTION WHEN NO_DATA_FOUND THEN V_ROW_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); WHEN OTHERS THEN V_ROW_ID := NULL; V_INSERT_OR_UPDATE := 0; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_ROW_ID IS NOT NULL THEN -- UPDATE THE EXISTING RECORD BEGIN UPDATE RTVQ_MA_PRODUCTION_DATA SET PRODUCT_ID = V_PRODUCT_ID, START_DATE = V_PROD_DATA.TS_MODIFIED, WORK_DATE = V_PROD_DATA.TS_MODIFIED, ACTIVE_FLG = V_PROD_DATA.STATUS WHERE ROW_ID = V_ROW_ID; PROD_DATA_COUNT := PROD_DATA_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PRODUCTION_DATA_UPDATE', 'PRODUCT_ID = '||V_PRODUCT_ID||' START_DATE = '||V_PROD_DATA.TS_MODIFIED||' WORK_DATE = '||V_PROD_DATA.TS_MODIFIED||' ACTIVE_FLG = '||V_PROD_DATA.STATUS||' ROW_ID = '||V_ROW_ID||' WORKPLACE_NAME = '||V_PROD_DATA.WORKPLACE_NAME, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; ELSE -- INSERT THE RECORD BEGIN INSERT INTO RTVQ_MA_PRODUCTION_DATA(ROW_ID,PRODUCT_ID,START_DATE,WORK_DATE,ACTIVE_FLG,WORKPLACE_ID,FACTORY_ID) VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_MA_PRODUCTION_DATA), V_PRODUCT_ID, V_PROD_DATA.TS_MODIFIED, V_PROD_DATA.TS_MODIFIED, V_PROD_DATA.STATUS, (SELECT WORKPLACE_ID FROM RTVQ_MA_WORKPLACE WHERE WORKPLACE_NAME = V_PROD_DATA.WORKPLACE_NAME), V_FACTORY_ID); PROD_DATA_COUNT := PROD_DATA_COUNT + 1; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PRODUCTION_DATA_INSERT', 'PRODUCT_ID = '||V_PRODUCT_ID||' START_DATE = '||V_PROD_DATA.TS_MODIFIED||' WORK_DATE = '||V_PROD_DATA.TS_MODIFIED||' ACTIVE_FLG = '||V_PROD_DATA.STATUS||' ROW_ID = '||V_ROW_ID||' WORKPLACE_NAME = '||V_PROD_DATA.WORKPLACE_NAME, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; ELSE ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := 'PRODUCT NOT FOUND '||V_PROD_DATA.PRODUCT_NAME; BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PRODUCTION_DATA_INSERT', 'PRODUCT = '||V_PROD_DATA.PRODUCT_NAME||' START_DATE = '||V_PROD_DATA.TS_MODIFIED||' WORK_DATE = '||V_PROD_DATA.TS_MODIFIED||' ACTIVE_FLG = '||V_PROD_DATA.STATUS||' WORKPLACE_NAME = '||V_PROD_DATA.WORKPLACE_NAME, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN INSERT INTO RTVQ_TR_REFRESH_AUDIT_LOG VALUES( (SELECT NVL(MAX(ROW_ID + 1),0) FROM RTVQ_TR_REFRESH_AUDIT_LOG), 'RT_PRODUCTION', SYSDATE, SYSDATE, ERR_COUNT, PROD_DATA_COUNT - ERR_COUNT ); EXCEPTION WHEN OTHERS THEN NULL; END; OUT_MESSAGE := 'PROCESSED '||PROD_DATA_COUNT||' RECORDS - '||ERR_COUNT||' RECORDS FAILED'; END IF; END RTVQ_PR_PMT_PROD_DATA_SYNC; PROCEDURE RTVQ_PR_SYNC_PERSON(IN_PERSON_LIST IN RTVQ_TY_TAB_NEDI_PERSON,IN_FACTORY_NAME IN VARCHAR2,OUT_MESSAGE OUT VARCHAR2) AS V_FACTORY_ID RTVQ_MA_FACTORY.FACTORY_ID%TYPE; V_PERSON RTVQ_TY_OBJ_NEDI_PERSON; ERR_COUNT NUMBER := 0; V_COUNT NUMBER := 0; V_INSERT_OR_UPDATE NUMBER; EX_INVALID_INPUT EXCEPTION; V_GROUP_ID RTVQ_MA_GROUP.ROW_ID%TYPE; V_GROUP_NAME VARCHAR2(50); V_OBSOLETE_ID RTVQ_MA_PERSON_DATA.NOKIA_ID%TYPE; BEGIN -- GET FACTORY ID FROM NAME BEGIN SELECT FACTORY_ID INTO V_FACTORY_ID FROM RTVQ_MA_FACTORY WHERE LOWER(IN_FACTORY_NAME) = LOWER(FACTORY_NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_FACTORY_ID = '||V_FACTORY_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND FACTORY ID'; WHEN OTHERS THEN V_FACTORY_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_FACTORY_ID IS NULL THEN RAISE EX_INVALID_INPUT; END IF; FOR I IN IN_PERSON_LIST.FIRST .. IN_PERSON_LIST.LAST LOOP BEGIN V_PERSON := IN_PERSON_LIST(I); V_COUNT := V_COUNT + 1; V_INSERT_OR_UPDATE := 0; -- CHECK IF THERE IS ALREADY A RECORD IN RTVQ FOR THIS PERSON_ID BEGIN SELECT 1 INTO V_INSERT_OR_UPDATE FROM RTVQ_MA_PERSON_DATA WHERE UPPER(NOKIA_ID) = UPPER(V_PERSON.EMPLOYEE_NUMBER); EXCEPTION WHEN OTHERS THEN NULL; END; IF V_INSERT_OR_UPDATE = 0 THEN -- INSERT THE RECORD BEGIN INSERT INTO RTVQ_MA_PERSON_DATA("ROW_ID", "NOKIA_ID", "FACTORY_ID", "COMMON_NAME", "LAST_NAME", "FIRST_NAME", "JOB_TITLE", "START_DATE", "EMAIL_ID", "PREFERRED_NAME", "STATUS", "EMPLOYEE_TYPE", "CREATE_DATE") VALUES((SELECT NVL(MAX(ROW_ID+1),0) FROM RTVQ_MA_PERSON_DATA), UPPER(V_PERSON.EMPLOYEE_NUMBER), V_FACTORY_ID, V_PERSON.GIVEN_NAME, V_PERSON.LAST_NAME, V_PERSON.FIRST_NAME, V_PERSON.TITLE, V_PERSON.START_DATE, V_PERSON.MAIL, V_PERSON.PREFERRED_NAME, V_PERSON.STATUS, V_PERSON.EMPLOYEE_TYPE, SYSDATE); EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PERSON_DATA_INSERT', ' NOKIA_ID ='||V_PERSON.EMPLOYEE_NUMBER||' FACTORY_ID ='||V_FACTORY_ID||' COMMON_NAME ='||V_PERSON.GIVEN_NAME||' LAST_NAME ='||V_PERSON.LAST_NAME||' FIRST_NAME ='||V_PERSON.FIRST_NAME||' JOB_TITLE ='||V_PERSON.TITLE||' START_DATE ='||V_PERSON.START_DATE||' EMAIL_ID ='||V_PERSON.MAIL||' PREFERRED_NAME ='||V_PERSON.PREFERRED_NAME||' STATUS ='||V_PERSON.STATUS||' EMPLOYEE_TYPE ='||V_PERSON.EMPLOYEE_TYPE, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; ELSE BEGIN IF V_PERSON.STATUS <> 'OBSOLETE' THEN -- UPDATE THE RECORD UPDATE RTVQ_MA_PERSON_DATA SET LAST_NAME = V_PERSON.LAST_NAME, COMMON_NAME = V_PERSON.GIVEN_NAME, FIRST_NAME = V_PERSON.FIRST_NAME, JOB_TITLE = V_PERSON.TITLE, START_DATE = V_PERSON.START_DATE, EMAIL_ID = V_PERSON.MAIL, PREFERRED_NAME = V_PERSON.PREFERRED_NAME, STATUS = V_PERSON.STATUS, EMPLOYEE_TYPE = V_PERSON.EMPLOYEE_TYPE, MODIFY_DATE = SYSDATE, TERMINATION_DATE = NULL WHERE UPPER(NOKIA_ID) = UPPER(V_PERSON.EMPLOYEE_NUMBER); ELSE BEGIN V_OBSOLETE_ID := NULL; BEGIN SELECT NOKIA_ID INTO V_OBSOLETE_ID FROM RTVQ_MA_PERSON_DATA WHERE NOKIA_ID = V_PERSON.EMPLOYEE_NUMBER AND STATUS = V_PERSON.STATUS; EXCEPTION -- THE USER HAS BEEN RECENTLY DEACTIVATED IN NEDI WHEN NO_DATA_FOUND THEN BEGIN UPDATE RTVQ_MA_PERSON_DATA SET LAST_NAME = V_PERSON.LAST_NAME, COMMON_NAME = V_PERSON.GIVEN_NAME, FIRST_NAME = V_PERSON.FIRST_NAME, JOB_TITLE = V_PERSON.TITLE, START_DATE = V_PERSON.START_DATE, EMAIL_ID = V_PERSON.MAIL, PREFERRED_NAME = V_PERSON.PREFERRED_NAME, STATUS = V_PERSON.STATUS, EMPLOYEE_TYPE = V_PERSON.EMPLOYEE_TYPE, MODIFY_DATE = SYSDATE, TERMINATION_DATE = SYSDATE WHERE NOKIA_ID = V_PERSON.EMPLOYEE_NUMBER; EXCEPTION WHEN OTHERS THEN NULL; END; WHEN OTHERS THEN NULL; END; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'PERSON_DATA_UPDATE', ' NOKIA_ID ='||V_PERSON.EMPLOYEE_NUMBER||' FACTORY_ID ='||V_FACTORY_ID||' COMMON_NAME ='||V_PERSON.GIVEN_NAME||' LAST_NAME ='||V_PERSON.LAST_NAME||' FIRST_NAME ='||V_PERSON.FIRST_NAME||' JOB_TITLE ='||V_PERSON.TITLE||' START_DATE ='||V_PERSON.START_DATE||' EMAIL_ID ='||V_PERSON.MAIL||' PREFERRED_NAME ='||V_PERSON.PREFERRED_NAME||' STATUS ='||V_PERSON.STATUS||' EMPLOYEE_TYPE ='||V_PERSON.EMPLOYEE_TYPE, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; END; END LOOP; OUT_MESSAGE := 'PROCESSED '||V_COUNT||' PERSON RECORDS. '||ERR_COUNT||' FAILED'; END RTVQ_PR_SYNC_PERSON; PROCEDURE RTVQ_PR_SKILL_FIGURE_REPORT(IN_GREEN_QUERY IN VARCHAR2,IN_RED_QUERY IN VARCHAR2,IN_TRAINING_QUERY IN VARCHAR2,IN_REPORT_QUERY IN VARCHAR2,OUT_CURSOR OUT RTVQ_CUR,OUT_MESSAGE OUT VARCHAR2) AS TYPE ANY_CURSOR IS REF CURSOR; C1 ANY_CURSOR; V_SKILL_ID NUMBER; V_NUM_QUALIFIED NUMBER; V_NUM_ELAPSED NUMBER; V_NUM_TRAINING NUMBER; BEGIN BEGIN -- EMPTY THE TEMPORARY TABLE DELETE FROM RTVQ_TEM_SKILL_FIGURE; -- GET THE COUNT OF PEOPLE WHO HAVE 'GREEN' STATUS OPEN C1 FOR IN_GREEN_QUERY; LOOP FETCH C1 INTO V_SKILL_ID,V_NUM_QUALIFIED; EXIT WHEN C1%NOTFOUND OR C1%NOTFOUND IS NULL; INSERT INTO RTVQ_TEM_SKILL_FIGURE(SKILL_ID,NUM_QUALIFIED) VALUES(V_SKILL_ID,V_NUM_QUALIFIED); END LOOP; CLOSE C1; OPEN C1 FOR IN_RED_QUERY; LOOP FETCH C1 INTO V_SKILL_ID,V_NUM_ELAPSED; EXIT WHEN C1%NOTFOUND OR C1%NOTFOUND IS NULL; UPDATE RTVQ_TEM_SKILL_FIGURE SET NUM_ELAPSED = V_NUM_ELAPSED WHERE SKILL_ID = V_SKILL_ID; END LOOP; CLOSE C1; OPEN C1 FOR IN_TRAINING_QUERY; LOOP FETCH C1 INTO V_SKILL_ID,V_NUM_TRAINING; EXIT WHEN C1%NOTFOUND OR C1%NOTFOUND IS NULL; UPDATE RTVQ_TEM_SKILL_FIGURE SET NUM_TRAINING = V_NUM_TRAINING WHERE SKILL_ID = V_SKILL_ID; END LOOP; CLOSE C1; COMMIT; OPEN OUT_CURSOR FOR IN_REPORT_QUERY; EXCEPTION WHEN OTHERS THEN OUT_MESSAGE := SQLERRM(SQLCODE); END; END RTVQ_PR_SKILL_FIGURE_REPORT; PROCEDURE RTVQ_PR_SYNC_GROUP(IN_GROUP_MEMBERS IN RTVQ_TY_TAB_GROUP_MEMBER,IN_GROUP_NAME IN VARCHAR2,OUT_MESSAGE OUT VARCHAR2) AS ERR_COUNT NUMBER := 0; V_COUNT NUMBER := 0; V_INSERT_OR_UPDATE NUMBER; EX_INVALID_INPUT EXCEPTION; V_GROUP_ID RTVQ_MA_GROUP.ROW_ID%TYPE; V_PERSON_ID RTVQ_MA_GROUP_MEMBERS.PERSON_ID%TYPE; V_TEMP RTVQ_MA_GROUP.ROW_ID%TYPE; BEGIN -- GET GROUP ID FROM NAME BEGIN SELECT ROW_ID INTO V_GROUP_ID FROM RTVQ_MA_GROUP WHERE LOWER(IN_GROUP_NAME) = LOWER(NAME); --DBMS_OUTPUT.PUT_LINE('OUTPUT_V_GROUP_ID = '||V_GROUP_ID); EXCEPTION WHEN NO_DATA_FOUND THEN V_GROUP_ID:=NULL; OUT_MESSAGE := 'UNABLE TO FIND GROUP NAMED '||IN_GROUP_NAME; WHEN OTHERS THEN V_GROUP_ID:=NULL; OUT_MESSAGE := SQLERRM(SQLCODE); END; IF V_GROUP_ID IS NOT NULL THEN -- INSERT OR UPDATE EACH GROUP MEMBER FOR I IN IN_GROUP_MEMBERS.FIRST .. IN_GROUP_MEMBERS.LAST LOOP V_COUNT := V_COUNT + 1; V_PERSON_ID := IN_GROUP_MEMBERS(I); V_TEMP := NULL; BEGIN SELECT ROW_ID INTO V_TEMP FROM RTVQ_MA_GROUP_MEMBERS WHERE V_GROUP_ID = GROUP_ID AND UPPER(PERSON_ID) = UPPER(V_PERSON_ID); EXCEPTION WHEN OTHERS THEN OUT_MESSAGE := SQLERRM(SQLCODE); END; -- INSERT ONLY IF THE RECORD IS NOT FOUND IF V_TEMP IS NULL THEN BEGIN INSERT INTO RTVQ_MA_GROUP_MEMBERS("ROW_ID", "GROUP_ID", "PERSON_ID", "CREATE_DATE") VALUES( (SELECT NVL(MAX(ROW_ID+1),0) FROM RTVQ_MA_GROUP_MEMBERS), V_GROUP_ID, UPPER(V_PERSON_ID), SYSDATE ); EXCEPTION WHEN OTHERS THEN ERR_COUNT := ERR_COUNT + 1; OUT_MESSAGE := SQLERRM(SQLCODE); BEGIN INSERT INTO RTVQ_TR_ERROR_LOG VALUES( (SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_TR_ERROR_LOG), 'GROUP_INSERT', 'GROUP_ID = '||V_GROUP_ID||' PERSON_ID = '||V_PERSON_ID, OUT_MESSAGE, SYSDATE); EXCEPTION WHEN OTHERS THEN NULL; END; END; END IF; END LOOP; OUT_MESSAGE := 'PROCESSED '||V_COUNT||' GROUP MEMBERS. '||ERR_COUNT||' FAILED'; END IF; END RTVQ_PR_SYNC_GROUP; --PROCEDURE FOR UPLOADING QUALIFICATON PROCEDURE RTVQ_PR_UPLOAD_QUAL ( IN_VA_QUALDATA IN RTVQ_TY_TAB_UPL_QUAL, OUT_VA_QUALDATA OUT RTVQ_TY_TAB_UPL_QUAL, IN_CREATED_BY IN VARCHAR2, OUT_MSG OUT VARCHAR2, OUT_UPLOAD_STATUS OUT VARCHAR2 ) AS V_QUAL_TYPE RTVQ_TY_OBJ_UPL_QUAL; EX_INVALID_INPUT EXCEPTION; EX_UPLOAD EXCEPTION; EX_CONTINUE EXCEPTION; V_UPLOAD_STATUS VARCHAR2(4000) := 'FAIL'; V_SKILL_ID RTVQ_MA_SKILL.SKILL_ID%TYPE; V_TESTTYPE_ID RTVQ_MA_TEST_TYPE.TESTTYPE_ID%TYPE; V_EQUIPMENT_ID RTVQ_MA_EQUIPMENT.EQUIPMENT_ID%TYPE; V_PRODUCT_ID RTVQ_MA_PRODUCT.PRODUCT_ID%TYPE; V_SKILL_XREF_ID RTVQ_MA_SKILL_TESTTYPE_XREF.ROW_ID%TYPE; V_HEADER_FLAG CHAR := 'Y'; V_TOTAL_RECORDS NUMBER := 0; V_SUCCESS_COUNT NUMBER := 0; BEGIN BEGIN OUT_UPLOAD_STATUS := 'SUCCESS'; --INVALID INPUT IF IN_VA_QUALDATA IS NULL THEN OUT_MSG := 'INVALID INPUT...'; RAISE EX_INVALID_INPUT; END IF; OUT_VA_QUALDATA := RTVQ_TY_TAB_UPL_QUAL(); V_TOTAL_RECORDS := IN_VA_QUALDATA.COUNT - 1; IF V_TOTAL_RECORDS <= 0 THEN OUT_MSG := 'INVALID INPUT... NO RECORDS TO INSERT'; RAISE EX_INVALID_INPUT; END IF; FOR I IN IN_VA_QUALDATA.FIRST .. IN_VA_QUALDATA.LAST LOOP BEGIN V_QUAL_TYPE := IN_VA_QUALDATA(I); --INVALID QUALIFICATION RECORD IF V_QUAL_TYPE IS NULL THEN RAISE EX_UPLOAD; END IF; --HANDLING HEADER FLAG IN EXCEL FILE IF V_HEADER_FLAG = 'Y' THEN V_HEADER_FLAG := 'N'; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'UPLOAD STATUS'; OUT_VA_QUALDATA.EXTEND; OUT_VA_QUALDATA(OUT_VA_QUALDATA.LAST) := V_QUAL_TYPE; RAISE EX_CONTINUE; END IF; --RESET VALUES V_UPLOAD_STATUS := 'FAIL'; V_SKILL_ID := NULL; V_TESTTYPE_ID := NULL; V_EQUIPMENT_ID := NULL; V_PRODUCT_ID := NULL; V_SKILL_XREF_ID := NULL; --INVALID NOKIA ID TO INSERT IF V_QUAL_TYPE.UPQ_NOKID IS NULL THEN V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'NOKIA ID IS EMPTY'; RAISE EX_UPLOAD; END IF; --EMPTY SKILL NAME IF V_QUAL_TYPE.UPQ_SKILLNAME IS NULL THEN V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'SKILL NAME IS EMPTY'; RAISE EX_UPLOAD; END IF; --CHECK NULL FOR TEST TYPE IF V_QUAL_TYPE.UPQ_TEST_TYPE IS NULL THEN V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'TEST TYPE IS EMPTY'; RAISE EX_UPLOAD; END IF; --GET SKILL ID BEGIN SELECT SKILL_ID INTO V_SKILL_ID FROM RTVQ_MA_SKILL WHERE LOWER(SKILL_NAME) = LOWER(V_QUAL_TYPE.UPQ_SKILLNAME); EXCEPTION WHEN NO_DATA_FOUND THEN V_SKILL_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'INVALID SKILL NAME'; RAISE EX_UPLOAD; WHEN OTHERS THEN V_SKILL_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS :='ERROR IN FINDING SKILL NAME'; RAISE EX_UPLOAD; END; --INVALID SKILL ID IF V_SKILL_ID IS NULL THEN V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'SKILL NAME NOT FOUND'; RAISE EX_UPLOAD; END IF; --GET TEST TYPE ID BEGIN SELECT TESTTYPE_ID INTO V_TESTTYPE_ID FROM RTVQ_MA_TEST_TYPE WHERE LOWER(TESTTYPE_NAME) = LOWER(V_QUAL_TYPE.UPQ_TEST_TYPE); EXCEPTION WHEN NO_DATA_FOUND THEN V_TESTTYPE_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'INVALID TEST TYPE'; RAISE EX_UPLOAD; WHEN OTHERS THEN V_TESTTYPE_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS :='ERROR IN FINDING TEST TYPE'; RAISE EX_UPLOAD; END; --INVALID TEST TYPE ID IF V_TESTTYPE_ID IS NULL THEN OUT_MSG := 'TEST TYPE NOT FOUND'; RAISE EX_UPLOAD; END IF; --GET EQUIPMENT / PRODUCT ID IF V_QUAL_TYPE.UPQ_PREQ_NAME IS NOT NULL THEN IF UPPER(V_QUAL_TYPE.UPQ_TEST_TYPE) = 'EQUIPMENT' THEN BEGIN SELECT EQUIPMENT_ID INTO V_EQUIPMENT_ID FROM RTVQ_MA_EQUIPMENT WHERE UPPER(TRIM(EQUIPMENT_NAME)) = UPPER(TRIM(V_QUAL_TYPE.UPQ_PREQ_NAME)); EXCEPTION WHEN NO_DATA_FOUND THEN V_EQUIPMENT_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'INVALID EQUIPMENT NAME'; WHEN OTHERS THEN V_EQUIPMENT_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS :='ERROR IN FINDING EQUIPMENT NAME'; END; ELSIF UPPER(V_QUAL_TYPE.UPQ_TEST_TYPE) = 'PRODUCT' THEN BEGIN SELECT PRODUCT_ID INTO V_PRODUCT_ID FROM RTVQ_MA_PRODUCT WHERE UPPER(TRIM(PRODUCT_NICK_NAME)) = UPPER(TRIM(V_QUAL_TYPE.UPQ_PREQ_NAME)); EXCEPTION WHEN NO_DATA_FOUND THEN V_PRODUCT_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'INVALID PRODUCT NAME'; WHEN OTHERS THEN V_PRODUCT_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'ERROR IN FINDING PRODUCT NAME'; END; END IF; END IF; --FIND XREF ID BEGIN SELECT ROW_ID INTO V_SKILL_XREF_ID FROM RTVQ_MA_SKILL_TESTTYPE_XREF WHERE SKILL_ID = V_SKILL_ID AND TESTTYPE_ID = V_TESTTYPE_ID AND NVL(PRODUCT_ID, 1) = NVL(V_PRODUCT_ID, 1) AND NVL(EQUIPMENT_ID, 1) = NVL(V_EQUIPMENT_ID, 1); EXCEPTION WHEN NO_DATA_FOUND THEN V_SKILL_XREF_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'INVALID SKILL AND TEST TYPE'; RAISE EX_UPLOAD; WHEN OTHERS THEN V_SKILL_XREF_ID := NULL; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'ERROR IN FINDING SKILL AND TEST TYPE'; RAISE EX_UPLOAD; END; --INVALID SKILL XREF ID IF V_SKILL_XREF_ID IS NULL THEN V_QUAL_TYPE.UPQ_UPLOAD_STATUS := 'SKILL AND TEST TYPE NOT FOUND'; RAISE EX_INVALID_INPUT; END IF; --INSERT / UPDATE QUALIFICATION DETAIL RTVQ_PA_INTERNAL_INTERFACE.RTVQ_PR_INSUP_QUAL(V_QUAL_TYPE.UPQ_NOKID, V_SKILL_XREF_ID, V_SKILL_ID, TO_DATE(V_QUAL_TYPE.UPQ_DOT, 'MM/DD/YYYY'), NULL, V_QUAL_TYPE.UPQ_SCORE, NULL, V_QUAL_TYPE.UPQ_COMMENT1, V_QUAL_TYPE.UPQ_COMMENT2, NULL, V_QUAL_TYPE.UPQ_CERT_NOKID, IN_CREATED_BY, NULL, NULL, V_UPLOAD_STATUS); IF V_UPLOAD_STATUS = 'PASS' THEN V_SUCCESS_COUNT := V_SUCCESS_COUNT + 1; ELSE OUT_UPLOAD_STATUS:='FAIL'; END IF; V_QUAL_TYPE.UPQ_UPLOAD_STATUS := V_UPLOAD_STATUS; OUT_VA_QUALDATA.EXTEND; OUT_VA_QUALDATA(OUT_VA_QUALDATA.LAST) := V_QUAL_TYPE; EXCEPTION WHEN EX_CONTINUE THEN V_SKILL_ID := NULL; V_TESTTYPE_ID := NULL; V_EQUIPMENT_ID := NULL; V_PRODUCT_ID := NULL; V_SKILL_XREF_ID := NULL; WHEN EX_UPLOAD THEN V_UPLOAD_STATUS := 'FAIL'; V_SKILL_ID := NULL; V_TESTTYPE_ID := NULL; V_EQUIPMENT_ID := NULL; V_PRODUCT_ID := NULL; V_SKILL_XREF_ID := NULL; OUT_UPLOAD_STATUS := 'FAIL '; V_QUAL_TYPE.UPQ_UPLOAD_STATUS :=OUT_UPLOAD_STATUS || ' - UPLOAD EXCEPTION - '||V_QUAL_TYPE.UPQ_UPLOAD_STATUS; OUT_VA_QUALDATA.EXTEND; OUT_VA_QUALDATA(OUT_VA_QUALDATA.LAST) := V_QUAL_TYPE; WHEN OTHERS THEN V_UPLOAD_STATUS := 'FAIL'; V_SKILL_ID := NULL; V_TESTTYPE_ID := NULL; V_EQUIPMENT_ID := NULL; V_PRODUCT_ID := NULL; V_SKILL_XREF_ID := NULL; OUT_UPLOAD_STATUS := 'FAIL '; V_QUAL_TYPE.UPQ_UPLOAD_STATUS :=OUT_UPLOAD_STATUS || ' - '||V_QUAL_TYPE.UPQ_UPLOAD_STATUS|| ' - '||SQLERRM(SQLCODE); OUT_VA_QUALDATA.EXTEND; OUT_VA_QUALDATA(OUT_VA_QUALDATA.LAST) := V_QUAL_TYPE; END; END LOOP; EXCEPTION WHEN EX_INVALID_INPUT THEN --DBMS_OUTPUT.PUT_LINE('EX_INVALID_INPUT'); OUT_UPLOAD_STATUS := 'FAIL'; WHEN OTHERS THEN --DBMS_OUTPUT.PUT_LINE('OTHERS....' || SQLERRM(SQLCODE)); OUT_UPLOAD_STATUS := 'FAIL'; END; OUT_MSG := 'SUCCESS COUNT = ' || V_SUCCESS_COUNT || ', FAILURE COUNT = ' || TO_CHAR(V_TOTAL_RECORDS - V_SUCCESS_COUNT) ||'. ' ||OUT_MSG; END RTVQ_PR_UPLOAD_QUAL; --PROCEDURE FOR UPDATING PERSON DETAIL (UPLOAD) PROCEDURE RTVQ_PR_UPLOAD_PERS ( IN_VA_PERSDATA IN RTVQ_TY_TAB_UPL_PERS, IN_CREATED_BY IN VARCHAR2, OUT_VA_PERSDATA OUT RTVQ_TY_TAB_UPL_PERS, OUT_MSG OUT VARCHAR2, OUT_UPLOAD_STATUS OUT VARCHAR2 ) AS V_PERS_TYPE RTVQ_TY_OBJ_UPL_PERS; EX_NO_CREATE EXCEPTION; EX_INVALID_INPUT EXCEPTION; EX_UPLOAD EXCEPTION; EX_CONTINUE EXCEPTION; V_DEF_WKP RTVQ_MA_PERSON_DATA.DEFAULT_WORKPLACE_ID%TYPE; V_HEADER_FLAG CHAR := 'Y'; V_TOTAL_RECORDS NUMBER := 0; V_SUCCESS_COUNT NUMBER := 0; V_SUP_NOKIA_ID RTVQ_MA_PERSON_DATA.NOKIA_ID%TYPE; V_PERSON_FLAG NUMBER(1) := 0; BEGIN OUT_UPLOAD_STATUS := 'SUCCESS'; --INVALID INPUT IF IN_VA_PERSDATA IS NULL THEN RAISE EX_INVALID_INPUT; END IF; --LOAD INPUT TO VARIABLE OUT_VA_PERSDATA := RTVQ_TY_TAB_UPL_PERS(); V_TOTAL_RECORDS := IN_VA_PERSDATA.COUNT - 1; IF V_TOTAL_RECORDS <= 0 THEN OUT_MSG := 'NO RECORDS TO INSERT'; RAISE EX_INVALID_INPUT; END IF; FOR I IN IN_VA_PERSDATA.FIRST .. IN_VA_PERSDATA.LAST LOOP BEGIN V_PERS_TYPE := IN_VA_PERSDATA(I); IF V_HEADER_FLAG = 'Y' THEN V_HEADER_FLAG := 'N'; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'UPLOAD STATUS'; OUT_VA_PERSDATA.EXTEND; OUT_VA_PERSDATA(OUT_VA_PERSDATA.LAST) := V_PERS_TYPE; RAISE EX_CONTINUE; END IF; --RESET VALUES IF V_PERS_TYPE IS NOT NULL THEN IF V_PERS_TYPE.UPP_NOKID IS NULL THEN OUT_MSG := 'INVALID NOKIA ID.'; RAISE EX_UPLOAD; END IF; --CHECK FOR EXISTENCE OF PERSON RECORD FOR UPDATE BEGIN SELECT 1 INTO V_PERSON_FLAG FROM RTVQ_MA_PERSON_DATA WHERE LOWER(NOKIA_ID) = LOWER(V_PERS_TYPE.UPP_NOKID); EXCEPTION WHEN NO_DATA_FOUND THEN OUT_MSG := 'PERSON NOT FOUND FOR UPDATE'; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL'; OUT_UPLOAD_STATUS := 'FAIL'; RAISE EX_UPLOAD; WHEN OTHERS THEN OUT_MSG := 'PERSON CANNOT BE UPDATED '; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL'; OUT_UPLOAD_STATUS := 'FAIL'; RAISE EX_UPLOAD; END; --IDENTIFYING WORKPLACE ID IF V_PERS_TYPE.UPP_DEF_WKP IS NOT NULL THEN BEGIN SELECT WORKPLACE_ID INTO V_DEF_WKP FROM RTVQ_MA_WORKPLACE WHERE LOWER(WORKPLACE_NAME) = LOWER(V_PERS_TYPE.UPP_DEF_WKP); EXCEPTION WHEN NO_DATA_FOUND THEN V_DEF_WKP := NULL; OUT_MSG := 'UNABLE TO FIND WORKPLACE ID'; WHEN OTHERS THEN V_DEF_WKP := NULL; OUT_MSG := 'WORKPLACE ID CANNOT BE FOUND'; END; END IF; --IDENTIFYING SUPERVISOR ID IF V_PERS_TYPE.UPP_SUP_NOKID IS NOT NULL THEN BEGIN SELECT 1 INTO V_PERSON_FLAG FROM RTVQ_MA_PERSON_DATA WHERE LOWER(NOKIA_ID) = LOWER(V_PERS_TYPE.UPP_SUP_NOKID); EXCEPTION WHEN NO_DATA_FOUND THEN OUT_MSG := 'SUPERVISOR NOT FOUND FOR UPDATE'; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL'; OUT_UPLOAD_STATUS := 'FAIL'; RAISE EX_UPLOAD; WHEN OTHERS THEN OUT_MSG := 'SUPERVISOR NOT FOUND FOR UPDATE - '||SQLERRM(SQLCODE); V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL'; OUT_UPLOAD_STATUS := 'FAIL'; RAISE EX_UPLOAD; END; END IF; --INSERTING INTO RTVQ_MA_PERSON_DATA BEGIN UPDATE RTVQ_MA_PERSON_DATA SET DEFAULT_WORKPLACE_ID = V_DEF_WKP, SUPERVISOR_ID = V_PERS_TYPE.UPP_SUP_NOKID WHERE NOKIA_ID = V_PERS_TYPE.UPP_NOKID; V_SUCCESS_COUNT := V_SUCCESS_COUNT + 1; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'PASS'; EXCEPTION WHEN OTHERS THEN OUT_UPLOAD_STATUS := 'FAIL'; OUT_MSG := 'INSERT QUALIFICATION ::: ' || SQLERRM(SQLCODE); V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL - '||OUT_MSG; END; OUT_VA_PERSDATA.EXTEND; OUT_VA_PERSDATA(OUT_VA_PERSDATA.LAST) := V_PERS_TYPE; END IF; EXCEPTION WHEN EX_CONTINUE THEN --RESET VALUES V_DEF_WKP := NULL; --OUT_UPLOAD_STATUS := 'FAIL'; WHEN EX_UPLOAD THEN --RESET VALUES --DBMS_OUTPUT.PUT_LINE('EX_UPLOAD'); V_DEF_WKP := NULL; V_PERS_TYPE.UPP_UPLOAD_STATUS := 'FAIL - '||OUT_MSG; OUT_VA_PERSDATA.EXTEND; OUT_VA_PERSDATA(OUT_VA_PERSDATA.LAST) := V_PERS_TYPE; WHEN OTHERS THEN V_DEF_WKP := NULL; OUT_UPLOAD_STATUS := 'FAIL - '||OUT_MSG||' - '||SQLERRM(SQLCODE); OUT_VA_PERSDATA.EXTEND; OUT_VA_PERSDATA(OUT_VA_PERSDATA.LAST) := V_PERS_TYPE; END; END LOOP; OUT_MSG := 'SUCCESS COUNT = ' || V_SUCCESS_COUNT || CHR(10) || CHR(13) || 'FAILURE COUNT = ' || TO_CHAR(V_TOTAL_RECORDS - V_SUCCESS_COUNT); EXCEPTION WHEN EX_INVALID_INPUT THEN OUT_UPLOAD_STATUS := 'FAIL - '||OUT_MSG; WHEN OTHERS THEN OUT_UPLOAD_STATUS := 'FAIL - '||OUT_MSG||' - '||SQLERRM(SQLCODE); END RTVQ_PR_UPLOAD_PERS; --PROCEDURE FOR UPLOADING QUALIFICATION DETAILS PROCEDURE RTVQ_PR_INSUP_QUAL ( IN_PERSON_ID IN RTVQ_TR_QUALIFICATIONS.PERSON_ID%TYPE, IN_SKILL_TEST_X_ID IN RTVQ_TR_QUALIFICATIONS.SKILL_TEST_X_ID%TYPE, IN_SKILL_ID IN RTVQ_MA_SKILL.SKILL_ID%TYPE, IN_ACQUIRED_DATE IN RTVQ_TR_QUALIFICATIONS.ACQUIRED_DATE%TYPE, IN_RESULT IN RTVQ_TR_QUALIFICATIONS.RESULT%TYPE, IN_SCORE IN RTVQ_TR_QUALIFICATIONS.SCORE%TYPE, IN_DURATION_MIN IN RTVQ_TR_QUALIFICATIONS.DURATION_MIN%TYPE, IN_REMARKS_01 IN RTVQ_TR_QUALIFICATIONS.REMARKS_01%TYPE, IN_REMARKS_02 IN RTVQ_TR_QUALIFICATIONS.REMARKS_02%TYPE, IN_ATTEMPT_NUM IN RTVQ_TR_QUALIFICATIONS.ATTEMPT_NUM%TYPE, IN_CERTIFIER_ID IN RTVQ_TR_QUALIFICATIONS.CERTIFIER_ID%TYPE, IN_CREATED_BY IN RTVQ_TR_QUALIFICATIONS.CREATED_BY%TYPE, IN_IN_TRAINING_FLAG IN RTVQ_TR_TRAINING_TRACKER.IN_TRAINING_FLAG%TYPE, IN_END_DATE IN RTVQ_TR_TRAINING_TRACKER.END_DATE%TYPE, OUT_UPLOAD_MSG OUT VARCHAR2 ) AS V_PASS_PERCENT RTVQ_MA_SKILL_TESTTYPE_XREF.PASS_PERCENT%TYPE; V_RESULT RTVQ_TR_QUALIFICATIONS.RESULT%TYPE := IN_RESULT; V_PREV_RESULT RTVQ_TR_QUALIFICATIONS.RESULT%TYPE := NULL; V_SCORE RTVQ_TR_QUALIFICATIONS.SCORE%TYPE := IN_SCORE; V_SKILL_VALIDITY_PERIOD RTVQ_MA_SKILL.VALIDITY_PERIOD%TYPE; V_ATTEMPT_NUMBER RTVQ_TR_QUALIFICATIONS.ATTEMPT_NUM%TYPE := IN_ATTEMPT_NUM; V_QUAL_ROW_ID RTVQ_TR_QUALIFICATIONS.ROW_ID%TYPE; V_OLD_LAST_ACQUIRED_DATE RTVQ_TR_QUALIFICATIONS.ACQUIRED_DATE%TYPE; V_TT_ROW_ID RTVQ_TR_TRAINING_TRACKER.ROW_ID%TYPE; V_MAX_ACQUIRED_DATE RTVQ_TR_QUALIFICATIONS.ACQUIRED_DATE%TYPE; V_QUAL_INS_ROW_ID RTVQ_TR_QUALIFICATIONS.ROW_ID%TYPE; V_CERTIFIER_FLAG VARCHAR2(1):='0'; V_MSG VARCHAR2(4000):=''; EX_INVALID_INPUT EXCEPTION; EX_TRAINING_TRACKER EXCEPTION; BEGIN OUT_UPLOAD_MSG:='PASS'; --CHECK NULL FOR NOKIA ID IF IN_PERSON_ID IS NULL THEN V_MSG := 'NOKIA ID IS EMPTY'; RAISE EX_INVALID_INPUT; END IF; --CHECK NULL FOR CERTIFIER ID IF IN_CERTIFIER_ID IS NULL THEN V_MSG := 'CERTIFIER ID IS EMPTY'; RAISE EX_INVALID_INPUT; END IF; --CHECK NULL FOR SKILL ID IF IN_SKILL_ID IS NULL THEN V_MSG := 'SKILL ID IS EMPTY'; RAISE EX_INVALID_INPUT; END IF; --CHECK NULL FOR SCORE IF IN_SCORE IS NULL THEN V_MSG := 'SCORE IS EMPTY'; RAISE EX_INVALID_INPUT; END IF; --CHECK NULL FOR SKILL TEST LINK ID IF IN_SKILL_TEST_X_ID IS NULL THEN V_MSG := 'SKILL TEST TYPE IS EMPTY'; RAISE EX_INVALID_INPUT; END IF; --CHECK FOR VALID CERTIFIER ID BEGIN SELECT 1 INTO V_CERTIFIER_FLAG FROM RTVQ_MA_PERSON_DATA PD, RTVQ_MA_GROUP_MEMBERS GM, RTVQ_MA_GROUP G WHERE G.ROW_ID=GM.GROUP_ID AND UPPER(GM.PERSON_ID)=UPPER(PD.NOKIA_ID) AND UPPER(PD.NOKIA_ID)=UPPER(IN_CERTIFIER_ID) AND UPPER(G.NAME) LIKE 'I_EXT_RTVQ_CERTIFIERS_%'; EXCEPTION WHEN NO_DATA_FOUND THEN V_MSG := 'CERTIFIER NOT FOUND'; RAISE EX_INVALID_INPUT; WHEN OTHERS THEN V_MSG := 'INVALID CERTIFIER - '||SQLERRM(SQLCODE); RAISE EX_INVALID_INPUT; END; --GET PASS VALUE BEGIN SELECT NVL(PASS_PERCENT, 0) INTO V_PASS_PERCENT FROM RTVQ_MA_SKILL_TESTTYPE_XREF WHERE ROW_ID = IN_SKILL_TEST_X_ID; EXCEPTION WHEN OTHERS THEN V_PASS_PERCENT := 0; END; --IF THERE IS NO INPUT FOR SCORE, SET IT TO 0 BEGIN IF NVL(IN_SCORE, 0) >= V_PASS_PERCENT THEN V_RESULT := 'PASS'; ELSE V_RESULT := 'FAIL'; END IF; EXCEPTION WHEN OTHERS THEN V_RESULT := 'FAIL'; END; --CHECK FOR ENTRY IN QUALIFICATION TABLE BEGIN SELECT ROW_ID, NVL(ATTEMPT_NUM, 0), ACQUIRED_DATE, RESULT INTO V_QUAL_ROW_ID, V_ATTEMPT_NUMBER, V_OLD_LAST_ACQUIRED_DATE, V_PREV_RESULT FROM RTVQ_TR_QUALIFICATIONS WHERE UPPER(PERSON_ID) = UPPER(IN_PERSON_ID) AND SKILL_TEST_X_ID = IN_SKILL_TEST_X_ID; EXCEPTION WHEN NO_DATA_FOUND THEN V_QUAL_ROW_ID := NULL; V_ATTEMPT_NUMBER := 0; V_OLD_LAST_ACQUIRED_DATE := NULL; WHEN OTHERS THEN V_QUAL_ROW_ID := NULL; V_ATTEMPT_NUMBER := 0; V_OLD_LAST_ACQUIRED_DATE := NULL; END; --FIND ATTEMPT NUMBER --IF SKILL ACQUIRED DATE IS LESS THAN SYSDATE, RESET ATTEMPT NUMBER TO 0 BEGIN --IF V_RESULT='PASS' THEN --V_ATTEMPT_NUMBER := 0; --END IF; --SELECT NVL(VALIDITY_PERIOD, -- 0) --INTO V_SKILL_VALIDITY_PERIOD --FROM RTVQ_MA_SKILL --WHERE SKILL_ID = IN_SKILL_ID; --IF (NVL(IN_ACQUIRED_DATE, -- V_OLD_LAST_ACQUIRED_DATE) + V_SKILL_VALIDITY_PERIOD) < SYSDATE THEN --V_ATTEMPT_NUMBER := 0; --END IF; IF V_PREV_RESULT = 'PASS' THEN V_ATTEMPT_NUMBER := 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN V_ATTEMPT_NUMBER := 0; WHEN OTHERS THEN V_ATTEMPT_NUMBER := 0; END; -- UPDATE QUALIFICATION TABLE BEGIN IF V_QUAL_ROW_ID IS NOT NULL THEN UPDATE RTVQ_TR_QUALIFICATIONS SET ACQUIRED_DATE = NVL(IN_ACQUIRED_DATE, V_OLD_LAST_ACQUIRED_DATE), RESULT = V_RESULT, SCORE = V_SCORE, DURATION_MIN = IN_DURATION_MIN, REMARKS_01 = IN_REMARKS_01, REMARKS_02 = IN_REMARKS_02, ATTEMPT_NUM = V_ATTEMPT_NUMBER + 1, CERTIFIER_ID = IN_CERTIFIER_ID, MODIFY_DATE = SYSDATE, LAST_UPD_BY = IN_CREATED_BY WHERE ROW_ID = V_QUAL_ROW_ID; ELSE -- INSERT INTO QUALIFICATION TABLE INSERT INTO RTVQ_TR_QUALIFICATIONS (ROW_ID, PERSON_ID, SKILL_TEST_X_ID, ACQUIRED_DATE, RESULT, SCORE, DURATION_MIN, REMARKS_01, REMARKS_02, ATTEMPT_NUM, CERTIFIER_ID, CREATE_DATE, MODIFY_DATE, CREATED_BY, LAST_UPD_BY) VALUES (RTVQ_SEQ_QUALIFICA_ID.NEXTVAL, UPPER(IN_PERSON_ID), IN_SKILL_TEST_X_ID, IN_ACQUIRED_DATE, V_RESULT, V_SCORE, IN_DURATION_MIN, IN_REMARKS_01, IN_REMARKS_02, '1', IN_CERTIFIER_ID, SYSDATE, SYSDATE, IN_CREATED_BY, IN_CREATED_BY) RETURNING ROW_ID INTO V_QUAL_INS_ROW_ID; END IF; OUT_UPLOAD_MSG := 'PASS'; EXCEPTION WHEN OTHERS THEN V_MSG := 'QUALIFICATION UPDATE FAILED - '||SQLERRM(SQLCODE); RAISE EX_INVALID_INPUT; END; --SELECT SKILL ENTRY IN IN_TRAINING_TRACKER BEGIN SELECT ROW_ID INTO V_TT_ROW_ID FROM RTVQ_TR_TRAINING_TRACKER WHERE UPPER(PERSON_ID) = UPPER(IN_PERSON_ID) AND SKILL_ID = IN_SKILL_ID; EXCEPTION WHEN NO_DATA_FOUND THEN V_TT_ROW_ID := NULL; WHEN OTHERS THEN V_TT_ROW_ID := NULL; END; BEGIN SELECT MAX(ACQUIRED_DATE) INTO V_MAX_ACQUIRED_DATE FROM RTVQ_TR_QUALIFICATIONS Q WHERE EXISTS ( SELECT X2.ROW_ID FROM RTVQ_MA_SKILL_TESTTYPE_XREF X2 WHERE EXISTS (SELECT SKILL_ID FROM RTVQ_MA_SKILL_TESTTYPE_XREF X1 WHERE ROW_ID=IN_SKILL_TEST_X_ID AND X1.SKILL_ID=X2.SKILL_ID) AND X2.ROW_ID=Q.SKILL_TEST_X_ID ) AND UPPER(PERSON_ID) = UPPER(IN_PERSON_ID); EXCEPTION WHEN OTHERS THEN V_MAX_ACQUIRED_DATE := NULL; END; BEGIN IF V_MAX_ACQUIRED_DATE > SYSDATE THEN V_MSG:='QUALIFICATION DATE IS GREATER THAN CURRENT DATE'; RAISE EX_TRAINING_TRACKER; END IF; IF V_TT_ROW_ID IS NOT NULL THEN --UPDATE TRAINING TRACKER UPDATE RTVQ_TR_TRAINING_TRACKER SET QUALIFICATION_DATE = V_MAX_ACQUIRED_DATE, MODIFY_DATE = SYSDATE, LAST_UPD_BY = IN_CREATED_BY WHERE ROW_ID = V_TT_ROW_ID; ELSE --INSERT TRAINING TRACKER INSERT INTO RTVQ_TR_TRAINING_TRACKER (ROW_ID, PERSON_ID, SKILL_ID, LAST_PRACTICED_DATE, QUALIFICATION_DATE, CREATE_DATE, MODIFY_DATE, CREATED_BY, LAST_UPD_BY) VALUES (RTVQ_SEQ_TRAINING_ID.NEXTVAL, UPPER(IN_PERSON_ID), IN_SKILL_ID, V_MAX_ACQUIRED_DATE, V_MAX_ACQUIRED_DATE, SYSDATE, SYSDATE, IN_CREATED_BY, IN_CREATED_BY); END IF; EXCEPTION WHEN EX_TRAINING_TRACKER THEN V_MSG:='*******'||V_MSG||'*******'; OUT_UPLOAD_MSG := 'FAIL - '|| V_MSG; BEGIN DELETE FROM RTVQ_TR_QUALIFICATIONS WHERE ROW_ID = V_QUAL_INS_ROW_ID; EXCEPTION WHEN OTHERS THEN OUT_UPLOAD_MSG := 'FAIL - UNABLE TO DELETE QUALIFICATION'; END; WHEN OTHERS THEN OUT_UPLOAD_MSG := 'FAIL - UNABLE TO DELETE QUALIFICATION'; BEGIN DELETE FROM RTVQ_TR_QUALIFICATIONS WHERE ROW_ID = V_QUAL_INS_ROW_ID; EXCEPTION WHEN OTHERS THEN OUT_UPLOAD_MSG := 'FAIL - UNABLE TO DELETE QUALIFICATION'; END; END; EXCEPTION WHEN EX_INVALID_INPUT THEN V_MSG:='>>>>>>'||V_MSG||'>>>>>>'; OUT_UPLOAD_MSG := 'FAIL - '|| V_MSG; WHEN OTHERS THEN IF V_MSG IS NULL THEN V_MSG:=SQLERRM(SQLCODE); END IF; OUT_UPLOAD_MSG := 'FAIL - '|| V_MSG || '-' || SQLERRM(SQLCODE); END RTVQ_PR_INSUP_QUAL; END RTVQ_PA_INTERNAL_INTERFACE; /