VRM_PERF_OBLIG_DR_RULE_DIST

Details

Schema: FUSION
Object owner: VRM
Object type: VIEW

Columns

Name
PERF_OBLIGATION_LINE_ID DOCUMENT_LINE_ID REVENUE_RULE_TYPE PERIOD_START_DATE PERIOD_END_DATE ACCOUNTING_DATE SATISFACTION_PERIOD_PROPORTION ENTER_ALLOC_TRX_PERIOD_AMT NET_LINE_PERIOD_AMT ORIGINAL_LINE_PERIOD_AMT ENTERED_CUR_ALLOCATED_AMT NET_LINE_AMT ORIGINAL_LINE_AMT PERIOD_TYPE PERIOD_SET_NAME PERIOD_NUMBER FIRST_PERIOD_FLAG LAST_PERIOD_FLAG

Name

PERF_OBLIGATION_LINE_ID

DOCUMENT_LINE_ID

REVENUE_RULE_TYPE

PERIOD_START_DATE

PERIOD_END_DATE

ACCOUNTING_DATE

SATISFACTION_PERIOD_PROPORTION

ENTER_ALLOC_TRX_PERIOD_AMT

NET_LINE_PERIOD_AMT

ORIGINAL_LINE_PERIOD_AMT

ENTERED_CUR_ALLOCATED_AMT

NET_LINE_AMT

ORIGINAL_LINE_AMT

PERIOD_TYPE

PERIOD_SET_NAME

PERIOD_NUMBER

FIRST_PERIOD_FLAG

LAST_PERIOD_FLAG

Query

SQL_Statement
SELECT main_qry.PERF_OBLIGATION_LINE_ID, main_qry.DOCUMENT_LINE_ID, main_qry.REVENUE_RULE_TYPE, main_qry.START_DATE PERIOD_START_DATE, main_qry.END_DATE PERIOD_END_DATE, DECODE(main_qry.LAST_PERIOD_FLAG, 'N', main_qry.END_DATE, main_qry.ACCOUNTING_DATE) ACCOUNTING_DATE, (CASE main_qry.REVENUE_RULE_TYPE WHEN 'PP_DR_ALL' THEN days_per_gl_period WHEN 'PP_DR_PP' THEN (CASE full_or_partial WHEN 'F' THEN / Full periods / ROUND(((main_qry.total_days_in_schedule - main_qry.days_in_partial_periods) / (main_qry.cnt_of_full_periods)), 10) ELSE / Partial Periods / main_qry.days_per_gl_period END) ELSE 0 END) SATISFACTION_PERIOD_PROPORTION, (CASE main_qry.REVENUE_RULE_TYPE WHEN 'PP_DR_ALL' THEN (main_qry.ENTER_ALLOC_TRX_AMT_DAILY_RATE * days_per_gl_period) WHEN 'PP_DR_PP' THEN (CASE full_or_partial WHEN 'F' THEN / Full periods / ((main_qry.ENTERED_CUR_ALLOCATED_AMT - ( main_qry.days_in_partial_periods * main_qry.ENTER_ALLOC_TRX_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods)) ELSE / Partial Periods / main_qry.days_per_gl_period END) ELSE ((main_qry.ENTERED_CUR_ALLOCATED_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods)) END) ENTER_ALLOC_TRX_PERIOD_AMT, (CASE main_qry.REVENUE_RULE_TYPE WHEN 'PP_DR_ALL' THEN (main_qry.NET_LINE_AMT_DAILY_RATE * days_per_gl_period) WHEN 'PP_DR_PP' THEN (CASE full_or_partial WHEN 'F' THEN / Full periods / ((main_qry.NET_LINE_AMT - ( main_qry.days_in_partial_periods * main_qry.NET_LINE_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods)) ELSE / Partial Periods / (main_qry.NET_LINE_AMT_DAILY_RATE * main_qry.days_per_gl_period) END) ELSE ((main_qry.NET_LINE_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods)) END) NET_LINE_PERIOD_AMT, (CASE main_qry.REVENUE_RULE_TYPE WHEN 'PP_DR_ALL' THEN (main_qry.ORIG_LINE_AMT_DAILY_RATE * days_per_gl_period) WHEN 'PP_DR_PP' THEN (CASE full_or_partial WHEN 'F' THEN / Full periods / ((main_qry.ORIGINAL_LINE_AMT - ( main_qry.days_in_partial_periods * main_qry.ORIG_LINE_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods)) ELSE / Partial Periods / (main_qry.ORIG_LINE_AMT_DAILY_RATE * main_qry.days_per_gl_period) END) ELSE ((main_qry.ORIGINAL_LINE_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods)) END) ORIGINAL_LINE_PERIOD_AMT, main_qry.ENTERED_CUR_ALLOCATED_AMT, main_qry.NET_LINE_AMT, main_qry.ORIGINAL_LINE_AMT, main_qry.PERIOD_TYPE, main_qry.PERIOD_SET_NAME, main_qry.PERIOD_NUMBER, main_qry.FIRST_PERIOD_FLAG, main_qry.LAST_PERIOD_FLAG FROM (SELECT /+ cardinality(vpol_gt 10) index(vpol_gt VRM_PERF_OBLIG_LINES_GT_U1) / vpol_gt.PERF_OBLIGATION_LINE_ID, vpol_gt.DOCUMENT_LINE_ID, vpol_gt.REVENUE_START_DATE, vpol_gt.REVENUE_END_DATE, periods.START_DATE, periods.END_DATE, periods.PERIOD_TYPE, periods.PERIOD_SET_NAME, vpol_gt.REVENUE_RULE_TYPE, vpol_gt.REVENUE_RULE_DURATION, / Accounting Date / LEAST((vpol_gt.REVENUE_START_DATE - FIRST_VALUE(periods.START_DATE) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ) ) + periods.start_date, periods.END_DATE, vpol_gt.REVENUE_END_DATE ) ACCOUNTING_DATE, /** Period Sequence Number +/ ROW_NUMBER() OVER( PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.start_date ) period_seq_num, / / Partial or Full / DECODE(LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)), (periods.END_DATE - periods.START_DATE), 'F', 'P' ) full_or_partial, /* Total number of days in RevRec schedule / (vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1 total_days_in_schedule, / Allocated Transaction Price in Entered Currency Daily Rate / (vpol_gt.ENTERED_CUR_ALLOCATED_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) ENTER_ALLOC_TRX_AMT_DAILY_RATE, / Net Line Amount Daily Rate / (vpol_gt.NET_LINE_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) NET_LINE_AMT_DAILY_RATE, / Original Line Amount Daily Rate / (vpol_gt.ORIGINAL_LINE_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) ORIG_LINE_AMT_DAILY_RATE, / Days per given GL period / (LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE))) + 1 days_per_gl_period, / Maximum Days per given GL period */ (periods.END_DATE - periods.START_DATE) + 1 max_days_per_gl_period, / Period Factor / (LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1 ) / ((periods.END_DATE - periods.START_DATE) + 1) period_factor, / total full periods (includes partial periods) */ SUM((LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1 ) / ((periods.END_DATE - periods.START_DATE) + 1) ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ) no_of_full_periods, / Count of only full periods */ SUM( CASE / Count only full periods / WHEN LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)) = (periods.END_DATE - periods.START_DATE) THEN 1 ELSE / Do not count partial periods / 0 END ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID) cnt_of_full_periods, /* Days in partial periods */ SUM( CASE WHEN LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)) = (periods.END_DATE - periods.START_DATE) THEN / Full periods / 0 ELSE / Partial periods / LEAST((periods.END_DATE - periods.START_DATE), (periods.END_DATE - vpol_gt.REVENUE_START_DATE), (vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1 END ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID) days_in_partial_periods, /* Allocated Transaction Price in Entered Currency */ vpol_gt.ENTERED_CUR_ALLOCATED_AMT, / Net Line Amount */ vpol_gt.NET_LINE_AMT, / Original Line Amount **/ vpol_gt.ORIGINAL_LINE_AMT, COUNT(periods.START_DATE) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ASC) PERIOD_NUMBER, (CASE WHEN COUNT(periods.START_DATE) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ASC) = 1 THEN 'Y' ELSE 'N' END ) FIRST_PERIOD_FLAG, (CASE WHEN vpol_gt.REVENUE_END_DATE BETWEEN periods.START_DATE AND periods.END_DATE THEN 'Y' ELSE 'N' END ) LAST_PERIOD_FLAG FROM VRM_PERF_OBLIG_LINES_GT vpol_gt, GL_LEDGERS ledger, GL_PERIODS periods WHERE vpol_gt.SATISFACTION_MEASUREMENT_MODEL = 'ORA_MEASURE_PERIOD_SATISFIED' AND ledger.LEDGER_ID = vpol_gt.LEDGER_ID AND periods.PERIOD_SET_NAME = ledger.PERIOD_SET_NAME AND vpol_gt.REVENUE_RULE_TYPE IN ('PP_DR_ALL', 'PP_DR_PP') / Additional condition to filter only Daily Rate Rules */ AND periods.PERIOD_TYPE = ledger.ACCOUNTED_PERIOD_TYPE AND periods.ADJUSTMENT_PERIOD_FLAG = 'N' AND ( vpol_gt.REVENUE_START_DATE BETWEEN periods.START_DATE AND periods.END_DATE OR vpol_gt.REVENUE_END_DATE BETWEEN periods.START_DATE AND periods.END_DATE OR periods.START_DATE BETWEEN vpol_gt.REVENUE_START_DATE AND vpol_gt.REVENUE_END_DATE ) ) main_qry

SQL_Statement

SELECT main_qry.PERF_OBLIGATION_LINE_ID,

main_qry.DOCUMENT_LINE_ID,

main_qry.REVENUE_RULE_TYPE,

main_qry.START_DATE PERIOD_START_DATE,

main_qry.END_DATE PERIOD_END_DATE,

DECODE(main_qry.LAST_PERIOD_FLAG, 'N', main_qry.END_DATE,

main_qry.ACCOUNTING_DATE) ACCOUNTING_DATE,

(CASE main_qry.REVENUE_RULE_TYPE

WHEN 'PP_DR_ALL'

THEN days_per_gl_period

WHEN 'PP_DR_PP'

THEN (CASE full_or_partial

WHEN 'F' THEN

/** Full periods **/

ROUND(((main_qry.total_days_in_schedule - main_qry.days_in_partial_periods) / (main_qry.cnt_of_full_periods)), 10)

ELSE

/** Partial Periods **/

main_qry.days_per_gl_period

END)

ELSE 0

END) SATISFACTION_PERIOD_PROPORTION,

(CASE main_qry.REVENUE_RULE_TYPE

WHEN 'PP_DR_ALL'

THEN (main_qry.ENTER_ALLOC_TRX_AMT_DAILY_RATE * days_per_gl_period)

WHEN 'PP_DR_PP'

THEN (CASE full_or_partial

WHEN 'F' THEN

/** Full periods **/

((main_qry.ENTERED_CUR_ALLOCATED_AMT - ( main_qry.days_in_partial_periods * main_qry.ENTER_ALLOC_TRX_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods))

ELSE

/** Partial Periods **/

main_qry.days_per_gl_period

END)

ELSE ((main_qry.ENTERED_CUR_ALLOCATED_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods))

END) ENTER_ALLOC_TRX_PERIOD_AMT,

(CASE main_qry.REVENUE_RULE_TYPE

WHEN 'PP_DR_ALL'

THEN (main_qry.NET_LINE_AMT_DAILY_RATE * days_per_gl_period)

WHEN 'PP_DR_PP'

THEN (CASE full_or_partial

WHEN 'F' THEN

/** Full periods **/

((main_qry.NET_LINE_AMT - ( main_qry.days_in_partial_periods * main_qry.NET_LINE_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods))

ELSE

/** Partial Periods **/

(main_qry.NET_LINE_AMT_DAILY_RATE * main_qry.days_per_gl_period)

END)

ELSE ((main_qry.NET_LINE_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods))

END) NET_LINE_PERIOD_AMT,

(CASE main_qry.REVENUE_RULE_TYPE

WHEN 'PP_DR_ALL'

THEN (main_qry.ORIG_LINE_AMT_DAILY_RATE * days_per_gl_period)

WHEN 'PP_DR_PP'

THEN (CASE full_or_partial

WHEN 'F' THEN

/** Full periods **/

((main_qry.ORIGINAL_LINE_AMT - ( main_qry.days_in_partial_periods * main_qry.ORIG_LINE_AMT_DAILY_RATE )) / (main_qry.cnt_of_full_periods))

ELSE

/** Partial Periods **/

(main_qry.ORIG_LINE_AMT_DAILY_RATE * main_qry.days_per_gl_period)

END)

ELSE ((main_qry.ORIGINAL_LINE_AMT * main_qry.period_factor) / (main_qry.no_of_full_periods))

END) ORIGINAL_LINE_PERIOD_AMT,

main_qry.ENTERED_CUR_ALLOCATED_AMT,

main_qry.NET_LINE_AMT,

main_qry.ORIGINAL_LINE_AMT,

main_qry.PERIOD_TYPE,

main_qry.PERIOD_SET_NAME,

main_qry.PERIOD_NUMBER,

main_qry.FIRST_PERIOD_FLAG,

main_qry.LAST_PERIOD_FLAG

FROM

(SELECT /*+ cardinality(vpol_gt 10) index(vpol_gt VRM_PERF_OBLIG_LINES_GT_U1) */

vpol_gt.PERF_OBLIGATION_LINE_ID,

vpol_gt.DOCUMENT_LINE_ID,

vpol_gt.REVENUE_START_DATE,

vpol_gt.REVENUE_END_DATE,

periods.START_DATE,

periods.END_DATE,

periods.PERIOD_TYPE,

periods.PERIOD_SET_NAME,

vpol_gt.REVENUE_RULE_TYPE,

vpol_gt.REVENUE_RULE_DURATION,

/** Accounting Date **/

LEAST((vpol_gt.REVENUE_START_DATE - FIRST_VALUE(periods.START_DATE)

OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ) ) + periods.start_date,

periods.END_DATE, vpol_gt.REVENUE_END_DATE ) ACCOUNTING_DATE,

/** Period Sequence Number *+/

ROW_NUMBER() OVER( PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.start_date ) period_seq_num, */

/** Partial or Full ***/

DECODE(LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)),

(periods.END_DATE - periods.START_DATE), 'F', 'P' ) full_or_partial,

/** Total number of days in RevRec schedule **/

(vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1 total_days_in_schedule,

/** Allocated Transaction Price in Entered Currency Daily Rate **/

(vpol_gt.ENTERED_CUR_ALLOCATED_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) ENTER_ALLOC_TRX_AMT_DAILY_RATE,

/** Net Line Amount Daily Rate **/

(vpol_gt.NET_LINE_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) NET_LINE_AMT_DAILY_RATE,

/** Original Line Amount Daily Rate **/

(vpol_gt.ORIGINAL_LINE_AMT) /((vpol_gt.REVENUE_END_DATE - vpol_gt.REVENUE_START_DATE) + 1) ORIG_LINE_AMT_DAILY_RATE,

/** Days per given GL period **/

(LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE))) + 1 days_per_gl_period,

/** Maximum Days per given GL period ***/

(periods.END_DATE - periods.START_DATE) + 1 max_days_per_gl_period,

/** Period Factor **/

(LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1 ) / ((periods.END_DATE - periods.START_DATE) + 1) period_factor,

/** total full periods (includes partial periods) ***/

SUM((LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1 ) / ((periods.END_DATE - periods.START_DATE) + 1) ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ) no_of_full_periods,

/** Count of only full periods ***/

SUM( CASE

/** Count only full periods **/

WHEN LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)) = (periods.END_DATE - periods.START_DATE)

THEN 1

ELSE

/** Do not count partial periods */

0

END ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID) cnt_of_full_periods,

/** Days in partial periods ***/

SUM( CASE

WHEN LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)) = (periods.END_DATE - periods.START_DATE)

THEN

/** Full periods **/

0

ELSE

/** Partial periods */

LEAST((periods.END_DATE - periods.START_DATE),

(periods.END_DATE - vpol_gt.REVENUE_START_DATE),

(vpol_gt.REVENUE_END_DATE - periods.START_DATE)) + 1

END ) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID) days_in_partial_periods,

/** Allocated Transaction Price in Entered Currency ***/

vpol_gt.ENTERED_CUR_ALLOCATED_AMT,

/** Net Line Amount ***/

vpol_gt.NET_LINE_AMT,

/** Original Line Amount ***/

vpol_gt.ORIGINAL_LINE_AMT,

COUNT(periods.START_DATE) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ASC) PERIOD_NUMBER,

(CASE WHEN COUNT(periods.START_DATE) OVER (PARTITION BY vpol_gt.PERF_OBLIGATION_LINE_ID ORDER BY periods.START_DATE ASC) = 1

THEN 'Y'

ELSE 'N'

END ) FIRST_PERIOD_FLAG,

(CASE WHEN vpol_gt.REVENUE_END_DATE BETWEEN periods.START_DATE AND periods.END_DATE

THEN 'Y'

ELSE 'N'

END ) LAST_PERIOD_FLAG

FROM VRM_PERF_OBLIG_LINES_GT vpol_gt,

GL_LEDGERS ledger,

GL_PERIODS periods

WHERE vpol_gt.SATISFACTION_MEASUREMENT_MODEL = 'ORA_MEASURE_PERIOD_SATISFIED'

AND ledger.LEDGER_ID = vpol_gt.LEDGER_ID

AND periods.PERIOD_SET_NAME = ledger.PERIOD_SET_NAME

AND vpol_gt.REVENUE_RULE_TYPE IN ('PP_DR_ALL', 'PP_DR_PP') /* Additional condition to filter only Daily Rate Rules */

AND periods.PERIOD_TYPE = ledger.ACCOUNTED_PERIOD_TYPE

AND periods.ADJUSTMENT_PERIOD_FLAG = 'N'

AND ( vpol_gt.REVENUE_START_DATE BETWEEN periods.START_DATE AND periods.END_DATE

OR vpol_gt.REVENUE_END_DATE BETWEEN periods.START_DATE AND periods.END_DATE

OR periods.START_DATE BETWEEN vpol_gt.REVENUE_START_DATE AND vpol_gt.REVENUE_END_DATE )

) main_qry