INV620 by Part Description - Doable?

     [Al Macintyre]
This might be a midrange_L question - The INV620 Inventory Tags program 
currently prints in sequence by a logical over fields wholey contained within 
the ILI file - my users would like me to sequence it by fields contained 
within more than one file & I need to figure out a practical way of doing 
that.  I also want to learn AS/400 techniques that are new to me & efficient 
ways of using the AS/400 data base machine that will stretch my future 
programming horizons.

If I was doing this on BPCS/36, I would simply create an intermediate file 
that has data from both ILI & IIM, then sequence that intermediate file to 
drive the program.  But there are hints that AS/400 efficient alternatives 
can be found within logical JOIN of files, DDS, SQL, or Open QUERY Pseudo 
file access path to grab needed info in right sequence on the CL fly.

Knowing the right terminology & right manual to look this up in is the first 
battle - what I am looking for - many BPCS reports flash on screen bottom 
about 
QUERY selecting various records & building access path
it is getting the records needed for the report whose program treats that 
input as if it was a physical data file - I need to learn how to program by 
that method & I suspect that it might be a solution to my current application 
need

OPNQRYF appears to be the relevant command

I am looking at on-line help & manual DB2/400 SC41-5701.  Is there a more 
introductory level available & what should be the first manual for me to read 
up on this?

The only warehouses we want processed are those with Yes in WFLAG from ZPA 
"PHYSICAL' record from the prompt screen selection of warehouse on which to 
generate tags - I think.  In recent times we have been running all warehouses 
in a particular facility when we do our physical inventory.  Perchance could 
that be combined into OPNQRYF so it only processes the relevant warehouses?

> Subj:  RE: INV620 by Cust - Doable?
>  From:        dslessman@nationalelectrical.com (Dwight Slessman)
 
>  You can join the IIM to the ILI file with the IIM file being primary so 
you 
>  can sequence on the drawing number field (if this is a permanent join). 

JOIN - I looked at several manuals, such as DB2/400 Programming SC41-5701

It seems clear that DDS access to secondary has to be from info in primary, 
so ILI-primary to get item # for IIM-secondary, since IIM does not have 
warehouse location diversity to get at ILI.  However, the killer is that all 
key fields must be found in the primary file ... this is telling me in this 
context everything used for sort sequence purposes has to come from one file, 
so perhaps a JOIN will not do what I want, sequencing the processing of a 
program based on information in two or more files.

In the original user request they wanted inventory tags printed by customer 
name, in which I could get at customer number from our drawing number, so 
three files were in the join concept, but thanks to me talking about what 
might be doable & users discussing what would most help the process, the 
request has changed, now they want inventory tags printed in sequence by 
Warehouse/Location (from ILI file) then by Part Description (IDESC in IIM 
file)

>   Joining on the fly (ie through Query/400 does not restrict primary file 
>  only sequencing).  In Query/400 you would join with a type 2 so you would 
>  receive all of the item masters whether or not an ILI record exists.  

Thanks - I will be creating a Query that does this to evaluate how reliable 
the data entry in the drawing field has been & to demonstrate to power users 
how to use sub-string & concatenation to work with the neccessary sub-field 
info.

> This same thing can be done with a permanent join using DDS or SQL.  

I tentatively hope to take the SQL class this September.

> Good luck.
>  
>  Dwight Slessman
>  National Electrical Carbon Products, Inc.
>  
>  
>  -----Original Message-----
>  From:        MacWheel99@aol.com [Al Macintyre]
>  Subject:     INV620 by Cust - Doable?
>  
>  from Al Macintyre on BPCS 405 CD
>  
>  My users want BPCS inventory tags to print within location by customer 
> & I am telling them that if it is even doable, 
> it is at the fringes of my AS/400 programming know-how. 
> Can we just settle for printing the customer identity on the tag?
>  
>  And I know how to do a query which prints $ value of the inventory by
>  customer it is for - would that be helpful?  No, the problem is that with
>  employee turn-over & customer parts turn-over, 
>  knowing what customer this is for is a big help, 
>  but we also have some big locations with parts grouped by
>  customer that the parts are being made for, 
>  so having the tags pre-sorted
>  that way would streamline the physical process.
>  
>  So what they want is sequence Warehouse-Location-Customer-Item
>  
>  There are common items for which no one customer is relevant.
>  
>  We recently started printing on our shop ticket packets a reference list 
of 
>  all the parts, with current customer orders, that this common in 
production 
>  goes into, to help expedite reuniting & programming that was at the 
>  frontiers of my know-how so far. 

I accomplished that via 2 programs communicating a selected BOM data array, 
using techniques combined from 2 different IBM classes.  This is now working 
great & inspiring fueling more mod requests.

>  It is nice that they have this great confidence in my
>  abilities, stretching my mind beyond what I could do before.
>  
>  Right now our physical tags are sequenced off ILIL07 which is
>  Warehouse-Location-Item & during the printing flow, 
>  INV620 accesses the item master for the item involved.
>  
>  Our end items have the customer number keyed into the begininning of the
>  drawing field of item master & in my last IBM class, on DB/400 I learned 
>  that we can create a joined logical of more than one file, so if I did a 
join of 
>  ILI with IIM, is there a way to do it so I still get all the ILI files 
data 
>  I need as there are many ILI records for each IIM record? --- I would be
>  sequencing by the total drawing field, even though the customer number is
>  only in the first few positions & the folks who keyed it in did not 
believe 
>  in registration.
>  
>  I can handle the sub-stringing to extract valid customer # for RCM alpha
>  representation, but if I get groupings, by what was keyed, I will consider
>  this mission a success.  Is this even doable?
>  
>  I guess I will have to read the manual on joining.

Well I have been reading about joining & am coming to the conclusion that its 
limitations constraints mean that what I want cannot be accomplished this way.
  
>  Al
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---