PJB_XLA_BILLOFF_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 ACCOUNTING_EVENT_ID LINE_NUMBER BO_LINE_NUMBER CUSTOMER_ID CUSTOMER_NUMBER CUSTOMER_NAME LEDGER_ID ACCOUNTING_EVENT_TYPE BILLING_TYPE_CODE REVERSED_ACCOUNTING_EVENT_ID BO_LINE_REVERSED_FLAG BO_LINE_NUM_REVERSED ACCOUNTING_DATE ACCOUNTING_PERIOD ACCT_CURRENCY_CODE ACCT_CURR_ACCT_EVT_AMT EXCHANGE_RATE EXCHANGE_RATE_TYPE EXCHANGE_RATE_DATE ENTERED_AMOUNT ENTERED_CURRENCY_CODE PROJECT_NULL_CCID TEST_CCID BILL_TO_SITE_USE_ID CONTRACT_ORGANIZATION_ID

Query

SQL_Statement

SELECT 10036 APPLICATION_ID ,10035 SOURCE_APPLICATION_ID ,'PJBACCOUNTING' PJB_ENTITY_CODE ,AcctEvt.ACCOUNTING_EVENT_ID PJB_FIRST_DIST_ID ,AcctEvt.SLA_EVENT_ID EVENT_ID ,AcctEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID ,AcctEvt.LINE_NUM LINE_NUMBER ,AcctEvt.LINE_NUM BO_LINE_NUMBER ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID ,CustAcct.ACCOUNT_NUMBER CUSTOMER_NUMBER ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_NAME ,AcctEvt.LEDGER_ID LEDGER_ID ,ACCOUNTING_EVENT_TYPE ,AcctEvt.BILLING_TYPE_CODE BILLING_TYPE_CODE ,AcctEvt.REVERSED_ACCOUNTING_EVENT_ID REVERSED_ACCOUNTING_EVENT_ID ,AcctEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG ,AcctEvt.REVERSED_LINE_NUM BO_LINE_NUM_REVERSED ,AcctEvt.ACCOUNTING_DATE ACCOUNTING_DATE ,AcctEvt.ACCOUNTING_PERIOD ACCOUNTING_PERIOD ,AcctEvt.LEDGER_CURR_CODE ACCT_CURRENCY_CODE ,AcctEvt.LEDGER_CURR_AMT ACCT_CURR_ACCT_EVT_AMT ,AcctEvt.LEDGER_CURR_EXCHG_RATE EXCHANGE_RATE ,AcctEvt.LEDGER_CURR_RATE_TYPE EXCHANGE_RATE_TYPE ,AcctEvt.LEDGER_CURR_EXCHG_DATE EXCHANGE_RATE_DATE ,AcctEvt.ENTERED_CURR_AMT ENTERED_AMOUNT ,AcctEvt.ENTERED_CURR_CODE ENTERED_CURRENCY_CODE ,TO_NUMBER(NULL) PROJECT_NULL_CCID ,TO_NUMBER(NULL) TEST_CCID ,BillPlan.Bill_to_site_use_id BILL_TO_SITE_USE_ID ,AcctEvt.org_id CONTRACT_ORGANIZATION_ID FROM PJB_ACCOUNTING_EVENTS AcctEvt, OKC_K_LINES_B ContLines, PJB_BILL_PLANS_B BillPlan, HZ_CUST_ACCOUNTS CustAcct, XLA_EVENTS_GT xlaEvt WHERE AcctEvt.CONTRACT_LINE_ID = ContLines.ID AND ContLines.VERSION_TYPE IN ('A','C') AND ContLines.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContLines.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID AND BillPlan.MAJOR_VERSION = ContLines.MAJOR_VERSION AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID AND AcctEvt.REVERSED_ACCOUNTING_EVENT_ID IS NULL AND xlaEvt.application_id = 10036 AND xlaEvt.SOURCE_ID_INT_1 = AcctEvt.Accounting_event_id AND xlaEvt.event_id = AcctEvt.sla_event_id AND AcctEvt.sla_event_id is not null UNION ALL SELECT 10036 APPLICATION_ID ,10035 SOURCE_APPLICATION_ID ,'PJBACCOUNTING' PJB_ENTITY_CODE ,AcctEvt.ACCOUNTING_EVENT_ID PJB_FIRST_DIST_ID ,AcctEvt.SLA_EVENT_ID EVENT_ID ,AcctEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID ,AcctEvt.LINE_NUM LINE_NUMBER ,AcctEvt.LINE_NUM BO_LINE_NUMBER ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_ID ,CustAcct.ACCOUNT_NUMBER CUSTOMER_NUMBER ,BillPlan.BILL_TO_CUST_ACCT_ID CUSTOMER_NAME ,AcctEvt.LEDGER_ID LEDGER_ID ,ACCOUNTING_EVENT_TYPE ,AcctEvt.BILLING_TYPE_CODE BILLING_TYPE_CODE ,AcctEvt.REVERSED_ACCOUNTING_EVENT_ID REVERSED_ACCOUNTING_EVENT_ID ,AcctEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG ,AcctEvt.REVERSED_LINE_NUM BO_LINE_NUM_REVERSED ,AcctEvt.ACCOUNTING_DATE ACCOUNTING_DATE ,AcctEvt.ACCOUNTING_PERIOD ACCOUNTING_PERIOD ,AcctEvt.LEDGER_CURR_CODE ACCT_CURRENCY_CODE ,AcctEvt.LEDGER_CURR_AMT ACCT_CURR_ACCT_EVT_AMT ,AcctEvt.LEDGER_CURR_EXCHG_RATE EXCHANGE_RATE ,AcctEvt.LEDGER_CURR_RATE_TYPE EXCHANGE_RATE_TYPE ,AcctEvt.LEDGER_CURR_EXCHG_DATE EXCHANGE_RATE_DATE ,AcctEvt.ENTERED_CURR_AMT ENTERED_AMOUNT ,AcctEvt.ENTERED_CURR_CODE ENTERED_CURRENCY_CODE ,TO_NUMBER(NULL) PROJECT_NULL_CCID ,TO_NUMBER(NULL) TEST_CCID ,BillPlan.Bill_to_site_use_id BILL_TO_SITE_USE_ID ,ContHead.org_id CONTRACT_ORGANIZATION_ID FROM PJB_ACCOUNTING_EVENTS AcctEvt, OKC_K_LINES_B ContLines, PJB_BILL_PLANS_B BillPlan, HZ_CUST_ACCOUNTS CustAcct, OKC_K_HEADERS_ALL_B ContHead, OKC_BU_IMPL_OPTIONS_ALL BU, XLA_EVENTS_GT xlaEvt WHERE AcctEvt.CONTRACT_LINE_ID = ContLines.ID AND ContLines.BILL_PLAN_ID = BillPlan.BILL_PLAN_ID AND BillPlan.MAJOR_VERSION = ContLines.MAJOR_VERSION AND BillPlan.BILL_TO_CUST_ACCT_ID = CustAcct.CUST_ACCOUNT_ID AND AcctEvt.REVERSED_ACCOUNTING_EVENT_ID IS NULL AND ContLines.DNZ_CHR_ID= ContHead.id AND ContLines.major_Version=ContHead.major_version AND ContLines.Version_type='C' AND ContLines.STS_CODE IN ('UNDER_AMENDMENT','CLOSED') AND ContHead.Sts_Code ='UNDER_AMENDMENT' AND ContHead.Version_type='C' AND ContHead.under_amend_version_flag='N' AND AcctEvt.org_id = Bu.org_id AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT' AND xlaEvt.application_id = 10036 AND xlaEvt.SOURCE_ID_INT_1 = AcctEvt.Accounting_event_id AND xlaEvt.event_id = AcctEvt.sla_event_id AND AcctEvt.sla_event_id is not null