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;