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

  CREATE OR REPLACE EDITIONABLE PROCEDURE "OTHQ_OWN"."UPDATE_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_PROC" 
    AUTHID CURRENT_USER
IS
BEGIN
    dbms_output.put_line('Procedure UPDATE_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_PROC started.');
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP';
        dbms_output.put_line('Dropped table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL';
        dbms_output.put_line('Dropped table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL doesn''t exists.');
    END;
    COMMIT;
    EXECUTE IMMEDIATE 'CREATE TABLE ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP as (

SELECT
    STYPE,
    COUNT(DISTINCT SCOMPID) AS UNIQUE_COUNT_SCOMPID,
    SUM(DOC_COUNT) AS REQUESTS,
    TRUNC(TIMESTAMP, ''MM'') AS PERIOD
FROM
    SRC_OSEARCH_WEBSERVICESEXECUTION_RABI_TB
WHERE sAccountType = ''PRODUCTION'' and sCompId not like ''%TSTDRV%''
GROUP BY
    STYPE,
    TRUNC(TIMESTAMP, ''MM'')

    )';
    dbms_output.put_line('Created table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP using pre-defined SQL.');
    COMMIT;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB to ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL';
        dbms_output.put_line('Renamed table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB to ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP to ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB';
        dbms_output.put_line('Renamed table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP to ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL';
        dbms_output.put_line('Dropped table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_DEL doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB TO USER_OTHQ';
        dbms_output.put_line('Granted user USER_OTHQ select permission on table ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX INDEX_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB ON ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB (STYPE, PERIOD)';
        dbms_output.put_line('Created index on ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_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 ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_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 ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB ADD CONSTRAINT INDEX_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB PRIMARY KEY (STYPE, PERIOD)';
        dbms_output.put_line('Created constrain on ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB.');
    END;
    COMMIT;
    dbms_output.put_line('Procedure UPDATE_ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_SUMMARIZED_TB_PROC finished successfully.');
END;

/
