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