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

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

SELECT
    issues.CREATED,
    issues.FEATURE_TYPE,
    issues.NSCORP_RECORD_ID,
    issues.ASSIGNEE_NAME,
    issues.ASSIGNEE_EMAILADDRESS,
    issues.REPORTER_EMAILADDRESS,
    issues.URGENCY_LEVEL,
    issues.SUMMARY,
    issues.DESCRIPTION,
    issues.UPDATED,
    issues.MOOD_RING,
    issues.MOOD_RING_COMMENT,
    issues.ISSUETYPE,
    issues.LABELS,
    issues.RESOLUTION,
    issues.RESOLUTIONDATE,
    issues.STATUS,
    issues.STATUS_CATEGORY,
    issues.FTE_ESTIMATE,
    issues.PROJECT,
    issues.COMPLETION_PERIOD_CHANGE_REASON,
    issues.FIRST_TRANSITION_TO_IN_PROGRESS,
    issues.ID,
    issues.TECHNICAL_WRITER_DISPLAYNAME,
    issues.issue_key,
    issues.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME AS SNAPSHOT_DATE,
    issues.issues_status,
    issues.COMPLETION_PERIOD_KEY,
    issues.TARGET_VERSION_KEY,
    issues.COMMUNICATION,
    issues.EXTERNAL_RELEASE_NOTES,
    issues.INTERNAL_COMMUNICATION,
    issues.PRIORITY,
    cp.OBJECTTYPE AS CP_OBJECTTYPE,
    cp.KEY AS CP_KEY,
    cp.NAME AS CP_NAME,
    cp.INACTIVE AS CP_INACTIVE,
    cp.PERIOD_START AS CP_PERIOD_START,
    cp.PERIOD_END AS CP_PERIOD_END,
    target_version.OBJECTTYPE AS TV_OBJECTTYPE,
    target_version.KEY AS TV_KEY,
    target_version.NAME AS TV_NAME,
    target_version.INACTIVE AS TV_INACTIVE,
    target_version.INTEGRATION_DEADLINE AS TV_INTEGRATION_DEADLINE,
    target_version.RELEASE_TYPE AS TV_RELEASE_TYPE,
    target_version.PERIOD_START AS TV_PERIOD_START,
    target_version.PERIOD_END AS TV_PERIOD_END,
    issue_on_issue_mapping.CHILD AS BLOCKER
FROM
    (SELECT
        FEATURE_TYPE,
        NSCORP_RECORD_ID,
        ASSIGNEE_DISPLAYNAME AS ASSIGNEE_NAME,
        ASSIGNEE_EMAILADDRESS,
        REPORTER_EMAILADDRESS,
        URGENCY_LEVEL,
        SUMMARY,
        DESCRIPTION,
        TRUNC(CREATED) AS CREATED,
        TRUNC(UPDATED) AS UPDATED,
        LABELS,
        MOOD_RING,
        MOOD_RING_COMMENT,
        ISSUETYPE,
        RESOLUTION,
        TRUNC(RESOLUTIONDATE) AS RESOLUTIONDATE,
        STATUS,
        STATUS_CATEGORY,
        FIRST_TRANSITION_TO_IN_PROGRESS,
        FTE_ESTIMATE,
        PROJECT,
        ID,
        TECHNICAL_WRITER_DISPLAYNAME,
        COMPLETION_PERIOD_CHANGE_REASON,
        INTERNAL_COMMUNICATION,
        KEY AS issue_key,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
        STATUS_CATEGORY AS issues_status,
        JSON_VALUE(COMPLETION_PERIOD, ''$.key'') AS COMPLETION_PERIOD_KEY,
        JSON_VALUE(TARGET_VERSION, ''$.key'') AS TARGET_VERSION_KEY,
        COMMUNICATION,
        EXTERNAL_RELEASE_NOTES,
        PRIORITY
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ORIGINAL_TB
    WHERE
        ISSUETYPE IN (''New Feature'', ''Defect'', ''Minor Enhancement'', ''Discovery'')
    ) issues
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  issues.COMPLETION_PERIOD_KEY = cp.KEY
AND issues.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = cp.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
LEFT JOIN
    (SELECT
        OBJECTTYPE,
        KEY,
        NAME,
        INACTIVE,
        TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME,
        INTEGRATION_DEADLINE,
        RELEASE_TYPE,
        PERIOD_START,
        PERIOD_END
    FROM
        USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_ASSETS_TB
    WHERE
        RELEASE_TYPE IS NOT NULL
    ) target_version
ON  issues.TARGET_VERSION_KEY = target_version.KEY
AND issues.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = target_version.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME

LEFT JOIN 
          (SELECT 
           LINK_TYPE,
           CHILD_KEY AS CHILD,
           PARENT_KEY AS PARENT,
           RELATION,
           TRUNC(JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME) AS ISSUE_ON_ISSUE_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME
           FROM USER_PRODUCT_TRACKING.JIRA_DE_SNAPSHOTS_MAPPING_TB
           WHERE RELATION = ''blocks''
          ) issue_on_issue_mapping
ON  issues.issue_key = issue_on_issue_mapping.PARENT
AND issues.JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME = issue_on_issue_mapping.ISSUE_ON_ISSUE_JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME

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

/
