on 09-17-2008 3:48 PM
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 ?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.