ad_role_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER AD_Role_Trg
AFTER INSERT OR UPDATE OF UserLevel
	ON AD_Role
FOR EACH ROW
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: AD_Role_Trg.sql,v 1.3 2003/05/04 06:46:07 jjanke Exp $
 ***
 * Title:	Role Setup
 * Description:
 *	- Create Role_OrgAccess & User_Roles
 *	- Insert Access for Role
 ************************************************************************/
BEGIN

	IF (INSERTING) THEN
	--	Client / OrgAccess
		/**	Recursive Trigger
		INSERT INTO AD_Role_OrgAccess
			(AD_Role_ID, AD_Client_ID, AD_Org_ID, 
			IsActive, Created, CreatedBy, Updated, UpdatedBy)
		VALUES
			(:new.AD_Role_ID, :new.AD_Client_ID, :new.AD_Org_ID, 
			'Y', SysDate, :new.CreatedBy, SysDate, :new.UpdatedBy);
		**/
		
		IF (:new.UpdatedBy <> 0) THEN
			--	User_Roles for Dialog entry
			INSERT INTO AD_User_Roles
				(AD_User_ID, AD_Role_ID, AD_Client_ID, AD_Org_ID,
				IsActive, Created, CreatedBy, Updated, UpdatedBy)
			VALUES
				(:new.UpdatedBy, :new.AD_Role_ID, :new.AD_Client_ID, :new.AD_Org_ID, 
				'Y', SysDate, :new.CreatedBy, SysDate, :new.UpdatedBy);
		END IF;
	ELSE
		--	Window
		DELETE AD_Window_Access
		WHERE AD_Role_ID = :new.AD_Role_ID;
		--	Process
		DELETE AD_Process_Access
		WHERE AD_Role_ID = :new.AD_Role_ID;
		--	Form
		DELETE AD_Form_Access
		WHERE AD_Role_ID = :new.AD_Role_ID;
		--	WorkFlow
		DELETE AD_WorkFlow_Access
		WHERE AD_Role_ID = :new.AD_Role_ID;
	END IF;
	
	/**
	 *	Fill AD_Window_Access + AD_Process_Access
	 *	---------------------------------------------------------------------------
	 *	SCO# Levels			S__ 100		4	System info
	 *						SCO	111		7	System shared info
	 *						SC_ 110		6	System/Client info
	 *						_CO	011		3	Client shared info
	 *						__O	001		1	Organization info
	 *	Roles:
	 *		S		4,7,6
	 *		_CO		7,6,3,1
	 *		__O		3,1,7
	 */
	--	System
	IF (:new.UserLevel='S') THEN
		--	Window
		INSERT INTO AD_Window_Access
			(AD_Window_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT w.AD_Window_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM	AD_Window w, AD_Tab t, AD_Table tt
		WHERE	w.AD_Window_ID=t.AD_Window_ID
		  AND	t.AD_Table_ID=tt.AD_Table_ID
		  AND	tt.AccessLevel IN ('4','7','6');
		--	Process
		INSERT INTO AD_Process_Access
			(AD_Process_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT p.AD_Process_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Process p
		WHERE	AccessLevel IN ('4','7','6');
		--	Form
		INSERT INTO AD_Form_Access
			(AD_Form_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT f.AD_Form_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Form f
		WHERE	AccessLevel IN ('4','7','6');
		--	Workflow
		INSERT INTO AD_WorkFlow_Access
			(AD_WorkFlow_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT w.AD_WorkFlow_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_WorkFlow w
		WHERE	AccessLevel IN ('4','7','6');

	--	Client/Org
	ELSIF (:new.UserLevel=' CO' OR :new.UserLevel=' C') THEN
		--	Window
		INSERT INTO AD_Window_Access
		(AD_Window_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT w.AD_Window_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM	AD_Window w, AD_Tab t, AD_Table tt
		WHERE	w.AD_Window_ID=t.AD_Window_ID
		  AND	t.AD_Table_ID=tt.AD_Table_ID
		  AND	tt.AccessLevel IN ('7','6','3','1');
		--	Process
		INSERT INTO AD_Process_Access
			(AD_Process_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT p.AD_Process_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Process p
		WHERE	AccessLevel IN ('7','6','3','1');
		--	Form
		INSERT INTO AD_Form_Access
			(AD_Form_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT f.AD_Form_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Form f
		WHERE	AccessLevel IN ('7','6','3','1');
		--	Workflow
		INSERT INTO AD_WorkFlow_Access
			(AD_WorkFlow_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT w.AD_WorkFlow_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_WorkFlow w
		WHERE	AccessLevel IN ('7','6','3','1');

	--	Organization
	ELSIF (:new.UserLevel='  O') THEN
		--	Window
		INSERT INTO AD_Window_Access
			(AD_Window_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT w.AD_Window_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM	AD_Window w, AD_Tab t, AD_Table tt
		WHERE	w.AD_Window_ID=t.AD_Window_ID
		  AND	t.AD_Table_ID=tt.AD_Table_ID
		  AND	tt.AccessLevel IN ('3','1','7');
		--	Process
		INSERT INTO AD_Process_Access
			(AD_Process_ID, AD_Role_ID,
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT DISTINCT p.AD_Process_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Process p
		WHERE	AccessLevel IN ('3','1','7');
		--	Form
		INSERT INTO AD_Form_Access
			(AD_Form_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT f.AD_Form_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_Form f
		WHERE	AccessLevel IN ('3','1','7');
		--	Workflow
		INSERT INTO AD_WorkFlow_Access
			(AD_WorkFlow_ID, AD_Role_ID, 
			AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsReadWrite)
		SELECT w.AD_WorkFlow_ID, :new.AD_Role_ID,
			:new.AD_CLIENT_ID, :new.AD_ORG_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy, 'Y'
		FROM AD_WorkFlow w
		WHERE	AccessLevel IN ('3','1','7');

	END IF;

END AD_Role_Trg;
/