when a picking process require an item to be retrieved from 2 or more locations to fulfill an order, the invoice shows one line per every location the item was picked up from including the quantity retrieved from each location, our customer service department require 1 line per item with the total quantity shipped, do some one had the same problem with invoicing?, any help will be appreciated.
Do you use serial numbers or lot numbers?
Lot numbers
Then that is why it is showing up twice on the invoice. You could edit the report to group together the 2 lines and display both lot numbers under it. But that is the only way I know of getting them to 1 line.
Sorry John, I double check the Query, it only list calc_SerialNumber but not Lot number, but we have no serial numbers in our product and we implementing lot numbers.
I am going from memory, so I would need to double check before I could say anything definitive.
But, when you ship with Lot Numbers, if you are shipping the same part with more than 1 Lot Number, you have to do 2 lines on the pack. When the pack it transferred to an invoice, it does it as a 1 to 1 to the pack. Unless there has been a customization done, I believe the Lot Numbers should be printing out on the Invoice.
If you really wanted to stop having multiple lines in these situations, someone might be able to suggest a customization to combine the lines from a pack into 1 line on the invoice. I don’t think that would be a really good idea as you would be changing some core functionality and I would imagine it would be a beast to maintain.
My recommendation would be to tell Customer Service that there is no easy way to change this and for them to get used to it. You can group together the lines and sum the quantity to get to 1 line on the Invoice. But you will also have missing lines on the Invoice if you ship more than 1 part. As an example, you ship 10 part A with 2 different Lot Numbers and 10 of part B. If you combine the lines on the Invoice report, you would only have Line 1 and 3 showing as line 2 was combined with 1.
This is how our invoices line look like
line partnumber/description quantity unit price extended price
1 xxx1 200 $2.00 $400.00
description of product xxx1
Qty. ordered: 600
2 xxx1 300 $2.00 $600.00
description of product xxx1
Qty. ordered: 600
3 xxx1 100 $2.00 $200.00
description of product xxx1
Qty. ordered: 600
4 xxx2 200 $1.00 $200.00
description of product xxx2
Qty. ordered: 200
The problem is that our invoices can have more than 20 different items packed from up to 6 locations in our warehouse, that is 120 lines per 1 invoice instead of only 20 lines. (worst case scenario). is there any way to get 1 line per item with out losing the rest of the items listed?
Not that I am aware of. If you grouped the like numbers on the report, your invoice would look like this.
line partnumber/description quantity unit price extended price
1 xxx1 600 $2.00 $1200.00
description of product xxx1
Qty. ordered: 600
4 xxx2 200 $1.00 $200.00
description of product xxx2
Qty. ordered: 200
It’s not just a Lot thing (although Lot tracking forces this case happen) …
If your packer has multiple lines for the same Item, they generate individual invoice lines.
So if you’re setup to prevent a bin from going negative, you’ll have to make separate packer lines.
If you allow bins going negative, you could make one packer line, and have the entire qty come from one bin. If that bin did not have sufficient qty, its QOH will go negative, and require a Material Transfer entry, from the bins that material was physically pulled from, to the bin it “shipped from” (the one specified on the packer)
tha tis exactly the problem we have , when the packer has multiple bins to pick the item from (we are no allowing negative numbers on the bins), but we still need the invoices to use one line per item to avoid costumer and finance employees confusion, how can I group the invoice by part number? or modify the current sql query to consolidate by part number
Maybe a BPM that looks at bin qty’s and does a STK-STK part tran to get the required Qty into one bin before saving the packer line.
Thanks Calvin, but we can not do that because every transaction has to be traceable internally, I was wondering if someone had the same problem on the past and could give some tips of how to modify the current dataset to get a consolidation of the invoice lines per item.
John, that is exactly what I want to do but I tried modification on the expression on the dataset but it doesn’t work, do you know how to get that result?
You don’t need to do anything with the dataset. You would have to alter the Invoice report. I’m assuming you use SSRS? You will have to put the line in a group and then hide the detail and sum on part number.
yes I use SSRS, so far I used to hide of show fields, not an expert, so just hide the lines and display the result of a calculation by part?
I am adding a group child of invoice line and it mess the invoice, after undo that change I created group parent of details and also mess the report, what I am doing wrong?
I am not currently in 10 now, so I have to be generic with my answer.
If there is not already a group for line, you need to add one. If there already is one, skip this.
In the Line group, you need to group by the part number. Then you need to use aggregates to get the rest of the data there. So, I would use MIN for the line num, FIRST for the part description, SUM for the qty and price, etc.
Thanks John!!, I created a parent group for invoice line and on the footer I created the sum of the quantities and the extended price , copied the other field to display , it is working OK, the only problem is after I created the group the report displays 2 extra pages, one is empty (just the header and the footer of the report are displaying) and the second page display the part number and a rectangle , the last page display the invoice as it should be, this is when it is only one part number in the invoice.
Sounds like you have a page break on something. If you can figure out where the random stuff is coming from (perhaps put a text field in each report section with a different value) you can hide that section.
yes, that is what I am looking for I disabled the page breaks on the other groups but I still getting the same result, going to try to hide sections as you suggested.