The tables that can be inserted/updated using this import are listed below. Additional tables (not listed), related to Cost Estimates may be impacted by the import, but users are not allowed to import directly to these tables.
COST_ESTIMATE (there is an unsupported field list for this table)
COST_ESTIMATE!MARITAL_STATUS – if left blank, the system will attempt to default the value. If you enter a value on the import, you must use the Classification Code, not the Display Value.
COST_ESTIMATE!EFFECTIVE_DATE – should be expressed in a sortable date format or in US standard month-day-year format. This includes “MM/DD/YYYYY”.
COST_ESTIMATE!BASE_CURRENCY – must be three character currency code.
COST_ESTIMATE _CF
COST_ESTIMATE_LINE_ITEM (there is an unsupported field list for this table)
COST_ESTIMATE_LINE_ITEM!COST_ESTIMATE_TEMPLATE_LINE_ITEM_ID – required if you are importing line item details, including inputs, fixed amounts, and/or assumption text.
COST_ESTIMATE_LINE_ITEM!LINE_ITEM_NAME – required if you are importing line item details, including inputs, fixed amounts, and/or assumption text. Although this field isn’t actually used/validated by the import (it uses the ID instead), it is used in log/error messages for useability.
COST_ESTIMATE_LINE_ITEM_CURRENCY_TRUE_UP
COST_ESTIMATE_CURRENCY_TRUE_UP (there is an unsupported field list for this table)
COST_ESTIMATE_CURRENCY_TRUE_UP!PERFORM_TRUE_UP – cannot be imported at this time. The import automatically sets this to ‘Yes’.
COST_ESTIMATE_CURRENCY_TRUE_UP! REFRESH_RATES_AUTOMATICALLY – set to No if you want to import your own exchange rates with dates.
COST_ESTIMATE_LINE_ITEM!LINE_ITEM_NAME - Up to four additional line items may need to be added to your import template. In addition to the line items configured, the Tax Estimators will be handled as a special case. NOTE: there will be no COST_ESTIMATE_LINE_ITEM!COST_ESTIMATE_TEMPLATE_LINE_ITEM_ID value for each of these. The following should be added as rows in the COST_ESTIMATE_LINE_ITEM!LINE_ITEM_NAME column as needed, with the appropriate Input Name and Input Values included. This is case sensitive, and the values must be exactly as noted below.
Home Tax Estimator Country
Home Tax Estimator State/Province
Host Tax Estimator Country
Host Tax Estimator State/Province
Also, if you want to enter your own Exchange Rate for the tax estimators, rather than using the ones available in the system, you will need to include an Input Name of ‘Refresh Rate’ with an Input Value of ‘No’ and an additional Input Name of ‘Exchange Rate (<from currency> to <to currency>) with an Input Value of the exchange rate you want to use (similar to the fields you see on the screen when creating a calculation manually).
NOTE: Currently, Cost Estimate Template level tax positions are not fully supported (i.e., Calculate Social Taxes, Real property Owned in State, etc). Some of these are magically set by the import, but some are left blank; it is unclear why at this point. You can’t be sure your imported/batch calculated cost estimate is the same tax calculated amounts if you manually create one using the same data, so thorough testing should be done.
COST_ESTIMATE!NUM_YEARS – always defaulted to 1 for a compensation calculation, regardless if you include this column in your import or not. For Cost Estimates, you must include this column if you want the estimate to span more than 1 year; if left off, the import will default to 1 (a warning message will be given).
Cost Estimate Identifier – must be included for each row related to a cost estimate or compensation calculation. Must be the same value for all related rows (for a given assignment and cost estimate/compensation calculation).
Template Name – This field will be ignored by the import. It is there for reference to the user.
Input Name – This field is case sensitive. The name of the input, as appears on the screen when creating the calculation manually. If the Input Name is Cost of Living Index or Quality of Living Percent, and you want to Lookup Automatically, leave the Input Value blank. If you want to not Lookup Automatically and enter your own Index/Percent, enter an Input Value.
Input Value – This field is case sensitive. Enter the value for the input, as displayed on the screen when creating the calculation manually. This may be for stored procedure calculations, spreadsheet calculations, or tax calculations. If the line item is configured such that the value is defaulted (from a prior line item, or from a user report), you may choose to leave this input blank; when you run a calculation batch, the value will be refreshed as appropriate.
Currency Type – This is informational only and will be ignored by the import. This is to help the user know what currency to amounts in when the line item is configured for calculation = None. This is the value of the Data Entry currency configured for each line item.
If you have a line item that is configured with the Entry Currency set to something other than Data Entry (i.e., Home, Host, Company, System), you will need to add the appropriate column(s) to your import template. The column(s) you may need to add include:
COST_ESTIMATE_LINE_ITEM_CURRENCY_TRUE_UP!FROM_AMOUNT_YEAR1
COST_ESTIMATE_LINE_ITEM_CURRENCY_TRUE_UP!TO_AMOUNT_YEAR1
COST_ESTIMATE_LINE_ITEM_CURRENCY_TRUE_UP!COMPANY_AMOUNT_YEAR1
COST_ESTIMATE_LINE_ITEM_CURRENCY_TRUE_UP!SYSTEM_AMOUNT_YEAR1
Similar columns would be needed to support additional years (2 – 6 and/or post year).
Generally speaking, the import can update any field in any of these tables other than the ID field and any foreign key fields that link these tables to each other For lookup-type fields (any field that appears as a drop-down list on the screen) the value given to the import is the full text of the value the user sees on the screen (NOT the ID or code that is looked up and stored in the database), unless otherwise indicated. A currency field should be imported using the appropriate three-letter symbol (not the full name). A field that appears as a checkbox on the screen should be imported with a value of “Yes” if the box should be checked and “No” otherwise. Also, very few fields are required apart from the fields used to identify a record. Be generating the import template from an existing Compensation or Cost Estimate template, cell notes of instructions are provided.
The following tables are “subordinate” to the cost estimate table, and as such each table includes one field that is required if and only if any other fields from that table are included in the header (i.e. you can ignore the table completely, but if you choose to import any data into the table, you must include the indicated field):
COST_ESTIMATE_LINE_ITEM requires COST_ESTIMATE_TEMPLATE_LINE_ITEM_ID field
Other notes:
If the line item is configured such that a prior line item is used for input mapping (uses Get Value From (Line Item) and Input Value To (Calculation Input) configuration), the generated import template will not indicate so and will list the inputs for the line item. These inputs may be left blank on the import, and when a calculation batch is run, the inputs will default as expected and line items will calculate with proper values.
For line items configured as Compensation/Custom Calculation (new) - System Base Salary or (spreadsheet) calculations configured with inputs of ANNUAL_SALARY_AMOUNT, HOME_FAMILY_SIZE, or HOST_FAMILY_SIZE: You may choose to leave the Input Value blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Compensation /Cost Estimate Batch is run.