SQL - Evo - Update Inventory Item Segment Value

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Alexander Toufexis
-- Create date: 24/05/2019
-- Description: Copy prices from one pricelist, to another, if no price is already set in the 2nd pricelist.
-- =============================================
ALTER PROCEDURE [dbo].[_as_UpdateItemSegmentValue]
    -- Add the parameters for the stored procedure here
    @ItemCode as varchar(50) = NULL,
    @SimpleCode as varchar(50) = NULL,
    @StockLink bigint = 0,
    @SegmentName as varchar(50),
    @NewSegmentValue as varchar(50)
AS
BEGIN
 
/* USAGE:
    --Item code based:
  EXEC [_as_UpdateItemSegmentValue] '1001/FIN/IND/CEL/RET/BAG',NULL,NULL,'Customer Grouping','SPA'; --FIN/SPA/CEL/RET/BAG
  EXEC [_as_UpdateItemSegmentValue] 'FIN/SPA/CEL/RET/BAG',NULL,NULL,'Customer Grouping','IND'; --FIN/SPA/CEL/RET/BAG
  EXEC [_as_UpdateItemSegmentValue] NULL,NULL,328,'Customer Grouping','IND'; --FIN/SPA/CEL/RET/BAG
    --Simple Code based:
  EXEC [_as_UpdateItemSegmentValue] NULL,'1001',NULL,'Customer Grouping','IND';
 
  --should fail due to duplicate simple code: 20040
  EXEC [_as_UpdateItemSegmentValue] NULL,'20040',NULL,'Customer Grouping','IND';
 
  --check:
  select top 10 StockLink, Code, cSimpleCode, cExtDescription from _bvStockFull where StockLink = 328 -- Code = '1001/FIN/SPA/CEL/RET/BAG'
*/
 
 
/*DEBUG SELECTS:
 SELECT * FROM sys.tables order by name
select * from _etblInvSegType
select * from _etblInvSegValue
select top 10 * from StkItem
select top 10 * from _bvStockFull
SELECT * FROM sys.tables where name like '%seg%' order by name
*/
 
DECLARE @SegGroupID int
SELECT @SegGroupID = COALESCE((SELECT idInvSegType FROM _etblInvSegType T WHERE T.cDescription = @SegmentName),0)
 
--getting the right stock item:
 
--DECLARE @StockLink bigint
IF (COALESCE(@ItemCode,'') <> '')
BEGIN
    SELECT @StockLink = COALESCE((SELECT StockLink FROM StkItem WHERE Code = @ItemCode),0)
END
ELSE IF (COALESCE(@SimpleCode,'') <> '')
BEGIN
    IF EXISTS (select cSimpleCode, count(*) from _bvStockFull group by cSimpleCode having count(*) > 1)
        BEGIN
            RaisError('Simple code %s is not unique!',17,1, @SimpleCode)
            RETURN -1
        END
    ELSE
        BEGIN
            SELECT @StockLink = COALESCE((SELECT StockLink FROM StkItem WHERE cSimpleCode = @SimpleCode),0)
        END
END
 
 
--check if segment group exists. if not, early out with error.
IF (@SegGroupID = 0)
        BEGIN
            RaisError('Segment Group %s not found!',17,1, @SegmentName)
            RETURN -1
        END
IF (@StockLink = 0)
        BEGIN
            RaisError('Stock Code %s not found!',17,1, @ItemCode)
            RETURN -1
        END
 
        PRINT 'about to lookup seg value id'
        PRINT @SegGroupID;
DECLARE @SegValueID int

/*also handling blank values, to set to 0*/
IF (@NewSegmentValue = '')
 BEGIN
	PRINT 'Setting segment ID to 0 for a blank value';
	SELECT @SegValueID = 0
 END 
ELSE
BEGIN
SELECT @SegValueID = COALESCE((SELECT TOP 1 idInvSegValue FROM _etblInvSegValue WHERE cValue = @NewSegmentValue AND iInvSegGroupID = @SegGroupID),0)
--select * from _etblInvSegValue WHERE cValue = 'IND' AND iInvSegGroupID = 2
 
IF (@SegValueID = 0)
        BEGIN
            RaisError('New segment value %s not found!',17,1, @NewSegmentValue)
            RETURN -1
        END
END --IF (@NewSegmentValue = '')
 
PRINT 'about to update stkitem'
--all good, let's update:
UPDATE StkItem
    SET
        iInvSegValue1ID = CASE WHEN @SegGroupID = 1 THEN @SegValueID ELSE iInvSegValue1ID END
        ,iInvSegValue2ID = CASE WHEN @SegGroupID = 2 THEN @SegValueID ELSE iInvSegValue2ID END
        ,iInvSegValue3ID = CASE WHEN @SegGroupID = 3 THEN @SegValueID ELSE iInvSegValue3ID END
        ,iInvSegValue4ID = CASE WHEN @SegGroupID = 4 THEN @SegValueID ELSE iInvSegValue4ID END
        ,iInvSegValue5ID = CASE WHEN @SegGroupID = 5 THEN @SegValueID ELSE iInvSegValue5ID END
        ,iInvSegValue6ID = CASE WHEN @SegGroupID = 6 THEN @SegValueID ELSE iInvSegValue6ID END
        ,iInvSegValue7ID = CASE WHEN @SegGroupID = 7 THEN @SegValueID ELSE iInvSegValue7ID END
         
    WHERE StockLink = @StockLink
 
UPDATE _bvStockFull
    SET Code = cSimpleCode
            + CASE WHEN iInvSegValue1ID = 0 THEN '' ELSE '/' + cInvSegValue1Value END
            + CASE WHEN iInvSegValue2ID = 0 THEN '' ELSE '/' + cInvSegValue2Value END
            + CASE WHEN iInvSegValue3ID = 0 THEN '' ELSE '/' + cInvSegValue3Value END
            + CASE WHEN iInvSegValue4ID = 0 THEN '' ELSE '/' + cInvSegValue4Value END
            + CASE WHEN iInvSegValue5ID = 0 THEN '' ELSE '/' + cInvSegValue5Value END
            + CASE WHEN iInvSegValue6ID = 0 THEN '' ELSE '/' + cInvSegValue6Value END
            + CASE WHEN iInvSegValue7ID = 0 THEN '' ELSE '/' + cInvSegValue7Value END
        ,cExtDescription = Description_1
            + CASE WHEN iInvSegValue1ID = 0 THEN '' ELSE '/' + cInvSegValue1Desc END
            + CASE WHEN iInvSegValue2ID = 0 THEN '' ELSE '/' + cInvSegValue2Desc END
            + CASE WHEN iInvSegValue3ID = 0 THEN '' ELSE '/' + cInvSegValue3Desc END
            + CASE WHEN iInvSegValue4ID = 0 THEN '' ELSE '/' + cInvSegValue4Desc END
            + CASE WHEN iInvSegValue5ID = 0 THEN '' ELSE '/' + cInvSegValue5Desc END
            + CASE WHEN iInvSegValue6ID = 0 THEN '' ELSE '/' + cInvSegValue6Desc END
            + CASE WHEN iInvSegValue7ID = 0 THEN '' ELSE '/' + cInvSegValue7Desc END
    WHERE StockLink = @StockLink
 
 
 
 
 
END