mibuso.com
Home› NAV Three Tier

Quantity Zero but cost amount(actual) +ve/-ve (per Location)

Chandra_BoharaChandra_Bohara Member Posts: 13
edited 2015-05-05 in NAV Three Tier
In Nav 2009 r2 (inventory valuation per Location)

Previous Set Up
Average Cost Calc. Type: Item
Average Cost Period: Day
All Purchase Orders, transfer orders and Sales Orders completely invoiced.
Item Costing Method: Average

After running ACIE (Adjust Cost Item Entries batch job),


Below is the inventory valuation of Item (A001) at different Location (Say BLUE, YELLOW, GREEN, BLACK):

Qty means sum of invoiced Quantity
Amount means Sum of Cost Amount(Actual)

Item
>Location
>Qty
>Amount
A001
>BLUE
> 0
> -20
A001
>BLACK
> 0
> -30
A001
>YELLOW
> 1
> 120
A001
>GREEN
> 0
> 30
Total
> 1
> 100

After Changing following Set Up
Average Cost Calc. Type: Item & Variant & Location
Average Cost Period: Day
All Purchase Orders, transfer orders and Sales Orders completely invoiced.
Item Costing Method: Average

Cost is adjusted (In Item Card): False
Cost is adjusted (In Avg. Cost Adjmt. Entry Point) of all valuation dates: False

After running ACIE (Adjust Cost Item Entries batch job), I was expecting following result

Item
>Location
>Qty
>Amount
A001
>BLUE
> 0
> 0
A001
>BLACK
> 0
> 0
A001
>YELLOW
> 1
> 100
A001
>GREEN
> 0
> 0
Total
> 1
> 100

but the result was same like before i.e.

Item
>Location
>Qty
>Amount
A001
>BLUE
> 0
> -20
A001
>BLACK
> 0
> -30
A001
>YELLOW
> 1
> 120
A001
>GREEN
> 0
> 30
Total
> 1
> 100

Finally what I want to ask are;

(1) Is it possible ,by changing Average Cost Calc. Type from Item to Item&Variant&Location, to revalue the amount of an item to zero in location which has quantity zero?
(2) If not possible by this way, can anyone please suggest me other possible way?

Comments

  • Chandra_BoharaChandra_Bohara Member Posts: 13
    Anomaly in stock value per location.




    What we did?




    Since we changed average cost calculation type in inventory setup in FY 2013/14 (i.e. after closing FY 2012/13),

    the average cost calculation type in FY 2013/14 and following year (in table 50) has been changed to Item&Variant&location but the same setup of FY 2012/13 has remained as it is i.e. item





    Note: Average cost Calculation Type has setup in two tables

    (1) Inventory setup (table no. 313)

    (2) Accounting Period (on Starting Date) (table no 50)




    Because the setup of average cost calculation type in the accounting period (2012/13) is item (not item&location&variant), the system is not able revalue the stock per location.




    What has to be done now to correct the stock value per location?




    Solution:

    1. Please ensure that the inventory period is closed up to 07/16/2014. But If you want your inventory value on 07.16.2014 should be equal to that on 07/17/2014 (i.e. closing value should be equal to opening value) then make sure the inventory period

    is closed up to 07/17/2014.




    2. In table 50 (Accounting Period), Change Average Cost Calc. Type of Fiscal Year 2012/13 from Item to Item&Variant&Location (Note: Make the field editable first). The change process will take several hours because it will make necessary adjustment in 5804 (Avg. Cost Adjmt. Entry Point) table




    3. Then run adjust cost-item entries batch job. This will take several hours to complete.




    4. There will be Change in Stock value in GL due to change in average cost of items (which has +ve quantity and +ve stock value in total but -ve/+ve stock value with zero quantity per location).

    [note: export Chart of accounts up to 07/16/2014 and up to till date, before running this process & that after running this process. There will not be any change in GL balances till 07/16/2014 but to document the changes in GL account's balances till date]




    5. Pre-requisite: Inventory of item should be equal to net invoiced quantity i.e. there should not be outstanding Received POs, Shipped SOs, Shipped service orders, Shipped Purchase return orders, Received Sales Return Orders of items. Else anomaly in stock value per location of these items will not be corrected by this process perfectly. However it will be later corrected by the system after those PO,SO, PRO, Service orders, SROs have been invoiced.

    This information (Inventory and net invoiced quantity) is in item table.




    6. This has to be done before upgrading data in Nav 2013 R2.




    7. It is strictly advised that this process has to be done in test database before. Upon satisfactory result in test database, should we process in Live database during off hours (like Saturdays).
  • Chandra_BoharaChandra_Bohara Member Posts: 13
    Adjust Cost Item Entries batch job (795) is taking more than weekend hours (38 hours) to execute and we cannot run this batch during working days due to table lock error?:
    .....................................................................................
    In both in inventory setup and accounting period, Average Cost Calc. Type was Item previously. We changed to Item & Location & Variant.
    Additional information
    Average Cost Period: Day
    Automatic Cost Posting: Yes
    Automatic Cost Adjustment: Never
    Data of almost Three fiscal years (07/15/2012 to 10/04/2015)
    1. Opening data as on 07/15/2012
    2. First: 07/16/12 to 07/15/13
    3. Second: 07/16/13 to 07/16/14
    4. Third: 07/17/14 to 07/16/15
    Fiscal Year & Inventory Period Closed till 07/16/2014
    ………………………………………………………………………………………………………………………………………………………………
    Now system has to adjust cost for total of
    183,922 item records
    2,173,866 Avg. Cost Adjmt. Entry Point records
    8,867,151 value entry records
    ………………………………………………………………………………………………………………………………………………………………
    Out of above total, we selected inventory posting group “LUBE” which comprises
    359 item records
    181,530 Avg. Cost Adjmt. Entry Point records
    1,259,046 value entry records
    We started running the report on 10-apr-2015 1 PM and it was running till 11-apr-2015 4 PM and suddenly it got rolled back. So, it run for around 27 hours
    It almost took our week end but did not complete.
    However when we did try for 10 items, it completed in approximately 5 hours.
    Due to “table lock” error, we are able to run this batch during working days (Sun-Friday). That means we can run only from Friday 6 P.M. to Sunday 8 A.M. (approximately 38 hours).
    ………………………………………………………………………………………………………………………………………………………………

    All of the above, this batch job is taking long time to execute. Besides following workarounds
    (1) Running adjust cost item entries for item in small buckets (let’s say 100 instead of 359 at a time)
    (2) Running Adjust-Cost Item Entries first (without cost post to GL) and secondly running Post Inventory Cost to G/L (1002).

    Is there any other ways to increase performance of these batch jobs?
Sign In or Register to comment.