Retrieve rating details about usage charges or records
The rating and billing of usage charges with the Pre-Rated Pricing or Multi-Attribute Pricing charge models depend on user-entered data into custom fields, custom objects, or the price formula. In these areas, strict data validation cannot be guaranteed at the point of data entry. Therefore, it is expected that errors might occur during the rating process.
If you are using one of these charge models, errors might occur in any of the following situations:
- Usage records are loaded with invalid data, for example, the
rate__ccustom field has a blank value or an invalid numerical value, likeabc. - Custom object records are loaded with invalid data, similar to custom fields.
- No custom object records might match the
objectLookup()function. - Errors occur in the price formula calculation, for example, divided by 0.
If any of the preceding situations exists, the corresponding bill run will fail, and you will see the following error message displayed on the bill run details page:
There are subscriptions that failed to generate invoice. See details below.
To find the cause of the failure for these charge models only, or to understand the calculation steps, you can use Data Query to retrieve details about usage charges and usage records involved in the bill run through the Zuora UI or API. Before the retrieve, you have to obtain the related reference ID from the bill run details page through the Zuora UI, or from the error message returned in the response body of the corresponding operation through the API.
If the bill run succeeds, you can obtain the related reference ID from the bill run details page through the Zuora UI, or from the Zuora-Request-Id header in the corresponding API response.
Two data objects are available with usage rating information.
-
ChargeRatingDetail - rating details at the charge level
-
UsageRatingDetail - rating details at the usage record level
Zuora stores ChargeRatingDetail and UsageRatingDetail data for seven calendar days after the data is created.
The Pre-Rated Pricing charge models and Multi-Attribute Pricing charge model are available for customers with Enterprise and Nine editions by default. If you are a Growth customer, see Zuora Editions for pricing information.
Retrieve rating details through Data Query
Perform the following steps to retrieve the rating details about usage charges or usage records through Data Query:
- On the bill run details page, find more information including the failure reason in the Failed Accounts or Subscriptions tab.
- In the left navigation menu, navigate to Platform > Data Query.
- Create a data query to retrieve error details at the charge level, and save the data query results.
- Create a data query to retrieve error details at the usage record level, and save the data query results.
ChargeRatingDetail columns
The following table lists the ChargeRatingDetail columns that are displayed in the data query results at the charge level.
| Column | Description |
|---|---|
| BILLINGPERIODENDDATE | The end date of the billing period. |
| BILLINGPERIODSTARTDATE | The start date of the billing period. |
| CHARGENUMBER | The number of the corresponding rate plan charge. |
| CREATEDBYID | The ID of the user who initiated the calculation for this rate plan charge. |
| CREATEDDATE | The date and time when this detail record was created. |
| ERRORDETAILS | The details of the error. |
| ERRORRECORDCOUNT | The total number of usage records with errors. |
| ID | The ID of the charge rating detail. |
| REFERENCE | The number of the corresponding bill run or request that triggered the calculation. |
| SUCCESSRECORDCOUNT | The number of usage records. |
| UPDATEDBYID | The ID of the user who last updated this record, should always match CreatedById. |
| UPDATEDDATE | The date and time when this record was last updated, should always match CreatedDate. |
UsageRatingDetail columns
The following table lists the UsageRatingDetail columns that are displayed in the data query results at the usage record level.
| Column | Description |
|---|---|
| CALCULATIONDETAILS | The detailed price formula for calculation and resolved lookup values. |
| CHARGENUMBER | The number of the corresponding rate plan charge. |
| CHARGERATINGDETAILID | The ID of the corresponding charge rating detail. |
| CREATEDBYID | The ID of the user who initiated the calculations for this usage record. |
| CREATEDDATE | The date and time when this record was created. |
| ERRORDETAILS | The details of the error. |
| ERRORSEQUENCE | The sequence of the records in error. Use this to go thru all of the errors, once this field equals the ChargeRatingDetail.ErrorRecordCount. |
| ID | The ID of the usage rating detail. |
| RECORDSEQUENCE | The sequence of the usage record calculation, given all the usage records considered for this charge. |
| REFERENCE | The number of the corresponding bill run or request that triggered this calculation. |
| SUCCESS | Whether the usage record was calculated successfully. |
| UPDATEDBYID | The ID of the user who last updated this record, should always match CreatedById. |
| UPDATEDDATE | The date and time when this record was last updated, should always match CreatedDate. |
| USAGEID | The ID of the usage record. |
Use case for retrieving rating details through Data Query
Imagine a scenario where:
- You created a subscription with two usage charges, one with the Pre-Rated Pricing charge model and the other with the Pre-Rated Per Unit charge model. The charges are configured to look at two separate custom fields for their rate data,
rate__candtotal__c. - Two usage records were uploaded against the subscription, meaning they will be rated by both charges mentioned above.
- One of the usage records was loaded with only one of the custom fields containing the appropriate rate information that the charges required for proper calculation.
- You created a bill run, BR-00000041, to rate and bill the two uploaded usage records.
The bill run failed. To find the failure cause, retrieve details about each data object, for the two usage records.
Retrieve rating details at the charge level
To retrieve rating details at the charge level, enter the following SQL query into the text box on the Create Data Query page:
Select * from ChargeRatingDetail where Reference = 'BR-00000041'
After running the data query, you can download the query result in CSV format. The following table lists the information contained in the CSV file
| Column | Row value |
|---|---|
| billingperiodenddate | 20/8/1 |
| billingperiodstartdate | 20/7/1 |
| chargenumber | C-00000098 |
| createdbyid | 11e643f4-a3f8-a9da-b061-0025904c57d6 |
| createddate | 2020-07-17T23:33:51.802Z |
| errordetails | {"errorCode":"UNKNOWN_ERROR","additionalDetails":"Failed to rate one or more usages, please see usage level details"} |
| errorrecordcount | 1 |
| id | fbe29496-b50f-429e-b32b-2dea5e0a4c5f |
| reference | BR-00000041 |
| successrecordcount | 1 |
| updatedbyid | 11e643f4-a3f8-a9da-b061-0025904c57d6 |
| updateddate | 2020-07-17T23:33:51.802Z |
The value of the errordetails column indicates that an error occurs at the usage record level. You can retrieve rating details at the usage record level for more information.
Retrieve rating details at the usage record level
To retrieve rating details at the usage record level, enter the following SQL query into the text box on the Create Data Query page:
Select * from UsageRatingDetail where Reference = 'BR-00000041'
After running the data query, you can download the query result in CSV format. The following table lists the information contained in the CSV file
| Column | Usage record 1 | Usage record 2 |
|---|---|---|
| calculationdetails | {"formula":"UsageQuantity() * fieldLookup(\"usage\", \"rate__c\")","fieldLookups":{"usage.rate__c":1.0}} | |
| chargenumber | C-00000098 | C-00000098 |
| chargeratingdetailid | fbe29496-b50f-429e-b32b-2dea5e0a4c5f | fbe29496-b50f-429e-b32b-2dea5e0a4c5f |
| createdbyid | 11e643f4-a3f8-a9da-b061-0025904c57d6 | 11e643f4-a3f8-a9da-b061-0025904c57d6 |
| createddate | 2020-07-17T23:33:52.026Z | 2020-07-17T23:33:52.048Z |
| errordetails | {"errorCode":"MISSING_CUSTOM_FIELD","additionalDetails":"Invalid value for for pre-rated custom field [rate__c], required a numeric value, was: null"} | |
| errorsequence | 1 | |
| id | 2d862214-fc6b-4480-a5c3-8af35a437ec2 | a856046f-af4f-4cef-ba70-b865901394f0 |
| recordsequence | 1 | 2 |
| reference | BR-00000041 | BR-00000041 |
| success | TRUE | FALSE |
| updatedbyid | 11e643f4-a3f8-a9da-b061-0025904c57d6 | 11e643f4-a3f8-a9da-b061-0025904c57d6 |
| updateddate | 2020-07-17T23:33:52.026Z | 2020-07-17T23:33:52.048Z |
| usageid | 2c92c0f8735614e101735f1fef4179df | 2c92c0f8735614e101735f1fcd137922 |
The first usage record, based on therecordSequencefield value 1, shows success, as expected, since a proper value for the rate__c custom field was set.
The value of the errordetails field for usage record 2 indicates that an invalid value was specified for the rate__c custom field -- it was missing. You have to specify a valid numeric value for the rate__c custom field of the usage record so that the usage record can be uploaded successfully.
Please note that even after correcting the rate__c custom field on usage record 2, usage record 1 will likely fail, if the other custom field, the total__c field, is not set for both usage records.