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

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;

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.

Queries: AD

Client

Windows

Windows referencing a specific process

Window

Tab

Field information

Field

Combining Window, tab, field

As a view

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

Form

Table

Table

Table Columns

Menu

All the main (root) menu items in a tree

Update entity type

Callout

Update callout

References

For use in Jasper Report to lookup reference name

Validation Rules

Elements

Report and process

Deletion

Delete all fields on window

Delete all fields on tab

Delete all occurences of column from windows

Delete index definitions on table

Sequences

Doctype

Attachments

All pack-in attachments

Synchronize Translations

Translations

Various helpers (work in progress)

Pack out

Backup AD information before running 2pack

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

Packout/Packin package details

Duplicate key error when importing AD_Message

Pack in/out helpers

Delete all fields on a window/tab

Delete all ad_userquery of window/tab process

Delete all ad_customization of window/tab

Delete all attachments of process

Migration scripts

Which migration scripts have been run

Session

Sessions run during the actual year