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

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 Julien,

Yes, it seems the reason is some change in valutation method, but there is nothing in AMTDEV

I've seen other similar changes, to AVC to AVL for example.

But, Do you think there is a way to fix it in order to match VARVAL and actual AVC??  That difference is not reflected in STOJOU, so I guess the unic way to go back is the same way AVC inquiry does, so just by hard-code from last value.

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

Hi Mfalco, The valuation report and this AVC evolution query share a similar process to rebuild the past AVC. Please see the discussion below for more details; 
https://www.sagecity.com/support_communities/sage_erp_x3/f/sage-x3-general-discussion-forum/153542/cumulative-auc-cost-versus-stock-valuation-report

In a nutshell, if the PRINAT for an issue movement is not 5, this mean the recalculated past AVC base amount will not be correct. In such scenario, you can end with a starting AVC base amount base different from zero.
In that case, even the sum of the VARVAL amount (done using the query) will not be correct. Indeed, if you have some movements valued with standard cost and some valued with order price (for the same product-site), you add apples with bananas... In such context, rebuild past lot AVC is way more complex. For this reason, it's important to ensure you never use a valuation method mixing order cost and standard cost and your customer should not modify the valuation method of a product if there is existing stock.

Agree.

But, what I have verified is that, in case there is no error due to change of valuation method, but the method is just AVL, and, there are actually several prices by lot, the difference between the sum of VARVAL moviments of just those lots that just have qty balance at that date, is, precisely, that value in "Amount base before" at older moviment, regarding the AVC standard that the AVC inquiry calculates.

So, my sql inquiry, to know AVL at date, should work. Just, in case of starting with other method by mistake, better to issue the stock and enter again with new lots at AVL, and the history of the active lots will be ok forever

Before that change, eat the bananas...

Regards