1. Tables and Views for Financials
  2. VRM_ACTUAL_REV_ASSIGNMENTS_V

VRM_ACTUAL_REV_ASSIGNMENTS_V

Details

  • Schema: FUSION

  • Object owner: VRM

  • Object type: VIEW

Columns

Name

REVENUE_DOCUMENT_LINE_ID

REVENUE_DOCUMENT_ID

PREV_REVENUE_DOCUMENT_LINE_ID

REQUEST_ID

QUANTITY

ACCOUNTING_DATE

AMOUNT

ACCOUNT_CLASS

PERIOD_SET_NAME

Query

SQL_Statement

SELECT

CTL.REVENUE_DOCUMENT_LINE_ID /* REVENUE_DOCUMENT_LINE_ID */

, CTL.REVENUE_DOCUMENT_ID

, CTL.PREV_REVENUE_DOCUMENT_LINE_ID PREV_REVENUE_DOCUMENT_LINE_ID

, CTL.REQUEST_ID /* REQUEST_ID */

, NVL(Nvl(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED), CTL.QUANTITY) QUANTITY

, NVL(LGD.ORIGINAL_ACCOUNTING_DATE, NVL(CMA.ACCOUNTING_DATE, NVL(RRS.RULE_DATE,

LEAST( ((CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE), GPS2.END_DATE, NVL(CTL.RULE_END_DATE, TO_DATE('31-12-4712','DD-MM-RRRR')))

))) ACCOUNTING_DATE /* GL DATE */ /* USE THE AR_CREDIT_MEMO_AMOUNT VALUE IF IT IS NOT NULL. OR

USE THE LUMP SUM IF THIS IS A VARIABLE DURATION RULE WITH A LUMP SUM. */

, TO_NUMBER(DECODE(CT.COMPLETE_FLAG, 'N', 0,

NVL(LGD.SCHEDULED_AMOUNT,

NVL(CMA.AMOUNT, DECODE((RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL)),

NULL, DECODE(RR.TYPE, 'A', CTL.EXTENDED_REVENUE_PRICE_AMOUNT * (RRS.PERCENT / 100),

/* PRORATE THE AMOUNT EXCEPT OVER FIRST */ /* PERIOD IF THERE IS A LUMP SUM. */

( CTL.EXTENDED_REVENUE_PRICE_AMOUNT - /* LUMP SUM CASE */ NVL( ( CTL.EXTENDED_REVENUE_PRICE_AMOUNT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) /

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

/* USE THE LUMP SUM IF IT IS NOT NULL AND THIS IS THE FIRST PERIOD. */ CTL.EXTENDED_REVENUE_PRICE_AMOUNT * (RRS_LUMP.PERCENT / 100) /*

INSURE THAT THE SIGN OF THE ROW REFLECTS THE ACCOUNT TYPE AND

INVOICING RULE. */ ) /* END CMA.AMOUNT NOT SPECIFIED CASE */ ) /*

END CMA.AMOUNT NVL */ ) /* END LGD.SCHEDULED_AMOUNT NVL */ *

DECODE(RAL.LOOKUP_CODE, 'REV', 1, - 1) ) ) AMOUNT

, /* AMOUNT */ RAL.LOOKUP_CODE ACCOUNT_CLASS

, /* ACCOUNT TYPE */ GPS1.PERIOD_SET_NAME PERIOD_SET_NAME /* PERIOD_SET NAME */

FROM

VRM_REVENUE_DOC_LINES_ALL ctl,

gl_sets_of_books sob,

VRM_REVENUE_DOCUMENTS_ALL ct,

ra_rules rr,

RA_RULE_SCHEDULES rrs,

vrm_period_types gpt,

VRM_CREDIT_DOC_AMOUNTS cma,

RA_RULE_SCHEDULES rrs_lump,

VRM_REV_DOC_LINE_SCHEDULES_GT lgd,

vrm_periods gps1,

vrm_periods gps2,

VRM_LOOKUPS ral

WHERE

sob.set_of_books_id = ctl.set_of_books_id

AND ct.REVENUE_DOCUMENT_ID = ctl.REVENUE_DOCUMENT_ID

AND ctl.REVENUE_DOCUMENT_LINE_ID = cma.REVENUE_DOCUMENT_LINE_ID(+)

AND DECODE(ctl.autorule_complete_flag,

NULL, DECODE(ctl.RULE_DURATION_PROCESSED,

-1, ctl.REVENUE_DOCUMENT_LINE_ID,

NULL),

'Y', DECODE(ctl.RULE_DURATION_PROCESSED,

-1, ctl.REVENUE_DOCUMENT_LINE_ID,

NULL),

NULL) = lgd.REVENUE_DOCUMENT_LINE_ID(+)

AND 'N' = lgd.account_set_flag(+)

AND 'REV' = lgd.account_class(+)

AND DECODE(ctl.autorule_complete_flag,

NULL, DECODE(ctl.RULE_DURATION_PROCESSED,

-1, gps1.rowid,

gps2.rowid),

gps2.rowid) = gps2.rowid

AND DECODE(cma.ACCOUNTING_DATE,

NULL, gps2.rowid,

gps1.rowid) = gps2.rowid

/* Doubles the amount of rows returned for each GL date. */

AND ral.lookup_type = 'VRM_AUTOGL_TYPE'

AND (ral.lookup_code = 'REV'

OR

ral.lookup_code = DECODE(ct.DOCUMENT_RULE_ID,

-2, 'UNEARN',

-3, 'UNBILL'))

AND DECODE(rr.type,

'A', rr.rule_id,

-1) = rrs.rule_id

/* Join to rrs_lump if the rule is variable duration with a lump sum */

AND rrs_lump.rule_id(+) = DECODE(rr.type,

'ACC_DUR', rr.rule_id,

-10)

AND rrs_lump.period_number(+) = 1

AND rr.rule_id = ctl.REVENUE_RULE_ID

AND rrs.period_number <= DECODE(ctl.RULE_DURATION ,

NULL, rr.occurrences,

ctl.RULE_DURATION)

AND gps2.period_set_name = gps1.period_set_name

AND gpt.period_type = gps1.period_type

AND gpt.period_type = DECODE(rr.frequency,

NULL, accounted_period_type,

'SPECIFIC', accounted_period_type,

DECODE(ctl.PREV_REVENUE_DOCUMENT_LINE_ID,

NULL, rr.frequency,

accounted_period_type))

AND gps2.period_type = gps1.period_type

AND ctl.rule_start_date BETWEEN gps1.start_date AND gps1.end_date

AND gps1.end_date BETWEEN

ctl.rule_start_date AND

ctl.rule_start_date + gpt.max_regular_period_length

AND gps2.new_period_num = gps1.new_period_num + (rrs.period_number - 1)

AND (rr.type in ('A', 'ACC_DUR')

OR ctl.PREV_REVENUE_DOCUMENT_LINE_ID IS NOT NULL)

/** Handle only Fixed and Variable rules **/

UNION ALL

SELECT main_qry.REVENUE_DOCUMENT_LINE_ID,

main_qry.REVENUE_DOCUMENT_ID,

main_qry.prev_REVENUE_DOCUMENT_LINE_ID,

main_qry.request_id,

main_qry.quantity,

main_qry.ACCOUNTING_DATE,

NVL(cma.amount,

CASE main_qry.rule_type

WHEN 'PP_DR_ALL'

THEN (main_qry.daily_rate * days_per_gl_period)

WHEN 'PP_DR_PP'

THEN (

CASE full_or_partial

WHEN 'F'

THEN

/** Full periods **/

((EXTENDED_REVENUE_PRICE_AMOUNT - ( days_in_partial_periods * main_qry.daily_rate )) / (cnt_of_full_periods))

ELSE

/** Partial Periods **/

(main_qry.daily_rate * days_per_gl_period)

END)

ELSE ((EXTENDED_REVENUE_PRICE_AMOUNT * period_factor) / (no_of_full_periods))

END ) * DECODE(ral.lookup_code, 'REV', 1, -1) amount,

ral.lookup_code account_class,

period_set_name

FROM

(SELECT

/* */

ctl.REVENUE_DOCUMENT_ID,

ctl.REVENUE_DOCUMENT_LINE_ID,

ctl.PREV_REVENUE_DOCUMENT_LINE_ID prev_REVENUE_DOCUMENT_LINE_ID,

ctl.request_id,

ctl.rule_start_date,

ctl.rule_end_date,

ct.DOCUMENT_RULE_ID,

NVL(Nvl(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED), CTL.QUANTITY) quantity,

gps.start_date,

gps.end_date,

gps.period_type,

gps.period_set_name,

rr.type rule_type,

/** GL Date **/

LEAST((ctl.rule_start_date - FIRST_VALUE(start_date) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.REVENUE_DOCUMENT_LINE_ID, ctl.REVENUE_DOCUMENT_ID ORDER BY gps.start_date ) ) + gps.start_date, gps.end_date, ctl.rule_end_date ) ACCOUNTING_DATE,

/** Period Seq Number **/

ROW_NUMBER() OVER( PARTITION BY gps.period_set_name, gps.period_type, ctl.REVENUE_DOCUMENT_LINE_ID,ctl.REVENUE_DOCUMENT_ID ORDER BY gps.start_date ) period_seq_num,

/** Partial or Full ***/

DECODE(LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ), (gps.end_date - gps.start_date), 'F', 'P' ) full_or_partial,

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

(ctl.rule_end_date - ctl.rule_start_date) + 1 total_days_in_schedule,

/** Daily Rate **/

(ctl.EXTENDED_REVENUE_PRICE_AMOUNT) /((ctl.rule_end_date - ctl.rule_start_date) + 1) daily_rate,

/** Days per given GL period **/

LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 days_per_gl_period,

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

(gps.end_date - gps.start_date) + 1 max_days_per_gl_period,

/** Period Factor **/

(LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 ) / ((gps.end_date - gps.start_date) + 1) period_factor,

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

SUM((LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 ) / ((gps.end_date - gps.start_date) + 1) ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.REVENUE_DOCUMENT_LINE_ID, ctl.REVENUE_DOCUMENT_ID ) no_of_full_periods,

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

SUM(

CASE

/** Count only full periods **/

WHEN LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) = (gps.end_date - gps.start_date)

THEN 1

ELSE

/** Do not count partial periods */

0

END ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.REVENUE_DOCUMENT_LINE_ID,ctl.REVENUE_DOCUMENT_ID ) cnt_of_full_periods,

/**Days in partial periods ***/

SUM(

CASE

WHEN LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) = (gps.end_date - gps.start_date)

THEN

/** Full periods **/

0

ELSE

/** Partial periods */

LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) + 1

END ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.REVENUE_DOCUMENT_LINE_ID,ctl.REVENUE_DOCUMENT_ID ) days_in_partial_periods,

/** Revenue Amount for line item ***/

ctl.EXTENDED_REVENUE_PRICE_AMOUNT

FROM vrm_periods gps,

gl_sets_of_books gls,

vrm_period_types gpt,

ra_rules rr,

VRM_REVENUE_DOC_LINES_ALL ctl,

VRM_REVENUE_DOCUMENTS_ALL ct

WHERE ctl.set_of_books_id = gls.set_of_books_id

AND gls.period_set_name = gps.period_set_name

AND gps.period_type = gpt.period_type

AND ctl.REVENUE_RULE_ID = rr.rule_id

AND rr.type NOT IN ('A', 'ACC_DUR')

AND ctl.PREV_REVENUE_DOCUMENT_LINE_ID IS NULL

/* no regular credit memos */

AND NVL(rr.frequency,accounted_period_type) = gpt.period_type

AND ct.REVENUE_DOCUMENT_ID = ctl.REVENUE_DOCUMENT_ID

AND (ctl.rule_start_date BETWEEN gps.start_date AND gps.end_date

OR ctl.rule_end_date BETWEEN gps.start_date AND gps.end_date

OR gps.start_date BETWEEN ctl.rule_start_date AND ctl.rule_end_date )

) main_qry,

VRM_LOOKUPS ral,

VRM_CREDIT_DOC_AMOUNTS cma

WHERE ral.lookup_type = 'VRM_AUTOGL_TYPE'

AND (ral.lookup_code = 'REV'

OR ral.lookup_code = DECODE(main_qry.DOCUMENT_RULE_ID, -2, 'UNEARN', -3, 'UNBILL'))

AND main_qry.REVENUE_DOCUMENT_LINE_ID = cma.REVENUE_DOCUMENT_LINE_ID (+)

AND main_qry.ACCOUNTING_DATE = cma.ACCOUNTING_DATE (+)