-- 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 TABLE CMCHECKCONSISTENCY (
	INCONSISTENCY	number(10) not null,
	DESCRIPTION	varchar2(255),
	CMID	number(10) 
);

create or replace procedure getInconsistentCMIDs as
   tablename char(20);
   cmid number(10);
   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;
   cmid_cursor integer;
   fdbk integer;
begin
   cmid_cursor := DBMS_SQL.OPEN_CURSOR;
   FOR tables_row in tables LOOP
      if tables_row.TABLE_NAME != 'CMCHECKCONSISTENCY' then
      strsql := 'insert into CMCHECKCONSISTENCY(INCONSISTENCY,DESCRIPTION,CMID) select 1, '''||tables_row.TABLE_NAME||''', CMID from '||tables_row.TABLE_NAME||' where CMID not in (select CMID from CMOBJECTS)';
      DBMS_SQL.PARSE(cmid_cursor, strsql, DBMS_SQL.NATIVE);
      fdbk := DBMS_SQL.EXECUTE(cmid_cursor);
      end if;
   END LOOP;
   strsql := 'insert into CMCHECKCONSISTENCY(INCONSISTENCY,DESCRIPTION,CMID) select 2, ''CMOBJECTS'', CMID from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES) or CMID not in (select CMID from CMPOLICIES)';
   DBMS_SQL.PARSE(cmid_cursor, strsql, DBMS_SQL.NATIVE);
   fdbk := DBMS_SQL.EXECUTE(cmid_cursor);
   strsql := 'insert into CMCHECKCONSISTENCY(INCONSISTENCY,DESCRIPTION,CMID) select 3, ''CMSTOREIDS'', CMID from CMSTOREIDS where STOREID is null';
   DBMS_SQL.PARSE(cmid_cursor, strsql, DBMS_SQL.NATIVE);
   fdbk := DBMS_SQL.EXECUTE(cmid_cursor);
   strsql := 'insert into CMCHECKCONSISTENCY(INCONSISTENCY,DESCRIPTION,CMID) select 4, ''CMSTOREIDS'', s.CMID from CMSTOREIDS s where UPPER(s.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(cmid_cursor, strsql, DBMS_SQL.NATIVE);
   fdbk := DBMS_SQL.EXECUTE(cmid_cursor);
   DBMS_SQL.CLOSE_CURSOR(cmid_cursor);
exception
   when others then
      if DBMS_SQL.IS_OPEN(cmid_cursor) then
         DBMS_SQL.CLOSE_CURSOR(cmid_cursor);
      end if;
      raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);  
end;
/

Rem CMSCRIPT_SKIP_COMMAND
create or replace procedure printInconsistentCMIDs as
   tablename char(20);
   cmid number(10);
   cursor tables is
     SELECT a.DESCRIPTION, a.CMID FROM CMCHECKCONSISTENCY a WHERE a.INCONSISTENCY = 1 ORDER BY CMID ASC;
   cursor cmobjects is
     SELECT a.DESCRIPTION, a.CMID FROM CMCHECKCONSISTENCY a WHERE a.INCONSISTENCY = 2 ORDER BY CMID ASC;
   cursor cmstoreids is
     SELECT a.DESCRIPTION, a.CMID FROM CMCHECKCONSISTENCY a WHERE a.INCONSISTENCY = 3 ORDER BY CMID ASC;
   cursor cmduplicatestoreids is
     SELECT a.DESCRIPTION, a.CMID FROM CMCHECKCONSISTENCY a WHERE a.INCONSISTENCY = 4 ORDER BY CMID ASC;       
   header_printed integer;
begin
   header_printed := 0;
   FOR tables_row in tables LOOP
          if header_printed = 0 then
             DBMS_OUTPUT.PUT_LINE('CMIDs not in CMOBJECTS table');
             DBMS_OUTPUT.PUT_LINE('Table               Orphaned CMID');
             DBMS_OUTPUT.PUT_LINE('-----               -------------');
             header_printed := 1;
          end if;
          tablename := tables_row.DESCRIPTION;
          cmid := tables_row.CMID;
          DBMS_OUTPUT.PUT_LINE(tablename || TO_CHAR(cmid));
   END LOOP;
   header_printed := 0;
   FOR cmobjects_row in cmobjects LOOP
       if header_printed = 0 then
          DBMS_OUTPUT.PUT_LINE('');
          DBMS_OUTPUT.PUT_LINE('CMIDs in CMOBJECTS table but not in CMOBJNAMES or CMPOLICIES tables');
          DBMS_OUTPUT.PUT_LINE('Table               CMID');
          DBMS_OUTPUT.PUT_LINE('-----               -------------');
          header_printed := 1;
       end if;
          cmid := cmobjects_row.CMID;
       DBMS_OUTPUT.PUT_LINE('CMOBJECTS           ' || TO_CHAR(cmid));
   END LOOP;
   header_printed := 0;
   FOR cmobjects_row in cmstoreids LOOP
       if header_printed = 0 then
          DBMS_OUTPUT.PUT_LINE('');
          DBMS_OUTPUT.PUT_LINE('CMIDs in CMSTOREIDS table where STOREID is null');
          DBMS_OUTPUT.PUT_LINE('Table               CMID');
          DBMS_OUTPUT.PUT_LINE('-----               -------------');
          header_printed := 1;
       end if;
          cmid := cmobjects_row.CMID;
       DBMS_OUTPUT.PUT_LINE('CMSTOREIDS          ' || TO_CHAR(cmid));
   END LOOP;
     header_printed := 0;
   FOR cmobjects_row in cmduplicatestoreids LOOP
       if header_printed = 0 then
          DBMS_OUTPUT.PUT_LINE('');
          DBMS_OUTPUT.PUT_LINE('CMIDs in CMSTOREIDS table which have non-unique STOREIDs');
          DBMS_OUTPUT.PUT_LINE('Table               CMID');
          DBMS_OUTPUT.PUT_LINE('-----               -------------');
          header_printed := 1;
       end if;
          cmid := cmobjects_row.CMID;
       DBMS_OUTPUT.PUT_LINE('CMSTOREIDS          ' || TO_CHAR(cmid));
   END LOOP;
exception
   when others then
      raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);  
end;
/

Rem CMSCRIPT_SKIP_COMMAND
set serveroutput on format wrapped;

delete from CMCHECKCONSISTENCY;

exec getInconsistentCMIDs;

Rem CMSCRIPT_SKIP_COMMAND
exec printInconsistentCMIDs;

drop procedure getInconsistentCMIDs;

Rem CMSCRIPT_SKIP_COMMAND
drop procedure printInconsistentCMIDs;