Fixing LocAncestor Data integrity In Maximo

·         Take a backup of existing database table using below query

o   create table locancestor_bkp as (select * from locancestor);

·         Create a new procedure like this below and execute it.

*******************************************************************

create or replace procedure BUILD_LOCANCESTOR as

  MODULE_NAME varchar2(50) := 'LOCATIONS';

  SCRIPT_NAME varchar2(50) := 'BUILD_LOCANCESTOR.sql';

  TABLE_NAME  varchar2(50) := 'LOCANCESTOR';

  ERROR_COUNT number := 0; --Track no of errors

  REC_COUNTER number := 0; --Track no of loaded records

  R           number := 200; --Number of records to commit

  V_MAX_ID number;

  cursor LOCATIONS_CUR is

    select L.LOCATION, L.ORGID, L.SITEID, L.SYSTEMID from LOCHIERARCHY L;

  FIX_LOCATION LOCATIONS.LOCATION%type;

  FIX_SITEID   LOCATIONS.SITEID%type;

  /*These two variables are to hold the LOCATION and SITEID of the location

  for which we are building the hierarchy (the entries in LOCANCESTOR table)*/

  X_LOCATION LOCATIONS.LOCATION%type;

  X_SITEID   LOCATIONS.SITEID%type;

  /*These two variables are to hold the LOCATION and SITEID of the current location in the loop*/

  X_ANCESTOR_LOCATION LOCATIONS.LOCATION%type;

  X_ANCESTOR_SITEID   LOCATIONS.SITEID%type;

  /*These two variables are to hold the LOCATION and SITEID of the ancestor

  of the current location in the loop*/

begin

 

  execute immediate ('TRUNCATE TABLE ' || TABLE_NAME);

  execute immediate ('ALTER TABLE ' || TABLE_NAME || ' NOLOGGING');

  --RESET_SEQUENCE(TABLE_NAME);

  for LC in LOCATIONS_CUR loop

    declare

      FIX_SYSTEMID LOCHIERARCHY.SYSTEMID%type;

    begin

      FIX_LOCATION := LC.LOCATION;

      FIX_SITEID   := LC.SITEID;

      --These two variables will be fixed all the inner loop

     

      FIX_SYSTEMID := LC.SYSTEMID;

      X_LOCATION := LC.LOCATION;

      X_SITEID   := LC.SITEID;

      --These two variables will be changed for each iteration of the inner loop

      loop

        --Inner loop to build all LOCANCESTOR entries for the current location (FIX_LOCATION, FIX_SITEID)

        REC_COUNTER := REC_COUNTER + 1;

    

        insert /*+ APPEND */

        into LOCANCESTOR

          (LOCATION, ANCESTOR, SYSTEMID, SITEID, ORGID, LOCANCESTORID)

        values

          (FIX_LOCATION, --LOCATION

           X_LOCATION, --ANCESTOR

           FIX_SYSTEMID, --SYSTEMID

           LC.SITEID, --SITEID

           LC.ORGID, --ORGID

           LOCANCESTORSEQ.NEXTVAL --LOCANCESTORID

           );

        select max(H.PARENT), max(H.SITEID)

          into X_ANCESTOR_LOCATION, X_ANCESTOR_SITEID

          from LOCHIERARCHY H

         where H.LOCATION = X_LOCATION

           and H.SITEID = X_SITEID

           and H.SYSTEMID = FIX_SYSTEMID;

        if (X_ANCESTOR_LOCATION is null) then

          /*If there is no parent of the current location,

          so it's the top level one and we have to exit the inner loop*/

          exit;

        else

          X_LOCATION := X_ANCESTOR_LOCATION;

          X_SITEID   := X_ANCESTOR_SITEID;

        end if;

      end loop; --End the inner loop

    exception

      WHEN DUP_VAL_ON_INDEX THEN

        null;

      when others then

        ERROR_COUNT := ERROR_COUNT + 1;

       

    end;

    commit;

  end loop;

  commit;

  execute immediate ('ANALYZE TABLE ' || TABLE_NAME ||

                    ' COMPUTE STATISTICS');

  execute immediate ('ALTER TABLE ' || TABLE_NAME || ' LOGGING');

  DBMS_OUTPUT.PUT_LINE(TABLE_NAME || ' errors = ' || ERROR_COUNT);

 

end BUILD_LOCANCESTOR;

/

************************************************************

---------------------------------------------------------------------------- 

All the messagesbelow are just forwarded messages if some one feels hurt about it please add your comments we will remove the post.Host/author is not responsible for these posts.