VRM_REVISION_NET_SCHED_AMTS

Details

Schema: FUSION
Object owner: VRM
Object type: VIEW

Columns

Name
REVENUE_DOCUMENT_LINE_ID PREV_REVENUE_DOCUMENT_LINE_ID PREV_REVENUE_DOCUMENT_ID ACCOUNTING_DATE AMOUNT QUANTITY REVENUE_REVERSAL_METHOD PERIOD_SET_NAME

Name

REVENUE_DOCUMENT_LINE_ID

PREV_REVENUE_DOCUMENT_LINE_ID

PREV_REVENUE_DOCUMENT_ID

ACCOUNTING_DATE

AMOUNT

QUANTITY

REVENUE_REVERSAL_METHOD

PERIOD_SET_NAME

Query

SQL_Statement
SELECT vra.revenue_adjustment_id revenue_document_line_id, vra.from_rev_doc_line_id prev_revenue_document_line_id, vra.revenue_document_id prev_revenue_document_id, cma.accounting_date , DECODE(fc.minimum_accountable_unit, NULL, ROUND(cma.amount, fc.precision), ROUND(cma.amount / fc.minimum_accountable_unit) * fc.minimum_accountable_unit) amount, /* amount */ NVL(pl.net_quantity, 0) quantity, Nvl(vra.revenue_reversal_method, 'PRORATE') revenue_reversal_method, ledger.period_set_name FROM vrm_revenue_adjustments_all vra, vrm_revision_line_amounts cma, vrm_revenue_documents_all vrd, fnd_currencies fc, gl_ledgers ledger, vrm_pricing_lines pl WHERE vra.type = 'RETURN' AND vra.document_line_id = cma.document_line_id AND vra.revenue_document_id = vrd.revenue_document_id AND vra.revenue_adjustment_id > (SELECT Nvl(Max(revenue_adjustment_id), -1) FROM vrm_revenue_adjustments_all rule_chg_adj WHERE rule_chg_adj.revenue_document_id = vra.revenue_document_id AND rule_chg_adj.from_rev_doc_line_id = vra.from_rev_doc_line_id AND rule_chg_adj.type = 'REV_RULE_UPDATE') AND vrd.currency_code = fc.currency_code AND vrd.set_of_books_id = ledger.ledger_id AND vra.document_line_id = pl.document_line_id UNION SELECT TO_NUMBER('') revenue_document_line_id, orig_rev_doc_line.revenue_document_line_id prev_revenue_document_line_id, orig_rev_doc_line.revenue_document_id, orig_sched.original_accounting_date, DECODE(orig_rev_doc.complete_flag, 'N', 0, SUM(orig_sched.amount)) amount, orig_rev_doc_line.quantity_invoiced, NULL, ledger.period_set_name FROM vrm_revenue_documents_all orig_rev_doc, vrm_revenue_doc_lines_all orig_rev_doc_line, vrm_rev_doc_line_schedules_all orig_sched, gl_ledgers ledger WHERE orig_rev_doc_line.revenue_document_id = orig_rev_doc.revenue_document_id AND orig_sched.revenue_document_line_id = orig_rev_doc_line.revenue_document_line_id AND orig_sched.account_class = 'REV' AND orig_sched.account_set_flag = 'N' AND orig_sched.terminated_flag = 'N' AND orig_sched.schedule_event IN ('CREATION_SCHEDULE', 'RULE_CHANGE_SCHEDULE','MANUAL ADJUSTMENT_SCHEDULE') AND orig_rev_doc_line.set_of_books_id = ledger.ledger_id GROUP BY orig_rev_doc_line.revenue_document_line_id, orig_rev_doc_line.revenue_document_id, orig_rev_doc.complete_flag, orig_sched.original_accounting_date, orig_rev_doc_line.quantity_invoiced, ledger.period_set_name

SQL_Statement

SELECT vra.revenue_adjustment_id revenue_document_line_id,

vra.from_rev_doc_line_id prev_revenue_document_line_id,

vra.revenue_document_id prev_revenue_document_id,

cma.accounting_date ,

DECODE(fc.minimum_accountable_unit, NULL, ROUND(cma.amount, fc.precision), ROUND(cma.amount / fc.minimum_accountable_unit) * fc.minimum_accountable_unit) amount, /* amount */

NVL(pl.net_quantity, 0) quantity,

Nvl(vra.revenue_reversal_method, 'PRORATE') revenue_reversal_method,

ledger.period_set_name

FROM

vrm_revenue_adjustments_all vra,

vrm_revision_line_amounts cma,

vrm_revenue_documents_all vrd,

fnd_currencies fc,

gl_ledgers ledger,

vrm_pricing_lines pl

WHERE vra.type = 'RETURN'

AND vra.document_line_id = cma.document_line_id

AND vra.revenue_document_id = vrd.revenue_document_id

AND vra.revenue_adjustment_id > (SELECT Nvl(Max(revenue_adjustment_id), -1)

FROM vrm_revenue_adjustments_all rule_chg_adj

WHERE rule_chg_adj.revenue_document_id = vra.revenue_document_id

AND rule_chg_adj.from_rev_doc_line_id = vra.from_rev_doc_line_id

AND rule_chg_adj.type = 'REV_RULE_UPDATE')

AND vrd.currency_code = fc.currency_code

AND vrd.set_of_books_id = ledger.ledger_id

AND vra.document_line_id = pl.document_line_id

UNION

SELECT TO_NUMBER('') revenue_document_line_id,

orig_rev_doc_line.revenue_document_line_id prev_revenue_document_line_id,

orig_rev_doc_line.revenue_document_id,

orig_sched.original_accounting_date,

DECODE(orig_rev_doc.complete_flag,

'N', 0,

SUM(orig_sched.amount)) amount,

orig_rev_doc_line.quantity_invoiced,

NULL,

ledger.period_set_name

FROM vrm_revenue_documents_all orig_rev_doc,

vrm_revenue_doc_lines_all orig_rev_doc_line,

vrm_rev_doc_line_schedules_all orig_sched,

gl_ledgers ledger

WHERE orig_rev_doc_line.revenue_document_id = orig_rev_doc.revenue_document_id

AND orig_sched.revenue_document_line_id = orig_rev_doc_line.revenue_document_line_id

AND orig_sched.account_class = 'REV'

AND orig_sched.account_set_flag = 'N'

AND orig_sched.terminated_flag = 'N'

AND orig_sched.schedule_event IN ('CREATION_SCHEDULE', 'RULE_CHANGE_SCHEDULE','MANUAL ADJUSTMENT_SCHEDULE')

AND orig_rev_doc_line.set_of_books_id = ledger.ledger_id

GROUP BY orig_rev_doc_line.revenue_document_line_id, orig_rev_doc_line.revenue_document_id, orig_rev_doc.complete_flag,

orig_sched.original_accounting_date, orig_rev_doc_line.quantity_invoiced, ledger.period_set_name