1. Tables and Views for Financials
  2. VRM_PERF_OBLIG_FV_RULE_DIST_V

VRM_PERF_OBLIG_FV_RULE_DIST_V

Details

  • Schema: FUSION

  • Object owner: VRM

  • Object type: VIEW

Columns

Name

PERF_OBLIGATION_LINE_ID

DOCUMENT_LINE_ID

REVENUE_RULE_TYPE

FIXED_RULE_PERIOD_PERCENT

FIRST_PERIOD_VAR_RULE_PERCENT

PERIOD_START_DATE

PERIOD_END_DATE

ACCOUNTING_DATE

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

vpol_gt.PERF_OBLIGATION_LINE_ID

, vpol_gt.DOCUMENT_LINE_ID

, vpol_gt.REVENUE_RULE_TYPE

, rrs.PERCENT FIXED_RULE_PERIOD_PERCENT

, NVL(rrs_lump.PERCENT, DECODE(vpol_gt.line_status,'TEMPORARY',-1, ROUND(100/vpol_gt.REVENUE_RULE_DURATION,10))) FIRST_PERIOD_VAR_RULE_PERCENT

, all_periods.START_DATE PERIOD_START_DATE

, all_periods.END_DATE PERIOD_END_DATE

, NVL(RRS.RULE_DATE,

LEAST( ((vpol_gt.REVENUE_START_DATE - first_period.START_DATE) + all_periods.START_DATE),

all_periods.END_DATE,

NVL(vpol_gt.REVENUE_END_DATE, TO_DATE('31-12-4712','DD-MM-RRRR')))

) ACCOUNTING_DATE

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

, TO_NUMBER(DECODE((RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL)), /* Will be Null for Fixed Rules and for Variable Rules periods other than period number 1 */

NULL, DECODE(vpol_gt.REVENUE_RULE_TYPE, 'A', vpol_gt.ENTERED_CUR_ALLOCATED_AMT * (RRS.PERCENT / 100), /* For Fixed Rules PRORATE using schedules defined */

/* For Variable rule PRORATE the left over amount for other than first periods across all the subsequent periods based on Rule Duration */

(vpol_gt.ENTERED_CUR_ALLOCATED_AMT - NVL( ( vpol_gt.ENTERED_CUR_ALLOCATED_AMT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) /

DECODE(RRS_LUMP.PERCENT, NULL, vpol_gt.REVENUE_RULE_DURATION, vpol_gt.REVENUE_RULE_DURATION -1) ), /* END NOT LUMP SUM CASE */

vpol_gt.ENTERED_CUR_ALLOCATED_AMT * (RRS_LUMP.PERCENT / 100)

)

) ENTER_ALLOC_TRX_PERIOD_AMT

, TO_NUMBER(DECODE((RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL)), /* Will be Null for Fixed Rules and for Variable Rules periods other than period number 1 */

NULL, DECODE(vpol_gt.REVENUE_RULE_TYPE, 'A', vpol_gt.NET_LINE_AMT * (RRS.PERCENT / 100), /* For Fixed Rules PRORATE using schedules defined */

/* For Variable rule PRORATE the left over amount for other than first periods across all the subsequent periods based on Rule Duration */

(vpol_gt.NET_LINE_AMT - NVL( ( vpol_gt.NET_LINE_AMT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) /

DECODE(RRS_LUMP.PERCENT, NULL, vpol_gt.REVENUE_RULE_DURATION, vpol_gt.REVENUE_RULE_DURATION -1) ), /* END NOT LUMP SUM CASE */

vpol_gt.NET_LINE_AMT * (RRS_LUMP.PERCENT / 100)

)

) NET_LINE_PERIOD_AMT

, TO_NUMBER(DECODE((RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL)), /* Will be Null for Fixed Rules and for Variable Rules periods other than period number 1 */

NULL, DECODE(vpol_gt.REVENUE_RULE_TYPE, 'A', vpol_gt.ORIGINAL_LINE_AMT * (RRS.PERCENT / 100), /* For Fixed Rules PRORATE using schedules defined */

/* For Variable rule PRORATE the left over amount for other than first periods across all the subsequent periods based on Rule Duration */

(vpol_gt.ORIGINAL_LINE_AMT - NVL( ( vpol_gt.ORIGINAL_LINE_AMT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) /

DECODE(RRS_LUMP.PERCENT, NULL, vpol_gt.REVENUE_RULE_DURATION, vpol_gt.REVENUE_RULE_DURATION -1) ), /* END NOT LUMP SUM CASE */

vpol_gt.ORIGINAL_LINE_AMT * (RRS_LUMP.PERCENT / 100)

)

) ORIGINAL_LINE_PERIOD_AMT

, vpol_gt.ENTERED_CUR_ALLOCATED_AMT

, vpol_gt.NET_LINE_AMT

, vpol_gt.ORIGINAL_LINE_AMT

, first_period.PERIOD_TYPE

, first_period.PERIOD_SET_NAME

, RRS.PERIOD_NUMBER PERIOD_NUMBER

, (CASE WHEN RRS.PERIOD_NUMBER = 1

THEN 'Y'

ELSE 'N'

END ) FIRST_PERIOD_FLAG

, (CASE WHEN (ADD_MONTHS(vpol_gt.REVENUE_START_DATE, vpol_gt.REVENUE_RULE_DURATION - 1) BETWEEN all_periods.START_DATE AND all_periods.END_DATE)

THEN 'Y'

ELSE 'N'

END ) LAST_PERIOD_FLAG

FROM

VRM_PERF_OBLIG_LINES_GT vpol_gt,

GL_LEDGERS ledger,

RA_RULE_SCHEDULES rrs, /* Join to rrs for Fixed Rules */

RA_RULE_SCHEDULES rrs_lump, /* Join to rrs_lump Variable Rules */

VRM_PERIODS first_period,

VRM_PERIODS all_periods

WHERE vpol_gt.SATISFACTION_MEASUREMENT_MODEL = 'ORA_MEASURE_PERIOD_SATISFIED' /* This view is applicable for only for Fixed and Variable Rules */

AND ledger.LEDGER_ID = vpol_gt.LEDGER_ID

AND first_period.PERIOD_SET_NAME = ledger.PERIOD_SET_NAME

AND vpol_gt.REVENUE_RULE_TYPE IN ('A', 'ACC_DUR') /* Additional condition to filter only Fixed and Variable Rules */

AND rrs.RULE_ID = DECODE(vpol_gt.REVENUE_RULE_TYPE, 'A', vpol_gt.REVENUE_RULE_ID, -1) /* Fixed rules join condition */

AND rrs_lump.RULE_ID(+) = DECODE(vpol_gt.REVENUE_RULE_TYPE, 'ACC_DUR', vpol_gt.REVENUE_RULE_ID, -10) /* Variable rules join condition */

AND rrs_lump.PERIOD_NUMBER(+) = 1

AND rrs.PERIOD_NUMBER <= vpol_gt.REVENUE_RULE_DURATION

AND first_period.PERIOD_TYPE = ledger.ACCOUNTED_PERIOD_TYPE

AND vpol_gt.REVENUE_START_DATE BETWEEN first_period.START_DATE AND first_period.END_DATE

AND all_periods.PERIOD_SET_NAME = ledger.PERIOD_SET_NAME

AND all_periods.PERIOD_TYPE = ledger.ACCOUNTED_PERIOD_TYPE

AND all_periods.NEW_PERIOD_NUM = first_period.NEW_PERIOD_NUM + (rrs.PERIOD_NUMBER - 1)