PJB_XLA_REV_HEADERS

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
EVENT_ID CONTRACT_ID CONTRACT_TYPE_ID CONTRACT_LINE_ID REVENUE_CATEGORY LINKED_PROJECT_ID LINKED_TASK_ID REV_DISTRIBUTION_ID LINE_NUMBER TRANSACTION_PROJECT_ID PROJECT_ID TRANSACTION_TASK_ID REVENUE_PLAN_ID CONTRACT_PROJECT_LINKAGE_ID TRANSACTION_TYPE_CODE EXPENDITURE_ITEM_ID BILLING_EVENT_ID EXPENDITURE_TYPE_ID BILLING_EVENT_TYPE_ID PERSON_ID HCM_ASSIGNMENT_ID ACCOUNTING_DATE REVERSED_FLAG REV_LINE_NUM_REVERSED CREDITING_FLAG REV_DIST_CREATION_DATE REV_DIST_CREATED_BY REV_DIST_LAST_UPDATED_DATE REV_DIST_LAST_UPDATED_BY HCM_PERSON_TYPE

Name

EVENT_ID

CONTRACT_ID

CONTRACT_TYPE_ID

CONTRACT_LINE_ID

REVENUE_CATEGORY

LINKED_PROJECT_ID

LINKED_TASK_ID

REV_DISTRIBUTION_ID

LINE_NUMBER

TRANSACTION_PROJECT_ID

PROJECT_ID

TRANSACTION_TASK_ID

REVENUE_PLAN_ID

CONTRACT_PROJECT_LINKAGE_ID

TRANSACTION_TYPE_CODE

EXPENDITURE_ITEM_ID

BILLING_EVENT_ID

EXPENDITURE_TYPE_ID

BILLING_EVENT_TYPE_ID

PERSON_ID

HCM_ASSIGNMENT_ID

ACCOUNTING_DATE

REVERSED_FLAG

REV_LINE_NUM_REVERSED

CREDITING_FLAG

REV_DIST_CREATION_DATE

REV_DIST_CREATED_BY

REV_DIST_LAST_UPDATED_DATE

REV_DIST_LAST_UPDATED_BY

HCM_PERSON_TYPE

Query

SQL_Statement
SELECT Xev.EVENT_ID EVENT_ID , ContHead.ID CONTRACT_ID , ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID , RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID , RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY , RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID , RevDist.LINKED_TASK_ID LINKED_TASK_ID , RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID , RevDist.LINE_NUM LINE_NUMBER , RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID , RevDist.TRANSACTION_PROJECT_ID PROJECT_ID , RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_ID , RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID , RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID , RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_ID,NULL) EXPENDITURE_ITEM_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_ID,NULL) BILLING_EVENT_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE_ID , RevDist.PERSON_ID PERSON_ID , NULL HCM_ASSIGNMENT_ID , RevDist.GL_DATE ACCOUNTING_DATE , RevDist.REVERSED_FLAG REVERSED_FLAG , RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED , DECODE(RevDist.REVERSED_REV_DISTRIBUTION_ID, NULL, 'N', 'Y') CREDITING_FLAG , trunc(RevDist.CREATION_DATE) REV_DIST_CREATION_DATE , RevDist.CREATED_BY REV_DIST_CREATED_BY , trunc(RevDist.LAST_UPDATE_DATE) REV_DIST_LAST_UPDATED_DATE , RevDist.LAST_UPDATED_BY REV_DIST_LAST_UPDATED_BY , NULL HCM_PERSON_TYPE FROM PJB_REV_DISTRIBUTIONS RevDist, OKC_K_HEADERS_ALL_B ContHead, XLA_EVENTS_GT Xev, OKC_BU_IMPL_OPTIONS_ALL BU WHERE Xev.APPLICATION_ID = 10036 AND Xev.SOURCE_ID_INT_1 = RevDist.REV_DISTRIBUTION_ID AND RevDist.CONTRACT_ID = ContHead.ID AND ContHead.Org_id = Bu.org_id AND ( (ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') ) OR (ContHead.under_amend_version_flag = 'N' AND ContHead.sts_code='UNDER_AMENDMENT' AND ContHead.version_type='C' AND BU.attribute15='INCLUDE_UNDER_AMENDMENT') ) AND RevDist.SLA_EVENT_ID IS NOT NULL AND RevDist.BILL_TRANSACTION_TYPE_CODE = 'EVT' AND ContHead.TEMPLATE_YN = 'N' UNION ALL SELECT Xev.EVENT_ID EVENT_ID , ContHead.ID CONTRACT_ID , ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID , RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID , RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY , RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID , RevDist.LINKED_TASK_ID LINKED_TASK_ID , RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID , RevDist.LINE_NUM LINE_NUMBER , RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID , RevDist.TRANSACTION_PROJECT_ID PROJECT_ID , RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_ID , RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID , RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID , RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_ID,NULL) EXPENDITURE_ITEM_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_ID,NULL) BILLING_EVENT_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE_ID , DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE_ID , RevDist.PERSON_ID PERSON_ID , ExpItem.HCM_ASSIGNMENT_ID HCM_ASSIGNMENT_ID , RevDist.GL_DATE ACCOUNTING_DATE , RevDist.REVERSED_FLAG REVERSED_FLAG , RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED , DECODE(RevDist.REVERSED_REV_DISTRIBUTION_ID, NULL, 'N', 'Y') CREDITING_FLAG , trunc(RevDist.CREATION_DATE) REV_DIST_CREATION_DATE , RevDist.CREATED_BY REV_DIST_CREATED_BY , trunc(RevDist.LAST_UPDATE_DATE) REV_DIST_LAST_UPDATED_DATE , RevDist.LAST_UPDATED_BY REV_DIST_LAST_UPDATED_BY , ExpItem.PERSON_TYPE HCM_PERSON_TYPE FROM PJB_REV_DISTRIBUTIONS RevDist, OKC_K_HEADERS_ALL_B ContHead, XLA_EVENTS_GT Xev, PJC_EXP_ITEMS_ALL ExpItem, OKC_BU_IMPL_OPTIONS_ALL BU WHERE Xev.APPLICATION_ID = 10036 AND Xev.SOURCE_ID_INT_1 = RevDist.REV_DISTRIBUTION_ID AND RevDist.CONTRACT_ID = ContHead.ID AND ContHead.Org_id = Bu.org_id AND ( (ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') ) OR (ContHead.under_amend_version_flag = 'N' AND ContHead.sts_code='UNDER_AMENDMENT' AND ContHead.Version_type='C' AND BU.attribute15='INCLUDE_UNDER_AMENDMENT') ) AND RevDist.SLA_EVENT_ID IS NOT NULL AND RevDist.TRANSACTION_ID = ExpItem.EXPENDITURE_ITEM_ID AND RevDist.BILL_TRANSACTION_TYPE_CODE = 'EI' AND ContHead.TEMPLATE_YN = 'N'

SQL_Statement

SELECT

Xev.EVENT_ID EVENT_ID

, ContHead.ID CONTRACT_ID

, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID

, RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID

, RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY

, RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID

, RevDist.LINKED_TASK_ID LINKED_TASK_ID

, RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID , RevDist.LINE_NUM LINE_NUMBER

, RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID

, RevDist.TRANSACTION_PROJECT_ID PROJECT_ID

, RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_ID

, RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID

, RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID

, RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE

, DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_ID,NULL) EXPENDITURE_ITEM_ID

, DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_ID,NULL) BILLING_EVENT_ID

, DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI',RevDist.TRANSACTION_TYPE_ID,NULL) EXPENDITURE_TYPE_ID

, DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EVT',RevDist.TRANSACTION_TYPE_ID,NULL) BILLING_EVENT_TYPE_ID

, RevDist.PERSON_ID PERSON_ID

, NULL HCM_ASSIGNMENT_ID

, RevDist.GL_DATE ACCOUNTING_DATE

, RevDist.REVERSED_FLAG REVERSED_FLAG

, RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED

, DECODE(RevDist.REVERSED_REV_DISTRIBUTION_ID, NULL, 'N', 'Y') CREDITING_FLAG

, trunc(RevDist.CREATION_DATE) REV_DIST_CREATION_DATE

, RevDist.CREATED_BY REV_DIST_CREATED_BY

, trunc(RevDist.LAST_UPDATE_DATE) REV_DIST_LAST_UPDATED_DATE

, RevDist.LAST_UPDATED_BY REV_DIST_LAST_UPDATED_BY

, NULL HCM_PERSON_TYPE

FROM

PJB_REV_DISTRIBUTIONS RevDist,

OKC_K_HEADERS_ALL_B ContHead,

XLA_EVENTS_GT Xev,

OKC_BU_IMPL_OPTIONS_ALL BU

WHERE

Xev.APPLICATION_ID = 10036

AND Xev.SOURCE_ID_INT_1 = RevDist.REV_DISTRIBUTION_ID

AND RevDist.CONTRACT_ID = ContHead.ID

AND ContHead.Org_id = Bu.org_id

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

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

OR (ContHead.under_amend_version_flag = 'N' AND ContHead.sts_code='UNDER_AMENDMENT'

AND ContHead.version_type='C'

AND BU.attribute15='INCLUDE_UNDER_AMENDMENT') )

AND RevDist.SLA_EVENT_ID IS NOT NULL

AND RevDist.BILL_TRANSACTION_TYPE_CODE = 'EVT'

AND ContHead.TEMPLATE_YN = 'N'

UNION ALL