Provide an ASCII file of all quotes/orders that have the following configurator comments selected:
- 004 - 40x48 Pallet
- 005 - 4-Way Pallet
- 008 - Max Pallet Height
We will take information from the most recent revision number of every configuration.
Create IIUTIL WEB.PALLET.PREF (timeline 30-9):
- SSELECT CFHDR (the key to this file is the configuration#)
- Extract the most recent transaction created for this configuration from CFHDR<8,highest multivalue>. Field 8 contains order numbers and quotes. Extract the associated Revision# from the associated multi-value in CFHDR<9> and Revision Type (1=order; 2=quote) from the associated multi-value in CFHDR<10>. Also extract the CPN from CFHDR<1>
- Read Build# from CFBLDHDR<1> using Configuration#.Revision#
- Read CFBLD and CFBLD.USR records: Key= Configuration#:".":Revision#:".":Build#
- Locate each of the 3 comment numbers above in CFBLD.USR<25>. Extract associated Input1from CFBLD.USR<26>. If at least one comment number was used, place into the ASCII. If none were used, bypass this configuration.
- Build ASCII Line:
- Configuration Nbr:".":Revision Nbr
- If Revision Type is a "1", put the Order # into this column
- If Revision Type is a "2", put the Quote# into this column
- Order or Quote Date (from SOHDR<9> or QUHDR<9>)
- Customer Number (billto customer# from SOHDR<2> or QUHDR<2>)
- Customer Name (using billto customer# from above)
- Part Number from ITMMST<1>
- Item Description: from SODET<12> if order. Put 3 multi-values together after trimming out extra spaces. If quote, from QUDET<12>. Put together 3 sets of 25 characters after trimming extra spaces.
- Quantity Ordered - SODET<3> or QUDET<3>
- 40x48 Pallet: Put a "Y" in this column if you found comment# 004
- 4-WayPallet: Put a "Y" in this column if you found comment# 005
- Max Pallet Hgt: Put the 'height' in this column if you found comment #008. The height would be in Input1.
Line 1 of the Ascii should be column headings.
04/01/14 - additional columns added:
- Export Pallet - comment #006 - has no inputs. If you find this comment#, put a "Y" in this column
- #Packages Per Pallet - comment #007 - has Input1 - if you find this comment#, put the #packages (from input1) into this column
Write to TRANSFERS with a name of "WEB-PALLETPREF"
Step 2:
When complete, please copy this IIUTIL to LIVE.DATA and run it. We would like to get an idea of how many customers have these preferences.
Step 3 (timeline 30-18):
Create another IIUTIL program called WEB.UPDT.PALLET.PREF that will:
- Open the TRANSFERS record from above "WEB.PALLETPREF.xls"
- For each line on the ASCII, update CUSTMST.USR for selected preferences using the customer# from the spreadsheet:
- If 40x48 Pallet = "Y" or 4-Way Pallet=Y, add comment# "004" to CUSTMST.USR<63> and write comment text from COMMENTS.USR<3> to CUSTMST<53>
- If Max Pallet Height # "", add comment# "008" to CUSTMST.USR<63> and write comment text from COMMENTS.USR<3> to CUSTMST<53>. Replace "\" in text with the height from the spreadsheet.
- If Export Pallet=Y, add comment# "006" to CUSTMST.USR<63> and write comment text from COMMENTS.USR<3> to CUSTMST<53>
- We won't be writing #Packages Per Pallet to CUSTMST at this time
- You can use IIUTIL UPDT.ITMMST.USR (IN LIVE.DATA) as an example of writing from a TRANSFERS record to a file
4/3/14 - added logic:
FIND the comment# in CUSTMST<63>:
If comment# not found: Write comment# to USR<63>, INPUT1 to <64>, INPUT2 to <65> and write CUSTMST<53>
- If comment# is found in <63> but it’s a comment with no INPUT1, bypass
- If comment# is found in <63> and it’s a comment with INPUT1 (max pallet height): replace the pallet height in <64,WHR> and overlay CUSTMST<53> with the text including the new pallet height