B1UP Calendar - Display only expired Batch and Serial numbers with available stock
Completed
The new 2020.04 Dashboard Calendar has the option to display the expired batches and serialnumbers.
Unfortunately also the Batch and Serial numbers without stock are displayed.
I suggest Boyum updates the default query.
-
Thank you for your request. It has been scheduled for review by the development team that will get back to you should there be questions.
(Please note that as we are user-driven we can't guarantee that your request will be met unless it gets many votes and/or fit the product vision)
Read more about the Feature Request process here
[Internal Id: 19676] -
Really showing my inexperience with Serial number and Batches :-) How would one write that SQL? Would it be OITM.OnHand = 0 or some other way (the Quantity on OBTN seems to always be null or 0 so can use that one)
-
Hello Rasmus,
I created the following Queries.
There does not seem to be a quantity on the OBTN table for the total batch quantity anymore.
As an Item can have several batches and a batch can be in more than 1 Warehouse, I used the DISTINCT function.
To make use of this function the note field had to be changed to text.
SELECT DISTINCT
T0.AbsEntry AS 'PrimaryKey',
T0.ObjType AS 'ObjectType',
CONCAT('Batch ''',T0.DistNumber,''' for item ''',T0.ItemCode, ''' expires today') AS 'Subject',
T0.Location AS 'Location',
CAST(T0.Notes AS VARCHAR(254)) AS 'Details',
T0.ExpDate AS 'StartDate'
FROM OBTN T0 WITH (NOLOCK) INNER JOIN OBTQ T1 WITH (NOLOCK) ON T0.SysNumber = T1.SysNumber AND T0.ItemCode = T1.ItemCode
WHERE T1.Quantity > 0 AND T0.ExpDate BETWEEN @StartDate AND @EndDate
For Serial Numbers no DISTINCT is required AS a Serialnumber Quantity is allways 1
SELECT
T0.AbsEntry AS 'PrimaryKey',
T0.ObjType AS 'ObjectType',
CONCAT('Serial ''',T0.DistNumber,''' for item ''',T0.ItemCode, ''' expires today') AS 'Subject',
T0.Location AS 'Location',
CAST(T0.Notes AS VARCHAR(254)) AS 'Details',
T0.ExpDate AS 'StartDate'
FROM OSRN T0 INNER JOIN OSRQ T1 ON T0.[SysNumber] = T1.[SysNumber] AND T0.ItemCode = T1.ItemCode
WHERE T1.Quantity > 0 AND T0.ExpDate BETWEEN @StartDate AND @EndDate -
Great. Thank you... I will check them out and if I don't see any side-effects, consider it part of the June Release
-
The development team has now completed this request. Expect it to be in the next release of the product. Thank you again for helping us make the product better :-)
Please sign in to leave a comment.
Comments
5 comments