--------------------------------------------------------
--  DDL for Procedure UPDATE_ON_DEMAND_CSM_NETSUITE_RELEASE_TB_PROC
--------------------------------------------------------
set define off;

  CREATE OR REPLACE EDITIONABLE PROCEDURE "OTHQ_OWN"."UPDATE_ON_DEMAND_CSM_NETSUITE_RELEASE_TB_PROC" 
    AUTHID CURRENT_USER
IS
BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_CSM_NETSUITE_RELEASE_TB_TMP';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_CSM_NETSUITE_RELEASE_TB_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB_DEL doesn''t exists.');
    END;
    COMMIT;
    EXECUTE IMMEDIATE 'CREATE TABLE ON_DEMAND_CSM_NETSUITE_RELEASE_TB_TMP as (
    
SELECT *
FROM
(
    WITH CSM_MMD_SMD AS
    (
        SELECT
            T.*,
            -- new columns
            ROW_NUMBER() OVER (PARTITION BY NVL(TO_CHAR(SMD.PDBID), ''Not found in SMD''), T.MIGRATEVERSION, T.UPGRADE_TYPE2 ORDER BY T.NKEY DESC, T.STATUS_ORDER DESC, T.TARGETMAINSTARTTIME DESC) AS ROW_NUMBER,
            NVL(CASE WHEN SMD.DATABASETYPE LIKE ''%DEMO%'' THEN ''DEMO'' ELSE SMD.DATABASETYPE END, ''Not found in SMD'') AS DATABASETYPE_SMD_CONSOLIDATED,
            NVL(SMD.COMPID, ''Not found in SMD'') AS ACCOUNTNAME_SMD_CONSOLIDATED,
            NVL(TO_CHAR(SMD.PDBID), ''Not found in SMD'') AS PDBID_SMD_CONSOLIDATED,
            DENSE_RANK() OVER (PARTITION BY T.UPGRADE_TYPE2 ORDER BY T.MIGRATEVERSION) AS MIGRATEVERSION_SEQUENCE,
            DENSE_RANK() OVER (PARTITION BY T.UPGRADE_TYPE2 ORDER BY T.MIGRATEVERSION desc) AS MIGRATEVERSION_SEQUENCE_DESC
        FROM
        (
            SELECT * FROM CSM_NETSUITE_RELEASE_VW
            UNION ALL
            SELECT * FROM MMD_NETSUITE_RELEASE_RETRIEVEMAINTENANCEINFO_VW
        ) T
        LEFT JOIN
        (
            SELECT ACCOUNTS.COMPID, ACCOUNTS.PDBID, ACCOUNTS.REGION, PDBS.DATABASETYPE
            FROM SMD_ACCOUNTS_PRODUCTION_VW ACCOUNTS
            LEFT JOIN SMD_PDBS_PRODUCTION_VW PDBS
            ON ACCOUNTS.REGION = PDBS.REGION AND ACCOUNTS.PDBID = PDBS.PDBID
        ) SMD
        ON T.METADATA_REGION = SMD.REGION AND T.ACCOUNTNAME = SMD.COMPID
    )
    SELECT
        R.*,
        -- new columns
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN SUM(R.DOWNTIME_MINUTES) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_DOWNTIME,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN SUM(CASE WHEN R.DOWNTIME_MINUTES > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_RECORDS_WITH_DOWNTIME,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN COUNT(*) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_RECORDS,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN COUNT(DISTINCT(TARGETMAINSTARTTIME)) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_UNIQUE_TARGETMAINSTARTTIME,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN COUNT(DISTINCT(PMRID)) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_UNIQUE_PMRS,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN SUM(CASE WHEN R.STATUS IN (''ROLLED_BACK'', ''FAILED'') THEN 1 ELSE 0 END) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_RECORDS_WITH_ROLLEDBACK_OR_FAILED,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN SUM(CASE WHEN R.STATUS IN (''CANCELLED'') THEN 1 ELSE 0 END) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_RECORDS_WITH_CANCELLED,
        CASE WHEN R.ROW_NUMBER = 1 AND R.METADATA_SOURCE = ''CSM'' THEN SUM(CASE WHEN R.DOWNTIME_MINUTES > 120 THEN 1 ELSE 0 END) OVER (PARTITION BY R.DATABASEID, R.MIGRATEVERSION, R.UPGRADE_TYPE2) ELSE NULL END AS TOTAL_RECORDS_WITH_DOWNTIME_GT_120
    FROM CSM_MMD_SMD R
)
WHERE MIGRATEVERSION_SEQUENCE_DESC <= 6
    
    )';
    COMMIT;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_CSM_NETSUITE_RELEASE_TB to ON_DEMAND_CSM_NETSUITE_RELEASE_TB_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_CSM_NETSUITE_RELEASE_TB_TMP to ON_DEMAND_CSM_NETSUITE_RELEASE_TB';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_CSM_NETSUITE_RELEASE_TB_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB_DEL doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_CSM_NETSUITE_RELEASE_TB TO USER_NETSUITE_RELEASE';
        dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB grant select on USER_NETSUITE_RELEASE.');
        EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_CSM_NETSUITE_RELEASE_TB TO USER_RR';
        dbms_output.put_line('Table ON_DEMAND_CSM_NETSUITE_RELEASE_TB grant select on USER_RR.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX INDEX_CUSTOM_ON_DEMAND_CSM_NETSUITE_RELEASE_TB_001 ON ON_DEMAND_CSM_NETSUITE_RELEASE_TB (NKEY, METADATA_REGION, METADATA_ENDPOINT, DATABASEID, MIGRATEVERSION, PMRID, DATACENTER, ACCOUNTNAME, DATABASETYPE, DATABASEHOST, STAGE, STATUS, TARGETMAINSTARTTIME, DATABASEOFFLINETIMESTAMPINUTC, DATABASEONLINETIMESTAMPINUTC, COLDMIGRATESTART, COLDMIGRATEEND, PLANNEDCUSTOMERDOWNTIME, PREPSTEPDURATION, COLDMIGRATEDURATION, POSTMIGRATEDURATION, ORA_CODES, ERROR_CLASS, ANALYZED_TEXT, DOWNTIME_MINUTES, UPGRADE_TYPE2, STATUS_ORDER, ROW_NUMBER, DATABASETYPE_SMD_CONSOLIDATED, ACCOUNTNAME_SMD_CONSOLIDATED, MIGRATEVERSION_SEQUENCE)';
        dbms_output.put_line('Created index on ON_DEMAND_CSM_NETSUITE_RELEASE_TB.');
    END
    COMMIT;
    dbms_output.put_line('>>> RECREATING ON_DEMAND_CSM_NETSUITE_RELEASE_TB DONE <<<');
END;

/
