CREATE OR REPLACE PROCEDURE UPDATE_ON_DEMAND_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB_PROC AUTHID CURRENT_USER
IS
    -- BEGIN MODIFY THIS --
    TABLE_NAME VARCHAR2(128) := 'ON_DEMAND_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB';
    USER_SCHEMA VARCHAR2(128) := 'USER_PLATFORM_INTEGRATION';
    CODE_BLOCK VARCHAR2(32767) := '

SELECT
    web.*,
    smd_accounts.compname AS smd_accounts_company_name,
    smd_accounts.email    AS smd_accounts_email,
    active_customer       AS nlcorp_mcp_active_customer,
    zoominfo_industry     AS nlcorp_mcp_zoominfo_industry,
    zoominfo_subindustry  AS nlcorp_mcp_zoominfo_subindustry,
    naics                 AS nlcorp_mcp_naics,
    CASE
        WHEN web2.distinct_count = 0  THEN
            ''0''
        WHEN web2.distinct_count = 1  THEN
            ''1''
        WHEN web2.distinct_count = 2  THEN
            ''2''
        WHEN web2.distinct_count < 6  THEN
            ''3-5''
        WHEN web2.distinct_count < 11 THEN
            ''6-10''
        ELSE
            ''>10''
    END          AS sintegrationapplicationname_category
FROM
    ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB web
    LEFT JOIN (
        SELECT
            scompid,
            COUNT(DISTINCT sintegrationapplicationname) AS distinct_count
        FROM
            ARCHIVE_OSEARCH_WEBSERVICESEXECUTION_RABI_DAILY_TB
        GROUP BY
            scompid
    ) web2 ON web.scompid = web2.scompid
    LEFT JOIN SMD_ACCOUNTS_LOCAL_DCID_PRODUCTION_VW smd_accounts ON web.scompid = smd_accounts.compid
    LEFT JOIN NLCORP_MCP_CUSTOMER_CONSOLIDATED_VW nlcorp_mcp ON web.scompid = nlcorp_mcp.par_account

    ';
    -- END MODIFY THIS --
BEGIN
    dbms_output.put_line('INFO: Procedure UPDATE_' || TABLE_NAME || '_PROC started');

    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_TMP';
        dbms_output.put_line('INFO: Dropped table ' || TABLE_NAME || '_TMP');
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_TMP does not exist');
    END;

    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_DEL';
        dbms_output.put_line('INFO: Dropped table ' || TABLE_NAME || '_DEL');
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_DEL does not exist');
    END;

    EXECUTE IMMEDIATE 'CREATE TABLE ' || TABLE_NAME || '_TMP AS (' || CODE_BLOCK || ')';
    dbms_output.put_line('INFO: Created table ' || TABLE_NAME || '_TMP');

    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || TABLE_NAME || ' RENAME TO ' || TABLE_NAME || '_DEL';
        dbms_output.put_line('INFO: Renamed table ' || TABLE_NAME || ' to ' || TABLE_NAME || '_DEL');
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('WARNING: Table ' || TABLE_NAME || ' does not exist');
    END;

    EXECUTE IMMEDIATE 'ALTER TABLE ' || TABLE_NAME || '_TMP RENAME TO ' || TABLE_NAME;
    dbms_output.put_line('INFO: Renamed table ' || TABLE_NAME || '_TMP to ' || TABLE_NAME);

    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_DEL';
        dbms_output.put_line('Dropped table ' || TABLE_NAME || '_DEL');
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_DEL does not exist');
    END;

    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || TABLE_NAME || ' TO ' || USER_SCHEMA;
    dbms_output.put_line('INFO: Granted SELECT on ' || TABLE_NAME || ' to ' || USER_SCHEMA);

    dbms_output.put_line('INFO: Procedure UPDATE_' || TABLE_NAME || '_PROC finished successfully');
END;