Billing Data Import

From Varibill Documentation
Jump to: navigation, search
Varibill is not a provisioning system. This means that you will provide/configure your client's products/services as you would normally. Varibill collects usage data from the source system and uses that as your billing data.

Billing data (Billing record) are records that relate to products / services rendered, for which a client must be billed.

Billing data can be retrieved from various sources and imported into Varibill, automatically or manually.

The Process at A Glance

ImportBillingData Active.png

Arrow.png

LinkRecord Gray.png

Automatic Data Import

This import can occur automatically through a facility called Source Collectors.

Manual Data Import

To import data manually an import file has to be prepared. The template can be obtained by selecting the "Download Template" link on the portal, Billing, Data Import. Please ensure that the file format matches the template available. Should you upload a file with multiple sheets, Varibill will validate and import only the first sheet. Multiple files can be imported at once. The maximum allowable size for all files to be uploaded is 100MB.

Parameters Explained

Record Type

(For File Upload ONLY) Each billing record must have a record type of "R". At the end of the upload file a record with record type "T" must indicate the total records ("R") expected in the file. The header for this column in the upload file is "RecordType". In the example provided, each row, except for the header row and the total row, has a record type of "R". The example file has four records, therefore the total ("T") will be "4". If an additional row were added, the value for "T" would be "5". The value for "T" must be specified in column B, in the same row as record type "T". In the example record type "T" is in row 6, therefore the value is populated in cell B6.

Client ID {ClientIdentifier}

This value will be used to link your billing record to an active contract. This value does not have to match the client's account code as it’s created in Varibill or in your accounting system. This value can be the value as extracted from the source system. You can link this value to a contract once the data has been imported using the "Client Identifiers" screen. The header for this column in the upload file is "ClientID". If a client ID does not yet exist in Varibill, and you validate your billing data (Billing Validation), Varibill will give an error for each client identifier that does not exist or has not been set up correctly. This value is never seen by your clients. The ClientID is limited to 150 characters.

Product Code {ProductCode}

This value should match the product code as it’s created in Varibill (Refer to Products). The header for this column in the upload file is "ProductCode". If the product does not yet exist in Varibill it can be crated at any time. When you validate your billing data (Billing Validation) Varibill will give an error for each product code that does not exist. The ProductCode is limited to 200 characters.

Record ID {RecordIdentifier}

The value of this field will be reflected on your client’s invoice once the client drills into the detail of a line item, i.e. view the invoice online and expand the line item. The header for this column in the upload file is "RecordID". Use a sensible description here. The Record ID is limited to 400 characters, but keep in mind that your clients would not want a paragraph per record on his/her invoice.

GUID {RecordGUID}

This value uniquely identifies the record, together with a few other fields (See Billing Record Import Status for more information). The header for this column in the upload file is "GUID". This value does not have to be a GUID / UUID [[1]], but is preferable. In the example provided a simple value such as "1" has been used. This value is never seen by your clients. The GUID is limited to 400 characters.

Last Seen Date {LastSeenDateTime}

The last seen date of a record indicates the date and (optionally) the time on which a record was seen. This date will be used when calculating pro-rated rates, where applicable.

This field must be in a valid date format and should preferably contain a timestamp (DATETIME data type e.g. 2019-01-21T08:17:36). Varibill follows the [|ISO 8601] standard which means the TZD (Time Zone Designator) must be Z or +hh:mm or -hh:mm or not provided. It is then also imperative to take the server, receiving the data, time zone into consideration. If your instance is hosted by Varibill then the timezone is GMT+02:00.

Herewith a few examples of each TZD format to follow
Example of data collected at source which has been converted into the Varibill API format using the TZD +hh:mm:</p>
  <soapenv:Body>
     <tem:records>
       <var:Record>
         <var:ClientIdentifier>7</var:ClientIdentifier>
         <var:LastSeenDateTime>2019-03-14T09:18:25+08:00</var:LastSeenDateTime>
         <var:ProductCode>43</var:ProductCode>
         <var:Quantity>188</var:Quantity>
         <var:RecordGUID>3gYSV0zLtYqiNQ8qN7PN</var:RecordGUID>
         <var:RecordIdentifier>Eisenhower</var:RecordIdentifier>
       </var:Record>
     </tem:records>
  </soapenv:Body>

Example of how the data is pulled into the Varibill database:</p>

DataExample.PNG


Example of data collected at the source which has been converted into the Varibill API format using the TZD -hh:mm:</p>
  <soapenv:Body>
     <tem:records>
       <var:Record>
         <var:ClientIdentifier>8</var:ClientIdentifier>
         <var:LastSeenDateTime>>2019-03-14T09:18:25-05:00</var:LastSeenDateTime>
         <var:ProductCode>44</var:ProductCode>
         <var:Quantity>314</var:Quantity>
         <var:RecordGUID>24ILbkJHMChvH8ofvkZE</var:RecordGUID>
         <var:RecordIdentifier>Pierce</var:RecordIdentifier>
       </var:Record>
     </tem:records>
  </soapenv:Body>

Example of how the data is pulled into the Varibill database:</p>

DataExample2.PNG

Example of data collected at the source which has been converted into the Varibill API format using the TZD "Z":</p>
 <soapenv:Body>
     <tem:records>
       <var:Record>
         <var:ClientIdentifier>8</var:ClientIdentifier>
         <var:LastSeenDateTime>>2019-03-14T09:18:25Z</var:LastSeenDateTime>
         <var:ProductCode>40</var:ProductCode>
         <var:Quantity>292</var:Quantity>
         <var:RecordGUID>ZMwfLzwQuJkUgGjxJyZ1</var:RecordGUID>
         <var:RecordIdentifier>Tyler</var:RecordIdentifier>
       </var:Record>
     </tem:records>
  </soapenv:Body>

Example of how the data is pulled into the Varibill database:</p>

DataExample3.PNG

Example of data collected at the source which has been converted into the Varibill API format without applying TZD:</p>
 <soapenv:Body>
     <tem:records>
       <var:Record>
         <var:ClientIdentifier>1</var:ClientIdentifier>
         <var:LastSeenDateTime>>2019-03-14T09:18:25</var:LastSeenDateTime>
         <var:ProductCode>6</var:ProductCode>
         <var:Quantity>453</var:Quantity>
         <var:RecordGUID>nHyNOfSxbsh5uc96qv51</var:RecordGUID>
         <var:RecordIdentifier>Van Buren</var:RecordIdentifier>
       </var:Record>
     </tem:records>
  </soapenv:Body>

Example of how the data is pulled into the Varibill database:</p>

DataExample4.PNG

If this is your first billing data import (automatically / manually) for a set of records, ensure you upload a file to indicate the date from which Varibill must start billing. As long as the client ID, product code, record ID and GUID combination remain the same for all billing data imports, for this record, Varibill will know the date from which it should start to bill.

The header for this column in your upload file is "LastSeenDate".

Quantity {Quantity}

The number of units that the client made use of.

The header for this column in the upload file is "Quantity". Be sure to import the records in the correct unit. The conversion rule selected for the Products will convert this quantity, if applicable, when calculating the line item value. This value can be supplied in number or decimal format ([precision = 18, scale = 5]). (Refer to Tax_and_Rounding for more information on how decimals are handled in Varibill).

Batch ID {batchIdentifier}

(For Source Collected Data ONLY) This field is optional and can be used to specify a unique ID per batch posted. This could be handy when a unique ID (In the form of a GUID) is available at source for a specific batch posted and you wish to tie back collected data to source batches or if you wish to group multiple posts into one batch. A batch could consist of one or multiple records. Varibill will generate a batch identifier, per post, if this value is not supplied.

Please Note

All fields ([Parameters explained]), are compulsory and must be provided during automatic or manual import.

One file can contain multiple products, records, sources' records, clients' records, etc. Just keep in mind that when you search for records on Billing Data Maintenance, it's easier to search by source name which is the name of the file imported.

File Upload Example

File Upload Example.xlsx

File Example.PNG

Billing Record Import Status

<ImportSourceDataResult>
   <BatchIdentifier>E27C2D09-9B1D-48AB-93F8-1864EAE7FF3B</BatchIdentifier>
   <DuplicateRecords>0</DuplicateRecords>
   <ElapsedMilliseconds>70.7545</ElapsedMilliseconds>
   <EndDateTime>2019-01-29T07:51:28.5265303+02:00</EndDateTime>
   <ExitCode>0</ExitCode>
   <Messages/>
   <NewRecords>107</NewRecords>
   <Outcome>Successful</Outcome>
   <ProcessedRecords>107</ProcessedRecords>
   <SourceIP>::1</SourceIP>
   <SourceKey>39ECECE1-9C9D-4487-8F1F-ECEB53630E4C</SourceKey>
   <SourceName>Example Source Collector</SourceName>
   <StartDateTime>2019-01-24T11:51:20.9557758+02:00</StartDateTime>
</ImportSourceDataResult>

BatchIdentifier

This will be the Batch Identifier sent in the request. If this value was not provided this value is system generated.

StartDateTime

Start Date time of the request.

Outcome

Request outcome.

ExitCode

A negative number will indicate a failure with the error message in the <Messages> field.

Exit Code Code Description Explanation
0 Successful No further action required. The data has been imported into Varibill and is available under [Data Maintenance].
-1 UnknownError An unknown exception has occurred. Contact [Support] for assistance.
-2 InvalidSourceKeyAndIPAddress The Source Key of the Source Collector does not match the Key of the Source Collector on Varibill, and / or the IP Address from which the Source Collector is communicating has not been set as a valid IP Address for the Source Collector on Varibill, and/or the Source Name of the Source Collector does not match the name of the Source Collector as set up on Varibill. (Refer to [Source Collector Setup Attributes Explained]
-3 NoServiceAccount The Varibill account has no users flagged as a service account. Contact [Support] for assistance.
-4 MutexFail The Source Collector could not get exclusive access to process the request. Please retry later.
-5 inactiveStatus The Source Collector is set to inactive. Refer to [Source Collector Setup Attributes Explained] to change the status of the Source Collector on Varibill.
-6 billingLock The Varibill account to which the Source Collector is posting is currently busy with billing processing and cannot currently accept new data. Please retry later.

SourceKey

Source Key received.

SourceName

Source Name received.

SourceIP

IP address of the requestor.

Messages

A message from Varibill, mostly used for errors.

ProcessedRecords

Number of records processed.

NewRecords

Number of unique new records seen.

DuplicateRecords

Number of records posted that is already in Varibill.

EndDateTime

End Date Time of the request.

ElapsedMilliseconds

Time in milliseconds that Varibill took to process the batch.

Next Steps

  • Billing Data Maintenance
  • Billing Validation
  • Link records to a contract (Client Identifiers)
  • Related

  • Frequently Asked Questions