Rem
Rem Copyright (c) 2000, 2008, Oracle. All rights reserved.  
Rem

----------------------------------------------------------------------------
-- PUBLIC METADATA VIEWS
----------------------------------------------------------------------------

-- Public View Version set to 11.2 (11.1 for Tokyo, 10.1 for Paris)
create or replace view all_rt_installations 
as
  select
    null as installation_id,
    '' as installation_name,
    '' as description,
    wb_rt_version as installed_version,
    null as release,
    '11.2' as public_view_version,
    decode(wb_rt_is_platform_repository, 0, 'DESIGN CENTER', 'CONTROL CENTER') as schema_type,
    null as updated_on,
    null as created_on
  from 
    dual
  with read only;


create or replace view all_rt_locations
as
  select
    s.store_name as location_name,
    s.store_uoid as location_uoid,
    decode(s.is_deployment_target, 0, 'FALSE', 'TRUE') as is_deployment_target,
    st.store_type_name as location_type,
    decode(st.store_type_version, '0', null, st.store_type_version) as location_type_version
  from
    wb_rt_stores s,
    wb_rt_def_store_types st
  where
    s.store_type_id = st.store_type_id
  with read only;
    

create or replace view all_rt_location_parameters
as
  select
    s.store_name as location_name,
    p.parameter_name as parameter_name,
    decode(p.access_restricted, 0, 'FALSE', 'TRUE') as is_access_restricted,
    p.parameter_value
  from
    wb_rtv_store_parameters p,
    wb_rt_stores s
  where
    p.store_id = s.store_id
  with read only;


create or replace view all_rt_objects
as
  select
    o.object_name as object_name,
    o.object_qname as object_qname,
    ot.object_type_name as object_type,
    po.object_name as parent_object_name,
    po.object_qname as parent_object_qname,
    pot.object_type_name as parent_object_type,
    s.store_name as location_name,
    ts.store_name as tgt_location_name,
    o.object_uoid as object_uoid,
    o.version_tag as version_tag,
    o.creation_script as last_deployment_script,
    o.creation_script_binary as last_deployment_script_binary,
    wb_rt_constants.FORMAT_TYPE(o.script_mime_type, o.script_encoding_type) as last_script_format,
    o.script_mime_type as last_script_mime_type,
    o.script_encoding_type as last_script_encoding_type,
    wb_rt_constants.to_string(o.object_status) as status_when_last_deployed,
    decode(o.sub_objects_valid, 0, 'FALSE', 'TRUE') as sub_objects_valid,
    decode(o.related_objects_valid, 0, 'FALSE', 1, 'TRUE', null) as related_objects_valid,
    o.created_on as deployment_date,
    o.created_by as deployed_by
  from
    wb_rt_warehouse_objects o,
    wb_rt_warehouse_objects po,
    wb_rt_def_object_types ot,
    wb_rt_def_object_types pot,
    wb_rt_stores s,
    wb_rt_stores ts
  where
    po.warehouse_object_id(+) = o.parent_object_id and
    pot.object_type_id(+) = po.object_type_id and
    ot.object_type_id = o.object_type_id and
    o.store_id = s.store_id and   
    o.tgt_store_id = ts.store_id(+)    
  with read only;


create or replace view all_rt_tasks
as
  select
    tko.object_name as context_object_name,
    tko.object_qname as context_object_qname,
    tkot.object_type_name as context_object_type,
    tkos.store_name as context_location_name,
    tkots.store_name as context_tgt_location_name,
    es.store_name as exec_location_name,    
    tk.task_name as task_name,
    eop.operator_name as task_type,
    tk.task_input as task_input,
    tk.task_input_binary as task_input_binary,
    tk.task_input_mime_type as task_input_mime_type,
    tk.task_input_encoding_type as task_input_encoding_type,
    eo.object_name as object_name,
    eo.object_qname as object_qname,
    eot.object_type_name as object_type,
    eos.store_name as object_location_name,
    eots.store_name as object_tgt_location_name
  from
    wb_rt_tasks tk,
    wb_rt_warehouse_objects tko,
    wb_rt_def_object_types tkot,
    wb_rt_stores tkos,
    wb_rt_stores tkots,
    wb_rt_stores es,
    wb_rt_def_execution_operators eop,
    wb_rt_warehouse_objects eo,
    wb_rt_stores eos,
    wb_rt_stores eots,
    wb_rt_def_object_types eot
  where
    tk.warehouse_object_id = tko.warehouse_object_id and
    tko.object_type_id = tkot.object_type_id and
    tko.store_id = tkos.store_id and
    tko.tgt_store_id = tkots.store_id(+) and
    tk.execution_store_uoid = es.store_uoid and
    tk.execution_operator_id = eop.execution_operator_id and
    eo.object_uoid(+) = tk.execution_object_uoid and
    eo.object_type_id(+) = tk.execution_object_type_id and
    eo.store_id = eos.store_id(+) and
    eo.tgt_store_id = eots.store_id(+) and
    eo.object_type_id = eot.object_type_id(+)
  with read only;


create or replace view all_rt_task_parameters
as
  select 
    tko.object_name as context_object_name,
    tko.object_qname as context_object_qname,
    tkot.object_type_name as context_object_type,
    tkos.store_name as context_location_name,
    tkots.store_name as context_tgt_location_name,
    es.store_name as exec_location_name,
    tk.task_name as task_name,
    wb_rt_constants.to_string(wb_rt_constants.PARAM_KIND_CUSTOM) as parameter_kind,
    cp.parameter_uoid as custom_parameter_uoid, 
    cp.parameter_name as parameter_name, 
    wb_rt_constants.to_string(cp.parameter_type) as parameter_type, 
    wb_rt_constants.to_string(cp.parameter_mode) as parameter_mode, 
    wb_rt_constants.to_string(cp.parameter_scope) as parameter_scope,
    pd.bound_to_name as bound_to_name,
    wb_rt_constants.to_string(pd.bound_to_kind) as bound_to_kind,
    wb_rt_constants.to_string(pd.bound_to_scope) as bound_to_scope,
    decode(cp.is_fixed, 0, 'FALSE', 'TRUE') as is_fixed,
    decode(cp.is_required, 0, 'FALSE', 'TRUE') as is_required,
    cp.type_length as type_length,
    cp.type_scale as type_scale,
    cp.type_precision as type_precision,
    pd.value_kind as default_value_kind,
    pd.clob_value as default_clob_value,
    pd.value as default_value
  from 
    wb_rt_tasks tk,
    wb_rt_warehouse_objects tko,
    wb_rt_def_object_types tkot,
    wb_rt_stores tkos,
    wb_rt_stores tkots,
    wb_rt_stores es,
    wb_rt_task_custom_parameters cp, 
    wb_rt_task_parameter_defaults pd 
  where 
    tk.warehouse_object_id = tko.warehouse_object_id and
    tko.object_type_id = tkot.object_type_id and
    tko.store_id = tkos.store_id and
    tko.tgt_store_id = tkots.store_id(+) and
    tk.execution_store_uoid = es.store_uoid and
    cp.task_id = tk.task_id and
    pd.custom_parameter_id (+) = cp.custom_parameter_id    
  union all   
  select 
    tko.object_name as context_object_name,
    tko.object_qname as context_object_qname,
    tkot.object_type_name as context_object_type,
    tkos.store_name as context_location_name,
    tkots.store_name as context_tgt_location_name,
    es.store_name as exec_location_name,
    tk.task_name as task_name,
    wb_rt_constants.to_string(wb_rt_constants.PARAM_KIND_SYSTEM) as parameter_kind,
    null as custom_parameter_uoid,
    sp.parameter_name as parameter_name, 
    wb_rt_constants.to_string(sp.parameter_type) as parameter_type, 
    wb_rt_constants.to_string(sp.parameter_mode) as parameter_mode, 
    wb_rt_constants.to_string(sp.parameter_scope) as parameter_scope,
    pd.bound_to_name as bound_to_name,
    wb_rt_constants.to_string(pd.bound_to_kind) as bound_to_kind,
    wb_rt_constants.to_string(pd.bound_to_scope) as bound_to_scope,
    decode(sp.is_fixed, 0, 'FALSE', 'TRUE') as is_fixed,
    decode(sp.is_required, 0, 'FALSE', 'TRUE') as is_required,
    sp.type_length as type_length,
    sp.type_scale as type_scale,
    sp.type_precision as 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 tk,
    wb_rt_warehouse_objects tko,
    wb_rt_def_object_types tkot,
    wb_rt_stores tkos,
    wb_rt_stores tkots,
    wb_rt_stores es
  where 
    tk.warehouse_object_id = tko.warehouse_object_id and
    tko.object_type_id = tkot.object_type_id and
    tko.store_id = tkos.store_id and
    tko.tgt_store_id = tkots.store_id(+) and
    tk.execution_store_uoid = es.store_uoid and
    pd.task_id = tk.task_id and
    pd.system_parameter_id = sp.system_parameter_id and 
    tk.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 
    tko.object_name as context_object_name,
    tko.object_qname as context_object_qname,
    tkot.object_type_name as context_object_type,
    tkos.store_name as context_location_name,
    tkots.store_name as context_tgt_location_name,
    es.store_name as exec_location_name,
    tk.task_name as task_name,
    wb_rt_constants.to_string(wb_rt_constants.PARAM_KIND_SYSTEM) as parameter_kind,
    null as custom_parameter_uoid,
    sp.parameter_name as parameter_name, 
    wb_rt_constants.to_string(sp.parameter_type) as parameter_type, 
    wb_rt_constants.to_string(sp.parameter_mode) as parameter_mode, 
    wb_rt_constants.to_string(sp.parameter_scope) as parameter_scope,
    sp.bound_to_name as bound_to_name,
    wb_rt_constants.to_string(sp.bound_to_kind) as bound_to_kind,
    wb_rt_constants.to_string(sp.bound_to_scope) as bound_to_scope,
    decode(sp.is_fixed, 0, 'FALSE', 'TRUE') as is_fixed,
    decode(sp.is_required, 0, 'FALSE', 'TRUE') as is_required,
    sp.type_length as type_length,
    sp.type_scale as type_scale,
    sp.type_precision as 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 tk,
    wb_rt_warehouse_objects tko,
    wb_rt_def_object_types tkot,
    wb_rt_stores tkos,
    wb_rt_stores tkots,
    wb_rt_stores es
  where 
    tk.warehouse_object_id = tko.warehouse_object_id and
    tko.object_type_id = tkot.object_type_id and
    tko.store_id = tkos.store_id and
    tko.tgt_store_id = tkots.store_id(+) and
    tk.execution_store_uoid = es.store_uoid and
    not exists (
      select null 
      from wb_rt_task_parameter_defaults pd 
      where pd.task_id = tk.task_id and pd.system_parameter_id = sp.system_parameter_id
    ) and
    tk.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;