Lot Numbers beside the Item Code
I have been trying to figure out a way to print the multiple lot numbers besides each Item Code (Without Repetition). I tried using variable and subreport but couldn't succeed. Looking for a better suggestion.
@vimal Welcome to AUGForums!
Are you trying to pull these into a new report or simply add them to a packing list or invoice or another form? The "issue" here is the way the data is stored and the many to one relationship that occurs for multiple allocations on the same line. Are you wanting these to be listed like LOT1, LOT2, LOT3 as opposed to the mulitple rows? Also, is the issue with the repetition that you are getting multiple lines of the item as opposed to a single item and then all the lots? Are you screens shots what you currently have or what you are trying to acheive?
Thanks for the response. Actually I have a requirement where Line Item having multiple Lot/Serial Numbers should not repeat in multiple times. Item code and description will appear once whereas the Lot/Serial numbers corresponding to the same item will appear beside the Item Description(Which could be many). As shown in the above screenshot, it is appearing in a single line but I want lot numbers and their respective quantity in different lines.
Like Kevin said the lot serial information is in multiple tables. We had a similar request from a client who needed lot numbers on several different forms. As a result I put together a custom SQL view to pull the lot/serial data from all tables based on the order type (normal sales order, direct IN order, or drop ship). Below is the code for the SQL view to create a custom DAC and then add it as a subreport. If you need more direction let me know and I can probably put together a customization package to share.
SELECT O.CompanyID, O.OrderType, O.OrderNbr, O.LineNbr, O.InventoryID, O.ShipDate
, O.InvtMult, O.Qty, O.LotSerialNbr, O.ExpireDate, O.ShipmentNbr, NULL AS 'ShipLineNbr'
FROM SOLineSplit O
LEFT JOIN SOShipLineSplit S ON S.CompanyID=O.CompanyID
WHERE O.CompanyID > 1
AND (ISNULL(O.LotSerialNbr,'') <> '' OR ISNULL(O.ExpireDate,'') <> '')
AND S.ShipmentNbr IS NULL
SELECT S.CompanyID, S.OrigOrderType, S.OrigOrderNbr, S.OrigLineNbr, S.InventoryID, S.ShipDate
, S.InvtMult, S.Qty, S.LotSerialNbr, S.ExpireDate, S.ShipmentNbr, S.LineNbr AS 'ShipLineNbr'
FROM SOShipLineSplit S
WHERE S.CompanyID > 1
AND (ISNULL(S.LotSerialNbr,'') <> '' OR ISNULL(S.ExpireDate,'') <> '')
SELECT RS.CompanyID, SS.OrderType, SS.OrderNbr, SS.LineNbr, SS.InventoryID, RS.ReceiptDate
, RS.InvtMult, RS.Qty, RS.LotSerialNbr, RS.ExpireDate, RS.ReceiptNbr, RS.LineNbr
FROM POReceiptLineSplit RS
LEFT JOIN POReceiptLine RL ON RL.CompanyID = RS.CompanyID
AND RL.ReceiptNbr = RS.ReceiptNbr
AND RL.LineNbr = RS.LineNbr
LEFT JOIN SOLineSplit SS ON SS.CompanyID = RL.CompanyID
AND SS.PONbr = RL.PONbr
AND SS.POType = RL.POType
AND SS.POLineNbr = RL.POLineNbr
WHERE RS.CompanyID > 1
AND (ISNULL(RS.LotSerialNbr,'') <> '' OR ISNULL(RS.ExpireDate,'') <> '')
AND SS.OrderType = 'DS'
Will try this approach and will let you know if need any assistance.
Here is a customization package that adds the SQL view and DAC. This would be enough to use in an inquiry or report.