Cost Import

Import of actual costs and field costs into Aucerna Execute

Overview

Aucerna Execute can be set to import actual costs and field costs from an external system. Cost imports can be defined by the following options:

1. Import Type
   • Field Costs
   • Actual Costs
2. Source Type
   • File – Import from a CSV file of a known format
   • Database table or view – Database table or view of a known format. Can be a link to a table or view outside of the Aucerna Execute database schema
3. Cost Type
   • Total – A single cost is booked against a single AFE
   • Monthly – A single cost is booked against a month for an AFE
   • Line Item – A single cost is booked against a specific month and account for an AFE
4. Calculation Method
   • Calculate Net – The import supplies only a gross amount. Aucerna Execute calculates the net amount as (gross amount) × (sum of affiliate interest)
   • No Calculation – The import supplies both a gross and a new amount. Aucerna Execute imports the amounts as supplied.
   • Calculate Gross – The import supplies only a net amount. Aucerna Execute calculates the gross amount as (net amount) ÷ (sum of affiliate interest)

Cost Sources

Table or View

Every installation of Aucerna Execute includes sample configuration for importing actual costs or field costs:

• synchronize_actuals_table.config.sample
• synchronize_actuals_table_external.config.sample
• synchronize_fieldcosts_table.config.sample
• synchronize_fieldcosts_table_external.config.sample

Find these in the “plugins_available\synchronization” folder.

Table Columns

Both actual cost imports and field cost imports require the same set of columns. Here are the required columns per given cost type:

• Total
  • AFE_NUMBER
  • GROSS_AMOUNT
  • NET_AMOUNT
• Monthly
  • AFE_NUMBER
  • AMOUNT_DATE
  • GROSS_AMOUNT
  • NET_AMOUNT
• Line Item
  • AFE_NUMBER
  • AMOUNT_DATE
  • ACCOUNT
  • GROSS_AMOUNT
  • NET_AMOUNT

Aucerna Execute’s expected column types:

Column Name	Oracle	SQLServer	Nullable
AFE_NUMBER	VARCHAR2(50)	nvarchar(50)	No
AMOUNT_DATE	DATE	datetime	No
ACCOUNT	VARCHAR2(50)	nvarchar(50)	Yes
GROSS_AMOUNT	NUMBER	decimal(28, 14)	Yes
NET_AMOUNT	NUMBER	decimal(28, 14)	Yes

CSV

• CSV columns should appear in the same order as listed in the Table Columns section above
• CSV column headers should be capitalized
• CSV files should be placed in the location configured in uploads.config
• applicable sample configuration:
  • synchronize_actuals_file.config.sample
  • synchronize_actuals_file_manual.config.sample
  • synchronize_fieldcosts_file.config.sample
  • synchronize_fieldcosts_file_manual.config.sample

uploads.config

This file configures where Aucerna Execute expects CSV files for field costs and actual costs.

Sample “uploads.config” file:

<castle>
<properties>
<uploadsPath>#{networkPath}\Uploads</uploadsPath>
<!-- *** These folders are subfolders of the <uploadsPath> *** -->
<fieldCostsSubFolder>FieldCosts</fieldCostsSubFolder>
<actualsSubFolder>Actuals</actualsSubFolder>
</properties>
</castle>

In this example, Aucerna Execute now expects a Folder named “Uploads”. Within that folder, it expects folders named “FieldCosts” and “Actuals”. The expected folder structure is shown below:

Enabling Cost Import

The folder at “plugins_available\synchronization” contains files which are meant to be good starting points. Copy a cost import sample into the “plugins” folder and rename the file to remove the “.sample” extension to get started.

This folder also contains files for synchronizing other data. Only files with names starting with “synchronize_actuals“ or “synchronize_fieldcosts“ relate to cost imports.

1. synchronize_actuals_file_manual.config.sample – Configures actual costs to be imported manually from a CSV file.
2. synchronize_fieldcosts_file_manual.config.sample – Configures field costs to be imported manually from a CSV file.
3. synchronize_actuals_file.config.sample – Configures a scheduled actual cost import from a CSV file. Requires parameters for file location, file names and a schedule.
4. synchronize_fieldcosts_file.config.sample – Configures a scheduled field cost import from a CSV file. Requires parameters for file location, file names and a schedule.
5. synchronize_actuals_table.config.sample – Configures a scheduled actual cost import from a database table or view. Requires parameters for the database table and a schedule.
6. synchronize_fieldcosts_table.config.sample – Configures a scheduled field cost import from a database table or view. Requires parameters for the database table and a schedule.

Example Configuration

Configuration files for field costs imports and actual imports are near-identical. These examples will use configuration for actual imports.

The configuration files are written such that behavior can be changed by modifying only the first block, the <properties> element. The rest of the file generally does not need to be changed.

Example CSV Configuration

<castle>
<properties>
<actual_cost_sync_path>C:\Aucerna Execute\Uploads\Actuals</actual_cost_sync_path>
<actual_cost_sync_inputFileMask>actuals.csv</actual_cost_sync_inputFileMask>
<actual_cost_sync_schedule>month=*:date=*:dayOfWeek=1,2,3,4,5:hour=00:minute=00</actual_cost_sync_schedule>
<actual_cost_sync_user>System Admin</actual_cost_sync_user>
</properties>
...

Property	Definition
actual_cost_sync_path	The folder whereAFENavigator will look for this task’s cost import source files.
actual_cost_sync_inputFileMask	The name of the cost import source file.
actual_cost_sync_schedule	Schedule defining when this task runs. (For more information on scheduling syntax, see Syntax in the Data Synchronization guide).
actual_cost_sync_user	The userAFENavigator will use to run synchronization.

Example Table or View Configuration

<castle>
<properties>
<actual_costs_import_query>
select AFE_NUMBER, AMOUNT_DATE, ACCOUNT, GROSS_AMOUNT, NET_AMOUNT
from actualcosts_external
</actual_costs_import_query>
<actual_db_cost_sync_schedule>month=*:date=*:dayOfWeek=1,2,3,4,5:hour=0:minute=0</actual_db_cost_sync_schedule>
</properties>
...

Property	Definition
actual_costs_import_query	The query Aucerna Execute will use to retrieve costs. Must return expected columns (see Table Columns).
actual_cost_sync_schedule	Schedule defining when this task runs. (For more information on scheduling syntax, see Syntax in the Data Synchronization guide).

Cost import from a database does not require a defined user. This task runs as the system user, with access to all permissions.

Required Permissions

Import Type	Privilege
Actual Costs	Import Actual Costs on AnyAFE
Field Costs	Enter Field Costs on AnyAFE

Settings

Some scheduled imports do not define a date. Administrators can define the timeframes for these imports by going to Tools > Configuration, under the category “AFE/Scheduled Cost Import”. Two types of date ranges are available: Absolute Date, and Current Date. Select one under the option “Import timeframe type”.

Absolute Date

Two values need to be defined:

• Beginning Date
  • Beginning Year
  • Beginning Month
• Ending Date
  • Ending Year
  • Ending Month

Scheduled imports will always read from the date defined here. An administrator must change these values before the next scheduled cost import. Otherwise, the cost import overwrites costs for what is now an outdated date range.

Current Date

This configuration defines a sliding window of months to import costs for based on how many months before or after the current date.

Field Cost Submitted after Import

This setting controls whether field costs are submitter immediately after import.

Running a Manual Import

Manual imports are accessible under the Import Actual Costs and Import Field Costs menu items in the Tools menu.

Logging

All cost import details are logged to a file. When the cost import is running, a unique log file will be created in the logs directory on the Aucerna Execute server. With manually triggered imports, in addition to generating a log file stored to the server, the log’s contents are displayed to the user.

Click image to expand or minimize.