--------------------------------------------------------
--  DDL for View SMD_PDBS_PRODUCTION_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."SMD_PDBS_PRODUCTION_VW" ("PDBID", "CDB", "THINNAME", "SERVICENAME", "PDBNAME", "VERSIONMAJOR", "VERSIONMINOR", "DCID", "DATABASETYPE", "DESCRIPTION", "SINGLETONDB", "ALLOWCLONES", "MAINTENANCEWINDOW", "NOTE", "COMPID", "AVAILABILITYDOMAIN", "ADDITIONALATTRIBUTES", "LASTMODIFIED", "EDITION", "ISPOOLEDCLONE", "TYPE", "EXTENSION", "OCPUCOUNT", "ECPUCOUNT", "STORAGE", "DBVERSION", "PRIVATEIP", "HOSTNAME", "USERNAME", "NSDNSHOSTNAME", "TOWERID", "ISTEMPLATE", "STANDBYOCID", "STANDBYDCID", "RESOURCEUSER", "REGION", "ENDPOINT", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "PDB_ONLINE", "GROUP_", "PDB_CATEGORY", "PRODUCT_TYPE", "ISHIPAA", "DATABASENAME", "DISPLAYNAME", "OCID") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  select
    PDBID, CDB, THINNAME, SERVICENAME, PDBNAME, VERSIONMAJOR, VERSIONMINOR, DCID, DATABASETYPE, DESCRIPTION,
    SINGLETONDB, ALLOWCLONES, MAINTENANCEWINDOW, NOTE, COMPID, AVAILABILITYDOMAIN, ADDITIONALATTRIBUTES, LASTMODIFIED,
    EDITION, ISPOOLEDCLONE, TYPE, EXTENSION, OCPUCOUNT, ECPUCOUNT, STORAGE, DBVERSION, PRIVATEIP, HOSTNAME,
    USERNAME, NSDNSHOSTNAME, TOWERID, ISTEMPLATE, STANDBYOCID, STANDBYDCID, RESOURCEUSER, REGION, ENDPOINT,
    METADATA_DOWNLOADED_TIMESTAMP_UTC,
    "ONLINE" as PDB_ONLINE,
    "GROUP" as GROUP_,
    case when THINNAME like '%.adb.%' then 'ATP' else 'EXA' end as PDB_CATEGORY,
    case when ENDPOINT like '%nsx%' then 'NSX' else 'NSN' end as PRODUCT_TYPE,
    case when ENDPOINT like '%nsx%' then null else JSON_VALUE(EXTENSION, '$.ishipaa') end as ISHIPAA,
    case when ENDPOINT like '%nsx%' then DATABASENAME else JSON_VALUE(EXTENSION, '$.databasename') end as DATABASENAME,
    case when ENDPOINT like '%nsx%' then DISPLAYNAME else JSON_VALUE(EXTENSION, '$.displayname') end as DISPLAYNAME,
    case when ENDPOINT like '%nsx%' then OCID else JSON_VALUE(EXTENSION, '$.ocid') end as OCID
from
    SRC_SMD_PDBS_TB
where
    ENDPOINT like 'netsuite-production%'
;
