SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV, Alex
-- Create date: 2022-06-23
-- Description: Creating stkItem
-- this stored procedure creates inventory items with the most basic options
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[_as_AddUpdateStockItem]
@Code as varchar(100),
@Desc as varchar(30),
@GroupCode as varchar(20),
@IsServiceItem as bit,
@IsWarehouse as bit,
@IsLot as bit,
@ItemCategoryCode varchar(100),
@cEachDesc varchar(10) = '',
@StockLink bigint OUTPUT /*used for returning the created item id.*/
AS
BEGIN
/*Usage:
EXEC _as_AddUpdateStockItem 'Code1','Desc1','001',0,1,0,'002'
select * from stkItem where Code IN ('Code1','Code2')
select * from _etblStockDetails where StockID IN (28118,28119)
SELECT
CONCAT('EXEC _as_AddUpdateStockItem ''',ItemCode,''',''',Description,''','''',Category,''',',0,1,0,'002'')
FROM pp_Inventory
select * from pp_Inventory
select * from GrpTbl --138
select * from _etblStockCategories --138
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*steps:
1) check if code exists. if yes, then update.
2) if not, then create in both stkitem and _etblStockDetails*/
--DECLARE @StockLink bigint
DECLARE @GroupID int = COALESCE((SELECT idGrpTbl FROM GrpTbl G WHERE G.StGroup = @GroupCode),0)
DECLARE @ItemCategoryID int = COALESCE((SELECT idStockCategories FROM _etblStockCategories IC WHERE IC.cCategoryName = @ItemCategoryCode),0)
DECLARE @tblStockID TABLE (ID bigint)
IF EXISTS(SELECT * FROM stkitem WHERE [Code] = @Code)
BEGIN
UPDATE stkitem
SET
Description_1 = @Desc,
ServiceItem = @IsServiceItem,
WhseItem = @IsWarehouse,
bLotItem = @IsLot
OUTPUT inserted.StockLink INTO @tblStockID(ID)
WHERE [Code] = @Code
SELECT TOP 1 @StockLink = ID FROM @tblStockID
END
ELSE
BEGIN
/*Create item.
First prefetch other data*/
INSERT INTO [dbo].[StkItem]
([Code]
,[Description_1]
,[Description_2]
,[Description_3]
,[ServiceItem]
,[ItemActive]
,[WhseItem]
,[SerialItem]
,[DuplicateSN]
,[StrictSN]
,[BomCode]
,[SMtrxCol]
,[PMtrxCol]
,[cModel]
,[cRevision]
,[cComponent]
,[dDateReleased]
,[dStkitemTimeStamp]
,[iInvSegValue1ID]
,[iInvSegValue2ID]
,[iInvSegValue3ID]
,[iInvSegValue4ID]
,[iInvSegValue5ID]
,[iInvSegValue6ID]
,[iInvSegValue7ID]
,[cExtDescription]
,[cSimpleCode]
,[bCommissionItem]
,[bLotItem]
,[iLotStatus]
,[bLotMustExpire]
,[iItemCostingMethod]
,[iEUCommodityID]
,[iEUSupplementaryUnitID]
,[fNetMass]
,[iUOMStockingUnitID]
,[iUOMDefPurchaseUnitID]
,[iUOMDefSellUnitID]
,[fStockGPPercent]
,[cEachDescription]
,[cMeasurement]
,[fBuyLength]
,[fBuyWidth]
,[fBuyHeight]
,[fBuyArea]
,[fBuyVolume]
,[cBuyWeight]
,[cBuyUnit]
,[fSellLength]
,[fSellWidth]
,[fSellHeight]
,[fSellArea]
,[fSellVolume]
,[cSellWeight]
,[cSellUnit]
,[bOverrideSell]
,[bUOMItem]
,[bDimensionItem]
,[bVASItem]
,[bAirtimeItem]
,[iAttributeGroupID]
,[xAttribute]
,[StkItem_iBranchID]
,[bSyncToSOT]
,[iMajorIndustryCodeID]
,[bImportedServices]
)
OUTPUT inserted.StockLink INTO @tblStockID (ID)
VALUES
(@Code --<Code, varchar(400),>
,@Desc --<Description_1, varchar(50),>
,'' --<Description_2, varchar(50),>
,'' --<Description_3, varchar(50),>
,@IsServiceItem --<ServiceItem, bit,>
,1 --<ItemActive, bit,>
,@IsWarehouse --<WhseItem, bit,>
,0 --<SerialItem, bit,>
,0 --<DuplicateSN, bit,>
,1 --<StrictSN, bit,>
,NULL --<BomCode, varchar(1),>
,0 --<SMtrxCol, int,>
,0 --<PMtrxCol, int,>
,''--<cModel, varchar(50),>
,''--<cRevision, varchar(50),>
,''--<cComponent, varchar(50),>
,getdate() --<dDateReleased, smalldatetime,>
,getdate() --<dStkitemTimeStamp, datetime,>
,0 --<iInvSegValue1ID, int,>
,0 --<iInvSegValue2ID, int,>
,0 --<iInvSegValue3ID, int,>
,0 --<iInvSegValue4ID, int,>
,0 --<iInvSegValue5ID, int,>
,0 --<iInvSegValue6ID, int,>
,0 --<iInvSegValue7ID, int,>
,@Desc --<cExtDescription, varchar(255),>
,@Code --<cSimpleCode, varchar(20),>
,1 --<bCommissionItem, bit,>
,@IsLot --<bLotItem, bit,>
,0 --<iLotStatus, int,>
,0 --<bLotMustExpire, bit,>
,0 --<iItemCostingMethod, int,>
,0 --<iEUCommodityID, int,>
,0 --<iEUSupplementaryUnitID, int,>
,0 --<fNetMass, float,>
,0 --<iUOMStockingUnitID, int,>
,0 --<iUOMDefPurchaseUnitID, int,>
,0 --<iUOMDefSellUnitID, int,>
,0 --<fStockGPPercent, real,>
,@cEachDesc --<cEachDescription, varchar(30),>
,'' --<cMeasurement, varchar(5),>
,0 --<fBuyLength, float,>
,0 --<fBuyWidth, float,>
,0 --<fBuyHeight, float,>
,0 --<fBuyArea, float,>
,0 --<fBuyVolume, float,>
,0 --<cBuyWeight, float,>
,0 --<cBuyUnit, varchar(5),>
,0 --<fSellLength, float,>
,0 --<fSellWidth, float,>
,0 --<fSellHeight, float,>
,0 --<fSellArea, float,>
,0 --<fSellVolume, float,>
,0 --<cSellWeight, float,>
,0 --<cSellUnit, varchar(5),>
,0 --<bOverrideSell, bit,>
,0 --<bUOMItem, bit,>
,0 --<bDimensionItem, bit,>
,0 --<bVASItem, bit,>
,0 --<bAirtimeItem, bit,>
,0 --<iAttributeGroupID, int,>
,null --<xAttribute, xml,>
,0 --<StkItem_iBranchID, int,>
,0 --<bSyncToSOT, bit,>
,0 --<iMajorIndustryCodeID, int,>
,0 --<bImportedServices, bit,>
)
SELECT TOP 1 @StockLink = ID FROM @tblStockID
DECLARE @tblWHIDs TABLE (WHID int)
IF (@IsWarehouse = 1)
BEGIN
INSERT INTO @tblWHIDs (WHID) VALUES (-1) /*-1 default record, if it's a wh item*/
INSERT INTO @tblWHIDs (WHID) (SELECT WhseLink FROM WhseMst W WHERE W.DefaultWhse = 1) /*master WH*/
END
ELSE
BEGIN
INSERT INTO @tblWHIDs (WHID) VALUES (0) /*only 0 record if item is not a WH item.*/
END
INSERT INTO [dbo].[_etblStockDetails]
([StockID]
,[WhseID]
,[GroupID]
,[BuyingAgentID]
,[ItemCategoryID]
,[BarcodeID]
,[TTInvID]
,[TTCrnID]
,[TTGrvID]
,[TTRtsID]
,[PackCodeID]
,[AllowNegStock]
,[ReorderLevel]
,[ReorderQty]
,[MinLevel]
,[MaxLevel]
,[LeadDays]
,[_etblStockDetails_iBranchID]
,[iDefaultSalesBinID]
,[iDefaultPurchasesBinID])
--VALUES
(SELECT
@StockLink --<StockID, int,>
,WHID --<WhseID, int,>
,@GroupID --<GroupID, int,>
,0 --<BuyingAgentID, int,>
,@ItemCategoryID --<ItemCategoryID, int,>
,0 --<BarcodeID, int,>
,iINVTaxTypeID --<TTInvID, int,>
,iCRNTaxTypeID --<TTCrnID, int,>
,iGRVTaxTypeID --<TTGrvID, int,>
,iRTSTaxTypeID --<TTRtsID, int,>
,0 --<PackCodeID, int,>
,0 --<AllowNegStock, bit,>
,0 --<ReorderLevel, float,>
,0 --<ReorderQty, float,>
,0 --<MinLevel, float,>
,0 --<MaxLevel, float,>
,0 --<LeadDays, float,>
,0 --<_etblStockDetails_iBranchID, int,>
,0 --<iDefaultSalesBinID, int,>
,0 --<iDefaultPurchasesBinID, int,>
FROM @tblWHIDs W
LEFT JOIN (select TOP 1 iINVTaxTypeID, iCRNTaxTypeID, iGRVTaxTypeID, iRTSTaxTypeID from stdftbl) SDF ON 1=1
)
END --IF NOT EXISTS(SELECT * FROM StkItem WHERE Code = @Code)
END --sproc
Overview
Content Tools