Zuora revenue data object model and data query
Frequently used object-based queries are provided in the following table for different scenarios:
| 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;
|