/*
 What is this script for ? Is workaround to fix the error like: PRS-00503:  You do not have privilege:READ on object: DEFAULT_CONFIGURATION and other symptoms which actually underneath caused by this exception.  This script is for 11.2 release
 How to use this scripts ? 
    1. login SQL*Plus as OWBSYS, and run this sql script :SQL> @fixAllMissingCharMaps_11_2.sql
    2. In SQL*Plus ,login as OWBSYS then:
           A. first setup the workspace to the correct workspace, for example SQL> call owb_workspace_manager.set_workspace(2); --(note: here 2 is the workspace elementId and you need to find out the workspace elementID by:  SQL>  select elementid , name from cmpworkspace_v; , and feed the correct elementid to the above procedure)
           B. then to fix  all lost char maps in the workspace, do: SQL> call fixAllMissingCharMaps();
                (Note:to see some output, first execute:SQL> set serveroutput on)

*/



create or replace procedure fixLostACLContainer( FCOid number) 
as

elemId number;
userName varchar2(30);
theUOID varchar2(400);
thePrivOwner number;
aclOwnerId number;
charMap varchar2(200):= '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';

begin 
	select cwmseq.nextval into elemId  from dual;
	--elemId :=getElementId();
	aclOwnerId := elemId;
	select sys_context('userenv','current_user') into userName from dual;
	select sys_guid() into theUOID from dual;

	--add ACL container...

	insert into CMPACLContainer_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DELETEINOVERRIDE, DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,METADATASIGNATURE,NAME,  PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,ISSECURITYPOLICYSET,REALFCO)
	values('CMPACLContainer','0',userName,sysdate,'1','0','1','autocreatedinplsql',
	'1',elemId,'0','MapACL','','MapACL','1','0',theUOID,userName,sysdate,'1',FCOid);

          --     DBMS_OUTPUT.PUT_LINE('-->insert acl container id:'||elemId );


	---Need to hookup this ACL Continaer with the FCO
        update firstclassobject_v set aclcontainer = aclOwnerId where elementid = FCOid;

            --   DBMS_OUTPUT.PUT_LINE('-->update fco set aclcontainer col as: '||aclOwnerId);


	--then create access control lists...
	select cwmseq.nextval into elemId  from dual;

	--elemId := getElementId();
        select sys_guid() into theUOID from dual;

	--first for creator
/* in 11g r2: OWBSYS is not a valid user
	select elementid into thePrivOwner  from cmpwbuser_v where name = userName;

       DBMS_OUTPUT.PUT_LINE('-->update fco set aclcontainer col as: '||aclOwnerId);


         if (	thePrivOwner is null  ) then 
               DBMS_OUTPUT.PUT_LINE('the priv owner is null: ' );


             null; -- do nothing 
         else 
               DBMS_OUTPUT.PUT_LINE('the priv owner is NOT null: ' );

	insert into CMPAccessControlList_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,NAME,PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,FIRSTCLASSOBJECT,ACCESSCHARMAP,ACLCONTAINER,PRIVILEGEOWNER)
	values('CMPAccessControlList','0',userName,
	sysdate,'1','0','autocreatedinplsql',
	'1',elemId,'0','a','a','1','0',theUOID,userName,sysdate,
	aclOwnerId,charMap,aclOwnerId,thePrivOwner);

               DBMS_OUTPUT.PUT_LINE('-->insert acl charmap  id:'||elemId );

         end if; 
*/

	-- then for ADMIN role
	select cwmseq.nextval into elemId  from dual;

	--elemId := getElementId();
	
        select sys_guid() into theUOID from dual;

	select elementid into thePrivOwner  from cmpwbrole_v where name = 'ADMINISTRATOR';

	insert into CMPAccessControlList_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,NAME,PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,FIRSTCLASSOBJECT,ACCESSCHARMAP,ACLCONTAINER,PRIVILEGEOWNER)
	values('CMPAccessControlList','0',userName,
	sysdate,'1','0','autocreatedinplsql',
	'1',elemId,'0','a','a','1','0',theUOID,userName,sysdate,
	aclOwnerId,charMap,aclOwnerId,thePrivOwner);


              -- DBMS_OUTPUT.PUT_LINE('-->insert acl charmap  id:'||elemId );


	-- lastly for EVERYONE role
	--elemId := getElementId();
	select cwmseq.nextval into elemId  from dual;

        select sys_guid() into theUOID from dual;
 
	select elementid into thePrivOwner  from cmpwbrole_v where name = 'EVERYONE';

	insert into CMPAccessControlList_v(CLASSNAME,COMPLETED,CREATEDBY,CREATIONTIMESTAMP,
	CUSTOMERDELETABLE,CUSTOMERRENAMABLE,DESCRIPTION,
	EDITABLE,ELEMENTID,IMPORTED,LOGICALNAME,NAME,PERSISTENT,SEEDED,UOID,
	UPDATEDBY,UPDATETIMESTAMP,FIRSTCLASSOBJECT,ACCESSCHARMAP,ACLCONTAINER,PRIVILEGEOWNER)
	values('CMPAccessControlList','0',userName,
	sysdate,'1','0','autocreatedinplsql',
	'1',elemId,'0','a','a','1','0',theUOID,userName,sysdate,
	aclOwnerId,charMap,aclOwnerId,thePrivOwner);
	               
        --DBMS_OUTPUT.PUT_LINE('-->insert acl charmap  id:'||elemId );

	commit;

exception 
  WHEN OTHERS THEN
        raise;

end;

/


create or replace procedure fixAllLostCharMapOfProj( projectId number) as


fcoId number;

fcocursor SYS_REFCURSOR;

hasCharMap number;
acl number;
everyoneRoleId number;
begin 

    select elementid into everyoneRoleId from cmpwbrole_v where name ='EVERYONE';
    open fcocursor for
    select elementid, aclcontainer from firstclassobject_v start with
    elementid=projectId connect by prior elementid = owningfolder;
    loop
          begin

            fetch fcocursor into fcoId, acl;
            exit when fcocursor%NOTFOUND;
     
            --DBMS_OUTPUT.PUT_LINE('-->loop through each fco, id is:'|| fcoId);
                ------>here you can do any query on the fcoid , the fcoId is the elementid column in firstclassobject_v  
           select count(*) into hasCharMap from cmpaccesscontrollist_v list where list.aclcontainer= acl and list.privilegeOwner=everyoneRoleId;

           --select count(*) into hasACLContainer from cmpaclcontainer_v con  where  con.elementid = acl;
            if(hasCharMap <1) then
             DBMS_OUTPUT.PUT_LINE('-->Found object has no Char Map and fixing it...., the object id is:'|| fcoId);
             delete from cmpaccesscontrollist_v where aclcontainer=acl; --here remove the children of cmpaclcontainer_v 
             delete from cmpaclcontainer_v where elementid = acl; -- here fix the corrupted acl container

             fixLostACLContainer(fcoId); -- here insert a new acl container

            end if;
          
           end;
     end loop;
     close fcocursor;


exception 
        WHEN OTHERS THEN
        raise;


end; 

/



--following procedure will fix all missing char maps for the workspace.
create or replace procedure fixAllMissingCharMaps  as
projId number;
 
projcursor SYS_REFCURSOR;
projName varchar2(30);
begin 
  open projcursor for
    select elementid , name from cmpwbproject_v;
    loop
            fetch projcursor into projId, projName;
            exit when projcursor%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('-->start fix missing char maps for project:'|| projName||' , projId:'||projId);

	    fixAllLostCharMapOfProj(projId);
 
    end loop;

 close projcursor;
           
          DBMS_OUTPUT.PUT_LINE('-->Successfully finished fixing all lost char maps');


exception 
         WHEN OTHERS THEN
        raise;


end; 

/