Queries: AD

Client

select ad_client_id,value,name from ad_client order by ad_client_id;

select ad_client_id from ad_client where name like 'A%';

Windows

Windows referencing a specific process

select name from ad_window where ad_window_id in (
select ad_window_id from ad_tab where ad_process_id in (
select ad_process_id from ad_process where value = 'processname')
);

Window

select * from ad_window a
where a.ad_window_id=id

Tab

select * from ad_tab a
where a.ad_window_id=id

select distinct a.whereclause from ad_tab a

select b.name as WindowName from ad_tab a
join ad_window b on a.ad_window_id = b.ad_window_id
where 
	a.ad_table_id = 
	(select ad_table_id from ad_table where name='tablename');

Field information

Field

select a.* from ad_field a
join ad_tab b on a.ad_tab_id=b.ad_tab_id
where b.ad_window_id=id

Combining Window, tab, field

As a view

drop view if exists zz_fieldinfo_v;

create view zz_fieldinfo_v as
select 
	tabtable.tablename as table_name, 
	tabtable.entitytype as table_entitytype,
  	fld.isdisplayed as field_isdisplayed,
  	fld.seqno as field_seqno,
  	fld.name as field_name, 
	col.columnname as column_name,
  	fld.displaylogic as field_display_logic,
	fld.readonlylogic as field_readonly_logic,
	tab.name as tab_name,
	win.name as WindowName, 
	win.ad_window_id,
	tab.ad_tab_id,
  	tab.isactive as tab_isactive,
  	fld.ad_field_uu,
	fld.ad_field_id,
	fld.iscentrallymaintained as field_centrallymaintained,
	fld.ismandatory as field_ismandatory,
	fld.mandatorylogic as field_mandatorylogic,
	col.ad_element_id,
	ele.name as element_name,
	ref.name as ref_name, refvalue.name as ref_value,
	refvalue.ad_reference_id,
	reftabletbl.tablename as ref_table,
	col.created as column_created, col.updated as column_updated,
	fld.created as field_created, fld.updated as field_updated,
	fld.ad_val_rule_id as field_val_rule_id
from 
	ad_field fld
join ad_tab tab on fld.ad_tab_id = tab.ad_tab_id
join ad_table tabtable on tabtable.ad_table_id = tab.ad_table_id
join ad_window win on tab.ad_window_id = win.ad_window_id
join ad_column col on fld.ad_column_id = col.ad_column_id
join ad_element ele on ele.ad_element_id = col.ad_element_id
left join ad_reference ref on col.ad_reference_id = ref.ad_reference_id
left join ad_reference refvalue on col.ad_reference_value_id = refvalue.ad_reference_id
left join ad_ref_table reftable on reftable.ad_reference_id = refvalue.ad_reference_id
left join ad_table reftabletbl on reftabletbl.ad_table_id = reftable.ad_table_id
order by win.name, tab.seqno, field_seqno;

select field_display_name, field_ismandatory, field_mandatorylogic from zz_fieldinfo_v where windowname like  'C%' and field_mandatorylogic is not null;

Show fields by update date (useful to run after loading 2pack)

select column_updated, column_name, * from zz_fieldinfo_v order by column_updated desc;

Form

select a.entitytype, a.name, a.classname from ad_form a where a.entitytype='U' order by classname;

Table

Table

select a.* from ad_table a
order by a.created desc;

select a.* from ad_table a
where a.ad_table_id in (id, id)

(select ad_table_id from ad_table where tablename='tablename')

delete from ad_table where ad_table_id in (select ad_table_id from ad_table where tablename='tablename');

Table Columns

select a.name, a.columnname, a.created from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in ((select ad_table_id from ad_table where tablename='tablename'))
order by a.name;
--order by a.created desc;

Menu

All the main (root) menu items in a tree

SELECT m.name, mm.* FROM AD_TreeNodeMM mm
left join ad_menu m on mm.node_id = m.ad_menu_id
WHERE mm.AD_Tree_ID=1000171 and mm.parent_id=0;

Update entity type

update ad_column set entitytype='U' where ad_column_id=3510;

Callout

select a.ad_column_id,tbl.tablename, a.columnname,a.name, a.callout from ad_column a
join ad_table tbl on a.ad_table_id = tbl.ad_table_id
where a.callout is not null and a.callout like '%bPartner%'
--and tbl.tablename = 'C_BankStatementLine'
order by tbl.tablename, a.callout, a.name;
--order by a.updated desc;

Update callout

update ad_column set callout = 'org.compiere.model.CalloutInvoiceNew.bPartner' where ad_column_id = 3499;

References

select * from ad_ref_list where ad_reference_id in ( select ad_reference_id from ad_reference where name = 'Ref Name' );
-
Incomplete:
left join
	ad_reference ref on ref.name = 'ZZ_Status'
left join
	ad_ref_list reflist on reflist.ad_reference_id = ref.ad_reference_id and reflist.value = perm.zz_status

For use in Jasper Report to lookup reference name

( SELECT rl.name FROM ad_ref_list rl WHERE ((rl.ad_reference_id = (select ad_reference_id from ad_reference where name = 'my ref name')::numeric) AND ((rl.value)::text = (v.ice_fuel_type)::text))) AS field_name

Validation Rules

select ad_val_rule_id, name, code from ad_val_rule where upper(code) like upper('%quota%');

Elements

select b.* from ad_column a
join ad_element b on a.ad_element_id=b.ad_element_id
where a.ad_table_id in (1000153, 1000152, 1000154, 1000156)

Report and process

select * from ad_process order by created desc;


select ad_process_id, value, name,description, classname, jasperreport from ad_process where upper(value) like upper('%') order by value;

select * from ad_process_para where ad_process_para_id = 1000000;

select columnname, name, defaultvalue from ad_process_para where ad_process_id = 1000000 order by seqno;

update ad_process_para set defaultvalue='1' where ad_process_id = 1000000 and columnname = 'columname';	--Sequence

Deletion

Delete all fields on window

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
	select ad_window_id from ad_window where name like 'Name of window' 
) );

Delete all fields on tab

1. 

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'tabname' and ad_window_id in 
(
	select ad_window_id from ad_window where name = 'Name of window' 
) );

2.

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' ) 
) );

3.

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where ad_tab_uu in ( 'REPLACEWITHWINDOWUUHERE' ) );

Delete all occurences of column from windows

delete from ad_field where ad_column_id in 
( select ad_column_id from ad_column where upper(columnname) like upper('zz_nbsm_matchsetup_id') )

Delete index definitions on table

• NB: Indexes not dropped automatically

delete from ad_indexcolumn where AD_TableIndex_ID in (select ad_tableindex_id from ad_tableindex where ad_table_id = 318);

delete from AD_TableIndex where ad_table_id = 318;

Sequences

update ad_sequence a set CURRENTNEXTSYS =13000  where a.name = 'name';

Doctype

select c_doctype_id from c_doctype where name = 'DocumentName'

Attachments

All pack-in attachments

select * from ad_attachment where ad_table_id = 50008 and record_id in (
  select AD_Package_Imp_Proc_id from AD_Package_Imp_Proc where name like '%%' 
  );

Synchronize Translations

An example:

update ad_process_trl a set name = b.name, description = b.name from ad_process b where a.ad_process_id = b.ad_process_id and b.name = 'Process Name';

Translations

Various helpers (work in progress)

select ad_message_id, value, msgtext, msgtip from ad_message where upper(msgtext) like upper('%login%') order by msgtext;

drop view if exists zz_trl_helper_field_v;

create or replace view zz_trl_helper_field_v  as 
select 
	main.ad_field_id, 
	trl.ad_language, trl.ad_field_trl_uu, 
	main.ad_field_uu, main.name as field_name, main.description as main_description, 
	trl.name as trl_name, trl.description as trl_description, 
	win.name as window_name, 
	col.columnname
from
ad_field main
join ad_field_trl trl on trl.ad_field_id = main.ad_field_id
join ad_column col on col.ad_column_id = main.ad_column_id
join ad_tab tab on tab.ad_tab_id = main.ad_tab_id
join ad_window win on win.ad_window_id = tab.ad_window_id;


select * from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = '';

update ad_field_trl set name = 'name', description = 'name' where ad_field_trl_uu in (
	select ad_field_trl_uu from zz_trl_helper_field_v where ad_language in ( '' ) and columnname = ''
	);

select main.ad_process_id, trl.ad_language, trl.ad_process_para_trl_uu, main.name, main.description, trl.name, trl.description from
ad_process_para main
join ad_process_para_trl trl on trl.ad_process_para_id = main.ad_process_para_id
where 
	ad_process_id in ( select ad_process_id from ad_process where name like '' );

select main.ad_column_id, trl.ad_language, trl.ad_column_trl_uu, main.columnname, main.name, main.description, trl.name from
ad_column main
join ad_column_trl trl on trl.ad_column_id = main.ad_column_id
where 
	trl.ad_language in ( 'en_ZA' ) and
	main.ad_column_id in ( select ad_column_id from ad_column where columnname like '' );
	
select main.ad_element_id, trl.ad_language, trl.ad_element_trl_uu, main.columnname, main.name, main.description, trl.name, trl.description from
ad_element main
join ad_element_trl trl on trl.ad_element_id = main.ad_element_id
where 
	trl.ad_language in ( 'en_ZA' ) and
	main.ad_element_id in ( select ad_element_id from ad_element where columnname like '' );

select main.ad_menu_id, main.ad_menu_uu, trl.ad_language, trl.ad_menu_trl_uu, main.name, main.description, trl.name, trl.description from
ad_menu main
join ad_menu_trl trl on trl.ad_menu_id = main.ad_menu_id
where 
	trl.ad_language in ( 'en_ZA' ) and
	main.ad_menu_id in ( select ad_menu_id from ad_menu where name like '' );

-- For 2pack (sync UUID's)
update ad_menu_trl 
set ad_menu_trl_uu = '521bf63c-c0ab-40ac-a188-fe7cfd0a9612'
from ad_menu b
where 
ad_menu_trl.ad_menu_id = b.ad_menu_id
and b.ad_menu_uu = 'ccf0fc37-76cc-4a3c-8e1c-80d4c28e695a';

Pack out

Backup AD information before running 2pack

select * into bk_rq16_process from ad_process;

select * into bk_rq16_table from ad_table;

select * into bk_rq16_column from ad_column;

select * into bk_rq16_window from ad_window;

select * into bk_rq16_tab from ad_tab;

select * into bk_rq16_field from ad_field;

select * into bk_rq16_reference from ad_reference;

select * into bk_rq16_ref_table from ad_ref_table;

select * into bk_rq16_ref_list from ad_ref_list;

Delete all fields + tabs on windows (for Pack In)

• Helps to ensure is packed in correctly

delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
select ad_window_id from ad_window where name in ( 'nameOfWindow' ) 
));

) );

• See also under the heading: 'Deletion'

Packout/Packin package details

select 
	hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from 
	AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;
order by updated desc;

select 
	updated, hdr.name, hdr.description
from 
	AD_Package_Exp hdr
order by updated desc;

select 
	updated, hdr.name, hdr.description
from 
	AD_Package_Imp hdr
order by updated desc;

-- For searching for tables linked to packouts

select 
	hdr.name, det.created, det.updated, det.ad_package_exp_id, det.AD_Package_Exp_Detail_id, det.line, det.description, det.dbtype, det.sqlstatement, det.ad_table_id, tbl.tablename
from 
	AD_Package_Exp_Detail det
join AD_Package_Exp hdr on hdr.ad_package_exp_id = det.ad_package_exp_id
left join ad_table tbl on tbl.ad_table_id = det.ad_table_id;

Duplicate key error when importing AD_Message

• unique constraint (...AD_MESSAGE_TRL_KEY) violated

delete from ad_message_trl
WHERE AD_Message_ID=
( SELECT ad_message_id FROM ad_message WHERE value='KEYOFMESSAGE' );

Pack in/out helpers

Delete all fields on a window/tab

delete from ad_tab where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE' ) 
) );

Delete all ad_userquery of window/tab process

delete from ad_userquery where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE') 
));

Delete all ad_customization of window/tab

delete from ad_tab_customization where ad_tab_id in ( select ad_tab_id from ad_tab where ad_window_id in 
(
select ad_window_id from ad_window where ad_window_uu in ( 'REPLACEWITHWINDOWUUHERE') 
));

Delete all attachments of process

delete from ad_attachment where record_id in (
	select ad_process_id from ad_process where value like '%MYPROCESSKEY%' ) and 
ad_table_id = (select ad_table_id from ad_table where tablename='AD_Process');

Migration scripts

Which migration scripts have been run

select releaseno,created,name,status,isapply,filename, script  from ad_migrationscript order by releaseno, name;

Session

Sessions run during the actual year

SELECT
	c.name AS clientname,
	s.created,
	u.name AS username,
	s.remote_addr,
	s.processed,
	CASE
		WHEN s.processed = 'Y' THEN s.updated-s.created
		ELSE NULL
	END AS duration,
	r.name AS rolename
FROM ad_session s
     JOIN ad_client c ON (s.ad_client_id = c.ad_client_id)
     JOIN ad_user u ON (u.ad_user_id = s.createdby)
     JOIN ad_role r ON (r.ad_role_id = s.ad_role_id)
WHERE 	s.created>date_trunc('year', now())
ORDER BY s.ad_session_id DESC

This page is brought to you by nTier Software Services. Feel free to improve directly or suggest using the Discussion tab.