Queries: AD

Synchronize Translations

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 = 'Import Legacy Data';

Windows of 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 tablename='ZZ_PackingList');

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('Import Permits') and fld.isdisplayed = 'Y' -- and --upper(col.columnname) = upper('zz_docowner_id') upper(fld.name) like upper('Submitted To DCAC') --upper(tabtable.tablename) = upper('ZZ_EUU') 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('Import Permits') and fld.isdisplayed = 'Y' and --upper(col.columnname) = upper('column_name') and --upper(fld.name) like upper('User column name') --upper(tabtable.tablename) = upper('zz_packinglist') and win.name in ( 'Application' ) --and --tab.seqno = 10 order by win.name, tab.seqno, fld.seqno, col.columnname ;

table

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

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

columns

Pick columns by name 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='AD_User')) order by a.name;

select a.* 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)

select a.* 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='ZZ_Permit') t) u

select a.* 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='ZZ_Permit');

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)

window

select * from ad_window a where a.ad_window_id=1000090

tab

select * from ad_tab a where a.ad_window_id=1000090

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=1000090

report and process

select * from ad_process order by created desc;

select * into bk_ad_field_20140327_08h19 from ad_field ;

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='C_BPartner.xml' where ad_process_para_id = 1000000;

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

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

All fields on tab

delete from ad_field where ad_tab_id in ( select ad_tab_id from ad_tab where name = 'Packing List' 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 = 'AD_Field';

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

Doctype

select c_doctype_id from c_doctype where name = 'Temporary Import Permit'

Delete all attachments of process

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