Physical Inventory Impacts on Average Invoice Costing Record - Sometimes Updates

[Chuck Boecking]

Hi Everyone,

I was performing some testing on the costing impacts of performing a Physical Inventory on a Product that is tagged as "Average Invoice" Costing Method. If found that a Physical Inventory will 'sometimes' update the Current and Accumulated Qty fields. If the "Average Invoice" M_Cost exists for a given product before the Physical Inventory is completed, the Current and Accumulated Qty fields update accordingly. However, if the product is new and it does not have an "Average Invoice" M_Cost record yet, the Current and Accumulated Qty fields do not change.

Here are the steps to replicate:

1. Create a new product by just giving it a name (i.e. "test")
2. Create an Import Inventory record with the following fields
1. Doc Type = Material Physical Inventory
2. Locator = Default HQ Locator
3. Product = test
4. Qty Count = 10
5. Current Cost Price = 5.55
3. Import the above record and check the Update Costing flag.

The this process will result in the following:

1. A completed and Posted Physical Inventory
2. The posting will be $0.00
3. The Product will get an "Average Invoice" M_Cost record with the correct cost
4. However, The "Average Invoice" M_Cost record will show 0 qty

If you then replicate the above Inventory Import a second time (with or without the Update Costing flag), the "Average Invoice" will get its Qty fields updated, but by only the Qty of the second Physical Inventory. The first Physical Inventory is lost in space; therefore, you will get a posting error when you go to ship your last quantities.

As a second note, I tried to create the "Average Invoice" M_Cost record for my new product using the Create Costing Records process; however, this process did not create it. It did nothing. The Cost Adjustment window will not create the record; it throws an error stating that no M_Cost record exists. If you create Physical Inventory without the import process, the posting fails because of no M_Cost record. The only way I can think to create M_Cost record is to fake an invoice.

Does this situation below as a Jira ticket?

Regards,

Chuck Boecking

erp-academy.chuckboecking.com

[Chuck Boecking]

Hi Everyone,

Sorry for the long email - the short of the story is:

You should not let the Import Inventory process complete your Physical Inventory when you are importing/updating costs. Doing so causes the Physical Inventory to post incorrectly and the Product's costing record to be updated incorrectly. The fix is to import the Physical Inventory in a "Drafted" Doc Status. Manually complete and post the Physical Inventory after the import finishes. All will work as expected.

Regards,

Chuck Boecking

erp-academy.chuckboecking.com

[Carlos Antonio Ruiz Gómez]

Chuck, maybe you're missing the posting of the Cost Adjustment document generated on the import inventory.

I mean, when importing inventory with costs two documents are created: physical inventory and cost adjustment.

We recently modified the cost adjustment to set the initial cost in these conditions:

https://bitbucket.org/idempiere/idempiere/commits/e01a234

Regards,

Carlos Ruiz

[Chuck Boecking]

Hi Carlos,

I am aware of the Cost Adjustment change (very nice - thank you!!). I must have missed a step in my testing. Here is what I find in today's testing (slightly different than yesterday).

• The initial Import Inventory for a newly created Product (no cost record yet) will yield a Physical Inventory with a posting error. 
• You must repost the Physical Inventory to (1) see the affects on inventory gl account and (2) see the qty change on the Product's costing record.

Below is an XLS that walks through two subsequent imports (with cost adjustment) for the same product. The first pass is with the import auto completing each of the Physical Inventories. The second pass is with the user manually completing each of the Physical Inventories.

• First pass (auto complete): so long as you repost the initial Physical Inventory, all works as expected.
  
• Second pass (manual complete): all worked as expected

https://docs.google.com/a/chuboe.com/spreadsheets/d/1zX1ZPww-3WKsncuzdjDC-Oy4VLgp98lZqBnK0ouw0eA/edit#gid=0

Even though the above posting error is not perfect, it does seem acceptable. You agree?

Regards,

Chuck Boecking
erp-academy.chuckboecking.com

[Carlos Antonio Ruiz Gómez]

> Even though the above posting error is not perfect, it does seem acceptable. You agree?

Agree

[Chuck Boecking]

Thank you for taking the time to read and respond!!

[Andreas Thiel]

Dear Chuck,

we are a german wholesaler and worked had to solve some problems with (perhaps) typical german needs for idempiere.

Concerning exactly your issue we added in the inventory booking process that the Current Qty is set on the physical counted quantities in stock.

We think that in addition to the standard a "booking sheet" for articles should be created where you can change the physical Qty in stock when you
encountered a mistake. A line in this booking sheet should effect the same booking as if you book a delivery.

Our Idempiere-Freak Patric Maßing can give you further informations over all changements we needed for european or german law requirements.

Kind regards from germany

Andreas Thiel
Hans Auler GmbH