I have a customer who would like a customized back order report. They would like a query that would show them the following:
- The Items on any orders that have been partially shipped on the row level. (I was able to create a query that would do this by making it a requirement that the Delivered Quantity be greater than 0).
- The Items on any orders that may be partially shipped overall. I cannot figure out how to write this part of the query. I don't know how to tell the query only to pull the lines from orders that have other lines that have been shipped. The one query I wrote only looked at the Delivered Quantity and I made it so it had to be greater than 0. However, this left out a number of orders that did not have whole lines shipped. I would still like to see those rows.
The conventional Back Order report is not very helpful to our customer because they are a seasonal distributor. They receive most of their product all at once. For this reason, they may have whole orders that are overdue a few days (due to a delay in overseas shipping or other reasons). They know about these orders. They would like a list of the orders in which they have already shipped something (either partially shipped the line or partially shipped the order). They would then use this list to call the affected customers and ask if they would like to remainder of their order to ship when they receive the product or if they would like to cancel the remainder.
The conventional backorder report has over two thousand lines on it and it would be very time-consuming for them to go through the individual documents to determine which ones had shipments created and which ones were listed simply because they were overdue.
Can anybody help with this? I'm not sure how to tell if the sales order has had a delivery created or not. Like I said, I can figure out how to tell when a line is partially shipped but not when the order overall is only partially shipped.
I appreciate everybody's help!