Spend Data Import File Format

The following table provides the column descriptions for spend data that will be imported as a file. This file is typically structured by placing the column headers (CONTEXT_SUFFIX, UPDATE_DATE, etc.) in the first row and the spend data records starting in row 2. In addition to the first two batch identification columns, each data record has one spend amount, one spend date, optional dimension IDs to classify the spend, and optional attributes to describe the source of the data in more detail.

The column values in each row (including the header row) must be separated with either tabs or commas. The tab separator is recommended, and this is the default setting in the import action document, which initiates an import. The tab character is not expected to be contained in any column value.

Commas, on the other hand, are often found in some column values, so each value in these columns must be surrounded by double or single quotes (the Trim Quotes field in the import action document provides the choice of quote character, double quote being the default for comma-separated values). If the quote character itself can appear in the column value, it must be distinguished from the end-of-value delimiter by being doubled. For example, the following value in parentheses (Super "Wow" Enterprises, Inc.) would have to be transformed to ("Super ""Wow"" Enterprises, Inc.") in order to be processed correctly when the double quote is used.

If the ASCII character set is used for all the column values, the input file can contain 1 byte per character. If some non-ASCII characters are included, 1 byte per character is still supported if all these characters happen to be the same as the corresponding Unicode character, with zero-padding. Characters that need multiple bytes to be properly represented must be contained in a Unicode text file.

Column Name	Reqd	Target Data Type	Max Chars	Comment
CONTEXT_SUFFIX	X	Integer		Same for each row; get from spend config; leading zeros OK.
UPDATE_DATE	X	Date		Same for each row; Must be later than in previous import.
COMMODITY_ID		String	64	These are dimension IDs, the unique values defined in master data. Synonyms (aliases) may be used instead of dimension IDs if they have been added to a synonym-to-dimension-ID map.
SUPPLIER_ID		String	64
ORGANIZATION_ID		String	64
GEOGRAPHY_ID		String	64
AP_TYPE_ID		String	64
COST_CENTER_ID		String	64
CUSTOM1_ID		String	64
CUSTOM2_ID		String	64
CUSTOM3_ID		String	64
CUSTOM4_ID		String	64
SPEND	X	Decimal		Spend amount in currency defined in spend config. This is not converted during import.
SPEND_TYPE		String		ACTUAL (default), PLANNED, or FORECAST
SPEND_DATE	X	Date		Mapped to period in time dimension
TRANSACTION_DATE		Date		Date data changed on customer system; defaults to UPDATE_DATE if not provided
AGREEMENT_ID		String	32	Agreement unique doc name
AGREEMENT_LINE_ITEM		Integer		Not required, even when AGREEMENT_ID is provided
SOURCE_DOC_NUMBER		String	50	For example, PO number, Invoice number
SOURCE_DOC_LINE_ITEM		String	20
ITEM_ID		String	50	Also called material or part number
ITEM_DESCRIPTION		String	50
SUPPLIER_ITEM_ID		String	50
MANUFACTURER_ITEM_ID		String	50
ITEM_QUANTITY		Decimal
ITEM_QUANTITY_UNIT		String	10
UNIT_PRICE		Decimal
UNIT_PRICE_CURRENCY		String	3
AGREEMENT_PRICE		Decimal		Total item price; used to update agreement
AGREEMENT_PRICE_CURRENCY		String	3
GL_ACCOUNT_NUMBER		String	50
PAYMENT_TERMS		String	40
DELIVERY_TERMS		String	40
SHIP_TO_LOCATION		String	40
SHIP_FROM_LOCATION		String	40
MISC_STRING_1		String	40	Available for customer-specific details
MISC_STRING_2		String	40
MISC_STRING_3		String	40
MISC_NUMBER_1		Decimal
MISC_NUMBER_2		Decimal
MISC_DATE		Date

Note the following:

All dates must be in the format specified in the import action document (which defaults from the date format specified in the spend configuration document).
The COMMODITY_ID should be provided even if it is the same as the ITEM_ID. It is used to classify the spend in the commodity dimension.

Input File Columns Related to Agreement Update

The fields from SPEND to the bottom of the list are copied to the detail spend fact table. Most of these fields are for information only, available for reports that associate the spend data with these extra attributes.

Some of these fields are used to update agreements, assisting in compliance measurement. They are as follows:

SPEND_TYPE
SPEND_DATE
AGREEMENT_ID
AGREEMENT_LINE_ITEM
ITEM_QUANTITY
ITEM_QUANTITY_UNIT
UNIT_PRICE
UNIT_PRICE_CURRENCY
AGREEMENT_PRICE
AGREEMENT_PRICE_CURRENCY

The existence of an AGREEMENT_ID triggers an update of the identified agreement with the spend data during the import action. Agreement updates can lead to the generation of alerts and email notifications related to rebates and penalties. The AGREEMENT_ID must be the unique document name of a master agreement or sub-agreement. The AGREEMENT_LINE_ITEM is optional, but if it is provided, the agreement can be updated to the line item level, which allows more comprehensive compliance measurement.

The AGREEMENT_PRICE and AGREEMENT_PRICE_CURRENCY are required if the AGREEMENT_ID is set. The currency should be the same as the currency used in the agreement, which could be different than the currency used for the spend analysis environment for the context. The amount in the SPEND field must be in the latter currency.

If the AGREEMENT_LINE_ITEM is provided, ITEM_QUANTITY, ITEM_QUANTITY_UNIT, UNIT_PRICE and UNIT_PRICE_CURRENCY are required. The currency should be the same as the currency used in the agreement. The unit of measure should be the same unit as that which is used in the agreement line item quantity.

If these rules are not followed, the spend import will log the invalid agreement data and stop with a failure status.

Order of Columns in Input File

The import action's file loader supports a variable ordering of the columns and the omission of unused, optional columns. This support means that the column header row is required and each column header must be one of the well-known terms listed in the table above (case-insensitive). The well-known column headers will be used to interpret the values from the subsequent data records.

Increasing the Benefit of Spend Data

You can increase the benefit of the spend data by doing the following:

Make the dimension ID references as specific as possible.
The columns labeled COMMODITY_ID through CUSTOM4_ID are references to the dimension IDs. Each dimension has its own hierarchy, including the number of levels. The lover the level of the dimension ID, the more detail can be provided in spend reports. All detail information is automatically rolled up for reporting at the higher levels.
Define the desired time periods in a reporting calendar before the first import.
Once an import has completed, the processed data cannot be changed. Therefore, time periods should be predefined. SPEND_DATE is mapped to a time period in the calendar. If, for example, quarterly periods are used for one import, the SPEND_DATE is mapped to the appropriate quarter. If monthly periods are added later, subsequent imports will map each SPEND_DATE to the appropriate month. The data imported before this change will not be updated to map to a month rather than a quarter.