Page History

-- SP

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_EnableWarehouseItem') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_EnableWarehouseItem


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[_AS_EnableWarehouseItem]
@StockCode Varchar(100),
@WHCode Varchar(100) = NULL
AS
BEGIN

Declare @LogMessage as Varchar(1024)
  
	
	--1) Get basic information
	Declare @stockLink int
	Declare @Group varchar(100)
	
	Select @stockLink =  (Select stocklink from stkitem where Code = @StockCode)
	IF (COALESCE(@stockLink,0) = 0)
		BEGIN
		Select @LogMessage = 'Stock code does not exist, aborting ('+@stockLink+')'
		print @LogMessage
		return
	END  
		
	Select @Group =  (Select ItemGroup from stkitem where Code = @StockCode)
	
	----DECLARE Temp Table, in which we will put the WH Codes to add the stock item to.
	DECLARE @tblWHCodes TABLE (WHCode varchar(100))
	IF (@WHCode IS NULL)
		BEGIN
			INSERT INTO @tblWHCodes (WHCode) (SELECT Code FROM WhseMst)
		END
	ELSE
		BEGIN
			INSERT INTO @tblWHCodes (WHCode) VALUES (@WHCode)
		END
	
	--2) Set STK item as WH item
	Update StkItem set Whseitem =1  where Code =@StockCode
	
	--3) Foreach the warehouses and add Item
				DECLARE @WhseLink int
				
				DECLARE WHCursor CURSOR FOR
				Select WhseLink from dbo.WhseMst WHERE WhseMst.Code IN (SELECT WHCode FROM @tblWHCodes)
				
				--Open WHCursor
				OPEN WHCursor
				
				FETCH NEXT FROM WHCursor INTO @WhseLink 
				WHILE @@FETCH_STATUS = 0
					BEGIN	
				 -- do not create new record, if record already exists
				 IF NOT EXISTS (SELECT ws.IdWhseStk FROM dbo.WhseStk ws WHERE ws.WHStockLink = @stockLink AND ws.WHWhseID = @WhseLink)
					BEGIN
					insert into dbo.WhseStk (
							   [WHWhseID]
							  ,[WHStockLink]
							  ,[WHStockGroup]
							  ,[WHQtyOnHand]
							  ,[WHQtyOnSO]
							  ,[WHQtyOnPO]
							  ,[WHQtyReserved]
							  ,[WHTTInv]
							  ,[WHTTCrn]
							  ,[WHTTGrv]
							  ,[WHTTRts]
							  ,[WHBarCode]
							  ,[WHRe_Ord_Lvl]
							  ,[WHRe_Ord_Qty]
							  ,[WHMin_Lvl]
							  ,[WHMax_Lvl]
							  ,[WHUsePriceDefs]
							  ,[WHUseInfoDefs]
							  ,[WHUseOrderDefs]
							  ,[WHUseDefaultDefs]
							  ,[WHPackCode]
							  ,[WHJobQty]
							  ,[iBinLocationID]
						   --   ,[fLGRVCount]
							  ,[WHMFPQty]
							  ,[WHUseSupplierDefs]
							  ,[fAverageCost]
							  ,[fLatestCost]
							  ,[fLowestCost]
							  ,[fHighestCost]
							  ,[fManualCost]
							  ,[fWhseLastGRVCost]
							  ,[WhseStk_iBranchID]
							  ,[WhseStk_iCreatedBranchID]
							  ,[WhseStk_iModifiedBranchID]
							  ,[WhseStk_iCreatedAgentID]
							  ,[WhseStk_iModifiedAgentID]
							  ,[WhseStk_iChangeSetID]
							  ,[bWHAllowNegStock]
							--  ,[fWHQtyToDeliver]
							  ,[WhseStk_fLeadDays]
							  ,[WHBuyingAgentID])
							  Values(
							  @WhseLink,  --WHWhseID
							  @stockLink, --WHStockLink
							  @Group,		--Group
							  0,			--qty
							  0,			--WHQtyOnSO
							  0,			--WHQtyOnPO
							  0,			--WHQtyReserved
							  1,			--WHTTInv
							  1,			--WHTTCrn
							  6,			--WHTTGrv
							  6,			--WHTTRts
							 '',			--WHBarCode 
							  0,			--WHRe_Ord_Lvl
							  0,			--WHRe_Ord_Qty
							  0,			--WHMin_Lvl
							  0,			--WHMax_Lvl
							  1,			--WHUsePriceDefs
							  1,			--WHUseInfoDefs
							  1,				--WHUseOrderDefs
							  1,			--WHUseDefaultDefs
							  '',			--WHPackCode
							  0,			--WHJobQty
							  0,			--iBinLocationID
							 -- NULL,			--fLGRVCount
							  0,			--WHMFPQty
							  1,			--WHUseSupplierDefs
							  0,			--fAverageCost
							  0,			--fLatestCost
							  0,			--fLowestCost
							  0,			--fHighestCost
							  0,			--fManualCost
							  0,			--fWhseLastGRVCost
							  0,			--WhseStk_iBranchID
							  0,			--WhseStk_iCreatedBranchID
							  0,			--WhseStk_iModifiedBranchID
							  1,			--WhseStk_iCreatedAgentID
							  1,			--WhseStk_iModifiedAgentID
							  1,			--WhseStk_iChangeSetID
							  0,			--bWHAllowNegStock
							--  NULL,			--fWHQtyToDeliver
							  0,			--WhseStk_fLeadDays
							  0)			--WHBuyingAgentID
					END
	  
					--CURSOR NEXT: WHCursor
					FETCH NEXT FROM WHCursor INTO @WhseLink
				END --WHILE @@FETCH_STATUS... WHCursor
				--Cleaning up cursor:
				CLOSE WHCursor
				DEALLOCATE WHCursor	
END