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]