Small and medium business | Business Central, N...

Sales Shipment Not Fully Invoiced but Sales Order Deleted

(0) Share

Report

Posted on by Blake Burnley

We have an issue where we have sales shipment lines that have not been invoiced in full . As a result it looks like certain items have a $ value on hand even though the quantity on hand is 0. We need help figuring out the best way to clear these amounts.

Background: We have a customer that often says they did not receive some of the items we sent them and they will only pay part of the invoice. We think we shipped the items so we don't want to do a sales return since we think the material left. To reflect this on the sales order what we would do is update the Qty to Invoice to reflect what the customer was willing to pay for. Then we generated an invoice for what the customer wanted to pay. This left a remaining amount to be invoiced. We would then change the Quantity to Invoice to be 0 so that we could delete the Sales Order since in our mind it should be closed out. We thought this was working fine for us until we looked at our inventory balances by item and noticed that items we have 0 qty on hand were still showing a $ value on hand. We have since stopped this practice but need to correct our mistakes.

What would be the best way to clear these balances out of inventory?

We know a general journal entry would work to fix the total $ amount on hand but not for individual items. We would ideally be able to bring the $ value on hand for each item to 0.

Another thing we tried was to create a new invoice and use the Get Shipment Lines function. When we do this we can see all the shipment lines that have not been invoiced in full. When we try to pull some of them in we get the following error message "The program cannot find this Sales line". This feels like it would be the best approach but I am not sure why we are receiving this error message.

Any suggestions would be greatly appreciated as I think we have stumped our VAR with this issue.

Thanks

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Blake,

Based on your story am I correct you have a Sales Order, say for Item 1, Qty = 10, and you shipped and invoiced the Sales Order but you see the Sales Order show shipped quantity but Qty. to Invoice with a value >0. Can you offer an example with pictures so I can reply better to your exact scenario.

Let me provide some other insight as it relates to Inventory Valuation and Inventory to G/L Reconcile report:

Here are a couple of reasons why you would get inventory value if you have 0 quantities when you print Inventory Valuation report and Inventory to G/L Reconcile report as of a certain date.

Scenario 1:

If the adjust cost is processed on 7/31/20, and the Allow Posting From was 7/1/20 on General Ledger Setup, the following would occur:

6/15/20 – Purchase Receipt – $10
6/28/20 – Ship and Invoice – $10
7/15/20 – Purchase Invoice – $12
7/31/20 – Additional Cost for the sale made on 6/28/20 – $2

In this case, when you print the inventory valuation as of 6/30/20, the inventory quantity would be 0 and the inventory value would be 0.

Scenario 2:
If the adjust cost is processed on 7/31/20, and the Allow Posting From was 6/1/20 on General Ledger Setup, the following would occur:

6/15/20 – Purchase Receipt – $10
6/28/20 – Ship and Invoice – $10
7/15/20 – Purchase Invoice – $12
6/28/20 – Additional Cost for the sale made on 6/28/20 – $2

In this case, when you print the inventory valuation as of 6/30/20, the inventory quantity would be 0 and the inventory value would be -$2.00.

Explanation
NAV will automatically post the additional cost to the original invoicing entry because that’s where the cost originally applies to. If NAV detects that the Allow Posting From is before the adjusting date, then it will use whatever the Posting Date is when the adjust cost is ran.

Scenario 3:
If the adjust cost is processed on 8/31/20, and the Allow Posting From was 7/1/20 on General Ledger Setup, the following would occur:

6/15/20 – Purchase Receipt – $10
6/28/20 – Ship and Invoice – $10
7/15/20 – Purchase Invoice – $12
8/31/20 – Additional Cost for the sale made on 6/28/20 – $2

In this case, when you print the inventory valuation as of 6/30/20, the inventory quantity would be 0 and the inventory value would be 0. When you print the inventory valuation as of 7/31/20, the inventory quantity would be 0, but the inventory value would be $2.00.

Explanation
NAV will automatically post the additional cost to the original invoicing entry because that’s where the cost originally applies to. If Navision detects that the Allow Posting From is before the adjusting date, then it will use whatever the Posting Date is when the adjust cost is ran. In this example, since the Adjust Cost process posting date is 8/31/20, it will use this date as the Posting Date for the adjusting entry.

A few notes... If you are Average Costing make sure Inventory Setup/Average Cost Calc Type is setup correctly. I always set it too - Item & Location Code & Variant.

Also, I am not bringing up using Inventory Periods, which is a must when you are maintaining Inventory and keeping the Inventory sub-ledger tie to General Ledger and Inventory Reconciliation.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report
Hi Steve,

No, I don't think that's quite it.

Sticking with your example:

We have a Sales Order for Item 1, qty 10

We ship the order in full so that quantity shipped and qty. to invoice fields are both 10

Customer claims they only received 9 units and that's all they will pay for

We change the qty. to invoice field to 9 so the invoice generated is only for 9 units

We then change the qty. to invoice to 0 so the order can be deleted

What we noticed is that if we look at the entries for this item/lot and sum up the quantity output and shipped we get 0 but if we sum up the cost amount output and shipped we are off by the cost of 1 unit.

Below is a screenshot of the ILE for one of the items showing a shipment that was not invoiced in full. The sales order the shipment was posted against has long been deleted.

Here is a screen shot of what we see for that item/lot combination. Those are not the actual unit costs, I set it to $5/unit for the example

You can see that in this example $10 appears to be still sitting in our finished goods inventory. This matches the 2 units that were not invoiced on the shipment above.

I don't think any of those scenarios apply since several of these items were discontinued and all the entries should be accounted for.

I also didn't run any of the inventory valuation reports. What I did was download all the item ledger entries for our finished goods. I used a pivot table to look at all the items by lot and by entry type. I then found all the lots where we still had a balance on hand but 0 quantity. I double check this by looking at general ledger entries and when I match the transactions up I get the same result.

We use FIFO costing for all our items.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Blake,

I am going to recreate the scenario in Cronus:

Step 1 - Create Sales Order for an Item for Quantity = 10

Step 2 - Post Shipment of the 10

Step 3 - Customer states they only received 9 so Invoice 9

Step 4 - Erase the Qty to Invoice so I can delete the Order

You notice my message above doe snot allow this because I shipped 10 therefore I need to Invoice 10. You cannot delete the Order. This would create inventory and accounting issues and audit problems.

I would suggest when this occurs you can do a few things:

1. Undo Shipment and correct the Quantity.

2. Invoice the 10 units shipped and adjust the Unit Price to show 1 was free, since you are NOT going to place the Item back into inventory.

In #2 , since this sounds like you are not bringing the Shipped Item back into Inventory, might be the approach to follow and your posting and cost will flow correctly. I could have added a Line Discount to reflect 1 was free. I am suspecting you only want to Invoice 9 to them vs 10. You could also enter an Item Line showing the Item was not shipped netting the Invoice to 9 shipped and Invoiced. You still will need to do an Item Journal to negative adjust the Item out of inventory since it was truly not received back.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

Steve,

My mistake, we changed the Qty. Shipped Not Invoiced field. In our system when we change Qty. Shipped Not Invoiced to 0 it lets us delete the order, although it probably should not have. We are now noticing those inventory and accounting issues you mentioned.

I was able to replicate this in Cronus.

Here we have a sales order for 4 items and shipped 4

Invoice 3 of the units

Change the Qty. Shipped Not Invoiced field to 0 and try to delete

Sales Order no longer appears

One of the shipment lines is now not invoiced in full.

We have stopped this practice since we realized it was causing issues and we have a better system in place now for reflecting the true quantity we want to invoice.

The issue is that we need to clean up all the old entries that have not been invoiced in full since there still seems to be some $ amount sitting in inventory. We thought the best approach would be to create a new invoice and then pull in the shipment lines that haven't been invoiced in full using the Get Shipment Lines function. That way we could create another invoice that was connected to the shipment lines and then credit to clear out that invoice. The issue is that when we try to pull in the shipment lines we get an error message that says "The program cannot find this Sales line."

Let me know if any of this isn't clear.

Thanks

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Blake,

Your Quantity Shipped Not Invoiced field is a custom field and the fact you can perform a deletion means your Partner has created custom code. You should contact your Partner to discuss this. Your Cronus Company is this inside the same database as your LIVE company? If yes, then the custom code is working in Cronus. I would show the Partner that created the custom code the issue like you described here and have them resolve.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

Steve,

Yes, Cronus is in the same database as our LIVE company. Are you saying that base NAV prevents the deletion of sales orders that have been shipped but not invoiced to prevent this particular error? Is there any way to invoice the remaining amount on these shipment lines? I reached out to our Partner but they have not been able to make progress on this issue.

Thanks

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Blake,

Yes, standard NAV prevents this from occurring. I can only think about walking the Posted Shipment backwards and bringing back the Item into inventory, Apply the Item Journal to the Posted Shipment on the ILE. Now you have reversed the entry. Now you can sell the Item out of Inventory to the Customer at 0.00 Unit Price or adjust the Item out of inventory via Item Journal.

Do you follow?

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Blake Burnley 10 on at

Like (0)

Report

Steve,

Yes I think I understand what you mean. I will give that a try but I am not sure if that will change the initial shipment to be invoiced in full. Wouldn't the new shipment/ item journal to remove the items from inventory be invoiced in full but not the initial shipment?

Thanks

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Blake,

My steps will NOT update the original Shipment with the missing Invoice. This step should be stopped and go back to standard. My suggestion will only correct the Inventory. Your step deleted the Sales Order so their is no document to update.

Steve

Was this reply helpful? Yes No
Am_Pa82 10 on at

Like (0)

Report

Sir, I have seen with my customer that he purchases some qty. of an Item which is still showing in Item card and value entry. But when I go to ILE then at Remaining Qty field 0 value is showing .Means (Pur. Qty=10, Invoiced Qty =10, Remaining Qty =0,Open=No)I am unable to understand how it’s possible but Now customer unable to create the sales invoice or any negative transaction for this ITEM as it shows Insufficient QTY but neither this item have any other posted entry nor pending entry .

Was this reply helpful? Yes No