Versions Compared

Old Version 7

changes.mady.by.user Mike Terning

Saved on

compared with

New Version Current

changes.mady.by.user Mike Terning

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
maxLevel12

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
_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 to 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.

...

  • 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
_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 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
_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. 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.

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

  5. You go into AGRIS > Imports > Import Procedures to bring this file and its records into AGRIS.

  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.

  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 RemovedImage 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.

  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 RemovedImage 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.

  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.

  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.

  5. 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.

  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.

  11. 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.

  12. Review the import report for the next set of rejections in the report spool.

  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 type (c) fill in the import default for invoice type in ACR setup (d) 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.

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

  2. Select the Modify button in the lower left hand corner.

  3. 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.

  2. Then you fill in the old value and the new value. Note that you can also place a wildcard (star) 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.

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:

...

  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.

  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 35th 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 its 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.

...