Item tracking
-
Item tracking
Posted by DSC Communities on December 8, 2016 at 12:49 pm-
Debra Rohlik
MemberDecember 8, 2016 at 12:49 PM
We are on NAV16. I am trying to invoice a sales order and am getting the following error: The qty to invoice does not match the quantity defined in item tracking. There are seven lines on the order and I don’t know which line is causing the error. There have been multiple errors regarding the item tracking but they referenced an item number. This latest error does not.
Looking at the item tracking entries I see columns: Quantity(base); Quantity Handled(base); Qty to Handle (base); Qty to invoice(base). What makes up the numbers in these columns?
——————————
Debra Rohlik
Accounting Process Manager
Farmers Union Industries, LLC
Redwood Falls MN
—————————— -
The Qty. to Invoice on the Sales Line needs to match the total of the Qty. to Invoice on the Item Tracking Lines. Basically, if you are trying to invoice qty 5 you have to tell it exactly which 5 serials / lots that you are invoicing.
Sadly the error message doesn’t tell you which line it is. You have to go line by line and check.
Qty. Handled = Qty. Shipped. Qty. to Handle = Qty. to Ship.
——————————
Matt Traxinger
NAV Developer
ArcherPoint Inc.
San Antonio TX
————————————————————————- -
This is not one of my favorite NAv features when your test report doesn’t warn you about errors before you hit post.
While I don’t know you’re exact situation, the SQl below can show you line by line the quantities on the sales document line and the matching quantities that are tracked. This will need to be adjusted for your company and plugged into Excel or SSRS.
I adapted this from something else so i haven’t tested extensively. Let me know if you have questoins.
This shows you all lines (which makes it easier to test). It can be easily adapted to only show errors
WITH ItemTrack AS
(– total Reservation entries by Sales Line
SELECT re.[Source Type] AS SourceType
, re.[Source Subtype] AS DocType
, re.[Source ID] AS DocNo
, re.[Source Ref_ No_] AS Line
, SUM(re.[Quantity]) AS Qty
, SUM(re.[Qty_ to Handle (Base)]) AS QtyToHandle
, SUM(re.[Qty_ to Invoice (Base)]) AS QtyToInvoice
FROM dbo.[CRONUS USA, Inc_$Reservation Entry] re
GROUP BY re.[Source Type], re.[Source Subtype], re.[Source ID], re.[Source Ref_ No_]
)
— compare sales line to item tracking information
SELECT
sh.[Document Type]
, sh.No_
, sh.[Bill-to Customer No_]
, sh.[Document Date]
, sl.[Line No_]
, sl.No_
, sl.[Qty_ to Invoice (Base)] AS LineInvoiceQty
, sl.[Qty_ to Ship (Base)] AS LineShipQty
, it.QtyToInvoice AS TrackInvoiceQty
, it.QtyToHandle AS TrackHandleQty
FROM dbo.[CRONUS USA, Inc_$Sales Header] sh
LEFT OUTER JOIN dbo.[CRONUS USA, Inc_$Sales Line] sl
ON sh.[Document Type] = sl.[Document Type]
AND sh.No_ = sl.[Document No_]
LEFT OUTER JOIN dbo.[CRONUS USA, Inc_$Item] ite
ON sl.No_ = ite.No_
LEFT OUTER JOIN ItemTrack it
ON 37 = it.SourceType
AND sl.[Document Type] = it.DocType
AND sl.[Document No_] = it.DocNo
AND sl.[Line No_] = it.LineWHERE
ite.[Item Tracking Code] <> ” — item is set for tracking
and
(sl.[Qty_ to Invoice] <> 0 — item has started invoicing or shipping
OR sl.[Qty_ to Ship] <> 0)——————————
Adam Jacobson
President
Red Three Consulting, Inc.
Bronx NY
————————————————————————- -
Andre Cavalcanti
MemberDecember 9, 2016 at 8:43 AM
On the item tracking lines you might have more than one Lot No. or Serial No. . The (Lot No, Serial No.) you are not receiving, you can set the quantity to invoice to zero. It should work
——————————
andre cavalcanti
Consultant
UXC Eclipse
montreal QC
————————————————————————- -
David Williams
MemberDecember 9, 2016 at 10:23 AM
Debra,
If you are running the planning worksheet and are on an early version of 2016 there is a bug that causes this. If that is the case, you need to on CU10 or above. Go to Help, About Microsoft Dynamics NAV and check your version. I should be 9.0.46621 or higher.
In the mean time, go to the item tracking lines and expand on the item tracking availability and you will see where some lines have more allocated than is available and you can adjust them to help you post.
Thanks,
David
——————————
David Williams
Application Consultant
Innovia Consulting
Kinston NC
————————————————————————-
DSC Communities replied 7 years, 5 months ago 1 Member · 0 Replies -
-
0 Replies
Sorry, there were no replies found.
The discussion ‘Item tracking’ is closed to new replies.