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';