~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__TMP_SOTD_SMD_ACCOUNTS2_TB__PROC
table=TMP_SOTD_SMD_ACCOUNTS2_TB
~~~END_MAIN_SECTION~~~

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

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__TMP_SOTD_SMD_ACCOUNTS2_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_SOTD_SMD_ACCOUNTS2_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		begin
		  execute immediate 'CREATE TABLE "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_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_SMD_ACCOUNTS2_TB_INDEX6" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("ACCOUNTTYPE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX7" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("IMPACT_RATIO")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX8" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("DOWNTIME")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX1" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("COMPID")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX2" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("INACTIVE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX3" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("START")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX4" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("END")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB_INDEX5" ON "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" ("DCID")';
		  execute immediate 'ALTER TABLE "OTHQ_OWN"."TMP_SOTD_SMD_ACCOUNTS2_TB" MODIFY ("COMPID" NOT NULL ENABLE)';
		end;!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into TMP_SOTD_SMD_ACCOUNTS2_TB
		(select COMPID
			--,PDBID
			,INACTIVE
			,"START"
			,"END"
			--,0 "DOWN_MINUTES"
			--,"NAME"
			--,"USABLE"
			--,NOTE
			--,VERSIONMAJOR
			--,RANK
			--,RANK2
			,DCID
			,UPPER(ACCOUNTTYPE) "ACCOUNTTYPE"
			,1 "IMPACT_RATIO"
			,0 "DOWNTIME"
			--,CASE WHEN (TOP200 is null) THEN 0 WHEN (TOP200='Yes') THEN 1 ELSE 0 END "TOP200"
		from
		(
			select
				"PRIM".COMPID
				,"PRIM".PDBID    
				,"PRIM".DCID
				,CASE WHEN (rank() over (partition by "PRIM".compid,"PRIM".pdbid order by "PRIM".compid,"PRIM".pdbid,"PRIM".LASTMODIFIED)) = 1 THEN to_date('2000-01-01', 'yyyy-mm-dd') --if it is first occurence for this compid.pdbid, it is assumed it srarted running before
				ELSE "PRIM".LASTMODIFIED
				END "START"
				,CASE WHEN ("TER".LASTMODIFIED is null) THEN to_date('3000-01-01', 'yyyy-mm-dd') --we don't have any 'next' record from DC change, so it is still running on the last DC
				ELSE "TER".LASTMODIFIED
				END "END"
				,0 "INACTIVE"
				,"PRIM".NAME
				,"PRIM".ACCOUNTTYPE
				,"PRIM".USABLE
				,"PRIM".NOTE
				,"PRIM".VERSIONMAJOR    
				,rank() over (partition by "PRIM".compid,"PRIM".pdbid,"PRIM".dcid order by "PRIM".compid,"PRIM".pdbid,"PRIM".LASTMODIFIED) "RANK" --partition by compid(pdbid) and DC, but ordere by compid(pdbid) and lastmodified, will give a list of changes between DCs during time and we will take just first occurence in DC change (there can and there are multiple records without DC change)
				--,rank() over (partition by "PRIM".compid,"PRIM".pdbid order by "PRIM".compid,"PRIM".pdbid,"PRIM".LASTMODIFIED) "RANK2"
			from
				SRC_SMD_ACCOUNTS2_TB "PRIM"
			left join lateral
			(
				select
					"SEC".LASTMODIFIED
					,"SEC".PDBID
					,"SEC".COMPID
				from
					SRC_SMD_ACCOUNTS2_TB "SEC"
				where
					"PRIM".PDBID = "SEC".PDBID
					and "PRIM".COMPID = "SEC".COMPID
					and "PRIM".LASTMODIFIED < "SEC".LASTMODIFIED and "PRIM".DCID != "SEC".DCID and rownum = 1 --will find whether there is another DC change in future, if not, it won't pair (returns null), hence 3000-01-01 is used as end time of this change
				order by COMPID,PDBID,LASTMODIFIED
			
			) "TER" 
			on "PRIM".PDBID = "TER".PDBID
				and "PRIM".COMPID = "TER".COMPID
			order by "PRIM".COMPID,"PRIM".PDBID,"PRIM".LASTMODIFIED
		) MA --left join SRC_NLCORP_CSI_TB NLCORP on MA.COMPID=NLCORP.NAME --just to get TOP200
		where
			RANK = 1
		)!';
	end;
	commit;
end;
~~~END_VERSION_SECTION~~~