CREATE OR REPLACE package body wb_workspace_management as


--global variable
m_charmap_len number:=100;
TYPE TTYPE is REF CURSOR;


PROCEDURE MyDebug(debugMessage VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
	
  null;
     --	insert into sochen_debug(t,info) values(to_date(sysdate,'yyyy/mm/dd:hh:mi:ss'),debugMessage);
	COMMIT;

END MyDebug;

procedure cleanTmpTables
	
as
begin
      delete from WS_TMP_CMPFCOClasses;
      delete from WS_TMP_CMPSCOClasses;
      delete from WS_TMP_CMPSCOCfgClasses;
      delete from WS_TMP_CMPSCOMapClasses;
      delete from WS_TMP_CMPSCOPrpClasses;
      delete from WS_TMP_FCOFOLDERNamespace;
      --Bug 8252852: No longer needed. MCM Association views changed to always check GlobalWksp.
      --Bug 8502681: Revert bug 8252852 MCM Association changes required.
      delete from WS_TMP_AllPCTree;
      delete from WS_TMP_AllInterlink;
      delete from WS_TMP_AllIntralink;

 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20002,'When creating workspace, error happened:'||SUBSTR(SQLERRM,1,400));


end cleanTmpTables;

procedure insertGlobalWkspToStorage

as
 global_ws_id number :=1;
 global_mmmcnt number :=0;
begin
	select count(*) into global_mmmcnt from CMPFCOStorage where workspaceId = global_ws_id 
           and s2_1 in ('CMPClassDefinition');
	if(global_mmmcnt > 0) then
	  return;
	end if;
	--gggraham: 02Mar2009. Bug 8252852: In addition to the meta-mdatadata rows, include all seeded,
	--           non-editable rows which are not meta-metadata rows.
	--          Column mappings: Editable is b_5, Seeded is b_11.
	insert into CMPFCOStorage select * from CMPFCOClasses where workspaceId = global_ws_id 
           and s2_1 in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into CMPFCOStorage select * from CMPFCOClasses where workspaceId = global_ws_id 
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType' 
              )
           and b_5=0
           and b_11=1;
	update CMPFCOStorage set isSeed=0 where workspaceId = global_ws_id and isSeed=1; 
	insert into CMPSCOStorage select * from CMPSCOClasses where workspaceId = global_ws_id 
           and s2_1 in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into CMPSCOStorage select * from CMPSCOClasses where workspaceId = global_ws_id 
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              )
           and b_5=0
           and b_11=1;
	update CMPSCOStorage set isSeed=0 where workspaceId = global_ws_id and isSeed=1; 

 exception WHEN OTHERS THEN
	raise;
end insertGlobalWkspToStorage;

procedure fixStorageForCurWksp(ws_id number)

as
begin
	update CMPFCOStorage    set isSeed=0 where workspaceId = ws_id and isSeed=1; 
	update CMPSCOStorage    set isSeed=0 where workspaceId = ws_id and isSeed=1; 

 exception WHEN OTHERS THEN
	raise;
end fixStorageForCurWksp;

procedure insertGlobalWkspToTmpTabs

as
 global_ws_id number :=1;
begin
	insert into WS_TMP_CMPFCOClasses select * from CMPFCOClasses where workspaceId = global_ws_id 
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into WS_TMP_CMPSCOClasses select * from CMPSCOClasses where workspaceId = global_ws_id 
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into WS_TMP_CMPSCOCfgClasses select * from CMPSCOCfgClasses where workspaceId = global_ws_id
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into WS_TMP_CMPSCOMapClasses select * from CMPSCOMapClasses where workspaceId = global_ws_id
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into WS_TMP_CMPSCOPrpClasses select * from CMPSCOPrpClasses where workspaceId = global_ws_id
           and s2_1 not in (
              'CMPArrayModelAttribute' ,'CMPAssociationDefinition' ,'CMPClassDefinition'
              ,'CMPClassUsage' ,'CMPClassUsageGroup' ,'CMPCollectionModelAttribute'
              ,'CMPDefinitionElement' ,'CMPDependencyDefinition' ,'CMPModelAttribute'
              ,'CMPModelDefinition' ,'CMPModelOwner' ,'CMPPrimitiveModelAttribute'
              ,'CMPPropertyDefinition' ,'CMPPropertyGroupDefinition' ,'CMPPropertyGroupOwner'
              ,'CMPPropertySetDefinition' ,'CMPReferenceModelAttribute' ,'CMPRegistryEntry'
              ,'CMPRegistryService' ,'CMPRoleDefinition' ,'CMPScalarModelAttribute'
              ,'CMPTranslatedField' ,'CMPAbstractType' ,'CMPAnonymousDomain'
              ,'CMPArrayType' ,'CMPCollectionType' ,'CMPDomain'
              ,'CMPNamedDomain' ,'CMPPrimitiveType' ,'CMPRange'
              ,'CMPScalarType'
              );
	insert into WS_TMP_FCOFOLDERNamespace select * from FCOFOLDERNamespace where workspaceId = global_ws_id;
	--Bug 6519987: Limit copied MCM Association data. But if restricted to rows with workspace parent, it regresses.
	--Bug 8252852: No longer needed. MCM Association views changed to always check GlobalWksp.
	--Bug 8502681: Revert bug 8252852 MCM Association changes required.
	insert into WS_TMP_AllPCTree select * from AllPCTree where workspaceId = global_ws_id;
	insert into WS_TMP_AllInterlink select * from AllInterlink where workspaceId = global_ws_id;
	insert into WS_TMP_AllIntralink select * from AllIntralink where workspaceId = global_ws_id;

 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20003,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end insertGlobalWkspToTmpTabs;
procedure fixTmpTabForCurWksp(ws_id number, ws_name varchar2,ws_owner varchar2)

as
 global_ws_id number :=1;

begin
    --1 fix the workspaceid
     update WS_TMP_CMPFCOClasses set workspaceid = ws_id;
     update WS_TMP_CMPSCOClasses set workspaceid = ws_id;
     update WS_TMP_CMPSCOCfgClasses set workspaceid = ws_id;
     update WS_TMP_CMPSCOMapClasses set workspaceid = ws_id;
     update WS_TMP_CMPSCOPrpClasses  set workspaceid = ws_id;



    -- 2 change the global wksp to be cur workspace
      update WS_TMP_CMPFCOClasses  set s4_2 = upper(ws_name) where  s2_1 = 'CMPWorkspace';
      update WS_TMP_CMPFCOClasses  set s4_1 = upper(ws_name) where  s2_1 = 'CMPWorkspace';
     -- update WS_TMP_CMPFCOClasses set i_1 = cwmWorkspaceSeq.nextval  where  s2_1 = 'CMPWorkspace';
     update WS_TMP_CMPFCOClasses set i_1 = ws_id  where  s2_1 = 'CMPWorkspace';
     update WS_TMP_CMPFCOClasses set s1_2 = sys_guid()  where  s2_1 = 'CMPWorkspace';

  --3 change the owningfolder to curWksp for miv, project, acl container, user, role, platform, user config template set
    update WS_TMP_CMPFCOClasses set r_6 = ws_id where s2_1 in ('CMPWBProject', 'CMPACLContainer', 'CMPWBMIVDefinition','CMPWBRole', 'CMPWBUser','CMPExtensionModel', 'CMPPlatform', 'CMPUserConfigTemplateSet');
  --4 change the owningWorkspace to curWksp for miv, project, acl container, user, role, platform
    update WS_TMP_CMPFCOClasses set r_15 = ws_id where s2_1 = 'CMPACLContainer';
    update WS_TMP_CMPFCOClasses set r_15 = ws_id where s2_1 = 'CMPUserConfigTemplateSet';
    update WS_TMP_CMPFCOClasses set r_15 = ws_id where s2_1 = 'CMPWBRole';
    update WS_TMP_CMPFCOClasses set r_16 = ws_id where s2_1 in ('CMPWBUser', 'CMPWBProject', 'CMPWBMIVDefinition');
    update WS_TMP_CMPFCOClasses set r_17 = ws_id where s2_1 = 'CMPPlatform';


  --5 change the workspace owner name to this wksp owner name
   update WS_TMP_CMPFCOClasses set s4_2 = upper(ws_owner) where s2_1 = 'CMPWBUser';
   update WS_TMP_CMPFCOClasses set s4_1 = upper(ws_owner)  where s2_1 = 'CMPWBUser';

  --6 fix the def proj name
--   update WS_TMP_CMPFCOClasses set s4_2 = upper(proj_name) where s2_1 = 'CMPWBProject' and s4_2 = 'MY_PROJECT';
  -- update WS_TMP_CMPFCOClasses set s4_1 = upper(proj_name)  where s2_1 = 'CMPWBProject' and s4_2 = 'MY_PROJECT';
  --7 namespace table info??
   update WS_TMP_FCOFOLDERNamespace  set workspaceid = ws_id;
   update WS_TMP_FCOFOLDERNamespace  set aggregateParent = ws_id where parentid = global_ws_id;
   update  WS_TMP_FCOFOLDERNamespace set parentid = ws_id where parentid = global_ws_id;
   --fix default project's namespace...
   --update  WS_TMP_FCOFOLDERNamespace set name = upper(proj_name) where domain = 'Project' and name = 'MY_PROJECT';
   --update  WS_TMP_FCOFOLDERNamespace set name = upper(proj_name) where domain = 'Project' and name = 'My Project';






  --fix cur workspace namespace...
  --update  WS_TMP_FCOFOLDERNamespace set name = upper(ws_name) where domain = 'CMPWorkspace';
  --Aug 02,06: since the unique on the FCOFOLDERNamespace is (workspaceid, parentid,name), but all workspace's workspaceid is different, and parentid which is userid is the same, but we want name unique among owners..., so set teh workspaceid to zero(cannot be one) for all workspace namespace entry and concate username with ws_name into the name field... this is kind of workaround
  update WS_TMP_FCOFOLDERNamespace set workspaceid = 0 where domain = 'CMPWorkspace';
  update WS_TMP_FCOFOLDERNamespace  set name = CONCAT(CONCAT(upper(ws_owner),'.'),upper(ws_name)) where  domain = 'CMPWorkspace';


 --aug 17, fix default control center and defalt owb loc, to change CONNECTED_AS_USER, SCHEMA to workspacename(actually chagne on cmpstringpropertyvalue_v, on value column....)
	update  WS_TMP_CMPSCOPrpClasses set s3_7 = upper(ws_name) where r_15=(select i_1 from  WS_TMP_CMPFCOClasses where s4_2= 'OWB_REPOSITORY_LOCATION' and s2_1 ='CMPLogicalLocation')  and s4_1='CMPLocation_Schema';
	update  WS_TMP_CMPSCOPrpClasses set s3_7 = upper(ws_name) where  r_15=(select i_1 from  WS_TMP_CMPFCOClasses where s4_2= 'OWB_REPOSITORY_LOCATION' and s2_1 ='CMPLogicalLocation')  and s4_1='CMPLocation_ConnectAsUser';

	--change on cmpruntimelocation_v on schema column
	update WS_TMP_CMPFCOClasses set s2_6 = upper(ws_owner||'.'||ws_name) where s4_2 = 'DEFAULT_CONTROL_CENTER' and s2_1 = 'CMPRuntimeLocation';




  --Bug 5583003: Copy/Paste requires seeded MCM Association data be copied into each workspace.
  --             Not sure if all rows are really needed, but at least those rows where the
  --             parentid=workspaceid (pctree) or linkfrom=workspaceid (interlink, intralink)
  --Bug 8252852: No longer needed. MCM Association views changed to always check GlobalWksp.
  --Bug 8502681: Revert bug 8252852 MCM Association changes required.
  update WS_TMP_AllPCTree set workspaceid = ws_id;
  update WS_TMP_AllPCTree set parentid = ws_id where parentid = global_ws_id;
  update WS_TMP_AllInterlink set workspaceid = ws_id;
  update WS_TMP_AllInterlink set linkfrom = ws_id where linkfrom = global_ws_id;
  update WS_TMP_AllIntralink set workspaceid = ws_id;
  update WS_TMP_AllIntralink set linkfrom = ws_id where linkfrom = global_ws_id;



 EXCEPTION WHEN OTHERS THEN
	raise;
	--	raise_application_error(-20004,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end fixTmpTabForCurWksp;

procedure insertTmpTabsToCurWksp
as

begin
  --Bug 5691039: Snapshot Import/Export requires seeded Classes info be copied into Storage.
  --Bug 6519987: But filter out any meta-metadata (exists in CMPFCOClasses, CMPSCOClasses).
  --Bug 6993426: Fix Snapshot import, but also include only necessary non-meta-metadata.
  --Bug 8252852: Explicit seeding of Storage for the current workspace is still required
  --             for the new workspace row itself and platform related data. Even though
  --             all non-editable, seeded rows are in Storage for GlobalWksp, they are only
  --             exposed in views like cmpelement_x, firstclassobject_x, secondclassobject_x and 
  --             folder_x (due to StaticGenerators.java changes made previously for MMM_CLASSES).
  --             This is sufficient to get MDL Snapshot functioning without going to the trouble
  --             of exposing them through all the _X views plus seeding ALLFCOFolderNamespaceStorage.
	insert into CMPFCOClasses select * from WS_TMP_CMPFCOClasses ;
	insert into CMPFCOStorage select * from WS_TMP_CMPFCOClasses where s2_1 in (
	             'CMPWorkspace', 'CMPWBProject', 'CMPDatatype', 'CMPTypeMapping',
	             'CMPPlatform', 'CMPPlatformMappingSet', 'CMPPlatformTypeSet');
	insert into CMPSCOClasses select * from WS_TMP_CMPSCOClasses ;
	insert into CMPSCOStorage select * from WS_TMP_CMPSCOClasses where s2_1 in (
	             'CMPWorkspace', 'CMPWBProject', 'CMPDatatype', 'CMPTypeMapping',
	             'CMPPlatform', 'CMPPlatformMappingSet', 'CMPPlatformTypeSet');
	insert into CMPSCOCfgClasses select * from WS_TMP_CMPSCOCfgClasses;
	--insert into CMPSCOCfgStorage select * from WS_TMP_CMPSCOCfgClasses;
	insert into CMPSCOMapClasses select * from WS_TMP_CMPSCOMapClasses;
	--insert into CMPSCOMapStorage select * from WS_TMP_CMPSCOMapClasses;
	insert into CMPSCOPrpClasses select * from WS_TMP_CMPSCOPrpClasses;
	--insert into CMPSCOPrpStorage select * from WS_TMP_CMPSCOPrpClasses;
	insert into FCOFOLDERNamespace select * from WS_TMP_FCOFOLDERNamespace;
	--Bug 8252852: No longer needed. MCM Association views changed to always check GlobalWksp.
	--Bug 8502681: Revert bug 8252852 MCM Association changes required.
	insert into AllPCTree select * from WS_TMP_AllPCTree;
	insert into AllInterlink select * from WS_TMP_AllInterlink;
	insert into AllIntralink select * from WS_TMP_AllIntralink;



 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20005,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end insertTmpTabsToCurWksp;




procedure copySeededCMPDataToWorkspace(ws_id number, ws_name varchar2,ws_owner varchar2)

as
begin
	cleanTmpTables();
	insertGlobalWkspToTmpTabs();
	fixTmpTabForCurWksp(ws_id, ws_name,ws_owner);
	insertTmpTabsToCurWksp();
	insertGlobalWkspToStorage();
	fixStorageForCurWksp(ws_id);


 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20000,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end copySeededCMPDataToWorkspace;


function isWorkspaceNameUnique(p_ws_name varchar2, p_owner varchar2,  toReserve boolean default false) return number

as
begin
 return NamespaceServiceImpl.isUniqueWorkspaceName(p_ws_name,p_owner, toReserve);

 exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20020,'When call isWorkspaceNameUnique ,error happened:'||SUBSTR(SQLERRM,1,400));
end isWorkspaceNameUnique;




  -- create_workspace creates a workspace with the specified name
  -- owned by the current user.
  -- workspace id should only be specified as a positive number when called by repository upgrade.
  -- workspace is set or not is not required
  procedure create_workspace(
    p_ws_name varchar2,
    p_owner varchar2 default user,
    p_ws_id number
  )

 as

PRAGMA AUTONOMOUS_TRANSACTION;

 ws_id number;
 triggerstmt varchar2(200);

 begin

  --p_ws_name := upper(p_ws_name);
 --0: any user can not use reserve word: GLOBAL_WORKSPACE
 if(upper(p_ws_name) = 'GLOBAL_WORKSPACE') then
    	raise_application_error(-20018,'Name GLOBAL_WORKSPACE is a reserved name for a workspace in OWB. Please choose a different name.');
 end if;	

 --0.1 prevent OWBSYS to create workspace
  if(upper(p_owner) = 'OWBSYS') then
   raise_application_error(-20021,'OWBSYS can not create a workspace since it can not be a workspace user or owner.');
  end if;
 --0.15: p_ws_name should not be null fix bug 5662500
  if(p_ws_name is null) then
   raise_application_error(-20037,'wb_workspace_management finds error: Workspace name can not be null.');
  end if;
 --0.2 check namespace
 if(isWorkspaceNameUnique(p_ws_name, p_owner, true) = 0) then
   raise_application_error(-20019,'The workspace name:'||p_ws_name||' already exists and owned by '||p_owner||'. The workspace name must be unique under its owner.');

 end if;



 --1st: check the p_owner is a valid db user

   WBSecurityHelper.registerOWBUser(upper(p_owner));

--fix bug 7417117
  	triggerstmt := 'CREATE OR REPLACE TRIGGER '|| upper(p_owner) ||'_TG '|| ' AFTER LOGON ON '|| upper(p_owner) ||'.SCHEMA '||' BEGIN '|| 'owbsys.wb_initialize_workspace; '||' END;' ;

	execute immediate triggerstmt;
   -- then seed the 1st default workspace
    set_default_workspace(upper(p_owner), upper(p_owner),upper(p_ws_name), true);


  --aug also need to grant teh user the tgt priv....
   begin
   wb_rti_target_schema_privs.grant_privileges(upper(p_owner));
   exception
     when others then
       null; -- do nothing...
   end;
	
  if (p_ws_id >= 0) then
    ws_id := p_ws_id;
  else
    select cwmWorkspaceSeq.nextval into ws_id from dual;
  end if;
  
  copySeededCMPDataToWorkspace(ws_id, p_ws_name,p_owner);

   -- create a runtime config entry for the workspace.
   insert into owb$wb_rt_platform_config (workspace_id, enterprise_mode, notm)
     select ws_id, enterprise_mode, 1 from owb$wb_rt_platform_config where workspace_id is null;

   --add commit here before unreserveworkspacename in case unreserveworkspacename has any error(autonomous txn is either all or nothing on any uncommitted data , here used "uncommitted" since there could have several commit/rollback on inside a autonomous txn)...
  commit;


 --last, need clean up the reservation for the workspcenamespace
  NamespaceServiceImpl.unReserveWorkspaceName(p_ws_name, p_owner);	

 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20001,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end create_workspace;

  -- create_workspace creates a workspace with the specified name
  -- owned by the current user.
  -- workspace is set or not is not required
  procedure create_workspace(
    p_ws_name varchar2,
    p_owner varchar2 default user
  )

 as

PRAGMA AUTONOMOUS_TRANSACTION;

 begin

 create_workspace(p_ws_name, p_owner, -1);

 EXCEPTION WHEN OTHERS THEN
	raise;
	--raise_application_error(-20001,'When create workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end create_workspace;


procedure   deleteOneTableWhenDrpWksp(tabName varchar2, theWorkspaceId number) as
PRAGMA AUTONOMOUS_TRANSACTION;

stmt varchar2(4000);

begin

	  stmt := 'delete from '|| tabName||' where workspaceid='||theWorkspaceId;	
           execute immediate stmt;
          commit; -- to not overrun the rollback segment if workspace is too large, so delete one table a time, and commit it


exception 
when others then 
 raise;
end;



procedure  deleteWorkspaceRecords(theWorkspaceId number ,ws_name varchar2, ws_owner varchar2) as


tcursor TTYPE ;
--stmt varchar2(4000);
tabName varchar2(30);

begin

	open tcursor for
	     select distinct(user_tab_cols.table_name) from user_tab_cols, user_tables where user_tab_cols.table_name=user_tables.table_name and user_tab_cols.column_name='WORKSPACEID';

	loop

		begin
		  fetch tcursor into tabName;
		  exit when tcursor%NOTFOUND;
                  deleteOneTableWhenDrpWksp(tabName, theWorkspaceId);
		 -- stmt := 'delete from '|| tabName||' where workspaceid='||theWorkspaceId;	
		 -- execute immediate stmt;

		end;

	end loop;

	close tcursor;

	--take special treatment on ns tables regarding workspace object since workspace object's workspace id i 0
	--dropWorkspaceNamespace();

	delete from FCOFOLDERNamespace where workspaceid = 0 and domain = 'CMPWorkspace' and upper(name) = CONCAT(CONCAT(upper(ws_owner),'.'),upper(ws_name));


EXCEPTION WHEN OTHERS THEN
	raise;
end deleteWorkspaceRecords;


procedure dealWithOWBUsersWhenDropWksp (theWorkspaceId number) as

tcursor TTYPE;
theUser varchar2(200);
stmt varchar2(4000);
v_ErrorCode number;

begin	
	--find out a list of users registered on this workspace but not on other workspace
	
       -- MyDebug('1. come to dealWithOWBUsersWhenDropWksp...');
	open tcursor for
		select user_name from workspace_assignment where workspace_id = theWorkspaceId
		MINUS
	        select user_name from workspace_assignment where workspace_id != theWorkspaceId;

	loop
	    begin
	      fetch tcursor into theUser;
	      exit when tcursor%NOTFOUND;

		--MyDebug('2. begin deal with user:'||theUser);
	        begin
		WBSecurityHelper.unRegisterOWBUser(upper(theUser));
                exception 
                  when others then 
                   null; --fix bug 6875031: allow workspace is dropped even if un-reg user get error... 
                 -- v_ErrorCode := SQLCODE;
                 -- MyDebug('sochen: unregister user get error,errorcode:'||v_ErrorCode||', error msg:'||SUBSTR(SQLERRM,1,200));
		--raise_application_error(-20030,' get error when un regi user:'||SUBSTR(SQLERRM,1,200));
                --  raise ;
	        end;
	        --MyDebug('3. WBSecurityHelper.unRegisterOWBUser:'|| theUser);

 		begin
	        	wb_rti_target_schema_privs.revoke_object_privileges(upper(theUser));
		exception
		 when others then
			null; --do nothing...
		end;
	        --MyDebug('4. after revoke obj privs on tgt user');

		

	/*
	      stmt := 'revoke OWB_USER from '||theUser;
	      execute immediate stmt;
	
	      stmt := 'revoke OWB$CLIENT from '||theUser;
	      execute immediate stmt;
	
	      stmt := 'revoke OWB_DESIGNCENTER_VIEW from '||theUser;
	      execute immediate stmt;
	
       */
	    end;
	end loop;
	

	close tcursor;

exception when others then
      
 	raise;

end dealWithOWBUsersWhenDropWksp;




procedure checkInvokerHasOWBAdminRole
as

isAdmin number:=-1;
begin

	select sys_context('owb_workspace','isWorkspaceAdmin') into isAdmin from dual;

	if(isAdmin !=1 OR isAdmin is null) then
	       	raise_application_error(-20014,'You must be the workspace administrator to manage workspaces, workspace users and workspace system privileges.');

	end if;

end checkInvokerHasOWBAdminRole;


  -- drop_workspace
  -- Checks that the user has the appropriate privilege (ADMIN_WS) on the
  -- specified workspace or is the workspace owner, and if so, drops the
  -- workspace.
  -- workspace is set or not is not required
  -- if the workspace owner is not valid anymore e.g. it is not a db user or not re-created after drop, you can login as owbsys or sys  to drop a workspace

  procedure drop_workspace(
    p_ws_name varchar2,
    p_owner varchar2 default user
  )
as

PRAGMA AUTONOMOUS_TRANSACTION;
theWorkspaceId number;

begin

	if(upper(p_ws_name) = 'GLOBAL_WORKSPACE' ) then
	  raise_application_error(-20031,'The GLOBAL_WORKSPACE should not be dropped.');
	end if;


	begin
	 select workspace_id into theWorkspaceId  from workspace_assignment where upper(workspace_name) = upper(p_ws_name) and upper(user_name) = upper(p_owner) and  isWorkspaceOwner = '1';
	
	exception when NO_DATA_FOUND then
         raise_application_error(-20030,'The workspace with name:'||p_ws_name||' and owner:'||p_owner||' does not exist.');
	
	end;

	--set the workspace... for easier query to find out if the current login is a workspace admin
	owb_workspace_manager.set_workspace(theWorkspaceId);
	
	--need to lock on the workspace(robert will help here)
	if(OWB_LOCK.LOCK_WORKSPACE(theWorkspaceId, OWB_LOCK.EXCLUSIVE_MODE)=false) then

	   raise_application_error(-20034,'You can not drop the workspace:'||p_owner||'.'||p_ws_name||' since other sessions are using this workspace at this time. Please try again later.');

	end if;

	-- check if the invoker is workspace admin...
	checkInvokerHasOWBAdminRole();

	-- check if users are registered to other workspaces or not, if not, need to revoke owb related roles: OWB_USER, OWB$CLIENT, OWB_DESIGNCENTER_VIEW
	
	dealWithOWBUsersWhenDropWksp(theWorkspaceId);

   	deleteWorkspaceRecords(theWorkspaceId,p_ws_name, p_owner );	
	--now release the lock...
	
	OWB_LOCK.UNLOCK_WORKSPACE(theWorkspaceId, OWB_LOCK.EXCLUSIVE_MODE);

	--fix bug 5705152
	 owb_workspace_manager.unset_workspace;
	commit;


exception WHEN OTHERS THEN
	--begin
	
	--OWB_LOCK.UNLOCK_WORKSPACE(theWorkspaceId, OWB_LOCK.EXCLUSIVE_MODE);

	--exception WHEN OTHERS THEN
	  -- null;
	--end;

	raise;

end drop_workspace;




procedure checkWorkspaceBeSetted

as

isSetted number:=-1;

begin

    	select  sys_context('owb_workspace','workspaceID') into isSetted from dual;

	if(isSetted <1 OR isSetted is null) then
	
      	raise_application_error(-20013,'The workspace has not been set. Please call wb_workspace_management.set_workspace procedure to set workspace first. Or you can go to OWB Client Console and under menu Tool/Preference you can change your default workspace to a valid workspace within Environment prefrence group then whenever you login to database your valid default workspace will be automantically set.');

	end if;

end checkWorkspaceBeSetted;




procedure  validateUser(p_user varchar2)

as
  isValidUser number:=-1;
begin

	select count(*) into isValidUser from cmpwbuser_v where upper(name) = upper(p_user);
	if(isValidUser != 1) then
        	raise_application_error(-20015,'The argument: '|| p_user||' is not a valid user on this workspace:'||sys_context('owb_workspace','workspaceName')||'.' );

	end if;


end validateUser;

function computeValidSysPrivNames return varchar2

as
  tcursor TTYPE;
  validSysNames varchar2(4000);
  validSys varchar2(50);
  beginConcat boolean :=false;

begin 	

	open tcursor for
	   select SYSPRIVNAME from SYS_PRIV_ID_TO_NAME_TAB MINUS
           select obsolete_sys_name from obsolete_sys_privs;
	
	loop
		begin
			fetch tcursor into validSys;
			exit when tcursor%NOTFOUND;
			--if(beginConcat = false) then
			--	validSysNames:= validSys;
			--end if;	
			beginConcat:=true;	
			validSysNames:=validSysNames||validSys||',';

		end;

	end loop;

	close tcursor;

	--if(beginConcat=true) then
		--validSysNames.

--	end if;

	return validSysNames;

end computeValidSysPrivNames;


procedure   validateSysPrivName(p_priv_name varchar2)
as

isValidSysName number:=-1;
isObsoleted number:=-1;
begin
	select count(*) into isValidSysName from SYS_PRIV_ID_TO_NAME_TAB where upper(sysPrivName) = upper(p_priv_name);

	if(isValidSysName !=1) then
        	raise_application_error(-20016,'The argument: '|| p_priv_name||' is not a valid workspace system privilege name. The list of valid names:'||computeValidSysPrivNames());

	end if;

	--if the name is obsolete, also need to throw exception
	select count(*) into isObsoleted from obsolete_sys_privs where upper(obsolete_sys_name)= upper(p_priv_name);

	if(isObsoleted > 0) then
        	raise_application_error(-20018,'The argument: '|| p_priv_name||' is not a valid workspace system privilege name.The list of valid names:'||computeValidSysPrivNames());

	
	end if;


end validateSysPrivName;


/*
*
*
*/
procedure doManageSysPriv(p_priv_name varchar2,p_user varchar2, isGranting number)

as
--PRAGMA AUTONOMOUS_TRANSACTION;--only need to be done at the top level caller instead of callee procedure...

  charToBeSet CHAR(1);
  theCharMap varchar2(255);
secondPartCharMap varchar2(255);
  hasCharMap number := 1;
   theSysId number;
   pos number;
begin
	begin
	select SYSPRIVSACCESSCHARMAP into theCharMap from cmpwbuser_v where upper(name)= upper(p_user);
	
	exception when NO_DATA_FOUND then
	  hasCharMap := 0;

	end;

	if(theCharMap is null) then
        	--MyDebug('0 theCharmap is null');
	    hasCharMap := 0;

	elsif(LENGTH(theCharMap) <=0) then
            --MyDebug('0.5 theCharmap length is 0');

	    hasCharMap := 0;
	
	end if;	
	
	
	--MyDebug('1. sys char map is:'||theCharMap||', hasCharMap:'||hasCharMap);
	if(hasCharMap = 0) then
		--no charmap, need to create one
		theCharMap := '0';
		for i IN 1..m_charmap_len-1 LOOP
			theCharMap :=CONCAT(theCharMap,'0');
		END LOOP;

	end if;
	--MyDebug('2. sys char map now is:'||theCharMap||', hasCharMap:'||hasCharMap);


	if(isGranting = 1) then
		charToBeSet:='1';
	else	
		charToBeSet:='0';
	end if;
	
	--find which position to be set
	
	select sysPrivId into theSysId from 	SYS_PRIV_ID_TO_NAME_TAB where upper(sysPrivName) = upper(p_priv_name);

	--MyDebug('3. sys id is:'||theSysId);

	pos := theSysId+1; -- in PL/SQL, string index starts with 1

	-- now set the char to be wanted
	secondPartCharMap := SUBSTR(theCharMap,pos+1);
	--MyDebug('4. secondPartCharMap is:'||secondPartCharMap);

	theCharMap :=CONCAT(CONCAT(SUBSTR(theCharMap,1, pos-1), charToBeSet),secondPartCharMap);
	--MyDebug('5. theCharMap is:'||theCharMap);

	-- finally update the sys charmap column on the user
	update cmpwbuser_v set SYSPRIVSACCESSCHARMAP = theCharMap where upper(name) = upper(p_user);
	--MyDebug('6. finished update:'||theCharMap);

	--commit;
 exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20015,'When grant/revoke workspace system privileges to a user '||p_user||', error happened:'||SUBSTR(SQLERRM,1,400));

end doManageSysPriv;



  -- grant_workspace_sys_priv
  -- Checks that the user is a  workspace admin on the
  -- specified workspace ,  and if so, grants the
  -- specified privilege to the specified user.
  -- assume that the workspace is already set by set_workspace call
  procedure grant_workspace_sys_priv(
    p_priv_name varchar2,
    p_user varchar2

  ) as

PRAGMA AUTONOMOUS_TRANSACTION;

isGranting number:=1;
wksp_id number;
userID number;

begin
	--note: here we need to first lock the p_user to make any changes otherwise if other session is changing the same object p_user, this procedure will hang until another session commit/rollback... now we skip this locking service ...

	-- check the workspace has been set or not
	checkWorkspaceBeSetted();

	--check whether the invoker is OWB_ADMIN in this workspace...
	checkInvokerHasOWBAdminRole();

	--check p_user is valid owb user in this workspace
        validateUser(p_user);
        --check priv_name
        validateSysPrivName(p_priv_name);

	--first lock the user
	select  sys_context('owb_workspace','workspaceID') into wksp_id from dual;
	select elementid into userID from cmpwbuser_v where upper(name) = upper(p_user);
	if(OWB_LOCK.LOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE)=false) then
	 raise_application_error(-20033, 'You cannot grant the system privilege to the user:'||p_user||' now since you can not get the necessary write lock. Please try it later.');

	end if;
	doManageSysPriv(p_priv_name,p_user, isGranting);	
	
	--after done, release lock
	
	OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE);
	
		
    commit;
 exception WHEN OTHERS THEN
	--begin

	--OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE);

	 --exception WHEN OTHERS THEN
	   --null;
	--end;

	raise;
	--raise_application_error(-20017,'When grant/revoke workspace system privileges to/from a user '||p_user||', error happened:'||SUBSTR(SQLERRM,1,400));

end grant_workspace_sys_priv;








  -- revoke_worpkspace_priv
  -- Checks that the user has the appropriate privilege (ADMIN_WS) on the
  -- specified workspace or is the workspace owner, and if so, revokes the
  -- specified privilege from the specified user.
  -- assume that the workspace is already set by set_workspace call
  procedure revoke_workspace_sys_priv(
    p_priv_name varchar2,
    p_user varchar2
  ) as
PRAGMA AUTONOMOUS_TRANSACTION;

isGranting number:=0;
wksp_id number;
userID number;

begin

	--note: here we need to first lock the p_user to make any changes otherwise if other session is changing the same object p_user, this procedure will hang until another session commit/rollback... now we skip this locking service ...

	-- check the workspace has been set or not
	checkWorkspaceBeSetted();

	--check whether the invoker is OWB_ADMIN in this workspace...
	checkInvokerHasOWBAdminRole();

	--check p_user is valid owb user in this workspace
        validateUser(p_user);
        --check priv_name
        validateSysPrivName(p_priv_name);
	
	--first lock the user
	select  sys_context('owb_workspace','workspaceID') into wksp_id from dual;
	select elementid into userID from cmpwbuser_v where upper(name) = upper(p_user);
	if(OWB_LOCK.LOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE)=false) then
	 raise_application_error(-20033, 'You cannot grant the system privilege to the user:'||p_user||' now since you can not get the necessary write lock. Please try it later.');

	end if;
	doManageSysPriv(p_priv_name,p_user, isGranting);	
	
	--after done, release lock	
	OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE);

	commit;
 exception WHEN OTHERS THEN

	raise;
	--begin
	  --OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.WRITE_MODE);

	-- exception WHEN OTHERS THEN
	  --null;
	--end;

	--raise_application_error(-20012,'When grant/revoke workspace system privileges to/from a user '||p_user||', error happened:'||SUBSTR(SQLERRM,1,400));

end revoke_workspace_sys_priv;


procedure     validateUserName(p_user varchar2) as

isDBUser number:=0;
isExistingWkspUser number:=0;
begin

	select count(*) into isDBUser from all_users where username = upper(p_user);
 	if(isDBUser <1) then
           raise_application_error(-20023, 'The user with name:'||p_user||' is not an existing database user.');
	end if;

	--then check if the p_user is already existing owbuser
        select count(*) into isExistingWkspUser from cmpwbuser_v where upper(name) = upper(p_user);
	if(isExistingWkspUser > 0 ) then
           raise_application_error(-20024, 'The user with name:'||p_user||' has already registered to the workspace:'||  sys_context('owb_workspace','workspaceName')||' owned by:'||sys_context('owb_workspace','workspace_owner'));
	end if;

end validateUserName;

function doesDBUserExist(p_user varchar2) return boolean
as

isDBUser number;

begin

	select count(*) into isDBUser from all_users where username = upper(p_user);
 	if(isDBUser <1) then
           return false;
	end if;
        return true;

exception
   	  when others then
		raise;

end doesDBUserExist;


procedure  validateUserNameforUnRegister(p_user varchar2) as

isDBUser number:=0;
isExistingWkspUser number:=0;
begin

	/* fix bug 5622487
	select count(*) into isDBUser from all_users where username = upper(p_user);
 	if(isDBUser <1) then
           raise_application_error(-20023, 'The user with name:'||p_user||' is not an existing database user.');
	end if;
	*/
       --then check if the user is an owb user

       select count(*) into isExistingWkspUser from cmpwbuser_v where upper(name) = upper(p_user);
	if(isExistingWkspUser < 1 ) then
           raise_application_error(-20024, 'The user with name:'||p_user||' is not a registered user on the workspace:'||  sys_context('owb_workspace','workspaceName')||' owned by:'||sys_context('owb_workspace','workspace_owner'));
	end if;

end validateUserNameforUnRegister;



procedure insertCMPInfoForUser(p_user varchar2, p_is_ws_admin number )

as

theWkspId number;
theElemId number;
theUserId number;
everyOneRoleId number;
adminRoleId number;
sysprivsCharMap varchar2(200);
theUOID varchar2(200);
defAccessCharMap varchar2(200);
baseISOID varchar2(200);
begin

  --1 insert cmpwbuser row
   	

	sysprivsCharMap := '0';
	for i IN 1..m_charmap_len-1 LOOP
		sysprivsCharMap :=CONCAT(sysprivsCharMap,'0');
	END LOOP;

        select cwmseq.nextval into theElemId  from dual;
        theUserId := theElemId;
	select  sys_context('owb_workspace','workspaceID') into theWkspId  from dual;
        select sys_guid() into theUOID from dual;

	insert into cmpwbuser_v( WorkspaceID, ClassName  ,Completed ,CreatedBy,CreationTimestamp,CustomerDeletable,
    CustomerRenamable   ,Description   ,Editable ,ElementId , Imported
   ,LogicalName  ,Name   ,Persistent   ,Seeded   ,UOID  ,UpdatedBy ,UpdateTimestamp
   ,SysPrivsAccessCharMap   ,OwningFolder   ,IsProtectedFromPublic   ,IsWorkspaceOwner
   ,OwningWorkspace) values(theWkspId, 'CMPWBUser', '0',user,sysdate, '1','0','autocreatedbyplsql','1',theElemId,
   '0',upper(p_user),upper(p_user),'1','0',theUOID,user,sysdate,sysprivsCharMap,theWkspId,'0','0',theWkspId);
    --2. insert access preference info
       select elementid into everyOneRoleId from cmpwbrole_v where name = 'EVERYONE';
          select sys_guid() into theUOID from dual;


	defAccessCharMap := '1';
	for i IN 1..m_charmap_len-1 LOOP
		defAccessCharMap :=CONCAT(defAccessCharMap,'1');
	END LOOP;

	theElemId := theElemId+1;
	insert into cmpaccesspreference_v(WorkspaceID, ClassName ,Completed ,CreatedBy ,CreationTimestamp
           ,CustomerDeletable ,CustomerRenamable ,Description ,Editable
           ,ElementId ,Imported ,LogicalName ,Name   ,Persistent  ,Seeded   ,UOID
   	   ,UpdatedBy ,UpdateTimestamp,FirstClassObject ,AccessCharMap ,PrivilegeOwner,Wbuser) values(
	   theWkspId, 'CMPAccessPreference', '0',user, sysdate,'1','0','autocreatedbyplsql','1',
          theElemId,'0','A','A','1','0',theUOID,user,sysdate,theUserId,	defAccessCharMap,everyOneRoleId,theUserId
	);

    --3. assign everyone role to the p_user
	theElemId := theElemId+1;	
        select sys_guid() into theUOID from dual;
	
	insert into CMPRoleAssignment_v(    WorkspaceID, ClassName ,Completed ,CreatedBy,CreationTimestamp
  	 ,CustomerDeletable ,CustomerRenamable ,Description,Editable
	   ,ElementId, Imported ,LogicalName ,Name  ,Persistent,Seeded,UOID
   	,UpdatedBy,UpdateTimestamp,FirstClassObject,Wbrole,Wbuser) values(theWkspId, 'CMPRoleAssignment','0',
	  user,sysdate,'1','0','autocreatedbyplsql','1',theElemId,'0','R','R','1','0',theUOID,user,
	 sysdate,theUserId,everyOneRoleId,theUserId );

	if( p_is_ws_admin  = 1) then
	       select elementid into adminRoleId from cmpwbrole_v where name = 'ADMINISTRATOR';
	        theElemId := theElemId+1;	
	        select sys_guid() into theUOID from dual;

         	insert into CMPRoleAssignment_v(    WorkspaceID, ClassName ,Completed ,CreatedBy,CreationTimestamp
  	 	,CustomerDeletable ,CustomerRenamable ,Description, Editable
	   	,ElementId , Imported ,LogicalName ,Name  ,Persistent,Seeded,UOID
   		,UpdatedBy,UpdateTimestamp,FirstClassObject,Wbrole,Wbuser) values(theWkspId, 'CMPRoleAssignment','0',
	  	user,sysdate,'1','0','autocreatedbyplsql','1',theElemId,'0','R','R','1','0',theUOID,user,
	 	sysdate,theUserId,adminRoleId,theUserId );


	end if;


        --finally, insert into namespace
        select ISOID into baseISOID from cmpsupportedLanguage_v where IsBaseLanguage = '1';

	insert into cmpallfolderfconames( WORKSPACEID , PARENTID, DOMAIN, NAME, ISPHYS ,
		ELEMENTID, AGGREGATEPARENT, LANGUAGEISOID) values(theWkspId,theWkspId,'PrivilegeOwner',upper(p_user),
		1,theUserId,theWkspId,baseISOID );

	insert into cmpallfolderfconames( WORKSPACEID , PARENTID, DOMAIN, NAME, ISPHYS ,
		ELEMENTID, AGGREGATEPARENT, LANGUAGEISOID) values(theWkspId,theWkspId,'PrivilegeOwner',upper(p_user),
		2,theUserId,theWkspId,baseISOID );

	
end  insertCMPInfoForUser;

procedure   deleteCMPInfoForUser(p_user varchar2) as

userId number;

begin
	--first delete anything owned by this user
	select elementid into userId from cmpwbuser_v where upper(name) = upper(p_user);
        delete from CMPSCOElement_V where firstclassobject = userId;
	delete from FirstClassObject_V where elementid = userId;
       --then delete namespace stuff
        delete from cmpallfolderfconames where workspaceid = get_workspace_id() and domain = 'PrivilegeOwner' and upper(name) = upper(p_user);

end deleteCMPInfoForUser;


 --register a db user to the current workspace
 -- assume that the workspace is already set by set_workspace call
--fix bug 5860256: OWBSYS is not allowed to register owb user to any workspace
 procedure register_workspace_user(p_user varchar2 , p_is_ws_admin number default 0)
as

PRAGMA AUTONOMOUS_TRANSACTION;
wksp_id number;


begin
    --0 check if workspace set or not
	checkWorkspaceBeSetted();
    --check if the invoker is OWBSYS ? if yes now allow to register user
       if(upper(user)='OWBSYS') then 
         raise_application_error(-20038,'OWBSYS can not register workspace users. Try to register workspace users by workspace owner');
       end if;
    --0.5 check if the invoker is admin
     checkInvokerHasOWBAdminRole();
    --1 check if the p_user already exists
       validateUserName(p_user);
    
    	
    --request lock on the workspace
      select sys_context('owb_workspace','workspaceID') into wksp_id from dual;
      if(OWB_LOCK.LOCK_WORKSPACE(wksp_id, OWB_LOCK.SHARED_MODE) = false) then
	
		 raise_application_error(-20035, 'You cannot register user:'||p_user||' at this time due to the current workspace:'||sys_context('owb_workspace','workspaceName')|| ' is used by another session in single user mode. Please try it later.');

      end if;

    --2. register the uesr, it may fail due to the user has the default role set as ALL 	
      WBSecurityHelper.registerOWBUser(upper(p_user));
   -- 3. insert a row into cmpwbuser_v for this user
	insertCMPInfoForUser(p_user, p_is_ws_admin  );

   --4 aug 24,06: need to grant the necessary privs needed when the user used as target user later...
	begin
        	wb_rti_target_schema_privs.grant_privileges(p_user);
	exception
	  when others then
		null; --do nothing
	end;


    -- now unlock the workspace
	begin
	   OWB_LOCK.UNLOCK_WORKSPACE(wksp_id, OWB_LOCK.SHARED_MODE);

	exception WHEN OTHERS THEN
		null; -- do not care what happened...
	end;

	commit;


 exception WHEN OTHERS THEN
	--begin
	  -- OWB_LOCK.UNLOCK_WORKSPACE(wksp_id, OWB_LOCK.SHARED_MODE);

	--exception WHEN OTHERS THEN
		--null; -- do not care what happened...
       ----	end;

       raise;

end  register_workspace_user;


procedure  revokeTgtPriv(theUser varchar2) as

registeredToThisWksp number;
registeredToAllWksp number;

begin

	select count(*) into registeredToThisWksp  from cmpallwbuser_v where upper(name) = upper(theUser) and workspaceid = sys_context('owb_workspace','workspaceID');

	select count(*) into  registeredToAllWksp from cmpallwbuser_v where upper(name) = upper(theUser);

	if(( registeredToAllWksp - registeredToThisWksp)>0 ) then
		return;
 	end if;

	wb_rti_target_schema_privs.revoke_object_privileges(upper(theUser));



exception
	when others then
		null; -- do nothing...

end revokeTgtPriv;


 --unregister a owb use from the current workspace
 -- assume that the workspace is already set by set_workspace call
 procedure unregister_workspace_user(p_user varchar2) as
	PRAGMA AUTONOMOUS_TRANSACTION;

wksp_id number;
userID number;

begin

  --check workspace set
   checkWorkspaceBeSetted();
   if(upper(user)='OWBSYS') then 
         raise_application_error(-20038,'OWBSYS can not unregister workspace users. Try to unregister workspace users by workspace owner');
   end if;


  --check the invoker's privilege
  checkInvokerHasOWBAdminRole();
  --check the p_user valid
  validateUserNameforUnRegister(p_user);

  --need to first lock this user before un-register it,
   select  sys_context('owb_workspace','workspaceID') into wksp_id from dual;
   select elementid into userID from cmpwbuser_v where upper(name) = upper(p_user);




   if(OWB_LOCK.LOCK_USER(wksp_id, userID,OWB_LOCK.EXCLUSIVE_MODE)=false) then
	 raise_application_error(-20036, 'You cannot unregister this user:'||p_user||' at this time since this user is currently connected to this workspace or is edited by some other session.');

   end if;

   --fix bug 5699843: originally we want use the get excluseve lock to prevent revoke a user which is connected to cur wksp, but whcih requires that we place a shared lock on user object, but there is no shared lock on a FCO which is not a folder...
 --so to fix bug 5699843, need to do hte followng :
  if(WBSecurityHelper.isUserConnedToCurrWksp(0,0,wksp_id, upper(p_user)) = 1) then
    	 raise_application_error(-20036, 'You cannot unregister this user:'||p_user||' at this time since this user is currently connected to this workspace or is edited by some other session.');
  end if;

   --then call unregister from the pkg which will detect if there is any owb session on for the p_user
  --fix bug 5622487: if the user p_user has been droped from db, no need to call this procedure...
  if(doesDBUserExist(p_user)) then
    WBSecurityHelper.unRegisterOWBUser(upper(p_user));
    --aug 24,06: need to revoke those privs for target schema only if no other workspace to work on...
    revokeTgtPriv(p_user);
  end if;

  --delete anything owned by the user
  deleteCMPInfoForUser(p_user);

  --after done, release lock
	
  OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.EXCLUSIVE_MODE);


commit;

exception when others then
 --  begin
   -- OWB_LOCK.UNLOCK_USER(wksp_id, userID,OWB_LOCK.EXCLUSIVE_MODE);
   -- exception when others then
	--null; -- dont care...
	
   --end;
   raise;
end unregister_workspace_user;

procedure checkWorkspaceExists(p_ws_name varchar2,
                                         p_owner varchar2)
as
theWorkspaceId number;

begin

 --0th: not allow end user to set GLOBAL_WORKSPACE
  if(upper(p_ws_name) = 'GLOBAL_WORKSPACE' and
     upper(p_owner)  = 'OWBSYS') then

      raise_application_error(-20019,'You are trying to set workspace to GLOBAL_WORKSPACE which is for internal usage.');

  end if;

   begin
  select workspace_id into theWorkspaceId  from workspace_assignment where upper(workspace_name) = upper(p_ws_name) and upper(user_name) = upper(p_owner) and  isWorkspaceOwner = '1';

  exception when NO_DATA_FOUND then
	raise_application_error(-20008,'The workspace with name:'||p_ws_name||' and owner:'||p_owner||' does not exist. You can find out the existing workspace information from view ALL_IV_WORKSPACE or view WORKSPACE_ASSIGNMENT.');
 end;

end checkWorkspaceExists;



procedure checkUserCanAccessTheWorkspace(p_ws_name varchar2,
                                         p_owner varchar2)
as

theWorkspaceId number;
canAccess number:=0;

begin

 select workspace_id into theWorkspaceId  from workspace_assignment where upper(workspace_name) = upper(p_ws_name) and upper(user_name) = upper(p_owner) and  isWorkspaceOwner = '1';




  --2nd: check if the user can access that workspace
  select count(*) into canAccess from workspace_assignment where workspace_id = theWorkspaceId and upper(user_name) = user;

  if(canAccess <1) then
	raise_application_error(-20009,'User '||user||' does not have access to the workspace with name:'||p_ws_name||' and owner:'||p_owner||'. '||user||' must be registered to access the workspace by the administrator or owner of the workspace');
  end if;

exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20007,'When setting the workspace, an error occured:'||SUBSTR(SQLERRM,1,400));

end checkUserCanAccessTheWorkspace;

procedure releaseLock
as
pre_wksp number;	
begin
  --Sept 15,06: need to release shared lock on the previous active workspace if exist; then place shared lock on the to-be-active workspace
  --MyDebug('1.1 releaseLock is called');
  select sys_context('owb_workspace','workspaceID') into pre_wksp from dual;
  --MyDebug('1.2. releaseLock is called on workspceid:'||pre_wksp);

  --if(pre_wksp !='') then
--    begin
	  --MyDebug('1.3. releaseLock really called :OWB_LOCK.UNLOCK_WORKSPACE');

           OWB_LOCK.UNLOCK_WORKSPACE(pre_wksp, OWB_LOCK.SHARED_MODE);
    exception WHEN OTHERS THEN
         -- raise;
          null; -- since unlock_workspace is just been nice to release it earlier so other session can use it...
                -- so if anything happened, we do not bother to inferece with this session....
  --  end;
  --end if;
end releaseLock;
	


function ignoreLockError return number
as
hasRecord number:=0;
ignore number:=0;
result number :=0; 
begin 
  select count(*) into hasRecord from ignore_lockerror_set_workspace; 
  if(hasRecord =1) then 
    select ignore_lockerror into ignore from ignore_lockerror_set_workspace; 
    if(ignore =1) then 
     result :=1;
    end if;

  end if;
  


  return result;

exception when others then 

 null;

end ignoreLockError;

procedure setWorkspaceContext(p_ws_name varchar2 ,p_owner varchar2)
as
theWorkspaceId number;
begin
    releaseLock;
    select workspace_id into theWorkspaceId  from workspace_assignment where upper(workspace_name) = upper(p_ws_name) and upper(user_name) = upper(p_owner) and  isWorkspaceOwner = '1';

      -- now lock this to-be-active workspace
       if(OWB_LOCK.LOCK_WORKSPACE(theWorkspaceId, OWB_LOCK.SHARED_MODE) = false) then


        if(ignoreLockError() !=1) then
        

	 raise_application_error(-20032, 'Setting the workspace to '||p_owner||'.'||p_ws_name||' failed due to another session using this workspace in single user mode.');
        else 
        

         null; -- fix bug 8206958: getAnotherConnection will fail if the main connection is single user mode, so ingore this 
	end if;
       end if;	

    owb_workspace_manager.set_workspace(theWorkspaceId);	

exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20010,'When set workspace, error happened:'||SUBSTR(SQLERRM,1,400));

end setWorkspaceContext;


procedure  ignore_lockerror_setwksp(ignore number) 
as 

PRAGMA AUTONOMOUS_TRANSACTION;
existing number :=0;

begin 
    select count(*) into existing from ignore_lockerror_set_workspace;
    if(	existing > 0) then 
      delete from ignore_lockerror_set_workspace;
    end if;
  

   
   insert into ignore_lockerror_set_workspace (ignore_lockerror ) values(ignore);
   commit;


--exception when others 

-- null;

end ignore_lockerror_setwksp;


  -- unset_workspace
  -- Makes no workspace active in the current session (ie back the default state)
  procedure unset_workspace
  as
  begin
	--MyDebug('1. come to unset_workspace');
    releaseLock;
	--MyDebug('2. after release lock');

    owb_workspace_manager.unset_workspace;
	--MyDebug('3, after internal unset_workspace');
  end unset_workspace;


  -- set_workspace
  -- Sets the current workspace to the one named in workspace_name. Checks
  -- that the user has access to the workspace and sets access flags.
  -- also if user has ACCESS_PUBLICVIEW_BROWSER privilege OWB_DESIGNTIME_VIEW role will be enabled(OWB_USER role will be enabled anyway): sorry we can not enable roles in stored procedure, can only in anonymous sql section
  procedure set_workspace(
    p_ws_name varchar2
  )
  as
    posDot number(2) := 0;
    owner varchar2(32) := null;
    name  varchar2(32) := null;
  begin

  

    -- Is the name workspace name in owner.name format
    posDot:=instr(p_ws_name,'.');
    if (posDot > 1) then
      -- if so, split into name and owner
      owner := substr(p_ws_name,1,posDot-1);
      name := substr(p_ws_name, posDot+1);
    else
      -- otherwise default the owner to the current user
      owner := user;
      name := p_ws_name;
    end if;

    -- now invoke the full procedure
    set_workspace(name, owner);
  end set_workspace;

  procedure set_workspace(
    p_ws_name varchar2,
    p_owner varchar2
  )
  --AUTHID CURRENT_USER: PLS-00157: AUTHID only allowed on schema-level programs
 as

  -- otherRoles varchar2(200):='OWB_USER,';
  begin
        --first needs to unset_workspace fix bug 6340158
	unset_workspace(); -- fix it from 

        --first check whether the workspace exists...
        checkWorkspaceExists(p_ws_name,p_owner);
        
        --add SYS because real time APPLY process runs in SYS
       	if( (user is null) OR (trim(user) is null))  then  -- fix bug 6735804 
         null; 
       elsif(upper(user) != 'OWBSYS' AND upper(user) != 'SYS'  ) then
	checkUserCanAccessTheWorkspace(p_ws_name,p_owner);
	end if;

	setWorkspaceContext(p_ws_name,p_owner);
	--if(hasPublicViewPriv()=1) then
	  --start_access_publicview(otherRoles);
--	else
 	 --start_enable_owbuser_role();
--	end if;

 exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20006,'When set workspace, error happened:'||SUBSTR(SQLERRM,1,400));

  end set_workspace;

  -- get_workspace_id
  -- Returns the id of the current workspace.
  function get_workspace_id return number
  as

  begin
	return owb_workspace_manager.get_workspace();
	
  end get_workspace_id;



  -- get_workspace
  -- Returns the name of the current workspace.
  function get_workspace_name return varchar2
  as

  begin
       return sys_context('owb_workspace','workspaceName');

  end get_workspace_name;

  -- get_workspace_owner
  -- Returns the owner of the current workspace.
  function get_workspace_owner return varchar2
  as

  begin
      return sys_context('owb_workspace','workspace_owner');
  end get_workspace_owner;

  -- internal_has_system_privilege
  -- Returns true if the current user has the specified privilege
  -- on the current workspace
  function internal_has_system_privilege(
    p_privilege_name varchar2,
    theUser varchar2
  ) return boolean
  as

  pos number;
  hasSysFromAnyRole number;
  userHasSysPriv number;
  result boolean:=FALSE;
  theSysId number;
  userId number;

  begin

	-- check the workspace has been set or not
	checkWorkspaceBeSetted();
	
	--check whether the sys priv name is valid
	validateSysPrivName(p_privilege_name );
	--first check if the user is workspace admin,he should have
	if(upper(get_workspace_owner()) = upper(theUser)) then
	  return TRUE;
	end if;
	
       --then check if user himself has teh priv
	select sysPrivId into theSysId from 	SYS_PRIV_ID_TO_NAME_TAB where upper(sysPrivName) = upper(p_privilege_name);
	pos := theSysId+1;

	select count(*) into userHasSysPriv from cmpwbuser_v where upper(name) = upper(theUser) and  SUBSTR(SYSPRIVSACCESSCHARMAP, pos, 1) = '1';



	if(userHasSysPriv >0) then
	  return TRUE;
	end if;

	--finally check whether role has the priv
        select elementid into userId from cmpwbuser_v where upper(name) = upper(theUser);
		
	select count(*) into hasSysFromAnyRole from cmpwbrole_v where elementid in (select  wbrole from  cmproleassignment_V where wbuser= userId ) and SUBSTR(SYSPRIVSACCESSCHARMAP, pos, 1) = '1' ;
	if(hasSysFromAnyRole >0) then
	 return TRUE;
	end if;
	
	return FALSE;
  	

 exception WHEN OTHERS THEN
	raise;
	--raise_application_error(-20010,'When query system privilege:'||p_privilege_name ||' ,error happened:'||SUBSTR(SQLERRM,1,400));


end internal_has_system_privilege;



  -- has_system_privilege
  -- Returns true if the current user has the specified privilege
  -- on the current workspace
  function has_system_privilege(
    p_privilege_name varchar2,
    theUser varchar2 default user
  ) return boolean
  as

    result boolean := FALSE;
    priv_value varchar2(1);

    begin
     	if( (user is null) OR  (trim(user) is null))  then  -- fix bug 6735804 
         return TRUE;
        end if; 

      -- OWBSYS always has privilege on everything
      if (theUser = 'OWBSYS' OR theUser='SYS') then
        return TRUE;
      end if;

      -- check the context variables
      priv_value := sys_context('owb_workspace', p_privilege_name);
      if (priv_value is null or priv_value = '') then
        -- if the context does not contain the privilege, check the
        -- full procedure.
        result := internal_has_system_privilege(p_privilege_name,theUser);
      else
        result := (priv_value = '1');
      end if;
      return result;
    end has_system_privilege;

  -- has_system_privilege_int
  -- Returns true if the current user has the specified privilege
  -- on the current workspace. Required as SQL does not handle booleans
  function has_system_privilege_int(
    p_privilege_name varchar2
  ) return number
  as
  begin
    if (has_system_privilege(p_privilege_name)) then
      return 1;
    end if;
    return 0;
  end has_system_privilege_int;

 -- if seed1stDefWksp is true: which only apply when the userName is registered to this workspace, and need to setup the 1st value of the default workspace.
 -- And if this seed1stDefWksp is true,  this procedure will check if the userName has already setup the 1st default workspace and that workspace still exists, if true this procedure will not do anything; otherwise it will seed the 1st default workspace info (or delete the invalid workspace info and seed a new one)
-- And if this seed1stDefWksp is false: that means: end user wants to change the old default worksapce to something else, so just update that info if exists or insert a new one if not exists already(this later case shoujld not happen ,but no bother to throw an exception)
procedure set_default_workspace(username varchar2, p_ws_owner varchar2, p_ws_name varchar2, seed1stDefWksp boolean  default false) 
as 
seeded number:=0;
existing number:=0;
old_owner varchar2(30);
old_ws varchar2(200); 

begin 
 

       -- deal with 1st time to setup the preferend workspace
	if(seed1stDefWksp = true) then 

              select count(*) into seeded from workspace_preference where workspace_user = username;
              if(seeded =0) then
                  insert into workspace_preference(workspace_owner, workspace_name, workspace_user) values(p_ws_owner, p_ws_name, username);
              else

                  select workspace_name, workspace_owner into old_ws, old_owner from workspace_preference where workspace_user =  username;

                  select count(*) into existing from workspace_assignment  where workspace_name = old_ws and user_name =old_owner and isWorkspaceOwner = '1';

                   if(existing = 0) then 
                          
	                update workspace_preference set workspace_owner = p_ws_owner ,  workspace_name = p_ws_name where workspace_user = username;

                   end if; 
	      end if; 
	return; 
	end if;  

      -- now deal with update case
         select count(*) into existing from workspace_preference where workspace_user =  username;


        if(existing = 0) then 
               insert into workspace_preference(workspace_owner, workspace_name, workspace_user) values(p_ws_owner, p_ws_name, username);

        else 
               
	     update workspace_preference set workspace_owner = p_ws_owner ,  workspace_name = p_ws_name where workspace_user = username;

        end if;


end set_default_workspace;
 



end wb_workspace_management;