Skip to main content

Query Billing Schedule objects through Data Query

Zuora

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.

  • 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