QUERY List of serial number that are ALLOCATED SAP B1 HANA

[kanesamba]

Hello

I have serial number who has status "allocated" .

They are two main table for serial number

"OSRN" = The Column "Status" give no value and is empty (i don'kt know why ?)

"OSRI" = The Column "Status" only give two value ('0' when available and 'Allocated' ? , '1' when 'unaivailable')

can you please help me find the query to have this value ?

Thanks

[mgregur]

Hi kanesamba,

You can use this query:

SELECT T0."Quantity"
, T0."CommitQty"
, T1."DistNumber"
, T1."MnfSerial"
, T1."LotNumber"
, T2."ItemCode" 
, T2."ItemName"
FROM OSRQ T0  
INNER JOIN OSRN T1 ON T0."SysNumber" = T1."AbsEntry" 
INNER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
WHERE T0."CommitQty" > 0

BR,

Matija

[ManasGhoshal]

Hi,

Please refer this link. Hope this will help you.

answers.sap.com/questions/13091103/osrn-and-osri-tables-to-find-status.html

https://www.sap-business-one-tips.com/find-your-allocated-batch/

Thanks.

[LoHa]

Hi Samba,

i only got a query for sales order allocated serial or batch. Perhabs you can change it on your own

DECLARE @DocEntry AS INT
SET @DocEntry = 126343
;
with BaseInfo AS
(
SELECT
	  [RDR1].[ObjType]
	, Max([OITL].[LogEntry]) AS [MaxLogEntry]
	, [OITL].[ItemCode]
	, [OITL].[DocLine] AS [LineNum]
	, [ITL1].[SysNumber]
	, [OITL].[ManagedBy]

FROM
	[RDR1] 
		INNER JOIN OITL ON 
			[OITL].[DocEntry] = [RDR1].[DocEntry] AND [OITL].[DocType] = [RDR1].[ObjType] AND [OITL].[DocLine] = [RDR1].[LineNum] 
		INNER JOIN [ITL1] ON 
			[ITL1].[LogEntry] = [OITL].[LogEntry]
WHERE 
	[RDR1].[DocEntry] = @DocEntry
Group BY 
	[RDR1].[ObjType],[OITL].[ItemCode], [OITL].[DocLine],[ITL1].[SysNumber], [OITL].[ManagedBy]
)

SELECT 
	@DocEntry AS [DocEntry]
	,[BaseInfo].*
	,CASE	WHEN [LineInfo].[OrderedQty] > 0 THEN [LineInfo].[OrderedQty]
			WHEN [LineInfo].[AllocQty] > 0 THEN [LineInfo].[AllocQty]
			ELSE 
				CASE	WHEN [LineInfo].[Quantity] < 0 THEN [LineInfo].[Quantity] * -1
						ELSE [LineInfo].[Quantity] 
				END
			END AS [Quantity]
	,[OSRI].[IntrSerial]
	,[OSRI].[SuppSerial]
	,[OBTN].[DistNumber] AS [BatchNum]

FROM 
	[BaseInfo]
		CROSS APPLY (SELECT * FROM [ITL1] WHERE [ITL1].[LogEntry] = [BaseInfo].[MaxLogEntry] AND [ITL1].[ItemCode] = [BaseInfo].[ItemCode] AND [ITL1].[SysNumber] = [BaseInfo].[SysNumber] AND ([ITL1].[OrderedQty] > 0 OR [ITL1].[AllocQty] > 0 OR [ITL1].[Quantity] <> 0)) [LineInfo]
		LEFT JOIN [OSRI] ON 
			[OSRI].[SysSerial] = [BaseInfo].[SysNumber] AND [OSRI].[ItemCode] = [BaseInfo].[ItemCode] AND [BaseInfo].[ManagedBy] = 10000045
		LEFT JOIN OBTN ON 
			[OBTN].[SysNumber] = [BaseInfo].[SysNumber] AND [OBTN].[ItemCode] = [BaseInfo].[ItemCode] AND [BaseInfo].[ManagedBy] = 10000044

regards

Lothar