Re: Largest Inventory Quantity of a Part

All,
In LX there are SQL Functions that can assist in some of the calculations.

Like OPENLOC - UDF-Returns location status (empty or not)
RUNBAL - UDF-Running Balance (like you see on mat sts history or IDF)
SALESAV - UDF-Sales Availability

They will be out there in you library structure as type *SRVPGM

EX: Inv transaction History SQL:
SELECT A0.TPROD, A0.TSEQ, A0.TWHS, A0.TTYPE, A0.TREF, A0.THFAC, A0.TLOCT, A0.TTDTE, A0.TQTY, (RUNBAL(A0.TSEQ,A0.TTYPE,A0.TPROD)), A0.TLOT, A0.THMRB, A0.THCNTR, A0.TCLAS, A0.TRES, A0.TCOM, B0.TTYPE, B0.TDESC FROM ITHL01 A0 LEFT OUTER JOIN ITEL01 B0 ON A0.TTYPE=B0.TTYPE ORDER BY 1, 2 FOR FETCH ONLY OPTIMIZE FOR 40 ROWS


You can use these in an SQL Statement and derive the same results as LX would.
The source should be in your DBUPD8xx/QSQLSRC file.

Also don't forget the Max() and Min() Aggregate in SQL to pull the largest/Smallest numbers

Mark


-----Original Message-----
From: bpcs-l-bounces@xxxxxxxxxxxx [mailto:bpcs-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, October 31, 2011 2:23 PM
To: BPCS ERP System
Subject: Re: [BPCS-L] Largest Inventory Quantity of a Part

If you drop the zero balances it helps:

with t as(SELECT LPROD, LWHS, max(LOPB-LISSU+LADJU+LRCT) as OnHand
-- leave off the location from above as we're trying to
-- find the maximum by product and warehouse
FROM ili
group by lprod, lwhs)
select t.lprod, t.lwhs, lloc, t.OnHand
from t join ili on t.lprod=ili.lprod and t.lwhs=ili.lwhs and
t.OnHand=LOPB-LISSU+LADJU+LRCT
-- find the location that has the same balance as the maximum
-- for the warehouse
where t.OnHand>0 -- skip zero balances
order by lprod, lwhs
Item Whs Location ONHAND
Number
.06POLY48X96 CY CY 14.069
.187 IN 39 39 8.000
*PSH006000 WY WY 427.000
A-73819-1 E4 FI0212 1,000.000
A-73819-1 E4 FK0121 1,000.000
A-73819-1 E4 FK0311 1,000.000
-- note: If these balances were not the same, you'd only see 1 of them.
ACTIVATORD C1 C1 4.000
ACTIVATORD E4 RF1821 6.000
ALPHA1896/15C E4 RB0822 203.000
ALPHA1897C E4 RB0822 39.000
ALPHA1898/15C E4 RI2131 328.000
ALPHA2172 E4 RB1512 137.000

The above is like the two query method. But you're just piping the
results from the one into the next. I forget the technical term -
correlated sub query or some such thing.

Rob Berendt