|
|
|
|
This feature allows for the importing of Per Diem Rate information into the Unanet system.
This screen is available to users having any of the following roles: Administrator and P&R Administrator
This feature is available with any Unanet edition.
Topics covered on this help page include:
Import File Record Layout (field by field descriptions)
Import File Format (note about csv layout and option to import a subset of columns using header values)
Processing Rules (how the data may be merged, etc.)
Excel Template (spreadsheet template containing column headings)
Import Screen (invoking the import via the user interface)
Sample File (sample conus and oconus files reformatted from DOD site data)
There are a number of available sources for this type of per diem data, each potentially having a slightly different layout and number of historical entries. Some customers may be interested in loading a limited set of their own custom locations, others may choose to load entries based on their locale or country, and others may be interested in loading up data from one of the US government sources.
Unanet Provided CONUS and OCONUS Per Diem Files
Unanet monitors the Department of Defense source and maintains a version of that data (manipulated into our import layout) on our Support >> Download site (login is required - then look for Per Diems section), which you can download and import directly into your system periodically per your needs. We maintain both a CONUS and OCONUS set of files and attempt to make available new versions as soon as we notice the DOD has posted updates. Unanet does not vouch for the accuracy/content of the data provided by the DOD.
Customer Supplied Per Diem Files
If you prefer to manage the per diem data being imported on your own, you will need to manipulate the data into the Unanet required format described below. Further, you will need to determine the frequency with which your data must be re-imported / updated.
Listed below are a number of known US sources for this type of data.
DOD (Department of Defense) - CONUS, OCONUS with and without military rates, also has a file with all rates, all history:
http://www.defensetravel.dod.mil/site/perdiemCalc.cfm
(this site may be a good choice as it has all of the rate information on the Per Diem Rates link -- "Relational File" links and appears to be updated whenever the other sites are updated).
GSA (General Services Administration) - CONUS (Continental US - Domestic), non-military schedule:
http://www.gsa.gov/portal/category/21287
Currently, none of the formats available map directly to the necessary Unanet format, thus you will need to manipulate these files into the necessary layout. For example, the DOD Relational Files require you to eliminate a number of columns. A common issue encountered when manipulating these files in Excel is dealing with the format of the seasonal date fields (mm/dd), these columns should be formatted as 'text' otherwise Excel may treat them as an equation.
Note: The maintenance of the per diem rate tables is a customer responsibility as there are numerous sources of per diem data and each customer may have their own policy dictating which source is to be used -- and at what frequency the data must be kept up to date.
IMPORTANT: Note that there are multiple places within this documentation that say you MUST use double quotes for the strings in your import. If you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually. Manually adding double quotes within Excel would result in multiple sets of double quotes in the resulting .csv file and thus cause an error (should you attempt to import that resulting file). Please see Excel Tips for more information.
|
|
|
Field Header Name |
Required/Comments |
|
1 |
A |
Country_State_Name |
ALWAYS REQUIRED. A name identifying the primary location. Typically this would be the name of a Country or State. Alpha-numeric, max of 45 characters. |
|
2 |
B |
City_County_Name |
ALWAYS REQUIRED. A name identifying a sub-location within the primary location. Typically this would be the name of a city or county. Alpha-numeric, max of 45 characters. |
|
3 |
C |
Effective_Date |
ALWAYS REQUIRED. The first date for which this particular per diem rate is effective. Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
|
4 |
D |
Expiration_Date |
ALWAYS REQUIRED. The last date for which this particular per diem rate is effective. Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
|
5 |
E |
Season_Start_Date |
ALWAYS REQUIRED. The month and day upon which this particular rate's season begins. Format must be "mm/dd". |
|
6 |
F |
Season_End_Date |
ALWAYS REQUIRED. The month and day upon which this particular rate's season ends. Format must be "mm/dd". |
|
7 |
G |
Lodging_Rate |
ALWAYS REQUIRED. The maximum per diem lodging allowance for this location and season. Format NUMBER (15,3) -- for example "01234567890.123 ". |
|
8 |
H |
Meals_Rate |
ALWAYS REQUIRED. The maximum per diem meals allowance for this location and season. Format NUMBER (15,3) -- for example "01234567890.123 ". |
|
9 |
I |
Incidentals_Rate |
ALWAYS REQUIRED. The maximum per diem incidentals allowance for this location and season. Format NUMBER (15,3) -- for example "01234567890.123 ". |
|
10 |
J |
Oconus_Indicator |
Indicates whether the rate being imported is to be categorized as oconus. Y meaning the location is to be considered oconus. Allowable values include Y or N (N is the default if not provided). This indicator was added in order to accommodate per diem tax calculations and is only taken into consideration on the Lodging Per Diem and the Lodging Allowable wizards. |
Note: Check out the Unanet Data model for specific field data types, lengths, and other attributes.
The file to import must be saved in a comma delimited format. The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.
If you are not using the default column layout sequence layout as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicated what data is contained in each column). The specific Header Names for each column are listed in the table above.
Each imported record will be added or merged into the contents of the existing Unanet per diem rate schedule. A general set of processing rules will apply to every record imported. These rules are as follows.
All fields are required in the import record.
The contents of all name fields will be converted to upper case before processing.
All rate values must be greater than or equal to zero.
The expiration date must be greater than or equal to the given effective date.
If a Season End Day is earlier in a calendar year than a Season Start Day, the single rate entry will be processed as though it specified two distinct seasonal periods.
For example, if the given Season Start Day and Season End Day are 09/15 and 04/15 respectively, the rate will be considered as applying to the two distinct seasonal periods of 01/01 – 04/15 and 09/15 – 12/31. Note that this condition will result in the number of rates added into Unanet being greater than the total number of records processed within the file.
All rejected rate records will be written out to a file on the Unanet server as well as displayed at the bottom of the Per Diem Rate Import user feedback screen.
Successfully importing rate records will not affect the enabled/disabled status of an existing location’s rates.
All rates for newly imported locations will, by default, be set to enabled.
Important Note Regarding Historical Rate Entries. When an entry is imported, it will replace all existing entries for that same location. Thus, if the data source you are using modifies the unique name of a location, or splits a location into two locations, when importing the new records, the old/historical records will remain. As a best practice, you may want to always exercise the "First Purge all existing Per Diem Rates in the system" option such that with each new import, you first clear out the existing records and replace with the new data set.
Should you be importing both Conus and Oconus files, you would only want to use the "Purge" option with the loading of the first of the two files.
You can create the comma delimited import file with any number of tools. For those interested in using an Excel spreadsheet to create the file, you can download 
an Excel Template with predefined headers and required fields noted.
See the Excel Tips regarding the use of double quotes and more.
The Import Per Diem screen simply looks like:
|
Field |
Description |
|
Type of Import |
Select the appropriate import type from the list of available types of imports. |
|
File to Import |
Enter (or browse for) the file that you would like to import. |
|
Output Options |
Specify the level of detail included in the output. |
|
Per Diem Rates |
First Purge all existing Per Diem Rates in the system. Note that this purge will NOT impact any expense report entries (rates already stamped on the expense reports will remain as is, the existing reference data is all that is being removed). Should you be importing both Conus and Oconus files (or any "set" of multiple files), you would likely only want to use the "Purge" option with the loading of the first of the files (so that you're not immediately deleting what you just imported). Note that you can also remove all per diem rate entries via the Per Diem Rates maintenance screen, however, due to the enormous volume of data the screen may take a long time to draw, and thus this option may be a preferred means to remove all existing per diem rate entries. Should an error be encountered in the date file after a successful purge, the data will remain deleted even if no records are successfully loaded (ie there is no "rollback" to the purge operation). |
The import process is triggered by pressing the Import button. All Warnings and Error messages are written to the screen and can then be saved if desired. While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database. The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the user feedback screen. The rejected record file name will be UnanetPerDiemRatesImportErrorxxx, where xxx is a randomly generated number. The file will be placed in the Unanet temporary directory (which is defined by the Temp Directory (Fully Qualified Directory Name) (unanet.temp_directory) Unanet property).
If you would like to experiment in a test environment, you can download and import per diem rate files from our support site.
Note: The maintenance of the per diem rate tables is a customer responsibility as there are numerous sources of per diem data and each customer may have their own policy dictating which source is to be used -- and at what frequency the data must be kept up to date.
Because we are not the owners of the per diem rate information, we can not vouch for the accuracy of their data, however, we do convert the All History Data files from the DOD source into our per diem import format periodically and post these files on our Support >> Download site. For the most up to date information, however, you may want to convert the files yourself from your data source of choice.
One word of warning.
The Per Diem Rate file uses a mm/dd format for specifying season start and end. If you open this file in Excel, Excel will automatically convert these values to full dates in dd-mmm format; e.g., 1/1 becomes 1-Jan. If you save the file with these converted values it will not import correctly.
