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;