--------------------------------------------------------
--  DDL for View NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_VW" ("ENVIRONMENT", "ID", "HISTORY_ID", "STATUS", "RELATED_SPE", "NO_OF_EXTENSION_REQUEST", "NO_OF_TIMES_SECURITY_APPROVED", "SCRIPT_ID", "RESPONSIBLE_MANAGER", "REQUEST_DATE", "INFORMATION_SECURITY_MANAGER", "VP_APPROVER", "VP_APPROVAL_STATUS", "APPROVAL_DATE", "EXCEPTION_EXPIRATION_DATE", "SECURITY_ISSUE_REGISTER", "SECONDARY_TECHNICAL_POC", "DATE_PT", "SECURITY_POC", "SEC_POC_APPROVAL_STATUS", "SEC_POC_MANAGER_ROSTER", "CUSTRECORD_SEC_POC_APPROVAL", "FIELD", "OLD_VALUE", "NEW_VALUE", "ML1", "ML2", "ML3", "ML4", "METADATA_REQUEST_DATE_UTC", "METADATA_EXCEPTION_EXPIRATION_DATE_UTC", "METADATA_DATE_UTC", "METADATA_APPROVAL_DATE_UTC", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "CLOSED", "APPROVED", "EXPIRED", "SECURITY_APPROVAL", "VP_APPROVAL", "TECHNICAL_POC", "OTHER", "EXPIRATION_TIME", "EXPIRATION_BUCKET") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  WITH expanded_exceptions AS (
    SELECT
        t1.id,
        t1.history_id,
        t1.request_date,
        t1.environment,
        t1.status,
        t1.exception_expiration_date,
        t1.no_of_extension_request,
        t1.responsible_manager,
        t1.secondary_technical_poc,
        t1.field,
        t1.old_value,
        t1.new_value,
        t1.ml4,
        t1.ml3,
        t1.ml2,
        t1.ml1,
        t1.custrecord_sec_poc_approval,
        t1.sec_poc_manager_roster,
        t1.security_issue_register,
        t1.information_security_manager,
        t1.approval_date,
        t1.vp_approver,
        t1.script_id,
        t1.no_of_times_security_approved,
        t1.related_spe,
        t1.security_poc,
        t1.sec_poc_approval_status,
        t1.vp_approval_status,
        t1.date_pt,
        t1.metadata_request_date_utc,
        t1.metadata_approval_date_utc,
        t1.metadata_exception_expiration_date_utc,
        t1.metadata_date_utc,
        t1.metadata_downloaded_timestamp_utc,
        CASE
            WHEN t1.status = 'Closed' THEN 'closed'
            WHEN t1.status = 'Approved' 
                AND t1.metadata_exception_expiration_date_utc > CURRENT_DATE THEN 'approved'
            WHEN t1.status = 'Approved' 
                AND t1.metadata_exception_expiration_date_utc < CURRENT_DATE THEN 'expired'
            WHEN t1.vp_approval_status = 'Approved' THEN 'security_approval'
            WHEN t1.sec_poc_approval_status = 'Reviewed' THEN 'vp_approval'
            WHEN t1.sec_poc_approval_status = 'Draft' 
                OR t1.sec_poc_approval_status = 'Waiting for more information' THEN 'technical_poc'
            ELSE 'other'
        END AS approval_stage
    FROM
        SRC_NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_TB t1
    WHERE t1.history_id = (
      SELECT MAX(t2.history_id)
      FROM SRC_NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_TB t2
      where t2.id=t1.id
    )
)
SELECT
    "ENVIRONMENT",
    "ID",
    "HISTORY_ID",
    "STATUS",
    "RELATED_SPE",
    "NO_OF_EXTENSION_REQUEST",
    "NO_OF_TIMES_SECURITY_APPROVED",
    "SCRIPT_ID",
    "RESPONSIBLE_MANAGER",
    "REQUEST_DATE",
    "INFORMATION_SECURITY_MANAGER",
    "VP_APPROVER",
    "VP_APPROVAL_STATUS",
    "APPROVAL_DATE",
    "EXCEPTION_EXPIRATION_DATE",
    "SECURITY_ISSUE_REGISTER",
    "SECONDARY_TECHNICAL_POC",
    "DATE_PT",
    "SECURITY_POC",
    "SEC_POC_APPROVAL_STATUS",
    "SEC_POC_MANAGER_ROSTER",
    "CUSTRECORD_SEC_POC_APPROVAL",
    "FIELD",
    "OLD_VALUE",
    "NEW_VALUE",
    "ML1",
    "ML2",
    "ML3",
    "ML4",
    "METADATA_REQUEST_DATE_UTC",
    "METADATA_EXCEPTION_EXPIRATION_DATE_UTC",
    "METADATA_DATE_UTC",
    "METADATA_APPROVAL_DATE_UTC",
    "METADATA_DOWNLOADED_TIMESTAMP_UTC",
    CASE WHEN approval_stage = 'closed' THEN 1 ELSE 0 END AS closed,
    CASE WHEN approval_stage = 'approved' THEN 1 ELSE 0 END AS approved,
    CASE WHEN approval_stage = 'expired' THEN 1 ELSE 0 END AS expired,
    CASE WHEN approval_stage = 'security_approval' THEN 1 ELSE 0 END AS security_approval,
    CASE WHEN approval_stage = 'vp_approval' THEN 1 ELSE 0 END AS vp_approval,
    CASE WHEN approval_stage = 'technical_poc' THEN 1 ELSE 0 END AS technical_poc,
    CASE WHEN approval_stage = 'other' THEN 1 ELSE 0 END AS other,
    TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE) AS expiration_time,
    CASE
    WHEN (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) <= 30 AND (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) > 0 AND approval_stage <> 'closed'
        THEN '0-30D'
    WHEN (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) <= 90 AND (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) > 30 AND approval_stage <> 'closed'
        THEN '31-90D'
    WHEN (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) <= 180 AND (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) > 90 AND approval_stage <> 'closed'
        THEN '91D-6M'
    WHEN (TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC) - TRUNC(CURRENT_DATE)) > 180 AND approval_stage <> 'closed'
        THEN 'Over 6M'
    ELSE NULL
        END AS expiration_bucket
FROM
    expanded_exceptions
;
