Substitution examples
SUBSTITUTION=(#17,OC.#2), KEY(NOT(#16,OC.#1)),NFA(SKIP)
Record Entry #17 is substituted with column 2 from the file with alias OC, if #16 does not match column 1 from OC. The substitution will take place at the first value that does not match. If the Expression is not true (for example, there is a match of #16 and OC.#1) then the value is handled normally.
Normally only one or two entries can be made in OC for an expression like this to be useful.
To substitute based on the same record entry ID, consider using TRANSFORM as it is faster and simpler.
TRANSFORM=#17;0:10;1:20;OTHERWISE:50;
If Record Entry 17 is 0, then it is changed to 10. If the value is 1, it is changed to 20, otherwise the value 50 is used.
SUBSTITUTION=(#18,FAM.#3),
KEYS((#3,FAM.#1),(#21,FAM.#2)),NFA(GEN_NEW_SEQNUM)
Record Entry 18 is substituted with column 3 from the file with alias FAM.
The substitution is performed if the Record entry 3 matches an entry in FAMs column 1 and 21 matches column 2 (the same line in the FAM file)
If a match can not be found, a new number is generated for record entry 18 and the FAM file is updated with a new line containing:
Value of #3; value of #21; Sequence number assigned to 18;
SUBSTITUTION=(#25,SYNC.#3), KEYS(NOT(#25,SYNC.#1),
NOT(#9,SYNC.#2)),NFA(SKIP)
Record Entry 25 is substituted with column 3 from the file with alias SYNC. The substitution is performed if Record entry 25 does not match an entry in FAMs column 1 and Record entry 9 does not match column 2 (the same line in the FAM file).
!*****************************************************************************
! DEFINITION OF SUBSTITUTION FILE
!*****************************************************************************
filedef(FAM,H:\PC\PMSIM400\Apv2\Custom\FAMCODES.DAT)
filedef(SYNC,H:\PC\PMSIM400\Apv2\Custom\SYNC.DAT)
filedef(OC,H:\PC\PMSIM400\Apv2\Custom\OC.DAT)
!*****************************************************************************
!* IMPORT FROM FILE WITH MULTIPLE RECORDS *
!* O,R,M/N & H IN THE SAME FILE: *
!* *
!* TOTAL: PMORGExx.DAT *
!* *
!* INCREMENTAL: PMOGI1xx.DAT *
!*****************************************************************************
[IMPORT]
! LAYOUT=MULTI_REC
! TYPE=READ
FILEFORMAT=SEPARATED
ID=#1
DELIMITER=;
! CHARQUOTES=
[O]
! Field Type Default Offset Length R Description
! ----- ------ --------------- ------ ------ - -------------------
SUBSTITUTION=(#17,OC.#2), KEY(NOT(#17,OC.#1)),NFA(SKIP)
#1 ;CHAR ; ;1029 ;1 ; "O" EXRTYP Record type
#2 ;CHAR ; ;9 ;12 ; COHPEG Peg ID
#3 ;CHAR ;NULL:ERROR ;113 ;25 ; COHITM Item number
#4 ;LONG ;NULL:ERROR ;8 ; ; MOHMRP Order number
#7 ;SHORT ; ;134 ; ; IOHPRI External network
priority
#8 ;DATE ; ;9 ; ; MOHDDT MRP order due
date
#9 ;TIME ; ;9 ; ; -"-
-"-
#10 ;DATE ; ;10 ; ; MOHRDT MRP order release
date
#11 ;TIME ; ;10 ; ; -"-
-"-
#12 ;DATE ; ;11 ; ; MOHCDT End item peg
receipt date
#13 ;TIME ; ;11 ; ; -"-
-"-
#14 ;SHORT ;NULL:ERROR ;136 ; ; IOHSTA Shop order status
#14 ;SHORT ;NULL:ERROR ;101 ; ; IOHMCS Memorized COPICS
Status
#15 ;FLOAT ;NULL:ERROR ;13 ; ; ROHPLQ Planned quantity
#16 ;FLOAT ; ;14 ; ; ROHFDQ Finished quantity
#17 ;CHAR ; ;138 ;25 ; COHRON Description
#18 ;CHAR ; ;163 ;10 ; COHSON Shop order number
#19 ;DATE ; ;60 ; ; MOHIST Infinite
scheduled start
#20 ;TIME ; ;60 ; ; -"-
-"-
#21 ;DATE ; ;61 ; ; MOHIED Infinite
scheduled end
#22 ;TIME ; ;61 ; ; -"-
-"-
#23 ;DATE ; ;58 ; ; MOHFST Finite scheduled
start
#23 ;DATE ; ;15 ; ; MOHMST Saved Finite
scheduled start
#24 ;TIME ; ;58 ; ; MOHFST Finite scheduled
start
#24 ;TIME ; ;15 ; ; MOHMST Saved Finite
scheduled start
#25 ;DATE ; ;59 ; ; MOHFED Finite scheduled
end
#25 ;DATE ; ;16 ; ; MOHMFE Saved Finite
scheduled end
#26 ;TIME ; ;59 ; ; MOHFED Finite scheduled
end
#26 ;TIME ; ;16 ; ; MOHMFE Saved Finite
scheduled end
#27 ;SHORT ; ;130 ; ; IOHMLC Low level code
#28 ;SHORT ; ;528 ; ; IXRPGC Pegging code
#30 ;SHORT ; ;13 ; ; IOHPU1 Pull-up code 1 -
too early
#31 ;SHORT ; ;14 ; ; IOHPU2 Pull-up code 2 -
delayed
#32 ;SHORT ; ;128 ; ; IOHEXT Extra code
#33 ;SHORT ; ;56 ; ; IOHORI Origination code
#34 ;SHORT ;NULL:1 ;135 ; ; IOHOCD Order group
#35 ;CHAR ; ;6 ;3 ; COHRTY Routing type
#36 ;LONG ; ;6 ; ; MOHPGS Peg Suffix
#37 ;FLOAT ; ;17 ; ; SOHMFC(1Multiplication
factor 1
#38 ;FLOAT ; ;18 ; ; SOHMFC(2Multiplication
factor 2
#39 ;FLOAT ; ;19 ; ; SOHMFC(3Multiplication
factor 3
#40 ;FLOAT ; ;20 ; ; SOHMFC(4Multiplication
factor 4
#41 ;FLOAT ; ;21 ; ; SOHMFC(5Multiplication
factor 5
#42 ;FLOAT ; ;22 ; ; SOHMFC(6Multiplication
factor 6
#43 ;FLOAT ; ;23 ; ; SOHMFC(7Multiplication
factor 7
#44 ;FLOAT ; ;24 ; ; SOHMFC(8Multiplication
factor 8
#45 ;FLOAT ; ;25 ; ; SOHMFC(9Multiplication
factor 9
#46 ;FLOAT ; ;26 ; ; SOHMFC(1Multiplication
factor 10
#47 ;SHORT ; ;55 ; ; IOHOPR External order
priority
#48 ;DATE ; ;63 ; ; MOHTIM Order timestamp
#49 ;TIME ; ;63 ; ; -"-
-"-
#50 ;SHORT ; ;139 ; ; IOHCGR Coloring group
#51 ;CHAR ; ;173 ;1 ; EOHOSS MRP status
#52 ;CHAR ; ;280 ;1 ; EOHOTY Order type
#57 ;DUMMY ; ; ; ;
[R]
! Field Type Default Offset Length R Description
! ----- ------ --------------- ------ ------ - -------------------
! TRANSFORM=#17;00000000.00:500.0;
SUBSTITUTION=(#18,FAM.#3), KEYS((#3,FAM.#1),(#21,FAM.#2)),NFA(GEN_NEW_SEQNUM)
#1 ;CHAR ; ;1029 ;1 ; "R" EXRTYP Record type
#2 ;CHAR ; ;1071 ;12 ; CXRPEG Peg ID
#3 ;CHAR ;NULL:ERROR ;1030 ;25 ; CXRITM Item number
#4 ;LONG ;NULL:ERROR ;257 ; ; MXRMRP Order number
#5 ;LONG ;NULL:ERROR ;9 ; ; MOPNUM Operation number
#6 ;CHAR ; ;155 ;4 ; MOPSFF Operation number
suffix
#7 ;FLOAT ; ;18 ; ; MOPSUS Setup time
standard
#10 ;FLOAT ;NULL:ERROR ;20 ; ; MOPTIM Operation time
#11 ;FLOAT ; ;23 ; ; MOPMAN Manpower time
#12 ;FLOAT ; ;24 ; ; MOPPRE Preparation time
#13 ;FLOAT ; ;25 ; ; MOPPOT Post operation
time
#14 ;SHORT ; ;66 ; ; MOPTNS Parallel
operation generation code
#15 ;CHAR ; ;196 ;1 ; MOPALO Alternative
operation indicator
#16 ;SHORT ; ;529 ; ; IXROKY Old overlap key
(only used in v.
#17 ;FLOAT ; ;32 ; ; MOPBAS Overlap base
#18 ;CHAR ; ;159 ;12 ; MOPFMF Scheduling group
#19 ;FLOAT ; ;26 ; ; MOPSHF Shrinkage factor
#20 ;FLOAT ;NULL:100.0 ;27 ; ; MOPEFF Efficiency (%)
#21 ;CHAR ;NULL:ERROR ;171 ;12 ; MOPWCF Workcenter number
#22 ;CHAR ; ;183 ;12 ; MOPPMF Planned single
machine number
#23 ;SHORT ;NULL:ERROR ;70 ; ; MOPCOP Operation status
#23 ;SHORT ;NULL:ERROR ;106 ; ; MOPMCS Saved Operation
status
#24 ;CHAR ; ;269 ;12 ; MOPMAF Finite scheduled
machine number
#25 ;FLOAT ; ;28 ; ; MOPCPQ Quantity
completed
#26 ;FLOAT ; ;29 ; ; MOPPLQ Planned quantity
#27 ;FLOAT ; ;30 ; ; MOPACS Actual scrap
#28 ;DATE ; ;11 ; ; MOPLTS Last timestamp
#29 ;TIME ; ;11 ; ; -"-
-"-
#30 ;SHORT ;NULL:ERROR ;77 ; ; MOPIND Operation time
indicator
#31 ;DATE ; ;36 ; ; MOPFOR Forced start
timestamp
#32 ;TIME ; ;36 ; ; -"-
-"-
#33 ;DATE ; ;12 ; ; MOPSTS Setup start
timestamp
#34 ;TIME ; ;12 ; ; -"-
-"-
#35 ;FLOAT ; ;31 ; ; MOPPCT Percent completed
#36 ;DATE ; ;56 ; ; MOPFST Finite scheduled
start
#36 ;DATE ; ;37 ; ; MOPMFS Saved Finite
scheduled start
#37 ;TIME ; ;56 ; ; MOPFST Finite scheduled
start
#37 ;TIME ; ;37 ; ; MOPMFS Saved Finite
scheduled start
#38 ;DATE ; ;57 ; ; MOPFED Finite scheduled
end
#38 ;DATE ; ;38 ; ; MOPMFE Saved Finite
scheduled end
#39 ;TIME ; ;57 ; ; MOPFED Finite scheduled
end
#39 ;TIME ; ;38 ; ; MOPMFE Saved Finite
scheduled end
#40 ;DATE ; ;58 ; ; MOPIST Infinite
scheduled start
#41 ;TIME ; ;58 ; ; -"-
-"-
#42 ;DATE ; ;59 ; ; MOPIED Infinite
scheduled end
#43 ;TIME ; ;59 ; ; -"-
-"-
#44 ;LONG ; ;10 ; ; MOPNON Next operation
number
#45 ;FLOAT ; ;19 ; ; MOPSUF Grouping setup
time
#47 ;FLOAT ; ;21 ; ; MOPRUN Total run time
#48 ;FLOAT ; ;22 ; ; MOPTOT Remaining run
time
#49 ;SHORT ; ;124 ; ; MOPCOD Scheduling
control code
#50 ;SHORT ; ;125 ; ; MOPSTA Scheduling status
#53 ;CHAR ; ;49 ;20 ; MOPDES Operation text
#54 ;FLOAT ; ;50 ; ; MOPFMA Max. operation
load
#55 ;CHAR ; ;6 ;1 ; MOPSKY Splitting key
#56 ;CHAR ;NULL:ERROR ;129 ;1 ; MOPOKY Overlap key
#57 ;SHORT ; ;4 ; ; MOPMIN Manpower
indicator
#58 ;CHAR ; ;9 ;12 ; MOPAMF Actual machine
#59 ;FLOAT ; ;7 ; ; MOPAOP Run time
accumulated
#60 ;FLOAT ; ;8 ; ; MOPASU Setup time
accumulated
#61 ;FLOAT ; ;65 ; ; MOPRSU Remaining setup
time
#62 ;SHORT ; ;67 ; ; MOPFNS Forced number of
splits
#63 ;FLOAT ; ;51 ; ; MOPFMI Min. operation
load
#64 ;CHAR ; ;205 ;5 ; MOPMRF Machine
restriction
#66 ;CHAR ; ;213 ;9 ; MOPFIF Filler
#67 ;DUMMY ; ; ; ;
[M]
! Field Type Default Offset Length R Description
! ----- ------ --------------- ------ ------ - -------------------
SUBSTITUTION=(#25,SYNC.#3), KEYS(NOT(#25,SYNC.#1),NOT(#9,SYNC.#2)),NFA(SKIP)
#1 ;CHAR ; ;1029 ;1 ; "M" EXRTYP Record type
#3 ;CHAR ;NULL:ERROR ;65 ;25 ; CRSPIT Parent item
number
#4 ;LONG ;NULL:ERROR ;14 ; ; MRSPON Parent order
number
#5 ;LONG ; ;16 ; ; MRSSEQ Sequence number
#7 ;LONG ; ;15 ; ; MRSPOP Operation no.
where used
#8 ;CHAR ; ;90 ;25 ; CRSCIT Component item
number
#9 ;LONG ; ;12 ; ; MRSCON Component order
number
#10 ;FLOAT ; ;5 ; ; RRSQTY Quantity of
requirement
#11 ;SHORT ; ;530 ; ; IXRMCC Material const.
code (only used in #12 ;DATE ; ;4 ; ; MRSCAD
Purchased component availability
#13 ;TIME ; ;4 ; ; -"-
-"-
#17 ;LONG ; ;13 ; ; MRSCOP Component
operation number
#18 ;CHAR ;NULL:ERROR ;29 ;1 ; ERSTYP Relation type
#19 ;CHAR ; ;37 ;2 ; FRSOCO Order overlap
code
#20 ;FLOAT ; ;9 ; ; RRSOBA Order overlap
base
#21 ;CHAR ; ;115 ;1 ; ERSPOS Purchased order
status
#22 ;FLOAT ; ;33 ; ; RRSOBV Overlap
calculation base
#23 ;CHAR ; ;138 ;55 ; CRSFIL Filler
#24 ;CHAR ; ;124 ;1 ; ERSOCM Overlap base
control
#25 ;CHAR ; ;43 ;1 ; ERSRST Synchronization
status
#26 ;CHAR ; ;44 ;1 ; ERSSST Shortage status
#27 ;FLOAT ; ;32 ; ; RRSDQT Shortage quantity
#28 ;FLOAT ; ;34 ; ; RRSOQT Original quantity
#29 ;CHAR ; ;137 ;1 ; ERSORI Relationship
origination
#30 ;CHAR ; ;116 ;4 ; GRSPSU Parent Operation
suffix
#31 ;CHAR ; ;120 ;4 ; GRSCSU Component
Operation suffix
#35 ;DUMMY ; ; ; ;
[N]
! Field Type Default Offset Length R Description
! ----- ------ --------------- ------ ------ - -------------------
#1 ;CHAR ; ;1029 ;1 ; "N" EXRTYP Record type
#3 ;CHAR ;NULL:ERROR ;65 ;25 ; CRSPIT Parent item
number
#4 ;LONG ;NULL:ERROR ;14 ; ; MRSPON Parent order
number
#5 ;LONG ; ;16 ; ; MRSSEQ Sequence number
#7 ;LONG ; ;15 ; ; MRSPOP Operation no.
where used
#8 ;CHAR ; ;90 ;25 ; CRSCIT Component item
number
#9 ;LONG ; ;12 ; ; MRSCON Component order
number
#10 ;FLOAT ; ;5 ; ; RRSQTY Quantity of
requirement
#11 ;SHORT ; ;530 ; ; IXRMCC Material const.
code (only used in v. 2.01)
#12 ;DATE ; ;4 ; ; MRSCAD Purchased
component availability date
#13 ;TIME ; ;4 ; ; -"-
-"-
#17 ;LONG ; ;13 ; ; MRSCOP Component
operation number
#18 ;CHAR ;NULL:ERROR ;29 ;1 ; ERSTYP Relation type
#19 ;CHAR ; ;37 ;2 ; FRSOCO Order overlap
code
#20 ;FLOAT ; ;9 ; ; RRSOBA Order overlap
base
#21 ;CHAR ; ;115 ;1 ; ERSPOS Purchased order
status
#22 ;FLOAT ; ;33 ; ; RRSOBV Overlap
calculation base
#23 ;CHAR ; ;138 ;55 ; CRSFIL Filler
#24 ;CHAR ; ;124 ;1 ; ERSOCM Overlap base
control
#25 ;CHAR ; ;43 ;1 ; ERSRST Synchronization
status
#26 ;CHAR ; ;44 ;1 ; ERSSST Shortage status
#27 ;FLOAT ; ;32 ; ; RRSDQT Shortage quantity
#28 ;FLOAT ; ;34 ; ; RRSOQT Original quantity
#29 ;CHAR ; ;137 ;1 ; ERSORI Relationship
origination
#30 ;CHAR ; ;116 ;4 ; GRSPSU Parent Operation
suffix
#31 ;CHAR ; ;120 ;4 ; GRSCSU Component
Operation suffix
#35 ;DUMMY ; ; ; ;
For further examples, refer to the example on using the NOT Key in Substitution.
Example using the NOT Keys in Substitution
You can use NOT keys in the expression part of a substitution. A NOT key evaluates to true if a match could not be found.
SUBSTITUTION=(#5,TEST.#3),KEYS((#5,TEST.#1),NOT(#4,TEST.#2)),NFA(SKIP))
The expression here is:
Record Entry #5 matches with TEST column 1 and the Record Entry does not match column 2.
- XX;3;YY
- ZZ;3;QQ
- ZZ;5;QQ
| Data in | Data out | Note | Substituted? |
|---|---|---|---|
| a;b;c; 3;XX | a;b;c; 3;XX | match found for NOT -Key | No |
| a;b;c; 4;XX | a;b;c; 4;YY | match not found for NOT-Key | Yes |
| a;b;c; 3;ZZ | a;b;c; 3;ZZ | match found for NOT -Key | No |
| a;b;c; 5;ZZ | a;b;c; 5;ZZ | match found for NOT -Key | No |
| a;b;c; 14;ZZ | a;b;c; 5;QQ | match not found for NOT-Key | Yes |