TABLE OF CONTENTS:
| Table of Contents | ||
|---|---|---|
|
Import Process Overview
Import Overview
...
The import process adds transactions to the AGRIS system.
The import process sets a standard format for all records being imported.
The import process is the only way a transaction can be updated in the AGRIS database from outside the application.
| Anchor | ||||
|---|---|---|---|---|
|
File is created to add or update a record or document type in AGRIS. Fixed position or spreadsheet layout.
The file follows the defined map for each record type.
In AGRIS > Reports > Import Procedures > select the pre-defined import.
Verify the file location and file name.
Verify the reject file location and file name. Note that if you leave the reject file location and name blank, it saves the rejects back to the same place it started.
Verify your import settings such as alternate name ids and date control.
Select your printer (which should always be the spool).
Run the import.
AGRIS reads each record to determine what to import, saves that record to a temporary import file, and then imports it into AGRIS.
If the record is rejected, then a copy of the record is saved back to a file (you determine in your setup) for you to determine how to handle later on.
An import report is created and printed that will display all records imported and rejected.
Review this report to determine how to handle rejects.
Fix the import file and re-import or manually key rejects.
| Anchor | ||||
|---|---|---|---|---|
|
Use Import Maintenance on the AGRIS > Maintenance menu to create and maintain import procedures. This option allows you to setup defaults for many different scenarios.
The import maintenance allows you to define a set of rules for this particular import such as:
Import File location
Import File name
Alt. Output File or Reject file location leave blank to use same location as Import file location.
Alt. Output File or Reject file name leave blank to use same file name as import file name.
End of Processing Option (how to handle rejects)The normal process is option 2) leave Rejected and Invalid Records
Use of alternate name id
Use of Current Processing Date for Out-of-range dates? If an invoice or ticket is outside your earliest and latest dates allowed, do you reject it or change the date on the document at time of import?
Report option (strongly recommend that this be set to 1) Always Print)
Output device (Strongly recommend that this be set to 5) Disk Spool File
Print Type
| Anchor | ||||
|---|---|---|---|---|
|
| Anchor | ||||
|---|---|---|---|---|
|
Every file imported into AGRIS must conform to a specific file layout. AGRIS > Maintenance > Import File Layouts > Each Document Type that can be updated has a map for the data to follow.
| Anchor | ||||
|---|---|---|---|---|
|
| Anchor | ||||
|---|---|---|---|---|
|
If the document number is on the record being imported, then we take whatever they import. It is not compared against our valid range & we don't stop the import or update document numbers because we don't compare them against settings for valid range. You can import in anything and we won't stop you, assuming the number hasn't been used.
If you leave the document number blank on the record, like your 2nd transaction, then AGRIS looks to the document numbers & assigns the next number AND updates the last number used because we are now accessing our document numbers to see what we should use.
| Anchor | ||||
|---|---|---|---|---|
|
Almost every import layout has an AGRIS Standard Report that is waiting to export out your existing data in the required layout to be imported into a dataset again.
The file name will always be AGEXPIMP, so you will know that it represents a file layout. Go to any package > report manager > select report type > standard reports > look for the file name if AGEXPIMP. That is an export setup to print in exact format of the file layouts for import.
The file can be a fixed position file (AGEXPIMP.FIL) where each field must start in an exact position.
The file can be a spreadsheet (AGEXPIMP.CSV) where each field gets its own column.
Package
Import Description
Standard Report
ACP
VOUCHERS
A/P VOUCHER EXPORT FILE OR SPREADSHEET
ACP
DISBURSEMENTS
A/P DISBURSEMENT EXPORT FILE OR SPREADSHEET
ACR
INVOICES
INVOICE EXPORT FILE OR SPREADSHEET
ACR
PAYMENTS
A/R PAYMENT EXPORT FILE OR SPREADSHEET
BNK
BANK TRANSACTIONS
BANK TRANSACTION EXPORT FILE OR SPREADSHEET
GRN
CONTRACT
CONTRACT EXPORT FILE OR SPREADSHEET
GRN
TICKETS
COMMODITY TICKET EXPORT FILE OR SPREADSHEET
GRN
DELIVERY SHEETS
DELIVERY SHEET EXPORT FILE OR SPREADSHEET
GRN
DISCOUNT TABLES
DISCOUNT TABLE EXPORT SPREADSHEET
GRN
COMMODITY PRICES
COMMODITY PRICES EXPORT SPREADSHEET
GRN
FUTURE PRICES
FUTURES PRICES EXPORT SPREADSHEET
GRN
MARKET BASIS
MARKET BASIS EXPORT SPREADSHEET
INV
INVENTORY FORMULAS
FORMULA/COMPONENT EXPORT FILE OR SPREADSHEET
INV
ORDERS
ORDER EXPORT FILE OR SPREADSHEET
INV
INVENTORY ACTIVITY
INVENTORY ACTIVITY EXPORT/IMPORT FILE OR SPREADSHEET
INV
INVENTORY ITEMS
INVENTORY ITEM EXPORT FILE OR SPREADSHEET
INV
DELIVERY TICKETS
DELIVERY TICKET EXPORT SPREADSHEET
INV
PHYSICAL COUNT
NONE
INV
CUSTOMER CARDS
CUSTOMER CARDS EXPORT SPREADSHEET
LDG
BUDGETS
NONE
LDG
JOURNAL ENTRIES
JOURNAL ENTRY EXPOR FILE OR SPREADSHEET
NAM
NAME/ADDRESS
NAME/ADDRESS EXPORT FILE OR SPREADSHEET
ONE
AUTO ID
AUTO-ID EXPORT SPREADSHEET
ONE
LAST HAULED PRODUCTS
PRODUCT EXPORT SPREADSHEET
PAT
PATRON ACTIVITY
PATRONAGE ACTIVITY EXPORT FILE OR SPREADSHEET
PAT
DISTRIBUTION TRANSACTIONS
PATRONAGE DISTRIBUTION EXPORT FILE OR SPREADSHEET
PAT
PASS THROUGH CREDITS
NONE
PAY
HOURS WORKED
HOURS WORKED EXPORT FILE OR SPREADSHEET
PAY
LABOR DISTRIBUTIONS
LABOR DISTRIBUTION EXPORT FILE OR SPREADSHEET
SJI
EXECUTION SJI
EXECUTION ID DETAIL SPREADSHEET
AGRIS > Maintenance > File Layouts for ImportsAnchor _Toc393291295 _Toc393291295 Below are the layouts required for importing name address records into the system. The same format is used to accept new name ID's or change existing name ID's. As you review the document below notice the
, (#) and 
notices listed next to each field. These fields will identify / require certain rules when importing those individual data fields of a record. 
Spreadsheet column or use Positions for fixed files
Note the special requirement need flags (look to bottom of layout for definition.
Len - represents how large the field can be
Type - represents the data type such as number or letter (look to bottom of layout for definition)
Comments – special notes about field.
Below – Update field/select option – how to only update a single field in a record and protect all other data.
The area highlighted above indicates the updated field selection for the NAMA1 record. This is a series of Yes or No indications that would look like this YYNNNNYYYNNNYYYNNNNNNNNNNNNNNNNNNNNNNNNN. By counting the column number that the data field resides and only answering YES for fields you wish to update you will be able to quickly and easily export and import data updates into your system.
| Anchor | ||||
|---|---|---|---|---|
|
The oneWeight grain ticket import is a process that can occur multiple times throughout the day or just once at the end of the day.
The oneWeigh system creates grain tickets at the scale as trucks weigh in and out from dropping off or picking up grain. These tickets follow the GRN Commodity Ticket Export file Layout. (note that it can also create inventory delivery tickets for outgoing fertilizer and stock additions for incoming fertlizer purchases)
| Anchor | ||||
|---|---|---|---|---|
|
oneWeigh station keys in grain tickets all throughout the day.
While the oneWeigh stations is entering tickets a file is being created and appended to for each of the tickets keyed. This is the file you will import.ONE > Setup > Workstation Information > Interface button > Export Files section
At the end of the day or multiple times during the day, this file can be imported to update your live dataset with active tickets.
Note above that there are multiple record types to import from this file.INVT0 tells AGRIS this is a delivery ticket.ACPV0 tells AGRIS this is a stock addition voucher.GRNT0 tells AGRIS this is a grain shipment. The first 5-characters of each record type identify it to AGRIS as to the type of record being imported. It also determines what rules (AGRIS import file layouts) must be meet for this record to import.
You go into AGRIS > Imports > Import Procedures to bring this file and its records into AGRIS.
Note: If you will key an "R" in the "End of Processing Option" AGRIS will provide you a listing of all the possible rejection codes this file could encounter.
When you finally read the file to import, AGRIS will display the number of records and file types that you will be importing. You can say No to each type and leave it in the reject file or yes to import.
The screen for the import of each record type will flash by showing you records read, updated, and rejected.
Once the file is processed and rejects or skips are saved to the Alt. Output File, then you go into the report spool to verify the import and deal with rejections. We are going to focus on the GRN Ticket Import report.
Every import report will show each record read and then there will be a column to tell you what records were rejected, if any. Almost to the bottom of the report will give a summary of the rejection codes. Then at the very bottom the number of records read, skipped, updated, and rejected. So after EVERY import or at the end of EVERY day, you should be reviewing these import reports for rejects. This is why you save them to the spool; to review.
Now we know that there were rejected tickets. C = INVALID OR INACTIVE CUSTOMER NAME/ID NUMBER G = TICKET NUMBER ALREADY s = INVALID OR INACTIVE APPLICATION NAME/ID NUMBERStart with the first code on the list and solve and import it. Usually the codes below it will go away once the first code is solved.
So we'll start with C = INVALID OR INACTIVE CUSTOMER NAME/ID NUMBERTicket 47 rejected due to C and s. The report says BLAY for customer name id. Verify that BLAY is setup in the system by going to name/address and keying in that name id. We find that he is not in our live dataset. Talk to the scale and verify how he got into their system and then decide if he should be added to your system or if the scale used the wrong name id.In this case, he needs to be added to our system. Key in his name id and pertinent information and re-import the reject file.
The re-import of the file showed only 2-rejects now with our Ticket 100-0000047 importing in now that the name id was setup.
Now we move on to the next rejection code.G = TICKET NUMBER ALREADY USEDTickets I-999-0000034 and I-999-0000043 say they are already used and in the AGRIS system. So go verify if these tickets are in the system and if they match the import records.GRN > Shipments > View ShipmentsIn our case we find that the two tickets are in the system. One is a voided transfer and one is an inbound for corn to name id Z89871.So we call the scale and ask what happened with these two.It was concluded that the scale set their document numbers back, so our only option is to key these tickets using control numbers by hand.No import.
| Anchor | ||||
|---|---|---|---|---|
|
Know where your raw data is stored. (Import Path)
Know what your reject file name is and where it is store. (Export path)
Review you import reports daily.
Deal with the first rejection code and import. Usually other codes go away when the first reject code is resolved.
Sometimes you have to do manual entry.
| Anchor | ||||
|---|---|---|---|---|
|
There are several outside systems that create AGRIS invoices and payment records for imports into AGRIS. Point-of-sale and gas pump system are two of the systems that will create data for AGRIS to import in as if you had keyed the invoice or payment by hand.
Most of these systems will go through a CFT (custom file translation) where the data from the outside point-of-sale or fuel pump system will be converted into one of the AGRIS file layouts. The thing that makes this unique is that AGRIS will convert and import the transactions for you. However, even though you don't see the import happen, you still need to review the import reports and deal with the rejections.
| Anchor | ||||
|---|---|---|---|---|
|
You complete your work in the outside accounting system. The Store Manager completes their Daily Close. The Energy Force completes their daily invoicing. All of these "end of day" functions create a file that contains information on invoices and payments for customers.(just like the oneWeigh created a file to import)
Process the CFT (Custom File Translation)AGRIS > CFT > Communications or File translation > Process or TranslateWe'll focus on Store Manger
The import is happening behind the scenes.
Even though the import is happening behind the scenes and you do NOT see the record count being added, there is still an import report with records read, skipped, updated, and rejected being created in the report spool.
The A/R Invoice Import Report will show all the rejections and the records that imported. Near the bottom of the report is all the rejection codes. Then the total records read, skipped, added, and Rejected will print at the bottom.
To solve the rejects, start with the first one in the list.B = Invoice Location Code not FoundLooking at the report, we can see that invoice location is V17. Verify if that is setup in the dataset. Correct and re-import.
To re-import, you have to know the file name and location of the rejects. You didn't start the import, it happened behind the scenes. With the CFT imports, each option has its own default of reject file names written into the coding. And the reject files are saved to the dataset folder.For Store Manager, the file the stores_name_yymmdd_hhmmss.fil.The point is that there ALWAYS a reject file. ALWAYS!AGRIS > Reports > Import Procedures > select file and import manually.
Review the report for the next set of rejections.
Take the first one on the list and solve for that rejection.C = BILL TO NAME/ID NUMBER NOT FOUND OR INACTIVESo Bill To Name id "000000018" is not setup in the live dataset or they are using the wrong number in at the store. We are going to set this one up in AGRIS and re-import.Just solve the first rejection code and try again.
Review the import report for the next set of rejections.
Take the first on the list and solve for that rejection.H = ITEM NUMBER NOT FOUND OR INACTIVE So we have 1-invoice rejected because the inventory line items are not in AGRIS.The inventory displays on the report. Verify the items and what they should be.In this case we are going to set them up in AGRIS, Item 100-6002387 the "930-HOOCHIE MAMA ELK CALL" sporting whistle. Just solve the first rejection code and try again.
Review the import report for the next set of rejections in the report spool.
Take the first rejection code on the list.L = Invoice Type Not Found or Blank when required. There are several ways to handle this. (a) remove the requirement. (b) edit the raw data file and fill in invoice type (c) fill in the import default for invoice type in ACR setup (d) Use the Modify function of the import.
| Anchor | ||||
|---|---|---|---|---|
|
Know where your raw data is stored. (Import Path)
Know what your reject file name is and where it is store. (Export path)
Review you import reports daily.
Deal with the first rejection code and import. Usually, other codes go away when the first reject code is resolved.
Sometimes you have to do manual entry.
| Anchor | ||||
|---|---|---|---|---|
|
During the imports above, we have been dealing with the reject issues by adding items to AGRIS. However, what do you do when the data is right in AGRIS and wrong in the import file?
In the case above, all the invoices are rejecting because they are missing an invoice type.
You edit the raw file via text editor. You have to know exactly what position the data needs to be keyed in and be sure to not move any other fields around. Typing a correct invoice type over the PS that is there. According to the file layout, the invoice type goes on the ACRI record in position 89 for 2-characters.
You use the Modify Function to Mass Replace data fields with the correct data. This option is much easier to use.It allows you to setup a overall override in the import procedure maintenance function.
| Anchor | ||||
|---|---|---|---|---|
|
Determine what import you are using. Maintenance > Import Maintenance. Do not use a generic. When using Modify function, create a unique import. Select or insert an import. You may need to search for the specific import you are looking for if you do not see it in the list. There will be a magnifying glass (search option) next to the Description field, to search for the Import to Modify.
Select the Modify button in the lower left hand corner.
The first time you go into Modify on each import, you must select a record type. Remember that the first 5-characters of each record being imported identify the record type and tell AGRIS what the rules & positions are for that record. So for our example, we are attempting to replace the PS invoice type on the ACRI0 record.
Then you will pick the record type that you want to edit. Every field that is on that record type will display. We will select the invoice type which is in position 89.
Then you fill in the old value and the new value. Note that you can also place a wildcard
in the Old Value so it picks up everything and replaces with the new field.Or you could leave it blank and only blanks would have their data replaced. This is a function that allows you to replace any field on any record type at the time of the import.NOTE: The field only replaces if the record imports. If it rejects, then the old data is still there.Now re-import using the modified import procedure.Review your report. Now everything is imported.
| Anchor | ||||
|---|---|---|---|---|
|
Know where your raw data is stored. (Import Path)
Know what your reject file name is and where it is store. (Export path)
Review you import reports daily.
Deal with the first rejection code and import.Usually other codes go away when the first reject code is resolved.
Sometimes you have to do manual entry.
| Anchor | ||||
|---|---|---|---|---|
|
Examples of Name Address exports include the following:
Finance charge table update
Miscellaneous NAME ID fields like customer type, mailing flag or producer vs. patron.
Change the default sales person assigned
Updating credit limits
Adjusting the default discount tables per customer
Assigning new AR or AP terms
Adjusting taxable flag status
Change NAME ID type
The best way to explain this functionality would be to present a real world scenario. In the following example "Joe" the user has been tasked with updating the AGRIS system to do a credit freeze for any customers that have an aged balance greater than 90 days old.
Always make a copy of the production dataset and log into that dataset prior to working on import export reports!
Create a user-defined report based off the standard name/address export spreadsheetNAM > Reports > Report writer > insert > standard > Name/address Export spreadsheet
Changes will need to be made on the following screens:
Name the new report something that will be easily identifiable.
Value selection criteria page – Selecting account only 90 days or greater by entering a 3 = Greater than.
Additional options page – To mark the Print Comments, Product license numbers and relationship info to "N"= No
Report Designer Screen (detail line #1) –NAMA1- Adjust the first forty Yes NO flags to all NO except the Sales Credit Limit. (see reference of which field based on column count on the Name Address import File layout report)
...
Report Designer Screen (detail line #1) NAMA2 – Adjust the second 20 Yes NO flags to NO.
...
Once changes are complete you can save and run the new User Defined report and review the results. In this case it is a fast easy adjustment to take all Sales Credit Limits to Zero will be as easy as a drag of the mouse to populate the column with a zero balance.
Once changes are made and reviewed you may now bring save the CSV file and prepare to import using the standard import format found under Reports\ Import Procedures. If you have rejects from this process the following are suggested steps to identify why a rejection occurred.
Review the Name Address import log for rejection codes and reasons.
Double check the number of No's and Yes's are accurate for your import. In the case above the import required a "Y" at the 35th position of the 40 YN's.
| Anchor | ||||
|---|---|---|---|---|
|
| Anchor | ||||
|---|---|---|---|---|
|
Know where your raw data import file is stored.
Know where your reject file is stored and its name.
Always check out the import reports to look for rejects daily.
Solve the first reject first and re-import, then move to the next.
Use the modify function to mass replace data on the import file, if the import file is wrong.
Can use imports to mass update data records like name or inventory, for example.
~ Documentation Written by Alisa Mueller 07/2014