~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=UPDATE_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_PROC
table=ON_DEMAND_AL_DYNAMICECPU_REPORT_TB
~~~END_MAIN_SECTION~~~

~~~BEGIN_VERSION_SECTION~~~
[VERSION]
version=2025-11-24
[SQL_SCRIPT___DEFAULT]
create or replace PROCEDURE            "UPDATE_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_PROC" 
    AUTHID CURRENT_USER
IS
BEGIN
    dbms_output.put_line('Procedure UPDATE_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_PROC started.');
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP';
        dbms_output.put_line('Dropped table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL';
        dbms_output.put_line('Dropped table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL doesn''t exists.');
    END;
    COMMIT;
    EXECUTE IMMEDIATE 'CREATE TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP as (

SELECT
    data.accountname,
    data.currentecpu,
    data.customecpu,
    data.defaultecpu,
    data.hightier,
    data.emptyclone,
    data.nondynamic,
    data.note,
    data.lastlogintime,
    data.metadata_region,
    data.metadata_endpoint,
    data.metadata_downloaded_timestamp_utc,
    data.boosttype,
    data.booststarttime,
    data.boostendtime,
    data.nochangespast30days,
    data.dataStorageSizeInGb,
    data.usedDataStorageSizeInGb,
    data.noStorageChangesPast30Days,
    smd_accounts.compid,
    smd_accounts.pdbid,
    smd_accounts.inactive,
    smd_accounts.region,
    smd_accounts.email,
    smd_accounts.compname,
    smd_pdbs.databasetype,
    smd_pdbs.ocid
FROM
    SMD_ACCOUNTS_LOCAL_DCID_PRODUCTION_VW smd_accounts
    INNER JOIN SMD_PDBS_PRODUCTION_VW smd_pdbs ON smd_accounts.pdbid = smd_pdbs.pdbid AND smd_accounts.region = smd_pdbs.region
    INNER JOIN SRC_AL_DYNAMICECPU_REPORT_DATA_TB data ON data.accountname = smd_accounts.compid AND data.metadata_region = smd_accounts.region
WHERE
    smd_pdbs.type = ''ATP''

    )';
    dbms_output.put_line('Created table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP using pre-defined SQL.');
    COMMIT;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_AL_DYNAMICECPU_REPORT_TB to ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL';
        dbms_output.put_line('Renamed table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB to ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP to ON_DEMAND_AL_DYNAMICECPU_REPORT_TB';
        dbms_output.put_line('Renamed table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP to ON_DEMAND_AL_DYNAMICECPU_REPORT_TB.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL';
        dbms_output.put_line('Dropped table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_DEL doesn''t exists.');
    END;
    BEGIN
        /*EXECUTE IMMEDIATE 'GRANT SELECT ON ON_DEMAND_AL_DYNAMICECPU_REPORT_TB TO USER_OTHQ';
        dbms_output.put_line('Granted user USER_OTHQ select permission on table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB.');*/
        EXECUTE IMMEDIATE 'GRANT SELECT ON ON_DEMAND_AL_DYNAMICECPU_REPORT_TB TO USER_ACCOUNT_LIFECYCLE';
        dbms_output.put_line('Granted user USER_ACCOUNT_LIFECYCLE select permission on table ON_DEMAND_AL_DYNAMICECPU_REPORT_TB.');
    END;
    /*BEGIN
        EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX INDEX_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB ON ON_DEMAND_AL_DYNAMICECPU_REPORT_TB (STYPE, PERIOD)';
        dbms_output.put_line('Created index on ON_DEMAND_AL_DYNAMICECPU_REPORT_TB.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB MODIFY (STYPE NOT NULL ENABLE)';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Column is already NOT NULL.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB MODIFY (PERIOD NOT NULL ENABLE)';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Column is already NOT NULL.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ON_DEMAND_AL_DYNAMICECPU_REPORT_TB ADD CONSTRAINT INDEX_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB PRIMARY KEY (STYPE, PERIOD)';
        dbms_output.put_line('Created constrain on ON_DEMAND_AL_DYNAMICECPU_REPORT_TB.');
    END;*/
    COMMIT;
    dbms_output.put_line('Procedure UPDATE_ON_DEMAND_AL_DYNAMICECPU_REPORT_TB_PROC finished successfully.');
END;
~~~END_VERSION_SECTION~~~
