~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
procedure=CREATE__TMP_SOTD_CUSTOMERS_ALL_DATES_TB__PROC
table=TMP_SOTD_CUSTOMERS_ALL_DATES_TB
~~~END_MAIN_SECTION~~~


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

[SQL_SCRIPT___DEFAULT]

CREATE OR REPLACE PROCEDURE CREATE__TMP_SOTD_CUSTOMERS_ALL_DATES_TB__PROC AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE 'DROP TABLE TMP_SOTD_CUSTOMERS_ALL_DATES_TB';exception WHEN OTHERS THEN NULL;end;commit;
	begin
		EXECUTE IMMEDIATE q'!
		begin
		  execute immediate 'CREATE TABLE "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_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", 
			"DDATE" DATE, 
			"TAG" NUMBER
		   )';
		end;!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		insert into tmp_sotd_customers_all_dates_tb 
		select * from (
		select
						"NLCORP".NAME
						,max("NLCORP".CUSTOMER) "CUSTOMER"
						,max("NLCORP".EMAIL) "EMAIL"
						,max(case when "NLCORP".DDATE = "DATES".DDATE then "NLCORP".CATEGORY end) "CATEGORY"
						,max("NLCORP".STATUS) "STATUS"
						,max("NLCORP".TOP200) "TOP200"
						,"DATES".DDATE DDATE
						,sum (case
							when "NLCORP".DDATE = "DATES".DDATE then 1
							else 0
						end) "TAG"
					from SRC_NLCORP_CSI_TB "NLCORP"
					cross join
						(select distinct ddate from SRC_NLCORP_CSI_TB) "DATES"
					group by
						"NLCORP".NAME
						,"DATES".DDATE
					order by "NLCORP".NAME,"DATES".DDATE
		)!';
	end;
	commit;
	begin
		EXECUTE IMMEDIATE q'!
		begin
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX1" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("NAME")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX2" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("CUSTOMER")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX3" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("EMAIL")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX4" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("CATEGORY")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX5" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("STATUS")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX6" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("TOP200")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX7" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("DDATE")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX8" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("TAG")';
		  execute immediate 'CREATE INDEX "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB_INDEX9" ON "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" ("NAME", "DDATE")';
		  execute immediate 'ALTER TABLE "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" MODIFY ("NAME" NOT NULL ENABLE)';
		  execute immediate 'ALTER TABLE "OTHQ_OWN"."TMP_SOTD_CUSTOMERS_ALL_DATES_TB" MODIFY ("DDATE" NOT NULL ENABLE)';
		end;!';
	end;
	commit;
end;
~~~END_VERSION_SECTION~~~
