home

Zuora Revenue

Zuora BillingZuora PaymentsZuora PlatformZuora CPQZephrZuora RevenueAccounts ReceivableZuora AIRelease NotesBasicsEntitlementsSupport and policies
Zuora BillingZuora PaymentsZuora PlatformZuora CPQZephrZuora RevenueAccounts ReceivableZuora AIRelease NotesBasicsEntitlementsSupport and policies

Welcome to Zuora Product Documentation

Explore our rich library of product information

Show Contents

thumbnailZuora Revenue

Revenue data access and models

Zuora revenue data object model and data query

Explore frequently used object-based queries for different revenue scenarios in Zuora's data object model.

Frequently used object-based queries are provided in the following table for different scenarios:

Note:

To enable Data Query using platform capabilities, you must enable the ENABLE_DATAQUERY_SERVICE profile in your Zuora Revenue tenant. Navigate to Setups > Profiles and enable this profile.

Sample queries on this page are designed to run in Data Query against Zuora Revenue objects. They are not supported in Data Query Utility, which is intended for querying tables rather than objects.

Category

Scenario

Query

Revenue for the period

Total revenue for the period

 
select PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) Revenue
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R', 'A')
and AccountingTypeID in ('R','X')
and INITIAL_REP_ENTRY_FLAG = 'N'
group by PostingPeriodID;

Contractual revenue for the period

 
select PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) Revenue
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R')
and AccountingTypeID in ('R')
and INITIAL_REP_ENTRY_FLAG = 'N'
group by PostingPeriodID;

Adjustment revenue for the period

 
select PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) Revenue
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('A')
and AccountingTypeID in ('X')
and INITIAL_REP_ENTRY_FLAG = 'N'
group by PostingPeriodID;

Filter by other revenue types

 
select PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) Revenue
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG = <Schedule Type>
and AccountingTypeID = <Accounting Type>
and INITIAL_REP_ENTRY_FLAG = 'N'
group by PostingPeriodID;

Balance of account types for the period

 
select AccountingTypeID, sum(TransactionCreditAmount - TransactionDebitAmount) Balance
from REVENUECONTRACTACCOUNTINGENTRIES
where AccountingTypeID in ('L')
and PostingPeriodID <= 202006
group by AccountingTypeID;

Balance of account types by schedule types for the period

 
select AccountingTypeID, SCHD_TYPE_FLAG, sum(TransactionCreditAmount - TransactionDebitAmount) Balance
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R')
and AccountingTypeID in ('L')
and PostingPeriodID <= 202006
group by AccountingTypeID, SCHD_TYPE_FLAG;

Netting activity for the period

 
select AccountingTypeID, PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) Activity
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R')
and AccountingTypeID in ('L')
and PostingPeriodID = 202006
group by AccountingTypeID, PostingPeriodID;

LT/ST activity for the period

 
select AccountingTypeID, PostingPeriodID, sum(TransactionCreditAmount - TransactionDebitAmount) LTST_Activity
from REVENUECONTRACTACCOUNTINGENTRIES
where AccountingTypeID in ('E', 'F')
and PostingPeriodID = 202006
group by AccountingTypeID, PostingPeriodID;

Revenue by customer

 
select CustomerName, sum(TransactionCreditAmount - TransactionDebitAmount) TotalRevenue
from REVENUECONTRACTACCOUNTINGENTRIES rcae,
REVENUECONTRACTLINESDIMENSIONS rcld
where rcae.RootRevenueContractLineID = rcld.RevenueContractLineID
and rcae.SCHD_TYPE_FLAG in ('R','A')
and rcae.AccountingTypeID in ('R','X')
and rcae.INITIAL_REP_ENTRY_FLAG = 'N'
and rcae.PostingPeriodID = 202112
group by CustomerName;

Revenue by RC

 
select rcld.RevenueContractID, sum(TransactionCreditAmount - TransactionDebitAmount) TotalRevenue
from REVENUECONTRACTACCOUNTINGENTRIES rcae,
REVENUECONTRACTLINESDIMENSIONS rcld
where rcae.RootRevenueContractLineID = rcld.RevenueContractLineID
and rcae.SCHD_TYPE_FLAG in ('R','A')
and rcae.AccountingTypeID in ('R','X')
and rcae.INITIAL_REP_ENTRY_FLAG = 'N'
and rcae.PostingPeriodID = 202112
group by rcld.RevenueContractID;

Revenue by RC

 
select rcld.RevenueContractID, sum(TransactionCreditAmount - TransactionDebitAmount) TotalRevenue
from REVENUECONTRACTACCOUNTINGENTRIES rcae,
REVENUECONTRACTLINESDIMENSIONS rcld
where rcae.RootRevenueContractLineID = rcld.RevenueContractLineID
and rcae.SCHD_TYPE_FLAG in ('R','A')
and rcae.AccountingTypeID in ('R','X')
and rcae.INITIAL_REP_ENTRY_FLAG = 'N'
and rcae.PostingPeriodID = 202112
group by rcld.RevenueContractID;

Revenue by Item

 
select rcld.ItemNumber, sum(TransactionCreditAmount - TransactionDebitAmount) TotalRevenue
from REVENUECONTRACTACCOUNTINGENTRIES rcae,
REVENUECONTRACTLINESDIMENSIONS rcld
where rcae.RootRevenueContractLineID = rcld.RevenueContractLineID
and rcae.SCHD_TYPE_FLAG in ('R','A')
and rcae.AccountingTypeID in ('R','X')
and rcae.INITIAL_REP_ENTRY_FLAG = 'N'
and rcae.PostingPeriodID = 202112
group by rcld.ItemNumber;

Product category

 
select rcld.ProductCategory, sum(TransactionCreditAmount - TransactionDebitAmount) TotalRevenue
from REVENUECONTRACTACCOUNTINGENTRIES rcae,
REVENUECONTRACTLINESDIMENSIONS rcld
where rcae.RootRevenueContractLineID = rcld.RevenueContractLineID
and rcae.SCHD_TYPE_FLAG in ('R','A')
and rcae.AccountingTypeID in ('R','X')
and rcae.INITIAL_REP_ENTRY_FLAG = 'N'
and rcae.PostingPeriodID = 202112
group by rcld.ProductCategory;

Bookings & billings

Total bookings for the period

 
select rcld.CreatedAccountingPeriodID, sum(ExtendedSellPrice) booked_amount
from REVENUECONTRACTLINESDIMENSIONS rcld,
REVENUECONTRACTLINESFACTS rclf
where rcld.RevenueContractLineID = rclf.RevenueContractLineID
and rcld.CreatedAccountingPeriodID = 202111
group by rcld.CreatedAccountingPeriodID
order by rcld.CreatedAccountingPeriodID;

Total billings for the period

 
select rcbd.CreatedAccountingPeriod, sum(BillingExtendedSellPrice) Billed_amount
from REVENUECONTRACTBILLSDIMENSIONS rcbd,
REVENUECONTRACTBILLSFACTS rclf
where rcbd.RevenueContractBillID = rclf.RevenueContractBillID
and rcbd.CreatedAccountingPeriod = 202111
group by rcbd.CreatedAccountingPeriod
order by rcbd.CreatedAccountingPeriod;

Accounting

Account activity for the period, along with the posted flag

 
select AccountingTypeID, PostingPeriodID, INTERFACED_FLAG, sum(TransactionCreditAmount - TransactionDebitAmount) Activity
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R')
and AccountingTypeID in ('L')
and PostingPeriodID = 202006
group by AccountingTypeID, PostingPeriodID, INTERFACED_FLAG;

Account balance as of a specific period by Account Type

 
select AccountingTypeID, SCHD_TYPE_FLAG, sum(TransactionCreditAmount - TransactionDebitAmount) Balance
from REVENUECONTRACTACCOUNTINGENTRIES
where SCHD_TYPE_FLAG in ('R')
and AccountingTypeID in ('L')
and PostingPeriodID <= 202006
group by AccountingTypeID, SCHD_TYPE_FLAG;

Waterfall prior period amount and current activity by as-of period

This query returns waterfall-related accounting entries as of a selected accounting period and splits amounts into prior period amounts and current activity based on POSTINGPERIODID.


 SELECT
    C.ACCOUNTINGPERIODID AS ASOFPERIODID,
    S.REVENUECONTRACTID,
    S.REVENUESCHEDULEID,
    S.REVENUECONTRACTLINEID,
    S.ROOTREVENUECONTRACTLINEID,
    S.SCHEDULEDPERIODID,
    S.POSTINGPERIODID,
    S.REVENUEORGANIZATIONCODE,
    S.REVENUEBOOKID,
    S.REVENUECLIENTID,
    S.ACCOUNTINGSEGMENT,
    S.ACCOUNTINGTYPEID,
    S.NETTING_ENTRY_FLAG,
    S.SCHD_TYPE_FLAG,
    CASE
        WHEN S.POSTINGPERIODID < C.ACCOUNTINGPERIODID
        THEN (S.TRANSACTIONDEBITAMOUNT + S.TRANSACTIONCREDITAMOUNT)
    END AS TPRIORPRDAMOUNT,
    CASE
        WHEN S.POSTINGPERIODID < C.ACCOUNTINGPERIODID
        THEN (S.FUNCTIONALDEBITAMOUNT + S.FUNCTIONALCREDITAMOUNT)
    END AS FPRIORPRDAMOUNT,
    CASE
        WHEN S.POSTINGPERIODID < C.ACCOUNTINGPERIODID
        THEN (S.REPORTINGDEBITAMOUNT + S.REPORTINGCREDITAMOUNT)
    END AS RPRIORPRDAMOUNT,
    CASE
        WHEN S.POSTINGPERIODID >= C.ACCOUNTINGPERIODID
        THEN (S.TRANSACTIONDEBITAMOUNT + S.TRANSACTIONCREDITAMOUNT)
    END AS TACTIVITY,
    CASE
        WHEN S.POSTINGPERIODID >= C.ACCOUNTINGPERIODID
        THEN (S.FUNCTIONALDEBITAMOUNT + S.FUNCTIONALCREDITAMOUNT)
    END AS FACTIVITY,
    CASE
        WHEN S.POSTINGPERIODID >= C.ACCOUNTINGPERIODID
        THEN (S.REPORTINGDEBITAMOUNT + S.REPORTINGCREDITAMOUNT)
    END AS RACTIVITY,
    S.CREATEDACCOUNTINGPERIOD,
    S.CREATEDDATE,
    S.CREATEDBY,
    S.UPDATEDDATE,
    S.UPDATEBY,
    S.INCREMENTALREFESHDATE
FROM REVENUECONTRACTACCOUNTINGENTRIES S,
     REVENUECALENDAR C,
     REVENUECONTRACTACCOUNTINGTYPE A
WHERE S.CREATEDACCOUNTINGPERIOD <= C.ACCOUNTINGPERIODID
  AND S.ACCOUNTINGTYPEID = A.REVENUEACCOUNTTYPEID
  AND A.WATERFALL_FLAG = 'Y'
  AND C.ACCOUNTINGPERIODNAME = 'MAY-23';
Note:

  • Replace ACCOUNTINGPERIODNAME = 'MAY-23' with the required as-of period

  • Prior period: POSTINGPERIODID < ACCOUNTINGPERIODID

  • Activity: POSTINGPERIODID >= ACCOUNTINGPERIODID

Last updated: August 10, 2026