~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__TMP_SOTD_CUSTOMERS_TB__PROC
table=TMP_SOTD_CUSTOMERS_TB
~~~END_MAIN_SECTION~~~

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

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__TMP_SOTD_CUSTOMERS_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_SOTD_CUSTOMERS_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		begin
		  execute immediate 'CREATE TABLE "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" 
		   (	"NAME" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"CUSTOMER" VARCHAR2(512 BYTE) COLLATE "USING_NLS_COMP", 
			"EMAIL" VARCHAR2(128 BYTE) COLLATE "USING_NLS_COMP", 
			"CATEGORY" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"STATUS" VARCHAR2(32 BYTE) COLLATE "USING_NLS_COMP", 
			"TOP200" VARCHAR2(3 BYTE) COLLATE "USING_NLS_COMP", 
			"FROM_DATE" DATE, 
			"TO_DATE" DATE
		   )';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX1" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("NAME")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX10" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("NAME", "TO_DATE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX2" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("CUSTOMER")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX3" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("EMAIL")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX4" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("CATEGORY")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX5" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("STATUS")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX6" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("TOP200")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX7" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("FROM_DATE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX8" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("TO_DATE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB_INDEX9" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" ("NAME", "FROM_DATE")';
		  execute immediate 'ALTER TABLE "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_TB" MODIFY ("NAME" NOT NULL ENABLE)';
		end;!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into tmp_sotd_customers_tb
		(
		select
			NAME
			,CUSTOMER
			,EMAIL
			,CATEGORY
			,STATUS
			,TOP200
			,FROM_DATE
			,TO_DATE
		from
		(
			select
				"PRIM".NAME
				,"PRIM".CUSTOMER
				,"PRIM".EMAIL
				,"PRIM".CATEGORY
				,"PRIM".STATUS
				,"PRIM".TOP200
				,CASE WHEN (rank() over (partition by "PRIM".NAME order by "PRIM".NAME,"PRIM".DDATE)) = 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".DDATE
					END "FROM_DATE"
				,case
					when "TER".DDATE is null then TO_DATE('2100-01-01', 'yyyy-mm-dd')
					else "TER".DDATE
				end "TO_DATE"
				,"PRIM".TAG
			from
				tmp_sotd_customers_all_dates_tb "PRIM"
				left join lateral
				(
					select
						NAME
						,DDATE
					from 
					tmp_sotd_customers_all_dates_tb "SEC"
					where
						"PRIM".NAME = "SEC".NAME
						and "PRIM".DDATE < "SEC".DDATE
						and rownum = 1
					order by
						NAME,DDATE
				) "TER" on "PRIM".NAME = "TER".NAME
				order by "PRIM".NAME, "PRIM".DDATE
		)
		where
			TAG=1
		)!';
	end;
	commit;
end;
~~~END_VERSION_SECTION~~~
