~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__TMP_SOTD_HUB_CSI_TB__PROC
table=TMP_SOTD_HUB_CSI_TB
~~~END_MAIN_SECTION~~~

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

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__TMP_SOTD_HUB_CSI_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_SOTD_HUB_CSI_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		begin
		  execute immediate 'CREATE TABLE "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" 
		   (	"COMPID" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"INACTIVE" NUMBER, 
			"START" DATE, 
			"END" DATE, 
			"DCID" VARCHAR2(16 BYTE) COLLATE "USING_NLS_COMP", 
			"ACCOUNTTYPE" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"IMPACT_RATIO" NUMBER, 
			"DOWNTIME" NUMBER
		   )';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX1" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("COMPID")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX2" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("INACTIVE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX3" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("START")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX4" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("END")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX5" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("DCID")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX6" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("ACCOUNTTYPE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX7" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("IMPACT_RATIO")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB_INDEX8" ON "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" ("DOWNTIME")';
		  execute immediate 'ALTER TABLE "OTHQ_OWN"."TMP_SOTD_HUB_CSI_TB" MODIFY ("COMPID" NOT NULL ENABLE)';
		end;!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into TMP_SOTD_HUB_CSI_TB
		(select --company_id, count(id) - count(distinct id) ids 
			COMPANY_ID "COMPID"
			,0 "INACTIVE"
			,EVENT_START "START"
			,EVENT_END "END"
			,EVENT_DATACENTRE "DCID"
			,ACCOUNTTYPE
			,IMPACT_RATIO
			,1 "DOWNTIME"
			--,CASE WHEN (TOP200 is null) THEN 0 WHEN (TOP200='Yes') THEN 1 ELSE 0 END "TOP200"
		from
		(
			select 
				ID,
				ACTIVE
				,COMPANY_ID
				,EVENT_START,EVENT_END
				,(COMPANY_ID_VALUE*SSF_NUMBER*0.0001) IMPACT_RATIO
				,UPPER(HUB.MOLECULE_MOLECULE) "ACCOUNTTYPE"
				,CASE WHEN NOT(((SMD."START"<HUB.EVENT_START) AND (SMD."END"<HUB.EVENT_START)) OR ((SMD."START">HUB.EVENT_END) AND (SMD."END">HUB.EVENT_END))) THEN SMD.DCID END "EVENT_DATACENTRE" --both start and end for SMD record are NOT before or after HUB incident windows ... if both SMD times are before start of HUB or after end of HUB, it is NULL, otherwise it gives DC
				,rank() over (partition by HUB.ID,HUB.COMPANY_ID order by HUB.ID,HUB.COMPANY_ID, CASE WHEN NOT(((SMD."START"<HUB.EVENT_START) AND (SMD."END"<HUB.EVENT_START)) OR ((SMD."START">HUB.EVENT_END) AND (SMD."END">HUB.EVENT_END))) THEN SMD.DCID END, SMD."START") "RANK"
					--potentially there can be multiple SMD changes durting HUB issue, we will just take one alphabetically (TODO: we might take the first one by the time)
			from
				SRC_HUB_CSI_TB HUB
			left join
				TMP_SOTD_SMD_ACCOUNTS2_TB SMD on HUB.COMPANY_ID = SMD.COMPID
		) MA --left join SRC_NLCORP_CSI_TB NLCORP on MA.COMPANY_ID=NLCORP.NAME
		where RANK=1
		)!';
	end;
	commit;
end;
~~~END_VERSION_SECTION~~~