set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
--------------------------------------------------------------------------------
--
-- ORACLE Application Express (APEX) export file
--
-- You should run the script connected to SQL*Plus as the Oracle user
-- APEX_050000 or as the owner (parsing schema) of the application.
--
-- NOTE: Calls to apex_application_install override the defaults below.
--
--------------------------------------------------------------------------------
begin
wwv_flow_api.import_begin (
 p_version_yyyy_mm_dd=>'2013.01.01'
,p_default_workspace_id=>51646466283622323
);
end;
/
prompt  WORKSPACE 51646466283622323
--
-- Workspace, User Group, User, and Team Development Export:
--   Date and Time:   18:00 Mittwoch Juni 22, 2016
--   Exported By:     SYSTEM
--   Export Type:     Workspace Export
--   Version:         5.0.3.00.03
--   Instance ID:     63117451966518
--
-- Import:
--   Using Instance Administration / Manage Workspaces
--   or
--   Using SQL*Plus as the Oracle user APEX_050000
 
begin
    wwv_flow_api.set_security_group_id(p_security_group_id=>51646466283622323);
end;
/
----------------
-- W O R K S P A C E
-- Creating a workspace will not create database schemas or objects.
-- This API creates only the meta data for this APEX workspace
prompt  Creating workspace DUMMY...
begin
wwv_flow_fnd_user_api.create_company (
  p_id => 51646552464622347
 ,p_provisioning_company_id => 51646466283622323
 ,p_short_name => 'DUMMY'
 ,p_display_name => 'DUMMY'
 ,p_first_schema_provisioned => 'TEST'
 ,p_company_schemas => 'TEST'
 ,p_ws_schema => 'TEST'
 ,p_account_status => 'ASSIGNED'
 ,p_allow_plsql_editing => 'Y'
 ,p_allow_app_building_yn => 'Y'
 ,p_allow_packaged_app_ins_yn => 'Y'
 ,p_allow_sql_workshop_yn => 'Y'
 ,p_allow_websheet_dev_yn => 'Y'
 ,p_allow_team_development_yn => 'Y'
 ,p_allow_to_be_purged_yn => 'Y'
 ,p_allow_restful_services_yn => 'Y'
 ,p_source_identifier => 'DUMMY'
 ,p_path_prefix => 'DUMMY'
 ,p_files_version => 1
);
end;
/
----------------
-- G R O U P S
--
prompt  Creating Groups...
begin
wwv_flow_api.create_user_groups (
  p_id => 10343731401008121,
  p_GROUP_NAME => 'OAuth2 Client Developer',
  p_SECURITY_GROUP_ID => 10,
  p_GROUP_DESC => 'Users authorized to register OAuth2 Client Applications');
end;
/
begin
wwv_flow_api.create_user_groups (
  p_id => 10343607722008121,
  p_GROUP_NAME => 'RESTful Services',
  p_SECURITY_GROUP_ID => 10,
  p_GROUP_DESC => 'Users authorized to use RESTful Services with this workspace');
end;
/
begin
wwv_flow_api.create_user_groups (
  p_id => 10343519567008118,
  p_GROUP_NAME => 'SQL Developer',
  p_SECURITY_GROUP_ID => 10,
  p_GROUP_DESC => 'Users authorized to use SQL Developer with this workspace');
end;
/
prompt  Creating group grants...
----------------
-- U S E R S
-- User repository for use with APEX cookie-based authentication.
--
prompt  Creating Users...
begin
wwv_flow_fnd_user_api.create_fnd_user (
  p_user_id                      => '51646330959622323',
  p_user_name                    => 'DIETMAR.AUST',
  p_first_name                   => 'Dietmar',
  p_last_name                    => 'Aust',
  p_description                  => '',
  p_email_address                => 'dietmar.aust@opal-consulting.de',
  p_web_password                 => '2C65E7E3597BA86BA713F4602E8C5D3EF7BA94A4',
  p_web_password_format          => '5;2;10000',
  p_group_ids                    => '10343519567008118:10343607722008121:10343731401008121:',
  p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
  p_default_schema               => 'TEST',
  p_account_locked               => 'N',
  p_account_expiry               => to_date('201601061717','YYYYMMDDHH24MI'),
  p_failed_access_attempts       => 0,
  p_change_password_on_first_use => 'Y',
  p_first_password_use_occurred  => 'Y',
  p_allow_app_building_yn        => 'Y',
  p_allow_sql_workshop_yn        => 'Y',
  p_allow_websheet_dev_yn        => 'Y',
  p_allow_team_development_yn    => 'Y',
  p_allow_access_to_schemas      => '');
end;
/
----------------
--Application Builder Preferences
--
----------------
--Click Count Logs
--
----------------
--csv data loading
--
----------------
--mail
--
----------------
--mail log
--
----------------
--app models
--
----------------
--password history
--
begin
  wwv_flow_api.create_password_history (
    p_id => 51646697334622355,
    p_user_id => 51646330959622323,
    p_password => 'E0A98B8135871DBFDDBE05AD9C046CED6CCE92F5');
end;
/
begin
  wwv_flow_api.create_password_history (
    p_id => 51665447323630132,
    p_user_id => 51646330959622323,
    p_password => '2C65E7E3597BA86BA713F4602E8C5D3EF7BA94A4');
end;
/
----------------
--preferences
--
begin
  wwv_flow_api.create_preferences$ (
    p_id => 51665668391630796,
    p_user_id => 'DIETMAR.AUST',
    p_preference_name => 'FSP_IR_4000_P1500_W3519715528105919',
    p_attribute_value => '3521529006112497____');
end;
/
----------------
--query builder
--
----------------
--sql scripts
--
----------------
--sql commands
--
----------------
--user access log
--
 
prompt ...RESTful Services
 
begin
wwv_flow_api.remove_restful_service(
 p_id=>wwv_flow_api.id(51646949699622361)
,p_name=>'oracle.example.hr'
);
 
end;
/
prompt --application/restful_services/oracle_example_hr
begin
wwv_flow_api.create_restful_module(
 p_id=>wwv_flow_api.id(51646949699622361)
,p_name=>'oracle.example.hr'
,p_uri_prefix=>'hr/'
,p_parsing_schema=>'TEST'
,p_items_per_page=>10
,p_status=>'PUBLISHED'
,p_row_version_number=>1
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51648313248622367)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'empinfo/'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51648418208622367)
,p_template_id=>wwv_flow_api.id(51648313248622367)
,p_source_type=>'QUERY'
,p_format=>'CSV'
,p_method=>'GET'
,p_require_https=>'NO'
,p_source=>'select * from emp'
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51647092468622363)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'employees/'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51647118884622364)
,p_template_id=>wwv_flow_api.id(51647092468622363)
,p_source_type=>'QUERY'
,p_format=>'DEFAULT'
,p_method=>'GET'
,p_items_per_page=>7
,p_require_https=>'NO'
,p_source=>wwv_flow_utilities.join(wwv_flow_t_varchar2(
'select empno "$uri", empno, ename',
'  from (',
'       select emp.*',
'            , row_number() over (order by empno) rn',
'         from emp',
'       ) tmp',
' where rn between :row_offset and :row_count '))
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51647545326622366)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'employeesfeed/'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51647697851622366)
,p_template_id=>wwv_flow_api.id(51647545326622366)
,p_source_type=>'FEED'
,p_format=>'DEFAULT'
,p_method=>'GET'
,p_items_per_page=>25
,p_require_https=>'NO'
,p_source=>'select empno, ename from emp order by deptno, ename'
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51647715760622366)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'employeesfeed/{id}'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51647880313622366)
,p_template_id=>wwv_flow_api.id(51647715760622366)
,p_source_type=>'QUERY'
,p_format=>'CSV'
,p_method=>'GET'
,p_require_https=>'NO'
,p_source=>'select * from emp where empno = :id'
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51647231132622364)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'employees/{id}'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51647326751622364)
,p_template_id=>wwv_flow_api.id(51647231132622364)
,p_source_type=>'QUERY_1_ROW'
,p_format=>'DEFAULT'
,p_method=>'GET'
,p_require_https=>'NO'
,p_source=>wwv_flow_utilities.join(wwv_flow_t_varchar2(
'select * from emp',
' where empno = :id'))
);
wwv_flow_api.create_restful_param(
 p_id=>wwv_flow_api.id(51647445187622365)
,p_handler_id=>wwv_flow_api.id(51647326751622364)
,p_name=>'ID'
,p_bind_variable_name=>'ID'
,p_source_type=>'HEADER'
,p_access_method=>'IN'
,p_param_type=>'STRING'
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51647901034622366)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'empsec/{empname}'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51648012051622366)
,p_template_id=>wwv_flow_api.id(51647901034622366)
,p_source_type=>'QUERY'
,p_format=>'DEFAULT'
,p_method=>'GET'
,p_require_https=>'NO'
,p_source=>wwv_flow_utilities.join(wwv_flow_t_varchar2(
'select empno, ename, deptno, job from emp ',
'	where ((select job from emp where ename = :empname) IN (''PRESIDENT'', ''MANAGER'')) ',
'    OR  ',
'    (deptno = (select deptno from emp where ename = :empname)) ',
'order by deptno, ename',
''))
);
wwv_flow_api.create_restful_template(
 p_id=>wwv_flow_api.id(51648113330622366)
,p_module_id=>wwv_flow_api.id(51646949699622361)
,p_uri_template=>'empsecformat/{empname}'
,p_priority=>0
,p_etag_type=>'HASH'
);
wwv_flow_api.create_restful_handler(
 p_id=>wwv_flow_api.id(51648268479622367)
,p_template_id=>wwv_flow_api.id(51648113330622366)
,p_source_type=>'PLSQL'
,p_format=>'DEFAULT'
,p_method=>'GET'
,p_require_https=>'NO'
,p_source=>wwv_flow_utilities.join(wwv_flow_t_varchar2(
'DECLARE',
'  prevdeptno     number;',
'  total_rows     number;',
'  deptloc        varchar2(20);',
'  deptname       varchar2(20);',
'  ',
'  CURSOR         getemps is select * from emp ',
'                             start with ename = :empname',
'                           connect by prior empno = mgr',
'                             order siblings by deptno, ename;',
'BEGIN',
'  sys.htp.htmlopen;',
'  sys.htp.headopen;',
'  sys.htp.title(''Hierarchical Department Report for Employee ''||:empname);',
'  sys.htp.headclose;',
'  sys.htp.bodyopen;',
' ',
'  for l_employee in getemps ',
'  loop',
'      if l_employee.deptno != prevdeptno or prevdeptno is null then',
'          select dname, loc ',
'            into deptname, deptloc ',
'            from dept ',
'           where deptno = l_employee.deptno;',
'           ',
'          if prevdeptno is not null then',
'              sys.htp.print(''</ul>'');',
'          end if;',
'',
'          sys.htp.print(''Department '' || apex_escape.html(deptname) || '' located in '' || apex_escape.html(deptloc) || ''<p/>'');',
'          sys.htp.print(''<ul>'');',
'      end if;',
'',
'      sys.htp.print(''<li>'' || apex_escape.html(l_employee.ename) || '', ''  || apex_escape.html(l_employee.empno) || '', '' || ',
'                        apex_escape.html(l_employee.job) || '', '' || apex_escape.html(l_employee.sal) || ''</li>'');',
'',
'      prevdeptno := l_employee.deptno;',
'      total_rows := getemps%ROWCOUNT;',
'      ',
'  end loop;',
'',
'  if total_rows > 0 then',
'      sys.htp.print(''</ul>'');',
'  end if;',
'',
'  sys.htp.bodyclose;',
'  sys.htp.htmlclose;',
'END;'))
);
end;
/
-- SET SCHEMA
 
begin
 
   wwv_flow_api.g_id_offset := 0;
   wwv_flow_hint.g_schema   := 'TEST';
   wwv_flow_hint.check_schema_privs;
 
end;
/

 
--------------------------------------------------------------------
prompt  SCHEMA TEST - User Interface Defaults, Table Defaults
--
-- Import using sqlplus as the Oracle user: APEX_050000
-- Exported 18:00 Mittwoch Juni 22, 2016 by: 
--
 
--------------------------------------------------------------------
prompt User Interface Defaults, Attribute Dictionary
--
-- Exported 18:00 Mittwoch Juni 22, 2016 by: 
--
-- SHOW EXPORTING WORKSPACE
 
begin
 
   wwv_flow_api.g_id_offset := 0;
   wwv_flow_hint.g_exp_workspace := 'DUMMY';
 
end;
/

begin
wwv_flow_api.import_end(p_auto_install_sup_obj => nvl(wwv_flow_application_install.get_auto_install_sup_obj, false));
commit;
end;
/
set verify on feedback on define on
prompt  ...done
