Re: Largest Inventory Quantity of a Part

Very COOL, Norman!!



--- On Mon, 10/31/11, Norman.Boyd@xxxxxxxxxxxxxxxxx <Norman.Boyd@xxxxxxxxxxxxxxxxx> wrote:


From: Norman.Boyd@xxxxxxxxxxxxxxxxx <Norman.Boyd@xxxxxxxxxxxxxxxxx>
Subject: Re: [BPCS-L] Largest Inventory Quantity of a Part
To: "BPCS ERP System" <bpcs-l@xxxxxxxxxxxx>
Date: Monday, October 31, 2011, 11:17 AM


You will have to do this with two queries and a file that the first query
creates.

Query 1 - I usually do something like MAXBAL01 (Maximum balance
#1)...since there will be two queries involved
Specify file selections - ILI
Define result fields - calculate quantity on hand (Field QOH for example)
Select and sequence fields - LPROD and QOH
Select records - Active records with a positive balance - QOH GT 0
Select sort fields - LPROD
Select report summary functions - 4=Maximum on QOH
Define report breaks - LPROD
Select output type and output form - 3=Database file, 2=Summary only then
specify a file in your user library that is not yet in use (I usually use
the same name of the query MAXBAL01) and 2=Replace file so whenever you
run this in the future you are working with the NEW data
Save and execute

Query 2 - ILI and MAXBAL01, join 1, with T01.LPROD = T02.LPROD (name with
one MAXBAL02)
Define result fields - again, calculate the quantity on hand (QOH again if
you wish)
Select and sequence fields - whatever fields you are looking for on your
finished report
Select records - here you will specify active records AND T02QOH05 EQ QOH
(this will grab the unique location or locations-should there be multiple
with the largest QOH balance)
finish out your query as you need.

I get very creative using query in this manner and you'd be surprised the
complex data that can be gathered IF you think through the process.  Let
me know if this helped.

Norman K. Boyd
MIS Administrator
Thermal Technology Corporation of America



From:   "Bailey, Dick" <Dick.Bailey@xxxxxxxx>
To:     "bpcs-l@xxxxxxxxxxxx" <bpcs-l@xxxxxxxxxxxx>
Date:   10/31/2011 11:40 AM
Subject:        [BPCS-L] Largest Inventory Quantity of a Part
Sent by:        bpcs-l-bounces@xxxxxxxxxxxx




There is a need here to identify the warehouse location of each part that
contains the largest quantity of that part.

Using AS400 IBM Query Language.

Anyone have a suggestion as to how?

We are on BPCS LX.

Dick Bailey
MCFA, Inc.