--------------------------------------------------------
--  DDL for View NLCORP_PRODUCTION_ACCOUNTS_APPLICATION_SERVER_HEALTH_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."NLCORP_PRODUCTION_ACCOUNTS_APPLICATION_SERVER_HEALTH_VW" ("PRODUCTION_ACCOUNTS_INTERNAL_ID", "ACCOUNT_ID", "ACCOUNT_DATABASE_TYPE", "DATABASE_INTERNAL_ID", "DATABASE", "DATABASE_INSTANCE", "VERSION", "DATACENTER", "DB_SIZE_IN_GB", "CUSTOMER_INTERNAL_ID", "CUSTOMER", "SERVICE_TIER", "WEB_ADDRESS", "ACQUIRED_ON", "PROVISIONED_PRODUCT", "ACTIVE_USERS", "VALUE_ANNUAL_ENTERPRISE", "VERTICAL", "V_RANK", "COMPANY_SIZE", "ANNUAL_REVENUE", "RENEWAL_RANKING", "METADATA_ACQUIRED_ON_UTC", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "REGION", "NKEY", "CUSTOMER_ID", "CUSTOMER_NAME", "INSTANCE_TYPE") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    t."PRODUCTION_ACCOUNTS_INTERNAL_ID",t."ACCOUNT_ID",t."ACCOUNT_DATABASE_TYPE",t."DATABASE_INTERNAL_ID",t."DATABASE",t."DATABASE_INSTANCE",t."VERSION",t."DATACENTER",t."DB_SIZE_IN_GB",t."CUSTOMER_INTERNAL_ID",t."CUSTOMER",t."SERVICE_TIER",t."WEB_ADDRESS",t."ACQUIRED_ON",t."PROVISIONED_PRODUCT",t."ACTIVE_USERS",t."VALUE_ANNUAL_ENTERPRISE",t."VERTICAL",t."V_RANK",t."COMPANY_SIZE",t."ANNUAL_REVENUE",t."RENEWAL_RANKING",t."METADATA_ACQUIRED_ON_UTC",t."METADATA_DOWNLOADED_TIMESTAMP_UTC",t."REGION",
    LTRIM(REGEXP_REPLACE(t.DATABASE, '^DB', ''), '0') AS NKEY,
    SUBSTR(t.CUSTOMER, 1, INSTR(t.CUSTOMER || ' ', ' ') - 1) AS CUSTOMER_ID,
    LTRIM(SUBSTR(t.CUSTOMER, INSTR(t.CUSTOMER, ' ') + 1), ' ') AS CUSTOMER_NAME,
    CASE
        WHEN LOWER(t.DATABASE_INSTANCE) LIKE '%_tp.adb_%' THEN 'ATP'
        ELSE 'ExaCS'
    END AS INSTANCE_TYPE
FROM
    SRC_NLCORP_PRODUCTION_ACCOUNTS_APPLICATION_SERVER_HEALTH_TB t
;