/*
 What is this script for ? Is workaround to fix the error like: Cannot find ACLContainer for object xxx.... This script is for 11.2 release
 How to use this scripts ? 
    1. login SQL*Plus as OWBSYS, and run this sql script :SQL> @fixAllACLContainers_11_2.sql
    2. In SQL*Plus ,login as OWBSYS then:
           A. first setup the workspace to the correct workspace: SQL> call owb_workspace_manager.set_workspace(2); --(note: you need to find out the workspace
              ID by:  SQL>  select elementid , name from cmpworkspace_v; , and feed the correct elementid to the above procedure)
           B. if want fix all lost ACLContainers in the workspace, do: SQL> call fixAllLostACLContainerOfRepos(); 
                (Note:to see some output, first execute:SQL> set serveroutput on)

           C. if want to fix all lost ACLContainers in a given project, do: SQL> call fixAllLostACLContainerOfProj(<projectId>): 
                  (Note:you query projectid from cmpwbproject_v )
 
 
           D. if want to fix one lost ACLContainer for one object, do : SQL> call  fixLostACLContainer(objectId);  
              (Note: the objectid should be in the error message)

*/



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 fixAllLostACLContainerOfProj( projectId number) as

fcoId number;

fcocursor SYS_REFCURSOR;

hasACLContainer number;
acl number;
begin 
          --  DBMS_OUTPUT.PUT_LINE('-->begin fixAllLostACLContainerOfProj , proj id:'|| projectId);

    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 hasACLContainer from cmpaclcontainer_v con  where  con.elementid = acl;
            if(hasACLContainer <1) then
             DBMS_OUTPUT.PUT_LINE('-->Found object has no ACLContainer, and fixing it...., the object id is:'|| fcoId);

             fixLostACLContainer(fcoId);
            end if;
          
           end;
     end loop;
     close fcocursor;


exception 
        WHEN OTHERS THEN
        raise;


end; 

/




create or replace procedure fixAllLostACLContainerOfRepos  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 aclcontainer for project:'|| projName||' , projId:'||projId);

	    fixAllLostACLContainerOfProj(projId);
 
    end loop;

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


exception 
         WHEN OTHERS THEN
        raise;


end; 

/