ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Min Ord Qty
http://www.istechforum.com/YaBB.pl?num=1171633919

Message started by tcook on 02/16/07 at 05:51:59

Title: Min Ord Qty
Post by tcook on 02/16/07 at 05:51:59

I am writing a report and need the Min Ord Qty field. Does anyone know what the field name is?
Also, I need the usage, preferrably last year's usage.

Title: Re: Min Ord Qty
Post by kkmfg on 02/16/07 at 06:36:56

You might want to checkout http://www.istechsupport.com/dl/dbafile.zip

It's a list of the fields in all of the tables along with descriptions of many fields. I think that the info you are looking for could be found in bkicmstr

This sort of question would be a great addition to the wiki I think. I'll be sure that happens.

Title: Re: Min Ord Qty
Post by tcook on 02/16/07 at 07:42:43

Unfortunately, I have checked this spreadsheet, but I still do not see where the Min Ord Qty is, nor the month by month usage is.

Title: Re: Min Ord Qty
Post by kevind on 02/16/07 at 08:20:26

The table is BKICMSTR, the field is BKIC_PROD_RAMT (Reorder Amount).

I thought the monthly usage was calculated each time you looked at that screen. In my Crystal reports, if the items only appear on invoices (not issued to WO), then I use the BKARHIVL table to calculate qty shipped. If the Items are also issued to WO's, I use the INVTXN table to calculate usage.

Title: Re: Min Ord Qty
Post by tcook on 02/20/07 at 13:33:09

Thanks for the Min Order Level field!

On the usage, there are fields in MTICMSTR that show YTD, MTD, Last year info, but when I look at Maintain Database, our data shows all zero's. Do I need to calculate? How do I access this info?

Title: Re: Min Ord Qty
Post by Tim Keating on 02/21/07 at 11:40:40

As far as I know, the usage fields are not supported in DBA. You need to calculate from transaction files.

Title: Re: Min Ord Qty
Post by tcook on 02/21/07 at 14:13:17

I'm still new to Crystal Reports. Can anyone help with a formula to calculate annual or monthly usage for Type R parts?

Title: Re: Min Ord Qty
Post by Tim Keating on 02/21/07 at 15:15:17

Tara: The INVTXN File is the best place to get transactional data on parts.

MTIT_TYPE Field contains transaction code per the following:

http://home.earthlink.net/~keatingt/DBA_SS.jpg

MTIT_DATA is Transaction Date
MTIT_CODE is Part Number

Link to BKICMSTR File to pick up part description and type, etc... and you're off to the races.

Title: Re: Min Ord Qty
Post by kevind on 02/23/07 at 08:17:58

If you want to take into account adjustments, it is a little more involved.

When you adjust items out of inventory, the adjustement ('A' type) record is a negative quantity. Issued ('I' type) to WO and shipped ('S' type) records are positive quantities. You have to negate 'A' type records to have a correct usage quantity.

Here is a simple sample of a YTD calculation that includes 'A' transactions.
http://spectronics.com/dba/sample_YTD_usage.zip
(simple, not pretty report) ::)

Title: Re: Min Ord Qty
Post by GasGiant on 02/23/07 at 12:09:25

Cool, another one to post on the Wiki

Title: Re: Min Ord Qty
Post by kevind on 02/23/07 at 13:33:53

I am Wiki impared :P

I would if someone would give me some guidance. 8-)

Title: Re: Min Ord Qty
Post by dameng on 06/26/07 at 22:42:24

i use a Stored Procedure for my Crystal Report for Inventory Usage. basically it sums the qty by transaction type on the INVTXN file for (J,I,M,S) types grouped by Item Code for the past 12 months.

here is a segment of the SP, becuase i also return other values useful for that type of report. i have it linked with the BKICMSTR and MTICMSTR
SELECT
SUM(IV.MTIT_QTY)
FROM INVTXN IV
WHERE IV.MTIT_TYPE IN ('J','I','M','S') AND (IV.MTIT_DATE > CURDATE() - 365)
AND (IV.MTIT_CODE = IC.BKIC_PROD_CODE)
GROUP BY IV.MTIT_CODE

the Usage Report that one views in IN-A is calculated everytime you clik on that Usage button using the similiar logic.

hth