ALTER PROCEDURE [dbo].[qryMMSEntityVendor_UpdateVendorEntityAssignmentsByVendorEntityItems_CFA]
AS
BEGIN

Delete v From tbVendorEntity v
WHERE NOT EXISTS(Select * from tbVendorEntityItem WITH (NOLOCK) WHERE EntityID = v.EntityID AND VendorID = v.VendorID AND (DiscontinueDate IS NULL OR DiscontinueDate > GETDATE()))

-- 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 
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

INSERT INTO tbVendorEntity (EntityID, VendorId, OrderItemCode, AutoClearBackOrder,LeadTime, EmailOrder,	CreateQuatroOrder, AutoReceiveDelayMinutes)
Select distinct vei.EntityID, vei.VendorID,(Isnull(OrderItemCode,'VendorCode')) OrderItemCode, AutoClearBackOrder, LeadTime,(Isnull(EmailOrder,0)) EmailOrder, ElectronicOrder, AutoReceiveDelayMinutes   
From tbVendorEntityItem vei WITH (NOLOCK)   
left Join tbVendor V WITH (NOLOCK) on V.VendorID = vei.VendorID   
left join tbVendorSettingsTemplate VST WITH (NOLOCK) on VST.VendorSettingsTemplateId = V.VendorSettingsTemplateId    
left join #tmpStoreConfigValue scv on scv.EntityID = vei.EntityID 
 WHERE NOT Exists (Select VendorID FROM tbVendorEntity WITH (NOLOCK) WHERE EntityID = vei.EntityID AND VendorID = vei.VendorID)   
 AND (DiscontinueDate IS NULL OR DiscontinueDate > GETDATE())   
 AND NOT (v.VendorZoneId Is Not Null AND ( @IsGlobalChecked = 1 OR (scv.Value is not null and scv.Value = 'True'))) 
		   
DROP TABLE #tmpStoreConfigValue

END
GO



