drop table item_type_structure
/

create table item_type_structure(
  apex_item_type   varchar2(200),
  component_type   varchar2(200)
)
/

insert into item_type_structure values ('Text Field', 'SIMPLE_TEXT');
insert into item_type_structure values ('Date Picker (DD/MM/YYYY)', 'SIMPLE_TEXT');
insert into item_type_structure values ('Select List', 'LOV_BASED');
insert into item_type_structure values ('Select List With Submit', 'LOV_BASED');
insert into item_type_structure values ('Hidden', 'SIMPLE_VARIABLE');


create or replace view apex_app_structure as
/* 
 * Ebene 0: Applikationen 
 */
select 
  application_id            app_id,
  'APPLICATION'             comp_id, 
  to_char(application_id)   comp_name,
  null                      parent_comp_id, 
  'APPLICATION'             comp_type,
  null                      comp_subtype,
  null                      comp_source
from apex_applications union all (
/* 
 * Ebene 1: a) Seiten
 */
 select 
   p.application_id         app_id,
   'PAGE_' || page_id       comp_id,
   to_char(page_id)         comp_name,
   'APPLICATION'            parent_comp_id,
   'PAGE'                   comp_type,
   null                     comp_subtype,
   null                     comp_source
 from apex_application_pages p, apex_applications a 
 where p.application_id = a.application_id
) union all (
/* 
 * Ebene 2: a) Regionen
 */
 select 
   r.application_id                         app_id,
   'REG_P' || page_id || '_'|| region_name  comp_id,
   region_name                              comp_name,
   'PAGE_' || page_id                       parent_comp_id,
   'REGION'                                 comp_type,
   source_type                                     comp_subtype,
   null                              comp_source
 from apex_application_page_regions r, apex_applications a 
 where a.application_id = r.application_id
) union all (
/* 
 * Ebene 2: b) Seitenelemente
 */
 select 
    r.application_id                        app_id,
   'ITEM_P' || page_id || '_'|| item_name   comp_id,
   item_name                                comp_name,
   'PAGE_' || page_id                       parent_comp_id,
   'ITEM'                                   comp_type,
   i.component_type                         comp_subtype,
   item_source_type                         comp_source
 from apex_application_page_items r, apex_applications a, item_type_structure i 
 where a.application_id = r.application_id and r.display_as = i.apex_item_type
) union all (
/* 
 * Ebene 2: c) Seitenprozesse
 */
 select 
    r.application_id                        app_id,
   'PROC_' || page_id || '_'|| process_name comp_id,
   process_name                             comp_name,
   'PAGE_' || page_id                       parent_comp_id,
   'PROCESS'                                comp_type,
   process_type                             comp_subtype,
   null                                     comp_source
 from apex_application_page_proc r, apex_applications a 
 where a.application_id = r.application_id
)
/

set lines 120
set pages 100
col app_id format 99999999
col comp_id format a30
col parent_comp_id format a30
col comp_type format a15
col comp_subtype format a20
col comp_name format a30

with myapp as (
  select * from apex_app_structure where app_id = :MY_APP_ID
) 
select lpad(' ',(level-1) * 2) || comp_type ||': '||comp_name || ' ('||comp_subtype||')' component
from myapp
start with comp_id='APPLICATION'
connect by parent_comp_id = prior comp_id
  order siblings by app_id, comp_type
/
