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

  CREATE OR REPLACE EDITIONABLE PROCEDURE "OTHQ_OWN"."UPDATE_ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB_PROC" 
    AUTHID CURRENT_USER
IS
BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TMP';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_DEL doesn''t exists.');
    END;
    COMMIT;
    EXECUTE IMMEDIATE 'CREATE TABLE ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TMP as (
    
SELECT T.* FROM
  (SELECT KEY as SOURCE,
  OBJECTTYPE,
  OBJECTSUBTYPE,
  ''Objective'' as FIELD,
  JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME as SNAPSHOT_DATETIME,
  REGEXP_SUBSTR(OBJECTIVE, ''(PRODUCT-[0-9]*)'', 1, L.LVL, NULL, 1) as DESTINATION,
  L.LVL
  FROM SRC_JIRA_DE_SNAPSHOTS_ASSETS_TB
  CROSS APPLY
    (SELECT LEVEL as LVL
    FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(OBJECTIVE, ''|'') + 1
    ) L
  WHERE OBJECTIVE is not null
  ) T
WHERE T.DESTINATION is not null

UNION ALL

SELECT T.* FROM
  (SELECT KEY as SOURCE,
  OBJECTTYPE,
  OBJECTSUBTYPE,
  ''Parent-Objective'' as FIELD,
  JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME as SNAPSHOT_DATETIME,
  REGEXP_SUBSTR(PARENT_OBJECTIVE, ''(PRODUCT-[0-9]*)'', 1, L.LVL, NULL, 1) as DESTINATION,
  L.LVL
  FROM SRC_JIRA_DE_SNAPSHOTS_ASSETS_TB
  CROSS APPLY
    (SELECT LEVEL as LVL
    FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(PARENT_OBJECTIVE, ''|'') + 1
    ) L
  WHERE PARENT_OBJECTIVE is not null
  ) T
WHERE T.DESTINATION is not null

UNION ALL

SELECT T.* FROM
  (SELECT KEY as SOURCE,
  OBJECTTYPE,
  OBJECTSUBTYPE,
  ''Value-Stream'' as FIELD,
  JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME as SNAPSHOT_DATETIME,
  REGEXP_SUBSTR(VALUE_STREAM, ''(PRODUCT-[0-9]*)'', 1, L.LVL, NULL, 1) as DESTINATION,
  L.LVL
  FROM SRC_JIRA_DE_SNAPSHOTS_ASSETS_TB
  CROSS APPLY
    (SELECT LEVEL as LVL
    FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(VALUE_STREAM, ''|'') + 1
    ) L
  WHERE VALUE_STREAM is not null
  ) T
WHERE T.DESTINATION is not null

UNION ALL

SELECT T.* FROM
  (SELECT KEY as SOURCE,
  OBJECTTYPE,
  OBJECTSUBTYPE,
  ''Value-Sub-Stream'' as FIELD,
  JIRA_DOWNLOADER_METADATA_SCHEDULED_TIME as SNAPSHOT_DATETIME,
  REGEXP_SUBSTR(VALUE_SUB_STREAM, ''(PRODUCT-[0-9]*)'', 1, L.LVL, NULL, 1) as DESTINATION,
  L.LVL
  FROM SRC_JIRA_DE_SNAPSHOTS_ASSETS_TB
  CROSS APPLY
    (SELECT LEVEL as LVL
    FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(VALUE_SUB_STREAM, ''|'') + 1
    ) L
  WHERE VALUE_SUB_STREAM is not null
  ) T
WHERE T.DESTINATION is not null
    
    )';
    COMMIT;    
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB to ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'RENAME ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TMP to ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TMP doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_DEL';
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_DEL doesn''t exists.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'GRANT SELECT ON ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB TO USER_PRODUCT_TRACKING';
        dbms_output.put_line('Table ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB grant select on USER_PRODUCT_TRACKING.');
    END;
    BEGIN
        EXECUTE IMMEDIATE 'CREATE INDEX INDEX_CUSTOM_ASSETS_MAPPING ON ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB (SOURCE, OBJECTTYPE, FIELD, SNAPSHOT_DATETIME, DESTINATION)';
        dbms_output.put_line('Created index on ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB.');
    END
    COMMIT;
    dbms_output.put_line('>>> RECREATING ON_DEMAND_JIRA_DE_SNAPSHOTS_ASSETS_MAPPING_TB DONE <<<');
END;

/
