Skip to content

Ninniku IT Hub

Provide open-source solutions for businesses.

Menu
  • Home
  • Technical Support
  • Traning
    • Arduino for IoT and ERP Integration
  • Expert Certification
  • Plug-In Download
Menu

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

Posted on 2023-03-27

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.

Two function buttons 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;

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Apple
  • iDempiere
  • IoT
  • IT Tools
  • Linux
  • SAP
  • uncategorized
  • Wordpress

Tags

Access Arduino Backup Bad Dept Business One Button Confirmation CRM Developer Document ERP Exam Financial GPIO iDempiere Jasper Report Java JavaFX Just-In-Time Language LED Linux M1 MacOS Material Receipt Maven Model mvn Period Control Permission PostgreSQL Potentiometer Process Profit Center PWM Raspberry PI repository Resistor Role Sales Management Swing Tips Ubuntu Wordpress Workflow

Recent Posts

  • Comparing Java GUI Frameworks: Pros and Cons
  • Implement the void function for Depreciation documents in iDempiere ERP.
  • What is the difference between Docker and VM?
  • Finding Data in SAP Business One: How to Locate Relevant Tables and Columns for Documents
  • How to Handle Bad Debts in iDempiere ERP: A Step-by-Step Guide for Writing Off Receivables and Maintaining Accurate Financial Records

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©2023 Ninniku IT Hub | Design: Newspaperly WordPress Theme