PJB_VRM_SOURCE_DOC_SUB_LINES

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
APPLICATION_ID DOCUMENT_TYPE_ID DOC_SUB_LINE_TYPE DOC_SUB_LINE_LEVEL DOC_SUB_LINE_ID_INT_1 DOC_SUB_LINE_ID_INT_2 DOC_LINE_ID_INT_1 DOC_ID_INT_1 LINE_NUMBER ORG_ID LEDGER_ID SUB_LINE_CREATION_DATE SUB_LINE_LAST_UPDATE_DATE ACCOUNT_CLASS AMOUNT ACCTD_AMOUNT ACCOUNTING_DATE SATISFACTION_MEASUREMENT_DATE EVENT_ID CODE_COMBINATION_ID DOCUMENT_TYPE_CODE SATISFACTION_EVENT_PERCENT SATISFACTION_EVENT_QUANTITY SLA_POSTED_FLAG

Name

APPLICATION_ID

DOCUMENT_TYPE_ID

DOC_SUB_LINE_TYPE

DOC_SUB_LINE_LEVEL

DOC_SUB_LINE_ID_INT_1

DOC_SUB_LINE_ID_INT_2

DOC_LINE_ID_INT_1

DOC_ID_INT_1

LINE_NUMBER

ORG_ID

LEDGER_ID

SUB_LINE_CREATION_DATE

SUB_LINE_LAST_UPDATE_DATE

ACCOUNT_CLASS

AMOUNT

ACCTD_AMOUNT

ACCOUNTING_DATE

SATISFACTION_MEASUREMENT_DATE

EVENT_ID

CODE_COMBINATION_ID

DOCUMENT_TYPE_CODE

SATISFACTION_EVENT_PERCENT

SATISFACTION_EVENT_QUANTITY

SLA_POSTED_FLAG

Query

SQL_Statement
select 510 APPLICATION_ID, 8 DOCUMENT_TYPE_ID, 'ORA_SATIS_EVENT_SMM_PERCENTAGE' DOC_SUB_LINE_TYPE, 'L' DOC_SUB_LINE_LEVEL, rdl.REV_DISTRIBUTION_ID DOC_SUB_LINE_ID_INT_1, al.AE_LINE_NUM DOC_SUB_LINE_ID_INT_2, rdl.CONTRACT_LINE_ID DOC_LINE_ID_INT_1, rdl.CONTRACT_ID DOC_ID_INT_1, rdl.LINE_NUM LINE_NUMBER, rdl.ORG_ID ORG_ID, rdl.LEDGER_ID LEDGER_ID, rdl.CREATION_DATE SUB_LINE_CREATION_DATE, xe.LAST_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE, 'REV' ACCOUNT_CLASS, TO_NUMBER(NULL) AMOUNT, TO_NUMBER(NULL) ACCTD_AMOUNT, TO_DATE(NULL) ACCOUNTING_DATE, rdl.GL_DATE SATISFACTION_MEASUREMENT_DATE, rdl.SLA_EVENT_ID EVENT_ID, al.CODE_COMBINATION_ID CODE_COMBINATION_ID, 'FUSION_CONTRACTS' DOCUMENT_TYPE_CODE, rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT SATISFACTION_EVENT_PERCENT, TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY, 'Y' SLA_POSTED_FLAG from PJB_REV_DISTRIBUTIONS rdl, OKC_VRM_SOURCE_DOC_LINES_V doc_line, OKC_VRM_SOURCE_DOCUMENT_V doc, XLA_AE_headers ah, XLA_AE_LINES al, XLA_EVENTS xe where rdl.sla_event_id = xe.event_id and rdl.CONTRACT_LINE_ID = doc_line.LINE_ID and doc.id = doc_line.CONTRACT_ID and doc_line.LINE_AMOUNT > 0 and xe.process_status_code = 'P' and xe.event_status_code = 'P' and ah.event_id = xe.event_id and al.ae_header_id = ah.ae_header_id and al.ledger_id = doc.PRIMARY_LEDGER_ID and xe.application_id = 10036 and al.accounting_class_code in ('PJB_REV_EX_EVT','PJB_REV_EX_EI','PJB_REV_EX_EVT_ADJ','PJB_REV_EX_EI_ADJ') UNION ALL SELECT 510 APPLICATION_ID, 8 DOCUMENT_TYPE_ID, 'ORA_SATIS_EVENT_SMM_PERCENTAGE' DOC_SUB_LINE_TYPE, 'L' DOC_SUB_LINE_LEVEL, rdl.REV_DISTRIBUTION_ID DOC_SUB_LINE_ID_INT_1, NULL DOC_SUB_LINE_ID_INT_2, rdl.CONTRACT_LINE_ID DOC_LINE_ID_INT_1, rdl.CONTRACT_ID DOC_ID_INT_1, rdl.LINE_NUM LINE_NUMBER, rdl.ORG_ID ORG_ID, rdl.LEDGER_ID LEDGER_ID, rdl.CREATION_DATE SUB_LINE_CREATION_DATE, rdl.LAST_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE, 'REV' ACCOUNT_CLASS, TO_NUMBER(NULL) AMOUNT, TO_NUMBER(NULL) ACCTD_AMOUNT, TO_DATE(NULL) ACCOUNTING_DATE, rdl.GL_DATE SATISFACTION_MEASUREMENT_DATE, rdl.SLA_EVENT_ID EVENT_ID, NULL CODE_COMBINATION_ID, 'FUSION_CONTRACTS' DOCUMENT_TYPE_CODE, rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT SATISFACTION_EVENT_PERCENT, TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY, 'N' SLA_POSTED_FLAG FROM PJB_REV_DISTRIBUTIONS rdl, okc_k_lines_b doc_line, VRM_LEDGER_OPTIONS_V vrmledg where rdl.CONTRACT_LINE_ID = doc_line.ID AND doc_line.VERSION_TYPE IN ('C','A') AND doc_line.source_code_class = 'PROJECT' AND doc_line.JTOT_OBJECT1_CODE IN ('USER_FREE_FORM_PROJECT_BASED','USER_ITEM_PROJECT_BASED') AND doc_line.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') and NVL(doc_line.LINE_AMOUNT,0) > 0 and rdl.sla_event_id IS NULL and rdl.billing_type_code ='EX' and rdl.gl_date >= vrmledg.adoption_start_date and rdl.ledger_id = vrmledg.ifrs_ledger_id and vrmledg.document_type_code='FUSION_CONTRACTS'

SQL_Statement

select

510 APPLICATION_ID,

8 DOCUMENT_TYPE_ID,

'ORA_SATIS_EVENT_SMM_PERCENTAGE' DOC_SUB_LINE_TYPE,

'L' DOC_SUB_LINE_LEVEL,

rdl.REV_DISTRIBUTION_ID DOC_SUB_LINE_ID_INT_1,

al.AE_LINE_NUM DOC_SUB_LINE_ID_INT_2,

rdl.CONTRACT_LINE_ID DOC_LINE_ID_INT_1,

rdl.CONTRACT_ID DOC_ID_INT_1,

rdl.LINE_NUM LINE_NUMBER,

rdl.ORG_ID ORG_ID,

rdl.LEDGER_ID LEDGER_ID,

rdl.CREATION_DATE SUB_LINE_CREATION_DATE,

xe.LAST_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE,

'REV' ACCOUNT_CLASS,

TO_NUMBER(NULL) AMOUNT,

TO_NUMBER(NULL) ACCTD_AMOUNT,

TO_DATE(NULL) ACCOUNTING_DATE,

rdl.GL_DATE SATISFACTION_MEASUREMENT_DATE,

rdl.SLA_EVENT_ID EVENT_ID,

al.CODE_COMBINATION_ID CODE_COMBINATION_ID,

'FUSION_CONTRACTS' DOCUMENT_TYPE_CODE,

rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT SATISFACTION_EVENT_PERCENT,

TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY,

'Y' SLA_POSTED_FLAG

from PJB_REV_DISTRIBUTIONS rdl,

OKC_VRM_SOURCE_DOC_LINES_V doc_line,

OKC_VRM_SOURCE_DOCUMENT_V doc,

XLA_AE_headers ah,

XLA_AE_LINES al,

XLA_EVENTS xe

where

rdl.sla_event_id = xe.event_id

and rdl.CONTRACT_LINE_ID = doc_line.LINE_ID

and doc.id = doc_line.CONTRACT_ID

and doc_line.LINE_AMOUNT > 0

and xe.process_status_code = 'P'

and xe.event_status_code = 'P'

and ah.event_id = xe.event_id

and al.ae_header_id = ah.ae_header_id

and al.ledger_id = doc.PRIMARY_LEDGER_ID

and xe.application_id = 10036

and al.accounting_class_code in ('PJB_REV_EX_EVT','PJB_REV_EX_EI','PJB_REV_EX_EVT_ADJ','PJB_REV_EX_EI_ADJ')

UNION ALL

SELECT

510 APPLICATION_ID,

8 DOCUMENT_TYPE_ID,

'ORA_SATIS_EVENT_SMM_PERCENTAGE' DOC_SUB_LINE_TYPE,

'L' DOC_SUB_LINE_LEVEL,

rdl.REV_DISTRIBUTION_ID DOC_SUB_LINE_ID_INT_1,

NULL DOC_SUB_LINE_ID_INT_2,

rdl.CONTRACT_LINE_ID DOC_LINE_ID_INT_1,

rdl.CONTRACT_ID DOC_ID_INT_1,

rdl.LINE_NUM LINE_NUMBER,

rdl.ORG_ID ORG_ID,

rdl.LEDGER_ID LEDGER_ID,

rdl.CREATION_DATE SUB_LINE_CREATION_DATE,

rdl.LAST_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE,

'REV' ACCOUNT_CLASS,

TO_NUMBER(NULL) AMOUNT,

TO_NUMBER(NULL) ACCTD_AMOUNT,

TO_DATE(NULL) ACCOUNTING_DATE,

rdl.GL_DATE SATISFACTION_MEASUREMENT_DATE,

rdl.SLA_EVENT_ID EVENT_ID,

NULL CODE_COMBINATION_ID,

'FUSION_CONTRACTS' DOCUMENT_TYPE_CODE,

rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT SATISFACTION_EVENT_PERCENT,

TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY,

'N' SLA_POSTED_FLAG

FROM PJB_REV_DISTRIBUTIONS rdl,

okc_k_lines_b doc_line,

VRM_LEDGER_OPTIONS_V vrmledg

where

rdl.CONTRACT_LINE_ID = doc_line.ID

AND doc_line.VERSION_TYPE IN ('C','A')

AND doc_line.source_code_class = 'PROJECT'

AND doc_line.JTOT_OBJECT1_CODE IN ('USER_FREE_FORM_PROJECT_BASED','USER_ITEM_PROJECT_BASED')

AND doc_line.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD')

and NVL(doc_line.LINE_AMOUNT,0) > 0

and rdl.sla_event_id IS NULL

and rdl.billing_type_code ='EX'

and rdl.gl_date >= vrmledg.adoption_start_date

and rdl.ledger_id = vrmledg.ifrs_ledger_id

and vrmledg.document_type_code='FUSION_CONTRACTS'