Convert bin locations

DECLARE @BinAttributes TABLE (binAbs INT, property NVARCHAR(50))
DECLARE @BinRestrictions TABLE (binAbs INT, maxItems NUMERIC(19,6) , maxBatches NUMERIC(19,6) , dedicatedItem NVARCHAR(100), dedicatedItemGroup NVARCHAR(100) , dedicatedOwnerId NVARCHAR(100))
DECLARE @UseAtrributeTitles NVARCHAR(1) = 'Y'

if @UseAtrributeTitles = 'Y'
BEGIN
	/* Use attributes titles */
	INSERT INTO @BinAttributes
			SELECT C.AbsEntry, A.DispName AS property
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 1
			WHERE ISNULL(C.Attr1Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 2
			WHERE ISNULL(C.Attr2Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 3
			WHERE ISNULL(C.Attr3Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 4
			WHERE ISNULL(C.Attr4Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 5
			WHERE ISNULL(C.Attr5Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 6
			WHERE ISNULL(C.Attr6Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 7
			WHERE ISNULL(C.Attr7Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 8
			WHERE ISNULL(C.Attr8Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 9
			WHERE ISNULL(C.Attr9Val, '') <> ''
			UNION
			SELECT C.AbsEntry,A.DispName AS property 
			FROM OBIN C WITH(NOLOCK)
			INNER JOIN OBFC A WITH(NOLOCK) ON A.FldType = 'A' AND A.FldNum = 10
			WHERE ISNULL(C.Attr10Val, '') <> ''
END
ELSE BEGIN
	/* Use attributes values */

	INSERT INTO @BinAttributes
			SELECT C.AbsEntry, C.Attr1Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr1Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr2Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr2Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr3Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr3Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr4Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr4Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr5Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr5Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr6Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr6Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr7Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr7Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr8Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr8Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr9Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr9Val, '') <> ''
			UNION
			SELECT C.AbsEntry, C.Attr10Val AS property
			FROM OBIN C WITH(NOLOCK)
			WHERE ISNULL(C.Attr10Val, '') <> ''
END

INSERT INTO @BinRestrictions
SELECT B.AbsEntry
	, ISNULL(B.ItmRtrictT, 0) AS maxItems
	, CASE B.SngBatch WHEN 'N' THEN '' ELSE 1 END AS maxBatches
	, ISNULL(B.SpcItmCode, '') AS dedicatedItem
	, ISNULL(B.SpcItmGrpC, '') AS dedicatedItemGroup
	, '' AS dedicatedOwnerId
FROM OBIN B WITH(NOLOCK)

SELECT B.BinCode AS binId
	, ISNULL(B.Descr, '') AS [name]
	, ISNULL(BarCode, '') AS barcode
	, ISNULL(AltSortCod, '') AS sortCode
	, WhsCode AS warehouseId
	, ISNULL(SL1Code, '') AS subLevelZone
	, ISNULL(SL2Code, '') AS subLevelRack
	, ISNULL(SL3Code, '') AS subLevelLevel
	, ISNULL(SL4Code, '') AS subLevelBin
	, CASE B.[Disabled] WHEN 'Y' THEN CAST(0 AS BIT) else CAST(1 AS BIT) END AS isActive
	, CASE B.ReceiveBin WHEN 'Y' THEN CAST(1 AS BIT) else CAST(0 AS BIT) END AS isReceive
	, ISNULL(B.MaxLevel, 0) AS maxQuantity
	, ISNULL(B.MinLevel, 0) AS minQuantity
	, ISNULL(B.MaxWeight1, 0.0) AS maxWeightInGr
	, JSON_QUERY((
			SELECT A.property , 'Reportive' as propertyType , CAST(1 AS BIT) AS allowed
			FROM @BinAttributes A
			WHERE A.binAbs = B.AbsEntry
			FOR JSON PATH 
		)) AS binProperties
	, JSON_QUERY((
			SELECT CASE R.maxItems WHEN 0 THEN null ELSE R.maxItems END AS maxItems
				, CASE R.maxBatches WHEN 0 THEN null ELSE R.maxBatches END AS maxBatches
				, R.dedicatedItem 
				, CASE R.dedicatedItemGroup WHEN 0 THEN null ELSE R.dedicatedItemGroup END AS dedicatedItemGroup
				, dedicatedOwnerId
			FROM @BinRestrictions R
			WHERE R.binAbs = B.AbsEntry
			FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
		)) AS restrictions
FROM OBIN B WITH(NOLOCK)
WHERE B.SL1Abs <> 1 /* Exclude System Bins */
FOR JSON PATH;

TO DO