1. Tables and Views for Financials
  2. VRM_REVISION_LINE_REV_ASSIGN_V

VRM_REVISION_LINE_REV_ASSIGN_V

Details

  • Schema: FUSION

  • Object owner: VRM

  • Object type: VIEW

Columns

Name

SESSION_ID

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

/*+ ORDERED USE_NL(ctl,ragt,lgd,gps1,gps2,cma) INDEX RAGT (ar_revenue_assignments_gt_n1) */

RAGT.SESSION_ID ,

CTL.revenue_document_line_id ,

CTL.revenue_document_id ,

CTL.prev_revenue_document_line_id ,

CTL.REQUEST_ID , /* REQUEST_ID */

NVL(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) QUANTITY , /* QUANTITY */

NVL(LGD.ORIGINAL_ACCOUNTING_DATE, NVL(CMA.ACCOUNTING_DATE, NVL(RAGT.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 */

(CASE SUBSTR(RAGT.RULE_TYPE,1,1)

WHEN 'A'

THEN

/* normal fixed or variable duration rule */

TO_NUMBER(NVL(LGD.AMOUNT, NVL(CMA.AMOUNT, CTL.EXTENDED_REVENUE_PRICE_AMOUNT * (RAGT.PERCENT / 100))) * DECODE(RAGT.ACCOUNT_CLASS, 'REV', 1, - 1))

WHEN 'P'

THEN

/* PPRR rule */

RAGT.PPRR_AMOUNT

END) AMOUNT, /* AMOUNT */

RAGT.ACCOUNT_CLASS, /* ACCOUNT CLASS */

GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */

FROM VRM_REVENUE_DOC_LINES_ALL ctl,

vrm_revenue_assignments_gt ragt,

VRM_REV_DOC_LINE_SCHEDULES_ALL lgd,

vrm_periods gps1,

vrm_periods gps2,

vrm_revision_line_amounts cma

WHERE ctl.revenue_document_line_id = ragt.revenue_document_line_id

AND ctl.document_line_id = cma.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

AND gps2.period_set_name = gps1.period_set_name

AND ragt.period_type = gps1.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 + ragt.max_regular_period_length

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