Re: logical view over mult files -- MIDRANGE-L

> From: skanakanui@triad.rr.com (Sam Kanakanui)
>  
>  Hello,
>  
>  I am trying to extract data from a BOM system with preferably a single view
>  of the data.  The data is stored in 4 files. Fin_Product, Interm_Product,
>  Raw_material, and Components.  The linking is as follows.
>  
>  The level 1 SKU in Fin_Product links to one record type in the Components 
to
>  get the level 2 SKU.
>  The level 2 SKU must link to Interm_Product.
>  Then, the level 2 SKU links to a second record type in the Components to 
get
>  the level 3 SKU.
>  The level 3 SKU must link to Raw_Materials.
>  
>  The end result would be to allow a user to view a SKU in Fin_Product and 
see
>  specifications of both level 2 SKU and level 3 SKU from Interm_Product and
>  Raw_Materials, respectively.  I first thought of somehow joining the files
>  with DDS or SQL but don't know if this might be too complex for that.  I
>  know I can write an RPG program to do this with chain to the different
>  levels. However, there are a number of varying requests, i.e. different
>  reports and screen inquiries with different parameters, that are needed.  
It
>  would be nice to have a way of getting to the needed data at the DB level.
>  
>  Does anyone have any suggestions?  Would a canned data mining tool work in
>  this scenario?  Which one?
>  
>  TIA,
>  Sam Kanakanui
>  Borg Systems, Inc.

Are you working with some kind of canned ERP?
Invariably they come with a bunch of programs that do all of what you ask for 
and lots more, and if you have access to the source code you can clone to 
your own variant programs.
RPG can be pretty fast & it is not unusual to have some BOM view where you 
key in an item number & can get at where used or child items & single level 
or multi-level ... different command keys for combination desired.  Find the 
source for this & copy it to a program in which you will be doing new 
combinations & different mixtures of stuff on the screen.
Depending on what the ERP is ... It sure does not sound to me like BPCS which 
i now work with, or MAPICS which I worked with several years ago ... you 
should be on an internet list or forum of other folks who use the same ERP.

I found it useful to create some interim files, with software that runs in 
the evenings to refresh the interim files from the official files.  The way 
BOM is often structured with lists of parent-child combinations & the way we 
have to navigate these trees in RPG is often such that end user queries 
impractical to access the data, but my interim files provided to let them get 
at the information they needed on the fly without having to access the actual 
BOM tree with the tools they using.

ECIF file (which I created) is an indexed file with every unique combination 
of end item, customer it went to, from which of our facilities.  Data in the 
file includes date of last shipment & how many hours to make one part, 
assuming made in quantity, a poor man's capacity planning estimator.

I got the hours via a program that goes down the BOM & accesses all 
manufactured parts connected to the BOM & from the routings gets at time to 
manufacture just one, factoring in BOM quantity actually required.  The 
reason this was important to our people is they wanted a quick way of seeing 
what the hit would be on the factory if we upped quantities of customer 
orders, without having to run the full simulations.

The ECIF file is accurate to our latest engineering, shipping, billing, etc. 
as of 24 hours ago, which is good enough for what they are using it for, and 
what I am using it for.

HCIF file (which I created) has one record for every combination of raw 
material & the end item it is used on.  The index includes the vendor the raw 
material is obtained from, the customer the end item is sold to, and which 
facilities involved are in the index picture.  The data in the file includes 
BOM multiplied down the parent child tree.  Rebuilding & updating this file 
requires a bunch of programs that can be run in the evening.  It then is 
pretty fast to run a variety of other programs, such as one that takes the 
shipping history & from it gets at the quantities of which raw materials must 
have been ordered for use on which end customers, assuming zero scrap & 
waste, so it is ball park numbers.

Alan Shore's approach works when for sure your BOM only goes down a limited 
number of levels.  We have found it expedient to have Queries that create 
intermediate files, and Queries that evaluate stuff from those output files, 
and put the whole string of execution in a CL off of a user menu.

MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
AS/400 Data Manager & Programmer for BPCS 405 CD Rel-02 mixed mode (twinax 
interactive & batch) @ http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies - fax # 812-424-6838

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---