SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects WHERE id = object_id(N'[Testing].[MxOrderGuide_ItemCreation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Testing].[MxOrderGuide_ItemCreation]
GO

CREATE PROCEDURE [Testing].[MxOrderGuide_ItemCreation]
	@ItemCode VARCHAR(30),
	@ItemName VARCHAR(30),	
	@EntityId BIGINT,
	@VendorId1 BIGINT,
	@VendorId2 BIGINT = NULL
AS
BEGIN

	-- Create New Item
	DECLARE @ItemId BIGINT = (select ItemId from tbItem where ItemCode = @ItemCode and [Description] = @ItemName)
	IF @ItemId IS NULL
	BEGIN
		INSERT INTO tbItem VALUES(
			@ItemCode, @ItemName, '', 6, NULL, '', 0.00, '','','', '75 Ct Case', NULL,
			'75 Ct Case', 'Each', 0.00, 0, 0, 0, NULL, NULL, NULL, 0, NULL,	'IV',
			'Regular', 0, 1, NULL, 0.00, NULL, NULL, NULL, NULL, '', NULL, 1,
			NULL, 1, '75 Ct Case', NULL, 1, 0, 1, NULL, 0, NULL, '', 0, '',	NULL,
			NULL, 0, 0, 0, NULL, 0,	'', NULL, 1, NULL, NULL, NULL, 0, 0, '', 0,
			NULL, 1, NULL, 0, NULL, NULL, NULL, 0, 0, 0, NULL, NULL, NULL, NULL)
		SET @ItemId = @@Identity
	END

	-- Create VendorItem for Item and Vendor 1
	IF NOT EXISTS (select * from tbVendorItem where itemId = @ItemId and vendorId = @VendorId1)
	BEGIN 
		INSERT INTO tbVendorItem VALUES (
			@VendorId1, @ItemId, 0.00, @ItemCode, NULL, @ItemName, 15, NULL, NULL, 0, 
			DATEADD(Day, 1, GetDate()), --Introduction Date
			NULL,					  --Discontinue Date
			NULL, NULL,	NULL, NULL, NULL)
	END

	-- Create VendorItem for Item and Vendor 2
	IF (NOT EXISTS (select * from tbVendorItem where itemId = @ItemId and vendorId = @VendorId2) ) And @VendorId2 IS NOT NULL
	BEGIN 
		INSERT INTO tbVendorItem VALUES (
			@VendorId2, @ItemId, 0.00, @ItemCode, NULL, @ItemName, 15, NULL, NULL, 0, 
			DATEADD(Day, 1, GetDate()), --Introduction Date
			NULL,					  --Discontinue Date
			NULL, NULL,	NULL, NULL, NULL)
	END

	-- Make sure VendorItems got created
	IF NOT EXISTS (select * from tbVendorItem where itemId = @ItemId and vendorId = @VendorId1)
		RAISERROR ('Problem creating VendorItem for Vendor 1', 18, 1)
	IF (NOT EXISTS (select * from tbVendorItem where itemId = @ItemId and vendorId = @VendorId2) ) And @VendorId2 IS NOT NULL
		RAISERROR ('Problem creating VendorItem for Vendor 2', 18, 1)

	-- Create EntityItem for Item and Store
	IF NOT EXISTS (select * from tbEntityItem where itemId = @ItemId and entityId = @EntityId)
	BEGIN
		INSERT INTO tbEntityItem VALUES (
			@EntityId, @ItemId, 0.00, 1, '2000-01-01 00:00:00.000', NULL, NULL, 1, 0.00, --SalesUnitPrice
			0, 0, 0, 0, NULL, NULL, NULL, 0.00, 0.00, NULL, @VendorId1, 0.00, NULL, NULL, --LastSalesReturnDate
			NULL, 0, 0, 1, 11200, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, --OrderCategoryID
			NULL, NULL, NULL, 0, 0, 0, NULL, 0, 1, 0, 7, NULL, NULL, NULL, NULL, 0, NULL, -- TaxableFlag
			NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, 0, 0, 0, NULL, NULL, --IsKeyItem
			NULL, 0, 0, 0, NULL, NULL)
	END
	
	-- Make sure EntityItem was Created
	IF NOT EXISTS (select * from tbEntityItem where itemId = @ItemId and entityId = @EntityId)
		RAISERROR ('Problem creating EntityItem', 18, 1)

	-- Create EntityItemDailyStockBalance for Item and Store
	IF NOT EXISTS (select * from tbEntityItemDailyStockBalance where itemId = @ItemId and entityId = @EntityId)
	BEGIN
		INSERT INTO tbEntityItemDailyStockBalance VALUES (
			@EntityId, @ItemId, '2000-01-01 00:00:00.000', '2000-01-01 00:00:00.000')
	END
	
	-- Make sure EntityItemDailyStockBalance was Created
	IF NOT EXISTS (select * from tbEntityItemDailyStockBalance where itemId = @ItemId and entityId = @EntityId)
		RAISERROR ('Problem creating tbEntityItemDailyStockBalance', 18, 1)

	/* Item Should Now Appear on Assign Vendor Item page. They won't appear on the Order Guide until the rules enging makes the Vendor Entity Item */

	RETURN @ItemId

END
GO
