--------------------------------------------------------
--  DDL for View CP_ANALYTICS_QUEUE_RP_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."CP_ANALYTICS_QUEUE_RP_VW" ("NKEY", "SERVICEENVIRONMENT", "SOURCEACCOUNT", "TARGETACCOUNT", "WORKFLOWID", "TARGETPDB", "TARGETUNIQUECDB", "STATUS", "SNAPSHOTDATE", "VERSION", "CREATEDDATE", "INSTANCESIZE", "DATACENTERID", "MOVEDTOATP", "LOGINTERVENTIONCOUNT", "TOTALREFRESHTIME", "REGION", "ENDPOINT", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "SOURCEPDB", "HARDWARETYPE", "EMAIL", "MODIFIEDDATE", "CLEANUPDBONFAILURE", "SOURCEDCID", "TARGETDCID", "REFRESHMODE", "DBCLONEWORKFLOWID", "DBCLONETOTALTIME", "ACCOUNTCLONEWORKFLOWID", "ACCOUNTCLONETOTALTIME", "LASTACTOR", "ERRORTEXT", "FORMATTED_CREATEDDATE") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT t1."NKEY",t1."SERVICEENVIRONMENT",t1."SOURCEACCOUNT",t1."TARGETACCOUNT",t1."WORKFLOWID",t1."TARGETPDB",t1."TARGETUNIQUECDB",t1."STATUS",t1."SNAPSHOTDATE",t1."VERSION",t1."CREATEDDATE",t1."INSTANCESIZE",t1."DATACENTERID",t1."MOVEDTOATP",t1."LOGINTERVENTIONCOUNT",t1."TOTALREFRESHTIME",t1."REGION",t1."ENDPOINT",t1."METADATA_DOWNLOADED_TIMESTAMP_UTC",t1."SOURCEPDB",t1."HARDWARETYPE",t1."EMAIL",t1."MODIFIEDDATE",t1."CLEANUPDBONFAILURE",t1."SOURCEDCID",t1."TARGETDCID",t1."REFRESHMODE",t1."DBCLONEWORKFLOWID",t1."DBCLONETOTALTIME",t1."ACCOUNTCLONEWORKFLOWID",t1."ACCOUNTCLONETOTALTIME",t1."LASTACTOR",t1."ERRORTEXT"
, case when t1.createddate is not null then to_char("CREATEDDATE", 'DD-MON-YYYY HH24:MI') else null end Formatted_CreatedDate    
FROM src_cp_analytics_queue_rp_tb t1

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