Questions Lot Numbers beside the Item Code

@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?

Hi Kevin,

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.

-Kurt

       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

                     AND S.OrigOrderType=O.OrderType

                     AND S.OrigOrderNbr=O.OrderNbr

                     AND S.OrigLineNbr=O.LineNbr

       WHERE O.CompanyID > 1

              AND (ISNULL(O.LotSerialNbr,'') <> '' OR ISNULL(O.ExpireDate,'') <> '')

              AND S.ShipmentNbr IS NULL

       UNION

       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,'') <> '')

       UNION

       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'

Thanks Kurt,

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.

Thanks,

Kurt