CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."CMDB_NEXT_ATP_LIST_VW" 
(
    "ID",
    "INSTANCE_TYPE",
    "MOLECULE",
    "PRODUCT",
    "STATUS",
    "LOGICAL_DATA_CENTER",
    "COMPARTMENT_ID",
    "COMPARTMENT",
    "REGION",
    "TENANCY",
    "AVAILABILITY_DOMAIN",
    "IP_ADDRESSES",
    "OCPU_COUNT",
    "ECPU_COUNT",
    "NSX",
    "NSN",
    "ACCOUNT_COMP_ID",
    "ACCOUNT_TYPE",
    "COMP_ID",
    "IP_ADDRESS",
    "REGION_SHORT_NAME",
    "DELETED",
    "DELETED_BY_CASCADE",
    "CREATED",
    "MODIFIED",
    "HOSTNAME",
    "OCI_NAME",
    "PROVISIONER",
    "TIME_MAINTENANCE_REBOOT_DUE",
    "IN_MONITORING",
    "BACKUP_BUCKET_NAME",
    "BACKUP_TYPE",
    "PRIVATE_ENDPOINT",
    "CPU_CORE_COUNT",
    "DATA_STORAGE_SIZE",
    "DATAGUARD_REGION_TYPE",
    "DB_NAME",
    "DB_TYPE",
    "VERSION",
    "AUTO_SCALING_ENABLED",
    "INSTANCE_NAME",
    "EPOD",
    "DATA_GUARD_ENABLED",
    "DEDICATED",
    "TIME_MAINTENANCE_BEGIN",
    "TIME_MAINTENANCE_END",
    "ACTUAL_STORAGE",
    "ALLOCATED_STORAGE",
    "COMPUTE_MODEL",
    "IS_HIPAA",
    "DR_SOLUTION_NAME",
    "LOCAL_DR_TYPE",
    "REMOTE_DR_TYPE",
    "APP_LABEL",
    "INSTANCE_SHAPE",
    "INTERNAL_AVAILABILITY_DOMAIN",
    "CAPACITY_RESERVATION",
    "N_KEY"   
)
DEFAULT COLLATION "USING_NLS_COMP"
AS
SELECT
    t."ID",
    t."INSTANCE_TYPE",
    t."MOLECULE",
    t."PRODUCT",
    t."STATUS",
    t."LOGICAL_DATA_CENTER",
    t."COMPARTMENT_ID",
    t."COMPARTMENT",
    t."REGION",
    t."TENANCY",
    t."AVAILABILITY_DOMAIN",
    t."IP_ADDRESSES",
    t."OCPU_COUNT",
    t."ECPU_COUNT",
    t."NSX",
    t."NSN",
    t."ACCOUNT_COMP_ID",
    t."ACCOUNT_TYPE",
    t."COMP_ID",
    t."IP_ADDRESS",
    t."REGION_SHORT_NAME",
    t."DELETED",
    t."DELETED_BY_CASCADE",
    t."CREATED",
    t."MODIFIED",
    t."HOSTNAME",
    t."OCI_NAME",
    t."PROVISIONER",
    t."TIME_MAINTENANCE_REBOOT_DUE",
    t."IN_MONITORING",
    t."BACKUP_BUCKET_NAME",
    t."BACKUP_TYPE",
    t."PRIVATE_ENDPOINT",
    t."CPU_CORE_COUNT",
    t."DATA_STORAGE_SIZE",
    t."DATAGUARD_REGION_TYPE",
    t."DB_NAME",
    t."DB_TYPE",
    t."VERSION",
    t."AUTO_SCALING_ENABLED",
    t."INSTANCE_NAME",
    t."EPOD",
    t."DATA_GUARD_ENABLED",
    t."DEDICATED",
    t."TIME_MAINTENANCE_BEGIN",
    t."TIME_MAINTENANCE_END",
    t."ACTUAL_STORAGE",
    t."ALLOCATED_STORAGE",
    t."COMPUTE_MODEL",
    t."IS_HIPAA",
    t."DR_SOLUTION_NAME",
    t."LOCAL_DR_TYPE",
    t."REMOTE_DR_TYPE",
    t."APP_LABEL",
    t."INSTANCE_SHAPE",
    t."INTERNAL_AVAILABILITY_DOMAIN",
    t."CAPACITY_RESERVATION",

    CASE 
        WHEN t."ACCOUNT_TYPE" IN ('PRODUCTION', 'SANDBOX')
        THEN TO_NUMBER(REGEXP_REPLACE(t."DB_NAME", '[^0-9]', ''))
        ELSE NULL
    END AS "N_KEY"

FROM
    "OTHQ_OWN"."SRC_CMDB_NEXT_ATP_LIST_TB" t;