Dayton Superior's Avanté System - Intro

  • File Structure & Core Files
    • Software
        Core Software
        HPUX Operating System
        UniData Database (huge history here - loosely called PICK after Dick Pick who originally wrote this - See Wiki for a history on PICK.
        System Builder Tool used to all user interfaces to the database- written in PICK - also called SB.
        Avanté ERP system written in SB by our vendor, Epicor.
        RedBack Backend UniData software for interfacing with the internet.
        DaytonAccess Web site for customer interface into Avanté (also called ePortal).
        StoreFront Subset of DaytonAccess - allows order entry by the customers.
    • System Structure
        All the software runs on HPUX (HP's flavor of UNIX). The Epicor software is stored in different directories and files based on the type of data - programs and DICTs are in one area while the true data is in another. These are further sub-divided into three stages of development (dev, pilot & live). Keeping the programs and DICTs separate from the data allows us to have multiple data accounts share the same programs/DICTs. It also allows us to easily refresh testing environments from the live environment without losing our program changes (though data changes would be lost). We do these "data refreshes" semi-regularly.
        • floprogs
            All programs and DICTs are stored in subdirectories within /av1/igi/floprogs.
            floprogs UNIX Directories
            EPORTAL The "live" account for the DaytonAccess web site. Unlike the Avanté software which is broken into two accounts (one for data and a second for programs/DICTs), EPORTAL accounts contain all the data, programs and DICTs. We have never done a data refresh with any EPORTAL accounts, but since almost all the data used by the web site actually comes from the Avanté accounts, this is not a problem.
            EPORTAL_DEV The development account for the DaytonAccess web site. The "DEV" portion of the name was a poor choice since it actually connects to the Avanté TEST.DATA account, not the DEV.DATA account. All ePortal changes are first done here. Once complete, they are moved to EPORTAL_PILOT.
            EPORTAL_PILOT The PILOT (or middle) account for the DaytonAccess web site. This is where the users verify that changes have been made correctly before rolling them to the EPORTAL (live) account.
            dev Parent UNIX directory containing all the programs and DICTs for the dev realm.
            global A UNIX directory containing various global software packages that are used by Avanté.
            live Parent UNIX directory containing all the programs and DICTs for the live realm.
            standard Parent UNIX directory containing all the untouched programs and DICTs provided by Epicor.
            train Parent UNIX directory containing all the programs and DICTs for the train realm.
        • flodata
            All data (not programs and not DICTs) are stored in subdirectories within /av1/igi/floprogs.
            flodata UNIX Directories
            dev This is a parent UNIX directory. It contains the DEV.DATA and TEST.DATA accounts.
            dldata This is a common dumping ground used by IT to import and export data. All accounts have VOC pointers to this UNIX directory.
            global This comes from Epicor, but it does not appear to be actively used.
            live This is a parent UNIX directory. It contains the LIVE.DATA, LIVE.ODS and ME.DATA accounts.
            standard This is a parent UNIX directory. It simply contains the core Epicor software without any changes.
            train This is a parent UNIX directory. It contains the PILOT.DATA and TRN.DATA accounts.
    • Realms
        • Realms are an Epicor concept. Avanté has 3 realms: live, pilot and test. A realm contains multiple, related accounts. A realm covers all the data and program accounts for a staging level. Thus, the "dev" realm would cover the 2 data accounts: DEV.DATA and TEST.DATA plus the program account. The "live" realm contains both the LIVE.DATA data account, the ME.DATA data account and the LIVE.IIBASE8 programs/DICTs account.
    • Accounts
        • A UniData account is a UNIX directory that contains all of the files within that directory. For example, LIVE.DATA contains all the data for the live environment, while TEST.DATA contains all the data for our test environment. In most PICK locations, this would also include all programs and DICTs. However, Epicor has broken these out into a separate account so that the programs and DICTs can be shared by all the data accounts within a realm.
    • File Types
        • There are three types of files:
          File Types
          hashed Built with an initial modulo (size in 2K blocks). As this file changes, additional blocks are added (or removed) as needed. As files grow, they need to periodically be re-sized for optimal performance. Most files in Avanté are hashed files. These files have a 2 gig limit.
          Dynamic Similar to hashed files, but do not require resizing.
          DIR Files are actually UNIX directories. These are primarily used for programs and other large files.
    • File Structure
        • Each logical file is made up of 2 files: a Data file and a DICT file. The data file contains all the raw data. The DICT file is used to describe the contents of each field and is required for TCL/ECL queries. It is similar to a SCHEMA, but unlike a SCHEMA, you are not required to define a field in order to put data into it (though it is highly recommended).

          PICK uses dynamic files and dynamic data. The data files truly grows and shrinks. You can have multiple pieces of data in a single field (called multi-values/sub-valued). The equivalent to this in SQL or Oracle would be to have multiple tables where PICK only needs one. The PICK style database has a very small footprint, is relatively cheap, and is easy to learn. On UNIX, PICK is case sensitive.
    • DICTs
        • Technically, a file (data file) does not need a DICT, but without a DICT file, there is no way to access the data via a TCL/ECL query (though a BASIC program could still manipulate the data file). The primary purpose of a DICT file is to:
              » Identify what type of data is in each field
              » Provide a way to query the files:

          The two main types of DICT records are:
              » Date fields (points to a field in a file)
              » I-desc (virtual data)

          You can have multiple DICTs that all point to the same raw data. For example, ENTRY.DATE, ENTRY.DT and F4 could all point to field 4.
          You can also have I-descs (short for I-Descriptors) that massage the data. Examples of this could be:
              » Adding multiple fields together
              » Parsing data
              » Concatinating data
              » Translating data from another file
          In some cases, you may wish to perform a complex task within an I-Desc. You can have an I-Desc call a cataloged subroutine. The only trick to this is the 1st argument must be the name of the subroutine, and the value returned by the subroutine must be placed in the first argument.

          There are no data "types" as in many other databases. In PICK, data is just data. Even in raw format, it is reasonably easy to read. Internal data, such as dates and time, are numbers. Other than the standard PICK delimiters (FM, VM, SVM), there should not be any non-printable characters in the data.

          Some data is store in internal format (dates, time, numbers). The DICTs for these types of data also have a mask that is used for display purposes only.
          The most common masks used are:
          Masks
          D2/ Display as a date in MM/DD/YY format.
          MTS Display as time in HH:MM:SS format.
          MD2 Display as a number with 2 decimals.
          MD2, Display as a number with 2 decimals and commas (as needed).
          There are many, many more formats. See the UniData UniBasic manual, under the OCONV section.
    • Record Structure
        • Files are made up of records. Each record has a key that must be unique to the file in question. Based on the file size, PICK will use a hashing algorithm to determine where to read and/or write the record in the file.

          Each record has a key that must be unique to the file in question. Based on the file size, PICK will use a hashing algorithm to determine where to read and/or write the record in the file.

          A record is technically a string of characters separated by special characters. Each field is separated by a Field Mark (often called FM, @FM, attribute mark, @AM, char(254)). Each field can contain multiple values (thus the name multi-valued database - @VM, VM, char(253)). Each multi-value can contain multiple sub-values (@SVM, @SM, char(252)). The PICK database is built to always know what these special characters are and how to use them.

          Below is a rough 3D example of a record with:
              » multi-values in field 3
              » sub-values in field3, multi-value 3
              » 2 multi-valued-associated fields (6 & 7)

          When 2 or more fields are "multi-valued-associated", that means the fields are directly related and must all contain the same number of multi-values. An example of this could be price breaks for a part. One field could contain the price break points, while an associated field could contain the price of the part for each of those price breaks. It is up to the programmer to ensure that the association is maintained.

          The fields in a record are referenced numerically (REC<1>, REC<2>, REC<3>, etc.). For query purposes, DICTs are built for a file to make it easier to understand what type of data is stored in the fields.
    • Standard UniData Files
      • The following files are standard in all UniData accounts or are in some other way worth noting here.
        • VOC
            The VOC file containing all the words that can be used from TCL/ECL/command line. This is the most critical file in an account. Without a VOC in an account, UniData will not function. There are various types of VOCs.
            Key Files
            File Pointers Contains a pointer to both the DATA and DICT part of a file.
            Catalog pointers Points to the object code of a program.
            Verbs Command Words.
            Key words Words used in query statements.
            Paragraphs UniData version of a batch file.
            These always use native UniData syntax.
            Sentences Just a one line paragraph.
            These always use native UniData syntax.
            PROCs Predecessor to paragraphs (ugly!).
            Below is the file pointer to the ITMMST (Item Master) file.
                » Field 1 is an "F" thus we know it is a file pointer
                » Field 2 is the path to the DATA file
                » Field 3 is the path to the DICT file


            Below is the cataloged program pointer for the program SYSS9002.1.
                » Field 1is a "C" thus we know it is a cataloged program pointer
                » Field 2 is the relative path to the actual object code

            File pointers and catalog program pointers can use either relative or absolute UNIX paths to point to their target object.

            Note that when doing a data refresh from LIVE.DATA to one of the other accounts (such as PILOT.DATA or TEST.DATA), the VOC file is always backup up from the destination account first. After the data has been restored, the backed up VOC is then restored so that all file pointers and cataloged program pointers still point to the correct account. Any new files that were not first built in the LIVE.DATA account will be lost.
        • SAVEDLISTS
            When running a select from TCL, it does not display anything (unlike SQL/Oracle). Instead, it simply stores the selected keys in memory and waits for a subsequent command that will act on these keys. A subsequent command could be:
                » further selection
                » listing the data based on the keys
                » saving the keys (not the records) in a temp file called SAVEDLISTS SAVEDLISTS are very useful, esp. when dealing with a complex selection or a selection that takes a while to execute.
            A SAVEDLIST can be pulled up at any time and will only be removed if someone specifically deletes it or overwrites it.
            SAVEDLISTS are unique to each account.
            The TCL prompt will change from ":" to ">" when you have an active select list in memory. To save the keys in a SAVEDLIST, type:
                SAVE.LIST listname
                SL listname
            To load a SAVEDLIST into an active select list, type:
                GET.LIST listname
                GL listname
            To get rid of an unwanted active select, type: CLEARSELECT

            For the most part, SAVEDLISTS are not intended for permanent storage. Reports that use SAVEDLISTS typically overwrite the SAVEDLISTS from the prior run. From the command line, programmers typically use their initials (thus overwriting whatever was in the SAVEDLIST).
        • COMOs (_PH_)
            Occasionally, you may wish to capture what goes to the screen. To capture the screen output, simply type: COMO ON xxx (where xxx is a name you wish to give it). When you are done capturing the screen data, type COMO OFF. You can then view the results by typing: OS.ED _PH_ O_xxx. If not in "up arrow" mode, type "^". Do not display the COMO if not in up arrow mode or you may lock up your session due to escape sequences captured from the SB screens.
            BE SURE TO TURN OFF THE COMO!
            DELETE IT WHEN YOU ARE DONE!
        • _HOLD_
            In general, when you create a print job, the desired destination is a printer. However, on occation, capturing the output to a file is desireable. To cause UniData to pass the TCL/ECL output to a file, type the following:
                SETPTR 0,132,999999,0,0,3,BANNER filename.txtNHEAD,NFMT,BRIEF
                LIST VOC SAMPLE 150 LPTR (or whatever)
                OS.ED _HOLD_ filename.txt
            DELETE IT WHEN YOU ARE DONE!
        • BP
            The BP file (UNIX directory) is the default location for Basic Programs. Epicor has removed all the BP files from the data accounts. For each realm, Epicor has created a shared BP file that resides in the floprogs path.
        • DLDATA
            DLDATA is the file pointer in the VOC file that points to a common UNIX dir. This file/directory is used for importing, exporting and the E-mailer (SYSS9002.1). This file is not a standard Avanté/PICK file. It was created by the Dayton Superior IT staff.
    • UniData vs. PICK Flavor
        The native UniData syntax is different from the original PICK syntax. However, UniData can be flagged to use teh older syntax to be backward compatable.
        When a user first connects to a UniData account, UniData will look in the VOC file for a record called "LOGIN". The LOGIN paragraph (or in our case a PROC) sets environment variables for the user. Avanté and System Builder need to use the older PICK flavor, so among other things, the LOGIN sets the user's environment as follows:
        ECLTYPE="P"
        BASICTYPE="P"
        So long as nothing tells the user's session otherwise, it will always use the older TCL/ECL syntax as well as compile the BASIC programs using the older syntax.

        The LOGIN PROC is identical in all Avanté accounts (though not all UniData accounts). The ePortal accounts are the only user accounts that run in the native UniData flavor. The only differences of note between UniData flavor and PICK flavor are the syntax in selection statements and the way the LOCATE command works in BASIC.
    • Zero vs. Null
        For most purposes, null and zero are treated the same. You can add null to a number and PICK will treat the null as if it were a zero. If you compare a variable vs. zero, nulls will pass this test.
            » IF VAR = 0 will work if VAR contains either zero or null.
            » When doing a numeric test, null passes (PICK treats it as numeric).
            » Where they are not different is if you put the zero in quotes.
            » Performing any math on a variable will always convert a null to a number (null + null = zero)
            » This will simply take experience to learn.
    • True vs. False
        Null, zero, multiple zeros, or @FALSE will all equate to a false condition. Anything else will equate to true. Note that if you are testing a multi-valued field that contains 2 nulls (i.e. null @VM null ) and you test the whole field for null, it will fail because of the value-mark. You would most likely need to test each multi-value separately.
  • Programming & Editing Data
      All programs in Avanté are written in UniBasic (also called PICK Basic or just Basic). This is a high level version of BASIC that folks learned in the old days. Just about the only thing that can't be done with BASIC is graphics.
      • AE/ED Editor Commands
          Most of these commands are also valid for editing data and DICT records.
          Editor Commands
          FI File (save) the current record and exit
          FIB File & compile (must be a BASIC program)
          FIBR File, compile & run (must be a BASIC program)
          EX Exit without saving any changes
          EXK Exit without saving any changes and kill active select list
          P Page down (22 lines)
          L/xxx Locate next line with xxx
          C/xxx/yyy Change 1st occurance of xxx to yyy on the current line
          C/xxx/yyy100 Change 1st occurance of xxx to yyy for the next 100 lines
          C/xxx/yyy100G Change all occurance of xxx to yyy for the next 100 lines
          R Same as C
          999 Go to line 999
          B Go to the bottom
          U999 Go up 999 lines
          ^ Toggles up-arrow mode - while in up-arrow mode, you can see esc sequences
          EV Exploded Value - pushes the line of code into another editor session & converts @VMs to @FMs, @SVMs to @VMs
          ? Shows info about the current record in the editor
          HELP Help (duh)
          H Help
          D Delete the current line (fine in a program, dangerous in a data record)
          D999 Delete 999 lines beginning with the current line
          I Insert after the current line
          BI Insert before the current line
          FD File Delete (delete the current record from the file)
          Note that the default editor is the AE editor. Originally, the ED editor pulls up data is a UNIX vi-like editor. Bruce and John loath the UniData ED editor, and come from a history where ED worked like the (new) AE editor. Therefore, Bruce/John has change the VOC item for ED to actually run the AE editor.
          Larry uses a full-screen editor - speak to him about that.
      • Windows Editors for Basic Programs
          As long as you have mapped your home directory on the UNIX box to your PC, you can use many editors available in Windows to change data in UNIX directories (typically programs but not PICK files). Do NOT use NotePad. It uses a between each line, but UNIX only uses . SlickEdit, TextPad, and many others will work well.
      • Compiling Programs
          From TCL, type:
          BASIC filename programname (where filename is usually BP)
              or
          BASIC filename programname -D (builds a debugging map)
              or
          From within the program in the editor; FIB

          If the program compiles, it will create the object code in the same file (usually BP). The object code will have the same name as the source code with an underscore at the beginning of the name. Thus, if the source code is called XXXX, the object code would be _XXXX.
      • Cataloging Programs
          Cataloging a program makes it accessable to other processes. This includes being able to call them from:
              » Other programs
              » I-Desc
              » Run directly from the command line (assuming there are no aurguments)

          There are three ways to catalog a program:
          Types of Cataloging
          Types Description Syntax
          Globally Puts a copy of the object code in the CTLGTB file (Global Catalog file - accessible from any UniData account - should be used very rarely.
          		 CATALOG filename program
          Locally Puts a copy of the object code in the CTLG file (unique to each UniData account)
          Puts a cataloged pointer in the VOC.
          This should never be used.           		CATALOG filename program LOCAL
          Directly Simply puts a cataloged pointer in the VOC.
          This is the expected method.
          Since the VOC pointer points directly to the object code, once a program has been cataloged directly, it never needs to be re-cataloged.           		CATALOG filename program DIRECT
          If the program has already been cataloged, UniData will ask if you as sure. To avoid this prompt, append the word "FORCE" to the catalog command.
          All subroutines must be cataloged.
          Stand alone programs do not need to be cataloged - to run these, type: & & RUN filename programname

          To remove a cataloged program, type:
              DELETE.CATALOG filename programname
          Since a program could have been cataloged both globally, locally and directly, repeat the above command until UniData states the program is not cataloged.
  • Triggers & Indexes
      Like many other databases, UniData has both triggers and indexes. Generally speaking, all the indexes and triggers that are needed in Avanté have already been created. New indexes and triggers should always be discussed with the Avanté team before implamenting.
      • Triggers
          UniData has two types of triggers: update and delete.
          If a file has an update trigger, every time a record is written to that file, UniData will run the trigger subroutine associated with that file.
          If a file has a delete trigger, that subroutine is run every time a record in the file is deleted.
          All triggers must be globally cataloged (the exception to the rule). This caused a lot of grief when making changes to an existing program (changes in TEST would hit PILOT and LIVE). To get around this, we would write a parent trigger program (globally cataloged) that did nothing but call a 2nd directly cataloged subroutine that did all the real work.
          When we upgraded to Avanté 9.6, we totally redesigned how triggers worked. Instead of each trigger having a 2nd program, we abandoned the parent triggers and went with just two new parent triggers (TRGS9999.1 for updates and TRGS9998.1 for deletes). These two globally cataloged programs read a control file and from it determine what the 2nd subroutine should be.
      • Indexes
          By the nature of how PICK stores data, the key is always indexed in a hashed or dynamic file. You gain instant access to a record when you pull it up by its key. However, if a search needs to be done and the file is large, these searches can take more time than we are willing to accept. Indexes (also called alternate indexes) can speed things up immensely, but they come at a cost. For each alternate index added to a file, there are that many more I/Os done when updating a file. In addition, the index file (a third file at the UNIX level that goes along with the DATA and DICT files) can take up a lot of disk space. Therefore, it is vital to carefully consider which fields are worthy of indexing. Once the indexes are turned on, no additional changes need to be made to the system. All writes to a PICK file will automatically update the indexes as needed. In addition, SELECT statements will automatically take advantage of any indexes.
  • ECL/TCL Commands   (Updated)
      ECL (often called TCL) is the command line language generally used for retrieving data from files. UniData is backward compatible to the original syntax for the old PICK/Reality systems. By default, UniData does not use this syntax. However, SB/Avanté use the old style syntax, so the LOGIN paragraph sets up the Avanté accounts to run in traditional PICK mode.

      If you start a TCL command with a lower case verb, that signals UniData to use the native UniData syntax even if you are in PICK mode. However, PICK is case sensative, so you need to ensure the remaining words are in uppercase.

      UniData Commands Reference (ECL/TCL commands in PDF)

      SELECT
      Unlike SQL, the "SELECT" verb does not display any data. Instead, is collects the keys in memory that meet the selection criteria and awaits a subsequent command to act upon these keys. A subsequent command could be to LIST (display) the data, further selection criteria, or saving thE keys to a SAVEDLIST. The syntax is:
          SELECT filename WITH criteria1 AND WITH criteria2 BY field1 BY.DSND field2
      where the only required word is the filename. You can use as much criteria as you wish so long as they line up with valid DICTs for filename. Sorting the keys is done using the "BY" or "BY.DSND" key words.
      The criteria syntax in PICK mode is:
          WITH FIELD1 = "desiredValue"
      To use a wildcard in PICK mode, use the square brackets. For example:
          SELECT ITMMST WITH PART.NBR = "123]" AND WITH STATUS = "1"
      The native UniData syntax is slightly different. The equals sign ("=") can only be used for an exact match (i.e. no wildcards). To use a wildcard, you use the LIKE keyword and elipses ("...") for wildcards. Therefore, the native UniData syntax for the select statement above would be:
          select ITMMST WITH PART.NBR LIKE "123..." AND WITH STATUS = "1"

      SSELECT
      Same as SELECT except is adds a final sorting criteria by the key.

      LIST
      To display the results of a SELECT, or to simply show the data on-the-fly, use the "LIST" verb. The LIST verb uses the same selection and sort criteria as the SELECT verb (see above), but the results will also be displayed to the screen. Note that you must specify which fields to display. Fields used in the selection criteria are not automatically displayed. For example, to simply show all the Item Master records that have the work "BOLT" in the description, you could type:
          LIST ITMMST WITH DESCRIPTION = "[BOLT]"
      This will only show the keys for the desired items. To see the descriptions as well, you would need to type:
          LIST ITMMST WITH DESCRIOPTION = "[BOLT]" DESCRIPTION

      SORT
      Same as LIST except is adds a final sorting criteria by the key.

      TOTAL
      To total a field, simply use teh keyword "TOTAL" before the field to be displayed.
          LIST INVWHS WITH QOH > "0" CPN TOTAL QOH

      ID.SUPP
      To suppress the key from being seen as the first column in a LIST/SORT command, simply append the ID.SUPP key word.
          LIST INVWHS WITH QOH > "0" CPN TOTAL QOH ID.SUPP

      DET.SUPP
      To get a summary report (don't show each record, just the totals), use the DET.SUPP keyword along with the ID.SUPP and TOTAL key words.
          LIST INVWHS WITH QOH > "0" TOTAL QOH ID.SUPP DET.SUPP

      COPY (PICK Syntax)
      To copy a record (or records) from one file to another, select the keys from the first file.
      With an active select list, issue the following 2 piece sommand:
          COPY file1 {options}[RTN]
              to: (file2 [RTN]

      To copy a single record to another file and assign it a new key, type:
          COPY file1 key1 {options}[RTN]
              to: (file2 key2[RTN]

      To copy a single record to the same file and assign it a new key, type:
          COPY file1 key1 {options}[RTN]
              to: key2[RTN]

      In all cases, there are optional options. These options are:
          (O - Overwriting
          (D - Deleting the original
          (OD - Overwriting and delting

      COPY (UniData Syntax)
      The native UniData syntax requires more typing, but is more intuative. To copy multiple records, first collect them in a select list, they type:
          COPY FROM file1 TO file2 {options}
      where options may be the key words:
          "OVERWRITING" and/or
          "DELETING"
  • Stack Commands
      Command Description
      .A# text Append text to command # (defaults to command 1)
      .C#/s1/s2 Change s1 to s2 in command # (defaults to command 1)
      .D# Delete command # (defaults to line 1)
      .L Show the command stack
      .R# Recall line # (defaults to command 1)
      .U# Converts line # to all upper case (defaults to command 1)
      .X# Execute line # from teh stack (defaults to command 1)
      .? Stack help
  • In-House Utilities
      Below are some of the more common utilities.
      Command Description
      MERGE filename key Blends DICT and raw data - it will also works with active a select list
      CMPF filename1 key1 filename2 key2 Compares 2 records in two files
      LOCATE key Search through all files for a specific key
      ESEARCH filename Searches all records for one or more strings
      BAC.SEARCH A more selective version of ESEARCH
      LISTDICT filename Lists all the DICTs for filename
      LISTDICT2 filename Lists all the DICTs for filename ignoring all SB items - just shows pure DICTs
      DATE2 date Converts internal date to external and vice versa
      TIME2 time Converts internal time to external and vice versa
      VOC.POINTER.UPDATE Searches all VOCs for a specific string and replaces it with another - normally used during a conversion process to change pointers from one realm to another
      it creates a report of what will be changed and allows the user to exit w/o saving changes
      FIND.USER This prompts for a last name (use the first 3-5 letters) and a first name (use the 1st letter). It will then look for all users in teh DMSECURITY file that match that name.
      Note that in almost all cases, a user's login is the last 5 characters of their last name + the first and middle initial.
      The exceptions to this are barcode users (last character is a "2"), people with a last name less than 5 characters, and/or people with no middle initial.
      This is just a simple UniData Paragraph.
      FIND.USER2 This program prompts for a primary search (first few letters of the last name), seconary search (first initial), and the user's full name. It will then do an exhaustive search through key files looking for this info. If any hits are found, the data will be sent to teh user in an E-mail. This E-mail will indicaet where the matches were found. If there is a standard process to maintain this data (vs. the editor), the process will be indicated. If the file is owned by a super user, the E-mail should be trimmed down to indicate the parts they own, and sent to them to be cleaned up. If no matches are found, no E-mail is sent.
      FIND.USER.FEEDER This program in meant to be used in conjuction with a weekly HelpSTAR ticket of all terminated users. From the parent HelpSTAR ticket, highlight ALL lines containing terminaed employees plus an extra line at the bottom. While highlighted, type [ctl]C to copy this data. Run the FIND.USER.FEEDER progam. At the first prompt, paste the data in the buffer into the input. The program will then parse out this data ans show a list of the desired users.
      make sure the last user in teh HelpSTAR ticket is on the list. If they are not, copy the names from the ticket again, grabbing an extra blank line or two and try again.
      Once you have a good list, continue on. The program will simply pass each of these users into the FIND.USER2 program (see above).
  • BASIC Programs   (Updated)
      There are many commands in BASIC, but here are key commands & concepts:
      • Dynamic vs. Dimensioned array
          Just like the records are stored as dynamic arrays, so too can BASIC deal with this data. Records are usually read in as dynamic arrays. This makes programming very easy, however, the bigger the records, the slower the program will run (remember, a record is just a string of data/single variable, thus the further through it you have to search, the slower it will take). Alternatively, you can read in records as dimensioned arrays. This takes more overhead to set up, but can actually run faster, esp. if the program is dealing with big records. However, dynamic files are almost always used. In addition, any variable in a PICK program is dynamic. Learn how to use them.

          Remember that a PICK record is best though or as simply a long string of characters with special delimiters. The fastest way to read a record is with the READ command. This loads a record into a single variable. However, to access a field, PICK must scan through the variable using the delimiters until it gets to the desired piece of data. This is not a big deal when dealing with small data and low field numbers, but the more data there is and/or the higher the field numbers, the more time must be spent scanning through the variable.
          The alternative is to use dimensioned arrays instead of dynamic arrays. Prior to reading or writing a dimensioned array, the program must first demension/define the record. This is done by stating:
              DIM variblename(##) where ## is the max number of fields
          Instead of a single variable, this will declair ## seperate variables. When a record is MATREAD from disk, PICK will load each field into the corresponding array. Thus, when wanting to access field 100, you could access it direct instead of having to parse through a dynamic string.
          Dimensioned arrays are rarely used due to the extra overhead, but if used correctly, they can be faster.
      • Records, Fields, Multi-values & Sub-values
        • Open a file
            To open a file, use the following syntax:
            OPEN "ITMMST" TO ITMMST ELSE STOP "UNABLE TO OPEN ITMMST"
                or
            OPEN "DICT", "ITMMST" TO DICT.ITMMST ELSE STOP "UNABLE TO OPEN DICT ITMMST"

            Note that with the exception of a trigger program, a program does not need to close a file. It will automatically close when the subroutine terminates.
        • READ or MATREAD a record
            Dynamic read a record
            READ DITMMST FROM ITMMST, KITMMST THEN
                do something
            ELSE
                do something else
            END

            Dimensioned read a record (rarely used in Avanté)
            MATREAD DITMMST FROM ITMMST, KITMMST THEN
                do something
            ELSE
                do something else
            END

            Read a single field
            READV DITMMST FROM ITMMST, KITMMST, filed# THEN
                do something
            ELSE
                do something else
            END

            Dynamic read and lock a record
            READU DITMMST FROM ITMMST, KITMMST THEN
                do something
            ELSE
                do something else
            END

            A record was read with the lock option, it will be un-locked via
            »   Writing the record back to disk or
            »   via the RELEASE command
        • Field, Multi-value & Sub-value Access
            Fields, multi-values, and sub-values are all accessed by their location in the variable.
            To access field 6 in a varible called RECORD, the reference would be RECORD<6>.
            Thus, a line of code could look like:
                SSN = RECORD<6>

            If field 8 stores multiple phone numbers, a reference to the 3rd phone might look like:
                THIRD.PHONE = RECORD<8,3>

            Likewise, if field 25 was made up of multi-values which were in turn further divided into sub-values, we could access field 25, multi-value 6, sub-value 2 with:
                XXX = RECORD<25,6,2>

            If a record has 2 fields that contain related multi-valued fields, then both fields should contain the same number of multi-values. Similarly, if a multi-value is sub-divided into sub-values, then these multi-values should contain the same number of sub-values.
      • Loops
          There are several types of loops in PICK
          Loops
          FOR/NEXT FOR XX1 = 1 TO 10
              your code
          NEXT XX1
          LOOP/WHILE/REPEAT LOOP
          WHILE condition
              your code
          REPEAT
          LOOP/UNTIL/REPEAT LOOP
          UNTIL condition
              your code
          REPEAT
      • INDEX vs. LOCATE
          When looking for a string in a variable, there are two main commands: INDEX and LOCATE.
          INDEX will look for a specific string anywhere in the variable and return the starting possition.
          LOCATE tests for a specific string in a field, multi-value or sub-value (depending on the syntax). The entire value of the field (multi-value or sub-value) must match the desired string.
              POS = INDEX("ABCDE", "BC", 1)
          would search the entire first aurgument ("ABCDE") for the 1st occurance of "BC". In this case, POS would be assigned the value of 2.

              POS = LOCATE(RECORD, "BC", 1)     -or-     LOCATE "BC" IN RECORD SETTING POS
          would search the entire variable called RECORD for a field that equals "BC".

          Note that LOCATE works differently between the PICK flavor and the native UniData flavor. If we had a dynamic array of: REC = "AAA^BBB^CCC^DDD" (where ^ indicates field marks), the PICK syntax would be:
              LOCATE "CCC" IN REC SETTING POS (and would assign POS to 3)
          The native UniData syntax would be
              LOCATE "CCC" IN REC<1> SETTING POS

          To force the compiler to compile in a specific flavor, you can use the comoiler directive:
              $BASICTYPE = "P" (or "U")
          This is rarely used.
      • Sample code
          The following sample program will close all open orders that are older than 6 months. (not very realistic, but it is simple)

          OPEN "SOHDR" TO SOHDR ELSE STOP "UNABLE TO OPEN SOHDR"
          SIX.MONTHS.AGO = DATE() -180
          SELECT SOHDR
          LOOP
          WHILE READNEXT SOHDR.KEY
                READU DSOHDR FROM SOHDR, SOHDR.KEY THEN
                      IF (DSOHDR<41> = "O") AND (DSOHDR<9> < SIX.MONTHS.AGO) THEN
                            DSOHDR<41> = "C"
                            WRITE DSOHDR ON SOHDR, SOHDR.KEY
                      END ELSE
                            RELEASE SOHDR, SOHDR.KEY
                      END
                END
          REPEAT
      • Important Subroutines
        • SYSS9002.1
            This is the standard E-mailer used throughout both Avanté and ePortal. It has gone through several incarnations, but today, the only "flavors" that should be used are:
          • 1 - open a sequncial file
              If the program in question needs to build an E-mail attachment, use the following syntax to create and open a sequencial file:
              CALL SYSS9002.1(1, OUTFILE, THIS.PROGRAM)
               where OUTFILE is the handle to the file and THIS.PROGRAM is the name of the program - automatically assigned if you use:
              $INCLUDE EPORTAL.BP JRI.COMMON
          • 5 - does everything else
              This way of calling the E-mailer does all kinds of things - it is often referred to as "mode 5". Instead of passing a "5" as the first aurgument, it instead passes a dynamic array (a variable). This is fully detailed at Standardized E-mailer. This is a vitally important program. It is our standard form of notification throughout the system.

              Note that there are some older programs that do not use version 5 to its fullest extent, or an entirely different way of sending E-mail. These programs should be updated as we get to them to comply with the standard E-mailer.
      • UniBasic Commands Reference (PDF)
  • System Builder
      System Builder is 4GL tool written in PICK and first developed in South Africa. By using this tool, a programmer is able to create files, DICTs, input screens, lookups, programs, reports, etc. Avanté is written using SB and all enhancements to Avanté should be done using SB.

      SB uses DICT files to store controls that are unique to SB. This includes proprietary DICT items as well as screens.
  • Avanté   (Updated)
      Avanté is the name of the Dayton Superior ERP software, which was written by Epicor. It runs in both text and GUI mode (though programming is done in the text-base mode).
      »   SYS3005 - Generic process used to maintain many of the control records in Avanté.
      »   SYS9040 - In-house process with additional controls over specific control records in Avanté.
  • PRC (PRC5)   (Updated)
      PRC5 is a special account that runs the PRC software. PRC is a 3rd party package that Epicor has plugged into Avanté to track changes.

      Making Changes to Avanté
      »   No changes are to be made in Avanté without a HelpSTAR ticket!
      »   All changes should initially be done in the DEV.DATA account in the test realm.
      »   All changes must have a PRC project to track the changes. DEV.DATA has sparse, Epicor provided data, and thus is not good for testing.
      »   Once a change has been made in DEV.DATA (and thus tracked via PRC), log to the TEST.DATA account do the actual testing.
      »   Once all changes have been verified to the programmer's satisfaction, the PRC project needs to be bumped via SRC.
      »   Log to the PRC5 account and roll the changes from the test realm to the pilot realm.
      »   Log to the PILOT.DATA account (which will re-compile and catalog your programs) and verify the changes. Manually build any control data as needed. Test as needed.
      »   The user should then be informed that the changes have been rolled to PILOT and is ready for them to test.
      »   Once you get confirmation from the user via E-mail, save the confirmation E-mail as an attachment in the HelpSTAR ticket.
      »   Log to the PRC account and roll the changes from PILOT to LIVE.
      »   Log to the LIVE.DATA account to re-compile/catalog any programs, and inform the user that the changes have been moved to LIVE. Test as needed.
      »   Inform the user that the change has been made.
      »   Finally, close out the HelpSTAR ticket.