VRM_REV_ASSIGN_FOR_PPRR_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 EXTENDED_CARVED_OUT_AMOUNT ACCOUNT_CLASS PERIOD_SET_NAME

Query

SQL_Statement

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, NVL(cma.amount, CASE main_qry.rule_type when 'PP_DR_ALL' THEN (main_qry.carve_out_daily_rate * days_per_gl_period) WHEN 'PP_DR_PP' THEN ( CASE full_or_partial WHEN 'F' THEN /** Full periods **/ ((EXTENDED_CARVED_OUT_AMOUNT - ( days_in_partial_periods * main_qry.carve_out_daily_rate )) / (cnt_of_full_periods)) ELSE /** Partial Periods **/ (main_qry.carve_out_daily_rate * days_per_gl_period) END) ELSE ((EXTENDED_CARVED_OUT_AMOUNT * period_factor) / (no_of_full_periods)) END ) * DECODE(ral.lookup_code, 'REV', 1, -1) EXTENDED_CARVED_OUT_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(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) 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, /** Carve Out Daily Rate **/ (ctl.EXTENDED_CARVED_OUT_AMOUNT) /((ctl.rule_end_date - ctl.rule_start_date) + 1) carve_out_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, /** Carve Out Amount for line item ***/ ctl.EXTENDED_CARVED_OUT_AMOUNT FROM vrm_periods gps, gl_sets_of_books gls, vrm_period_types gpt, ra_rules rr, VRM_REVENUE_DOC_LINES_GT ctl, VRM_REVENUE_DOCUMENTS_GT 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 (+)