Re: KTF file in Oracle - binary? -- BPCS-L

From Al Macintyre

I now work with BPCS 405 CD /400 & have been involved with IBM midrange for 
several decades.

The "F" after the 007 is used for several purposes, such as whether the data 
is a positive or negative number.  I am accustomed to seeing the last 7 digit 
having the zone value overlaid so that the 7 is not visible as a unique 
character, so there must be several different kinds of packed field protocols.

The way to read these numbers is upper left, just below, top of next column 
to right, just below, and so on until you have gone through the whole thing.

One problem when transferring such numbers between different computer systems 
is that what bits 00 or 7F represent on one computer system may not be what 
they represent on another computer system, such as EBCDIC on DB400 & ASCII on 
much of the rest of the computer world.

I do not know HP/Oracle.  If it does not support this kind of packed decimal 
field in which the data has two digits per byte, then I think the smart move 
is to UNPACK from 00 7F format to 007 before sending data there, so that it 
goes from correct format for DB400 to correct data that is meaningful to any 
computer system, before you send it to some other platform, at which point it 
is correct data that needs no translation.

If you determine that the problem is EBCDIC vs. ASCII, there used to be a 
conversion capability within RPG to handle that, and of course there are many 
conversion tools for data irrespective of programming languages.

You might also take a look at what is offered by UPI ... they have a package 
in which BPCS MRP data is copied to PC for massaging there & returning to the 
end users a variety of reports much more user-friendly than native BPCS.  I 
have not looked at the write-ups recently, so my memory could be flawed, and 
also UPI may have done improved versions since I last toured their web site.

My fingers have done some walking through the web sites of many places that 
serve the BPCS community & I do not recall seeing anything as extensive as 
UPI for MRP enhancements, although there are a lot of products out there from 
a lot of places that could be added to BPCS if budgets permitted.

http://www.unbeatenpathintl.com/services.html

Be sure to check out More Bells & Whistles.

It may be that the report you want already exists & it may be more economical 
for your client to have you install this for them.

I have had occasion to muck with the time frame data in several programs that 
I have copied to a clone then modified.  I am writing this e-mail from home 
without easy access to my work system, but my reccollection is that there is 
a start date, then a series of buckets in which we use 7 days or multiples of 
7 days, so I recognize the desire to have the first week be buckets of 1 day 
each, with some thinking how to handle the weekend.

When Criowe Chizek helped us convert our BPCS in 1998, you had some staff 
that was very helpful to us who understood these nuances.  If the following 
individuals are still on your staff, you might want to try to pick their 
brains.

Mack McCormack (Manufacturing Specialist)
Tom Lukawski (BPCS Business Analyst ... one of several we met)
Randy Merle
Justin Leonard 
Margaret Thrasher
Matthew H. Langfeldt
Brian White

Subj:    KTF file in Oracle - binary?
Date:   08/29/2000 1:25:50 PM Central Daylight Time
From:   mhoffman@crowechizek.com (Mike Hoffman)

I am trying to assist a company on BPCS v6.04 HP/Oracle. We are working on
a custom "Projected Available Report" where the user enters the preferred
time frame code to leverage the bucket days of  demand/supply. The KTF file
uses an array (at least on the AS/400). When running an SQL over the Oracle
database of the KTF file, we don't see values that mean much. After running
PL/SQL over the KTF file, we get values that are represented in the
attached e-mail. Does anybody have experience interpreting how 32,74 = 7
and 32,48 = 1? When we looked at the HEX/DEC on DB2400 it showed it like a
packed decimal with 0 on top, 0 underneath and then 7 up to the right and F
underneath that.

Mike Hoffman
Senior Manufacturing Specialist
Crowe, Chizek & Company, LLP
http://www.crowechizek.com/scg/
phone: 440.460.1318
fax:       440.460.1302

----- Forwarded by Mike Hoffman/CL/CroweChizek/US on 08/29/2000 12:45 PM
-----
                                                                              
                                     
                    rgravens@rumk                                             
                                     
                    en.com               To:     mtardiff@crowe_chizek.com    
                                     
                                         cc:     mhoffman@CroweChizek.Com     
                                     
                    08/29/2000           Subject:                             
                                     
                    12:35 PM                                                  
                                     
 
Mark,

Here's the first record (M) that we understand:

32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,
32,74,

32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,
32,74,32,74,32,74,32,74,

32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74,32,74

This record represents a series of 7s which on the user screen might be
displayed as 007.

The second record (W) that we understand is

32,48,32,48,32,48,32,48,32,48,32,48,32,48,32,74,32,74,32,74,32,32,32,32,32,32,
32,32,

32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,
32,32,32,32,32,32,32,32,

32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32

This record represents
1,1,1,1,1,1,1,7,7,7

My thoughts on this so far are that:

1.  Two bytes (numbers in the sequence) represent a number.  Therefore,
32,74 represent 7 while 32,48 represent 1, and 32,32 represent nothing or
possibly 0.

I agree with this ... 
32 is above the 00 column
74 is above the 7F column
48 is above the 1F column

the 00 7F 1F columns are garbage when viewed from that perspective, because 
the data is supposed to be accepted in the packed decimal format, which 
indicates to me that either PL/SQL on HP/Oracle does not support this kind of 
field, or you have an error in defining the field the same way as it is 
defined on DB400, in whatever lingo is relevant to HP/Oracle.

2.  On the AS400, in DB2, Hex F is used to terminate the current number.
3.  On the AS400, in DB2, the binary was used to represent packed decimal
and each number field was restricted to 3 digits.  Thus the highest number
you needed to be able to represent is 999.
4.  Only integers are allowed

Thanks
Rumpi

I agree with Rumpi's interpretations of the data.

Do you have access to source code of BPCS on HP/Oracle such as thru AS/Set?
You ought to be able to see how SSA does this on their Unix version of BPCS.

Al Macintyre  ���
MIS Manager Green Screen Programmer & Computer Janitor of BPCS 405 CD Rel-02 
running on AS/400 V4R3 http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies
+---
| 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
+---