VRM_BILL_REV_RPRT_DRILL_DOWN

Details

Schema: FUSION
Object owner: VRM
Object type: VIEW

Columns

Name
DOCUMENT_LINE_ID PERIOD_DATE INVOICE_DATE INVOICE_NUMBER INVOICE_TYPE BILL_AMOUNT BILL_ACCTD_AMOUNT REV_LINE_NUMBER REV_LINE_TYPE REV_LINE_DATE REV_LINE_AMOUNT REV_LINE_ACCTD_AMOUNT ORIGINAL_DATE

Name

DOCUMENT_LINE_ID

PERIOD_DATE

INVOICE_DATE

INVOICE_NUMBER

INVOICE_TYPE

BILL_AMOUNT

BILL_ACCTD_AMOUNT

REV_LINE_NUMBER

REV_LINE_TYPE

REV_LINE_DATE

REV_LINE_AMOUNT

REV_LINE_ACCTD_AMOUNT

ORIGINAL_DATE

Query

SQL_Statement
SELECT bill_details.document_line_id, bill_details.bill_accounting_date Period_Date, bill_details.bill_date Invoice_Date, bill_details.bill_number Invoice_Number, DECODE(sign(bill_details.bill_amount), -1, 'Credit Memo', 'Invoice') Invoice_Type, bill_details.bill_amount Bill_Amount, bill_details.bill_acctd_amount Bill_Acctd_Amount, NULL Rev_Line_Number, NULL Rev_Line_Type, NULL Rev_Line_Date, NULL Rev_Line_Amount, NULL Rev_Line_Acctd_Amount, bill_details.creation_date original_date FROM vrm_billing_line_details bill_details UNION ALL SELECT rev_line_dtls.document_line_id, sched.accounting_date Period_Date, NULL Invoice_Date, NULL Invoice_Number, NULL Invoice_Type, NULL Bill_Amount, NULL Bill_Acctd_Amount, rev_line_dtls.line_number Rev_Line_Number, lookup.meaning Rev_Line_Type, rev_doc.document_date Rev_Line_Date, sched.amount Rev_Line_Amount, sched.acctd_amount Rev_Line_Acctd_Amount, sched.creation_date original_date FROM vrm_revenue_doc_lines_all rev_line_dtls, vrm_revenue_documents_all rev_doc, vrm_rev_doc_line_schedules_all sched, vrm_lookups lookup WHERE rev_doc.revenue_document_id = rev_line_dtls.revenue_document_id AND rev_doc.accounting_effect_flag = 'N' AND sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id AND sched.rec_offset_flag = 'Y' AND sched.account_class = 'UNEARN' AND sched.schedule_event = 'CREATION_SCHEDULE' AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT' AND lookup.lookup_code = sched.schedule_event UNION ALL SELECT rev_line_dtls.document_line_id, sched.accounting_date Period_Date, NULL Invoice_Date, NULL Invoice_Number, NULL Invoice_Type, NULL Bill_Amount, NULL Bill_Acctd_Amount, rev_line_dtls.line_number Rev_Line_Number, lookup.meaning Rev_Line_Type, (CAST(sched.creation_date AS DATE)) Rev_Line_Date, sched.amount Rev_Line_Amount, sched.acctd_amount Rev_Line_Acctd_Amount, sched.creation_date original_date FROM vrm_revenue_doc_lines_all rev_line_dtls, vrm_revenue_documents_all rev_doc, vrm_rev_doc_line_schedules_all sched, vrm_lookups lookup WHERE rev_doc.revenue_document_id = rev_line_dtls.revenue_document_id AND rev_doc.accounting_effect_flag = 'Y' AND sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id AND sched.account_class = 'REV' AND sched.schedule_event = 'CREATION_SCHEDULE' AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT' AND lookup.lookup_code = sched.schedule_event UNION ALL SELECT rev_line_dtls.document_line_id, sched.accounting_date Period_Date, NULL Invoice_Date, NULL Invoice_Number, NULL Invoice_Type, NULL Bill_Amount, NULL Bill_Acctd_Amount, NULL Rev_Line_Number, lookup.meaning Rev_Line_Type, (CAST(sched.creation_date AS DATE)) Rev_Line_Date, sched.amount Rev_Line_Amount, sched.acctd_amount Rev_Line_Acctd_Amount, (sched.creation_date + DECODE(sched.revenue_adjustment_id, NULL, 2, 1)) original_date FROM vrm_revenue_doc_lines_all rev_line_dtls, vrm_rev_doc_line_schedules_all sched, vrm_lookups lookup WHERE sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id AND sched.schedule_event IN ('RULE_CHANGE_SCHEDULE', 'REVISION_LINE_SCHEDULE') AND sched.rec_offset_flag = 'Y' AND sched.account_class = 'UNEARN' AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT' AND lookup.lookup_code = sched.schedule_event

SQL_Statement

SELECT bill_details.document_line_id,

bill_details.bill_accounting_date Period_Date,

bill_details.bill_date Invoice_Date,

bill_details.bill_number Invoice_Number,

DECODE(sign(bill_details.bill_amount), -1, 'Credit Memo', 'Invoice') Invoice_Type,

bill_details.bill_amount Bill_Amount,

bill_details.bill_acctd_amount Bill_Acctd_Amount,

NULL Rev_Line_Number,

NULL Rev_Line_Type,

NULL Rev_Line_Date,

NULL Rev_Line_Amount,

NULL Rev_Line_Acctd_Amount,

bill_details.creation_date original_date

FROM vrm_billing_line_details bill_details

UNION ALL

SELECT rev_line_dtls.document_line_id,

sched.accounting_date Period_Date,

NULL Invoice_Date,

NULL Invoice_Number,

NULL Invoice_Type,

NULL Bill_Amount,

NULL Bill_Acctd_Amount,

rev_line_dtls.line_number Rev_Line_Number,

lookup.meaning Rev_Line_Type,

rev_doc.document_date Rev_Line_Date,

sched.amount Rev_Line_Amount,

sched.acctd_amount Rev_Line_Acctd_Amount,

sched.creation_date original_date

FROM vrm_revenue_doc_lines_all rev_line_dtls,

vrm_revenue_documents_all rev_doc,

vrm_rev_doc_line_schedules_all sched,

vrm_lookups lookup

WHERE rev_doc.revenue_document_id = rev_line_dtls.revenue_document_id

AND rev_doc.accounting_effect_flag = 'N'

AND sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id

AND sched.rec_offset_flag = 'Y'

AND sched.account_class = 'UNEARN'

AND sched.schedule_event = 'CREATION_SCHEDULE'

AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT'

AND lookup.lookup_code = sched.schedule_event

UNION ALL

SELECT rev_line_dtls.document_line_id,

sched.accounting_date Period_Date,

NULL Invoice_Date,

NULL Invoice_Number,

NULL Invoice_Type,

NULL Bill_Amount,

NULL Bill_Acctd_Amount,

rev_line_dtls.line_number Rev_Line_Number,

lookup.meaning Rev_Line_Type,

(CAST(sched.creation_date AS DATE)) Rev_Line_Date,

sched.amount Rev_Line_Amount,

sched.acctd_amount Rev_Line_Acctd_Amount,

sched.creation_date original_date

FROM vrm_revenue_doc_lines_all rev_line_dtls,

vrm_revenue_documents_all rev_doc,

vrm_rev_doc_line_schedules_all sched,

vrm_lookups lookup

WHERE rev_doc.revenue_document_id = rev_line_dtls.revenue_document_id

AND rev_doc.accounting_effect_flag = 'Y'

AND sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id

AND sched.account_class = 'REV'

AND sched.schedule_event = 'CREATION_SCHEDULE'

AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT'

AND lookup.lookup_code = sched.schedule_event

UNION ALL

SELECT rev_line_dtls.document_line_id,

sched.accounting_date Period_Date,

NULL Invoice_Date,

NULL Invoice_Number,

NULL Invoice_Type,

NULL Bill_Amount,

NULL Bill_Acctd_Amount,

NULL Rev_Line_Number,

lookup.meaning Rev_Line_Type,

(CAST(sched.creation_date AS DATE)) Rev_Line_Date,

sched.amount Rev_Line_Amount,

sched.acctd_amount Rev_Line_Acctd_Amount,

(sched.creation_date +

DECODE(sched.revenue_adjustment_id, NULL, 2, 1)) original_date

FROM vrm_revenue_doc_lines_all rev_line_dtls,

vrm_rev_doc_line_schedules_all sched,

vrm_lookups lookup

WHERE sched.revenue_document_line_id = rev_line_dtls.revenue_document_line_id

AND sched.schedule_event IN ('RULE_CHANGE_SCHEDULE', 'REVISION_LINE_SCHEDULE')

AND sched.rec_offset_flag = 'Y'

AND sched.account_class = 'UNEARN'

AND lookup.lookup_type = 'VRM_REV_SCHED_EVENT'

AND lookup.lookup_code = sched.schedule_event