-- B1 DEPENDS: AFTER:SP:_TmSp_BootCreateGlobalTempTables2 AFTER:PT:PROCESS_END

create procedure TmSp_TransferRecordsToUILM(in MsgFromCopy INT,in MsgToCopy INT)
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS
	MaxMIDfromUILM INT; 
	MessageIDDelta INT;
	cnum int;
	col nvarchar(256);
	sqll varchar(4096);
	extFields nclob;
	orgUDFField varchar(20);
	countUDFField int;
	curFieldID int;
	findInUILM1 int;
	sqlStr nclob;
BEGIN
	SELECT IFNULL(MAX("MessageID"),0) into MaxMIDfromUILM FROM UILM; 
	MessageIDDelta := :MaxMIDfromUILM - :MsgFromCopy;
	
	delete from "TmSpTransferRecordsToUILM_TMP";
	
	insert into "TmSpTransferRecordsToUILM_TMP" 
	SELECT SC.COLUMN_NAME, SC.POSITION
	FROM "PUBLIC".TABles SO INNER JOIN "PUBLIC"."TABLE_COLUMNS" SC ON SO.TABLE_OID = SC.TABLE_OID  
	WHERE SO.Table_name = 'OILM' and SC.POSITION <> 1 and SC.SCHEMA_NAME = CURRENT_SCHEMA 	ORDER BY SC.POSITION;
	
	sqll := 'INSERT INTO UILM SELECT "MessageID" + ' || cast(:MessageIDDelta as char(128));
	
	select count(*) into cnum from "TmSpTransferRecordsToUILM_TMP";
	--select cnum from dummy;
	while (:cnum) > 0 do
		select top 1 "NAME" into col from "TmSpTransferRecordsToUILM_TMP" order by "POS";
		delete from "TmSpTransferRecordsToUILM_TMP" where name = :col;
		sqll := :sqll || ', "' || :col || '"';
		cnum := :cnum - 1;
	END while;
	
	sqll := :sqll || ' FROM OILM WHERE "MessageID" > '
		|| cast (:MsgFromCopy as char(128)) || ' AND "MessageID" <= '
		|| cast (:MsgToCopy as char(128));
	exec (:sqll);

	select Min("FieldID") , count(1) into curFieldID,countUDFField FROM CUFD WHERE "TableID" = 'UILM1';

	extFields := '';
	curFieldID := :curFieldID - 1;

	while :countUDFField > 0
		do
		select TOP 1 "AliasID" , "FieldID" into orgUDFField, curFieldID FROM CUFD WHERE "TableID" = 'UILM1' AND "FieldID" > :curFieldID  ORDER BY "FieldID";

		SELECT count(1) into findInUILM1 FROM CUFD WHERE "TableID" = 'UILM1' and "AliasID" = :orgUDFField;
		
		if :findInUILM1 > 0
		then 
			extFields := :extFields || ', ILM1."U_' || :orgUDFField || '" AS "U_' || :orgUDFField || '" ';
		END if;
		countUDFField := :countUDFField - 1;
	END while;
	
	sqlStr := 'INSERT INTO UILM1 ' ||
	 	'SELECT "MessageID" + ' || :MessageIDDelta || ', "ItemCode", "SysNumber", "Quantity", "MdAbsEntry"' ||
	 	:extFields ||
	'FROM "ILM1"' ||
		'WHERE "MessageID" > ' || :MsgFromCopy || ' AND "MessageID" <= ' || :MsgToCopy;
	exec (:sqlStr);
	
	INSERT INTO UILM2
		SELECT "MessageID" + :MessageIDDelta,"AccountId","AcctCode","DebitCredi"
	FROM "ILM2"
		WHERE "MessageID" > :MsgFromCopy AND "MessageID" <= :MsgToCopy; 
	
	INSERT INTO UILM3
		SELECT "MessageID" + :MessageIDDelta, "LineID", "POLine", "ItemType", "ItemCode", "LocType", "LocCode",
		"Quantity", "TotalLC", "BaseAbsEnt", "BaseType", "BaseLine", "StgSeqNum", "StgEntry", "StgDesc"
	FROM "ILM3"
		WHERE "MessageID" > :MsgFromCopy AND "MessageID" <= :MsgToCopy; 

	delete from "TmSpTransferRecordsToUILM_TMP";
end;