Skip to main content

Sample queries

Zuora

Sample queries

  1. Last updated
  2. Save as PDF

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;

booking.png

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;

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

cost.png

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;

revenue-waterfall.png

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;

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

MJE.png