APPENDIX A – Generated Import Template
Appendix B - Sample Error/Log Messages
APPENDIX A – Generated Import Template
A Comp Calc\Cost estimate Flexible CSV Import template can be generated from an existing Compensation Template or Cost Estimate Template. Simply click the ‘Generate Import Template’ hyperlink near the bottom of the screen. The generated template will not be 100% complete. It is expected the user will have to add details/data, and may also have to modify the columns included, however; this template is a great starting point.
Generally, when a column is a higher level than the line item parameter input, only the first instance of data in that column within a given assignment/compensation calculation set (as indicated by the Cost Estimate Identifier), is used. Due to this, only the first row needs to be entered; however, data may be entered in subsequent rows within the same set for ease of use. If a value on a subsequent row is different than the original row, it will be ignored; the first value given will be used. There are a few exceptions which will be documented below.
The generated import template will create one set of rows for a single assignment/compensation calculation. If you wish to import multiple at once, simply copy the set of rows as many times as needed (as many assignment/compensation calculations you wish to import. Simply change the Cost Estimate Identifier to be unique for each set.
Generated Import Template – Compensation Template
The following columns are included in the generated template. NOTE: Some line item inputs state “This value is defaulted in the system and is optional.” But that is not always true; generally speaking only Annual Salary Amount, Home Family Size, and Host Family size will be refreshed in batch. You should test the import for one assignee and validate all inputs are as expected.
Cost Estimate Identifier – This column is used to group rows together for a given Assignment/Compensation Calculation. The identifier must be the same for a group of rows. The generated template will set this value to ‘a’ by default for each row for the given template.
COST_ESTIMATE!ASSIGNMENT_ID – Enter the Assignment ID. Only the first row of a group of rows with the same Cost Estimate Identifier needs to be entered. If you enter the Assignment ID on every row, the same ID must be entered for each row with a matching Cost Estimate Identifier; if a different ID is entered in a subsequent row within a group, it will be ignored.
COST_ESTIMATE!TEMPLATE_ID – The ID of the Compensation template you which to use. The template must be for the same company as the assignment or the import will error. The generated import template will enter this information for each row generated.
Template Name – The name of the Compensation Template. This column is for reference while creating the import. The column will be ignored by the import. The generated import template will enter this information for each row generated.
COST_ESTIMATE!DESCRIPTION – This is the description that will be used for each created compensation calculation. The generated import defaults this value to ‘DRAFT IMPORTED CALCULATION’, but you may override this value. The generated import template will enter this information for each row generated.
Home Tax Estimator Country – Include the display value of the Country, as it appears in the drop down list, when manually creating a compensation calculation.
Home Tax Estimator State/Province – Include the display value of the State/Province as it appears in the drop down list when manually creating a compensation calculation. The State/Province provided must belong to the Home Tax Estimator Country entered.
Host Tax Estimator Country – Not applicable and should be left blank, or the column removed.
Host Tax Estimator State/Province - Not applicable and should be left blank, or the column removed.
COST_ESTIMATE!BASE_SALARY – Enter the base salary for the assignment, to be used for the compensation calculations. The salary is assumed to be in the currency specified as the COST_ESTIMATE!BASE_CURRENCY. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Cost Estimate Batch is run.
COST_ESTIMATE!BASE_CURRENCY – Enter the 3 character ISO code for the data-entry currency to be used for the compensation calculation. This currency must be the home or host currency of the assignment or the base currency for the company. Data validation will be done during import to ensure the currency given matches one of these for the assignment, otherwise an error will be given.
COST_ESTIMATE!EFFECTIVE_DATE – Specify the Effective Date of the compensation calculation.
COST_ESTIMATE!MARITAL_STATUS – Indicate the marital status of the assignee; either Single or Married.
COST_ESTIMATE!HOME_FAMILY_SIZE – Specify the home family size to be used in the compensation calculation. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Compensation Batch is run.
COST_ESTIMATE!HOST_FAMILY_SIZE – Specify the host family size to be used in the compensation calculation. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Compensation Batch is run.
COST_ESTIMATE!REPORT_TITLE – Enter the desired Report Title. If one was specified in the Compensation Template, the generated import template will include this information for each row generated.
COST_ESTIMATE_CURRENCY_TRUE_UP!REFRESH_RATES_AUTOMATICALLY – The generated import template will default this to Yes. If you change this to No, you must enter additional columns to specify the exchange rate(s) to use. The additional columns may include:
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_FROM_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_FROM_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_TO_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_TO_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_COMPANY_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_COMPANY_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_SYSTEM_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_SYSTEM_EXCHANGE_RATE_DATE
COST_ESTIMATE_LINE_ITEM!NAME – The Description of the Line Item. The generated import template will list at least one row for each line item. Multiple rows for a given line item may exist if the line item has more than one Input Name. Do not change or delete the value in the COST_ESTIMATE_LINE_ITEM!NAME column. If you do, the import will not know which line item the row is for and will error. If you add additional rows because additional Input Name(s) must be added, be sure to enter the correct COST_ESTIMATE_LINE_ITEM!NAME for each row. If you do not, the import will assume the row is for a new/different line item and will error. Also, be sure all rows for a given line item are sorted together. That is, do not have row 2 and row 7 related to the same Line Item with information for other Line Items in between else the import will error.
COST_ESTIMATE_LINE_ITEM!CALCULATE – For each line item with Calculation configured as None, this value is No. For all others, this value is Yes. The generated import template will set this value per the Compensation Template. If a Line Item is configured to Calculate, per the Compensation Template configuration, but you wish to enter a fixed amount for the given assignment/cost estimate, change this to No. You cannot change the value from No to Yes, or the import will error.
COST_ESTIMATE_LINE_ITEM!PAYMENT_LOCATION – Home or Host. The generated import template will set this value per the Compensation Template. If the value is left blank, it will be treated as Host.
Currency Type – The Entry Currency as configured for the line item – Data Entry, Home, Host, Company, or System. This is informational only and will be ignored by the import. This is helpful if the line item is configured with Calculation of None and a fixed amount must be entered per assignment/compensation calculation.
COST_ESTIMATE_LINE_ITEM!AMOUNT_YEAR1 – If the line item is configured as Calculation type of Fixed Amount, the Amount will be displayed here. In these instances, the amount cannot be overridden; if it is, the import will ignore the value specified and use the amount per the Compensation Template. If the line item is configured as Calculation type of None (or if it is a line item set to Calculate, but the ‘Calculate’ checkbox is overridden in this import template, the fixed amount for the specified Assignment/Compensation Calculation should be entered. The amount must be entered in the Entry Currency for this line item; no additional currency field is available.
Input Name –Inputs needed for lines items that are set to Calculate and are not of Calculation type of Fixed Amount must be listed here. The value must be the display value of the input, as seen on the screen when a compensation calculation is created manually. The input must be associated with the correct COST_ESTIMATE_LINE_ITEM!NAME. Depending on the type of calculation, the generated import template will behave differently in determining which inputs are displayed. In any case, it is up to the user to verify the inputs are correct for each line item. If incorrect inputs are given for any line item, the import may error.
If the calculation is a <Spreadsheet> Calculation, the generated import template will display the inputs per the latest version of the calculation. When the compensation calculation is actually calculated, the calculation version used will be determined by the Effective Date specified. Therefore, if inputs are different per calculation versions, the import template may need to be adjusted (rows added or deleted).
If the calculation is a Stored Procedure, the generated import template will display the inputs needed.
If the calculation is an Expat4Cast tax calculation, the generated import template will not display any inputs because the inputs vary depending on the Tax Calculator Location of the assignment. It is up to the user to determine the correct Input Names for each line item.
Input Value – Enter the correct value for the given Input Name, as used when creating the compensation calculation manually. It is up to the user to determine the correct value.
Generated Import Template – Cost Estimate Template
The following columns are included in the generated template.
Cost Estimate Identifier – This column is used to group rows together for a given Assignment/Cost Estimate. The identifier must be the same for a group of rows. The generated template will set this value to ‘a’ by default for each row for the given template.
COST_ESTIMATE!ASSIGNMENT_ID – Enter the Assignment ID. Only the first row of a group of rows with the same Cost Estimate Identifier needs to be entered. If you enter the Assignment ID on every row, the same ID must be entered for each row with a matching Cost Estimate Identifier; if a different ID is entered in a subsequent row within a group, it will be ignored.
COST_ESTIMATE!TEMPLATE_ID – The ID of the Cost Estimate Template you which to use. The template must either be a system level template, or be for the same company as the assignment, or the import will error. The generated import template will enter this information for each row generated.
Template Name – The name of the Cost Estimate Template. This column is for reference while creating the import. The column will be ignored by the import. The generated import template will enter this information for each row generated.
COST_ESTIMATE!DESCRIPTION – This is the description that will be used for each created cost estimate. The generated import defaults this value to ‘DRAFT IMPORTED CALCULATION’, but you may override this value. The generated import template will enter this information for each row generated.
Home Tax Estimator Country – Include the display value of the Country, as it appears in the drop down list, when manually creating a compensation calculation.
Home Tax Estimator State/Province – Include the display value of the State/Province as it appears in the drop down list when manually creating a compensation calculation. The State/Province provided must belong to the Home Tax Estimator Country entered.
Host Tax Estimator Country Include the display value of the Country, as it appears in the drop down list, when manually creating a compensation calculation.
Host Tax Estimator State/Province - Include the display value of the State/Province as it appears in the drop down list when manually creating a compensation calculation. The State/Province provided must belong to the Host Tax Estimator Country entered.
COST_ESTIMATE!BASE_SALARY – Enter the base salary for the assignment, to be used for the cost estimate. The salary is assumed to be in the currency specified as the COST_ESTIMATE!BASE_CURRENCY. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Cost Estimate Batch is run.
COST_ESTIMATE!BASE_CURRENCY – Enter the 3 character ISO code for the data-entry currency to be used for the cost estimate. This currency must be the home or host currency of the assignment or the base currency for the company. Data validation will be done during import to ensure the currency given matches one of these for the assignment, otherwise an error will be given.
COST_ESTIMATE!EFFECTIVE_DATE – Specify the Effective Date of the cost estimate.
COST_ESTIMATE!MARITAL_STATUS – Indicate the marital status of the assignee; either Single or Married.
COST_ESTIMATE!HOME_FAMILY_SIZE – Specify the home family size to be used in the cost estimate. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Cost Estimate Batch is run.
COST_ESTIMATE!HOST_FAMILY_SIZE – Specify the host family size to be used in the cost estimate. You may choose to leave this field blank, if accurate data is entered on the Compensation screen. This value will be refreshed accordingly when a Cost Estimate Batch is run.
COST_ESTIMATE!REPORT_TITLE – Enter the desired Report Title. If one was specified in the Compensation Template, the generated import template will include this information for each row generated.
COST_ESTIMATE_CURRENCY_TRUE_UP!REFRESH_RATES_AUTOMATICALLY – The generated import template will default this to Yes. If you change this to No, you must enter additional columns to specify the exchange rate(s) to use. The additional columns may include:
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_FROM_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_FROM_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_TO_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_TO_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_COMPANY_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_COMPANY_EXCHANGE_RATE_DATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_SYSTEM_EXCHANGE_RATE
COST_ESTIMATE_CURRENCY_TRUE_UP!BASE_TO_SYSTEM_EXCHANGE_RATE_DATE
COST_ESTIMATE_LINE_ITEM!NAME – The Description of the Line Item. The generated import template will list at least one row for each line item. Multiple rows for a given line item may exist if the line item has more than one Input Name. Do not change or delete the value in the COST_ESTIMATE_LINE_ITEM!NAME column. If you do, the import will not know which line item the row is for and will error. If you add additional rows because additional Input Name(s) must be added, be sure to enter the correct COST_ESTIMATE_LINE_ITEM!NAME for each row. If you do not, the import will assume the row is for a new/different line item and will error. Also, be sure all rows for a given line item are sorted together. That is, do not have row 2 and row 7 related to the same Line Item with information for other Line Items in between else the import will error.
COST_ESTIMATE_LINE_ITEM!CALCULATE – For each line item with Calculation configured as None, this value is No. For all others, this value is Yes. The generated import template will set this value per the Compensation Template. If a Line Item is configured to Calculate, per the Cost Estimate Template configuration, but you wish to enter a fixed amount for the given assignment/cost estimate, change this to No. You cannot change the value from No to Yes, or the import will error.
COST_ESTIMATE_LINE_ITEM!PAYMENT_LOCATION – This column does not pertain to cost estimates and may be ignored. You may delete this column if you wish.
Currency Type – The Entry Currency as configured for the line item – Data Entry, Home, Host, Company, or System. This is informational only and will be ignored by the import. This is helpful if the line item is configured with Calculation of None and a fixed amount must be entered per assignment/compensation calculation.
COST_ESTIMATE_LINE_ITEM!AMOUNT_YEAR1 – If the line item is configured as Calculation type of Fixed Amount, the Amount will be displayed here. In these instances, the amount cannot be overridden; if it is, the import will ignore the value specified and use the amount per the Compensation Template. If the line item is configured as Calculation type of None (or if it is a line item set to Calculate, but the ‘Calculate’ checkbox is overridden in this import template, the fixed amount for the specified Assignment/Compensation Calculation should be entered. The amount must be entered in the Entry Currency for this line item; no additional currency field is available.
If you wish to enter amounts for more than 1 year, insert additional columns as needed (COST_ESTIMATE_LINE_ITEM!AMOUNT_YEAR2, COST_ESTIMATE_LINE_ITEM!AMOUNT_YEAR3, etc)
Input Name –Inputs needed for lines items that are set to Calculate and are not of Calculation type of Fixed Amount must be listed here. The value must be the display value of the input, as seen on the screen when a cost estimate is created manually. The input must be associated with the correct COST_ESTIMATE_LINE_ITEM!NAME. Depending on the type of calculation, the generated import template will behave differently in determining which inputs are displayed. In any case, it is up to the user to verify the inputs are correct for each line item. If incorrect inputs are given for any line item, the import may error.
If the calculation is a <Spreadsheet> Calculation, the generated import template will display the inputs per the latest version of the calculation. When the cost estimate is actually calculated, the calculation version used will be determined by the Effective Date specified. Therefore, if inputs are different per calculation versions, the import template may need to be adjusted (rows added or deleted).
If the calculation is a Stored Procedure, the generated import template will display the inputs needed.
If the calculation is an Expat4Cast tax calculation, the generated import template will not display any inputs because the inputs vary depending on the Tax Calculator Location of the assignment. It is up to the user to determine the correct Input Names for each line item.
Input Value – Enter the correct value for the given Input Name, as used when creating the cost estimate manually. It is up to the user to determine the correct value.
Appendix B - Sample Error/Log Messages
Log Message: Unexpected Error during SQL execution for table 'COST_ESTIMATE_LINE_ITEM' with message: Cannot insert the value NULL into column 'COST_ESTIMATE_TEMPLATE_LINE_ITEM_ID', table 'DEVQA03.dbo.COST_ESTIMATE_LINE_ITEM'; column does not allow nulls. INSERT fails.
You may see this error if you have overridden a COST_ESTIMATE_LINE_ITEM!LINE_ITEM_NAME with a value that is not actually a Line Item on the template.
Log Message: An error occurred while parsing headers.
You may see this error if you have a header that is not recognized by the import. You may have misspelled a table/field or you simply added a column that is not supported. You may also see this error if you have a column with no header.
Log Message: … WARNING: The field 'COST_ESTIMATE!ID' is read-only and will only be used as a recordIdentifier for updates.
You will see this warning when the import successfully imports a Cost Estimate or Compensation Calculation for an assignment.
Log Message: Record with Cost Estimate Identifier 'a' failed: Invalid tax estimator.
You will see this error when you have entered Host Tax Estimator information for a Compensation Calculation. You will also see this error if any of the four available Tax Estimators is not valid.