Rem
Rem $Header: whdev/2.0/owb/oracle/wh/runtime/sql/pls/metadata/wb_location_helper_views.sql /main/3 2011/04/22 00:51:36 yuhhuang Exp $
Rem
Rem wb_location_helper_views.sql
Rem
Rem Copyright (c) 2010, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      wb_location_helper_views.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    swzhou      08/08/10 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

create or replace view wb_rtv_store_summary as 
SELECT 
  "UNPIVOT"."STORE_NAME" "STORE_NAME", 
  "UNPIVOT"."HOST" "HOST", 
  "UNPIVOT"."PORT" "PORT", 
  "UNPIVOT"."SERVICE" "SERVICE", 
  "UNPIVOT"."VERSION" "VERSION", 
  "UNPIVOT"."SCHEMA" "SCHEMA", 
  "UNPIVOT"."USER" "USER", 
  '<password>' PASSWORD 
FROM 
  (SELECT 
  "STORE_NAME" "STORE_NAME", 
  MIN(CASE WHEN "PARAMETER_NAME" = 'Host' THEN "PARAMETER_VALUE" ELSE NULL END) "HOST", 
  MIN(CASE WHEN "PARAMETER_NAME" = 'Port' THEN "PARAMETER_VALUE" ELSE NULL END) "PORT", 
  MIN(CASE WHEN "PARAMETER_NAME" = 'Service' THEN "PARAMETER_VALUE" ELSE NULL END) "SERVICE", 
  MIN(CASE WHEN "PARAMETER_NAME" = 'Schema' THEN "PARAMETER_VALUE" ELSE NULL END) "SCHEMA", 
  MIN(CASE WHEN "PARAMETER_NAME" = 'ConnectAs' THEN "PARAMETER_VALUE" ELSE NULL END) "USER", 
  STORE_TYPE_VERSION "VERSION" 
FROM 
  (SELECT 
  "INGRP1"."STORE_NAME" "STORE_NAME", 
  "WB_RTV_STORE_PARAMETERS"."PARAMETER_NAME" "PARAMETER_NAME", 
  "WB_RTV_STORE_PARAMETERS"."PARAMETER_VALUE" "PARAMETER_VALUE", 
  "INGRP1"."STORE_TYPE_VERSION" "STORE_TYPE_VERSION" 
FROM 
  (SELECT 
  "WB_RTV_STORES"."STORE_ID" "STORE_ID", 
  "WB_RTV_STORES"."STORE_TYPE_ID" "STORE_TYPE_ID", 
  "WB_RTV_STORES"."STORE_UOID" "STORE_UOID", 
  "WB_RTV_STORES"."STORE_NAME" "STORE_NAME", 
  "WB_RTV_STORES"."VERSION_TAG" "VERSION_TAG", 
  "WB_RTV_STORES"."IS_DEPLOYMENT_TARGET" "IS_DEPLOYMENT_TARGET", 
  "WB_RTV_STORES"."OWNER_UOID" "OWNER_UOID", 
  "WB_RTV_STORES"."IS_LOCAL_TO_REPOS" "IS_LOCAL_TO_REPOS", 
  NVL((SELECT 
  "WB_RTV_DEF_STORE_TYPES_S"."STORE_TYPE_VERSION" "STORE_TYPE_VERSION" 
FROM 
  OWBSYS."WB_RTV_DEF_STORE_TYPES"  "WB_RTV_DEF_STORE_TYPES_S" 
  WHERE 
  ( 
"WB_RTV_DEF_STORE_TYPES_S".STORE_TYPE_NAME IN ('Oracle Database', 'Oracle Gateway', 'Oracle Workflow') and 
"WB_RTV_DEF_STORE_TYPES_S"."STORE_TYPE_ID" = "WB_RTV_STORES"."STORE_TYPE_ID" ) ), NULL) "STORE_TYPE_VERSION" 
FROM 
  OWBSYS."WB_RTV_STORES"  "WB_RTV_STORES" WHERE STORE_NAME !='PlatformSchema') "INGRP1", 
  OWBSYS."WB_RTV_STORE_PARAMETERS"  "WB_RTV_STORE_PARAMETERS"
  WHERE "INGRP1"."STORE_ID" = "WB_RTV_STORE_PARAMETERS"."STORE_ID"
  ) "OUTGRP1"   
GROUP BY 
"STORE_NAME",STORE_TYPE_VERSION ) "UNPIVOT" ;

grant select on wb_rtv_store_summary to OWB_USER;

CREATE OR REPLACE VIEW wb_dt_location_summary as
select 
  location_name,
  HOST,
  (CASE WHEN PORT IS NULL THEN '1521' ELSE PORT END) port,
  (CASE WHEN VERSION IS NULL And location_type='Oracle Database' then '11.2' 
        WHEN VERSION IS NULL And location_type='Oracle Workflow' THEN '2.6.3' 
        ELSE VERSION END) Version,
  SERVICE,
  SCHEMA,
  "USER"
from
  (select 
     location_name,
     location_type,
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_Host' THEN "PARAMETER_VALUE" ELSE NULL END) "HOST", 
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_Port' THEN "PARAMETER_VALUE" ELSE NULL END) "PORT", 
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_Version' THEN "PARAMETER_VALUE" ELSE NULL END) "VERSION",
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_ServiceName' THEN "PARAMETER_VALUE" ELSE NULL END) "SERVICE", 
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_Schema' THEN "PARAMETER_VALUE" ELSE NULL END) "SCHEMA", 
     MIN(CASE WHEN "PARAMETER_NAME" = 'CMPLocation_ConnectAsUser' THEN "PARAMETER_VALUE" ELSE NULL END) "USER" 
   from
     (select 
        ingrp.name location_name,
        ingrp.loctype location_type,
        cmpstringpropertyvalue_v.logicalname parameter_name,
        cmpstringpropertyvalue_v.value parameter_value
      from
        (SELECT 
           "ELEMENTID", 
           "LOCTYPE", 
           "UOID", 
           "NAME"
         FROM 
           cmplocation_v 
         WHERE 
           LOCTYPE in ('Oracle Database', 'Oracle Gateway', 'Oracle Workflow')
        ) ingrp, 
        cmpstringpropertyvalue_v
      where
        cmpstringpropertyvalue_v.propertyowner = ingrp.elementid
     ) outgrp
  group by 
     location_name, location_type
  ) "UNPIVOT";
  
grant select on wb_dt_location_summary to OWB_USER;  
/