Have you migrated your S36 MAPICS II files to native IBM i DB2 tables but kept the old MAPICS definitions and need to be able to do arithmetic in SQL on the malformed transaction quantity fields?

The IBM MAPICS (Manufacturing, Accounting and Production Information Control Systems) originated in the 1970s as a response to the growing demand for integrated enterprise software in the manufacturing sector. Developed by IBM for its midrange System/34 and later the System/38 and AS/400 platforms, MAPICS was designed to unify manufacturing processes with accounting and inventory control into a single, cohesive application suite. At a time when businesses were transitioning from manual recordkeeping to computerized systems, MAPICS stood out as one of the first modular ERP systems tailored specifically for discrete manufacturers. It enabled companies to streamline operations by automating production scheduling, materials planning, and financial reporting. IBM built MAPICS using RPG and other proprietary technologies, and it quickly became a cornerstone solution for mid-sized manufacturers, setting a foundation that would influence enterprise software architecture for decades to come.

Some of the master files that were used in the application had strangely defined quantity fields like this History Transaction file (IMHIST) definition we came across recently. The separately defined decimal part has the sign defined as “F” positive or “D” negative but it’s not in the bits of the field that you would expect ie it’s defined in the first four bits of the second byte of field TRQT3 and therefore not easy to evaluate in ILE RPG.

F*     Transaction history file for IM Y2K
FIMHIST  IC        300 28AI    17 DISK                      A
IIMHIST  NS
I       1   2 RECCD      Record code 'TR'
I       3   3 ACREC      Active record code
I P     4   5 BATCH      Update batch number
I       6   7 WORKID     Workstation ID
I       8   9 TCODE      Transaction code
I      10  16 ORDNO      Order number
I      17  31 ITNBR      Item number
I      32  32 HOUSE      Warehouse. Always '1'
I      33  40 UPDDT#     CCYYMMDD Date subtracted from 100000000
I      41  44 UPDTM#     HHMM Time subtracted from 10000
I P    45  48 TRQT7      Transaction qty Integer part
I P    49  54 TRAMT      Transaction amount
I P    55  58 TRNDT      Transaction date CYYMMDD
I P    59  62 PRQH7      Previous qty on hand integer part
I P    63  66 PRQO7      Previous qty on order integer part
I P    67  70 PRAL7      Previous allocation qty integer part
I P    71  76 STPCS      Current STP cost
I      77  77 CMPCD      Completion code
I      78  78 REVCD      Reversal code
I P    79  84 PCOST      Previous STP cost
I      85  86 ENTUM      Entry Unit of Measure
I      87  92 VNDNR      Vendor number
I      93 102 REFNO      Reference number
I P   103 104 DCBNO      DCSS batch number
I     105 105 CUSAW      Customer allowance
I P   106 109 NUQH7      New qty on hand integer part
I P   110 113 NUQO7      New qty on order integer part
I P   114 117 NUAL7      New allocation qty integer part
I     118 118 BCHTY      Batch type
I P   119 124 DTLOH      Last date effecting MOHTQ CYYMMDD
I     125 125 NDINV      Non inventory item
I     126 126 SALFG      Sales analysis flag
I     127 128 BLKSQ      Blanket purchase order sequence number
I     129 130 REASN      Reason code
I     131 131 TRWHS      Transfer warehouse
I     132 135 USRSQ      Sequence number
I P   136 139 LPHDT      Last physical cycle count date CYYMMDD
I P   140 145 AVCST      Average unit cost
I P   146 149 WGHT       Weight
I     150 161 HUS001     User area
I P   162 167 PNGAT      Previous weight
I     168 170 HUS002     User area
I     171 180 HLBHNO     Batch reference/batch lot number
I     181 190 LGWNO      Document reference (GRN or Invoice no.)
I     191 197 LLOCN      Location in warehouse
I     198 204 NLLOC      New location
I     205 205 QCFLG      QC control flag
I P   206 207 TRQT3      Transaction qty decimal part 2 bytes
I P   207 207 TRQT1      Transaction qty decimal part 1 byte
I P   208 209 PRQH3      Previous qty on hand decimal part
I P   210 211 PRQO3      Previous qty on order decimal part
I P   212 213 PRAL3      Previous allocation qty decimal part
I P   214 215 NUQH3      New qty on hand decimal part
I P   216 217 NUQO3      New qty on order decimal part
I P   218 219 NUAL3      New allocation qty decimal part
I     220 300 RS001      Reserved

You’ll notice that the integer and decimal part of the quantity fields are stored in different places in the file definition. In System 36 RPGII in order to form these separately specified fields into a useable field with correctly defined integer and decimal parts you would have to define a data structure with some weird double definition for the decimal part as per the code below in order to handle the mis-placed sign designation in the decimal part.

I            DS                                      
I                                        1  103TRQT
I                                        1   70TRQT7
I                                        8  103TRQT3
I                                       10  10 TRQT1

It’s not quite so easy in native RPG as it handles decimal data errors differently. In order to combine the fields to make the actual quantity you have to examine the bit settings of the TRQT1 field in order to ignore the sign nibble.

To perform numeric calculations on the IMHIST file in DB2 SQL you would use the following select statement with the these operators:

SELECT
 ITNBR,
 DEC( TRQT7 + SIGN(TRQT7) * ( 
  CAST(SUBSTR(HEX(SUBSTR(TRQT3, 1, 1)), 1, 1) AS INT) * 0.1 + 
  CAST(SUBSTR(HEX(SUBSTR(TRQT3, 1, 1)), 2, 1) AS INT) * 0.01 + 
  CAST(SUBSTR(HEX(SUBSTR(TRQT3, 2, 1)), 2, 1) AS INT) * 0.001 ) ,10,3) 
 AS TRANSACTION_QTY FROM "M.IMHIST"