StoreFront Controls

When StoreFront first came on line (late 2011), there was a lot of info that needed to be mass loaded. This data was broken up into 5 Excel worksheets which in turn were saved as 5 txt files to load into Avante. These files were saved in the user's UNIX home dir (O: drive), then loaded into Avante via SYS9097.

Prior to actually updating the data in Avante, all 5 files are tested to ensure that the data is 100% acceptable. If the data is not 100% clean, the process terminates with no updates.

Rather than build a bunch of data entry screens, it was decided that we would continue to maintain this data via the txt files. Each time the data was loaded, the prior data in Avante () would be cleared out and then reloaded based on the 5 txt files. Since there was no way to maintain the data in Avante, this was considered acceptable.

However, the 5-file import has out-lived it's usefulness. We need to be able to maintain this data directly in Avante, but still be able to do mass updates in rare situations.

The 5 txt files are all inter-related and must be loaded at the same time. This is rather over-kill when you are making a minor change, but it worked well when we first went LIVE.

The 5 files are named as follows and update the following Avante data:

• AvantePrivateLabelMaster.txt

  • This file is used to load Private Label info. Each row contains the Part Description (ignored), Part #, followed by National Customer #s. Rather than storing each customer #, we only need to store the National Customer # and the program will populate all the "child" customers under the National #. If none are found, then just that one customer will be considered.

    Those customer numbers are stored in ITMXRF F8.

• StoreFrontSalesGroups.txt

  • At this time, the codes are defined to line up with the physical catalog we offer.

    This data is initially stored in SYSTBL with a parent key of SF.SALES.GROUPS. Other txt files will use these codes later (see below).

• StoreFrontCatCodes.txt
  • 
    

    At this time, the codes are defined to line up with the physical catalog we offer.

    Note that the 3rd column must contain one of the values from StoreFrontSalesGroups.txt (see above). A Catalog Code may have additional Sales Groups.

    This data is initially stored in SYSTBL with a parent key of SF.CAT.CODES. No other files use this, but based on the links between customers and parts determines the "Catalogs" shown in StoreFront.

• StoreFrontCustomers.txt
  • 
    

    This file is used to link customers to one or more StoreFront Sales Groups and thus StoreFront Catalogs.

    This data (columns 3+) get loaded into CUSTMST.USR F43 (SF.SALES.GROUPS). For a customer to be able to use StoreFront, they must have at least 1 SF Sales Group, but they may have as many as we wish (limited only by the number of SF.SALES.GROUP values in SYSTBL).

• StoreFrontParts.txt
  • 
    

    This file is used to:
    1) Link parts to 1 or more StoreFront Sales Groups and thus StoreFront Catalogs
    2) Brand
    3) Order Qty Factor
    4) Minimum Order Qty
    

    The data gets loaded to ITMMST.USR as follows:

    Field #	Field Name	Note
    82	MIN.ORD.QTY	xxx
    86	ORD.QTY.FACTOR	xxx
    114	SF.SALABLE	Hard coded to "1" (flags part is available in StoreFront)
    116	SF.SALES.GROUPS	Multi-valued list of SF.SALES.GROUPS
    117	BRAND	Verified vs. SYSTBL BRAND

At one point (July 2012), it was found that Granger (EDI customer) was also using Private Label (ITMXRF F8). This became a problem because the prior data from the 5 txt files was cleared out, thus causing the Granger Private Lable data to be lost and not rebuilt. The solution for this was to not clear out the Private Label info as part of the 5-file import.

As we have grown, the txt import has more or less out-lived its usefulness. The need to update the data on a part by part or customer by customer situation has outpaced the method of updating via the 5-file import. Thus, in mid 2015, the ability to update all the data directly in Avante was introduced.

The driving force behind all of this is still the SF.SALES.GROUPS and the dependant SF.SALES.GROUPS. These are now able to be maintained via sub-screens launched from SYS9040.

• SF.SALES.GROUPS

  • This screen is used to build the SF.SALES.GROUPS.

    The only "hook" to this screen is if you remove a child code, when you hit F2 to save it, there is logic that will seach for the removed code in:
        1) CUSTMST.USR
        2) ITMMST.USR
        3) SYSTBL SF.CAT.CODES
    If the removed code is found to still exist in one of these locations, a warning is given and the update fails (you cannot remove a code that is still used). The offending data must be updated before the unwanted SF.SALES.CODE may be removed.

    Be sure to use SYS3005 SYS9040 to ensure it cannot be pulled up in SYS3005 and that it works correctly in SYS9040.

• SF.CAT.CODES

  • This screen is used to build the SF.CAT.CODES.

    Note that it is dependant on the SF.SALES.GROUPS codes (see above).

    Be sure to use SYS3005 SYS9040 to ensure it cannot be pulled up in SYS3005 and that it works correctly in SYS9040.

• Private Label (BOM3005)

  • This is not unique to StoreFront!

    This screen is has been altered quite a bit to work the way we want alternate parts and Private Labels to work. I am not going to try to explain the stupid way it was intended to be used!

    The "Reference Nbr" and "Base Item Nbr" should match. Do not ever change the "Ref Description" - this is the UPC code.

    To make a part Private Label, click the F6 key to link specific customers to the part.

    Simply enter the company/customer# into the list. This will restrict the part to only be available to those customers in the list. This is true for both StoreFront and Avante.

• CUSTMST (ARM3000)

  • Launch ARM3000 (Customer Maintenance), enter the desired customer, then go to User Data.

    At the bottom of the screen, enter the SF.SALES.GROUPS that should be tied to this customer. This will be used in StoreFront to pull up catalogs that the customer may access to make purchases in StoreFront.

• ITMMST (BOM3000)

  • Launch BOM3000 (Item Maintenance), enter the desired part #, then go to User Data.
    Click F5 for StoreFront data
    Enter the various StoreFront related data. Note that "Show On Storefront" must be a "Y" for the part to sppear in Storefront (though other factors could exclude it).

• Mass Export (SYS9097)

  • There could be times when we need to make a mass change and/or load of data into the key files. Examples of this are 1) a re-design of the catalogs (SF.SALE.GROUPS and SF,CAT.CODES) or 2) loading parts from an aquired company.

    To accomplish this, a new button has been added to SYS9097 to collect all the data in Avante and build a 5-tab Excel workbook. After exporting the data, it should be alterred as needed. When done with the changes, save each of the worksheets as a tab-delimited txt file, overwriting the files normally used to import the data.

    Note that it will not export Private Label info or Customer Sales Group info for inactive customers. Thus, those numbers could drop when the data is re-imported back into Avante.

    Also note that these changes should be done quickly because after exporting the data, changes could still be made to the data in Avante that is not in the export. Those changes could be lost when the data is re-imported.
    You have been warned!