VRM_REV_ASSIGN_FOR_STD

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

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 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(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) 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 , 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_CARVED_OUT_AMOUNT * (RRS.PERCENT / 100), /* PRORATE THE AMOUNT EXCEPT OVER FIRST / / PERIOD IF THERE IS A LUMP SUM. / ( CTL.EXTENDED_CARVED_OUT_AMOUNT - / LUMP SUM CASE / NVL( ( CTL.EXTENDED_CARVED_OUT_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_CARVED_OUT_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) ) ) EXTENDED_CARVED_OUT_AMOUNT , /* AMOUNT / RAL.LOOKUP_CODE ACCOUNT_CLASS , / ACCOUNT TYPE / GPS1.PERIOD_SET_NAME PERIOD_SET_NAME / PERIOD_SET NAME / FROM VRM_REVENUE_DOC_LINES_GT ctl, gl_sets_of_books sob, VRM_REVENUE_DOCUMENTS_GT 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)

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(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) 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

, 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_CARVED_OUT_AMOUNT * (RRS.PERCENT / 100),

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

( CTL.EXTENDED_CARVED_OUT_AMOUNT - /* LUMP SUM CASE */ NVL( ( CTL.EXTENDED_CARVED_OUT_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_CARVED_OUT_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) ) ) EXTENDED_CARVED_OUT_AMOUNT

, /* AMOUNT */ RAL.LOOKUP_CODE ACCOUNT_CLASS

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

FROM

VRM_REVENUE_DOC_LINES_GT ctl,

gl_sets_of_books sob,

VRM_REVENUE_DOCUMENTS_GT 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)