To add/remove a client user to a system-level role in iDempiere ERP by Process

When we need to add or remove System level roles for users from the frontend Client, we may encounter permission restrictions. In this case, we must rely on a Process to assist us.
We create two separate PostgreSQL procedures and assign them to Processes. Finally, we add two button-type columns on the Role window, which allow them to be displayed and used on the toolbar.

CREATE OR REPLACE FUNCTION adempiere.hr_roleadduser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN 
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT 
		pi.record_id, 
		pi.ad_client_id,
		pi.ad_org_id, 
		pi.ad_user_id, 
		pp.parametername,
		pp.p_string, 
		pp.p_number, 
		pp.p_date,
		pp.p_string_TO, 
		pp.p_number_TO, 
		pp.p_date_tO
	FROM 
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE 
		pi.ad_pinstance_id = pinstance
	ORDER BY 
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN 
	p_User_ID := 0; 
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='主要程式開始';

select ad_client_id,ad_org_id
into v_AD_Client_ID ,v_AD_Org_ID
from ad_role where ad_role_id = p_Record_ID;

INSERT INTO adempiere.ad_user_roles(
	ad_user_id, ad_role_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, ad_user_roles_uu)
	VALUES (p_AD_User_ID, p_Record_ID, v_AD_Client_ID, v_AD_Org_ID, 'Y', now(), p_User_ID, now(), p_User_ID, uuid_generate_v4());

v_message :='主要程式結束';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET 
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE 
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END; 

$BODY$;

ALTER FUNCTION adempiere.hr_roleadduser_sp(numeric)
    OWNER TO adempiere;

CREATE OR REPLACE FUNCTION adempiere.hr_roleremoveuser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN 
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT 
		pi.record_id, 
		pi.ad_client_id,
		pi.ad_org_id, 
		pi.ad_user_id, 
		pp.parametername,
		pp.p_string, 
		pp.p_number, 
		pp.p_date,
		pp.p_string_TO, 
		pp.p_number_TO, 
		pp.p_date_tO
	FROM 
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE 
		pi.ad_pinstance_id = pinstance
	ORDER BY 
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN 
	p_User_ID := 0; 
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='主要程式開始';

delete from adempiere.ad_user_roles where ad_role_id = p_Record_ID and ad_user_id = p_AD_User_ID;

v_message :='主要程式結束';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET 
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE 
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END; 

$BODY$;

ALTER FUNCTION adempiere.hr_roleremoveuser_sp(numeric)
    OWNER TO adempiere;
GRANT EXECUTE ON FUNCTION adempiere.hr_roleremoveuser_sp(numeric) TO adempiere;

English Version

CREATE OR REPLACE FUNCTION adempiere.hr_roleadduser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN 
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT 
		pi.record_id, 
		pi.ad_client_id,
		pi.ad_org_id, 
		pi.ad_user_id, 
		pp.parametername,
		pp.p_string, 
		pp.p_number, 
		pp.p_date,
		pp.p_string_TO, 
		pp.p_number_TO, 
		pp.p_date_tO
	FROM 
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE 
		pi.ad_pinstance_id = pinstance
	ORDER BY 
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN 
	p_User_ID := 0; 
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='主要程式開始';

select ad_client_id,ad_org_id
into v_AD_Client_ID ,v_AD_Org_ID
from ad_role where ad_role_id = p_Record_ID;

INSERT INTO adempiere.ad_user_roles(
	ad_user_id, ad_role_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, ad_user_roles_uu)
	VALUES (p_AD_User_ID, p_Record_ID, v_AD_Client_ID, v_AD_Org_ID, 'Y', now(), p_User_ID, now(), p_User_ID, uuid_generate_v4());

v_message :='主要程式結束';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET 
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE 
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END; 

$BODY$;

ALTER FUNCTION adempiere.hr_roleadduser_sp(numeric)
    OWNER TO adempiere;

CREATE OR REPLACE FUNCTION adempiere.hr_roleremoveuser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN 
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT 
		pi.record_id, 
		pi.ad_client_id,
		pi.ad_org_id, 
		pi.ad_user_id, 
		pp.parametername,
		pp.p_string, 
		pp.p_number, 
		pp.p_date,
		pp.p_string_TO, 
		pp.p_number_TO, 
		pp.p_date_tO
	FROM 
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE 
		pi.ad_pinstance_id = pinstance
	ORDER BY 
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN 
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN 
	p_User_ID := 0; 
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='主要程式開始';

delete from adempiere.ad_user_roles where ad_role_id = p_Record_ID and ad_user_id = p_AD_User_ID;

v_message :='主要程式結束';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET 
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE 
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END; 

$BODY$;

ALTER FUNCTION adempiere.hr_roleremoveuser_sp(numeric)
    OWNER TO adempiere;
GRANT EXECUTE ON FUNCTION adempiere.hr_roleremoveuser_sp(numeric) TO adempiere;

日本語版

フロントエンドクライアントからユーザーにシステムレベルのロールを追加または削除する必要がある場合、権限の制限に遭遇することがあります。この場合、プロセスを使用して支援する必要があります。
2つのPostgreSQLプロシージャを作成し、それぞれをプロセスに割り当てます。最後に、ロールウィンドウにボタンタイプのカラムを2つ追加し、ツールバーに表示して使用できるようにします。

CREATE OR REPLACE FUNCTION adempiere.hr_roleadduser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT
		pi.record_id,
		pi.ad_client_id,
		pi.ad_org_id,
		pi.ad_user_id,
		pp.parametername,
		pp.p_string,
		pp.p_number,
		pp.p_date,
		pp.p_string_TO,
		pp.p_number_TO,
		pp.p_date_tO
	FROM
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE
		pi.ad_pinstance_id = pinstance
	ORDER BY
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN
	p_User_ID := 0;
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='メインプロセス開始';

select ad_client_id,ad_org_id
into v_AD_Client_ID ,v_AD_Org_ID
from ad_role where ad_role_id = p_Record_ID;

INSERT INTO adempiere.ad_user_roles(
	ad_user_id, ad_role_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, ad_user_roles_uu)
	VALUES (p_AD_User_ID, p_Record_ID, v_AD_Client_ID, v_AD_Org_ID, 'Y', now(), p_User_ID, now(), p_User_ID, uuid_generate_v4());

v_message :='メインプロセス終了';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END;

$BODY$;

ALTER FUNCTION adempiere.hr_roleadduser_sp(numeric)
    OWNER TO adempiere;

CREATE OR REPLACE FUNCTION adempiere.hr_roleremoveuser_sp(
	pinstance numeric)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
v_message VARCHAR (400) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN
	pinstance := 0;
END IF;

v_message :=' process start';

BEGIN
	ResultStr := 'PInstanceNotFound';
	UPDATE adempiere.ad_pinstance
	SET created = SYSDATE,
	isprocessing = 'Y',
	reslut = 0
	WHERE ad_pinstance_id = pinstance_id;
	COMMIT;
EXCEPTION
	WHEN OTHERS THEN NULL;

END;

v_message :='parameter ';

FOR p IN (
	SELECT
		pi.record_id,
		pi.ad_client_id,
		pi.ad_org_id,
		pi.ad_user_id,
		pp.parametername,
		pp.p_string,
		pp.p_number,
		pp.p_date,
		pp.p_string_TO,
		pp.p_number_TO,
		pp.p_date_tO
	FROM
		adempiere.ad_pinstance pi
		LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
	WHERE
		pi.ad_pinstance_id = pinstance
	ORDER BY
		pp.SeqNo
)
LOOP
	p_Record_ID := p.record_id;
	p_User_ID := p.AD_User_id;
	p_AD_Client_ID := p.AD_Client_ID;
	IF p.parametername = 'AD_User_ID' THEN
		p_AD_User_ID = p.p_number;
	ELSIF p.parametername = 'AD_User_ID' THEN
		p_AD_User_ID = p.p_number;
	END IF;
END LOOP;


IF p_User_ID IS NULL THEN
	p_User_ID := 0;
END IF;

IF pinstance = 1000000 THEN
END IF;
v_message :='メインプロセス開始';

delete from adempiere.ad_user_roles where ad_role_id = p_Record_ID and ad_user_id = p_AD_User_ID;

v_message :='メインプロセス終了';
IF pinstance > 0 THEN
	BEGIN
		UPDATE adempiere.ad_pinstance
		SET
			updated = now(),
			isprocessing = 'N',
			result = 1,
			errormsg = v_message
		WHERE
			ad_pinstance_id = pinstance;
		-- COMMIT;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
END IF;


EXCEPTION

WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END;

$BODY$;

ALTER FUNCTION adempiere.hr_roleremoveuser_sp(numeric)
    OWNER TO adempiere;
GRANT EXECUTE ON FUNCTION adempiere.hr_roleremoveuser_sp(numeric) TO adempiere;