~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__ON_DEMAND_SODC_DOWNTIME_PRE4_TB__PROC
table=TMP_ON_DEMAND_SODC_DOWNTIME_PRE4_TB
table=ON_DEMAND_SODC_DOWNTIME_PRE4_TB
~~~END_MAIN_SECTION~~~

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

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__ON_DEMAND_SODC_DOWNTIME_PRE4_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_ON_DEMAND_SODC_DOWNTIME_PRE4_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		CREATE TABLE "OTHQ_OWN"."TMP_ON_DEMAND_SODC_DOWNTIME_PRE4_TB" 
		   (	"MONTH_MINUTES" NUMBER, 
			"ATP_CMIS" NUMBER, 
			"ATP_PLATFORM_UPTIME" VARCHAR2(41 BYTE) COLLATE "USING_NLS_COMP", 
			"EXA_CMIS" NUMBER, 
			"EXA_PLATFORM_UPTIME" VARCHAR2(41 BYTE) COLLATE "USING_NLS_COMP", 
			"ATP_SLC_BREAK" NUMBER, 
			"ATP_EXPOSED_CREDITS" NUMBER, 
			"EXA_SLC_BREAK" NUMBER, 
			"EXA_EXPOSED_CREDITS" NUMBER, 
			"MONTH" DATE, 
			"ATP_CNT" NUMBER, 
			"EXA_CNT" NUMBER, 
			"CASES_ATP" NUMBER, 
			"CASES_EXACS" NUMBER, 
			"CIM_ATP" NUMBER, 
			"CIM_EXACS" NUMBER, 
			"X0_ATP_CNT" NUMBER, 
			"X0_ATP_PERCENTAGE" NUMBER, 
			"X0_ATP_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X0_ATP_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X1_ATP_CNT" NUMBER, 
			"X1_ATP_PERCENTAGE" NUMBER, 
			"X1_ATP_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X1_ATP_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X2_ATP_CNT" NUMBER, 
			"X2_ATP_PERCENTAGE" NUMBER, 
			"X2_ATP_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X2_ATP_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X3_ATP_CNT" NUMBER, 
			"X3_ATP_PERCENTAGE" NUMBER, 
			"X3_ATP_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X3_ATP_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X4_ATP_CNT" NUMBER, 
			"X4_ATP_PERCENTAGE" NUMBER, 
			"X4_ATP_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X4_ATP_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X0_EXA_CNT" NUMBER, 
			"X0_EXA_PERCENTAGE" NUMBER, 
			"X0_EXA_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X0_EXA_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X1_EXA_CNT" NUMBER, 
			"X1_EXA_PERCENTAGE" NUMBER, 
			"X1_EXA_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X1_EXA_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X2_EXA_CNT" NUMBER, 
			"X2_EXA_PERCENTAGE" NUMBER, 
			"X2_EXA_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X2_EXA_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X3_EXA_CNT" NUMBER, 
			"X3_EXA_PERCENTAGE" NUMBER, 
			"X3_EXA_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X3_EXA_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X4_EXA_CNT" NUMBER, 
			"X4_EXA_PERCENTAGE" NUMBER, 
			"X4_EXA_PERCENTAGE1" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"X4_EXA_PERCENTAGE2" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP"
		   )!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into TMP_ON_DEMAND_SODC_DOWNTIME_PRE4_TB
		(
		select
			CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440 MONTH_MINUTES
			,case when (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*ATP_CNT) is null then 0 else (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*ATP_CNT) end ATP_CMIS
			,case when (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*ATP_CNT) is null then '0%' else (TRUNC((CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*ATP_CNT - CIM_ATP)/(CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*ATP_CNT)*100,2) || '%') end ATP_PLATFORM_UPTIME

			,case when (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*EXA_CNT) is null then 0 else (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*EXA_CNT) end EXA_CMIS
			,case when (CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*EXA_CNT) is null then '0%' else (TRUNC((CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*EXA_CNT - CIM_EXACS)/(CAST(to_char(LAST_DAY(MONTH),'dd') AS INT)*1440*EXA_CNT)*100,2) || '%') end EXA_PLATFORM_UPTIME

			,"x2_ATP_CNT"+"x3_ATP_CNT"+"x4_ATP_CNT" ATP_SLC_BREAK
			,TRUNC("x2_ATP_CNT"*0.1+"x3_ATP_CNT"*0.15+"x4_ATP_CNT"*0.25) ATP_EXPOSED_CREDITS
			,"x2_EXA_CNT"+"x3_EXA_CNT"+"x4_EXA_CNT" EXA_SLC_BREAK
			,TRUNC("x2_EXA_CNT"*0.1+"x3_EXA_CNT"*0.15+"x4_EXA_CNT"*0.25) EXA_EXPOSED_CREDITS
			,xa.*
		from
			(select
				MONTH
				,ATP_CNT
				,EXA_CNT
				,CASES_ATP
				,CASES_EXACS
				,CIM_ATP
				,CIM_EXACS
			
				,case when "0_ATP_CNT" is null then 0 else "0_ATP_CNT" end "x0_ATP_CNT"
				,case when "0_ATP_PERCENTAGE" is null then 0 else "0_ATP_PERCENTAGE" end "x0_ATP_PERCENTAGE"
				,case when "0_ATP_PERCENTAGE1" is null then '0%' else "0_ATP_PERCENTAGE1" end "x0_ATP_PERCENTAGE1"
				,case when "0_ATP_PERCENTAGE2" is null then '0% (0)' else "0_ATP_PERCENTAGE2" end "x0_ATP_PERCENTAGE2"
			
				,case when "1_ATP_CNT" is null then 0 else "1_ATP_CNT" end "x1_ATP_CNT"
				,case when "1_ATP_PERCENTAGE" is null then 0 else "1_ATP_PERCENTAGE" end "x1_ATP_PERCENTAGE"
				,case when "1_ATP_PERCENTAGE1" is null then '0%' else "1_ATP_PERCENTAGE1" end "x1_ATP_PERCENTAGE1"
				,case when "1_ATP_PERCENTAGE2" is null then '0% (0)' else "1_ATP_PERCENTAGE2" end "x1_ATP_PERCENTAGE2"
			
				,case when "2_ATP_CNT" is null then 0 else "2_ATP_CNT" end "x2_ATP_CNT"
				,case when "2_ATP_PERCENTAGE" is null then 0 else "2_ATP_PERCENTAGE" end "x2_ATP_PERCENTAGE"
				,case when "2_ATP_PERCENTAGE1" is null then '0%' else "2_ATP_PERCENTAGE1" end "x2_ATP_PERCENTAGE1"
				,case when "2_ATP_PERCENTAGE2" is null then '0% (0)' else "2_ATP_PERCENTAGE2" end "x2_ATP_PERCENTAGE2"
			
				,case when "3_ATP_CNT" is null then 0 else "3_ATP_CNT" end "x3_ATP_CNT"
				,case when "3_ATP_PERCENTAGE" is null then 0 else "3_ATP_PERCENTAGE" end "x3_ATP_PERCENTAGE"
				,case when "3_ATP_PERCENTAGE1" is null then '0%' else "3_ATP_PERCENTAGE1" end "x3_ATP_PERCENTAGE1"
				,case when "3_ATP_PERCENTAGE2" is null then '0% (0)' else "3_ATP_PERCENTAGE2" end "x3_ATP_PERCENTAGE2"
			
				,case when "4_ATP_CNT" is null then 0 else "4_ATP_CNT" end "x4_ATP_CNT"
				,case when "4_ATP_PERCENTAGE" is null then 0 else "4_ATP_PERCENTAGE" end "x4_ATP_PERCENTAGE"
				,case when "4_ATP_PERCENTAGE1" is null then '0%' else "4_ATP_PERCENTAGE1" end "x4_ATP_PERCENTAGE1"
				,case when "4_ATP_PERCENTAGE2" is null then '0% (0)' else "4_ATP_PERCENTAGE2" end "x4_ATP_PERCENTAGE2"
			
			
				,case when "0_EXA_CNT" is null then 0 else "0_EXA_CNT" end "x0_EXA_CNT"
				,case when "0_EXA_PERCENTAGE" is null then 0 else "0_EXA_PERCENTAGE" end "x0_EXA_PERCENTAGE"
				,case when "0_EXA_PERCENTAGE1" is null then '0%' else "0_EXA_PERCENTAGE1" end "x0_EXA_PERCENTAGE1"
				,case when "0_EXA_PERCENTAGE2" is null then '0% (0)' else "0_EXA_PERCENTAGE2" end "x0_EXA_PERCENTAGE2"
			
				,case when "1_EXA_CNT" is null then 0 else "1_EXA_CNT" end "x1_EXA_CNT"
				,case when "1_EXA_PERCENTAGE" is null then 0 else "1_EXA_PERCENTAGE" end "x1_EXA_PERCENTAGE"
				,case when "1_EXA_PERCENTAGE1" is null then '0%' else "1_EXA_PERCENTAGE1" end "x1_EXA_PERCENTAGE1"
				,case when "1_EXA_PERCENTAGE2" is null then '0% (0)' else "1_EXA_PERCENTAGE2" end "x1_EXA_PERCENTAGE2"
			
				,case when "2_EXA_CNT" is null then 0 else "2_EXA_CNT" end "x2_EXA_CNT"
				,case when "2_EXA_PERCENTAGE" is null then 0 else "2_EXA_PERCENTAGE" end "x2_EXA_PERCENTAGE"
				,case when "2_EXA_PERCENTAGE1" is null then '0%' else "2_EXA_PERCENTAGE1" end "x2_EXA_PERCENTAGE1"
				,case when "2_EXA_PERCENTAGE2" is null then '0% (0)' else "2_EXA_PERCENTAGE2" end "x2_EXA_PERCENTAGE2"
			
				,case when "3_EXA_CNT" is null then 0 else "3_EXA_CNT" end "x3_EXA_CNT"
				,case when "3_EXA_PERCENTAGE" is null then 0 else "3_EXA_PERCENTAGE" end "x3_EXA_PERCENTAGE"
				,case when "3_EXA_PERCENTAGE1" is null then '0%' else "3_EXA_PERCENTAGE1" end "x3_EXA_PERCENTAGE1"
				,case when "3_EXA_PERCENTAGE2" is null then '0% (0)' else "3_EXA_PERCENTAGE2" end "x3_EXA_PERCENTAGE2"
			
				,case when "4_EXA_CNT" is null then 0 else "4_EXA_CNT" end "x4_EXA_CNT"
				,case when "4_EXA_PERCENTAGE" is null then 0 else "4_EXA_PERCENTAGE" end "x4_EXA_PERCENTAGE"
				,case when "4_EXA_PERCENTAGE1" is null then '0%' else "4_EXA_PERCENTAGE1" end "x4_EXA_PERCENTAGE1"
				,case when "4_EXA_PERCENTAGE2" is null then '0% (0)' else "4_EXA_PERCENTAGE2" end "x4_EXA_PERCENTAGE2"
			 
			from
				(select
					MONTH
					,sum(case when CATEGORY='ATP' then CNT end) "ATP_CNT"
					,sum(case when CATEGORY='EXA' then CNT end) "EXA_CNT"
					
					,max(CASES_ATP) CASES_ATP
					,max(CASES_EXACS) CASES_EXACS
					,max(CIM_ATP) CIM_ATP
					,max(CIM_EXACS) CIM_EXACS
					
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=0 then CNT end) "0_ATP_CNT"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=0 then PERCENTAGE end) "0_ATP_PERCENTAGE"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=0 then PERCENTAGE1 end) "0_ATP_PERCENTAGE1"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=0 then PERCENTAGE2 end) "0_ATP_PERCENTAGE2"
				
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=1 then CNT end) "1_ATP_CNT"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=1 then PERCENTAGE end) "1_ATP_PERCENTAGE"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=1 then PERCENTAGE1 end) "1_ATP_PERCENTAGE1"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=1 then PERCENTAGE2 end) "1_ATP_PERCENTAGE2"
				
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=2 then CNT end) "2_ATP_CNT"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=2 then PERCENTAGE end) "2_ATP_PERCENTAGE"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=2 then PERCENTAGE1 end) "2_ATP_PERCENTAGE1"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=2 then PERCENTAGE2 end) "2_ATP_PERCENTAGE2"
				
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=3 then CNT end) "3_ATP_CNT"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=3 then PERCENTAGE end) "3_ATP_PERCENTAGE"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=3 then PERCENTAGE1 end) "3_ATP_PERCENTAGE1"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=3 then PERCENTAGE2 end) "3_ATP_PERCENTAGE2"
				
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=4 then CNT end) "4_ATP_CNT"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=4 then PERCENTAGE end) "4_ATP_PERCENTAGE"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=4 then PERCENTAGE1 end) "4_ATP_PERCENTAGE1"
					,max(case when CATEGORY='ATP' and SMILEY_TYPE=4 then PERCENTAGE2 end) "4_ATP_PERCENTAGE2"
				
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=0 then CNT end) "0_EXA_CNT"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=0 then PERCENTAGE end) "0_EXA_PERCENTAGE"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=0 then PERCENTAGE1 end) "0_EXA_PERCENTAGE1"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=0 then PERCENTAGE2 end) "0_EXA_PERCENTAGE2"
				
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=1 then CNT end) "1_EXA_CNT"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=1 then PERCENTAGE end) "1_EXA_PERCENTAGE"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=1 then PERCENTAGE1 end) "1_EXA_PERCENTAGE1"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=1 then PERCENTAGE2 end) "1_EXA_PERCENTAGE2"
				
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=2 then CNT end) "2_EXA_CNT"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=2 then PERCENTAGE end) "2_EXA_PERCENTAGE"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=2 then PERCENTAGE1 end) "2_EXA_PERCENTAGE1"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=2 then PERCENTAGE2 end) "2_EXA_PERCENTAGE2"
				
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=3 then CNT end) "3_EXA_CNT"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=3 then PERCENTAGE end) "3_EXA_PERCENTAGE"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=3 then PERCENTAGE1 end) "3_EXA_PERCENTAGE1"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=3 then PERCENTAGE2 end) "3_EXA_PERCENTAGE2"
				
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=4 then CNT end) "4_EXA_CNT"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=4 then PERCENTAGE end) "4_EXA_PERCENTAGE"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=4 then PERCENTAGE1 end) "4_EXA_PERCENTAGE1"
					,max(case when CATEGORY='EXA' and SMILEY_TYPE=4 then PERCENTAGE2 end) "4_EXA_PERCENTAGE2"
				
				from
					(select
						to_date(MONTH || '-01') MONTH
						,CASES_ATP
						,CASES_EXACS
						,CIM_ATP
						,CIM_EXACS
					from
						(select
							(to_char(DATE_OF_EVENT,'YYYY-MM')) "MONTH"
							,sum(case when (DATACENTER_TYPE_IMPACTED='OCI - ATP' and NSI_CASES>0) then 1 else 0 end) CASES_ATP
							,sum(case when (DATACENTER_TYPE_IMPACTED='OCI - ExaCS' and NSI_CASES>0) then 1 else 0 end) CASES_EXACS
							,sum(case when (DATACENTER_TYPE_IMPACTED='OCI - ATP' and to_number(CIM)>0) then to_number(CIM) else 0 end) CIM_ATP
							,sum(case when (DATACENTER_TYPE_IMPACTED='OCI - ExaCS' and to_number(CIM)>0) then to_number(CIM) else 0 end) CIM_EXACS
						from (select * from (select sh.*, row_number() over (partition by sh.issue_key order by sh.HISTORY_ITEM_CREATED desc) rank from JIRA_CSI_SH_TB sh) where rank = 1)
						where
							ISSUE_TYPE='Event Report'
							and (NSI_CASES>0 or CIM>0)
							and DATACENTER_TYPE_IMPACTED in ('OCI - ATP', 'OCI - ExaCS')
						group by
							(to_char(DATE_OF_EVENT,'YYYY-MM'))
						order by
							(to_char(DATE_OF_EVENT,'YYYY-MM')) DESC
						)
					)
					/*ON_DEMAND_SODC_DOWNTIME_PRE3_VW*/ xa
					left join ON_DEMAND_SODC_DOWNTIME_PRE2_TB xb on xa."MONTH"=xb.P_START
					--having xa."MONTH"=to_date('2025-07-01 00:00:00')
				group by
					MONTH
				order by
					MONTH DESC
				)
			)xa
		)!';
	end;
	commit;
	begin EXECUTE IMMEDIATE 'DROP TABLE ON_DEMAND_SODC_DOWNTIME_PRE4_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin EXECUTE IMMEDIATE 'rename TMP_ON_DEMAND_SODC_DOWNTIME_PRE4_TB to ON_DEMAND_SODC_DOWNTIME_PRE4_TB';exception WHEN OTHERS THEN NULL;end;commit;
	--begin EXECUTE IMMEDIATE 'GRANT SELECT ON OTHQ_OWN.ON_DEMAND_SODC_DOWNTIME_PRE4_TB TO USER_STATE_OF_DATACENTER';end;commit;
end;
~~~END_VERSION_SECTION~~~