Reorder Level & Order Up To History

[JayTheGeek]

My environment Macola ES Release 372 v4.0.0.324
This is a Distribution issue, but might fit with many manufacturing shops that use Min/Max.

I am looking for the ability to track Reorder Level & Order Up To (Min/Max) over time.

I have generated some wonderful Crystal Cross-Tab reports and graphs that show a 12 month history of stocking levels. These are excellent reports to illustrate problems within our distribution focused company. I.e. if sales and stocking requirements increase at our selling branches, then without changes our stock at the distribution warehouse depletes and we run into stock out issues.

I am able to query the iminvtrx_sql to dig up the history on inventory quantities. I routinely set the beginning balance records so these are very helpful in tracking the trends in Location Quantity on Hand.

My problem is that I can not look at changes to the Reorder Level & Order Up To values. I don't see these in any of the 'history' or 'transaction' tables.

So the first question is: Did I miss something? If someone knows where changes to iminvloc_sql.reorder_lvl & iminvloc_sql.ord_up_to_lvl are tracked please share. But I have to assume that the history of these values are not currently stored in the database.

So, what tricks can we come up with? In the beginning balance record written to iminvtrx_sql there are a number of fields that go unused. I would be happy if there was some way to write the two values into an available field like comment_2. I could parse this in my stored procedure and generate the numbers for my report.

But there is a trick there that I don't have the toolbox for. Do you 'Flex' wizards ever make changes or additions to internal procedures like the "Beginning Balance Records" utility? Is this approach an option for me?

Or, is this a good candidate for a Trigger? I'm not sure if I like the idea of a trigger like this on a high volume transaction table.

My only other option is to schedule a stored procedure to crunch the numbers and write to the Opening Balance record. Is Exact OK with that sort of an approach?

 

[crystalreporting]

If the reorder level is manually changed, the change is reflected in the inventory audit file (IMINVAUD). This assumes that the audit trail is enabled in the IM package settings. You might be referring to changes to these values that come about via the 'update calculated values' function in which case a trigger or scheduled procedure would probably be your best options.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.srhconsulting.com

 

[JayTheGeek]

BINGO!

Thanks Peter, these values certainly are written there. I thought I had looked at all the likely tables, I don't know how I missed that one!

FYI...

'update calculated values' does not work for our 'pure distribution' company. (We have a long drawn out manual process to fill that gap.)

Macola assumes PO Receiving and Sales/Shipping occur in the same Location. So for a single location company or a collection of autonomous branches, this is a great tool. The calculations performed are extensive. But in a more complex business environment, these calculations break down.

Our Distribution company does 90% PO Receiving to our distribution warehouse. Then we 'In Transit' from Distribution to the other 'selling' locations. 90% of our sales are out of these selling locations.

Thanks again,

Jay