R41544 Item Balance/Ledger Integrity When Using Weighted Average Cost

[kmckay]

We are finding out that the R41544 is not very beneficial as we are using average cost inventory. However, we still find it important to identify integrity issues by comparing the Cardex and As-Of Tables (F4111 & F41112) with the Item Location Table (F41021). I opened a call with Oracle and there is a BUG 8742885. However, they consider it an enhancement SAR. Has anyone else experienced issues with this report and what have you done to resolve them?

 

[JMast]

We are on Weighted Average also. We eventually created a custom process which I will outline below. We also noticed some inconsistencies with Cardex transactions not getting into the ASOF correctly. After much review, our consultant concluded that Average cost was the cause and as you found there would be no fix from Support. This was a couple years ago.

Our process:

Philosophy / Assumptions:
1. The Location file is likely to be correct or corrected when wrong through cycle and full inventory counts.

2. Cardex will be used to diagnose what happened when, but not used for integrity or validation.

3. ASOF will be used as the reporting/summary/integrity source to compare Location (Inventory System) with GL (Financial System).

4. ASOF and Cardex may or may not match exactly.

Solution:

We built a custom UBE that will compare the Location file with the ASOF file by item, branch, location, lot detail on both quantity and value.

This job is run after all batch jobs, especially R41542, and before any new transactions are entered in the system. This is the only time when the Location file and the ASOF should be in synch.

The output is in excel format which allows us to sort and review the large differences.

We added code to the job to update the ASOF with the changes essentially forcing it to match Location.

We can then use the ASOF in reports to compare against the GL.


We run this process on the last working day of the month, so our accountants can review the differences and make Journal Entries to Inventory as needed.


I hope this gives you some ideas and let me know if you have questions.

Jer

 

[kmckay]

Regarding number 4.
ASOF and Cardex may or may not match exactly.”
Since the Cardex “builds” the AS-OF, how can they not match (beyond a rounding error or manual entry)?

It sounds like you are using the As-Of to compare to the GL but there is no GL detail in the As-Of. The Cardex has a One-to-One relationship to the G/L which is the R41543 - not the R41544. Or is this something that you custome wrote?

 

[JMast]

As I said in the original response we have found that there are occasional transactions in Cardex that the R41542 misses writing to the ASOF. Oracle chalked it up to Average Cost and no action taken. You didn't state what release you are on, so maybe it is fixed in later releases.

I think I did not answer your original question well with my first post. We do the process in my response to keep the ASOF accurate with the Location file. This enables a direct connection/validation from G/L to the Inventory value in the Inventory system (Location file). It also cleans up the ASOF locations which have dollars but no quantity (the result of having Average Cost at the Branch level).

Here is how our accountants validate the G/L:
Daily:
R42542 is run each night after batch processing.
1. Run a Trial Balance by Object
2. Run a custom report that pulls the ASOF by Item, Branch, Location

If the totals don't match (ignoring rounding errors):
1. Run Access query on Cardex by Doc Type
2. Run Access query on G/L by Doc Type

Identify the culprit Doc Type and run detail queries on Cardex and G/L for that Doc Type to find the missing/broken transaction.

We are a manufacturing and distribution company with thousands of transactions daily. Being on Average Cost, our accountants keep a daily watch to fix errors both in the Branch Unit Cost and the GL. They work closely with manufacturing, warehouse, purchasing to educate users and/or modify processes to reduce the errors.

The result, over time, has been very little time spent investigating and fixing errors.

We do run the R41543 every Saturday night, but they rarely reference it.

I hope this helps explain things more clearly.

Jer

 

[Saskia]

One of my customers is running 02 costing on 8.11 SP1.

We have created a report on the AsOf file & Cardex, to ensure inventory integratity. Report shows beginning stock for a period (from AsOf), cummulated transactions (from Cardex, seperated in different columns), end of period stock (calculated) for both qty and value.

With this we can check if the system creates errors.

We have encountered that credit notes, received through transportation, are updated to the AsOf file twice.
There are more issues, but we have not yet classified this.

Kind regards,

Saskia

 

[TAV]

This is very helpful iv validation some of our assumptions so thank you for posting such detailed information.

One question:

Is there a way to have your process work without the system being quiet? In other words in a 24/7 shop how would this work?

My thinking is with the backup being restored to a new environment which we have anyway. We can run the reports there even though the production system has come back up and is live with transactions again. Is there another way to account for activity and run this in production?

Let me know what you think.

Thanks,