Anchor
_Toc158802917 _Toc158802917

Anchor
_Toc393291289 _Toc393291289

Anchor
_Toc388372650 _Toc388372650

Anchor
_Toc389493864 _Toc389493864

Import Process

Image Added

• The import process adds transactions to the AGRIS V9 system.
• The import process sets a standard format for all reords being imported.
• The import process is the only way a transaction can be updated in the V9 database from outside the application.

Anchor
_Toc393291290 _Toc393291290

General Steps for Import

1. File is created to add or update a record or document type in AGRIS.Fixed position or spreadsheet layout.
2. The file follows the defined map for each record type.
3. In AGRIS > Reports > Import Procedures > select the pre-defined import.
4. Verify the file location and file name.
5. 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.
6. Verify your import settings such as alternate name ids and date control.
7. Select your printer (which should always be the spool).
8. Run the import.
9. AGRIS reads each record to determine what is being import, saves that record to a temporary import file, and then imports it into AGRIS.
10. 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.
11. An import report is created and printed that will display all records imported and rejected.
12. Review this report to determine how to handle rejects.
13. Fix the import file and re-import or manually key rejects.

Anchor
_Toc393291291 _Toc393291291

Import Maintenance

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.
Image Added 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 locationLeave blank to use same location as Import file location.
• Alt. Output File or Reject file nameLeave 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
_Toc158802918 _Toc158802918

Anchor
_Toc393291292 _Toc393291292

File Layouts for Imports

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
_Toc393291293 _Toc393291293

Anchor
_Toc158802919 _Toc158802919

Document Numbers Rules

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
_Toc393291294 _Toc393291294

Reading File Layouts

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. Image Added
• The file can be a fixed position file (AGEXPIMP.FIL) where each field must start in an exact position. Image Added
• The file can be a spreadsheet (AGEXPIMP.CSV) where each field gets its own column. Image Added

  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

  
  

  Anchor
  _Toc393291295 _Toc393291295

  AGRIS > Maintenance > File Layouts for Imports

  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.
  Image Added
• 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.

Image Added
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.

Image AddedImage Added

Image AddedImage Added

Anchor
_Toc393291296 _Toc393291296

oneWeigh Grain Ticket Import

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
_Toc393291297 _Toc393291297

Start Import

1. Image Added oneWeigh station keys in grain tickets all throughout the day.
2. 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
3. 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. Image Added
4. 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-chararcters 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.
5. You go into AGRIS > Imports > Import Procedures to bring this file and its records into AGRIS. Image Added
6. 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. Image Added
7. 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. Image Added Image Added
8. The screen for the import of each record type will flash by showing you records read, updated, and rejected.
9. 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. Image Added
10. 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. Image Added
11. 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.
12. 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.
13. 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. Image Added
14. 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
_Toc393291298 _Toc393291298

Summary:

1. Know where your raw data is stored. (Import Path)
2. Know what your reject file name is and where it is store. (Export path)
3. Review you import reports daily.
4. Deal with the first rejection code and import.Usually other codes go away when the first reject code is resolved.
5. Sometimes you have to do manual entry.

Anchor
_Toc393291299 _Toc393291299

Invoice/Payment Import – Custom File Translation

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
_Toc393291300 _Toc393291300

Start Translation or Process Integration

1. 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)
2. Process the CFT (Custom File Translation)AGRIS > CFT > Communications or File translation > Process or TranslateWe'll focus on Store Manger
3. The import is happening behind the scenes. Image Added
4. 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. Image Added
5. Image Added 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.
6. 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.
7. 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.
8. Review the report for the next set of rejections. Image Added
9. 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.
10. Review the import report for the next set of rejections. Image Added
11. Take the first on the list and solve for that rejection.H = ITEM NUMBER NOT FOUND OR INACTIVESo 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-6002387the "930-HOOCHIE MAMA ELK CALL" sporting whistle.Just solve the first rejection code and try again.
12. Review the import report for the next set of rejections in the report spool. Image Added
13. 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 typec. fill in the import default for invoice type in ACR setupd. Use the Modify function of the import.

Anchor
_Toc393291301 _Toc393291301

Summary:

1. Know where your raw data is stored. (Import Path)
2. Know what your reject file name is and where it is store. (Export path)
3. Review you import reports daily.
4. Deal with the first rejection code and import.Usually other codes go away when the first reject code is resolved.
5. Sometimes you have to do manual entry.

Anchor
_Toc393291302 _Toc393291302

Modify Function of the Import

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.

1. 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. Image Added Image Added
2. 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
_Toc393291303 _Toc393291303

Import Modify Function

1. Determine what import you are using.Maintenance > Import MaintenanceDo not use a generic. When using Modify function, create a unique import.Select or insert an import.
2. Select the Modify button in the lower left hand corner. Image Added
3. Image Added 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.

1. 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. Image Added
2. Image Added 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.
3. Now re-import using the modified import procedure.Review your report. Now everything is imported. Image Added

Anchor
_Toc393291304 _Toc393291304

Summary:

1. Know where your raw data is stored. (Import Path)
2. Know what your reject file name is and where it is store. (Export path)
3. Review you import reports daily.
4. Deal with the first rejection code and import.Usually other codes go away when the first reject code is resolved.
5. Sometimes you have to do manual entry.

Anchor
_Toc393291305 _Toc393291305

Using Imports to Mass update Names

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.

1. Always make a copy of the production dataset and log into that dataset prior to working on import export reports!
2. Create a user-defined report based off the standard name/address export spreadsheetNAM > Reports > Report writer > insert > standard > Name/address Export spreadsheet
3. Changes will need to be made on the following screens:

• Name the new report something that will be easily identifiable. Image Added
• Image Added 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 Image Added
• 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)

Image Added

• Report Designer Screen (detail line #1) NAMA2 – Adjust the second 20 Yes NO flags to NO.

Image Added

1. 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. Image Added
2. 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.
3. Review the Name Address import log for rejection codes and reasons.
4. 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 35^th position of the 40 YN's.

Anchor
_Toc393291306 _Toc393291306

Anchor
_GoBack _GoBack

Wrap Up

1. Know where your raw data import file is stored.
2. Know where your reject file is stored and it's name.
3. Always check out the import reports to look for rejects daily.
4. Solve the first reject first and re-import, then move to the next.
5. Use the modify function to mass replace data on the import file, if the import file is wrong.
6. Can use imports to mass update data records like name or inventory, for example.

~ Documentation Written by Alisa Mueller 07/2014