Skip to main content

Zuora revenue data object model and data query

Zuora

Zuora revenue data object model and data query

  1. Last updated
  2. Save as PDF
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;