I need a list of open documents that have certain batches allocated to them
I have tried the Batch trx report, but that seems to show me all open and closed documents and all the trx's for the batch. I want to be able to see what open orders I have allocated batches for specific items. A customer is getting the error that there is not enough of the batch to 'ship' and we can't find all of the open documents that are assigned this batch for a specific item. Does anyone have a query or a way of getting this information easily?
Suda Sampath replied
Check this query
SELECT DISTINCT T2.DocNum AS 'Document Number', T2.DocDate AS 'Posting Date', T1.ItemCode AS 'Item No.', T1.Dscription AS 'Item/Service Description', T0.BatchNum AS 'Batch', T0.Quantity AS 'Quantity' FROM [dbo\].[RDR1\] T1 INNER JOIN [dbo\].[ORDR\] T2 ON T2.DocEntry = T1.DocEntry
INNER JOIN [dbo\].[IBT1\] T0 ON T1.DocEntry = T0.BaseEntry
WHERE T2.DocNum = T0.BaseNum AND T0.BaseType = 17 AND T0.Direction = '2'