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
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

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