Invoice Analytics
This extract file interface handles records for each organisation to be created or updated in the CXP
Customer Experience Platform, or CXP, is MDS Global's self-service app that allows business customers to manage their own accounts and subscriptions. database.
Extract files in this set and their use are shown below.
-
invoice_load_control.dat
Controls various elements of the invoice loading process. It is polled for by the invoice data loader when it is looking for extracts to load. It contains a single load control record.
Invoice load control interface table
This extract file interface is used to control various elements of the invoice loading process. There is only a single record in this file per extract. The record format includes an indication of how many invoice extract file sets make up an extract, an optional dependent hierarchy extract ID and a series of extract file record counts for validation purposes. Data loaded via this interface is primarily bound for the LOAD_CONTROL and DATA_LOAD Lavastorm Spend Analyzer Admin schema tables.
Invoice load control interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_DATA_SOURCE_ID
Y
VarChar
40
An identifier for the instance of the CRM database from which the hierarchy data has been extracted.
EXTRACT_ID VarChar 40 A unique identifier for the current extract. This value will usually be derived by the CRM from a sequence that it controls and tracks. SET NUMBER Integer Identifies data as belonging to a given source database extract set (a sub division within an extract operation allowing multi-threading of the extract job, but not the load job). NUMBER_OF_ SETS_IN_EXTR ACT Integer The total number of extract sets that the extract has been divided into for extraction convenience. This value allows the data loader to recognise when all of the extract sets have been loaded in for an extract so that reconciliation operations can be carried out. INVOICE_LOAD _CONTROL_CO UNT Number of records in the invoice_load_control.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. INVOICE_COU NT Number of records in the invoice.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. INVOICE_LINE_ COUNT Numeric
Number of records in the invoice_line.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. USAGE_ITEM_ COUNT Numeric Number of records in the usage_item.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. DELIVERY_ADD RESS_COUNT Number of records in the delivery_address.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. INC_USAGE_A LLOC_COUNT Number of records in the inc_usage_alloc.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. INC_USAGE_U NITS_COUNT Number of records in the inc_usage_units.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. STATEMENT_COUNT Number of records in the statement.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. STATEMENT_T RANS_COUNT Number of records in the statement_trans.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. HIERARCHY_N ODE_CHECK _COUNT Number of records in the Hierarchy_Node_Check.dat extract file. Used to confirm that the contents of that extract file have been successfully delivered without truncation. INVOICE_DATE The financial date associated with each and every invoice in the current extract. This is the date printed on the invoice, the date used to apply tax and is generally the date from which the invoice due date is calculated. Note that every invoice in the extract will have the same invoice date/time. Invoices from different dates/times should be separated out into different sets or extracts. DATE_FORMAT DATE MASK "yyyymmddhh24miss". INVOICE_PRO DUCTION_ID 40 The identifier used by the CRM for the invoice production run used to create the invoices in the current extract. This value is used to aid data load reconciliation operations. HIERARCHY_EF FECTIVE_DATE This date/time is used to synchronise the invoice extract with the effective set of organisation unit hierarchies with which it is associated. That is to say that the date/time here represents a point in time at which the hierarchy structures presented in the invoice extract's 'hierarchy_node_check.dat' extract file can be found in the database in their entirety (all parent/child relationships match) and are effective on this date/time. Notice that each extract set supports only a single hierarchy effective date. Invoices associated with different hierarchy dates should be separated out into different sets or extracts. If an invoice data load is dependent on a customer hierarchy extract data load, then the date/time here would match the hierarchy effective date of the corresponding hierarchy extract (or at least represent a date/time somewhere between the date/time of the desired extract and that of a hierarchy extract that supersedes it). DATE_FORMAT DATE MASK "yyyymmddhh24miss" EXTRACTION_T IMESTAMP Date and time that the extract file set was generated. DATE_FORMAT DATE MASK "yyyymmddhh24miss" DEPENDENT_O N_HIERARCHY _EXTRACT When invoices are loaded, the various elements of the invoice (header, lines, usage, etc) are attached to the organisation units that own them in the organisation unit hierarchy. Customer hierarchy building operations are carried out by a spate data loader (customer hierarchy data loader). If an attempt is made to load invoices for which all owning hierarchy elements have not been previously loaded, then the data load will fail (the invoice extract data will be rejected). The DEPENDENT_ON_HIERARCHY_EXTRACT field allows the invoice data loader to be told that it must check that a particular customer hierarchy extract has been loaded first, before attempting to load the invoices for in this invoice extract. In this way the loading of customer hierarchy data and invoice data can be coordinated to prevent invoice extract rejection. If the invoice loader sees that a prerequisite of loading an invoice extract set is to first load a given customer hierarchy extract, then it will delay loading the invoice set until that has been done. -
invoice.dat
Contain a header record for each invoice in the extract set.
Invoice interface table
This extract file interface handles header records for each invoice in the extract set. Note that every invoice in the current extract file set must have the same invoice date. If it is required to load invoices with several different dates, then multiple extract file sets are required, one for each individual invoice date. Data loaded via this interface is primarily bound for the INVOICE Lavastorm Spend Analyzer Admin schema table.
Invoice interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL _INVOICE_ID
Y
VarChar
40
Unique invoice identifier as used by the originating CRM data base.
EXTERNAL_ORGANISATION_UNIT_ID VarChar 40 Unique identifier denoting the CRM name for the organisation unit that owns the invoice. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. There is an assumption that EXTERNAL_ORGANISATION_UNIT_ID values are only unique at the level within the hierarchy that are indicated to be at. Therefore, for example, a node with an identifier of 'ABC' at level 1 is considered to be a different node to a node with identifier 'ABC' at level 2. EXTERNAL_DELIVERY_ADDRESS_ID Integer (40 CHAR) A CRM derived delivery address identifier. This value provides a link to the delivery_address.dat extract file record that contains the details of the current invoice's delivery address. EXTERNAL _STATEMENT _ID (40 CHAR) The CRM derived identifier for the statement produced alongside the current invoice. INVOICE_DESCRIPTION (100 CHAR) A textual description for the current invoice. For example 'ongoing monthly invoice', final invoice' etc. INVOICE_DUE_DATE Numeric
Date that the invoice becomes due for payment. DATE_FORMAT DATE MASK "yyyymmddhh24miss" PREVIOUS_INVOICE_DATE Numeric Date of the previous invoice for the current organisation unit. This value is used to check for gaps in the invoice history of each customer. Missing invoices represent a risk because they might hide organisation unit hierarchy changes that have not been reflected in the database and thus open up the possibility of data corruption (attaching invoices to the wrong version of an organisation unit hierarchy node). When missing invoices are detected, the data loader will refuse to load the extract and flag up a warning. To load the extract, either the missing invoices need to be loaded first, or the data loader needs to be run in a special override mode. DATE_FORMAT DATE MASK "yyyymmddhh24miss" BILLING_FREQUENCY (40 CHAR) This value is only required for on cycle invoices. It can be left NULL for off cycle invoices. BILLING_FREQUENCY is an enumerated type. Possible values would include: Daily WeeklyMonthly Quarterly Yearly PAYMENT_TYPE (40 CHAR) The payment type that is expected to be used to pay off this invoice. This value is usually derived from the payment scheme the customer has registered to use with the CRM. INVOICE_FORMAT (40 CHAR) An indication of the billing format used to produce the original invoice. One of the elements used to determine the formatting of invoices by Lavastorm Spend Analyzer. TAXATION_DOMAIN (40 CHAR), The market area (geographical or other) with which the current invoice is associated. One of the elements used to determine the formatting of invoices by Lavastorm Spend Analyzer. Locale (12 CHAR) A set of preference information related to the invoice's language, environment and/or cultural conventions. When combined with the preferences associated with the application user, the Locale is used to determine the formatting of invoices by Lavastorm Spend Analyzer ATTRIBUTE_1 (100 CHAR) Customisable invoice data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_2 (100 CHAR) Customisable invoice data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_3 40 (100 CHAR) Customisable invoice data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. -
invoice_line.dat
Contains a record for each charge line associated with an invoice in the extract set.
Invoice line interface table
This extract file interface handles the charge line records belonging to the invoices in the extract set. Data loaded via this interface is primarily bound for the INVOICE_LINE Lavastorm Spend Analyzer Admin schema table.
Invoice line interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL _INVOICE_ID
Y
VarChar
40
The CRM derived identifier of the invoice to which the current invoice line belongs.
EXTERNAL_ INVOICE_LINE_ID VarChar 40 CRM derived identifier for the current invoice line within its invoice. For example, a line number or similar. EXTERNAL_ORGANISATION_UNIT_ID Unique identifier denoting the CRM name for the organisation unit that owns the current invoice line. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. CHARGE_DESCRIPTION A textual description of the service being charged for by the current invoice line. Note: This field maps to INVOICE_LINE_DESCRIPTION in the Lavastorm Spend Analyzer database CHARGE_GROUP A name for a logical grouping of invoice line charges. This grouping can be used to drive data selection and collection in reports over invoice charge data. CHARGE_SUB_GROUP Numeric
A name for a logical grouping of invoice lines within a charge group. Again, this grouping can be used to drive data selection and collection in reports over invoice charge data. CHARGE_START_DATE Numeric The date/time on which the charged for service began. DATE_FORMAT DATE MASK "yyyymmddhh24miss" CHARGE_END_DATE The date/time on which the charged for service ended. AMOUNT (14,4) This value represents a monetary contribution made to the total invoice charge by this invoice line. There are two main categories of invoice line (see the invoice_line_category interface field). - If the invoice line is a tax line then the charge amount represents the tax associated with the charges for the current organisation unit and tax code. - If the invoice line is NOT a tax line, then the charge amount represents a net value (not inclusive of a tax component). TAX_RATE (14,4) The rate of tax that has been applied to a net charge type invoice line amount, or the rate of tax relevant to a tax type invoice line. TAX_CODE (40 CHAR), The identifying code for tax generated from a net charge type invoice line, or the code for the tax being shown on a tax type invoice line. TAX_CODE_DESCRIPTION A user readable description for the tax code. BILLED QUANTITY The billed quantity of billable units consumed by the services charged for by the current invoice line. This can differ from the gross quantity for various reasons, such as inclusive quantity discount schemes. UNIT_OF_MEASURE The unit of measure that qualifies the gross and billed quantity. UNIT_OF_MEASURE_CATEGORY 40 The categorisation for the unit of measure used on the current record. It allows records to be grouped together for reporting purposes. For example, inclusion of all charges that have a particular unit of measure category. Note that the unit of measure and unit of measure category combination given must be one of the valid combinations configured within the Lavastorm Spend Analyzer database. Note that it is possible for any particular unit of measure value to be used in conjunction with different unit of measure categories (it is not a one to one relationship). The category supplied acts to place a record in that category for reporting and analysis. INVOICE_LINE_CATEGORY An enumerated type. Tax lines have a category of 'TAX'. Other lines currently default to an open category with a NULL value and are all deemed to be non-tax lines. ATTRIBUTE_1 Customisable invoice line data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_2 Customisable invoice line data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by ATTRIBUTE_3 Customisable invoice line data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by TAX_LINE '1' (Yes/True) or '0' (No/False). Is the current invoice line a dedicated tax line? SERVICE_TYPE Defines the type of service that is being charged for by the current invoice line and used to differentiate the invoice line for reporting purposes (e.g. for report inclusion, exclusion, sorting and grouping). NETWORK_TYPE The type of the network, the use of whose services resulted in the raising of the current invoice line charge and used to differentiate the invoice line for reporting purposes (e.g. for report inclusion, exclusion, sorting and grouping). RECURRING_CHARGE '1' (Yes/True) or '0' (No/False). Is the current invoice line a recurring charge applied to the customer’s invoice every billing cycle, as part of their contract for example? NET_OF_TAX_SUMMARY This value applies to tax lines only. - If the invoice line is a tax line then the net of tax summary amount represents the total net value of charges associated with the current organisation unit and tax code. - If the invoice line is NOT a tax line, then this value should be left empty. -
usage
The consumption of services, for example a subscriber using call minutes._item.datContains a record for each charge line sub item (for example, a phone call) associated with invoice lines in the current extract set.
This extract file interface handles the usage item (itemised breakdown) records belonging to the invoices in the extract set.
This extract file will contain a record for each charge line sub item, such as a phone call, associated with invoice lines in the current extract set.
Data loaded via this interface is primarily bound for the USAGE_ITEM Lavastorm Spend Analyzer Admin schema table.
Usage item interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_USAGE_ITEM_ID
Y
VarChar
40
This value is used for reference with the inclusive usage data within the current extract. It must be unique within the extract, but is preferably unique to the usage item within the CRM.
EXTERNAL _INVOICE_ID VarChar 40 The CRM derived identifier of the invoice to which the current usage item line belongs. EXTERNAL_ INVOICE_LINE_ID VarChar CRM derived identifier for the current invoice line to which the current usage item line belongs. EXTERNAL_ORGANISATION_UNIT_ID VarChar Unique identifier denoting the CRM name for the organisation unit that owns the current invoice line. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. USAGE_ITEM_DESCRIPTION VarChar A textual description of the current usage item. As standard, for telecoms call data, Lavastorm Spend Analyzer takes this to be the number dialled. USAGE_ITEM_START_DATE_TIME Numeric
The date and time on which the current usage charge began. DATE_FORMAT DATE MASK "yyyymmddhh24miss USAGE_ITEM_END_DATE_TIME Numeric The date and time on which the current usage charge ended. DATE_FORMAT DATE MASK "yyyymmddhh24miss" GROSS_QUANTITY Integer The total number of billable units consumed by the current usage record. INCLUSIVE_QUANTITY Integer The number of billable units of the current usage record absorbed by an inclusive usage allocation (i.e. the units discounted from the gross quantity prior to rating). BILLED_QUANTITY Integer The number of units of the current usage record actually rated and billed. UNIT_OF_MEASURE (40 CHAR), The unit of measure that qualifies the gross, inclusive and billed quantity. UNIT_OF_MEASURE_CATEGORY The categorisation for the unit of measure used on the current record. It allows records to be grouped together for reporting purposes. for example, inclusion of all charges that have a particular unit of measure category. Note: The unit of measure and unit of measure category combination given must be one of the valid combinations configured within the Lavastorm Spend Analyzer database. Note: It is possible for any particular unit of measure value to be used in conjunction with different unit of measure categories (it is not a one to one relationship). The category supplied acts to place a record in that category for reporting and analysis. GROSS_VALUE NUMBER(14,4) The total monetary value of the current usage record before any inclusive bundles and discounts are applied. INCLUSIVE_VALUE NUMBER(14,4) The monetary value of the current usage record absorbed by an inclusive usage bundle allocation. Store as a positive value. DISCOUNT_VALUE NUMBER(14,4) The monetary value of discounts applied to the current usage record. Store as a positive value. BILLED_VALUE NUMBER(14,4) The billed monetary value of the current usage item record after any inclusive bundles and discounts have been applied. Billed value = gross value – inclusive value – discount value CUSTOMER_COST_CENTRE An identifier allowing the charges from the current usage record to be attributed to a division within a business organisation. Optional. INCLUSIVE_USAGE_STATUS Text description describing how inclusive usage schemes have operated on the current usage item. Optional. TAX_CHARGED '1' (Yes/True) or '0' (No/False). Is this usage item subject to taxation? Optional. ATTRIBUTE_1 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_2 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_3 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_4 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_5 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_6 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_7 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_8 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_9 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ATTRIBUTE_10 Customisable usage item data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by CXP. ITEM_TYPE Used to apply a type to the usage item to differentiate it for reporting purposes (e.g. for report inclusion, exclusion, sorting and grouping). -
delivery_address.dat
Contains a list of the delivery addresses associated with the invoices in the current extract set.
This extract file interface handles the delivery address records associated with the invoices in the current extract set.
Data loaded via this interface is primarily bound for the CONTACT_DETAILS Lavastorm Spend Analyzer Admin schema table.
Delivery address interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_DELIVERY_ADDRESS_ID
Y
VarChar
40
Unique delivery address identifier as used by the originating CRM data base.
TITLE VarChar 40 Addressee's title (Mr, Mrs, and so on). FIRST_NAME VarChar Addressee's forename. MIDDLE_NAME VarChar Addressee's middle name. SURNAME VarChar Addressee's surname. JOB_TITLE VarChar Addressee's job title (used as required in a business address) COMPANY_NAME VarChar
The company name used on a corporate type customer's address. ADDRESS_LINE_1 VarChar First geographical address line. ADDRESS_LINE_2 VarChar Second geographical address line. ADDRESS_LINE_3 VarChar Third geographical address line. ADDRESS_LINE_4 VarChar Fourth geographical address line. ADDRESS_LINE_5 VarChar Fifth geographical address line. ADDRESS_LINE_6 VarChar Sixth geographical address line. POSTAL_CODE Geographical post code or zip code. TELEPHONE_NUMBER Contact telephone number (optional for a pure delivery address). ALTERNATIVE_TELEPHONE_NUMBER Secondary contact telephone number (optional for a pure delivery address). FAX_NUMBER Contact fax number (optional for a pure delivery address). ADDRESS_TYPE Gives an indication of the nature of the current address and what it is used for. An address can represent different things such as an invoicing address or straight customer contact details. For invoice delivery address purposes this value can take values such as 'Business' or 'Personal' to give an indication of the kind of addressee involved. -
inc_usage_alloc.dat
Contains a record for each inclusive usage allocation utilised by usage items in the current extract data set.
This extract file interface handles the details of the inclusive usage allocations utilised by usage items associated with the invoices in the current extract set.
It is possible for usage items to be discounted by inclusive usage schemes (bundles). This extract file contains a record for each inclusive usage allocation utilised by usage items in the current extract data set
Data loaded via this interface is primarily bound for the INC_USAGE_INVOICE_SUMMARY and INC_USAGE_ALLOCATION_SNAPSHOT Lavastorm Spend Analyzer Admin schema tables.
Inclusive usage allocations interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_ INC_USAGE_ALLOC_ID
Y
VarChar
40
Unique inclusive usage allocation discount scheme identifier as used by the originating CRM data base.
EXTERNAL_ORGANISATION_UNIT_ID VarChar 40 Unique identifier denoting the CRM name for the organisation unit that owns the current inclusive usage allocation. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. INC_USAGE_ALLOC_NAME VarChar A name for the inclusive usage scheme in use on the current record. This value can be used to hold a CRM specific code for the scheme. INC_USAGE_ALLOC_DESCRIPTION VarChar A user friendly description for the inclusive usage scheme in use on the current record. QUANTITY_BROUGHT_FORWARD NUMBER(14,4) The number of allocation units carried into the current allocation from previous allocations. QUANTITY_ALLOCATED NUMBER(14,4)
The total number of units available to the current discount allocation when it was first created. QUANTITY_USED NUMBER(14,4)
The number of allocation units used up at the time of billing. QUANTITY_UNUSED NUMBER(14,4)
The number of allocation units remaining. QUANTITY_CARRIED_FORWARD NUMBER(14,4)
The number of allocation units that will be carried over to future allocations. UNIT_OF_MEASURE VarChar The unit of measure that qualifies the various quantities used on the current allocation record. UNIT_OF_MEASURE_CATEGORY VarChar The categorisation for the unit of measure used on the current record. It allows records to be grouped together for reporting purposes. For example, inclusion of all charges that have a particular unit of measure category. Note: The unit of measure and unit of measure category combination given must be one of the valid combinations configured within the Lavastorm Spend Analyzer database. Note: It is possible for any particular unit of measure value to be used in conjunction with different unit of measure categories (it is not a one to one relationship). The category supplied acts to place a record in that category for reporting and analysis. ATTRIBUTE_1 VarChar Customisable inclusive usage allocation data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_2 Customisable inclusive usage allocation data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. ATTRIBUTE_3 Customisable inclusive usage allocation data element. This field is usually used to hold a value specific to the original CRM data base's billing profile, which is not otherwise directly supported by Lavastorm Spend Analyzer. -
inc_usage_units.dat
Contains records that indicate how much of an inclusive usage allocation was consumed by a usage item.
This extract file interface handles the inclusive usage unit records that indicate how many inclusive usage allocation units have been consumed by usage items in the current extract set.
Note that it is possible for an individual usage item to be discounted by none, one, or many inclusive usage schemes.
Each record indicates how much of an inclusive usage allocation was consumed by a usage item.
The association between inc_usage_units.dat records and the corresponding usage_item.dat record is made via the EXTERNAL_USAGE_ITEM_ID field.
Data loaded via this interface is primarily bound for the INCLUSIVE_USAGE_ITEM Lavastorm Spend Analyzer Admin schema table.
Inclusive usage units interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_USAGE_ITEM_ID
Y
VarChar
40
This Value is used for reference with usage item data (see usage_item_Interface). It is used to associate the current inclusive usage units record with the usage item record that it has discounted.
EXTERNAL_ INC_USAGE_ALLOC_ID VarChar 40 Unique inclusive usage allocation discount scheme identifier as used by the originating CRM data base. This value indicates which allocation the current inclusive usage unit record has acquired its units from to discount a usage item. EXTERNAL _INVOICE_ID VarChar The CRM derived identifier of the invoice to which the usage item discounted by the current inclusive usage item record belongs. INCLUSIVE_QUANTITY NUMBER(14,4) The number of allocation units used up when discounting the associated usage item record. UNIT_OF_MEASURE VarChar The unit of measure that qualifies the inclusive quantity value. UNIT_OF_MEASURE_CATEGORY VarChar The categorisation for the unit of measure used on the current record. It allows records to be grouped together for reporting purposes. For example, inclusion of all charges that have a particular unit of measure category. Note: The unit of measure and unit of measure category combination given must be one of the valid combinations configured within the Lavastorm Spend Analyzer database. Note: It is possible for any particular unit of measure value to be used in conjunction with different unit of measure categories (it is not a one to one relationship). The category supplied acts to place a record in that category for reporting and analysis. -
statement.dat
Contains account
In the Cloud Monetisation Platform, a billing entity that can be used to manage payments on one or more subscriptions or payments for services. An account can hold details such as payments or invoices. statement header for selected hierarchy nodes associated with invoices in the current extract.This extract file interface handles header records for each statement in the extract set.
Data loaded via this interface is primarily bound for the STATEMENT Lavastorm Spend Analyzer Admin schema table.
Statement interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_STATEMENT_ID
Y
VarChar
40
Unique statement identifier as used by the originating CRM data base.
EXTERNAL_ORGANISATION_UNIT_ID VarChar 40 Unique identifier denoting the CRM name for the organisation unit that owns the current statement. This will be a posting account against charges are levied and for which the opening balance on the current statement is applicable. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. EXTERNAL_DELIVERY_ADDRESS_ID VarChar A CRM derived delivery address identifier. This value provides a link to the delivery_address.dat extract file record that contains the details of the current statement's delivery address. OPENING_BALANCE NUMBER(14,4) The amount due (closing balance) brought forward from the previous statement period. Note: To calculate the closing balance for the statement period you would do the following calculation: Closing balance = Opening Balance + Transaction total. The queried total can then be used to adjust the customer's outstanding balance for credit control pursuit purposes. TRANSACTION_TOTAL NUMBER(14,4) The total value of the transactions associated with the statement owner, raised since the previous statement was produced. This value is generated from the total of invoice, payment and adjustment transactions generated this period (since the last statement). Following the basic rules of accounting, if the transaction total is a net credit to the account it should be a negative value and if it is a net debit to the account is should be a positive value. Example: When calculating this value: A PAYMENT should be a negative value if it credits the account. An ADJUSTMENT that credits the account should be a negative value, one that debits the account a positive value. An INVOICE total should generally be a positive value (to debit the account), but this depends on the nature of the invoice. QUERIED_TOTAL NUMBER(14,4) This value represents the total of any charges that are in dispute by the customer. Queried amounts place a credit against an account whilst the disputed charges are investigated. This credit is designed to give the customer the benefit of the doubt and prevent create control activity from acting against their account. QUERIED_TOTAL should be a negative amount if a charge is in dispute (i.e. the queried amount acts as a credit on the account). STATEMENT_TEXT Textual Information associated with the current statement. STATEMENT_DATE Statement production date. The date at which the calculated closing balance for the current statement is correct. DATE_FORMAT DATE MASK "yyyymmddhh24miss" -
statement_trans.dat
Contains details of each monetary transaction that occurred between the time of the opening and closing balances on statements in the current extract.
This extract file interface handles the statement transaction records belonging to each statement in the extract set.
When a statement transaction represents an invoice already loaded into Lavastorm Spend Analyzer, an Invoice_transaction record is generated in the database that bridges between the statement transaction and the corresponding invoice.
Data loaded via this interface is primarily bound for the STATEMENT_TRANSACTION and INVOICE_TRANSACTION Lavastorm Spend Analyzer Admin schema table.
Statement transaction interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_STATEMENT_ID
Y
VarChar
40
CRM derived statement Identifier for the statement to which the current statement transaction belongs.
EXTERNAL_STATEMENT_TRANS_ID VarChar 40 Unique statement transaction identifier as used by the originating CRM data base. This value will generally represent the identifier for a document/transaction entry in the CRM's ledger. EXTERNAL_ORGANISATION_UNIT_ID Unique identifier denoting the CRM name for the organisation unit that owns the current statement transaction. ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. TRANSACTION_AMOUNT NUMBER(14,4) The monetary value of the current transaction. Transactions can be in one of three categories and should follow the basic rules of accountancy according to category: A PAYMENT should be a negative value if it credits the account. An ADJUSTMENT that credits the account should be a negative value, one that debits the account a positive value. An INVOICE total should generally be a positive value (to debit the account), but this depends on the nature of the invoice. TRANSACTION_TEXT Textual Information associated with the current statement transaction. For example, a note against the transaction in the ledger. TRANSACTION_CATEGORY Transactions can be in one of three categories: Invoice, Payment, or Adjustment. TRANSACTION_DATE The date of the transaction. DATE_FORMAT DATE MASK "yyyymmddhh24miss EXTERNAL _INVOICE_ID Used if the transaction refers to an invoice. This value is used by the database to create a bridge between statement transactions and invoices. -
Hierarchy_Node_Check.dat
Contains a list of all of the hierarchy nodes associated with invoice elements in the current invoice extract set. Each node record also contains the ID of its parent node. This data is used to ensure that all invoice extract data elements can be successfully attached to a valid organisation unit hierarchy within the Lavastorm Spend Analyzer database.
This extract file interface is used to validate that all invoice components in the invoice extract can be successfully attached to hierarchy nodes already stored in the Lavastorm Spend Analyzer database.
The hierarchy_node_check.dat extract file must contain a record for every hierarchy node that is associated with invoicing data in the extract.
Each hierarchy_node_check.dat record must correspond with an existing organisation_unit_snapshot table record in the database and that organisation_unit_snapshot record must be effective on the invoice date used within the current invoice extract.
To correspond with an organisation unit snapshot record, the parent and child nodes included on the hierarchy_node_check.dat record must both match
If an effective match cannot be found for any records in the hierarchy_node_check.dat file, then the current extract file set is rejected.
Hierarchy node check interface table fields
Column Heading
Unique?
Type
Length
Description
EXTERNAL_ORGANISATION_UNIT_ID
Y
VarChar
40
Unique identifier denoting an organisation unit element within a customer's organisation unit hierarchy.
ORGANISATION_UNIT_LEVEL Integer The level within the organisation unit hierarchy, at which the current organisation unit resides. EXTERNAL_PARENT_ORG_UNIT_ID Identifier denoting the parent organisation unit of the current organisation unit within the organisation unit hierarchy. If the current organisation unit has no parent (it is at the top of its hierarchy) then the parent value will be NULL. PARENT_ORG_UNIT_LEVEL The level within the organisation unit hierarchy, at which the parent organisation unit resides.