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

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

SELECT 
    issue_to_domain_mapping.SOURCE AS issue_key, 
    domain.DOMAIN_KEY, 
    domain.DOMAIN_NAME, 
    value_stream.VALUE_STREAM_KEY, 
    value_stream.VALUESTREAM_NAME, 
    value_stream.VALUE_STREAM_OWNER_NAME, 
    vss.VALUESUBSTREAM_KEY, 
    vss.VALUESUBSTREAM_NAME, 
    vss.VALUE_SUB_STREAM_OWNER_NAME, 
    vsss.VSSS_KEY, 
    vsss.VSSS_NAME, 
    vsss.VSSS_OWNER_NAME, 
    issue_to_domain_mapping.issue_to_domain_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME AS SNAPSHOT_DATE 
FROM 
    (
        SELECT 
            SOURCE, 
            DESTINATION, 
            TRUNC(SNAPSHOT_DATETIME) AS issue_to_domain_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ORIGINAL_MAPPING_TB 
        WHERE 
            field = ''Domain''
    ) issue_to_domain_mapping 
    
    LEFT OUTER JOIN (
        SELECT 
            OBJECTTYPE, 
            KEY AS DOMAIN_KEY, 
            NAME AS DOMAIN_NAME, 
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS DOMAIN_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB 
        WHERE 
            ObjectType = ''Domain''
    ) domain 
    ON issue_to_domain_mapping.DESTINATION = domain.DOMAIN_KEY 
    AND issue_to_domain_mapping.issue_to_domain_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = domain.DOMAIN_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            SOURCE, 
            DESTINATION, 
            TRUNC(SNAPSHOT_DATETIME) AS domain_to_vs_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB 
        WHERE 
            ObjectType = ''Domain'' 
            AND Field = ''Value-Stream''
    ) domain_to_vs 
    ON domain.DOMAIN_KEY = domain_to_vs.SOURCE 
    AND domain.DOMAIN_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = domain_to_vs.domain_to_vs_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            OBJECTTYPE, 
            KEY AS VALUE_STREAM_KEY, 
            NAME AS VALUESTREAM_NAME, 
            JSON_VALUE(OWNER, ''$.user_displayname'') AS VALUE_STREAM_OWNER_NAME, 
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB 
        WHERE 
            ObjectType = ''Value Stream''
    ) value_stream 
    ON domain_to_vs.DESTINATION = value_stream.VALUE_STREAM_KEY 
    AND domain_to_vs.domain_to_vs_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = value_stream.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            SOURCE, 
            DESTINATION, 
            TRUNC(SNAPSHOT_DATETIME) AS issue_to_vsss_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ORIGINAL_MAPPING_TB 
        WHERE 
            field = ''Value Sub-Sub-Stream''
    ) issue_to_vsss_mapping 
    ON issue_to_domain_mapping.SOURCE = issue_to_vsss_mapping.SOURCE 
    AND issue_to_domain_mapping.issue_to_domain_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = issue_to_vsss_mapping.issue_to_vsss_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            OBJECTTYPE, 
            KEY AS VSSS_KEY, 
            NAME AS VSSS_NAME, 
            JSON_VALUE(OWNER, ''$.user_displayname'') AS VSSS_OWNER_NAME, 
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS VSSS_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB 
        WHERE 
            ObjectType = ''Value Sub-Sub-Stream''
    ) vsss 
    ON issue_to_vsss_mapping.DESTINATION = vsss.VSSS_KEY 
    AND issue_to_vsss_mapping.issue_to_vsss_mapping_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = vsss.VSSS_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            SOURCE, 
            DESTINATION, 
            TRUNC(SNAPSHOT_DATETIME) AS vsss_to_vss_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB 
        WHERE 
            ObjectType = ''Value Sub-Sub-Stream'' 
            AND Field = ''Value-Sub-Stream''
    ) vsss_to_vss_map 
    ON vsss_to_vss_map.SOURCE = vsss.VSSS_KEY 
    AND vsss_to_vss_map.vsss_to_vss_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = vsss.VSSS_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
    
    LEFT OUTER JOIN (
        SELECT 
            KEY AS VALUESUBSTREAM_KEY, 
            NAME AS VALUESUBSTREAM_NAME, 
            JSON_VALUE(OWNER, ''$.user_displayname'') AS VALUE_SUB_STREAM_OWNER_NAME, 
            TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME 
        FROM 
            USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB 
        WHERE 
            ObjectType = ''Value Sub-Stream''
    ) vss 
    ON vsss_to_vss_map.DESTINATION = vss.VALUESUBSTREAM_KEY 
    AND vsss_to_vss_map.vsss_to_vss_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = vss.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME

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

/
