-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2009
--
-- US Government Users Restricted Rights - Use, duplication or disclosure
-- restricted by GSA ADP Schedule Contract with IBM Corp.

-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).


create or replace procedure makeCSConsistent as
   strsql varchar2(500);
   cursor tables is
     SELECT a.TABLE_NAME FROM USER_TAB_COLS a, USER_TABLES b WHERE a.COLUMN_NAME = 'CMID' AND a.TABLE_NAME like 'CM%' AND a.TABLE_NAME=b.TABLE_NAME ORDER BY a.TABLE_NAME ASC;
   util_cursor integer;
   num integer;
   
begin

   util_cursor := DBMS_SQL.OPEN_CURSOR;
   FOR tables_row in tables LOOP
      strsql := 'delete from '||tables_row.TABLE_NAME||' where CMID not in (select CMID from CMOBJECTS)';
      DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
      num := DBMS_SQL.EXECUTE(util_cursor);
   END LOOP;

   strsql := 'insert into CMOBJNAMES (CMID, LOCALEID, MAPDLOCALEID, ISDEFAULT, NAME) select CMID, 24, 24, 1, concat( ''REPAIRED BY CONSISTENCY CHECK '', RAWTOHEX(SYS_GUID())) from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES)';
   DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
   num := DBMS_SQL.EXECUTE(util_cursor);

   strsql := 'insert into CMPOLICIES (CMID) select CMID from CMOBJECTS where CMID not in (select CMID from CMPOLICIES)';
   DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
   num := DBMS_SQL.EXECUTE(util_cursor);

   strsql := 'insert into CMSTOREIDS (CMID, STOREID) select CMID, SUBSTR(''i00000000000000000000000000000000'',1,33 - LENGTH(RTRIM(CAST(CMID AS CHAR(10))))) || RTRIM(CAST(CMID AS CHAR(10))) from CMOBJECTS where CMID not in (select CMID from CMSTOREIDS)';
   DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
   num := DBMS_SQL.EXECUTE(util_cursor);
   
   strsql := 'update CMSTOREIDS set STOREID = NULL where UPPER(STOREID) in (select STOREID from (select b.CMID, UPPER(b.STOREID) as STOREID from CMSTOREIDS b) group by STOREID having COUNT(CMID) > 1)';
   DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
   num := DBMS_SQL.EXECUTE(util_cursor);
   
   strsql := 'update CMSTOREIDS set STOREID = SUBSTR(''i00000000000000000000000000000000'',1,33 - LENGTH(RTRIM(CAST(CMID AS CHAR(10))))) || RTRIM(CAST(CMID AS CHAR(10))) where CMID in (select CMID from CMSTOREIDS where STOREID is null)';
   DBMS_SQL.PARSE(util_cursor, strsql, DBMS_SQL.NATIVE);
   num := DBMS_SQL.EXECUTE(util_cursor);

   DBMS_SQL.CLOSE_CURSOR(util_cursor);

exception
   when others then
      if DBMS_SQL.IS_OPEN(util_cursor) then
         DBMS_SQL.CLOSE_CURSOR(util_cursor);
      end if;
      raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);  
end;
/

exec makeCSConsistent;

drop procedure makeCSConsistent;