Script to Fix Sundry Lines#
SET ECHO OFF -- -- PROGRAM NAME : FIX_SUNDRYLINES.SQL -- AUTHOR : IAIN BARR -- CREATED : 18-OCT-2005 -- DESCRIPTION : UPDATE FIELDS IN THE INF_CV_SUNDRYLINES TABLE AFTER INITIAL LOAD. -- IT FILLS DATA IN FIELDS THAT WERE NOT LOADED FROM THE LEGACY SYSTEM. -- LAST MODIFIED: 18-OCT-2005 - CHANGED CODE TO GET UNIT, GROUP, JOB AND POSITION -- ONLY IF THE INF COLUMNS ARE NULL. SPOOL FIX_SUNDRYLINES.LOG PROMPT UPDATING FIELDS IN INF_CV_SUNDRYLINES TABLE PROMPT . SET SERVEROUTPUT ON SIZE 1000000 SET LINES 200 SET PAGES 0 SET FEEDBACK OFF DECLARE CURSOR C_ASSIGN( P_PERSON_CODE VARCHAR2) IS SELECT P2K_SMGCD(DPS_ID, 'DPS') "POSN" , P2K_SMGCD(DJB_ID, 'DJB') "JOB" , P2K_SMGCD(DUN_ID, 'DUN') "UNIT" , P2K_SMGCD(DGR_ID, 'DGR') "GROUP_CODE" , PERSON_CODE FROM P2K_HR_ASSIGNMENTS EAS , P2K_HR_ASSIGNMENT_DETAILS EASD , P2K_HR_EMPLOYMENTS EEM , P2K_HR_IDENTITIES EID WHERE EID.ID = EEM.EID_ID AND EEM.ID = EAS.EEM_ID AND EAS.ID = EASD.EAS_ID AND SYSDATE BETWEEN EFFECTIVE AND EXPIRY AND PERSON_CODE = P_PERSON_CODE ORDER BY 1,2,3,4 ; R_ASSIGN C_ASSIGN%ROWTYPE; CURSOR C_PAY_POINT( P_PCCODE VARCHAR2) IS SELECT P2K_SMGCD(DEN_ID, 'DEN') "ENTITY" , P2K_SMGCD(DUN_ID, 'DUN') "UNIT" , P2K_SMGCD(DGR_ID, 'DGR') "GROUP_CODE" , PC_CODE , PAY_POINT_TYPE FROM P2K_PR_PAY_COMPONENTS PPC , P2K_PR_PC_RULES PPCR WHERE PPC.ID = PPCR.PPC_ID AND PPC.PC_CODE = P_PCCODE ; R_PAY_POINT C_PAY_POINT%ROWTYPE; CURSOR C_SUNDRIES IS SELECT ENTITY_CODE , PERSON_CODE , EMPLOYMENT_TYPE , HIRE_DATE , FREQUENCY_CODE , SUNDRY_LINE_TYPE , START_DATE , END_DATE , PC_CODE_HEADER , UNIT_CODE , GROUP_CODE , POSITION_CODE , JOB_CODE , LEAVE_TYPE_CODE , PLAN_CODE , USER_CALC_CODE , COUNTRY_CODE , STATE_PROVINCE_CODE , GEO_CODE , ASSIGNMENT_START_DATE , ASSIGNMENT_CODE , TIMES_REMAINING , PAY_POINT_TYPE , PAY_POINT_TASK , PAY_POINT_SEQUENCE , TIME_ENTERED , TIME_BASIS , WCB_CLASS , DISTRIBUTION_MASK , REFERENCE_INFO , PC_CODE_DETAILS , PC_ACTION , PC_DETAIL_TYPE , ELEMENT_CODE , USER_VARIABLE_CODE , PC_CALC_METHOD , DESCRIPTION , WAGE_RATE , WAGE_RATE_OVERRIDE , RATE_BASIS , RATE_BASIS_OVERRIDE , AMOUNT , AMOUNT_OVERRIDE , USER_VARIABLE_OVERRIDE , DISTRIBUTION_DR_MASK , DISTRIBUTION_CR_MASK , PC_RATE_SOURCE , HIGHER_OF_RATE , ROWID FROM INF_CV_SUNDRYLINES ORDER BY PERSON_CODE ; R_SUNDRIES C_SUNDRIES%ROWTYPE; V_COUNT NUMBER := 0; V_CNT_ERR NUMBER := 0; V_UPDATED_ASSIGN NUMBER := 0; V_UPDATED_PPOINT NUMBER := 0; BEGIN FOR R_SUNDRIES IN C_SUNDRIES LOOP V_COUNT := V_COUNT + 1; OPEN C_ASSIGN( R_SUNDRIES.PERSON_CODE); FETCH C_ASSIGN INTO R_ASSIGN; IF C_ASSIGN%FOUND THEN IF R_SUNDRIES.UNIT_CODE IS NULL THEN UPDATE INF_CV_SUNDRIES INF SET UNIT_CODE = R_ASSIGN.UNIT WHERE ROWID = R_SUNDRIES.ROWID ; END IF; IF R_SUNDRIES.GROUP_CODE IS NULL THEN UPDATE INF_CV_SUNDRIES INF SET GROUP_CODE = R_ASSIGN.GROUP_CODE WHERE ROWID = R_SUNDRIES.ROWID ; END IF; IF R_SUNDRIES.JOB_CODE IS NULL THEN UPDATE INF_CV_SUNDRIES INF SET JOB_CODE = R_ASSIGN.JOB WHERE ROWID = R_SUNDRIES.ROWID ; END IF; IF R_SUNDRIES.POSITION_CODE IS NULL THEN UPDATE INF_CV_SUNDRIES INF SET POSITION_CODE = R_ASSIGN.POSN WHERE ROWID = R_SUNDRIES.ROWID ; END IF; V_UPDATED_ASSIGN := V_UPDATED_ASSIGN +1; /* UPDATE INF_CV_SUNDRYLINES INF SET POSITION_CODE = R_ASSIGN.POSN , JOB_CODE = R_ASSIGN.JOB , UNIT_CODE = R_ASSIGN.UNIT , GROUP_CODE = R_ASSIGN.GROUP_CODE WHERE ROWID = R_SUNDRIES.ROWID ; */ ELSE DBMS_OUTPUT.PUT_LINE('NO ASSIGNMENT INFO. FOR EMPLOYEE '|| R_SUNDRIES.PERSON_CODE); V_CNT_ERR := V_CNT_ERR +1; END IF; CLOSE C_ASSIGN; OPEN C_PAY_POINT( R_SUNDRIES.PC_CODE_HEADER); FETCH C_PAY_POINT INTO R_PAY_POINT; IF C_PAY_POINT%FOUND THEN UPDATE INF_CV_SUNDRYLINES SET PAY_POINT_TYPE = R_PAY_POINT.PAY_POINT_TYPE WHERE ROWID = R_SUNDRIES.ROWID ; V_UPDATED_PPOINT := V_UPDATED_PPOINT +1; ELSE DBMS_OUTPUT.PUT_LINE('NO INFO. FOR PC_CODE '|| R_SUNDRIES.PC_CODE_HEADER); V_CNT_ERR := V_CNT_ERR +1; END IF; CLOSE C_PAY_POINT; END LOOP; DBMS_OUTPUT.PUT_LINE('...'); DBMS_OUTPUT.PUT_LINE('READ ' || LPAD(V_COUNT, 5) || ' RECORDS FROM INF_CV_SUNDRYLINES INFO.'); DBMS_OUTPUT.PUT_LINE('. ' || LPAD(V_CNT_ERR, 5) || ' ERRORS ENCOUNTERED. '); DBMS_OUTPUT.PUT_LINE('UPDATED ' || LPAD(V_UPDATED_ASSIGN, 5) || ' RECORDS WITH ASSIGNMENT INFO.'); DBMS_OUTPUT.PUT_LINE('UPDATED ' || LPAD(V_UPDATED_PPOINT, 5) || ' RECORDS WITH PAY_POINT INFO.'); --ROLLBACK; --CHOOSE ONE!! COMMIT; END; / SPOOL OFF PROMPT . PROMPT LOG FILE GENERATED [FIX_SUNDRYLINES.LOG]