IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qryMMSItem_UpdateDefaultStoreSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[qryMMSItem_UpdateDefaultStoreSettings]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[qryMMSItem_UpdateDefaultStoreSettings]
	@ItemID BIGINT,
	@InventoryUnitCost MONEY,
	@PurchaseUnit VARCHAR(50),
	@EasyOrderFlag BIT,
	@StocktakeUnit1 VARCHAR(50),
	@StocktakeUnit2 VARCHAR(50),
	@StocktakeUnit4 VARCHAR(50),
	@CountType BIGINT,
	@InUse BIT,
	@PerformStockCount INT,
	@Waste BIT,
	@ReportingUnitID SMALLINT,  
	@TaxableFlag SMALLINT, 
	@Transferable BIT,
	@OrderCategoryID INT,
	@UpdateEntityItem BIT,
	@AutoStockCount BIT,
	@ZeroLineOnDeliveryFlag BIT,
	@ReceiveUnitID SMALLINT, 
	@LocationType VARCHAR(50),
	@TaxCodeID SMALLINT, 
	@TaxEffectiveFrom DATETIME,
	@TaxDiscontinueFrom DATETIME,
	@EntityItemTaxableFlag SMALLINT, 
	@ZeroCostItem BIT,
	@DisableCountStkUnit1 BIT,
	@DisableCountStkUnit2 BIT,
	@DisableCountStkInvUnit BIT,
	@DisableCountStkUnit4 BIT,
	@CanSample BIT,
	@UPTAutoCalc BIT,
	@SafetyStockAutoCalc BIT,
	@IsSuggestedItem BIT,
	@IsKeyItem BIT,
	@ProcessingTime TINYINT,
	@UseProcessingTime BIT, 
	@CountryCode VARCHAR(3)
AS


IF @CountryCode IS NULL OR @CountryCode = ''
	SET @CountryCode = 'US'


-- Update default fields in tbItem
UPDATE tbItem 
SET
	InventoryUnitCost = @InventoryUnitCost,
	PurchaseUnit = NULLIF(@PurchaseUnit,''),
	EasyOrderFlag = @EasyOrderFlag,
	StocktakeUnit1 = NULLIF(@StocktakeUnit1,''),
	StocktakeUnit2 = NULLIF(@StocktakeUnit2,''),
	StocktakeUnit4 = NULLIF(@StocktakeUnit4,''),
	CountType = @CountType,
	PerformStockTake = @PerformStockCount,
	TaxableFlag = @TaxableFlag,
	OrderCategoryID = @OrderCategoryID,
    ZeroLineOnDeliveryFlag = @ZeroLineOnDeliveryFlag,
    ZeroCostItem = @ZeroCostItem
WHERE
	ItemID = @ItemID

IF EXISTS (SELECT DefaultEntityItemID FROM tbDefaultEntityItem WHERE ItemID = @ItemID)
BEGIN
	UPDATE tbDefaultEntityItem
		SET
			InUse = @InUse,
			Waste = @Waste,
			ReportingUnitID = @ReportingUnitID,
			Transferable = @Transferable,
			AutoStockCount = @AutoStockCount,
			ReceiveUnitID = @ReceiveUnitID,
			TaxCodeID = @TaxCodeID,
			TaxEffectiveFrom = @TaxEffectiveFrom,
			TaxDiscontinueFrom = @TaxDiscontinueFrom,
			DisableCountStkUnit1 = @DisableCountStkUnit1,
			DisableCountStkUnit2 = @DisableCountStkUnit2,
			DisableCountStkInvUnit = @DisableCountStkInvUnit,
			DisableCountStkUnit4 = @DisableCountStkUnit4,
			CanSample = @CanSample,
			UPTAutoCalc = @UPTAutoCalc,
			SafetyStockAutoCalc = @SafetyStockAutoCalc,
			IsSuggestedItem = @IsSuggestedItem,
			IsKeyItem = @IsKeyItem,
			ProcessingTime = @ProcessingTime,
			UseProcessingTime = @UseProcessingTime, 
			CountryCode = @CountryCode
		WHERE
			ItemID = @ItemID
END
ELSE
BEGIN
	INSERT INTO tbDefaultEntityItem
		(	
			ItemID,
			InUse ,
			Waste,
			ReportingUnitID,
			Transferable,
			AutoStockCount,
			ReceiveUnitID,
			TaxCodeID,
			TaxEffectiveFrom,
			TaxDiscontinueFrom,
			DisableCountStkUnit1,
			DisableCountStkUnit2,
			DisableCountStkInvUnit,
			DisableCountStkUnit4,
			CanSample,
			UPTAutoCalc,
			SafetyStockAutoCalc,
			IsSuggestedItem,
			IsKeyItem,
			ProcessingTime,
			UseProcessingTime,
			CountryCode
		)
	VALUES
		(
			@ItemID,
			@InUse,
			@Waste,
			@ReportingUnitID,
			@Transferable,
			@AutoStockCount,
			@ReceiveUnitID,
			@TaxCodeID,			
			@TaxEffectiveFrom,
			@TaxDiscontinueFrom,
			@DisableCountStkUnit1,
			@DisableCountStkUnit2,
			@DisableCountStkInvUnit,
			@DisableCountStkUnit4,
			@CanSample,
			@UPTAutoCalc,
			@SafetyStockAutoCalc,
			@IsSuggestedItem,
			@IsKeyItem,
			@ProcessingTime,
			@UseProcessingTime,
			@CountryCode
		)
END

-- Always try to insert to tbentityitem if record for this item does not exist for an entity

DECLARE @StocktakeUnit1ID AS SMALLINT
DECLARE @StocktakeUnit2ID AS SMALLINT
DECLARE @StocktakeUnit4ID AS SMALLINT

SET @StocktakeUnit1ID =	(SELECT UnitOfMeasureID FROM tbUnitOfMeasure WHERE UomName = @StocktakeUnit1)
SET @StocktakeUnit2ID =	(SELECT UnitOfMeasureID FROM tbUnitOfMeasure WHERE UomName = @StocktakeUnit2) 
SET @StocktakeUnit4ID =	(SELECT UnitOfMeasureID FROM tbUnitOfMeasure WHERE UomName = @StocktakeUnit4) 

INSERT INTO tbEntityItem (
	EntityID,
	ItemID,
	InventoryUnitCost,
	InUse,
	Waste,
	CountTypeDefault,
	CountTypeSpecific,
	EasyOrderFlag,
	PerformStockCount,
	StocktakeUnit1ID,
	StocktakeUnit2ID,
	StocktakeUnit4ID,
	ReportingUnitID,
	SpotCheck, -- this is to make sure spot check is not null so Manage_InventoryPage won't break
	OrderCategoryID,
	Transferable,
	IntroductionDate,
	NewItemForCountLocation,
	AutoStockCount,
	ZeroLineOnDeliveryFlag,
	NewItemDate,
	ReceiveUnitID,
	TaxableFlag,
	TaxCodeID,
	TaxEffectiveFrom,
	TaxDiscontinueFrom,
	DisableCountStkUnit1,
	DisableCountStkUnit2,
	DisableCountStkInvUnit,
	DisableCountStkUnit4,
	CanSample,
	UPTAutoCalc,
	SafetyStockAutoCalc,
	IsSuggestedItem,
	IsSuggestedItemByHeadOffice,
	IsKeyItem,
	ProcessingTime,
	UseProcessingTime
) 
(
	SELECT 
		e.EntityID,
		@ItemID,
		@InventoryUnitCost,
		@InUse,
		@Waste,
		0,
		@CountType,
		@EasyOrderFlag,
		@PerformStockCount,
		@StocktakeUnit1ID,
		@StocktakeUnit2ID,
		@StocktakeUnit4ID,
		@ReportingUnitID,
		0,
		@OrderCategoryID,
		@Transferable,
		dbo.fn_LocalTime(e.EntityID, GETUTCDATE(), GETDATE()),
		1,
		@AutoStockCount,
		@ZeroLineOnDeliveryFlag,
		DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),
		@ReceiveUnitID,
		@EntityItemTaxableFlag,
		@TaxCodeID,			
		@TaxEffectiveFrom,
		@TaxDiscontinueFrom,
		@DisableCountStkUnit1,
		@DisableCountStkUnit2,
		@DisableCountStkInvUnit, 
		@DisableCountStkUnit4,
		@CanSample,
		@UPTAutoCalc,
		@SafetyStockAutoCalc,
		@IsSuggestedItem,
		@IsSuggestedItem,
		@IsKeyItem,
		@ProcessingTime,
		@UseProcessingTime
	FROM  
		tbEntity e LEFT JOIN tbEntityItem ei
			ON (e.EntityID = ei.EntityID AND ei.ItemID = @ItemID)  
   WHERE EntityTypeID = 4 AND ei.EntityID IS NULL  
)

-- Other related tables (havent got default setting interface in wizard)
EXECUTE qryMMSItemQty_InsertByItem @ItemID  
EXECUTE qryMMSEntityLocationItem_InsertByItem @ItemID, @LocationType

CREATE TABLE #EntityItems (
	EntityID BIGINT, 
	ItemID BIGINT, 
	IsSuggestedItem BIT, 
	IsSuggestedItemByHeadOffice BIT,
	IsProcessingTimeFromHO BIT,
	ProcessingTime TINYINT,
	UseProcessingTime BIT
)
		
-- Get existing Entity Item configurations
INSERT INTO #EntityItems
SELECT EntityID, ItemID, IsSuggestedItem, IsSuggestedItemByHeadOffice,IsProcessingTimeFromHO, ProcessingTime, UseProcessingTime
FROM tbEntityItem (NOLOCK) 
WHERE ItemID = @ItemID
		
-- Truth table from INF-8821 - Relating the the Suggested Item flag at the entity item
-- When @IsSuggestedItem is set to FALSE, all field should be set to FALSE.
-- When @IsSuggestedItem is set to TRUE, only update IsSuggestedItemByHeadOffice 
--Set the same values for @IsKeyItem from CFAMX-6878
IF (@IsSuggestedItem = 0)
	UPDATE #EntityItems SET 
	IsSuggestedItem = 0, IsSuggestedItemByHeadOffice = 0
ELSE
    UPDATE #EntityItems SET
    IsSuggestedItem = 1 where IsSuggestedItemByHeadOffice = 0 and @IsSuggestedItem = 1
	UPDATE #EntityItems SET 
	IsSuggestedItemByHeadOffice = @IsSuggestedItem;

IF (@UseProcessingTime = 0)
	UPDATE #EntityItems SET 
	ProcessingTime = 0, IsProcessingTimeFromHO = 1, UseProcessingTime = 0
ELSE
    UPDATE #EntityItems SET
    ProcessingTime = @ProcessingTime, UseProcessingTime = @UseProcessingTime WHERE IsProcessingTimeFromHO = 1

UPDATE a  
SET	IsSuggestedItem = b.IsSuggestedItem,
	IsSuggestedItemByHeadOffice = b.IsSuggestedItemByHeadOffice,
	IsKeyItem = @IsKeyItem,
	ProcessingTime = b.ProcessingTime,
	IsProcessingTimeFromHO = b.IsProcessingTimeFromHO,
	UseProcessingTime = b.UseProcessingTime
FROM tbEntityItem a
INNER JOIN #EntityItems b ON a.EntityID = b.EntityID AND a.ItemID = b.ItemID
WHERE a.ItemID = @ItemID

IF @UpdateEntityItem = 1
BEGIN		
	UPDATE tbEntityItem 
		SET
			InventoryUnitCost = @InventoryUnitCost,
			InUse = @InUse,
			Waste = @Waste,
			CountTypeDefault = 0,
			CountTypeSpecific = @CountType,
			EasyOrderFlag = @EasyOrderFlag,
			PerformStockCount = @PerformStockCount,
			StocktakeUnit1ID = @StocktakeUnit1ID, 
			StocktakeUnit2ID = @StocktakeUnit2ID,
			StocktakeUnit4ID = @StocktakeUnit4ID,
			ReportingUnitID = @ReportingUnitID,
			OrderCategoryID = @OrderCategoryID,
			Transferable = @Transferable,
			AutoStockCount = @AutoStockCount,
		    ZeroLineOnDeliveryFlag = @ZeroLineOnDeliveryFlag,
		    ReceiveUnitID = @ReceiveUnitID,
			TaxableFlag = @EntityItemTaxableFlag,
			TaxCodeID = @TaxCodeID,
			TaxEffectiveFrom = @TaxEffectiveFrom,
			TaxDiscontinueFrom = @TaxDiscontinueFrom,
			DisableCountStkUnit1 = @DisableCountStkUnit1,
			DisableCountStkUnit2 = @DisableCountStkUnit2,
			DisableCountStkInvUnit = @DisableCountStkInvUnit,
			DisableCountStkUnit4 = @DisableCountStkUnit4,
			CanSample = @CanSample,
			UPTAutoCalc = @UPTAutoCalc,
			SafetyStockAutoCalc = @SafetyStockAutoCalc
		WHERE
			ItemID = @ItemID
END

GO
