Sample queries
Examples are provided in the following sections to explain how to use different queries to derive different reports. In these examples, you can see how data points from various data entities are combined to adapt to different reporting scenarios.
Sample codes and a diagram to illustrate the relationship between the related tables are present for each example.
Booking
SELECT RB.NAME AS BOOK_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
RCL.CSTMR_NM,
RCL.CT_DATE,
RCL.CT_NUM,
RCL.CT_LINE_NUM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
RCL.TYPE,
RCL.ITEM_NUM,
RCL.EXT_SLL_PRC,
RCL.EXT_FV_PRC,
RCL.DEF_AMT,
RCL.REC_AMT,
RCL.BLD_DEF_AMT,
RCL.BLD_REC_AMT
FROM RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG
WHERE RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RB.ID
AND RCL.BOOK_ID = RC.BOOK_ID
AND RCL.SEC_ATR_VAL = ORG.ORG_ID
ORDER BY RC.ID;
.
Billing
SELECT RB.NAME AS BOOK_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
RCL.CSTMR_NM,
RCL.CT_DATE,
RCL.CT_NUM,
RCL.CT_LINE_NUM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
RCL.TYPE,
RCL.ITEM_NUM,
RCL.EXT_SLL_PRC,
RCL.EXT_FV_PRC,
RCL.DEF_AMT,
RCL.REC_AMT,
RCL.BLD_DEF_AMT,
RCL.BLD_REC_AMT,
RRB.INVOICE_NUM,
RRB.INVOICE_LINE_NUM,
RRB.TYPE BILL_TYPE,
RRB.INVOICE_DATE,
RRB.EXT_SLL_PRC INVOICE_SELL_PRICE,
RRB.DEF_AMT INVOICE_DEF_AMT,
RRB.REC_AMT INVOICE_REC_AMT
FROM RPRO_BI3_RC_BILL_V RRB,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG
WHERE RCL.RC_ID = RC.ID
AND RRB.LINE_ID = RCL.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RB.ID
AND RCL.BOOK_ID = RC.BOOK_ID
AND RCL.SEC_ATR_VAL = ORG.ORG_ID
ORDER BY RC.ID;Cost
SELECT RB.NAME AS BOOK_NAME,
ORG.ORG_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
RCL.CSTMR_NM,
RCL.CT_DATE,
RCL.CT_NUM,
RCL.CT_LINE_NUM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
RCL.EXT_LST_PRC,
RCL.EXT_SLL_PRC,
RLC.COST_TYPE,
RLC.ITEM_NUM,
RLC.AMOUNT,
RLC.DEF_AMT AS COST_DEF_AMT,
RLC.REC_AMT AS COST_REC_AMT,
RLC.COST_TMPL_NAME
FROM RPRO_BI3_RC_LN_COST_V RLC,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG
WHERE RLC.LINE_ID = RCL.ID
AND RLC.BOOK_ID = RCL.BOOK_ID
AND RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RB.ID
AND RCL.BOOK_ID = RC.BOOK_ID
AND RCL.SEC_ATR_VAL = ORG.ORG_ID
ORDER BY RB.NAME,
RC.ID,
RCL.SO_NUM,
RCL.SO_LINE_NUM;Revenue waterfall
SELECT RB.NAME AS BOOK_NAME,
ORG.ORG_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
RCL.CSTMR_NM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
WF.ACCTG_SEG,
WF.ACCTG_TYPE_ID,
ACT.NAME,
CAL.PERIOD_NAME,
SUM(WF.F_AT) AMOUNT_IN_FUNC_CURR
FROM RPRO_BI3_WF_SUMM_V WF,
RPRO_BI3_ACCT_TYPE_V ACT,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG,
RPRO_BI3_CALENDAR_V CAL
WHERE WF.ROOT_LINE_ID = RCL.ID
AND WF.BOOK_ID = RB.ID
AND WF.ACCTG_TYPE_ID = ACT.ID
AND WF.SEC_ATR_VAL = ORG.ORG_ID
AND RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RC.BOOK_ID
AND ACT.WATERFALL_FLAG = 'Y'
AND ACT.COST_FLAG = 'N'
AND WF.PRD_ID = CAL.ID
AND WF.AS_OF_PRD_ID = 201904
GROUP BY RB.NAME,
ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM,
WF.ACCTG_SEG, WF.ACCTG_TYPE_ID, ACT.NAME, CAL.PERIOD_NAME, WF.PRD_ID
ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM, ACT.NAME, WF.PRD_ID;
.
Cost waterfall
SELECT RB.NAME AS BOOK_NAME,
ORG.ORG_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
RCL.CSTMR_NM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
WF.ACCTG_SEG,
WF.ACCTG_TYPE_ID,
ACT.NAME,
CAL.PERIOD_NAME,
SUM(WF.F_AT) AMOUNT_IN_FUNC_CURR
FROM RPRO_BI3_WF_SUMM_V WF,
RPRO_BI3_ACCT_TYPE_V ACT,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG,
RPRO_BI3_CALENDAR_V CAL
WHERE WF.ROOT_LINE_ID = RCL.ID
AND WF.BOOK_ID = RB.ID
AND WF.ACCTG_TYPE_ID = ACT.ID
AND WF.SEC_ATR_VAL = ORG.ORG_ID
AND RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RC.BOOK_ID
AND ACT.WATERFALL_FLAG = 'Y'
AND ACT.COST_FLAG = 'N'
AND WF.PRD_ID = CAL.ID
AND WF.AS_OF_PRD_ID = 201904
GROUP BY RB.NAME,
ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM,
WF.ACCTG_SEG, WF.ACCTG_TYPE_ID, ACT.NAME, CAL.PERIOD_NAME, WF.PRD_ID
ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM, ACT.NAME, WF.PRD_ID;
.
RC rollforward
SELECT RB.NAME AS BOOK_NAME,
ORG.ORG_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
RCL.CSTMR_NM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
CAL.PERIOD_NAME,
SUM(SUMM.F_BB) as F_BB, -- contract liability begin balance
SUM(SUMM.F_AD) as F_AD, -- contract liability additions
SUM(SUMM.F_RL) as F_RL, -- contract liability releases
SUM(SUMM.F_EB) as F_EB, -- contract liability end balance
SUM(SUMM.F_AL_BB) as F_AL_BB, -- liability adjustment begin balance
SUM(SUMM.F_AL_AD) as F_AL_AD, -- liability adjustment additions
SUM(SUMM.F_AL_RL) as F_AL_RL, -- liability adjustment releases
SUM(SUMM.F_AL_EB) as F_AL_EB, -- liability adjustment end balance
SUM(SUMM.F_TL_BB) as F_TL_BB, -- total liability adjustment begin balance
SUM(SUMM.F_TL_AD) as F_TL_AD, -- total liability adjustment additions
SUM(SUMM.F_TL_RL) as F_TL_RL, -- total liability adjustment releases
SUM(SUMM.F_TL_EB) as F_TL_EB -- total liability adjustment end balance
FROM RPRO_BI3_LN_ACCT_SUMM_V SUMM,
RPRO_BI3_ACCT_TYPE_V ACT,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG,
RPRO_BI3_CALENDAR_V CAL
WHERE SUMM.ROOT_LINE_ID = RCL.ID
AND SUMM.BOOK_ID = RCL.BOOK_ID
AND SUMM.BOOK_ID = RB.ID
AND SUMM.ACCTG_TYPE_ID = ACT.ID
AND SUMM.SEC_ATR_VAL = ORG.ORG_ID
AND RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RC.BOOK_ID
AND SUMM.PRD_ID = CAL.ID
AND SUMM.PRD_ID = 201904
AND ACT.DEF_REC_FLAG in ('DEF', 'EDEF')
GROUP BY RB.NAME, ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM, CAL.PERIOD_NAME
ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM asc;Revenue insight
SELECT RB.NAME AS BOOK_NAME,
ORG.ORG_NAME,
RC.ID AS RC_ID,
POB.RC_POB_NAME,
RCL.CSTMR_NM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
CAL.PERIOD_NAME,
SUM(SUMM.F_AT) as F_AT, -- revenue activity
SUM(SUMM.F_AR_AT) as F_AR, -- revenue adjustment
SUM(SUMM.F_TL_AT) as F_TL_AT, -- total revenue activity
SUM(SUMM.F_TL_AT_PS) as F_TL_AT_PS, -- total posted
SUM(SUMM.F_TL_AT_UN) as F_TL_AT_UN -- total unposted
FROM RPRO_BI3_LN_ACCT_SUMM_V SUMM,
RPRO_BI3_ACCT_TYPE_V ACT,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_RC_POB_V POB,
RPRO_BI3_BOOK_V RB,
RPRO_BI3_ORG_V ORG,
RPRO_BI3_CALENDAR_V CAL
WHERE SUMM.ROOT_LINE_ID = RCL.ID
AND SUMM.BOOK_ID = RCL.BOOK_ID
AND SUMM.BOOK_ID = RB.ID
AND SUMM.ACCTG_TYPE_ID = ACT.ID
AND SUMM.SEC_ATR_VAL = ORG.ORG_ID
AND RCL.RC_ID = RC.ID
AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
AND RCL.BOOK_ID = RC.BOOK_ID
AND SUMM.PRD_ID = CAL.ID
AND SUMM.PRD_ID = 201904
AND ACT.DEF_REC_FLAG in ('REC', 'EREC')
GROUP BY RB.NAME, ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM, CAL.PERIOD_NAME
ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM asc;MJE
SELECT MJE.JE_HEAD_ID,
MJE.JE_HEAD_NAME,
MJE.JE_HEAD_DESC,
MJE.JE_LINE_ID,
MJE.ACTIVITY_TYPE,
MJE.REASON_CODE,
MJE.DESCRIPTION,
MJE.START_DATE,
MJE.END_DATE,
MJE.AMOUNT AS JE_LINE_AMOUNT,
RC.ID AS RC_ID,
RCL.ID AS LINE_ID,
SCHD.ACCTG_TYPE,
SCHD.ACCTG_SEG,
SCHD.ACCOUNT_NAME,
C.PERIOD_NAME AS SCHD_POST_PERIOD,
SCHD.AMOUNT AS SCHD_AMOUNT
FROM RPRO_BI3_MJE_V MJE,
RPRO_BI3_RC_SCHD_V SCHD,
RPRO_BI3_RC_LNS_V RCL,
RPRO_BI3_RC_HEAD_V RC,
RPRO_BI3_CALENDAR_V C
WHERE MJE.JE_LINE_ID = SCHD.LINE_ID
AND SCHD.ROOT_LINE_ID = RCL.ID
AND RCL.RC_ID = RC.ID
AND RCL.BOOK_ID = RC.BOOK_ID
AND SCHD.POST_PRD_ID = C.ID
ORDER BY RC.ID, RCL.ID, SCHD.POST_PRD_ID;
.