USE [CFAStaging]
GO

/****** Object:  StoredProcedure [dbo].[qryMMSUpdateOrderGuideAfterVendorChange]    Script Date: 5/4/2023 10:10:11 PM ******/
DROP PROCEDURE [dbo].[qryMMSUpdateOrderGuideAfterVendorChange]
GO

/****** Object:  StoredProcedure [dbo].[qryMMSUpdateOrderGuideAfterVendorChange]    Script Date: 5/4/2023 10:10:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[qryMMSUpdateOrderGuideAfterVendorChange]
AS
BEGIN
	-- the usage of this table is for not to re-establish relationship for vendor entity 
	-- if the vendor belongs to vendor assignment national vendors and entity belongs to the pilot stores 
	-- Also used in qryMMSEntity_GetNewStoresForOrderGuideRules
	IF object_id('tempdb..#tmpStoreConfigValue') IS NOT NULL  
	DROP TABLE [dbo].[#tmpStoreConfigValue]  
		
	CREATE TABLE #tmpStoreConfigValue
	(
	   EntityId BIGINT,
	   Value VARCHAR(10),
	   [Type] INT
	)

	INSERT INTO #tmpStoreConfigValue
	EXEC qryMMSConfigurationSetting_GetByEnumForCache 100052 -- this is the config enum value for [Administration_InventoryItems_UseInFORMVendorItemAssignment] 

	DECLARE @IsGlobalChecked bit
	SELECT @IsGlobalChecked =  CASE WHEN EXISTS(SELECT 1 FROM #tmpStoreConfigValue WHERE EntityId = 0 and Value = 'True')
									THEN 1 ELSE 0
								END


	create table #tempPriorVendors  ([EntityId] bigint,                      
						 [VendorId] bigint,
						 [PreviousVendorId] bigint,
						 [UsingInFORMVendorItemAssignment] bit)
                     
	INSERT INTO #tempPriorVendors(
		EntityId,
		VendorId,
		PreviousVendorId,
		UsingInFORMVendorItemAssignment)
	SELECT 
		ve.EntityID, 
		ve.VendorId, 
		ve.PreviousVendorId,
		CASE  
			-- Either the assigned config is True there is no assigned setting and the default is True
			WHEN (scv.Value = 'True' OR (scv.Value is null and @IsGlobalChecked = 1)) THEN 1
			ELSE 0
			END 
	FROM tbVendorEntity ve with (nolock)
	LEFT JOIN #tmpStoreConfigValue scv on scv.EntityID = ve.EntityID 
	WHERE VendorId <> PreviousVendorId 

	DECLARE @EntityId bigint
	DECLARE @VendorId bigint
	DECLARE @PreviousVendorId bigint
	DECLARE @UsingInFORMVendorItemAssignment bit

	DECLARE cur CURSOR FOR SELECT EntityID, VendorId, PreviousVendorId, UsingInFORMVendorItemAssignment
	FROM #tempPriorVendors  
	OPEN cur

	FETCH NEXT FROM cur INTO @EntityId, @VendorId, @PreviousVendorId, @UsingInFORMVendorItemAssignment

	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		IF @UsingInFORMVendorItemAssignment = 1
		BEGIN
			UPDATE A 
			SET 
				A.DiscontinueDateOverridden = B.DiscontinueDateOverridden,
				A.DiscontinueDateOverrideEnabled = B.DiscontinueDateOverrideEnabled
			FROM tbVendorEntityItem as A
			JOIN tbVendorEntityItem B  on A.EntityID = B.EntityID and A.VendorCode = B.VendorCode
			WHERE A.EntityID = @EntityId and A.VendorID = @VendorId and B.VendorID = @PreviousVendorId
                                  
			SELECT @EntityId, @VendorId, @PreviousVendorId
		END

		-- Once we have copied these settings over (or skipped this), we no longer need to look at this any more
		UPDATE tbVendorEntity SET PreviousVendorId = NULL WHERE EntityID = @EntityId AND VendorID = @VendorId

	FETCH NEXT FROM cur INTO @EntityId, @VendorId, @PreviousVendorId, @UsingInFORMVendorItemAssignment
	END
	CLOSE cur    
	DEALLOCATE cur
	
	DROP TABLE #tmpStoreConfigValue
	DROP Table #tempPriorVendors

END
GO


