PJB_XLA_BILLOFF_LINES_ADJ

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

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 ,'BILLING_OFFSETS' 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 ,-1 * 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 ,-1 * 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.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ') AND AcctEvt.REVERSED_ACCOUNTING_EVENT_ID IS NOT 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 ,'BILLING_OFFSETS' 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 ,-1 * 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 ,-1 * 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.VERSION_TYPE ='C' AND ContLines.STS_CODE IN ('UNDER_AMENDMENT','CLOSED') AND AcctEvt.org_id = BU.org_id AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT' AND AcctEvt.Contract_id=Conthead.id AND ContLines.dnz_chr_id= Conthead.id AND ContLines.Major_version=Conthead.major_version AND ContHead.version_type='C' AND ContHead.under_amend_version_Flag='N' AND ContHead.sts_code ='UNDER_AMENDMENT' 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.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ') AND AcctEvt.REVERSED_ACCOUNTING_EVENT_ID IS NOT 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

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

,'BILLING_OFFSETS' 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

,-1 * 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

,-1 * 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.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ')

AND AcctEvt.REVERSED_ACCOUNTING_EVENT_ID IS NOT 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