1. Tables and Views for Project Management
  2. PJB_XLA_REV_LINES_V

PJB_XLA_REV_LINES_V

Details

  • Schema: FUSION

  • Object owner: PJB

  • Object type: VIEW

Columns

Name

APPLICATION_ID

SOURCE_APPLICATION_ID

PJB_ENTITY_CODE

PJB_FIRST_DIST_ID

EVENT_ID

LINE_NUMBER

REV_LINE_NUMBER

REV_DISTRIBUTION_ID

LEDGER_ID

CONTRACT_ID

CONTRACT_LINE_ID

BILL_TRX_ID

BILL_TRX_MANUAL_FLAG

CONTRACT_PROJECT_LINKAGE_ID

REVENUE_PLAN_ID

REVENUE_METHOD_ID

CUSTOMER_ID

CUSTOMER_NUMBER

CUSTOMER_NAME

CUSTOMER_CLASS_CATEGORY

CUSTOMER_CLASS_NAME

LINKED_PROJECT_ID

LINKED_TASK_ID

TRANSACTION_TYPE_CODE

REVENUE_DISTRIBUTION_TYPE

EXPENDITURE_ITEM_ID

BILLING_EVENT_ID

TRANSACTION_PROJECT_ID

TRANSACTION_TASK_ID

EXPENDITURE_TYPE_ID

BILLING_EVENT_TYPE_ID

SYSTEM_LINKAGE_FUNCTION

RBS_ELEMENT_ID

REVENUE_CATEGORY

BILLING_TYPE_CODE

REVERSED_REV_DISTRIBUTION_ID

REVERSED_FLAG

REV_LINE_NUM_REVERSED

ACCT_CURR_REVENUE_AMT

EXCHANGE_RATE

EXCHANGE_RATE_TYPE

EXCHANGE_RATE_DATE

ENTERED_AMOUNT

ENTERED_CURRENCY_CODE

PROJECT_NULL_CCID

ACCOUNTING_DATE

ACCOUNTING_PERIOD

PROJECT_ACCOUNTING_DATE

PROJECT_ACCOUNTING_PERIOD

TEST_CCID

BILL_TO_SITE_USE_ID

CONTRACT_ORGANIZATION_ID

Query

SQL_Statement

SELECT

10036 APPLICATION_ID

,10035 SOURCE_APPLICATION_ID

,'REVENUE' PJB_ENTITY_CODE

,RevDist.REV_DISTRIBUTION_ID PJB_FIRST_DIST_ID

,RevDist.SLA_EVENT_ID EVENT_ID

,RevDist.LINE_NUM LINE_NUMBER

,RevDist.LINE_NUM REV_LINE_NUMBER

,RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID

,RevDist.LEDGER_ID LEDGER_ID

,RevDist.CONTRACT_ID CONTRACT_ID

,RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID

,RevDist.BILL_TRX_ID BILL_TRX_ID

,BillTrx.MANUAL_FLAG BILL_TRX_MANUAL_FLAG

,RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID

,RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID

,RevPlan.BILL_METHOD_ID REVENUE_METHOD_ID

,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID

,CustAcct.ACCOUNT_NUMBER CUSTOMER_NUMBER

,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_NAME

,NULL CUSTOMER_CLASS_CATEGORY

,CustAcct.customer_class_code CUSTOMER_CLASS_NAME

,RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID

,RevDist.LINKED_TASK_ID LINKED_TASK_ID

,RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE

,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI','Revenue - Expenditure Revenue','EVT','Revenue - Event Revenue') REVENUE_DISTRIBUTION_TYPE

,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

,RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID

,RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_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.SYSTEM_LINKAGE_FUNCTION SYSTEM_LINKAGE_FUNCTION

,RevDist.RBS_ELEMENT_ID RBS_ELEMENT_ID

,RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY

,RevDist.BILLING_TYPE_CODE BILLING_TYPE_CODE

,RevDist.REVERSED_REV_DISTRIBUTION_ID REVERSED_REV_DISTRIBUTION_ID

,RevDist.REVERSED_FLAG REVERSED_FLAG

,RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED

,RevDist.LEDGER_CURR_REVENUE_AMT ACCT_CURR_REVENUE_AMT

,RevDist.LEDGER_CURR_REV_EXCHG_RATE EXCHANGE_RATE

,RevDist.LEDGER_CURR_REV_RATE_TYPE EXCHANGE_RATE_TYPE

,RevDist.LEDGER_CURR_REV_EXCHG_DATE EXCHANGE_RATE_DATE

,RevDist.REVENUE_CURR_AMT ENTERED_AMOUNT

,RevDist.REVENUE_CURRENCY_CODE ENTERED_CURRENCY_CODE

,TO_NUMBER(NULL) PROJECT_NULL_CCID

,RevDist.GL_DATE ACCOUNTING_DATE

,RevDist.GL_PERIOD_NAME ACCOUNTING_PERIOD

,RevDist.PA_DATE PROJECT_ACCOUNTING_DATE

,RevDist.PA_PERIOD_NAME PROJECT_ACCOUNTING_PERIOD

,TO_NUMBER(NULL) TEST_CCID

,BillPlan.Bill_to_site_use_id BILL_TO_SITE_USE_ID

,ContHead.org_id CONTRACT_ORGANIZATION_ID

FROM

PJB_REV_DISTRIBUTIONS RevDist,

PJB_BILL_TRXS BillTrx,

PJB_BILL_PLANS_B RevPlan,

PJB_BILL_PLANS_B BillPlan,

HZ_CUST_ACCOUNTS CustAcct,

OKC_K_HEADERS_ALL_B ContHead,

OKC_K_LINES_B ContLine,

XLA_EVENTS_GT xlaEvt

WHERE

RevDist.CONTRACT_ID = ContHead.ID

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

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

AND RevDist.REVENUE_PLAN_ID = RevPlan.BILL_PLAN_ID

AND RevPlan.major_version = ContLine.major_version

AND RevDist.CONTRACT_LINE_ID = ContLine.ID

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

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

AND ContLine.major_Version = ContHead.major_Version

AND ContLine.dnz_chr_id = ContHead.id

AND BillTrx.BILL_TRX_ID = RevDist.BILL_TRX_ID

AND BillTrx.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID

AND BillPlan.major_Version = ContLine.major_Version

AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID

AND BillTrx.contract_id = ContHead.id

AND BillTrx.contract_line_id = ContLine.id

AND RevDist.REVERSED_REV_DISTRIBUTION_ID IS NULL

AND ContHead.TEMPLATE_YN = 'N'

AND xlaEvt.application_id = 10036

AND xlaEvt.SOURCE_ID_INT_1 = RevDist.rev_distribution_id

AND xlaEvt.event_id = RevDist.sla_event_id

AND RevDist.sla_event_id is not null

UNION ALL

SELECT

10036 APPLICATION_ID

,10035 SOURCE_APPLICATION_ID

,'REVENUE' PJB_ENTITY_CODE

,RevDist.REV_DISTRIBUTION_ID PJB_FIRST_DIST_ID

,RevDist.SLA_EVENT_ID EVENT_ID

,RevDist.LINE_NUM LINE_NUMBER

,RevDist.LINE_NUM REV_LINE_NUMBER

,RevDist.REV_DISTRIBUTION_ID REV_DISTRIBUTION_ID

,RevDist.LEDGER_ID LEDGER_ID

,RevDist.CONTRACT_ID CONTRACT_ID

,RevDist.CONTRACT_LINE_ID CONTRACT_LINE_ID

,RevDist.BILL_TRX_ID BILL_TRX_ID

,NULL BILL_TRX_MANUAL_FLAG

,RevDist.CONTRACT_PROJECT_LINKAGE_ID CONTRACT_PROJECT_LINKAGE_ID

,RevDist.REVENUE_PLAN_ID REVENUE_PLAN_ID

,RevPlan.BILL_METHOD_ID REVENUE_METHOD_ID

,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID

,CustAcct.ACCOUNT_NUMBER CUSTOMER_NUMBER

,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_NAME

,NULL CUSTOMER_CLASS_CATEGORY

,CustAcct.Customer_Class_code CUSTOMER_CLASS_NAME

,RevDist.LINKED_PROJECT_ID LINKED_PROJECT_ID

,RevDist.LINKED_TASK_ID LINKED_TASK_ID

,RevDist.BILL_TRANSACTION_TYPE_CODE TRANSACTION_TYPE_CODE

,DECODE(RevDist.BILL_TRANSACTION_TYPE_CODE, 'EI','Revenue - Expenditure Revenue','EVT','Revenue - Event Revenue') REVENUE_DISTRIBUTION_TYPE

,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

,RevDist.TRANSACTION_PROJECT_ID TRANSACTION_PROJECT_ID

,RevDist.TRANSACTION_TASK_ID TRANSACTION_TASK_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.SYSTEM_LINKAGE_FUNCTION SYSTEM_LINKAGE_FUNCTION

,RevDist.RBS_ELEMENT_ID RBS_ELEMENT_ID

,RevDist.REVENUE_CATEGORY_CODE REVENUE_CATEGORY

,RevDist.BILLING_TYPE_CODE BILLING_TYPE_CODE

,RevDist.REVERSED_REV_DISTRIBUTION_ID REVERSED_REV_DISTRIBUTION_ID

,RevDist.REVERSED_FLAG REVERSED_FLAG

,RevDist.LINE_NUM_REVERSED REV_LINE_NUM_REVERSED

,RevDist.LEDGER_CURR_REVENUE_AMT ACCT_CURR_REVENUE_AMT

,RevDist.LEDGER_CURR_REV_EXCHG_RATE EXCHANGE_RATE

,RevDist.LEDGER_CURR_REV_RATE_TYPE EXCHANGE_RATE_TYPE

,RevDist.LEDGER_CURR_REV_EXCHG_DATE EXCHANGE_RATE_DATE

,RevDist.REVENUE_CURR_AMT ENTERED_AMOUNT

,RevDist.REVENUE_CURRENCY_CODE ENTERED_CURRENCY_CODE

,TO_NUMBER(NULL) PROJECT_NULL_CCID

,RevDist.GL_DATE ACCOUNTING_DATE

,RevDist.GL_PERIOD_NAME ACCOUNTING_PERIOD

,RevDist.PA_DATE PROJECT_ACCOUNTING_DATE

,RevDist.PA_PERIOD_NAME PROJECT_ACCOUNTING_PERIOD

,TO_NUMBER(NULL) TEST_CCID

,BillPlan.Bill_to_site_use_id BILL_TO_SITE_USE_ID

,ContHead.org_id CONTRACT_ORGANIZATION_ID

FROM

PJB_REV_DISTRIBUTIONS RevDist,

PJB_BILL_PLANS_B RevPlan,

PJB_BILL_PLANS_B BillPlan,

HZ_CUST_ACCOUNTS CustAcct,

OKC_K_HEADERS_ALL_B ContHead,

OKC_K_LINES_B ContLine,

OKC_BU_IMPL_OPTIONS_ALL BU,

XLA_EVENTS_GT xlaEvt

WHERE

RevDist.CONTRACT_ID = ContHead.ID

AND ContHead.VERSION_TYPE ='C'

AND ContHead.STS_CODE = 'UNDER_AMENDMENT'

AND ContHead.under_amend_version_flag = 'N'

AND RevDist.REVENUE_PLAN_ID = RevPlan.BILL_PLAN_ID

AND RevPlan.major_version = ContLine.major_version

AND RevDist.CONTRACT_LINE_ID = ContLine.ID

AND ContLine.VERSION_TYPE ='C'

AND ContLine.STS_CODE IN ( 'UNDER_AMENDMENT' ,'CLOSED')

AND ContLine.major_Version = ContHead.major_Version

AND ContLine.dnz_chr_id=ContHead.id

AND ContLine.Bill_plan_id = Billplan.Bill_plan_id

AND BillPlan.major_Version = ContLine.major_Version

AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID

AND RevDist.REVERSED_REV_DISTRIBUTION_ID IS NULL

AND ContHead.TEMPLATE_YN = 'N'

AND ContHead.Org_id = Bu.Org_id

AND Bu.attribute15 = 'INCLUDE_UNDER_AMENDMENT'

AND xlaEvt.application_id = 10036

AND xlaEvt.SOURCE_ID_INT_1 = RevDist.rev_distribution_id

AND xlaEvt.event_id = RevDist.sla_event_id

AND RevDist.sla_event_id is not null