-- B1 DEPENDS: BEFORE:PT:PROCESS_START

CREATE PROCEDURE ATP_A0_CREATE_DB_TYPES ()
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER

AS
cnt int;
BEGIN
	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_INT_QTY' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_INT_QTY CASCADE');
	end if;
	
	EXEC('
		CREATE TYPE ATP_INT_QTY AS TABLE(
			DATE INT,
			QTY DECIMAL(21,6)
		)
	');

	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_DATE_QTY' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_DATE_QTY CASCADE');
	end if;
	
	EXEC('
		CREATE TYPE ATP_DATE_QTY AS TABLE(
			"Date" DATE,
			"Qty" DECIMAL(21,6)
		)
	');

	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_COUNTER_DATE_QTY' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_COUNTER_DATE_QTY CASCADE');
	end if;
	
	EXEC('
		CREATE TYPE ATP_COUNTER_DATE_QTY AS TABLE(
			"Counter" INTEGER,
			"Date" DATE,
			"Qty" DECIMAL(21,6)
		)
	');

	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_T_NUMB_RECHECK' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_T_NUMB_RECHECK CASCADE');
	end if;
	
	EXEC('
		CREATE TYPE ATP_T_NUMB_RECHECK AS TABLE(
			"Counter" INTEGER,
			"ObjType" NVARCHAR (20),
			"DocEntry" INTEGER,
			"DocLineNum" INTEGER
		)
	');

	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_T_CONF' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_T_CONF CASCADE');
	end if;
		
	EXEC('
		CREATE TYPE ATP_T_CONF AS TABLE(
			"ObjType" NVARCHAR (20),
			"DocEntry" INTEGER,
			"DocLineNum" INTEGER,
			"SchdLine" INTEGER,
			"CfmDate" DATE,
			"CfmQty" DECIMAL(21,6)
			)
	');
	
	SELECT COUNT(*) INTO cnt FROM "PUBLIC".TABLES WHERE TABLE_NAME = 'ATP_T_DONATORS' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP TYPE ATP_T_DONATORS CASCADE');
	end if;
	
	EXEC('
		CREATE TYPE ATP_T_DONATORS AS TABLE(
			"ObjType" NVARCHAR (20),
			"DocEntry" INTEGER,
			"DocLineNum" INTEGER,
			"SchdLine" INTEGER,
			"CfmDate" DATE,
			"CfmQty" DECIMAL(21,6),
			"DonQty" DECIMAL(21,6)
		)
	');
	
	SELECT COUNT(*) INTO cnt FROM "PUBLIC".SEQUENCES WHERE SEQUENCE_NAME = 'OSLD_S' AND SCHEMA_NAME = CURRENT_SCHEMA;
	if :cnt > 0 then
		exec ('DROP SEQUENCE OSLD_S CASCADE');
	end if;
	
	EXEC('CREATE SEQUENCE OSLD_S START WITH 1');
END;