Batch Number in Pick List

[Former Member]

Hello,

I am using SAP Business one 2005A Patch level 29. In the pick list I would like to include the batch number of the goods to be picked. That is based on the expiry dates and those with the expiry dates closest are picked up first -- FIFO.

Before I start thinking of developing an add-on for this, is there a work around for it , a simpler solution ? Has some one worked on this before ?

[former_member583013 · Accepted solution]

Martin,

Batch numbers can be associated to an Item at the Sales Order stage. Using the usual CTRL+Tab on Quantity.

Once this is done, you can try to use this sample query, replace {your Sales Order No} with the actual Sales Order Number

SELECT T0.ItemCode AS 'Item No.', T0.BatchNum AS 'Batch', T0.BaseType AS 'Base Doc. Type', T0.BaseEntry AS 'Base Document Key', T0.BaseNum AS 'Base Document Number', T0.DocDate AS 'Posting Date', T0.Quantity AS 'Quantity' FROM [dbo\].[IBT1\] T0

WHERE T0.BaseType = 17 AND T0.BaseNum = {your Sales Order No}

You can create User fields in Inventory > Pick List Rows

On this User field add a Formatted Search using something like

SELECT T0.BatchNum AS 'Batch' FROM [dbo\].[IBT1\] T0

WHERE T0.BaseType = 17 AND T0.BaseNum = $\[$11.18.0]

Please test this first

[Former Member · Accepted solution]

Thank you Suda,

However I would like to know,

How does this query relate to the result, what is it for ?

SELECT T0.ItemCode AS 'Item No.', T0.BatchNum AS 'Batch', T0.BaseType AS 'Base Doc. Type', T0.BaseEntry AS 'Base Document Key', T0.BaseNum AS 'Base Document Number',  T0.DocDate AS 'Posting Date', T0.Quantity AS 'Quantity' FROM  [dbo\].[IBT1\] T0
WHERE T0.BaseType = 17 AND T0.BaseNum = {your Sales Order No}

This query below gives an error. Where is this parameter '$[$11.18.0]' coming from ?

SELECT T0.BatchNum AS 'Batch' FROM [dbo].[IBT1] T0
WHERE T0.BaseType = 17 AND T0.BaseNum = $[$11.18.0]

Edited by: Martin Kamau on Sep 18, 2008 9:39 AM

[Former Member · Accepted solution]

If you follow Suda's suggestion line by line, this query should work.

Where did you run this query? It can only be run under query but only in the UDF by FMS. You can find what the number stand for by checking on View - System Information. When your mouse pass by any fields, you can find those info.

Thanks,

Gordon

[former_member583013 · Accepted solution]

When you go to

Inventory > P&P Manager > Pick list....Open the Pick list..you should see the UDF at the row level.

Attached the FMS query to this UDF. The field reference $\[$11.18.0] is for the Doc. No. collumn

[Former Member · Accepted solution]

Ok,

I have gone around this and tried the FMS query on the UDF but was strangely unsuccessful.

However I did try this SELECT $[$11.18.0] for testing which gave back the correct document number.

But this could not display anything and no error for this query on the FMS

SELECT T0.[BatchNum] FROM IBT1 T0 WHERE T0.[BaseNum] = $[$11.18.0]

Still I ask what is this query to be used for ?

SELECT T0.ItemCode AS 'Item No.', T0.BatchNum AS 'Batch', T0.BaseType AS 'Base Doc. Type', T0.BaseEntry AS 'Base Document Key', T0.BaseNum AS 'Base Document Number', T0.DocDate AS 'Posting Date', T0.Quantity AS 'Quantity' FROM [dbo].[IBT1] T0

WHERE T0.BaseType = 17 AND T0.BaseNum = {your Sales Order No}

Edited by: Martin Kamau on Sep 23, 2008 4:00 PM

[Former Member · Accepted solution]

Hi guys,

Has any one tried this FMS below on a UDF on SAP Business one 2005A in the pick list ?

SELECT T0.BatchNum FROM IBT1 T0 WHERE T0.BaseNum = $[$11.18.0]

I still can't get it to work, yet its a pretty straight forward query. Any Ideas ?

[former_member583013 · Accepted solution]

Martin,

The solution has been explained Step by Step and I would hope that you could follow the steps and understand the explanation.

This is the actual query

SELECT T0.BatchNum AS 'Batch' FROM [dbo].[IBT1] T0
WHERE T0.BaseType = 17 AND T0.BaseNum = $[$11.18.0]

This query should be added as a Formatted search to the UDF on the PickList Rows. This will copy the Batch Number that has been allocated to the Sales Order

[Former Member · Accepted solution]

Thanks, Its the very first step - Batch numbers can be associated to an Item at the Sales Order stage. Using the usual CTRL+Tab on Quantity - that I kept missing out on.

Edited by: Martin Kamau on Sep 30, 2008 12:15 PM