set serveroutput on
declare
	cursor c0 (sUti varchar2) is
		select a.owner own,synonym_name from all_synonyms a, all_objects b
	   	where table_owner = sUti and
				b.owner = sUti and
				object_name = synonym_name and
	object_type IN ('TABLE','FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','VIEW');
	cursor c1 is
		select object_name, object_type from user_objects
	   	where object_type in ('TABLE','FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','VIEW') and status = 'VALID'
			order by object_type desc;
	n PLS_INTEGER;
   cursor c2 (sUser varchar2) is 
      select distinct referenced_name, referenced_type from all_dependencies 
         where name in ('PA_RISQUEBAFI','F_ISRUBCODEONFILTRE','F_PLACTIDGRISQUE','F_PLBCEGUICHET','F_PLCRODOUTEUX',
                        'F_PLCROFACPOOL','F_PLCROFACTSOMMEHT','F_PLDOSINTCUTOFF','F_PLDOSMTQP','F_PLDOSQPECFGARANTI',
                        'F_PLECFSCI','F_PLFACBENEFSYNDICATION','F_PLGETBCEEMP','F_PLISEXIGIBLEAVANT','F_PLQPFACTHTREGLE',
                        'F_PLROLEEXTERNE','F_PLSCIACTID','PA_COMMON','PA_ORFIDATE','PA_SELECTCHANTIER','F_PLACTIDGROUPE')
           and referenced_owner = sUser;
	nCountParmorfi   PLS_INTEGER;
	nCountPrev       PLS_INTEGER;
	nCountSPS        PLS_INTEGER;
	nCountSAS        PLS_INTEGER;
	nCountQUETZAL    PLS_INTEGER;
	nCountTFR	 PLS_INTEGER;
	nCountAVANTVENTE PLS_INTEGER;
	sOwner varchar2(30);
	sUser varchar2(30);
	sViewer varchar2(30);
	nVersion7 PLS_INTEGER;
begin
	select user into sOwner from dual;
        if sOwner = 'TRESIMMO' then
		sViewer := 'ORFI_VIEWER';
		sUser := 'ORFI_USER';
	else
		sViewer := sOwner || '_VIEWER';
		sUser := sOwner || '_USER';
	end if;
	select count(username) into nCountParmorfi from all_users where username = 'PARMORFI';
	select count(username) into nCountPrev from all_users where username = 'PREV';
	select count(username) into nCountSPS from all_users where username = 'SPS';
	select count(username) into nCountSAS from all_users where username = 'SAS';
	select count(username) into nCountTFR from all_users where username = 'TFR';
	select count(username) into nCountAVANTVENTE from all_users where username = 'AVANTVENTE';
	if sOwner in ('SYS','SYSTEM') then 
		dbms_output.put_line('Ce script ne doit pas être lancé en tant que SYS ou SYSTEM');
		return; 
	end if;
	if sOwner in ('ADM','AVANTVENTE','COMPTA','PARMORFI','PREV','TFR','SPS','SAS') then 
		dbms_output.put_line('Ce script doit être lancé en tant que TRESIMMO ou equivalent');
		return; 
	end if;
	select count(*) into nVersion7 from v$version where upper(banner) like '%ORACLE7%'; 
	n := dbms_sql.open_cursor;
	for c0rec in c0 (sOwner) loop
		begin
			if c0rec.own = 'PUBLIC' then
				dbms_sql.parse(n, 'drop public synonym ' || c0rec.synonym_name, dbms_sql.native);
			else
				dbms_sql.parse(n, 'drop synonym ' || c0rec.synonym_name, dbms_sql.native);
			end if;
		exception when others then null; end;
	end loop;
	for c1rec in c1 loop
		begin
			dbms_sql.parse(n, 'drop public synonym ' || c1rec.object_name, dbms_sql.native);
		exception when others then null; end;
		begin
		   if c1rec.object_type in ('FUNCTION', 'PACKAGE') then 
				if nVersion7 = 1 then
					dbms_sql.parse(n, 'create public synonym ' || c1rec.object_name || ' for ' || c1rec.object_name, dbms_sql.native);
				end if;	
			end if;
		exception when others then null; end;
		begin
			IF c1rec.object_type = 'TABLE' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to ' || sViewer, dbms_sql.native);
				dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to ' || sUser, dbms_sql.native);
			ELSIF c1rec.object_type = 'VIEW' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to ' || sViewer, dbms_sql.native);
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to ' || sUser, dbms_sql.native);
			ELSIF c1rec.object_type = 'SEQUENCE' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to ' || sUser, dbms_sql.native);
			ELSIF c1rec.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
				dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to ' || sUser, dbms_sql.native);
			END IF;
		exception when others then null; end;
---------------------------------------------------------
--Droits accordés à PARMORFI
---------------------------------------------------------
      if nCountParmorfi = 1 then
			begin
		   	if c1rec.object_type = 'TABLE' THEN
		      	dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to parmorfi', dbms_sql.native);
		   	ELSIF c1rec.object_type = 'VIEW' THEN
		      	dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to parmorfi', dbms_sql.native);
			   ELSIF c1rec.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
			      dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to parmorfi', dbms_sql.native);
		   	END IF;
	      exception when others then null; end;
      end if;
---------------------------------------------------
--Droits accordés à PREV
---------------------------------------------------------
     if nCountPrev = 1 then
			begin
		   	if c1rec.object_type = 'TABLE' THEN
		      	dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to prev', dbms_sql.native);
		   	ELSIF c1rec.object_type = 'VIEW' THEN
		      	dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to prev', dbms_sql.native);
			   ELSIF c1rec.object_type = 'SEQUENCE' THEN
	         	dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to prev', dbms_sql.native);
			   ELSIF c1rec.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
			      dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to prev', dbms_sql.native);
		   	END IF;
	      exception when others then null; end;
     end if;
--------------------------------------------------------
--Droits accordés à AVANTVENTE (ANDROMEDE)
---------------------------------------------------------
		if nCountAVANTVENTE = 1 then
		begin
			IF c1rec.object_type = 'TABLE' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AND_SEL_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AVANTVENTE', dbms_sql.native);
				dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to AND_MOD_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to AVANTVENTE', dbms_sql.native);
			ELSIF c1rec.object_type = 'VIEW' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AND_SEL_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AND_MOD_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AVANTVENTE', dbms_sql.native);
			ELSIF c1rec.object_type = 'SEQUENCE' THEN
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AND_MOD_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to AVANTVENTE', dbms_sql.native);
			ELSIF c1rec.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
				dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to AND_MOD_TOUT', dbms_sql.native);
				dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to AVANTVENTE', dbms_sql.native);
			END IF;
		exception when others then null; end;
		end if;
---------------------------------------------------
--Droits accordés à SAS
---------------------------------------------------------
     if nCountSas = 1 then
			begin
		   	if c1rec.object_type = 'TABLE' THEN
		      	dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c1rec.object_name || ' to sas', dbms_sql.native);
		   	ELSIF c1rec.object_type = 'VIEW' THEN
		      	dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to sas', dbms_sql.native);
			   ELSIF c1rec.object_type = 'SEQUENCE' THEN
	         	dbms_sql.parse(n, 'grant select on ' || c1rec.object_name || ' to sas', dbms_sql.native);
			   ELSIF c1rec.object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
			      dbms_sql.parse(n, 'grant execute on ' || c1rec.object_name || ' to sas', dbms_sql.native);
		   	END IF;
	      exception when others then null; end;
     end if;
	end loop;
	
-------------------------    AUTRE BOUCLE POUR DECLARATION QUETZAL   -----------------------	
---------------------------------------------------
--Droits accordés à SPS (QUETZAL)
---------------------------------------------------------
	if nCountSPS = 1 then
		for c2rec in c2 (sOwner) loop
			begin
				if c2rec.referenced_type in ('TABLE', 'VIEW', 'CLUSTER') then
					dbms_sql.parse(n, 'grant select, delete, update, insert on ' || c2rec.referenced_name || ' to SPS', dbms_sql.native);
				elsif c2rec.referenced_type in ('PACKAGE', 'FUNCTION', 'PROCEDURE') then
					dbms_sql.parse(n, 'grant execute on ' || c2rec.referenced_name || ' to SPS', dbms_sql.native);
				elsif c2rec.referenced_type in ('NON-EXISTENT') then
					dbms_output.put_line('ATTENTION, objet inexistant==> '|| sOwner || '.' || c2rec.referenced_name);
				end if;
			exception when others then null; end;
		end loop;
	end if;
---------------------------------------------------
--Droits accordés à QUETZAL
---------------------------------------------------------
	if nCountQUETZAL = 1 then
		for c2rec in c2 (sOwner) loop
			begin
				if c2rec.referenced_type in ('TABLE', 'VIEW', 'CLUSTER') then
					dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c2rec.referenced_name || ' to QUETZAL', dbms_sql.native);
				elsif c2rec.referenced_type in ('PACKAGE', 'FUNCTION', 'PROCEDURE') then
					dbms_sql.parse(n, 'grant execute on ' || c2rec.referenced_name || ' to QUETZAL', dbms_sql.native);
				elsif c2rec.referenced_type in ('NON-EXISTENT') then
					dbms_output.put_line('ATTENTION, objet inexistant==> '|| sOwner || '.' || c2rec.referenced_name);
				end if;
			exception when others then null; end;
		end loop;
	end if;
------------------------------------------------------------
---------------------------------------------------
--Droits accordés à TFR
---------------------------------------------------------
	if nCountTFR = 1 then
		for c2rec in c2 (sOwner) loop
			begin
				if c2rec.referenced_type in ('TABLE', 'VIEW', 'CLUSTER') then
					dbms_sql.parse(n, 'grant select,delete,update,insert on ' || c2rec.referenced_name || ' to TFR', dbms_sql.native);
				elsif c2rec.referenced_type in ('PACKAGE', 'FUNCTION', 'PROCEDURE') then
					dbms_sql.parse(n, 'grant execute on ' || c2rec.referenced_name || ' to TFR', dbms_sql.native);
				elsif c2rec.referenced_type in ('NON-EXISTENT') then
					dbms_output.put_line('ATTENTION, objet inexistant==> '|| sOwner || '.' || c2rec.referenced_name);
				end if;
			exception when others then null; end;
		end loop;
	end if;
------------------------------------------------------------
	dbms_sql.close_cursor (n);
end;
/