1. Tables and Views for Financials
  2. VRM_XLA_REVENUE_CONTRACTS_V

VRM_XLA_REVENUE_CONTRACTS_V

Details

  • Schema: FUSION

  • Object owner: VRM

  • Object type: VIEW

Columns

Name

LEDGER_NAME

CONTRACT_CLASSIFICATION_CODE

EVENT_ID

LINE_NUMBER

LEDGER_ID

CUSTOMER_CONTRACT_DATE

REV_CONTRACT_NUMBER

DISC_ACCT_LEGAL_ENTITY_NAME

REVISION_INTENT_CONTRACT

Query

SQL_Statement

SELECT ledger.NAME LEDGER_NAME,

lkp.meaning CONTRACT_CLASSIFICATION_CODE,

l.EVENT_ID EVENT_ID,

l.LINE_NUMBER LINE_NUMBER,

l.LEDGER_ID LEDGER_ID,

vcch.CUSTOMER_CONTRACT_DATE CUSTOMER_CONTRACT_DATE,

vcch.Customer_contract_number REV_CONTRACT_NUMBER,

le.name DISC_ACCT_LEGAL_ENTITY_NAME,

vcch.LATEST_REVISION_INTENT_CODE REVISION_INTENT_CONTRACT

FROM VRM_XLA_IFRS15_EXTRACT_GT l,

VRM_CUSTOMER_CONTRACT_HEADERS vcch,

GL_LEDGERS ledger,

XLE_ENTITY_PROFILES le,

VRM_LOOKUPS lkp

WHERE l.LEVEL_CODE = 'H'

AND vcch.CUSTOMER_CONTRACT_HEADER_ID = l.CUSTOMER_CONTRACT_HEADER_ID

AND ledger.LEDGER_ID = l.LEDGER_ID

AND vcch.legal_entity_id = le.legal_entity_id (+)

AND lkp.lookup_code (+) = vcch.CONTRACT_CLASSIFICATION_CODE

AND lkp.lookup_type (+) = 'ORA_VRM_CONTRACT_CLASSIF_CODE'

AND l.event_type_code IN ('REV_PERF_OBL_SATISFIED','REV_PERF_OBL_BILLED', 'REV_INITIAL_PERF','RESIDUAL_BAL_ADJ','REV_CUR_REVAL_OFFSET')

UNION ALL

SELECT MAX(ledger.NAME) LEDGER_NAME,

NULL CONTRACT_CLASSIFICATION_CODE,

l.EVENT_ID EVENT_ID,

l.LINE_NUMBER LINE_NUMBER,

l.LEDGER_ID LEDGER_ID,

MAX(vcch.CUSTOMER_CONTRACT_DATE) CUSTOMER_CONTRACT_DATE,

MAX(vcch_parent.Customer_contract_number) REV_CONTRACT_NUMBER,

MAX(le.name) DISC_ACCT_LEGAL_ENTITY_NAME,

NULl REVISION_INTENT_CONTRACT

FROM VRM_XLA_IFRS15_EXTRACT_GT l,

VRM_CUSTOMER_CONTRACT_HEADERS vcch,

GL_LEDGERS ledger,

XLE_ENTITY_PROFILES le,

VRM_LOOKUPS lkp,

VRM_PERF_OBLIGATION_LINES vpol,

VRM_PERF_OBLIGATION_LINES vpol_parent,

VRM_CUSTOMER_CONTRACT_HEADERS vcch_parent

WHERE l.LEVEL_CODE = 'H'

AND vcch.CUSTOMER_CONTRACT_HEADER_ID = l.CUSTOMER_CONTRACT_HEADER_ID

AND ledger.LEDGER_ID = l.LEDGER_ID

AND vcch.legal_entity_id = le.legal_entity_id (+)

AND lkp.lookup_code (+) = vcch.CONTRACT_CLASSIFICATION_CODE

AND lkp.lookup_type (+) = 'ORA_VRM_CONTRACT_CLASSIF_CODE'

AND l.event_type_code IN ('REV_INCREMENTAL')

AND vcch.CUSTOMER_CONTRACT_HEADER_ID = vpol.CUSTOMER_CONTRACT_HEADER_ID

AND vpol.PARENT_PERF_OBLIG_LINE_ID = vpol_parent.PERF_OBLIGATION_LINE_ID

AND vpol_parent.CUSTOMER_CONTRACT_HEADER_ID = vcch_parent.CUSTOMER_CONTRACT_HEADER_ID

GROUP BY l.EVENT_ID,l.LINE_NUMBER, l.LEDGER_ID