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, 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, CASE WHEN ( ( rdl.VRM_SATISFACTION_PERCENT IS NULL ) AND ( NVL(doc_line.line_amount,0) != 0 ) ) THEN ( rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT ) WHEN ( ( rdl.VRM_SATISFACTION_PERCENT IS NULL ) AND ( NVL(doc_line.line_amount,0) = 0 ) ) THEN 0 ELSE rdl.VRM_SATISFACTION_PERCENT END SATISFACTION_EVENT_PERCENT, TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY, NULL SLA_POSTED_FLAG FROM PJB_REV_DISTRIBUTIONS rdl, okc_k_lines_b doc_line, vrm_new_app_sources vrmapp 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 rdl.billing_type_code ='EX' AND rdl.vrm_extracted_flag IN ('N','P') and rdl.gl_date >= vrmapp.start_date and rdl.ledger_id = vrmapp.ledger_id and vrmapp.document_type_id= 8

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,

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,

CASE WHEN ( ( rdl.VRM_SATISFACTION_PERCENT IS NULL ) AND ( NVL(doc_line.line_amount,0) != 0 ) ) THEN

( rdl.CONT_CURR_REVENUE_AMT * 100/doc_line.LINE_AMOUNT )

WHEN ( ( rdl.VRM_SATISFACTION_PERCENT IS NULL ) AND ( NVL(doc_line.line_amount,0) = 0 ) ) THEN

0

ELSE rdl.VRM_SATISFACTION_PERCENT

END SATISFACTION_EVENT_PERCENT,

TO_NUMBER(NULL) SATISFACTION_EVENT_QUANTITY,

NULL SLA_POSTED_FLAG

FROM PJB_REV_DISTRIBUTIONS rdl,

okc_k_lines_b doc_line,

vrm_new_app_sources vrmapp

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 rdl.billing_type_code ='EX'

AND rdl.vrm_extracted_flag IN ('N','P')

and rdl.gl_date >= vrmapp.start_date

and rdl.ledger_id = vrmapp.ledger_id

and vrmapp.document_type_id= 8