Microsoft Dynamics AX (Archived)

PURCHLINE VS INVENTTRANS RELATION

(0) Share

Report

Posted on by Community Member

Hello, is everyline of purchline table have supposed to have a corresponding line on inventtrans table?

The standart relation is :

purchline.inventtransid -> inventtransorigin.inventtransid -> inventtransorigin.recid -> inventtrans.inventtransorigin

or as in sql:

SELECT *
FROM dbo.PURCHLINE AS P
LEFT JOIN dbo.INVENTTRANSORIGIN AS I ON I.INVENTTRANSID = P.INVENTTRANSID
LEFT JOIN dbo.INVENTTRANS AS T ON T.INVENTTRANSORIGIN = I.RECID

I'm looking for some data corruption so i have to know if this structure is correct.

Regards.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

This is correct. At creation of the PO line it creates the inventtrans origin and an inventtrans record. Note that due to e.g. partly delivery there can be more than ome inventtrans record related to the inventtrans origin.

Was this reply helpful? Yes No
Suggested answer

Bashir Ahmad 5,248 on at

Like (1)

Report

Correct You can use it ,

also you can use InventTable AS The InventTable table contains information about items.

Query will be like:

SELECT ID.RECID,P.RECID,PL.RECID, P.DATAAREAID,P.PURCHID,PL.DATAAREAID,PL.PURCHID , PL.INVENTDIMID , ID.INVENTLOCATIONID,pl.PURCHSTATUS FROM PurchLine PL

INNER JOIN PurchTable P ON P.PURCHID=PL.PURCHID

INNER JOIN INVENTTABLE IT ON IT.ITEMID = PL.ITEMID

INNER JOIN INVENTITEMGROUPITEM ITG ON ITG.ITEMDATAAREAID = IT.DATAAREAID AND ITG.ITEMID = IT.ITEMID

INNER JOIN INVENTDIM ID ON ID.INVENTDIMID = PL.INVENTDIMID

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, now as part 2, if those records in inventtrans table do not exist, how can I recreate them?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sin5k4,

Can you indicate if the missing inventtrans records are related to items which are setup as service or the tick box Stocked product on the inventory model group is not enabled?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello,unfortunately all these items are stocked products :(

Was this reply helpful? Yes No
André Arnaud de Cal... 301,130 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sin5k4,

You can try to set the Purchase order to the type of Journal. Save the record and then put back to type Purchase order. This Purchase type field is on the purchase order header.

Was this reply helpful? Yes No