Query Billing Schedule objects through Data Query

Last updated
Save as PDF

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 Customer Success Manager 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. If the invoice schedule needs to be credited back due to additional change orders like subscription cancellation or product removal, the value of this field is the run date of the credit memo. If no additional invoices or credit memos are generated from the invoice schedule, the value of this field is null.
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. Pending Partially Processed Fully Processed

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 `ActualAmount` field is the same as that of the Amount field. The actual amount might become different from the original amount due to additional order changes.
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. Pending: The invoice schedule item is waiting to be processed. Executing: The invoice schedule item is being processed. Processed: The invoice has been generated during the processing 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