5.22 CSV File API for Activity Batch Processing

For Enterprises performing metered billing (e.g., billing for storage or bandwidth), there is now a new API that permits you to send large volumes of data in a traditional CSV file. While we've always had a REST based XML API for sending individual or batch data, this new enhancement provides clients with large data volumes a more efficient mechanism for transmitting millions of records. Everything from mainframes to database export utilities handle the ubiquitous CSV file format and the new file API permits you to send large files and retrieve a response file that shows the status of each record in the file. The File API also allows you to update previously inserted records.

1. Insert new activity records

POST    /file/activityBatch/uploadCsvFile

Notes: 

  • Perform the HTTP POST as a multi-part file object with filename set to "csvFile" (required).
  • The request will return immediately after the file is received, but processing of the file will be asynchronous. See the sections below for checking on the status of the processing.
  • To download a sample input CSV file, log into the application as an administrator and go to the [System >> Usage Activity] page from where you can download a CSV template file with the right format and sample data.

Return Codes:

  • HTTP 200 (OK) – On successfully receiving the file, the server HTTP response will include a single plaintext output that indicates the "Activity Batch ID". This Activity Batch ID must be used to query for the response file.
  • HTTP 400 (Client Error) – The response string will indicate the cause of the error. Example: "Invalid file format, column 1 - integer value expected"


2. Query for Response File

GET    /file/activityBatch/status/[Activity Batch Id]


Notes: 

  • The "Activity Batch ID" is returned when a POST or PUT operation to insert/update an activity CSV file returns successfully.
  •  This file will contain one line for each line in the input file. The file format is: status, activity_id, customer_id, order_id, orderLineItem_id, extRefId, errorDescription. The status values are OK_INSERT, OK_UPDATE  INVALID_CUSTOMER, INVALID_ORDER, OTHER_ERROR. 

Return Codes:

  • HTTP 200 (OK) – Asynchronous  processing has completed and the response CSV file is sent back as the HTTP response payload.
  • HTTP 202 (Accepted) – Asynchronous  processing has not completed, check back later.
  • HTTP 404 (Not Found)  - Invalid Activity Batch ID
  • HTTP 410 (Gone) – File not found. Files are retained only for 30 days.

 

3. Update existing activity records (try Update, then Insert)

PUT    /file/activityBatch/uploadCsvFile

Notes: 

  • A HTTP PUT operation (as opposed to the POST operation) indicates that an UPDATE is to be tried first and then an INSERT. The intended use case is when a previous POST (INSERT) operation had successfully inserted records and now these records need to be updated.
  • The transmitted file requires an additional column to be added to the beginning of each CSV record indicating the ActivityID. The Activity ID is returned in the Response CSV file (generated as a result of the initial POST/INSERT operation).
  • Perform the HTTP PUT as a multi-part file object with filename set to "csvFile" (required).
  • The request will return immediately after the file is received, but processing of the file will be asynchronous. See the sections below for checking on the status of the processing.
  • To download a sample input CSV file, log into the application as an administrator and go to the [System >> Usage Activity] page from where you can download a CSV template file with the right format and sample data.

Return Codes:

  • HTTP 200 (OK) – On successfully receiving the file, the server HTTP response will include a single plaintext output that indicates the "Activity Batch ID". This Activity Batch ID must be used to query for the response file.
  • HTTP 400 (Client Error) – The response string will indicate the cause of the error. Example: "Invalid file format, column 1 - integer value expected"


 4. Bulk Query for Activity

GET    /file/activityBatch/downloadCsvFile?beginDate=[yyyy-MM-dd]&endDate=[yyyy-MM-dd]&max=[x]&offset=[z]
Notes: 
  • This call is used to fetch the usage activity in bulk, typically used after the billing run has rated the activity (i.e., assigned the price). 
  • The beginDate and endDate are mandatory parameters
  • The max rows returned in a single file is 50,000 and offset parameter may be used to get larger ranges in batches
  • The CSV file format:
 Field NameData Type Notes 
 activity_idUnsigned Long  The internal ID of the activity record
 activity_batch_idUnsigned Long  The internal ID of the activity batch record
 ext_ref_id String The externally assigned ID of the activity record (optional)
 customer_id Unsigned Long  The internal ID of the customer record
 customer_ext_reference_no String The Customer Account number seen of the screen
 order_id Unsigned Long  The internal ID of the order record
 order_number String 
 order_line_item_id Unsigned Long  The internal ID of the order line item record
 charge_date Date The assigned billable date for the activity
 charge_end_date Date The assigned billable end date for the activity if different from above
 date_created DateTime Timestamp of initial creation
 last_updated DateTime Timestamp of last update
 status String Status values are "unbilled", 
 invoice_id Unsigned Long  The internal ID of the invoice record. This is assigned only after the activity has been "processed" (as indicated by the status column)
 invoice_number String The invoice number. This is assigned only after the activity has been "processed" (as indicated by the status column)
 invoice_line_item_charge_id Unsigned Long  The internal ID of the invoice line item record. This is assigned only after the activity has been "processed" (as indicated by the status column)
 invoice_text String The externally assigned invoice text if any
 unit_price Decimal The rated unit price for the invoice record. This is assigned only after the activity has been "processed" (as indicated by the status column)
 amount Decimal The rated amount for the invoice record. This is assigned only after the activity has been "processed" (as indicated by the status column)
 quantity Decimal The quantity assigned
 purchase_order_no String Custom field


Return Codes:

  • HTTP 200 (OK) – The CSV file is sent back as the HTTP response payload.
  • HTTP 202 (Accepted) – Asynchronous  processing has not completed, check back later.
  • HTTP 404 (Not Found)  - No data found for specified parameters

 


Comments