--------------------------------------------------------
--  DDL for View MMD_NETSUITE_RELEASE_RETRIEVEMAINTENANCEINFO_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."MMD_NETSUITE_RELEASE_RETRIEVEMAINTENANCEINFO_VW" ("NKEY", "METADATA_REGION", "METADATA_ENDPOINT", "DATABASEID", "MIGRATEVERSION", "PMRID", "DATACENTER", "ACCOUNTNAME", "DATABASETYPE", "DATABASEHOST", "STAGE", "STATUS", "TARGETMAINSTARTTIME", "DATABASEOFFLINETIMESTAMPINUTC", "DATABASEONLINETIMESTAMPINUTC", "COLDMIGRATESTART", "COLDMIGRATEEND", "PLANNEDCUSTOMERDOWNTIME", "PREPSTEPDURATION", "MAINSTEPDURATION", "COLDMIGRATEDURATION", "POSTMIGRATEDURATION", "ERRORTEXT", "PREPSTAGEERRORMESSAGE", "MAINSTAGEERRORMESSAGE", "POSTSTAGEERRORMESSAGE", "ROLLBACKSTAGEERRORMESSAGE", "ROLLFORWARDSTAGEERRORMESSAGE", "CANCELSTAGEERRORMESSAGE", "ORA_CODES", "ERROR_CLASS", "ANALYZED_TEXT", "SOURCECDBUNIQUENAME", "TARGETCDBUNIQUENAME", "ISPMRSTATUSSYNCED", "ISVALIDANALYTICSROW", "HARDWARETYPE", "METADATA_SOURCE", "REGION", "ENDPOINT", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "DOWNTIME_MINUTES", "UPGRADE_TYPE2", "UPGRADE_TYPE", "STATUS_ORDER", "TARGETMAINSTARTTIME_PT", "ERRORMESSAGES") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    CAST(s_nkey AS NUMBER(38, 0))                 AS nkey,
    metadata_region                               AS metadata_region,
    metadata_endpoint                             AS metadata_endpoint,
    CAST(NULL AS VARCHAR2(4))                     AS databaseid,
    CAST(s_upgradeversion AS VARCHAR2(16))        AS migrateversion,
    CAST(s_pmrid AS VARCHAR2(16))                 AS pmrid,
    s_dcid                                        AS datacenter,
    s_sourcevalue                                 AS accountname,
    CAST(NULL AS VARCHAR2(4))                     AS databasetype,
    CAST(NULL AS VARCHAR2(4))                     AS databasehost,
    CAST(NULL AS VARCHAR2(4))                     AS stage,
    s_status                                      AS status,
    s_plannedexecution                            AS targetmainstarttime,
    CAST(NULL AS DATE)                            AS databaseofflinetimestampinutc,
    CAST(NULL AS DATE)                            AS databaseonlinetimestampinutc,
    CAST(NULL AS DATE)                            AS coldmigratestart,
    CAST(NULL AS DATE)                            AS coldmigrateend,
    CAST(s_downtimeinminutes AS NUMBER(38, 0))    AS plannedcustomerdowntime,
    CAST(NULL AS NUMBER(38, 0))                   AS prepstepduration,
    CAST(NULL AS NUMBER(38, 0))                   AS mainstepduration,
    CAST(NULL AS NUMBER(38, 0))                   AS coldmigrateduration,
    CAST(NULL AS NUMBER(38, 0))                   AS postmigrateduration,
    s_errorfailureinformation                     AS errortext,
    CAST(NULL AS VARCHAR2(4))                     AS prepstageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS mainstageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS poststageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS rollbackstageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS rollforwardstageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS cancelstageerrormessage,
    CAST(NULL AS VARCHAR2(4))                     AS ora_codes,
    CAST(NULL AS VARCHAR2(4))                     AS error_class,
    CAST(NULL AS VARCHAR2(4))                     AS analyzed_text,
    CAST(NULL AS VARCHAR2(4))                     AS sourcecdbuniquename,
    CAST(NULL AS VARCHAR2(4))                     AS targetcdbuniquename,
    CAST(NULL AS VARCHAR2(4))                     AS ispmrstatussynced,
    CAST(NULL AS VARCHAR2(4))                     AS isvalidanalyticsrow,
    CAST(NULL AS VARCHAR2(4))                     AS hardwaretype,
-- new columns
    'MMD'                                         AS metadata_source,
    metadata_region                               AS region,
    metadata_endpoint                             AS endpoint,
    METADATA_DOWNLOADED_TIMESTAMP_UTC,
    CAST(NULL AS NUMBER)                          AS downtime_minutes,
    CASE
        WHEN b_maintenancetype LIKE '%Pre-Migrate%' THEN
            'NSN Pre-Migrate'
        WHEN b_maintenancetype LIKE '%Major Version Upgrade%' THEN
            'NSN MVU'
        ELSE
            'Undefined B_MAINTENANCETYPE'
    END                                           AS upgrade_type2,
    CAST(NULL AS VARCHAR2(4))                     AS upgrade_type,
    '1_' || s_status                              AS status_order,
    CAST(from_tz(CAST(s_plannedexecution AS TIMESTAMP),
                 'UTC') AT TIME ZONE 'US/Pacific' AS DATE) AS targetmainstarttime_pt,
    CAST(NULL AS VARCHAR2(4))                     AS errormessages
FROM
    src_mmd_netsuite_release_retrievemaintenanceinfo_tb
;
