~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__ON_DEMAND_SODC_DOWNTIME_TB__PROC
table=TMP_ON_DEMAND_SODC_DOWNTIME_TB
table=ON_DEMAND_SODC_DOWNTIME_TB
grant=SELECT ON OTHQ_OWN.ON_DEMAND_SODC_DOWNTIME_TB TO USER_STATE_OF_DATACENTER
~~~END_MAIN_SECTION~~~

~~~BEGIN_VERSION_SECTION~~~
[VERSION]
version=2025-11-04

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__ON_DEMAND_SODC_DOWNTIME_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_ON_DEMAND_SODC_DOWNTIME_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		CREATE TABLE "OTHQ_OWN"."TMP_ON_DEMAND_SODC_DOWNTIME_TB" 
		   (	"P_START" DATE, 
			"INACTIVE" NUMBER, 
			"DCID" VARCHAR2(16 BYTE) COLLATE "USING_NLS_COMP", 
			"ACCOUNTTYPE" VARCHAR2(16 BYTE) COLLATE "USING_NLS_COMP", 
			"CATEGORY" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"TOP200" NUMBER, 
			"UP" NUMBER, 
			"DOWN" NUMBER, 
			"UPTIME" NUMBER
		   )!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into TMP_ON_DEMAND_SODC_DOWNTIME_TB 
		select * from
		(
		select 
			P_START
			,INACTIVE
			,DCID
			,ACCOUNTTYPE
			,CATEGORY
			--,DOWNTIME
			,1 TOP200
			,sum(case when DOWNTIME=0 then (DURATION) else 0 END) "UP"
			,sum(case when DOWNTIME=1 then (DURATION) else 0 END) "DOWN"
			,case when (sum(case when DOWNTIME=0 then (DURATION) else 0 END))=0 then 0
			else 100-(sum(case when DOWNTIME=1 then (DURATION) else 0 END)/sum(case when DOWNTIME=0 then (DURATION) else 0 END)*100) END "UPTIME"
		from ON_DEMAND_SODC_DOWNTIME_PRE1_TB
		where 
			INACTIVE=0 
		group by P_START,INACTIVE,DCID,ACCOUNTTYPE,CATEGORY
		--,DOWNTIME
		,TOP200
		order by P_START,DCID,ACCOUNTTYPE
		)!';
	end;
	commit;
	begin EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_SODC_DOWNTIME_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin EXECUTE IMMEDIATE 'rename TMP_ON_DEMAND_SODC_DOWNTIME_TB to ON_DEMAND_SODC_DOWNTIME_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_SODC_DOWNTIME_TB TO USER_STATE_OF_DATACENTER';end;commit;
end;
~~~END_VERSION_SECTION~~~