日本語のコンテンツは機械翻訳されており、補助的な参照を目的としています。機械翻訳の精度は保証できません。英語版が正となります。また、現時点では検索機能は日本語での検索をサポートしていません。翻訳に関するフィードバックについては、docs@zuora.comに送信してください。

Sample queries

Last updated

Oct 16, 2023
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

01SELECT RB.NAME AS BOOK_NAME,
02    RC.ID AS RC_ID,
03    POB.RC_POB_NAME,
04    DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
05    RCL.CSTMR_NM,
06    RCL.CT_DATE,
07    RCL.CT_NUM,
08    RCL.CT_LINE_NUM,
09    RCL.SO_BOOK_DATE,
10    RCL.SO_NUM,
11    RCL.SO_LINE_NUM,
12    RCL.TYPE,
13    RCL.ITEM_NUM,
14    RCL.EXT_SLL_PRC,
15    RCL.EXT_FV_PRC,
16    RCL.DEF_AMT,
17    RCL.REC_AMT,
18    RCL.BLD_DEF_AMT,
19    RCL.BLD_REC_AMT
20FROM RPRO_BI3_RC_LNS_V RCL,
21     RPRO_BI3_RC_HEAD_V RC,
22     RPRO_BI3_RC_POB_V POB,
23     RPRO_BI3_BOOK_V RB,
24     RPRO_BI3_ORG_V ORG
25WHERE RCL.RC_ID = RC.ID
26   AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
27   AND RCL.BOOK_ID = RB.ID
28   AND RCL.BOOK_ID = RC.BOOK_ID
29   AND RCL.SEC_ATR_VAL = ORG.ORG_ID
30ORDER BY RC.ID;

Billing

01  SELECT RB.NAME AS BOOK_NAME,
02    RC.ID AS RC_ID,
03    POB.RC_POB_NAME,
04    DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
05    RCL.CSTMR_NM,
06    RCL.CT_DATE,
07    RCL.CT_NUM,
08    RCL.CT_LINE_NUM,
09    RCL.SO_BOOK_DATE,
10    RCL.SO_NUM,
11    RCL.SO_LINE_NUM,
12    RCL.TYPE,
13    RCL.ITEM_NUM,
14    RCL.EXT_SLL_PRC,
15    RCL.EXT_FV_PRC,
16    RCL.DEF_AMT,
17    RCL.REC_AMT,
18    RCL.BLD_DEF_AMT,
19    RCL.BLD_REC_AMT,
20    RRB.INVOICE_NUM,
21    RRB.INVOICE_LINE_NUM,
22    RRB.TYPE BILL_TYPE,
23    RRB.INVOICE_DATE,
24    RRB.EXT_SLL_PRC INVOICE_SELL_PRICE,
25    RRB.DEF_AMT INVOICE_DEF_AMT,
26    RRB.REC_AMT INVOICE_REC_AMT
27FROM RPRO_BI3_RC_BILL_V RRB,
28     RPRO_BI3_RC_LNS_V RCL,
29     RPRO_BI3_RC_HEAD_V RC,
30     RPRO_BI3_RC_POB_V POB,
31     RPRO_BI3_BOOK_V RB,
32     RPRO_BI3_ORG_V ORG
33WHERE RCL.RC_ID = RC.ID
34   AND RRB.LINE_ID = RCL.ID
35   AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
36   AND RCL.BOOK_ID = RB.ID
37   AND RCL.BOOK_ID = RC.BOOK_ID
38   AND RCL.SEC_ATR_VAL = ORG.ORG_ID
39ORDER BY RC.ID;

Cost

01SELECT RB.NAME AS BOOK_NAME,
02     ORG.ORG_NAME,
03     RC.ID AS RC_ID,
04     POB.RC_POB_NAME,
05     DECODE (POB.LEAD_LINE_ID, RCL.ID, 'Y', 'N') AS LEAD_LINE,
06     RCL.CSTMR_NM,
07     RCL.CT_DATE,
08     RCL.CT_NUM,
09     RCL.CT_LINE_NUM,
10     RCL.SO_BOOK_DATE,
11     RCL.SO_NUM,
12     RCL.SO_LINE_NUM,
13     RCL.EXT_LST_PRC,
14     RCL.EXT_SLL_PRC,
15     RLC.COST_TYPE,
16     RLC.ITEM_NUM,
17     RLC.AMOUNT,
18     RLC.DEF_AMT AS COST_DEF_AMT,
19     RLC.REC_AMT AS COST_REC_AMT,
20     RLC.COST_TMPL_NAME
21FROM RPRO_BI3_RC_LN_COST_V RLC,
22     RPRO_BI3_RC_LNS_V RCL,
23     RPRO_BI3_RC_HEAD_V RC,
24     RPRO_BI3_RC_POB_V POB,
25     RPRO_BI3_BOOK_V RB,
26     RPRO_BI3_ORG_V ORG
27WHERE RLC.LINE_ID = RCL.ID
28   AND RLC.BOOK_ID = RCL.BOOK_ID
29   AND RCL.RC_ID = RC.ID
30   AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
31   AND RCL.BOOK_ID = RB.ID
32   AND RCL.BOOK_ID = RC.BOOK_ID
33   AND RCL.SEC_ATR_VAL = ORG.ORG_ID
34ORDER BY RB.NAME,
35    RC.ID,
36    RCL.SO_NUM,
37    RCL.SO_LINE_NUM;

Revenue waterfall

01   SELECT RB.NAME AS BOOK_NAME,
02     ORG.ORG_NAME,
03     RC.ID AS RC_ID,
04     POB.RC_POB_NAME,
05     RCL.CSTMR_NM,
06     RCL.SO_BOOK_DATE,
07     RCL.SO_NUM,
08     RCL.SO_LINE_NUM,
09     WF.ACCTG_SEG,
10     WF.ACCTG_TYPE_ID,
11     ACT.NAME,
12     CAL.PERIOD_NAME,
13     SUM(WF.F_AT) AMOUNT_IN_FUNC_CURR
14FROM RPRO_BI3_WF_SUMM_V WF,
15     RPRO_BI3_ACCT_TYPE_V ACT,
16     RPRO_BI3_RC_LNS_V RCL,
17     RPRO_BI3_RC_HEAD_V RC,
18     RPRO_BI3_RC_POB_V POB,
19     RPRO_BI3_BOOK_V RB,
20     RPRO_BI3_ORG_V ORG,
21     RPRO_BI3_CALENDAR_V CAL
22WHERE WF.ROOT_LINE_ID = RCL.ID
23   AND WF.BOOK_ID = RB.ID
24   AND WF.ACCTG_TYPE_ID = ACT.ID
25   AND WF.SEC_ATR_VAL = ORG.ORG_ID
26   AND RCL.RC_ID = RC.ID
27   AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
28   AND RCL.BOOK_ID = RC.BOOK_ID
29   AND ACT.WATERFALL_FLAG = 'Y'
30   AND ACT.COST_FLAG = 'N'
31   AND WF.PRD_ID = CAL.ID
32   AND WF.AS_OF_PRD_ID = 201904
33GROUP BY RB.NAME,
34    ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
35    RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM,
36    WF.ACCTG_SEG, WF.ACCTG_TYPE_ID, ACT.NAME, CAL.PERIOD_NAME, WF.PRD_ID
37ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM, ACT.NAME, WF.PRD_ID;

Cost waterfall

01SELECT RB.NAME AS BOOK_NAME,
02     ORG.ORG_NAME,
03     RC.ID AS RC_ID,
04     POB.RC_POB_NAME,
05     RCL.CSTMR_NM,
06     RCL.SO_BOOK_DATE,
07     RCL.SO_NUM,
08     RCL.SO_LINE_NUM,
09     WF.ACCTG_SEG,
10     WF.ACCTG_TYPE_ID,
11     ACT.NAME,
12     CAL.PERIOD_NAME,
13     SUM(WF.F_AT) AMOUNT_IN_FUNC_CURR
14FROM RPRO_BI3_WF_SUMM_V WF,
15     RPRO_BI3_ACCT_TYPE_V ACT,
16     RPRO_BI3_RC_LNS_V RCL,
17     RPRO_BI3_RC_HEAD_V RC,
18     RPRO_BI3_RC_POB_V POB,
19     RPRO_BI3_BOOK_V RB,
20     RPRO_BI3_ORG_V ORG,
21     RPRO_BI3_CALENDAR_V CAL
22WHERE WF.ROOT_LINE_ID = RCL.ID
23   AND WF.BOOK_ID = RB.ID
24   AND WF.ACCTG_TYPE_ID = ACT.ID
25   AND WF.SEC_ATR_VAL = ORG.ORG_ID
26   AND RCL.RC_ID = RC.ID
27   AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
28   AND RCL.BOOK_ID = RC.BOOK_ID
29   AND ACT.WATERFALL_FLAG = 'Y'
30   AND ACT.COST_FLAG = 'N'
31   AND WF.PRD_ID = CAL.ID
32   AND WF.AS_OF_PRD_ID = 201904
33GROUP BY RB.NAME,
34    ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
35    RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM,
36    WF.ACCTG_SEG, WF.ACCTG_TYPE_ID, ACT.NAME, CAL.PERIOD_NAME, WF.PRD_ID
37ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM, ACT.NAME, WF.PRD_ID;

RC rollforward

01  SELECT RB.NAME AS BOOK_NAME,
02     ORG.ORG_NAME,
03     RC.ID AS RC_ID,
04     POB.RC_POB_NAME,
05     RCL.CSTMR_NM,
06     RCL.SO_BOOK_DATE,
07     RCL.SO_NUM,
08     RCL.SO_LINE_NUM,
09     CAL.PERIOD_NAME,
10     SUM(SUMM.F_BB) as F_BB, -- contract liability begin balance
11     SUM(SUMM.F_AD) as F_AD, -- contract liability additions
12     SUM(SUMM.F_RL) as F_RL, -- contract liability releases
13     SUM(SUMM.F_EB) as F_EB, -- contract liability end balance
14     SUM(SUMM.F_AL_BB) as F_AL_BB, -- liability adjustment begin balance
15     SUM(SUMM.F_AL_AD) as F_AL_AD, -- liability adjustment additions
16     SUM(SUMM.F_AL_RL) as F_AL_RL, -- liability adjustment releases
17     SUM(SUMM.F_AL_EB) as F_AL_EB, -- liability adjustment end balance
18     SUM(SUMM.F_TL_BB) as F_TL_BB, -- total liability adjustment begin balance
19     SUM(SUMM.F_TL_AD) as F_TL_AD, -- total liability adjustment additions
20     SUM(SUMM.F_TL_RL) as F_TL_RL, -- total liability adjustment releases
21     SUM(SUMM.F_TL_EB) as F_TL_EB -- total liability adjustment end balance
22FROM RPRO_BI3_LN_ACCT_SUMM_V SUMM,
23     RPRO_BI3_ACCT_TYPE_V ACT,
24     RPRO_BI3_RC_LNS_V RCL,
25     RPRO_BI3_RC_HEAD_V RC,
26     RPRO_BI3_RC_POB_V POB,
27     RPRO_BI3_BOOK_V RB,
28     RPRO_BI3_ORG_V ORG,
29     RPRO_BI3_CALENDAR_V CAL
30WHERE SUMM.ROOT_LINE_ID = RCL.ID
31  AND SUMM.BOOK_ID = RCL.BOOK_ID
32  AND SUMM.BOOK_ID = RB.ID
33  AND SUMM.ACCTG_TYPE_ID = ACT.ID
34  AND SUMM.SEC_ATR_VAL = ORG.ORG_ID
35  AND RCL.RC_ID = RC.ID
36  AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
37  AND RCL.BOOK_ID = RC.BOOK_ID
38  AND SUMM.PRD_ID = CAL.ID
39  AND SUMM.PRD_ID = 201904
40  AND ACT.DEF_REC_FLAG in ('DEF', 'EDEF')
41GROUP BY RB.NAME, ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
42    RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM, CAL.PERIOD_NAME
43ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM asc;

Revenue insight

01  SELECT RB.NAME AS BOOK_NAME,
02     ORG.ORG_NAME,
03     RC.ID AS RC_ID,
04     POB.RC_POB_NAME,
05     RCL.CSTMR_NM,
06     RCL.SO_BOOK_DATE,
07     RCL.SO_NUM,
08     RCL.SO_LINE_NUM,
09     CAL.PERIOD_NAME,
10     SUM(SUMM.F_AT) as F_AT, -- revenue activity
11     SUM(SUMM.F_AR_AT) as F_AR, -- revenue adjustment
12     SUM(SUMM.F_TL_AT) as F_TL_AT, -- total revenue activity
13     SUM(SUMM.F_TL_AT_PS) as F_TL_AT_PS, -- total posted
14     SUM(SUMM.F_TL_AT_UN) as F_TL_AT_UN -- total unposted
15FROM RPRO_BI3_LN_ACCT_SUMM_V SUMM,
16     RPRO_BI3_ACCT_TYPE_V ACT,
17     RPRO_BI3_RC_LNS_V RCL,
18     RPRO_BI3_RC_HEAD_V RC,
19     RPRO_BI3_RC_POB_V POB,
20     RPRO_BI3_BOOK_V RB,
21     RPRO_BI3_ORG_V ORG,
22     RPRO_BI3_CALENDAR_V CAL
23WHERE SUMM.ROOT_LINE_ID = RCL.ID
24AND SUMM.BOOK_ID = RCL.BOOK_ID
25AND SUMM.BOOK_ID = RB.ID
26AND SUMM.ACCTG_TYPE_ID = ACT.ID
27AND SUMM.SEC_ATR_VAL = ORG.ORG_ID
28AND RCL.RC_ID = RC.ID
29AND RCL.RC_POB_ID = POB.RC_POB_ID(+)
30AND RCL.BOOK_ID = RC.BOOK_ID
31AND SUMM.PRD_ID = CAL.ID
32AND SUMM.PRD_ID = 201904
33AND ACT.DEF_REC_FLAG in ('REC', 'EREC')
34GROUP BY RB.NAME, ORG.ORG_NAME, RC.ID, POB.RC_POB_NAME, RCL.CSTMR_NM,
35    RCL.SO_BOOK_DATE, RCL.SO_NUM, RCL.SO_LINE_NUM, CAL.PERIOD_NAME
36ORDER BY RB.NAME, RC.ID, RCL.SO_NUM, RCL.SO_LINE_NUM asc;  

MJE

01  SELECT MJE.JE_HEAD_ID,
02     MJE.JE_HEAD_NAME,
03     MJE.JE_HEAD_DESC,
04     MJE.JE_LINE_ID,
05     MJE.ACTIVITY_TYPE,
06     MJE.REASON_CODE,
07     MJE.DESCRIPTION,
08     MJE.START_DATE,
09     MJE.END_DATE,
10     MJE.AMOUNT AS JE_LINE_AMOUNT,
11     RC.ID AS RC_ID,
12     RCL.ID AS LINE_ID,
13     SCHD.ACCTG_TYPE,
14     SCHD.ACCTG_SEG,
15     SCHD.ACCOUNT_NAME,
16     C.PERIOD_NAME AS SCHD_POST_PERIOD,
17     SCHD.AMOUNT AS SCHD_AMOUNT
18   FROM RPRO_BI3_MJE_V MJE,
19     RPRO_BI3_RC_SCHD_V SCHD,
20     RPRO_BI3_RC_LNS_V RCL,
21     RPRO_BI3_RC_HEAD_V RC,
22     RPRO_BI3_CALENDAR_V C
23   WHERE MJE.JE_LINE_ID = SCHD.LINE_ID
24     AND SCHD.ROOT_LINE_ID = RCL.ID
25     AND RCL.RC_ID = RC.ID
26     AND RCL.BOOK_ID = RC.BOOK_ID
27     AND SCHD.POST_PRD_ID = C.ID
28ORDER BY RC.ID, RCL.ID, SCHD.POST_PRD_ID;