Query Billing Schedule objects through Data Query
The Billing Schedule feature is generally available. You can enable this feature in Sandbox environments with the self-serve interface. For more information, see Enable billing features by yourself. If you want to enable this feature in your Production environments, contact your Zuora account team for assistance.
With the Billing Schedule feature, you can perform queries on the objects related to this feature through Data Query, including the Invoice Schedule, Invoice Schedule Item, Orders, and Subscription objects. For information about the basic usage of Data Query, see Constructing SQL Queries in Data Query.
Invoice Schedule fields
The following table lists all the fields that are defined on the Invoice Schedule object. You can access the Invoice Schedule object and all its fields through Data Query and the Invoice Schedules API operations.
Field | Type | Description |
---|---|---|
deleted |
boolean |
Whether the invoice schedule is deleted. |
Id |
string |
The unique identifier of the invoice schedule. |
CreatedById |
string |
The ID of the user who created the invoice schedule. |
CreatedDate |
datetime |
The date and time when the invoice schedule is created. |
UpdatedById |
string |
The ID of the user who updated the invoice schedule. |
UpdatedDate |
datetime |
The date and time when the invoice schedule is last updated. |
TotalAmount |
decimal |
The total amount that needs to be billed through the processing of the invoice schedule. |
ActualAmount |
decimal |
The actual amount that needs to be billed totally through the processing of the invoice schedule. The actual amount might become different from the total amount due to additional order changes. |
BilledAmount |
decimal |
The amount that has been billed through the processing of the invoice schedule. |
UnbilledAmount |
decimal |
The amount that is waiting to be billed through the invoice schedule. |
NextRunDate |
date |
The run date of the next invoice schedule item waiting to be processed.
|
Number |
string |
The sequence number of the invoice schedule. |
Notes |
string |
The comments on the invoice schedule. |
AccountId |
string |
The unique ID of the customer account that the invoice schedule belongs to. |
Status |
string |
The status of the invoice schedule.
|
Invoice Schedule Item fields
The following table lists all the fields that are defined on the Invoice Schedule Item object. You can access the Invoice Schedule Item object and all its fields through Data Query.
Field | Type | Description |
---|---|---|
deleted |
boolean |
Whether the invoice schedule item is deleted. |
Id |
string |
The unique identifier of the invoice schedule item. |
CreatedById |
string |
The ID of the user who created the invoice schedule item. |
CreatedDate |
datetime |
The date and time when the invoice schedule item is created. |
UpdatedById |
string |
The ID of the user who updated the invoice schedule item. |
UpdatedDate |
datetime |
The date and time when the invoice schedule item is last updated. |
Amount |
decimal |
The amount of the invoice generated during the processing of the invoice schedule item. |
ActualAmount |
decimal |
The amount that is actually billed during the processing of the invoice schedule item. By default, the value of the |
RunDate |
date |
The date in the tenant’s time zone when the invoice schedule item is planned to be processed to generate an invoice. |
InvoiceScheduleId |
string |
The ID of the invoice schedule that the invoice schedule item belongs to. |
InvoiceId |
string |
The ID of the invoice that is generated during the processing of the invoice schedule item. |
CreditMemoId |
string |
The ID of the credit memo that is generated during the processing of the invoice schedule item. |
Status |
string |
The status of the invoice schedule item.
|
Orders fields
If you have the Billing Schedule feature enabled, the InvoiceScheduleId
field becomes available on the Orders object. You can access the Orders object and all its fields through Data Query and the Orders API operations.
Subscription fields
If you have the Billing Schedule feature enabled, the InvoiceScheduleId
field becomes available on the Subscription object. You can access the Subscription object and all its fields through Data Query and the Subscriptions API operations.
Construct SQL queries about the Billing Schedule objects
To query the objects related to the Billing Schedule feature, you can construct SQL queries by referring to the following SQL examples:
- Query the Invoice Schedule object based on the order number:
SELECT invs.number,invs.status,invs.totalamount,invs.nextrundate FROM invoiceschedule as invs, orders as o WHERE o.ordernumber='O-00001416' and o.invoicescheduleid=invs.id
The sample request queries the invoice schedule associated with the specified order. The sample query result is as follows:NUMBER STATUS TOTALAMOUNT NEXTRUNDATE ORDERNUMBER IS-00000167
PartiallyProcessed
800.000000000
2022-12-23
O-00001416
- Query the Invoice Schedule object based on the subscription number:
SELECT invs.number,invs.status,invs.totalamount,invs.nextrundate, s.name FROM invoiceschedule as invs, subscription as s WHERE s.name='A-S00001427' and s.status='Active' and s.invoicescheduleid=invs.id
The sample request queries the invoice schedule associated with the specified subscription. The sample query result is as follows:NUMBER STATUS TOTALAMOUNT NEXTRUNDATE NAME IS-00000167
PartiallyProcessed
800.000000000
2022-12-23
A-S00001427
- Query the Invoice Schedule Item object based on the invoice schedule number:
SELECT si.id, si.amount, si.runDate, si.status, invs.number,invs.status,invs.totalamount FROM invoicescheduleItem as si, invoiceschedule as invs WHERE si.invoicescheduleid=invs.id and invs.number='IS-00000167'
The sample request queries the invoice schedule items contained in the invoice schedule with the specified number. The sample query result is as follows:ID AMOUNT RUNDATE STATUS NUMBER TOTALAMOUNT 8a90f508841c1ef301842d0475fc3b27
500.000000000
2022-11-20
PartiallyProcessed
IS-00000167
800.000000000
8a90f508841c1ef301842d0476013b28
300.000000000
2022-12-23
PartiallyProcessed
IS-00000167
800.000000000