I'm trying to find a table or page to use with the OData web services that links a production order back to it's originating Sales Order. Does anyone have any success in doing this? I've tried seemingly everything and can't find the right page or table.
The closest I got was with Order Tracking but the table itself does not hold any data.
Table "Order Tracking Entry" is used internally as a buffer for order tracking functionality, it does not actually store any tracking information. If there is a link between a sales order and a manufacturing order, the reference will reside in the "Reservation Entry" table.
But tracking must be enabled explicitly for each item you want to track - it is items that are in fact tracked, not orders.
Tracking is enabled in the item card:
If tracking is switched on, the planning engine will create a pair of reservation entries for supply and demand.
Actually, this only works for open production orders/sales orders and doesn't solve our problem.
Perhaps put another way, we are looking for a Sales Order Profit Report with drill down detail into the COGS through the Production Order.
Then, probably, Item Appliation Entry table is what you need. This table keeps the history of all item transitions as a graph adjacency list. But when orders are posted, you won't get all information from one table - first you need to find item ledger entries posted from the sales order, then find these entries in the "application entry" list, and track their source.
1. Find posted sales shipment (Sales Shipment Header table). Here, you can use the field Order No. to find shipments posted from a particular order.
2. Find item ledger entries originating from the shipment (table Item Ledger Entry, filters: Document Type = Sales Shipment; Document No. = <Your sales shipment no.>)
3. These are your outbound entries - find them in Item Application Entry, in the field Outbound Item Entry No.
4. If the item is sold directly from production, then Inbound Item Entry No. is the entry from the production order. Else (if there are intermediate transfers, for example) repeat the search recursively until the required entry is found.
5. Fields that you are looking for in the item ledger entry are: Entry Type = Output, Order Type = Production, Order No. = Prod. order no.
So this method, combined with the reservation entries, did result in getting 99% of all the production order - sales order links. The only errors were from production orders that were started (costs accumulated) but then canceled by the customer (no shipment). It's not a deal breaker but if you have any suggestions on how to also capture that it would be appreciated!
As an aside, why doesn't NAV just store the originating Order on the Production Order table? It would make things way easier
And wh does the "reservation entry" method not help in this case? As long as the sales order exists, it will be linked to the production order, or an item ledger entry posted from this production.
I suppose NAV doesn't store the originating sales order simply because it does not have to exist. Demand for the manufacturing order can be in a form of a sales forecast or an inventory reorder point, or a complicated combination of various factors.
On the other hand, it could be that this option was just not considered at the time when manufacturing module was developed, because a later addition - Assembly - does have this kind of table directly linking sales with assembly orders.
In the case of a canceled order that was already in process there is no reservation entry anymore, just like finished orders. There's no shipment either so either tie back to a sales order aren't possible from the item ledger entries or reservation entries.
And what is a cancelled production order? Was output already posted? If the output entry was posted, reservation entries with "Tracking" status should link the item ledger entry to the sales order. And it works the same for a finished production order - when order status is changed, reservation moves from the production order to the output item ledger entry. You should still be able to track it by the reservation entry till the moment of shipment. Then reservation is deleted and item application entries are created.