The Lot AVC past value is not stored but you can recalculate the value based on the STOJOU records related to the site, product and lot:
- If you are in V12, you can use the Average lot cost movement inquiry (CONSSML) to recalculate the value for you.
- Else you can export the STOJOU records and rebuild the value in Excel using PRIORD as value to build the AVC lot amount for receipt and QTYSTU to build the Lot AVC base quantity. If you need to do this for a large selection of lots, then you may need to develop a dedicated script.

Usually there is a simpler way: if you receive the lot only once (usually the case, each new receipt lead to a new lot or at least sub-lot), then the AVC will not change overtime so you just have to inquiry the AVC value in the STOLOTFCY table.

Hi Julien, Thanks. More details: My customer needs to know a list of the values of every lot at the end of the fiscal year due to auditing reasons of law. Each lot is a kind of large tub containing  liters and with its own overhead/landed cost. They also can be mixed and be sold partially and during years or nothing that fiscal year, but needed to appear to that list.

So, do you thing, as I say, it is possible to rebuild them from STOJOU or STOLOTFCY? I'll try to analize, but dont understand why not by standard..maybe not possible ??

Regards

Mfalco said:

but dont understand why not by standard..maybe not possible ??

It's standard since V12 using the new inquiry CONSSML.

For the next FY period, I advise to print and save the stock valuation report on the last day for the FY.
Best is to print it automatically as described in the blog below:
https://www.sagecity.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/posts/how-to-schedule-the-stock-valuation-report-to-run-automatically-and-save-it-a-specific-location

Mfalco said:

it is possible to rebuild them from STOJOU or STOLOTFCY?

It's possible. There are multiple ways to do this.

If the product was set with lot AVC valuation method during the entire fiscal year, then you need to build a SQL query to sum the VARVAL amount on on STOJOU records from date of go-live to last day of the closed fiscal year and group by STOFCY, ITMREF, LOT and SLO.

Pay attention if you have non absorbed value (AMTDEV) cause in that case auditors might need to have this  information as well.

Of course, please test carefully to ensure this process is working as expected.

Ok, if we must consider just the sum of the VARVAL values regardless of any type of record or flags from STOJOU, that looks very easy

Select F.LEGCPY_0, S.STOFCY_0, S.ITMREF_0, T.TEXTE_0 ITMDES_0, S.LOT_0, SUM(QTYSTU_0) QTUSTU_0, S.STU_0, SUM(VARVAL_0) VARVAL_0, SUM(VARVAL_0/QTYSTU_0) AVC_0, SUM(AMTDEV_0) AMTDEV_0
From STOJOU S
Inner JOIN FACILITY F On F.FCY_0=S.STOFCY_0
Inner JOIN ATEXTRA T On T.CODFIC_0='ITMMASTER' And T.ZONE_0='DES1AXX' And T.LANGUE_0='SPA' And T.IDENT1_0 = S.ITMREF_0
Where S.IPTDAT_0 BETWEEN '01/01/2000' And '31/12/2020'
Group By F.LEGCPY_0, S.STOFCY_0, S.ITMREF_0, T.TEXTE_0, S.LOT_0, S.STU_0
Having SUM(S.QTYSTU_0) <> 0 And S.LOT_0 <> ''
Order BY 1,2,3,5

Thanxs a lot, Julien

Hi Mfalco,

Did Julien's suggestion help you resolve the issue? If it did, please mark this as Verified answer.

Hi Julien,

Happens that, as I'm tracking by Average cost evol./movement sometimes, I see some products (AVC or AVL) tha have a value in Amount base before column at the first movement of history! Therefore, the Last base, the actual one, doesnt match with VARVAL summatory!, Do you know the reason that appearing that value? That difference is not in STOJOU. Thanks

Hi, I see no good reason why the AVC qty can be zero while the AVC amount is not zero... This might happen, if some of the movements are not based on order price or historical AVC. Can you check for those cases, if you have any STOJOU records where PRINAT <> 5 or 8?

I just got an idea: it's maybe due a variance non absorbed (AMTDEV). If you have a receipt adjustment that can't process the received quantity (because already gone), then you will have a variance non absorbed. In that case, I am not sure of the average base amount include or not the AMTDEV. To verify my theory, can you check, on the case with starting AVC amount not nill, if you have some STOJOU records with AMTDEV <> 0?

Hi Mfalco,

What is the version and patch level of Sage X3 and are you using currency conversion?

Hi Mfalco,

What is the version and patch level of Sage X3 and are you using currency conversion?

Hi Vanessa, in this case, v11 P9, but also seen in v12 p23.  No currency conversion

In SEED you can also seen some examples, but maybe due to have STD method