Create a report to show the location of PO lines

(0) Share

Report

Posted on by Community Member

I am not sure whether if it is possible. But, I am trying to create a report that shows the location (in a warehouse) and quantity of an item which is in a specific PO.

Say I have a PO in which there are PO lines. These lines might have variants as well. The report should show the location of the PO items. Actually, I want to track each Item from the day one when a user creates a new PO for and then received by Warehouse and so forth.

Could you please which tables are involved fo this scenario and how can I find the items' locations?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: Create a report to show the location of PO lines

On "the day one when a user creates a new PO", the items aren't in your warehouse yet; they'll get there only when you send the order, the vendor agrees to fulfill it, ships the goods and you receive it. Only then you can start tracking the goods.

If you want to track each individual items, consider using serial numbers.

In general, all these things like warehouse, warehouse location, pallet and serial number are inventory dimensions (InventDim table).

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: Create a report to show the location of PO lines

If you mean you would like to see where does an exact Received PO's inventory go with all inventory transfers, sales, scraps and everything, then as Martin recommended, you have inventory tracking dimensions for this.

Take a look at the following documentation which explains Batch number and Serial number a bit:

technet.microsoft.com/.../aa549043.aspx

technet.microsoft.com/.../hh209465.aspx

Also depending whether you use the new R3 WHS/TMS modules or just Warehousing I-II, setup might be slightly different, but you should be able to find details on these dimensions by the almighty power of Google.

After that in your report you could link the PO's inventory dimension with the InventTransOrigin (transaction origin) and InventTrans (inventory transaction) tables along with InventDim (dimensions) and InventSum (on-hand inventory) tables to get a picture on the state of your inventory.

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

RE: Create a report to show the location of PO lines

Its unclear what you are trying to achieve.

Will those items always go to the same location? In which case that can be set as an item default and small mod could also show it in the PO but if its always the same then there probably isn't a need.

If not fixed, then when will the location be decided?...... at the time of PO?... or at the time of receipt ....or after QA.... will each line go via another location first e.g. 'goods in"?

Which items will you track this way? All?

What if 5 POs, put the same item, in the same location? You might also need to split a PO line between more than one location etc. You then need some physical label to determine which specific PO related inventory, was moved, or issued, - and to view this in Ax would need tracking dimensions. Then the PO number can be e.g. a batch number and you can use a batch attribute for the location.

Ax would not generally be used to create PO specific locations, and thus the only other option is to hold the detail against an inventory batch.

Maybe manually writing the PO number on the case is a crude workaround.

You could extend the PO line to add a field for each line to capture this data, or use a QA order etc. - but it will not then be maintained by other inventory move/issues and seems more work, with less benefit than using batch trace and an attribute.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
RE: Create a report to show the location of PO lines

Thank you Vimlos for your response.

I have create the query you suggested this way

select * from INVENTTRANS join INVENTTRANSORIGIN on inventtrans.INVENTTRANSORIGIN=INVENTTRANSORIGIN.RECID join INVENTTRANSORIGINPURCHLINE on INVENTTRANSORIGINPURCHLINE.INVENTTRANSORIGIN=INVENTTRANSORIGIN.RECID join PURCHLINE on purchline.INVENTTRANSID=INVENTTRANSORIGIN.INVENTTRANSID join INVENTDIM on purchline.INVENTDIMID=INVENTDIM.INVENTDIMID where PURCHLINE.PURCHID='GEC-001635'

The thing is now I want to know which location these items are in? have you got any suggestion for that?

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

RE: Create a report to show the location of PO lines

This can only tell you at best where you put items at the time of receipt.

If multiple deliveries are mixed in the location, then without batch trace the system will not know from which PO items were issued, and for which PO, items still remain in inventory, so I don;t see how this will address your original requirement.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Create a report to show the location of PO lines

1- how can I do it if I had the serial number as my tracking dimension?

2- Suppose an Item is specific to a single PO and they never be renewed. How can I find their location with this assumption?

Was this reply helpful? Yes No
Verified answer

guk1964 10,888 on at

Like (1)

Report

RE: Create a report to show the location of PO lines

I doubt its straightforward, but its theoretically possible.

The system knows what item codes it has in inventory, so you can filter for only those that are serial tracked.

Each item serial in inventory will be in a known location, so you will also know that.

For each serial the system holds tracking data back to the PO line and the PO number and the receipt transaction.

Your requirement is to go from PO to item location - and because the serial is determined at receipt, the receipt of it against the item line on the PO is known, and the receipt of each serial is recorded to a location.

msdn.microsoft.com/.../inventtrans.aspx

This table contains location and serial etc.

In AX2012 the Direction field was removed from the InventTrans table and was replaced by a new display method direction added to the InventTrans table. This display method returns the same values. A new table was added called InventTransOrigin which holds the relationship between the originating tables (transaction tables) and InventTrans.

The InventTransId, TransType and TransRefId were removed from the InventTrans and moved to the InventTransOrigin table with names InventTransId, ReferenceCategory and ReferenceId respectively.

Every transaction has its own InventTransOrigin table.

For PurchLine the table named is InventTransOriginPurchLine which refers to InventTransOrigin through the InventTransId field.

(Transaction tables like PurchLine, SalesLine, ProdLine etc still contain an InventTransId field but it not recommended to use/refer these because as far as I recall they were to be to be deprecated.)

For more on the table see these articles the ere were improvements at R3 but this is useful background.

daxwhisp.blogspot.ae/.../digging-up-dirt-inside-inventtrans-part.html

daxwhisp.blogspot.ae/.../09

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Create a report to show the location of PO lines

Thank you @Magic1949. your answer and specially those two links helped me alot.

Was this reply helpful? Yes No