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"