CREATE PROCEDURE [dbo].[MisUpdateProductWareLinkNumbers] AS
SET NOCOUNT ON

/*
   Changes:
   KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
   SG 20170627: Changed cursor type to FORWARD_ONLY (Fast_forward caused locks in MISTable)
   SG 20130103, CR 050510: Linking og product and package on SystemDate changed. 
   OF 20120706: Ext: New mis attempt rev. 1
   OF 20120608: New: For linking product and packages with productWareLinkNumber in forecast (booked) when product price in actual
*/

Declare @SystemDate DateTime
Declare @SystemDateStr char(8)
Declare @YesterdayStr char(8);

Declare @ReferenceNumber int
Declare @GuestNumber int
Declare @ProductWareLinkNumber BIGint
Declare @TempPWLN int

SELECT @SystemDateStr = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1 -- Only one record  
SET @SystemDate = Convert(DateTime, @SystemDateStr) 
SET @YesterdayStr = Convert(Char(8), @SystemDate - 1, 112)

-- For MisTable
Declare ProductWareLinkCur Cursor FORWARD_ONLY Read_only For
	select ReferenceNumber, GuestNumber, ProductWareLinkNumber from MISTABLE WITH (NOLOCK) 
	where 
		((DateStamp = @YesterdayStr and ProductWareLinkNumber = -100)
		  or (DateStamp = @SystemDateStr and ProductWareLinkNumber = -200)) and
		IsPackage = 1  
-- -100: Room	-200: Day product and Room same day
Open ProductWareLinkCur 
FETCH Next FROM ProductWareLinkCur INTO @ReferenceNumber, @GuestNumber, @TempPWLN
While(@@FETCH_STATUS <> -1) 
begin
	select @ProductWareLinkNumber = ProductWareLinkNumber from MISTABLE WITH (NOLOCK) 
	where 
		((DateStamp = @YesterdayStr and @TempPWLN = -100)
		  or (DateStamp = @SystemDateStr and @TempPWLN = -200)) and
		ReferenceNumber = @ReferenceNumber and 
		GuestNumber = @GuestNumber and 
		ProductWareLinkNumber > 0 and
		IsPackage = 0
	if (@ProductWareLinkNumber is not null)
	begin
		Update MISTABLE Set ProductWareLinkNumber = @ProductWareLinkNumber
		Where
		((DateStamp = @YesterdayStr and ProductWareLinkNumber = -100)
		  or (DateStamp = @SystemDateStr and ProductWareLinkNumber = -200)) and
			ReferenceNumber = @ReferenceNumber and 
			FolioNumber = -1 and 
			GuestNumber = @GuestNumber and 
			IsPackage = 1 
	end
	FETCH Next FROM ProductWareLinkCur INTO @ReferenceNumber, @GuestNumber, @TempPWLN
end
Close ProductWareLinkCur 
Deallocate ProductWareLinkCur
