I need a list of open documents that have certain batches allocated to them

[Former Member]

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?

[former_member583013 · Accepted solution]

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'

[Former Member]

Thankyou. Your answers solved my problem.

[Former Member]

You will find the information you need in table IBT1. Look for records where the field Direction = 2 (Allocated). Field BaseNum will give you the sales order number. You might try something like this:

SELECT T0.[ItemCode], T0.[BatchNum], T0.[WhsCode], T0.[BaseNum], T0.[Quantity] FROM IBT1 T0 WHERE T0.[ItemCode] =[%0] AND T0.[Direction] = '2' AND T0.[BaseType] = '17'

See how it works.

Marcia