Customized Back Order Report

[Former Member]

Hello,

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!

Thanks,

Amanda

[Former Member · Accepted solution]

Hi Amanda,

I am so sorry for the last two queries. I was not testing them enough before posting. Please be patient to bear with me and try this tested one:

SELECT t0.Cardcode as 'Customer#', t0.Cardname 
as 'Customer Name',  t0.Docnum as 'SO #', t1.itemcode, 
t0.DocDate, t0.DocDueDate, t1.OpenQty As 'Open Qty', t1.LineTotal
FROM ORDR t0
inner join rdr1 t1 on t1.docentry = t0.docentry
WHERE (T1.OpenQty > 0) and t0.docentry in (select distinct 
t1.docentry from rdr1 t1 where t1.quantity > t1.openqty) 
Order by t0.Cardname, t0.Docnum

Thanks,

Gordon

[Former Member]

Hi Amanda,

Try this:

SELECT t0.Cardcode as 'Customer#', t0.Cardname 
as 'Customer Name',  t0.Docnum as 'SO #', t1.itemcode, 
t0.DocDate, t0.DocDueDate, t1.OpenQty As 'Open Qty', t1.LineTotal
FROM ORDR t0
inner join rdr1 t1 on t1.docentry = t0.docnum
WHERE (t1.linestatus = 'O') AND (T1.OpenQty < T1.Quantity)
Order by t0.Cardname, t0.Docnum

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thank you for your query. However, your query brings the same results I was able to query as well. It shows all of the lines that have had something shipped on them. But my customer would also like to to see those orders that have had some lines shipped and other lines not.

For example, they have a sales order 10962. This sales order has 3 lines on it. 1 line on this sales was order was shipped complete. The other two lines have not been shipped at all. I would the query to pull the two lines that have not been shipped at all.

The other part of the query is what you and I have both written. This part of the query pulls the lines that have had something shipped on the line but it is not the complete line.

I was hoping there may be some sort of flag in the table or something that would indicate if a delivery has been created from the sales order. Do you know of any such flag or any way of writing a query that would check this?

Again, thank you for taking the time to write your query. I appreciate the help and time you've spent.

Thank you,

Amanda

[Former Member]

Try this one to see:

SELECT t0.Cardcode as 'Customer#', t0.Cardname 
as 'Customer Name',  t0.Docnum as 'SO #', t1.itemcode, 
t0.DocDate, t0.DocDueDate, t1.OpenQty As 'Open Qty', t1.LineTotal
FROM ORDR t0
inner join rdr1 t1 on t1.docentry = t0.docnum
WHERE (t0.docstatus = 'O') AND (T1.OpenQty > 0)
Group by t0.Cardcode, t0.Cardname,  t0.Docnum, t1.itemcode, 
t0.DocDate, t0.DocDueDate, t1.OpenQty, t1.LineTotal
Having Sum(T1.Quantity - T1.OpenQty) > 0
Order by t0.Cardname, t0.Docnum

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thank you again for taking your time to revise the query. Unfortunately, it's still only bringing through rows that are partially delivered and not those lines that have not been delivered at all.

I don't want to consume your time with working on this query for me. I appreciate all of your efforts and help, but understand if you can't devote anymore time to the query. I don't want to discourage you from trying further, but I don't want you to feel obligated to spend anymore time on it.

Thank you,

Amanda