Rem
Rem Copyright (c) 2000 by Oracle Corporation
Rem

----------------------------------------------------------------------------
-- CATALOG VIEWS
----------------------------------------------------------------------------

create or replace view wb_rtv_platform_properties
as
  select
    property_id,
    property_path,
    property_value,
    is_mutable
  from
    wb_rt_platform_properties
  with read only;

create or replace view wb_rtv_stores
as
  select
    store_id,
    store_type_id,
    store_uoid,
    store_name,
    version_tag,
    is_deployment_target,
    owner_uoid,
    is_local_to_repos
  from
    wb_rt_stores
  with read only;
    
create or replace view wb_rtv_store_parameters
as
  select
    p.store_parameter_id,
    p.store_id,
    p.parameter_name,
    p.parameter_value as parameter_value,
    d.access_restricted,
    d.access_mode,
    0 as value_access_restricted,
    wb_rt_constants.ACCESS_MODE_SHARED as value_access_mode,
    0 as managed_mode
  from
    wb_rt_store_parameters p,
    wb_rt_stores s,
    wb_rt_def_store_type_params d    
  where
    p.store_id = s.store_id and
    d.store_type_id = s.store_type_id and
    d.parameter_name = p.parameter_name and
    owbsys.wb_rti_util.is_managed_value(d.access_restricted, d.access_mode) = 0
  union all
  select
    p.store_parameter_id,
    p.store_id,
    p.parameter_name,
    null as parameter_value,
    d.access_restricted,
    d.access_mode,
    0 as value_access_restricted,
    wb_rt_constants.ACCESS_MODE_SHARED as value_access_mode,
    1 as managed_mode
  from
    wb_rt_store_parameters p,
    wb_rt_stores s,
    wb_rt_def_store_type_params d    
  where
    p.store_id = s.store_id and
    d.store_type_id = s.store_type_id and
    d.parameter_name = p.parameter_name and
    owbsys.wb_rti_util.is_managed_value(d.access_restricted, d.access_mode) <> 0 and
    not exists (
      select 
        null
      from
        wb_rt_user_values uv,
        wb_rt_platform_config pc
      where
        uv.store_parameter_id = p.store_parameter_id and
        uv.user_id = owbsys.wb_rti_util.get_user_value_user_id(uid, d.access_restricted, d.access_mode, owbsys.wb_rti_util.is_in_enterprise_mode(pc.enterprise_mode))
    )
  union all
  select
    p.store_parameter_id,
    p.store_id,
    p.parameter_name,
    decode(uv.access_restricted, 0, uv.parameter_value, decode(uv.parameter_value, null, null, '******')) as parameter_value,
    d.access_restricted as access_restricted,
    d.access_mode,
    decode(uv.access_restricted, 0, 0, decode(uv.parameter_value, null, 0, 1)) as value_access_restricted,
    decode(owbsys.wb_rti_util.is_departmental_user_id(uv.user_id), 0, wb_rt_constants.ACCESS_MODE_ENTERPRISE, wb_rt_constants.ACCESS_MODE_DEPARTMENTAL),
    1 as managed_mode
  from
    wb_rt_store_parameters p,
    wb_rt_stores s,
    wb_rt_def_store_type_params d,
    wb_rt_user_values uv,
    wb_rt_platform_config pc
  where
    p.store_id = s.store_id and
    d.store_type_id = s.store_type_id and
    d.parameter_name = p.parameter_name and
    uv.store_parameter_id = p.store_parameter_id and
    owbsys.wb_rti_util.is_managed_value(d.access_restricted, d.access_mode) <> 0 and
    uv.user_id = owbsys.wb_rti_util.get_user_value_user_id(uid, d.access_restricted, d.access_mode, owbsys.wb_rti_util.is_in_enterprise_mode(pc.enterprise_mode))
  with read only;

create or replace view wb_rtv_specified_user_values
as
  select
    p.store_parameter_id,
    p.store_id,
    p.parameter_name,
    v.user_id,
    d.access_restricted as access_restricted,
    d.access_mode,
    decode(v.access_restricted, 0, 0, decode(v.parameter_value, null, 0, 1)) as value_access_restricted,
    owbsys.wb_rti_util.is_departmental_user_id(v.user_id) as value_departmental
  from
    wb_rt_store_parameters p,
    wb_rt_user_values v,
    wb_rt_stores s,
    wb_rt_def_store_type_params d,
    all_users u
  where
    v.store_parameter_id = p.store_parameter_id and 
    p.store_id = s.store_id and
    d.store_type_id = s.store_type_id and
    d.parameter_name = p.parameter_name and
    owbsys.wb_rti_util.is_managed_value(d.access_restricted, d.access_mode) <> 0 and
    (v.user_id = u.user_id or 
     owbsys.wb_rti_util.is_departmental_user_id(v.user_id) = 1)
  with read only;


create or replace view wb_rtv_warehouse_objects
as
  select
    warehouse_object_id,
    store_id,
    tgt_store_id,
    object_type_id,
    object_type_name,
    impl_type_name,
    parent_object_id,
    object_uoid,
    object_name,
    object_qname,
    version_tag,
    creation_script,
    creation_script_binary,
    wb_rt_constants.FORMAT_TYPE(script_mime_type, script_encoding_type) as script_format_type,
    script_mime_type,
    script_encoding_type,
    client_info,
    metadata_info,
    object_status,
    sub_objects_valid,
    related_objects_valid,
    deployment_repos_version,
    upgrade_incompatibility,
    created_on,
    created_by
  from
    wb_rt_warehouse_objects
  with read only;

create or replace view wb_rtv_tasks
as
  select
    task_id,
    warehouse_object_id,
    execution_store_uoid,
    execution_operator_id,
    execution_object_uoid,
    execution_object_type_id,
    execution_object_store_uoid,
    execution_object_store_type_id,
    task_name,
    task_input,
    task_input_binary,
    task_input_mime_type,
    task_input_encoding_type
  from
    wb_rt_tasks
  with read only;

create or replace view wb_rtv_task_custom_parameters
as
  select
    custom_parameter_id,
    task_id,
    parameter_uoid,
    parameter_name,
    parameter_type,
    parameter_mode,
    parameter_scope,
    is_fixed,
    is_required,
    type_length,
    type_scale,
    type_precision
  from
    wb_rt_task_custom_parameters
  with read only;

create or replace view wb_rtv_task_parameter_defaults
as
  select
    task_parameter_default_id,
    task_id,
    custom_parameter_id,
    system_parameter_id,
    bound_to_name,
    bound_to_kind,
    bound_to_scope,
    value_kind,
    clob_value,
    value
  from
    wb_rt_task_parameter_defaults
  with read only;

create or replace view wb_rtv_task_parameters
as
  select 
    cp.task_id as task_id,
    wb_rt_constants.PARAM_KIND_CUSTOM as parameter_kind,
    cp.parameter_uoid, 
    cp.parameter_name, 
    cp.parameter_type, 
    cp.parameter_mode, 
    cp.parameter_scope,
    pd.bound_to_name,
    pd.bound_to_kind,
    pd.bound_to_scope,
    cp.is_fixed,
    cp.is_required, 
    cp.type_length,
    cp.type_scale,
    cp.type_precision,
    pd.value_kind as default_value_kind,
    pd.clob_value as default_clob_value,
    pd.value as default_value
  from 
    wb_rt_task_custom_parameters cp, 
    wb_rt_task_parameter_defaults pd 
  where 
    pd.custom_parameter_id (+) = cp.custom_parameter_id  
  union all 
  select 
    t.task_id,
    wb_rt_constants.PARAM_KIND_SYSTEM as parameter_kind,
    null as parameter_uoid,
    sp.parameter_name, 
    sp.parameter_type, 
    sp.parameter_mode, 
    sp.parameter_scope,
    pd.bound_to_name,
    pd.bound_to_kind,
    pd.bound_to_scope,
    sp.is_fixed,
    sp.is_required, 
    sp.type_length,
    sp.type_scale,
    sp.type_precision,
    pd.value_kind as default_value_kind,
    pd.clob_value as default_clob_value,
    pd.value as default_value
  from 
    wb_rt_def_system_parameters sp, 
    wb_rt_def_execution_operators eo,
    wb_rt_def_execution_adapters ea,
    wb_rt_task_parameter_defaults pd,
    wb_rt_tasks t
  where 
    pd.task_id = t.task_id and
    pd.system_parameter_id = sp.system_parameter_id and 
    t.execution_operator_id = eo.execution_operator_id and
    eo.execution_adapter_id = ea.execution_adapter_id and
    (sp.execution_adapter_id = ea.execution_adapter_id or 
    sp.execution_operator_id = eo.execution_operator_id)  
  union all 
  select 
    t.task_id,
    wb_rt_constants.PARAM_KIND_SYSTEM as parameter_kind,
    null as parameter_uoid,
    sp.parameter_name, 
    sp.parameter_type, 
    sp.parameter_mode, 
    sp.parameter_scope,
    sp.bound_to_name,
    sp.bound_to_kind,
    sp.bound_to_scope,
    sp.is_fixed,
    sp.is_required, 
    sp.type_length,
    sp.type_scale,
    sp.type_precision,
    sp.default_value_kind as default_value_kind,
    sp.default_clob_value as default_clob_value,
    sp.default_value as default_value
  from 
    wb_rt_def_system_parameters sp, 
    wb_rt_def_execution_operators eo,
    wb_rt_def_execution_adapters ea,
    wb_rt_tasks t
  where 
    not exists (
      select null 
      from wb_rt_task_parameter_defaults pd 
      where pd.task_id = t.task_id and pd.system_parameter_id = sp.system_parameter_id
    ) and
    t.execution_operator_id = eo.execution_operator_id and
    eo.execution_adapter_id = ea.execution_adapter_id and
    (sp.execution_adapter_id = ea.execution_adapter_id or 
    sp.execution_operator_id = eo.execution_operator_id)
  with read only;

create or replace view wb_rtv_state_variables
as
  select
    state_variable_id,
    warehouse_object_id,
    variable_uoid,
    variable_name,
    variable_type,
    value_kind,
    clob_value,
    value,
    type_length,
    type_scale,
    type_precision
  from
    wb_rt_state_variables
  with read only;