Capturing Lot(s) on Packing Slip Report

Goal:To understand the mechanism of capturing multiple lots on the packing slip report.

Right now I can capture the first lot from Material Transactions for a packing slip, however in some cases there is going to be multiple lots(shown in below figure) pertaining to one packing slip, trying to understand how to capture the second, third and .... on the packing slip report.

Thank you!

20221201 Movex User Group DE MUG.pdf

Find more posts tagged with

Syteline Development

Accepted answers

All comments

burnsg

If you are asking how to do something like that via SQL (combine multiple rows into a comma delimited list), take a look at this code:

XML and STUFF for a simple CSV column | Notes on SQL (sqlrambling.net)

unknown

Thank you!

antonio-caria

You want copy the lines and export them to CSV/XLS format ?

unknown

So I linked the material transaction's table to the pack slip report viewer using item, transfer number, line number and type of transaction, now the problem is if we ship partial against the same line in multiple days it will capture all lot numbers. Tried to link it by using pack slip generation date in the pack slip report viewer which works, however in case that we generate the pack slip later(different day) it wont be able to capture the heat lot numbers.

Now my question: Is there any linkage between pack slip and material transaction or any other methods that I can use to retrieve the lot numbers only for a specific pack slip number.

unknown

No, need to capture the lot numbers and qty on the pack slip

jennifer-foster

Hootan, I am looking for something similar. I need to link the Packing Slip to the Serial Number. Did you ever find a solution? I am thinking mine may be similar.

itdept

We are also looking to show lots on the packing slip report. As you mentioned, linking to material transactions based on transaction type and the customer order information (number, line, release) seems to be the best option...but as you mentioned for partial shipments you will get all lot numbers. As far as I can tell, the best option is to join to shipments (co_ship), then use ship_date and qty_packed/qty_shipped as additional join conditions to material transactions.

We're currently trying something like below (add to end of Rpt_PackingSlipSp):

--End of Rpt_PackingSlipSp

(SELECT(STUFF((

SELECT ', ' + lot + ' Qty: ' + CAST(FORMAT(sum(qty * -1),'######') as nvarchar(20))

FROM matltrack

WHERE ref_num = co_ship.co_num

AND ref_line_suf = co_ship.co_line

AND ref_release = co_ship.co_release

AND ref_type = 'O'

AND trans_date = co_ship.ship_date

AND ABS(qty) = r.qty_packed --co_ship.qty_shipped?

GROUP BY lot

HAVING SUM(qty * -1) > 0

ORDER BY lot --TMF1

FOR XML PATH('')

), 1, 2, '')

)) AS lots

FROM @ReportSet r

--Added

JOIN co_ship ON co_ship.co_num = r.co_num

AND co_ship.co_line = r.co_line

AND co_ship.co_release = r.co_release

AND co_ship.pack_num = r.pack_num

ORDER BY pack_num, r.co_num ASC, r.co_line ASC, r.co_release,Kit_Flag ASC,serial_num

I'm hoping someone more familiar with the underlying tables/functionality can share if the above is acceptable or if there is a more correct way to link packing slip lines to lots.

Thanks!

Copyright © 2025 Infor. All rights reserved.