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.DEC_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,
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.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
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 RP,
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 = RP.ID
AND RCL.BOOK_ID = RC.BOOK_ID
AND RCL.SEC_ATR_VAL = ORG.ORG_ID
ORDER BY RP.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.CSTMTR_NM,
RCL.SO_BOOK_NAME,
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.CSTMTR_NM,
RCL.SO_BOOK_NAME, RCL.SO_NUM, RCL.SO_LINE_NUM,
WF.ACCTF_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.CSTMTR_NM,
RCL.SO_BOOK_DATE,
RCL.SO_NUM,
RCL.SO_LINE_NUM,
WF.ACCTG_SEG,
WF.ACCTG_TYPE,
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 = 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.CSTMTR_NM,
RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM,
WF.ACCTF_SEG, WF.ACCTG_TYPE, 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,
NJE.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.ROO_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
