Skip to main content

Sample queries

Zuora

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

booking.png

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

billing.png

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

cost.png

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

revenue-waterfall.png

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

cost-waterfall.png

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

rc-rollforward.png

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    

rc-rollforward.png

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

MJE.png