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

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

SELECT
    solutions.KEY AS solutions_KEY,
    solutions.NAME AS solutions_NAME,
    solutions.CREATED AS solutions_CREATED,
    solutions.UPDATED AS solutions_UPDATED,
    JSON_VALUE(solutions.OWNER, ''$.user_key'') AS solutions_USER_KEY,
    JSON_VALUE(solutions.OWNER, ''$.user_displayname'') AS solutions_USER_DISPLAYNAME,
    solutions.OBJECTTYPE AS solutions_OBJECTTYPE,
    solutions.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME AS SNAPSHOT_DATE,
    solutions.DESCRIPTION AS solutions_DESCRIPTION,
    solutions.STATUS as solutions_STATUS,
    solutions.SCOPE_DEFINED as solutions_SCOPE_DEFINED,
    solutions.EXPECTED_COMPLETION_PERIOD as solutions_EXPECTED_COMPLETION_PERIOD,
    solutions.INACTIVE as solutions_INACTIVE,
    cp.NAME as solutions_CP_NAME,
    cp.OBJECTTYPE as solutions_CP_OBJECTTYPE,
    cp.PERIOD_START as solutions_CP_PERIOD_START,
    cp.PERIOD_END as solutions_CP_PERIOD_END,
    vs.VS_NAME as VS_ON_SOLUTION_NAME,
    vs.VS_OWNER_NAME as VS_OWNER_ON_SOLUTION_NAME,

    objectives.KEY AS objectives_KEY,
    objectives.NAME AS objectives_NAME,
    objectives.CREATED AS objectives_CREATED,
    objectives.UPDATED AS objectives_UPDATED,
    JSON_VALUE(objectives.OWNER, ''$.user_key'') AS objectives_USER_KEY,
    JSON_VALUE(objectives.OWNER, ''$.user_displayname'') AS objectives_USER_DISPLAYNAME,
    objectives.OBJECTTYPE AS objectives_OBJECTTYPE,
    objectives.OBJECTSUBTYPE AS objectives_OBJECTSUBTYPE,
    objectives.COMPLETED AS objectives_COMPLETED,
    objectives.DESCRIPTION AS objectives_DESCRIPTION,
    objectives.INACTIVE AS objectives_INACTIVE,
    cp3.NAME AS objectives_EXPECTED_RELEASE_PERIOD,
    objectives.EXPECTED_RELEASE_PERIOD AS objectives_EXPECTED_RELEASE_PERIOD_KEY,
    cp3.PERIOD_START AS objectives_EXPECTED_RELEASE_PERIOD_START,
    cp3.PERIOD_END AS objectives_EXPECTED_RELEASE_PERIOD_END,

    milestones.KEY AS milestones_KEY,
    milestones.NAME AS milestones_NAME,
    milestones.CREATED AS milestones_CREATED,
    milestones.UPDATED AS milestones_UPDATED,
    JSON_VALUE(milestones.OWNER, ''$.user_key'') AS milestones_USER_KEY,
    JSON_VALUE(milestones.OWNER, ''$.user_displayname'') AS milestones_USER_DISPLAYNAME,
    milestones.OBJECTTYPE AS milestones_OBJECTTYPE,
    milestones.OBJECTSUBTYPE AS milestones_OBJECTSUBTYPE,
    milestones.COMPLETED AS milestones_COMPLETED,
    milestones.DESCRIPTION AS milestones_DESCRIPTION,
    milestones.EXPECTED_COMPLETION_PERIOD as milestones_EXPECTED_COMPLETION_PERIOD,
    milestones.INACTIVE as milestones_INACTIVE,
    cp2.NAME as milestones_CP_NAME,
    cp2.OBJECTTYPE as milestones_CP_OBJECTTYPE,
    cp2.PERIOD_START as milestones_CP_PERIOD_START,
    cp2.PERIOD_END as milestones_CP_PERIOD_END,

    strategic_initiative.KEY AS strategic_initiative_KEY,
    strategic_initiative.NAME AS strategic_initiative_NAME,
    strategic_initiative.CREATED AS strategic_initiative_CREATED,
    strategic_initiative.UPDATED AS strategic_initiative_UPDATED,
    JSON_VALUE(strategic_initiative.OWNER, ''$.user_key'') AS strategic_initiative_USER_KEY,
    JSON_VALUE(strategic_initiative.OWNER, ''$.user_displayname'') AS strategic_initiative_USER_DISPLAYNAME,
    strategic_initiative.OBJECTTYPE AS strategic_initiative_OBJECTTYPE,
    strategic_initiative.OBJECTSUBTYPE AS strategic_initiative_OBJECTSUBTYPE,
    strategic_initiative.COMPLETED AS strategic_initiative_COMPLETED,
    strategic_initiative.DESCRIPTION AS strategic_initiative_DESCRIPTION,
    strategic_initiative.INACTIVE AS strategic_initiative_INACTIVE,

    strategic_priority.KEY AS strategic_priority_KEY,
    strategic_priority.NAME AS strategic_priority_NAME,
    strategic_priority.CREATED AS strategic_priority_CREATED,
    strategic_priority.UPDATED AS strategic_priority_UPDATED,
    JSON_VALUE(strategic_priority.OWNER, ''$.user_key'') AS strategic_priority_USER_KEY,
    JSON_VALUE(strategic_priority.OWNER, ''$.user_displayname'') AS strategic_priority_USER_DISPLAYNAME,
    strategic_priority.OBJECTTYPE AS strategic_priority_OBJECTTYPE,
    strategic_priority.OBJECTSUBTYPE AS strategic_priority_OBJECTSUBTYPE,
    strategic_priority.COMPLETED AS strategic_priority_COMPLETED,
    strategic_priority.DESCRIPTION AS strategic_priority_DESCRIPTION,
    strategic_priority.INACTIVE AS strategic_priority_INACTIVE,

    issues_to_assets.SOURCE AS issue_key
FROM
    (
    SELECT SOURCE,
            ISSUETYPE,
            FIELD,
            DESTINATION,
            SNAPSHOT_DATETIME
        FROM USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ORIGINAL_MAPPING_TB
        WHERE FIELD = ''Solution''
            AND ISSUETYPE IN (''New Feature'',
                                ''Defect'',
                                ''Minor Enhancement'',
                                ''Discovery'')
    ) issues_to_assets
LEFT JOIN
    (
        SELECT
            OBJECTTYPE, KEY, NAME, CREATED, UPDATED, OWNER, VALUE_STREAM,
            BUSINESS_OBJECTIVE, OBJECTIVE, TARGET, INACTIVE, COMPLETED,
            PARENT_OBJECTIVE, QUARTER, DESCRIPTION, VALUE_SUB_STREAM, ORG_AREA,
            JIRA_ENABLEMENT_REPRESENTATIVE, DETAILS, NSCORP_RECORD_URL, WORK_AREA,
            BUSINESS_DETAILS, MORE_DETAILED_OUTCOMES, URL,
            FISCAL_YEAR, BASELINE,
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
            PROJECT, OBJECTSUBTYPE, TARGET_DATE, STATUS, PRODUCT_TYPE,
            PARENT_PRODUCT, CAPABILITY_PORTING, INTEGRATION_DEADLINE,
            RELEASE_TYPE, PERIOD_START, PERIOD_END, EXPECTED_COMPLETION_PERIOD, SCOPE_DEFINED
        FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
        WHERE OBJECTTYPE = ''Solution''
    ) solutions
    ON issues_to_assets.DESTINATION = solutions.KEY 
    AND TRUNC(issues_to_assets.SNAPSHOT_DATETIME) = solutions.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
LEFT JOIN
   (  SELECT
        OBJECTTYPE,
        KEY,
        NAME,
        INACTIVE,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
        PERIOD_START,
        PERIOD_END
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
    WHERE
        ObjectType IN (''Fiscal Year'', ''Fiscal Quarter'', ''Month'', ''Special'')
    ) cp
    ON solutions.EXPECTED_COMPLETION_PERIOD = cp.KEY
    AND TRUNC(solutions.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) = TRUNC(cp.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME)

LEFT JOIN
    (SELECT SOURCE,
            OBJECTTYPE,
            OBJECTSUBTYPE,
            FIELD,
            SNAPSHOT_DATETIME,
            DESTINATION,
            LVL
        FROM USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB
        WHERE objectType = ''Solution''
            AND field = ''Objective''
    ) solutions_mapping
    ON solutions.KEY = solutions_mapping.SOURCE
    AND solutions.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = TRUNC(solutions_mapping.SNAPSHOT_DATETIME)
LEFT JOIN
    (
        SELECT
            OBJECTTYPE, KEY, NAME, CREATED, UPDATED, OWNER, VALUE_STREAM,
            BUSINESS_OBJECTIVE, OBJECTIVE, TARGET, INACTIVE, COMPLETED,
            PARENT_OBJECTIVE, QUARTER, DESCRIPTION, VALUE_SUB_STREAM, ORG_AREA,
            JIRA_ENABLEMENT_REPRESENTATIVE, DETAILS, NSCORP_RECORD_URL, WORK_AREA,
            BUSINESS_DETAILS, MORE_DETAILED_OUTCOMES, WHY_ITS_IMPORTANT, URL,
            FISCAL_YEAR, BASELINE,
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
            PROJECT, OBJECTSUBTYPE, TARGET_DATE, STATUS, PRODUCT_TYPE,
            PARENT_PRODUCT, CAPABILITY_PORTING, INTEGRATION_DEADLINE, EXPECTED_RELEASE_PERIOD, 
            RELEASE_TYPE, PERIOD_START, PERIOD_END
        FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
        WHERE OBJECTTYPE = ''Objective''
            AND OBJECTSUBTYPE IS NULL
    ) objectives
    ON solutions_mapping.DESTINATION = objectives.KEY
    AND TRUNC(solutions_mapping.SNAPSHOT_DATETIME) = objectives.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME

LEFT JOIN
    (SELECT SOURCE,
            OBJECTTYPE,
            OBJECTSUBTYPE,
            FIELD,
            SNAPSHOT_DATETIME,
            DESTINATION,
            LVL
        FROM USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB
        WHERE objectType = ''Objective''
            AND objectSubType IS NULL
    ) objectives_mapping
    ON objectives.KEY = objectives_mapping.SOURCE
    AND objectives.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = TRUNC(objectives_mapping.SNAPSHOT_DATETIME)

LEFT JOIN
    (  SELECT
        OBJECTTYPE,
        KEY,
        NAME,
        INACTIVE,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
        PERIOD_START,
        PERIOD_END
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
    WHERE
        ObjectType IN (''Fiscal Year'', ''Fiscal Quarter'', ''Month'', ''Special'')
    ) cp3
    ON objectives.EXPECTED_RELEASE_PERIOD = cp3.KEY
    AND TRUNC(objectives.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) = TRUNC(cp3.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME)

JOIN
    (
        SELECT
            OBJECTTYPE, KEY, NAME, CREATED, UPDATED, OWNER, VALUE_STREAM,
            BUSINESS_OBJECTIVE, OBJECTIVE, TARGET, INACTIVE, COMPLETED,
            PARENT_OBJECTIVE, QUARTER, DESCRIPTION, VALUE_SUB_STREAM, ORG_AREA,
            JIRA_ENABLEMENT_REPRESENTATIVE, DETAILS, NSCORP_RECORD_URL, WORK_AREA,
            BUSINESS_DETAILS, MORE_DETAILED_OUTCOMES, WHY_ITS_IMPORTANT, URL,
            FISCAL_YEAR, BASELINE,
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
            PROJECT, OBJECTSUBTYPE, TARGET_DATE, STATUS, PRODUCT_TYPE,
            PARENT_PRODUCT, CAPABILITY_PORTING, INTEGRATION_DEADLINE,
            RELEASE_TYPE, PERIOD_START, PERIOD_END, EXPECTED_COMPLETION_PERIOD
        FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
        WHERE OBJECTTYPE = ''Objective''
            AND OBJECTSUBTYPE = ''Milestone''
    ) milestones
    ON objectives_mapping.DESTINATION = milestones.KEY
    AND TRUNC(objectives_mapping.SNAPSHOT_DATETIME) = milestones.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
    
LEFT JOIN
   (  SELECT
        OBJECTTYPE,
        KEY,
        NAME,
        INACTIVE,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
        PERIOD_START,
        PERIOD_END
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
    WHERE
        ObjectType IN (''Fiscal Year'', ''Fiscal Quarter'', ''Month'', ''Special'')
    ) cp2
    ON milestones.EXPECTED_COMPLETION_PERIOD = cp2.KEY
    AND TRUNC(milestones.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) = TRUNC(cp2.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME)
    
JOIN
    (SELECT SOURCE,
            OBJECTTYPE,
            OBJECTSUBTYPE,
            FIELD,
            SNAPSHOT_DATETIME,
            DESTINATION,
            LVL
        FROM USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB
        WHERE objectType = ''Objective''
            AND objectSubType = ''Milestone''
    ) milestones_mapping
    ON milestones.KEY = milestones_mapping.SOURCE
    AND milestones.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = TRUNC(milestones_mapping.SNAPSHOT_DATETIME)
JOIN
    (
        SELECT
            OBJECTTYPE, KEY, NAME, CREATED, UPDATED, OWNER, VALUE_STREAM,
            BUSINESS_OBJECTIVE, OBJECTIVE, TARGET, INACTIVE, COMPLETED,
            PARENT_OBJECTIVE, QUARTER, DESCRIPTION, VALUE_SUB_STREAM, ORG_AREA,
            JIRA_ENABLEMENT_REPRESENTATIVE, DETAILS, NSCORP_RECORD_URL, WORK_AREA,
            BUSINESS_DETAILS, MORE_DETAILED_OUTCOMES, WHY_ITS_IMPORTANT, URL,
            FISCAL_YEAR, BASELINE,
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
            PROJECT, OBJECTSUBTYPE, TARGET_DATE, STATUS, PRODUCT_TYPE,
            PARENT_PRODUCT, CAPABILITY_PORTING, INTEGRATION_DEADLINE,
            RELEASE_TYPE, PERIOD_START, PERIOD_END
        FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
        WHERE OBJECTTYPE = ''Objective''
            AND OBJECTSUBTYPE = ''Strategic Initiative''
    ) strategic_initiative
    ON milestones_mapping.DESTINATION = strategic_initiative.KEY
    AND TRUNC(milestones_mapping.SNAPSHOT_DATETIME) = strategic_initiative.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
JOIN
    (SELECT SOURCE,
            OBJECTTYPE,
            OBJECTSUBTYPE,
            FIELD,
            SNAPSHOT_DATETIME,
            DESTINATION,
            LVL
        FROM USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB
        WHERE objectType = ''Objective''
            AND objectSubType = ''Strategic Initiative''
    ) strategic_initiative_mapping
    ON strategic_initiative.KEY = strategic_initiative_mapping.SOURCE
    AND strategic_initiative.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = TRUNC(strategic_initiative_mapping.SNAPSHOT_DATETIME)
JOIN
    (
        SELECT
            OBJECTTYPE, KEY, NAME, CREATED, UPDATED, OWNER, VALUE_STREAM,
            BUSINESS_OBJECTIVE, OBJECTIVE, TARGET, INACTIVE, COMPLETED,
            PARENT_OBJECTIVE, QUARTER, DESCRIPTION, VALUE_SUB_STREAM, ORG_AREA,
            JIRA_ENABLEMENT_REPRESENTATIVE, DETAILS, NSCORP_RECORD_URL, WORK_AREA,
            BUSINESS_DETAILS, MORE_DETAILED_OUTCOMES, WHY_ITS_IMPORTANT, URL,
            FISCAL_YEAR, BASELINE,
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
            PROJECT, OBJECTSUBTYPE, TARGET_DATE, STATUS, PRODUCT_TYPE,
            PARENT_PRODUCT, CAPABILITY_PORTING, INTEGRATION_DEADLINE,
            RELEASE_TYPE, PERIOD_START, PERIOD_END
        FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
        WHERE OBJECTTYPE = ''Objective''
            AND OBJECTSUBTYPE = ''Strategic Priority''
    ) strategic_priority
    ON strategic_initiative_mapping.DESTINATION = strategic_priority.KEY
    AND TRUNC(strategic_initiative_mapping.SNAPSHOT_DATETIME) = strategic_priority.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
LEFT OUTER JOIN
    (SELECT
        SOURCE,
        OBJECTTYPE,
        FIELD,
        DESTINATION,
        TRUNC(SNAPSHOT_DATETIME) AS SNAPSHOT_DATE
    FROM
        USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB
    WHERE
        ObjectType = ''Solution'' AND
        Field = ''Value-Stream''
    ) solution_to_vs
ON
    solutions.KEY = solution_to_vs.SOURCE AND
    solutions.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = solution_to_vs.SNAPSHOT_DATE
LEFT OUTER JOIN
    (SELECT
        OBJECTTYPE,
        ORG_AREA,
        KEY AS VS_KEY,
        NAME AS VS_NAME,
        JSON_VALUE(OWNER, ''$.user_displayname'') AS VS_OWNER_NAME,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS SNAPSHOT_DATE
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
    WHERE
        ObjectType = ''Value Stream''
    ) vs
    
ON 
    solution_to_vs.DESTINATION = vs.VS_KEY AND
    solution_to_vs.SNAPSHOT_DATE = vs.SNAPSHOT_DATE

    )';
    dbms_output.put_line('Created table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_TMP using pre-defined SQL.');
    COMMIT;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB to ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_DEL';
        dbms_output.put_line('Renamed table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB to ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_TMP to ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB';
        dbms_output.put_line('Renamed table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_TMP to ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_DEL';
        dbms_output.put_line('Dropped table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_DEL.');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_DEL doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB TO USER_PRODUCT_TRACKING';
        dbms_output.put_line('Granted user USER_PRODUCT_TRACKING select permission on table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB.');
        EXECUTE IMMEDIATE 'GRANT SELECT ON ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB TO USER_RR';
        dbms_output.put_line('Granted user USER_RR select permission on table ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX INDEX_ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB ON ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB (ISSUE_KEY, SNAPSHOT_DATE)';
        dbms_output.put_line('Created index on ON_DEMAND_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB.');
    END
    COMMIT;
    dbms_output.put_line('Procedure UPDATE_ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_STRATEGIC_PRIORITY_SOLUTION_TB_PROC finished successfully.');
END;

/
