Queries: AD

Client

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

Windows

Tabs

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');

Combining Window, tab, field

select 
	win.name as WindowName, 
	tab.name as TabName,
	tabtable.tablename as TabTable, 
	fld.name as field_display_name, 
	fld.displaylogic as field_display_logic,
	fld.readonlylogic as field_readonly_logic,
	col.columnname as column_name,
	ref.name as ref_name, refvalue.name as ref_value,
	refvalue.ad_reference_id,
	reftabletbl.tablename as ref_table
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
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
where 
	--upper(win.name) like upper('Window Name') and
	fld.isdisplayed = 'Y' -- and
	--upper(col.columnname) = upper('columnname')
	upper(fld.name) like upper('Field Name')
	--upper(tabtable.tablename) = upper('tablename')
order by win.name, tab.seqno, col.columnname
	;

Shorter field info

select 
	--win.name as WindowName, 
	tab.name as TabName,
	tabtable.tablename as TabTable, 
	fld.seqno,
	fld.name as field_display_name, 
	--fld.displaylogic as field_display_logic,
	--fld.readonlylogic as field_readonly_logic,
	col.columnname as column_name
	--ref.name as ref_name, refvalue.name as ref_value,
	--refvalue.ad_reference_id,
	--reftabletbl.tablename as ref_table
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
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
where 
	--upper(win.name) like upper('Window Name') and
	fld.isdisplayed = 'Y'  and
	--upper(col.columnname) = upper('column_name') and
	--upper(fld.name) like upper('User column name')
	--upper(tabtable.tablename) = upper('tablename') and
	win.name in ( 'Window Name' ) --and 
	--tab.seqno = 10
order by win.name, tab.seqno, fld.seqno, col.columnname
	;

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

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

Table

Table

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

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

Table Columns

select a.name, a.columnname 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;

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%'
order by tbl.tablename, a.callout, a.name;

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

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 * 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

Delete fields from window

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

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' 
) );

Sequences

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

Doctype

select c_doctype_id from c_doctype where name = 'DocumentName'

Delete all attachments of process

delete from ad_attachment where record_id in (
	select ad_process_id from ad_process where value like 'processname%' ) ...

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';