Conversion Examples

Convert current inventory to JSON format

The output from these examples can be used in the Lines section of the /InventoryInbound Integration API.

Since there’s a row limit, the result of these queries is divided into multiple results with a maximum of 300 lines.

MS SQL

SQL

DECLARE @MAXROWS INT = 300
DECLARE @MAXROW INT = @MAXROWS
DECLARE @TMP_TABLE TABLE (documentLine INT, warehouseId NVARCHAR(50), binId NVARCHAR(50), itemId NVARCHAR(100), quantity NUMERIC(19,6), uomCode NVARCHAR(50), batchNumber NVARCHAR(255), expiryDate DATE, serialNumber NVARCHAR(255))

INSERT INTO @TMP_TABLE
SELECT 
  ROW_NUMBER() OVER (ORDER BY X.ItemCode, X.WhsCode, X.BinCode, X.BatchNumber, X.SerialNumber) AS documentLine,
  X.WhsCode AS warehouseId,
  '01-ST-01'/*X.BinCode*/ AS binId,
  X.ItemCode AS itemId,
  X.OnHandQty AS quantity,
  X.InvntryUom AS uomCode,
  X.BatchNumber AS batchNumber,
  X.expiryDate AS expiryDate,
  X.SerialNumber AS serialNumber
FROM (
  SELECT A.ItemCode, A.WhsCode, B.BinCode, C.DistNumber AS BatchNumber, CAST(C.ExpDate AS DATE) AS expiryDate,
         NULL AS SerialNumber, A.OnHandQty, I.InvntryUom
  FROM OBBQ A
  INNER JOIN OBIN B ON B.AbsEntry = A.BinAbs
  INNER JOIN OBTN C ON C.AbsEntry = A.SnBMDAbs
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.OnHandQty > 0

  UNION

  SELECT A.ItemCode, A.WhsCode, B.BinCode, NULL AS BatchNumber, NULL AS expiryDate, C.DistNumber AS SerialNumber,
         A.OnHandQty, I.InvntryUom
  FROM OSBQ A
  INNER JOIN OBIN B ON B.AbsEntry = A.BinAbs
  INNER JOIN OSRN C ON C.AbsEntry = A.SnBMDAbs
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.OnHandQty > 0

  UNION

  SELECT A.ItemCode, A.WhsCode, B.BinCode, NULL AS BatchNumber, NULL AS expiryDate, NULL AS SerialNumber,
         A.OnHandQty, I.InvntryUom
  FROM OIBQ A
  INNER JOIN OBIN B ON B.AbsEntry = A.BinAbs
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.OnHandQty > 0 AND I.ManBtchNum = 'N' AND I.ManSerNum = 'N'

  UNION

  SELECT A.ItemCode, A.WhsCode, '' AS BinCode, NULL AS BatchNumber, NULL AS expiryDate, NULL AS SerialNumber,
         A.OnHand AS OnHandQty, I.InvntryUom
  FROM OITW A
  INNER JOIN OWHS W ON W.WhsCode = A.WhsCode
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.OnHand > 0 AND W.BinActivat = 'N' AND I.ManBtchNum = 'N' AND I.ManSerNum = 'N'

  UNION

  SELECT A.ItemCode, A.WhsCode, '' AS BinCode, C.DistNumber AS BatchNumber, CAST(C.ExpDate AS DATE) AS expiryDate,
         NULL AS SerialNumber, A.Quantity AS OnHandQty, I.InvntryUom
  FROM OBTQ A
  INNER JOIN OWHS W ON W.WhsCode = A.WhsCode
  INNER JOIN OBTN C ON C.AbsEntry = A.MdAbsEntry
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.Quantity > 0 AND W.BinActivat = 'N'

  UNION
  SELECT A.ItemCode, A.WhsCode, '' AS BinCode, NULL AS BatchNumber, NULL AS expiryDate, C.DistNumber AS SerialNumber,
         A.Quantity AS OnHandQty, I.InvntryUom
  FROM OSRQ A
  INNER JOIN OWHS W ON W.WhsCode = A.WhsCode
  INNER JOIN OSRN C ON C.AbsEntry = A.MdAbsEntry
  INNER JOIN OITM I ON I.ItemCode = A.ItemCode
  WHERE A.Quantity > 0 AND W.BinActivat = 'N' AND C.DistNumber IS NOT NULL
) X
INNER JOIN OWHS W ON W.WhsCode = X.WhsCode
WHERE X.WhsCode IN ('01') /* Adjust if needed */
ORDER BY X.ItemCode, X.WhsCode, X.BinCode, X.BatchNumber, X.SerialNumber

WHILE (SELECT COUNT(*) FROM @TMP_TABLE) > 0
BEGIN
	SELECT * FROM @TMP_TABLE
	WHERE documentLine <= @MAXROW
	FOR JSON PATH;

	DELETE FROM @TMP_TABLE WHERE documentLine <= @MAXROW
	SET @MAXROW = @MAXROW + @MAXROWS
END

SAP HANA

SQL

DO
BEGIN
	DECLARE MAXROWS INT = 300;
	DECLARE MAXROW INT;
	(SELECT MAXROWS into MAXROW FROM DUMMY);
	
	-- Declare a local temporary table
	CREATE LOCAL TEMPORARY TABLE #TempInventory (
	  documentLine INT,
	  ItemCode NVARCHAR(50),
	  WhsCode NVARCHAR(50),
	  BinCode NVARCHAR(50),
	  BatchNumber NVARCHAR(255),
	  expiryDate NVARCHAR(50),
	  SerialNumber NVARCHAR(255),
	  OnHandQty DECIMAL(21,6),
	  InvntryUom NVARCHAR(50)
	);

	INSERT INTO #TempInventory
	SELECT 
	    ROW_NUMBER() OVER (ORDER BY X."ItemCode", X."WhsCode", X."BinCode", X."BatchNumber", X."SerialNumber") AS "documentLine",
	    X."ItemCode",
	    X."WhsCode",
	    X."BinCode",
	    X."BatchNumber",
	    X."expiryDate",
	    X."SerialNumber",
	    X."OnHandQty",
	    IFNULL(X."InvntryUom", '')
	FROM 
	(
		    /* Bin Managed warehouses and batch number items */
		  SELECT A."ItemCode", A."WhsCode", B."BinCode", C."DistNumber" AS "BatchNumber", CAST(C."ExpDate" AS DATE) AS "expiryDate", 
		         '' AS "SerialNumber", A."OnHandQty", I."InvntryUom"
		  FROM OBBQ A
		  INNER JOIN OBIN B ON B."AbsEntry" = A."BinAbs"
		  INNER JOIN OBTN C ON C."AbsEntry" = A."SnBMDAbs"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."OnHandQty" > 0
		
		  UNION
		
		  /* Bin Managed warehouses and serial number items */
		  SELECT A."ItemCode", A."WhsCode", B."BinCode", '' AS "BatchNumber", null AS "expiryDate", C."DistNumber" AS "SerialNumber", 
		         A."OnHandQty", I."InvntryUom"
		  FROM OSBQ A
		  INNER JOIN OBIN B ON B."AbsEntry" = A."BinAbs"
		  INNER JOIN OSRN C ON C."AbsEntry" = A."SnBMDAbs"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."OnHandQty" > 0
		
		  UNION
		
		  /* Bin Managed warehouses and items not managed by serials or batches */
		  SELECT A."ItemCode", A."WhsCode", B."BinCode", '' AS "BatchNumber", null AS "expiryDate", '' AS "SerialNumber", 
		         A."OnHandQty", I."InvntryUom"
		  FROM OIBQ A
		  INNER JOIN OBIN B ON B."AbsEntry" = A."BinAbs"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."OnHandQty" > 0 AND I."ManBtchNum" = 'N' AND I."ManSerNum" = 'N'
		
		  UNION
		
		  /* Non Bin managed warehouses and items not managed by serials or batches */
		  SELECT A."ItemCode", A."WhsCode", '' AS "BinCode", '' AS "BatchNumber", NULL AS "expiryDate", '' AS "SerialNumber", 
		         A."OnHand" AS "OnHandQty", I."InvntryUom"
		  FROM OITW A
		  INNER JOIN OWHS W ON W."WhsCode" = A."WhsCode"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."OnHand" > 0 AND W."BinActivat" = 'N' AND I."ManBtchNum" = 'N' AND I."ManSerNum" = 'N'
		
		  UNION
		
		  /* Non Bin managed warehouses and batch number items */
		  SELECT A."ItemCode", A."WhsCode", '' AS "BinCode", C."DistNumber" AS "BatchNumber", CAST(C."ExpDate" AS DATE) AS "expiryDate", 
		         '' AS "SerialNumber", A."Quantity" AS "OnHandQty", I."InvntryUom"
		  FROM OBTQ A
		  INNER JOIN OWHS W ON W."WhsCode" = A."WhsCode"
		  INNER JOIN OBTN C ON C."AbsEntry" = A."MdAbsEntry"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."Quantity" > 0 AND W."BinActivat" = 'N'
		
		  UNION
		
		  /* Non Bin managed warehouses and serial number items */
		  SELECT A."ItemCode", A."WhsCode", '' AS "BinCode", '' AS "BatchNumber", NULL AS "expiryDate", C."DistNumber" AS "SerialNumber", 
		         A."Quantity" AS "OnHandQty", I."InvntryUom"
		  FROM OSRQ A
		  INNER JOIN OWHS W ON W."WhsCode" = A."WhsCode"
		  INNER JOIN OSRN C ON C."AbsEntry" = A."MdAbsEntry"
		  INNER JOIN OITM I ON I."ItemCode" = A."ItemCode"
		  WHERE A."Quantity" > 0 AND W."BinActivat" = 'N' AND C."DistNumber" IS NOT NULL
	) X
	INNER JOIN OWHS W ON W."WhsCode" = X."WhsCode"
	WHERE X."WhsCode" IN ('01');

	WHILE (SELECT COUNT(*) FROM #TempInventory) > 0
	DO
		SELECT 
		  '[' || STRING_AGG(
		    '{' ||
		      '"documentLine":' || documentLine || ',' ||
		      '"warehouseId":"' || WhsCode || '",' ||
		      '"binId":"' || IFNULL(BinCode, '') || '",' ||
		      '"itemId":"' || ItemCode || '",' ||
		      '"quantity":' || OnHandQty || ',' ||
		      '"uomCode":"' || InvntryUom || '",' ||
		      '"batchNumber":"' || COALESCE(IFNULL(BatchNumber, ''), '') || '",' ||
		      '"expiryDate":"' || COALESCE(TO_NVARCHAR(expiryDate), '') || '",' ||
		      '"serialNumber":"' || COALESCE(IFNULL(SerialNumber, ''), '') || '"' ||
		    '}'
		  , ',') || ']' AS "json_output"
		FROM #TempInventory 
		WHERE documentLine < MAXROW;
		
		DELETE FROM #TempInventory WHERE documentLine < MAXROW;
		
		MAXROW = MAXROW + MAXROWS;
		
	END WHILE;
	
	DROP TABLE #TempInventory;
END