Zuora Revenue data object model

Revenue object data model

The following diagram is a high-level view of how key data objects are related to one another within Zuora Revenue.

Revenue objects and descriptions

The following objects are present in the data model. Click the name of each object for more details.

REVENUECALENDAR: Contains the details of the accounting calendar that are configured in Zuora Revenue.

REVENUECONTRACT: Contains the details of distinct revenue contracts (RC) based on the defined RC grouping template.

REVENUECONTRACTACCOUNTINGENTRIES: Contains the accounting entries / journal entries of every transaction based on its performance obligation template.

REVENUECONTRACTACCOUNTINGSEGMENTS: Contains the Accounting segments details for deferring and releasing segments of every accounting entry.

REVENUECONTRACTACCOUNTINGTYPE: Contains the accounting type details of every Accounting entries.

REVENUECONTRACTACTIONS: Contains the details of every revenue contract POB's release and deferral actions thats are taken by both the system and manual operation.

REVENUECONTRACTAPPROVALS: Contains the details of revenue contract approvals and their history.

REVENUECONTRACTBILLSDIMENSIONS: Contains the billing transaction dimensional details, which include transaction types such as INV, CM, CM-R, CM-C, CM-RO, and RORD.

REVENUECONTRACTBILLSFACTS: Contains the billing transaction fact, which includes transaction types such as INV, CM, CM-R, CM-C, CM-RO, and RORD.

REVENUECONTRACTCOSTDIMENSIONS: Contains the cost transaction dimensional details of both standard standard and user-defined cost.

REVENUECONTRACTCOSTFACTS: Contains the cost transaction fact, which includes both standard and user-defined costs.

REVENUECONTRACTHOLDS: Contains the details of revenue contract hold at all the line level, POB level, and Revenue Contract level.

REVENUECONTRACTLINESDIMENSIONS: Contains the sales order transaction dimensional details.

REVENUECONTRACTLINESFACTS: Contains the sales order transaction fact details.

REVENUECONTRACTMJENTRIESDIMENSIONS: Contains the manual journal transaction dimensional details.

REVENUECONTRACTMJENTRIESFACTS: Contains the Manual journal transaction fact details.

REVENUECONTRACTVCDIMENSIONS: Contains the variable consideration (VC) transaction dimensional details of both standard and user-defined VC.

REVENUECONTRACTVCFACTS: Contains the variable consideration transaction fact, which includes both standard and user-defined VC.

REVENUEPERIODS: Contains the open period details.

REVENUECONTRACTNETTINGSTATUS: Contains period-wise contract's asset/liability position of the revenue contract. This is applicable if you are using CA/CL Netting in Zuora Revenue.

REVENUEACCOUNTINGSUMMARY - This object provides the calculated fields such as the opening balance, additions, releases, activity, and ending balances for the various shedule types & account segment values. The balances are maintained period-wise at a Revenue Contract line level. With this object, you can directly create revenue reports in the data warehouse.

REVENUECONTRACTROLLFORWARD- Use this object to access the Roll forward report directly from your data warehouse.

Revenue objects data dictionary

The Revenue Objects Data Dictionary provides you with the following information:

Object and field level details between Revenue Objects and BI Views Object to help you transition from BI View Objects to the new Revenue Object Model.
Field-level definitions of revenue objects.

Chronology

Changes in release 2024.Q4.0.0

RevenueContractMJEEntriesDimensions — We have added the below fields.
- JEHeadStatusFlag - Status of Manual Journal
- JEHeadReversalStatusFlag - Reversal Status of the Manual Journal
- JEHeadTypeFlag - Manual Journal Type
RevenueContractAccountingEntries — We have added the below fields.
- PostBatchName- GL Transfer Batch Name
- CreditFlag - This field is consumed internally by Zuora.
- Negative accounting entries representation

If you have implemented the older version (Version 1) of the Waterfall sample query in your SnowFlake data warehouse, note that it may lead to incorrect revenue waterfall extract (because of the changes introduced to the AccountingEntries object in this release). We recommend using the updated waterfall sample query (Version 2) to factor for the change in the negative accounting entries representation. This is a one time change.

Until now — Any Negative accounting entries generated by Zuora Revenue were represented as negative debits and negative credits in the RevenueContractAccountingEntries object as shown below.

AccountingSegment	TransactionDebitAmount	TransactionCreditAmount	AccountingTypeID
1001	-100		L
4001		-100	R

Starting with this release, negative debits will be displayed as positive credits, and negative credits will be displayed as positive debits, as shown in the table below. With this change, the accounting representation in the AccountingEntries object will match the Accounting Report run from Zuora Revenue Reports.

AccountingSegment	TransactionDebitAmount	TransactionCreditAmount	AccountingTypeID
1001		100	L
4001	100		R

Until now — the manual journal (MJE) accounting schedules were represented as single-sided (debit) entries that displayed the amount as positive and negative amounts, as shown below.

AccountingSegment	TransactionDebitAmount	TransactionCreditAmount	AccountingTypeID
1001	100		L
4001	-100		R

Starting from this release, note that the MJE accounting schedules will be represented as double-sided entries with debits and credits as shown below.

AccountingSegment	TransactionDebitAmount	TransactionCreditAmount	AccountingTypeID
1001	100		L
4001		100	R

Creating views based on the data model

The following sample queries show how to create the views that are required for building any use case-specific reports or analytical dashboards.

Line level transactional report sample query

The following sample query shows how to get the line-level transactional report with limited fields including the booking, billing, and accounting details. You can customize the query to add any number of attributes to the line level transaction report.

SELECT rcld.revenuecontractlineid, rcld.revenueorganizationname, rcld.revenuebookname, rcld.revenuecontractid, rcld.salesordernumber, rcld.salesorderlinenumber, rcld.itemnumber, rcld.customernumber, rcld.productcategory, rcld.productfamily, rcld.businessunit, SUM(rclf.extendedsellprice) extendedsellprice, SUM(rclf.carveadjustment) carveadjustment, SUM(rclf.allocatedextendedprice) allocatedextendedprice, SUM(rclf.unreleasedrevenue) unreleasedrevenue, SUM(rclf.releasedrevenue) releasedrevenue, SUM(rcbf.billingextendedsellprice) billingextendedseliprice, SUM(rcbf.billingdeferedamount) billingunreleasedrevenue, SUM(rcbf.billingrecognizedamount) billingreleasedrevenue, SUM(rcae.transactioncreditamount) transactionalnetrevenue, SUM(rcae.transactionbilledcreditamount) transactionalbilledrevenue, SUM(rcae.transactionunbilledcreditamount) transactionalunbilledrevenue, SUM(rcae.transactioncarveadjustmentamount) transactionaladjustmentrevenue FROM RevenueContractLinesDimensions rcld, RevenueContractLinesFacts rclf, (SELECT /* Billings details */ rcbf.revenuecontractlineid, rcbf.revenueorganizationcode, rcbf.revenueclientid, rcbf.revenuebookid, SUM(rcbf.billingextendedsellprice) billingextendedsellprice, SUM(rcbf.deferedamount) billingdeferedamount, SUM(rcbf.recognizedamount) billingrecognizedamount FROM RevenueContractBillsFacts rcbf GROUP BY rcbf.revenuecontractlineid, rcbf.revenueorganizationcode, rcbf.revenueclientid, rcbf.revenuebookid) rcbf, (SELECT /* Revenue Accounting details */ revenuecontractlineid, revenueorganizationcode, revenueclientid, revenuebookid, SUM(transactioncreditamount) transactioncreditamount, SUM(CASE WHEN accountingtypeid = 'R' AND unbilled_flag = 'N' THEN transactioncreditamount ELSE 0 END) transactionbilledcreditamount, SUM(CASE WHEN accountingtypeid = 'R' AND unbilled_flag = 'Y' THEN transactioncreditamount ELSE 0 END) transactionunbilledcreditamount, SUM(CASE WHEN accountingtypeid = 'X' THEN transactioncreditamount ELSE 0 END) transactioncarveadjustmentamount FROM RevenueContractAccountingEntries WHERE accountingtypeid IN (SELECT revenueaccounttypeid FROM RevenueContractAccountingType WHERE wf_summ_type = 'Net Revenue') GROUP BY revenuecontractlineid, revenueorganizationcode, revenueclientid, revenuebookid) rcae WHERE rcld.revenuecontractlineid = rclf.revenuecontractlineid AND rcld.revenueorganizationcode = rclf.revenueorganizationcode AND rcld.revenueclientid = rclf.revenueclientid AND rcld.revenuebookid = rclf.revenuebookid AND rcld.revenuecontractlineid = rcbf.revenuecontractlineid (+) AND rcld.revenueorganizationcode = rcbf.revenueorganizationcode (+) AND rcld.revenueclientid = rcbf.revenueclientid (+) AND rcld.revenuebookid = rcbf.revenuebookid (+) AND rcae.revenuecontractlineid (+) = rcld.revenuecontractlineid AND rcae.revenueorganizationcode (+) = rcld.revenueorganizationcode AND rcae.revenueclientid (+) = rcld.revenueclientid AND rcae.revenuebookid (+) = rcld.revenuebookid GROUP BY rcld.revenuecontractlineid, rcld.revenueorganizationname, rcld.revenuebookname, rcld.revenuecontractid, rcld.salesordernumber, rcld.salesorderlinenumber, rcld.itemnumber, rcld.customernumber, rcld.productcategory, rcld.productfamily, rcld.businessunit;

Waterfall sample query

The following sample query shows how to get the waterfall report.

Version 2 (Applicable if you are on Version 37.019.00.00 and above )

The changes are applicable in the rows 14,15,16, and 27.

SELECT C.ACCOUNTINGPERIODID AS ASOFPERIODID,
       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,
       (S.TRANSACTIONCREDITAMOUNT - S.TRANSACTIONDEBITAMOUNT) AS TACTIVITY,
       (S.FUNCTIONALCREDITAMOUNT - S.FUNCTIONALDEBITAMOUNT) AS FACTIVITY,
       (S.REPORTINGCREDITAMOUNT - S.REPORTINGDEBITAMOUNT) 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.POSTINGPERIODID >= C.ACCOUNTINGPERIODID
   AND S.ACCOUNTINGTYPEID = A.REVENUEACCOUNTTYPEID
   AND A.WATERFALL_FLAG = 'Y'
   and C.ACCOUNTINGPERIODNAME = '<PERIOD NAME>';

Version 1 (Applicable if you are on Versions up to 37.018.xx.xx)

SELECT C.ACCOUNTINGPERIODID AS ASOFPERIODID,
       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,
       (S.TRANSACTIONDEBITAMOUNT + S.TRANSACTIONCREDITAMOUNT) AS TACTIVITY,
       (S.FUNCTIONALDEBITAMOUNT + S.FUNCTIONALCREDITAMOUNT) AS FACTIVITY,
       (S.REPORTINGDEBITAMOUNT + S.REPORTINGCREDITAMOUNT) 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.SCHEDULEDPERIODID >= C.ACCOUNTINGPERIODID
   AND S.ACCOUNTINGTYPEID = A.REVENUEACCOUNTTYPEID
   AND A.WATERFALL_FLAG = 'Y'
   and C.ACCOUNTINGPERIODNAME = '<PERIOD NAME>';