--
-- Execute as User SYSTEM
-- This script adds a Report Region listing all Applications to the
-- APEX Builder Login Site (for the english Installation only!).
--
-- Created by Peter Raganitsch ( http://blog.oracleapex.at
--                               http://www.oracle-and-apex.com )
--
--
set define off
set verify off
set serveroutput on size 1000000
set feedback off
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
begin wwv_flow.g_import_in_progress := true; end;
/

begin
  -- Assumes you are running the script connected to SQL*Plus as the Oracle user APEX_030200 or as the owner (parsing schema) of the application.
  wwv_flow_api.set_security_group_id(p_security_group_id=>APEX_UTIL.Find_Security_Group_Id('INTERNAL'));
end;
/

begin wwv_flow.g_import_in_progress := true; end;
/
begin
select value into wwv_flow_api.g_nls_numeric_chars from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';
end;

/
begin execute immediate 'alter session set nls_numeric_characters=''.,''';
end;

/
begin wwv_flow.g_browser_language := 'en-us'; end;
/
prompt  Check Compatibility...

begin
-- This date identifies the minimum version required to import this file.
wwv_flow_api.set_version(p_version_yyyy_mm_dd=>'2007.01.08');
end;
/

prompt  Set Application ID...

begin
   -- SET APPLICATION ID
   wwv_flow.g_flow_id := 4550;
   wwv_flow_api.g_id_offset := 0;
null;
end;
/

declare
  s varchar2(32767) := null;
  l_clob clob;
  l_length number := 1;
  vTemplate NUMBER;
  vRegionId NUMBER;
begin
s:='SELECT W.SHORT_NAME       AS WORKSPACE'||CHR(10)||
'     , F.ID               AS APPLICATION_ID'||CHR(10)||
'     , F.NAME             AS APPLICATION_NAME'||CHR(10)||
'     , F.ALIAS            AS ALIAS'||CHR(10)||
'     , F.OWNER            AS OWNER'||CHR(10)||
'     , F.LAST_UPDATED_BY  AS LAST_UPDATED_BY'||CHR(10)||
'     , F.LAST_UPDATED_ON  AS LAST_UPDATED_ON'||CHR(10)||
'  FROM WWV_FLOWS                F'||CHR(10)||
'     , WWV_FLOW_COMPANIES       W'||CHR(10)||
' WHERE F.SECURITY_GROUP_ID  = W.PROVISIONING_COMPANY_ID'||CHR(10)||
'   AND F.SECURITY_GROUP_ID != 10'||CHR(10)||
'   AND NOT EXISTS '||CHR(10)||
'       ( SELECT 1 FROM WWV_FLOW_LANGUAGE_MAP M WHERE M.TRANSLATION_FLOW_ID = F.ID'||CHR(10)||
'       )'||CHR(10)||
'   AND W.PROVISIONING_COMPANY_ID != 0';
  --
  SELECT TEMPLATE_ID
    INTO vTemplate
    FROM APEX_APPLICATION_TEMPLATES
   WHERE APPLICATION_ID = 4550
     AND TEMPLATE_NAME  = 'Report Region 600px (c:htmldbRR600)(Buttons Bottom)'
  ;
  --
wwv_flow_api.create_report_region (
  p_flow_id=> wwv_flow.g_flow_id,
  p_page_id=> 1,
  p_name=> 'Available Applications',
  p_region_name=>'',
  p_template=> vTemplate,
  p_display_sequence=> 100,
  p_display_column=> 1,
  p_display_point=> 'AFTER_SHOW_ITEMS',
  p_source=> s,
  p_source_type=> 'SQL_QUERY',
  p_display_error_message=> '#SQLERRM#',
  p_plug_caching=> 'NOT_CACHED',
  p_customized=> '0',
  p_translate_title=> 'Y',
  p_ajax_enabled=> 'N',
  p_query_row_template=> 11,
  p_query_headings_type=> 'QUERY_COLUMNS_INITCAP',
  p_query_num_rows=> '100',
  p_query_options=> 'DERIVED_REPORT_COLUMNS',
  p_query_break_cols=> '1',
  p_query_no_data_found=> 'No data found.',
  p_query_num_rows_type=> 'ROW_RANGES_WITH_LINKS',
  p_query_row_count_max=> '500',
  p_pagination_display_position=> 'TOP_AND_BOTTOM_LEFT',
  p_break_type_flag => 'DEFAULT_BREAK_FORMATTING',
  p_csv_output=> 'N',
  p_sort_null=> 'F',
  p_query_asc_image=> 'arrow_down_gray_dark.gif',
  p_query_asc_image_attr=> 'width="13" height="12"',
  p_query_desc_image=> 'arrow_up_gray_dark.gif',
  p_query_desc_image_attr=> 'width="13" height="12"',
  p_plug_query_strip_html=> 'Y',
  p_comment=>'');

  SELECT REGION_ID
    INTO vRegionId
    FROM APEX_APPLICATION_PAGE_REGIONS
   WHERE APPLICATION_ID = 4550
     AND PAGE_ID        = 1
     AND REGION_NAME    = 'Available Applications'
  ;

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 1,
  p_form_element_id=> null,
  p_column_alias=> 'WORKSPACE',
  p_column_display_sequence=> 1,
  p_column_heading=> 'WORKSPACE',
  p_column_link=>'javascript:$s(''F4550_P1_COMPANY'',''#WORKSPACE#'');',
  p_column_linktext=>'#WORKSPACE#',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>1,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_lov_show_nulls=> 'NO',
  p_pk_col_source=> s,
  p_lov_display_extra=> 'YES',
  p_include_in_export=> 'Y',
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 2,
  p_form_element_id=> null,
  p_column_alias=> 'APPLICATION_ID',
  p_column_display_sequence=> 2,
  p_column_heading=> 'APPLICATION_ID',
  p_column_alignment=>'RIGHT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_pk_col_source=> s,
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 3,
  p_form_element_id=> null,
  p_column_alias=> 'APPLICATION_NAME',
  p_column_display_sequence=> 3,
  p_column_heading=> 'APPLICATION_NAME',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_pk_col_source=> s,
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 4,
  p_form_element_id=> null,
  p_column_alias=> 'ALIAS',
  p_column_display_sequence=> 4,
  p_column_heading=> 'ALIAS',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_pk_col_source=> s,
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 5,
  p_form_element_id=> null,
  p_column_alias=> 'OWNER',
  p_column_display_sequence=> 5,
  p_column_heading=> 'OWNER',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_pk_col_source=> s,
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 6,
  p_form_element_id=> null,
  p_column_alias=> 'LAST_UPDATED_BY',
  p_column_display_sequence=> 6,
  p_column_heading=> 'LAST_UPDATED_BY',
  p_column_link=>'javascript:$s(''F4550_P1_USERNAME'',''#LAST_UPDATED_BY#'');',
  p_column_linktext=>'#LAST_UPDATED_BY#',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_lov_show_nulls=> 'NO',
  p_pk_col_source=> s,
  p_lov_display_extra=> 'YES',
  p_include_in_export=> 'Y',
  p_column_comment=>'');

s := null;
wwv_flow_api.create_report_columns (
  p_region_id=> vRegionId,
  p_flow_id=> wwv_flow.g_flow_id,
  p_query_column_id=> 7,
  p_form_element_id=> null,
  p_column_alias=> 'LAST_UPDATED_ON',
  p_column_display_sequence=> 7,
  p_column_heading=> 'LAST_UPDATED_ON',
  p_column_alignment=>'LEFT',
  p_heading_alignment=>'CENTER',
  p_default_sort_column_sequence=>0,
  p_disable_sort_column=>'N',
  p_sum_column=> 'N',
  p_hidden_column=> 'N',
  p_display_as=>'WITHOUT_MODIFICATION',
  p_pk_col_source=> s,
  p_column_comment=>'');
end;
/


commit;
begin
execute immediate 'alter session set nls_numeric_characters='''||wwv_flow_api.g_nls_numeric_chars||'''';
end;
/
set verify on
set feedback on
prompt  ...done