ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Anybody have a Inventory Value Report to share?
http://www.istechforum.com/YaBB.pl?num=1378732317

Message started by gtladmin on 09/09/13 at 06:11:57

Title: Anybody have a Inventory Value Report to share?
Post by gtladmin on 09/09/13 at 06:11:57

Our accounting Manager wants to know how to make a Crystal inventory value report that also shows the units on hand in all locations (including one labeled as Quarantine)? She is not sure of all the tables/fields to use in this situation. Any ideas?

Thanks!!!

Val

Title: Re: Anybody have a Inventory Value Report to share
Post by David Waldmann on 09/10/13 at 04:43:35

If all you need is units you can use BKICMSTR, BKIC.PROD.UOH. However, you said "value report", so if you need the value you can use BKIC.PROD.AVGC.

If you want to subtotal BY location (not just include all locations), you will need to also use MTICMSTR, and group by MTIC.PROD.LOC

Regardless of how you make your report, you should be advised that in order to have accurate numbers you will need to refresh the stock status, which can be done when you do a Reorder Report (IN-D); it doesn't need to be actually printed.

Two resources that we have found invaluable for determining where what data is are the "FILE NAMES" help page, and the XLS list of tables and fields (recently updated, includes a lot of description) available on the IS Tech Support site.

Title: Re: Anybody have a Inventory Value Report to share
Post by Lynn_Pantic on 09/11/13 at 12:16:25

Not quite. You need to total the BKIC.LOC.UOH for all the BKICLOC records for a given item to get the total quantity, then multiply by the BKICMSTR BKIC.PROD.AVGC to get the value. BKICMSTR UOH excludes any Warehouse Locations designated as Segregated in IN-L-B. MTIMCSTR MTIC.PROD.LOC is Bin Location when Warehouse Control is not turned on and has nothing to do with warehouse locations or on hand quantity.

Title: Re: Anybody have a Inventory Value Report to share
Post by David Waldmann on 09/11/13 at 13:51:57

Lynn_Pantic wrote:

BKICMSTR UOH excludes any Warehouse Locations designated as Segregated in IN-L-B.

I'm not sure what that means. When you say "Segregated" does that mean if you have more than one location? Of just that you have a location other than [blank]? Because we have a location and use BKIC.PROD.UOH for several reports and it seems to be fine... ??

Title: Re: Anybody have a Inventory Value Report to share
Post by Lynn_Pantic on 09/11/13 at 16:32:39

If you create multiple Warehouse Locations, you can designate one or more to be Segregated by entering a Q (Quality), R (RMA) or S (Service) into the Location Type in IN-L-B. If any of those 3 characters has been entered as the Location type, then any inventory in that location is considered Segregated from production inventory and excluded from the On Hand and Available inventory as seen on the main IN-A screen and stored in BKICMSTR. It is visible if the Locations button is clicked in IN-A.

Title: Re: Anybody have a Inventory Value Report to share
Post by gtladmin on 09/12/13 at 12:25:29

Thanks you guys! I am passing this along to the accounting manager... will follow back here if she
needs more information.

Kind regards,

Val