--------------------------------------------------------
--  DDL for View CP_ANALYTICS_QUEUE_SANDBOX_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."CP_ANALYTICS_QUEUE_SANDBOX_VW" ("NKEY", "SERVICEENVIRONMENT", "SOURCEACCOUNT", "TARGETACCOUNT", "WORKFLOWID", "TARGETPDB", "TARGETUNIQUECDB", "STATUS", "SNAPSHOTDATE", "VERSION", "CREATEDDATE", "INSTANCESIZE", "INCREMENTREFRESHCOUNT", "COPYADMINEMAILSONLY", "SOURCEDCID", "TARGETDCID", "LOGINTERVENTIONCOUNT", "TOTALREFRESHTIME", "REGION", "ENDPOINT", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "ORACLEERRORCODE", "TASKEXITCODE", "FIRSTTIMEREFRESH", "AWAITINGACTIVATIONSTART", "CLEANUPDBONFAILURE", "MODIFIEDDATE", "HARDWARETYPE", "SOURCEDB", "LASTACTOR", "EMAIL", "FORMATTED_CREATEDDATE", "INSTANCESIZELABEL") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT "NKEY","SERVICEENVIRONMENT","SOURCEACCOUNT","TARGETACCOUNT","WORKFLOWID","TARGETPDB","TARGETUNIQUECDB","STATUS","SNAPSHOTDATE","VERSION","CREATEDDATE","INSTANCESIZE","INCREMENTREFRESHCOUNT","COPYADMINEMAILSONLY","SOURCEDCID","TARGETDCID","LOGINTERVENTIONCOUNT","TOTALREFRESHTIME","REGION","ENDPOINT","METADATA_DOWNLOADED_TIMESTAMP_UTC","ORACLEERRORCODE","TASKEXITCODE","FIRSTTIMEREFRESH","AWAITINGACTIVATIONSTART","CLEANUPDBONFAILURE","MODIFIEDDATE","HARDWARETYPE","SOURCEDB","LASTACTOR","EMAIL"
, case when t1.createddate is not null then to_char("CREATEDDATE", 'DD-MON-YYYY HH24:MI') else null end Formatted_CreatedDate,
CASE 
 WHEN INSTANCESIZE = 0 or INSTANCESIZE is null then 'N/A'
 WHEN INSTANCESIZE<1000 then '<1000'
 WHEN INSTANCESIZE <=3000 then '1000-3000' 
 WHEN INSTANCESIZE <=10000 then '3000-10000' 
 WHEN INSTANCESIZE >10000 then '>10000'
 else 'N/A'
end as InstanceSizeLabel
FROM SRC_CP_ANALYTICS_QUEUE_SANDBOX_TB t1

where (t1.endpoint like 'netsuite-production/refresh-orchestration/v1/analytics/queue/sandbox/?noOfDaysBack=%' and exists(select 1 from SRC_CP_ANALYTICS_QUEUE_SANDBOX_TB t2 where t2.endpoint like 'netsuite-production/refresh-orchestration/v1/analytics/queue/sandbox/?noOfDaysBack=%' and t2.Nkey=t1.Nkey and t2.region =t1.region)) or 
(t1.endpoint like 'netsuite-production/refresh-orchestration/v1/analytics/queue/sandbox/uncomplete?noOfDaysBack=%' and not exists(select 1 from SRC_CP_ANALYTICS_QUEUE_SANDBOX_TB t2 where t2.endpoint like 'netsuite-production/refresh-orchestration/v1/analytics/queue/sandbox/?noOfDaysBack=%' and t2.Nkey=t1.Nkey and t2.region =t1.region))
;
