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]
