Re: Item with last transaction date

[rob@xxxxxxxxx]

I've used Access and other tools.  If you have the same tables, views and 
joins in Access that you have in DB2, and still refuse to use RI 
constraints and what not like BPCS does then it's not any easier in 
Access.  I've converted stuff from Access to DB2 on the i.  Of course, it 
might have been the Access developer there - an old RPG programmer who 
wouldn't use RI constraints and stuff in Access any more than he would use 
them in DB2.

Most of the "benefits" of alternative tools is because they often 
encourage you to define metadata or to consolidate data to ease querying 
of the data.  Most of this could be resolved by decent joins defined in 
DB2.  For example, if you often find yourself creating queries which join 
IIM, IIC, ECL, ECH, RCM and more then why not create a view that does all 
this and just have the users query that?  The benefits include:
- Much easier for the users
- You can automatically calculate on hand instead of having to calculate 
that in the query
- You can "encourage" them to not to forget about allocated by having an 
on hand - allocated column as available
- You can rename column names into something meaningful.
- Automatically handle active record codes
- No duplication of data wasting space
- Live data instead of duplicated data
- Ease date calculations by converting into real dates instead of some 
number

One view I whipped up quickly for this email (and forgive me if I, too, am 
confused by all the BPCS subtleties) is as follows:
create view rob/OpenOrders as
select 
ech.hord as OrderNumber, 
numtodate(ech.hedte) as DateOrderEntered, 
ech.hcust as CustomerNumber, 
ech.hdtot as OrderTotal, 
ech.hsal as Salespersonnumber, 
ech.hcpo as customerPOnumber, 
ifnull(ecl.lline,0) as orderline, 
ifnull(ecl.lprod,'null') as orderItemnumber, 
ifnull(ecl.lqord,0) as qtyOrdered, 
ifnull(ecl.lqshp,0) as qtyShipped, 
ifnull(ecl.lqord,0) - ifnull(ecl.lqshp,0) as qtyRemaining,
numtodate(ecl.lsdte) as ScheduledShipDate, 
case 
 when ecl.lstat is null then 'null' 
 when ecl.lstat='E' then 'OPEN' 
 when ecl.lstat='I' then 'SOMETHING' 
 when ecl.lstat='P' then 'PICKED' 
 end as LineStatus, 
ifnull(iim.idesc,'blank') as ItemDescription, 
case 
 when iim.iopb is null then 0 
 else iim.iopb + iim.iadj + iim.irct - iim.iiss 
 end as OnHand, 
case 
 when iim.iopb is null then 0 
 else iim.iopb + iim.iadj + iim.irct - iim.iiss - iim.icusa
   - iim.iprda 
 end as ItemQtyAvail 
from ech left outer join ecl on ech.hord = ecl.lord 
 left outer join iim on ecl.lprod = iim.iprod 

Now you can simply query:
select * from openorders

The view can be extended to:
- Add sales person name
- select only active records from each file
- since it is called "openorders" select only open orders

Now you can also easily do
select dateorderentered, ordernumber, customernumber
from openorders
where current date - dateorderentered > 10000
This will give you orders entered over a year ago.  That number is yymmdd 
as in 
20101021 - 20021001 = 8 years, 0 months and 20 days or 80020
or you could also do
select dateorderentered, ordernumber, customernumber
from openorders 
where dateorderentered > current date - 100 days 
to only get orders entered within the last 100 days.

You could also create matching shorter field names to ease legacy tools 
like Query/400.  For example
create view rob/OpenOrders as
select 
ech.hord as OrderNumber for column ordernum, 
numtodate(ech.hedte) as DateOrderEntered for column DtOrdEnt,
...

There's also nothing stopping you from using that view in imbedded sql 
either.

When creating a view I recommend always using the table names and not to 
try using logical file names in your FROM clause.

Rob Berendt