Pessoal,
Seguem dois scripts de exemplo para apagar Client e/ou Org em um sistema ADempiere utilizando PostgreSQL.
Estes scripts foram feitos com base em contribuições do Peter Shen e do Fernando Lucktemberg.
Um abraço,
Eduardo Montenegro.
[code:e8ee7]
CREATE OR REPLACE FUNCTION adempiere.remove_client () RETURNS int4 AS
$BODY$
DECLARE
/**
* Please change this one to any client id you want to delete
**/
v_client_id integer := 11; – 11 = GardenWorld
tmp RECORD;
msg varchar;
BEGIN
–RAISE NOTICE ‘‘Delete Client Where AD_Client_ID=%’’,v_client_id;
/****************************************************************
* Disable all triggers and constraints one by one
****************************************************************/
--RAISE NOTICE ''Disable triggers & constraints'';
/* Disable PostgreSQL Constraints. Check the correct NameSpace */
update pg_trigger set tgenabled = false where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
--RAISE NOTICE ''Remove all the records that belongs to the client'';
/****************************************************************
* Remove all the records belongs to that client
****************************************************************/
FOR tmp IN
select * from ad_table t where isview = 'N'
and ad_table_id in (select ad_table_id from ad_column where columnname = 'AD_Client_ID')
order by tablename
LOOP
--raise notice ''Removing items from table - %'', tmp.tablename;
--BEGIN
EXECUTE 'DELETE FROM ' || tmp.tablename
|| ' WHERE AD_Client_ID = ' || v_client_id;
--COMMIT;
END LOOP;
/****************************************************************
* Enable all triggers and constraints one by one
****************************************************************/
--RAISE NOTICE ''Enable triggers & constraints'';
/* Enable PostgreSQL Constraints. Check the correct NameSpace */
update pg_trigger set tgenabled = false where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 527568
and tr.tgrelid = cl.oid);
–RAISE NOTICE ‘‘Finished removing’’;
RETURN 1;
END;
$BODY$
LANGUAGE ‘plpgsql’
;
[/code:e8ee7]
[code:e8ee7]
CREATE OR REPLACE FUNCTION adempiere.remove_org () RETURNS int4 AS
$BODY$
DECLARE
/**
* Please change this one to any client id you want to delete
**/
v_org_id integer := 1000000; – 12 = Store
tmp RECORD;
msg varchar;
BEGIN
–RAISE NOTICE ‘‘Delete Client Where AD_Client_ID=%’’,v_client_id;
/****************************************************************
* Disable all triggers and constraints one by one
****************************************************************/
--RAISE NOTICE ''Disable triggers & constraints'';
/* Disable PostgreSQL Constraints. Check the correct NameSpace */
update pg_trigger set tgenabled = false where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 90488
and tr.tgrelid = cl.oid);
--RAISE NOTICE ''Remove all the records that belongs to the client'';
/****************************************************************
* Remove all the records belongs to that client
****************************************************************/
FOR tmp IN
select * from ad_table t where isview = 'N'
and ad_table_id in (select ad_table_id from ad_column where columnname = 'AD_Org_ID')
order by tablename
LOOP
--raise notice ''Removing items from table - %'', tmp.tablename;
--BEGIN
EXECUTE 'DELETE FROM ' || tmp.tablename
|| ' WHERE AD_Org_ID = ' || v_org_id;
--COMMIT;
END LOOP;
/****************************************************************
* Enable all triggers and constraints one by one
****************************************************************/
--RAISE NOTICE ''Enable triggers & constraints'';
/* Enable PostgreSQL Constraints. Check the correct NameSpace */
update pg_trigger set tgenabled = false where oid in (
select tr.oid from pg_class cl, pg_trigger tr
where cl.relnamespace = 90488
and tr.tgrelid = cl.oid);
–RAISE NOTICE ‘‘Finished removing’’;
RETURN 1;
END;
$BODY$
LANGUAGE ‘plpgsql’
;
[/code:e8ee7]
