Update: 10/29/19

Version: 8.5 and up

Introduction

Accepting firearms drop ship orders from e-retailers opens up new business opportunities but it also presents potential business risks.  This feature’s intention is to address this issue. 

According to BATF regulations, distributors need to collect the most recent copy of FFL for the firearms to be shipped to.  At the moment, these drop ship location FFLs are provided by the e-retailers as JPEG files. These files can be saved as JPEG files in a folder that Elliott can link to. However, since there is no human inspection of these JPEG files, there is no guarantee that these JPEG files contain a valid FFL.  This is a business risk from the BATF’s compliance point of view. 

To mitigate this risk, this feature provides an application where the distributor can survey the FFL holders and collect these FFLs on their own regardless of whether the FFL holders buy from them. Since some FFL holders may view that the transfer (drop ship) orders compete with their regular retail business and may not wish to accept transfer (drop ship) orders, this application allows the user to survey FFL holders to determine if they wish to accept drop ship orders.  If they do, the distributor will collect their FFL info as JPEG files or other format files (i.e., PNG or PDF) and save it as a link with Elliott. 

At the end of the survey process, the distributor will have a list of FFL holders, and their corresponding FFL image files validated by the distributor.  This a master list of FFL holders who are willing to accept transfer (drop ship) orders regardless of whether they currently purchase from the distributor.  

To make this list available to the e-retailers, a new web services, FFLNearYou, will be created.  This web service is similar to the ResellerFinder web service.  The third party e-retailers will use the new FFLNearYou web service by passing in a zip code. Then this web service will return a list of FFL holders who are willing to accept drop ship orders and already have their FFL image on file.  The list will be sorted by distance based on the FFL holder’s zip code.  The third party will suggest the locations of these FFL holders to consumers so they decide where the transfer (drop ship) order will be shipped to. See http://support.elliott.com/knowledgebase/articles/1912426-fflnearyou for more information. 

A configuration switch controls whether only FFL the expiration date should be validated against the FFL Extension file has been added to the ElishptoService web service. This is set through the ValidateFFLExtensionWhenInShipTo key.  If the key value is “N”, then there’s no change to the behavior of ship to web services. If the key value is “Y”, then the web service will ignore the passing in Ship To FFL Expiration Date. The web service will look up the FFL Extension table for the FFL number provided, and if record is found, it use the expiration date in extension table to update the ship to. If record is not found, the web service will use zeroes date for ship to FFL expiration date.  See http://support.elliott.com/knowledgebase/articles/1828840-elishiptoservice for more information.

The following changes were made to Elliott in order to support the new operation.

FFL Licensee Table Update through Merging Method

You can go to the BATF’s website and download a list of FFL holders.  Then you can import this list in CSV format to Elliott as the FFL Licensee Table (ARFFLFIL).  This FFL Licensee Table is widely used in Elliott in Customer File Maintenance, Order Entry, Picking Ticket printing and web services to ensure the address matches the FFL Licensee Table. 

The current import method to update the FFL Licensee Table is based on an “Override” method. During the “Override” period, no other process can open the FFL Licensee Table; otherwise, that process will fail.  If the FFL Licensee Table is already opened, then the update will fail.  Since the “override” process will take several minutes to complete, the import timing is becoming more and more critical as these activities start to increase.  A better method needs to be developed to address the conflict of timing issue. 

We propose, instead of using the “override” method, that we instead use a “merging” method. The application will go through three phases.

(1)    The first phase is to clear the “in-CSV-flag” on every record instead of initially deleting all of the FFL Licensee records.

(2)    The second phase will read and process each FFL Licensee CSV file’s record one by one:

a.      If the corresponding FFL records exist in the FFL Licensee Table, set the “in-CSV-flag” and update the licensee fields.

b.      If the corresponding FFL record does not exist in FFL Licensee Table, the new FFL record is added to the FFL Licensee Table. The “in-CSV-flag” is set to “Y.”

(3)    In the final phase, the application will read through the FFL Licensee Table and delete the FFL Licensee Table where “in-CSV-flag” does not equal “Y.”  We will not delete the corresponding FFL Extension Table record. See the FFL Extension Table section for more information. 

By using this “merging” method, you will be able to import an FFL Licensee CSV file any time without causing a locking issue or failed address validation due to missing FFL information.

Note: The FFL Licensee Table must be imported on a regular basis for the FFL Validation and FFL Relationship Synchronization to work.

FFL Extension Table

We believe the FFL Licensee Table should only contain the information provided by the BATF. Therefore, a new FFL Extension Table (ARFFLEXT) has been added to store extra FLL licensee information.  An FFL Extension Maintenance program for update, delete and list will be provided in the area of AR -> Maintenance -> Customer -> FFL -> FFL Info.  The maintenance screen also displays the FFL information from the FFL Licensee Table but the information can’t be changed since additions were made to the FFL Licensee Table strictly through the import process. Notes, Links, eContacts, and Attributes will be supported for FFL Extension files in the maintenance program. 

  

1.      FFL No.: This is the primary key of this table and it is the 8-digit FFL number. The reason we don’t use the 15-digit FFL number here is because the 15 digits will change when the FFL is renewed, while the 8-digit FFL number is permanent. Users can enter either the 8 or 15 digit FFL number. If the 15 digit FFL number is entered, the application will derive the 8 digit FFL number for field 1 and display the entered 15 digit FFL number in field 5. 

An F7 search is available to search by 8 digit FFL number, 15 digit FFL number, licensee name, and FFL zip code. 

An F2 toggle is available to only display FFL numbers that are on file in the FFL Extension table.

After entering an FFL number, the application will determine if the the FFL number is on file in the extension file. If so, the application will be placed in changed mode. If not, the application will be placed in add mode.

Relationships: This line shows the number of FFL relationship records on file for the 8 digit FFL number. 

If the first record found is from the ARCUSFIL, the line will be displayed as
    Relationships: (1/999) Customer 123456 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
If the first record found is from the CPSHPFIL, the line will be displayed as
    Relationships: (1/999) Cust/ShipTO 123456 1234 XXXXXXXXXXXXXXXXXXXXXXXXXXX
If no relationship records are found, the line will be displayed as
    Relationships: None

Note: Only the first relationship found is displayed. If there is more than one relationship on file, all relationships are shown on the FFL Extension Edit List if the Include Relationships option is set to Y.

2.      Accept Transfer: This flag indicates whether this FFL holder accepts transfer (drop ship) orders from the distributor.  The value can be “Y”=Yes, “N”=No, or Blank=Undetermined.  The reason for undetermined is because you could receive the FFL because they are your customer but they did not answer the question regarding if they accept transfer (drop ship) orders.

3.      Comment: Five 60-character fields.  We will also support the notes feature (5 Amigos) for the FFL Extension Table, so additional information can be stored in the Notes Table for the FFL.

4.       FFL-Expiration-Date: This represents the FFL expiration date from the FFL copy we received and it does not necessarily match the FFL expiration date in FFL Licensee Table. When an FFL copy is received, the operator should update this field accordingly.  This will also cause all customers and ship-to that have the same FFL to get an updated FFL expiration date. See below under the FFL Expiration Synchronization section for more information.

5.      15 Digit FFL No.: This is the current 15-digit FFL number. This field can only be entered if field 16 on screen 5 of the GUN setup is set to Y.

6.     FFLNearYou Rank: Ranking 1-9  where 1 = Highest and 9 = Lowest. The lower the number the more "preferred" a FFL holder is for transfer purposes. This field is used when sorting the FFLs returned by the FFLNearYou web service.

Created-By: The user who created this record and the date/time it was created.

Modify-By: The user who last modified this record and the last date/time it was modified.

 

16. Enter 15 Digit FFL In Ext.? – The default is Y. If set to Y, users will be allowed to enter the 15-digit FFL number in field 5 in the FFL Extension Maintenance application. If the FFL number is changed and the customer or ship-to FFL numbers associated with this record are also 15 digits, the records will be updated. If set to N, the user will not be able to enter or override the 15-digit FFL number in the FFL Extension Maintenance application. 

If a relationship record does not exist for the FFL number, adding or changing the FFL extension will not update any information other than the FFL Extension file. It will read the FFL Master file to retrieve the license name, business name, premise address, mailing address, phone number and the 15 digit FFL number. The expiration date will be blank in add mode and will not default to the derived expiration date from the FFL Master file. You must enter this information manually.

If the relationship record does exist and you are updating the expiration date or the 15 digit FFL number, the application will find all of the FFL relationship records that have a matching 8-digit FFL number. It will update the expiration date and the 15-digit FFL number, if applicable, on matching customer and ship-to records. The 15-digit FFL number will only be updated if it is allowed to be entered in this application and the current FFL numbers on file in the customer or ship-to record are also 15-digit FFL numbers. 

Five events are supported in this application. 

File Name: ARCUSFIL

Program: ARCUSMNT

Type: FFLCHNGC

Description: FFL Add/Change for Customer

The following event variables are available:

• Customer Number
• Customer Name
• Old Full FFL Number
• New Full FFL Number
• Old FFL Exp Date
• New FFL Exp Date
• New Short FFL No
• Address 1
• City/St/ZipCode

 

File Name: ARSLMFIL

Program: ARCUSMNT

Type: FFLCHNGS

Description: FFL Add/Chg for Slsmn Customer

The following event variables are available:

·        Salesman Number
·        Salesman Name
·        Old Full FFL Number
·        New Full FFL Number
·        Old FFL Exp Date
·        New FFL Exp Date
·        New Short FFL No
·        Address 1
·        City/St/ZipCode

 

File Name: CPSHPFIL

Program: CP1001

Type: STFFLCHG

Description: FFL Add/Change for Ship-To

The following event variables are available:

·        Ship-To Key
·        Ship-To Name
·        Old Full FFL Number
·        New Full FFL Number
·        Old FFL Exp Date
·        New FFL Exp Date
·        New Short FFL No
·        Address 1
·        City/St/ZipCode

 

File Name: ARSLMFIL

Program: CP1001

Type: STFFLCHS

Description: FFL Add/Chg for Slsmn Ship-To

The following event variables are available:

·        Salesman Number
·        Ship-To Key
·        Old Full FFL Number
·        New Full FFL Number
·        Old FFL Exp Date
·        New FFL Exp Date
·        New Short FFL No
·        Address 1
·        City/St/ZipCode

 

File Name: ARFFLEXT

Program: ARFFLEXM

Type: FFLEXTCH

Description: FFL Extension Add/Change

The following event variables are available:

·        Short FFL No
·        Accept Transfer Before
·        Accept Transfer After
·        Exp Date Before
·        Ex Date After
·        Comment Line 1
·        Comment Line 2
·        Comment Line 3
·        Comment Line 4
·        Comment Line 5

An edit list is provided.

Starting 8 Digit FFL# - The starting 8-digit FFL number. Press Enter for All.

Ending 8 Digit FFL# - The ending 8-digit FFL number.

Only Include Licensees Who Accept Transfers? – The default value is N. If set to Y, only FFL Licensees that accept transfers will be printed.

Include Comments? – The Default value is Y. If set to N, then comments will not print on the report.

Include Relationships - The default value is N. If set to Y, any relationship records associated with the FFL number will print.

Show Records with FFLX - The options are A=All, Y=Only FFLs with FFLX Links, N=Only FFLs without FFLX Links. The default is A. 

Only Show Records With Relationships - The options are Y=Yes, N=Only Records Without Relationships, and A=All. The default is A.

Create CSV File? - The field defaults to the last value entered. Answer Y to export the results to CSV.

CSV File Path & Name - If Create CSV File is set to Y, enter the path and the file name for the CSV file. The field will default to the value last entered.

CSV Fields

FFL8 - 8 digit FFL number
FFL15 - 15 digit FFL number
LicenseName - License name from the FFL Master File
Address - Premises address from the FFL Master File
City- Premises city from the FFL Master File
State - Premises state from the FFL Master File
ZipCode - Premises zip code from the FFL Master File
PhoneNumber - Phone number from the FFL Master File
FFLExpDate - FFL expiration date from the FFL Extension File
MasterFFLExpDate - Expiration date derived from the FFL Master File 15 digit FFL number
LinkDate - Date of the FFLX link file associated with the FFL Extension File. If the FFLX link supports wildcards and there are multiple link files found, this column will specify "Multiple" instead of a date.
CustNo - (Only Exported if Relationship is exported) Customer number from the FFL Relationship File
ShipToNo -  (Only Exported if Relationship is exported)  Ship-To number from the FFL Relationship File

Customer & Ship-To FFL Expiration Synchronization

One of the benefits of maintaining a central FFL Extension table is when the expiration date is updated; it can also update all related customers and ship-to records.  In the past, this was not a big issue because the user usually only had one customer per FFL holder.  With the transfer (drop ship) order business increased, more and more of the same FFL holders could appear as different records in various ship-to records.  To simplify the maintenance burden, the system provides a feature to update all customers and ship-to records that have the same FFL number with the new expiration date when the central FFL Extension table expiration date is updated. 

This is accomplished by reading through the FFL Relationship Table (ARFFLREL).  The purpose of the FFL Relationship Table is to establish a pointer between the FFL Licensee/Extension Table and Customer or Ship-To tables.  This table has the following information:

·        FFL-Number: Primary key – 8-digit FFL number.
·        File-Name: Primary Key – “ARCUSFIL” or “CPSHPFIL.”
·        Reference-ID: Primary Key – Customer Number or Ship-To Primary key.

This table is constructed in such a way that, for a given FFL number, we can find all the related customer or ship-to records.  This is important so that when the FFL Expiration date is changed in the FFL Extension Table, we can use this table to find all the related customer or ship-to records and update its corresponding FFL Expiration date. 

The FFL Relationship Table is self-maintained when customers or ship-to records are added, changed or deleted.  However, for the first time, the user will need to run a utility program to read through all customer and ship-to records to create this FFL Relationship Table initially.  Also, if the self-maintained feature ever has any issue, we can use this utility to regenerate the FFL Relationship Table. This utility program is accessible from Global Setup-> System-> Company Specialized Control-> Gun-> Utilities.

When the FFL Relationship records are generated using the utility, the application will also check for an FFL Extension record for the FFL number. If one exists and the FFL expiration date Customer or Ship-To record does not match the FFL Extension expiration date, the application will update the customer or ship-to FFL expiration date automatically. This is indicated on the Generate FFL Relationship report output.

When generating the relationship table, it is possible that the FFL number that is on file in the customer file or the ship-to-file is not on file in the ARFFLFIL. This is true for FFLs that have expired and have not been renewed or new FFLs that are available but have not been imported. The following rules are used to determine if the FFLs in the customer or ship-to file are in the correct format. FFL numbers that are in the correct format but are not in the FFL Licensee File are still added to the FFL Relationship Table.

Note:  FFL Relationship Table is self-maintained when customers or ship-to records are added, changed or deleted only if FFL Validation is enabled through Global Setup-> System-> Company Specialized Control-> Change-> Gun-> Screen 5-> Fields 1 and 3. These fields must be set to Y or W for the relationship data to be maintained.

8-Digit FFL: 

All 8 digits must be numeric.

The first digit must be 1, 3, 4, 5, 6, 8 or 9.

 

15-Digit FFL: 

The first digit must be 1, 3, 4, 5, 6, 8 or 9.

Digits 2, 3, 4, 5, and 6 must be numeric.

The value of digit 7 and 8 considered together must be 01, 02, 03, 06, 07, 08, 09, 10, or 11.

Digit 9 must be numeric.

Digit 10 must be A, B, C, D, E, F, G, H, J, K, L or M.

Digits 11, 12, 13, 14, and 15 must be numeric.

 

FFL numbers that are not formatted correctly, fake FFL numbers, or FFL numbers of “999999999999999” are not added to the relationship file.

Support FFL Links by FFL Number

Users will store the FFL as an image file in a folder(s) somewhere.  Therefore, it makes sense to support an FFL image link based on the 8-digit FFL number in the FFL Extension maintenance screen.  This way, users can bring up the FFL image anytime they need to verify if the FFL info is updated correctly.  This image link can be done easily in the current Elliott system via the FFL extension maintenance screen. In addition to FFL Extension Table maintenance, we think it will be beneficial to allow special links for FFL images in the following areas:

·        Customer File Maintenance & Inquiry
·        Ship-To File Maintenance & Inquiry
·        Order & Order History Inquiry by Customer/Order
·        Invoice History Inquiry by Customer/Invoice
·        Sales Desk
·        Order Entry

This link is a program link type named FFLX that is automatically created when Elliott is started.

The default template will point to the FFLX directory under the Elliott root directory. This can be changed to the correct directory for your installation. @@VAR1@@ represents the 8-digit FFL number that is passed to the link from the program that supports this link type. The system is initially set up to support PDF files (@@VAR1@@.PDF). This can be changed to the appropriate document type for your installation. Wild cards are also supported, such as@@VAR1@@*.*.

Note: This is a program link. The applications will check if a file exists in the specified location that matches the 8-digit FFL number and the file type specified in the link type. If it exists, the link will automatically be available for the user to access and view.

FFL Expiration Date Entry in Customer and Ship-To File Maintenance

When entering the FFL expiration date in Customer and Ship-To File Maintenance, if an FFL Extension record exists for the FFL number, the user will not be allowed to enter the expiration date. The expiration date on file in the extension file will be moved to the customer or ship-to-FFL expiration date and it it will be displayed. 

FFL Extension Special Function

When in change mode of customer or ship-to-file maintenance, a new FFL Extension Maintenance special function is available. This will  give the user access to the FFL Extension Maintenance application from customer and ship-to-file maintenance. The FFL number for the customer or ship-to is passed to the application and the FFL Extension application will determine if the FFL Extension exists. If it exists, the application will be in change mode. If it does not exist, the application will be in add mode and the user can add the FFL Extension information.

CLS