~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__ON_DEMAND_SODC_DOWNTIME_PRE1_TB__PROC
table=TMP_ON_DEMAND_SODC_DOWNTIME_PRE1_TB
table=ON_DEMAND_SODC_DOWNTIME_PRE1_TB
~~~END_MAIN_SECTION~~~


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


[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__ON_DEMAND_SODC_DOWNTIME_PRE1_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_ON_DEMAND_SODC_DOWNTIME_PRE1_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		CREATE TABLE "OTHQ_OWN"."TMP_ON_DEMAND_SODC_DOWNTIME_PRE1_TB" 
		   (	"P_START" DATE, 
			"P_END" DATE, 
			"COMPID" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"INACTIVE" NUMBER, 
			"START" DATE, 
			"END" DATE, 
			"DCID" VARCHAR2(16 BYTE) COLLATE "USING_NLS_COMP", 
			"ACCOUNTTYPE" VARCHAR2(16 BYTE) COLLATE "USING_NLS_COMP", 
			"IMPACT_RATIO" NUMBER, 
			"DOWNTIME" NUMBER, 
			"TOP200" NUMBER, 
			"DURATION" NUMBER,     
			"CATEGORY" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"RANK" NUMBER
			)!';
		end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into TMP_ON_DEMAND_SODC_DOWNTIME_PRE1_TB
		(
		select * from
		(select 
					"all".*
					,"cust".CATEGORY
					,ROW_NUMBER() over (partition by P_START,P_END,COMPID,INACTIVE,"START","END",DCID,ACCOUNTTYPE,IMPACT_RATIO,DOWNTIME,"all".TOP200,"DURATION","cust".CATEGORY order by "FROM_DATE","TO_DATE") "RANK"
				from
				(
					select 
						"dates".*
						,"all_data".*
						,((CASE 
							WHEN (("END" >= "P_START") and ("START" < "P_END")) then
							CASE 
								WHEN (("START"<"P_START") and ("END">="P_END")) then ("P_END"-"P_START")
								WHEN (("START">"P_START") and ("END">="P_END")) then ("P_END"-"START")
								WHEN (("START"<"P_START") and ("END"<"P_END")) then ("END"-"P_START")
								WHEN (("START">"P_START") and ("END"<"P_END")) then ("END"-"START")
								ELSE ("END"-"START")
							END
						END)*IMPACT_RATIO*24*60) "DURATION"
					from
					(
						tmp_sotd_dates_tb "dates"
						left join 
						(
							select 
								COMPID
								,INACTIVE
								,"START"
								,"END"
								,"DCID"
								,ACCOUNTTYPE
								,IMPACT_RATIO
								,"DOWNTIME"
								,1 "TOP200"
							from TMP_SOTD_SMD_ACCOUNTS2_TB 
							union all 
							select
								COMPID
								,INACTIVE
								,"START"
								,"END"
								,"DCID"
								,ACCOUNTTYPE
								,IMPACT_RATIO
								,"DOWNTIME"
								,1 "TOP200"
							from TMP_SOTD_HUB_CSI_TB 
						) "all_data"
						on
							((CASE 
								WHEN (("END" >= "P_START") and ("START" < "P_END")) then
								CASE 
									WHEN (("START"<"P_START") and ("END">="P_END")) then ("P_END"-"P_START")
									WHEN (("START">"P_START") and ("END">="P_END")) then ("P_END"-"START")
									WHEN (("START"<"P_START") and ("END"<"P_END")) then ("END"-"P_START")
									WHEN (("START">"P_START") and ("END"<"P_END")) then ("END"-"START")
									ELSE ("END"-"START")
								END
							END)*IMPACT_RATIO*24*60) is not null
					)
				) "all"
					left join TMP_SOTD_CUSTOMERS_TB "cust"
					on
						"cust".NAME = "all".COMPID
						and
						(not(("cust"."FROM_DATE"<"all"."P_START" and "cust"."TO_DATE"<"all"."P_START") or ("cust"."FROM_DATE">="all"."P_END" and "cust"."TO_DATE">="all"."P_END")))
						and
						(not(("cust"."FROM_DATE"<"all"."START" and "cust"."TO_DATE"<"all"."START") or ("cust"."FROM_DATE">"all"."END" and "cust"."TO_DATE">"all"."END")))
					where 
					 name is not null
		) where rank=1
		)!';
	end;
	commit;
	begin EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_SODC_DOWNTIME_PRE1_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin EXECUTE IMMEDIATE 'rename TMP_ON_DEMAND_SODC_DOWNTIME_PRE1_TB to ON_DEMAND_SODC_DOWNTIME_PRE1_TB';exception WHEN OTHERS THEN NULL;end;commit;
	--begin EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_SODC_DOWNTIME_PRE1_TB TO USER_STATE_OF_DATACENTER';end;commit;
end;
~~~END_VERSION_SECTION~~~
