PJB_XLA_BILLOFF_HEADERS

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
EVENT_ID CONTRACT_ID CONTRACT_TYPE_ID CONTRACT_LINE_ID ACCOUNTING_EVENT_ID ACCOUNTING_DATE BO_LINE_REVERSED_FLAG CREDITING_FLAG ACCT_TXN_CREATION_DATE ACCT_TXN_CREATED_BY ACCT_TXN_LAST_UPDATED_DATE ACCT_TXN_LAST_UPDATED_BY

Name

EVENT_ID

CONTRACT_ID

CONTRACT_TYPE_ID

CONTRACT_LINE_ID

ACCOUNTING_EVENT_ID

ACCOUNTING_DATE

BO_LINE_REVERSED_FLAG

CREDITING_FLAG

ACCT_TXN_CREATION_DATE

ACCT_TXN_CREATED_BY

ACCT_TXN_LAST_UPDATED_DATE

ACCT_TXN_LAST_UPDATED_BY

Query

SQL_Statement
SELECT Xev.EVENT_ID EVENT_ID , ContHead.ID CONTRACT_ID , ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID , ActEvt.CONTRACT_LINE_ID CONTRACT_LINE_ID , ActEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID , ActEvt.ACCOUNTING_DATE ACCOUNTING_DATE , ActEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG , DECODE(ActEvt.REVERSED_ACCOUNTING_EVENT_ID, NULL, 'N', 'Y') CREDITING_FLAG , trunc(ActEvt.CREATION_DATE) ACCT_TXN_CREATION_DATE , ActEvt.CREATED_BY ACCT_TXN_CREATED_BY , trunc(ActEvt.LAST_UPDATE_DATE) ACCT_TXN_LAST_UPDATED_DATE , ActEvt.LAST_UPDATED_BY ACCT_TXN_LAST_UPDATED_BY FROM PJB_ACCOUNTING_EVENTS ActEvt, OKC_K_HEADERS_ALL_B ContHead, XLA_EVENTS_GT Xev WHERE Xev.APPLICATION_ID = 10036 AND Xev.SOURCE_ID_INT_1 = ActEvt.ACCOUNTING_EVENT_ID AND (ActEvt.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ','PERFORM_OB_ACCR') AND ActEvt.CONTRACT_ID = ContHead.ID AND ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD')) AND ActEvt.SLA_EVENT_ID IS NOT NULL AND ContHead.TEMPLATE_YN = 'N' UNION ALL SELECT Xev.EVENT_ID EVENT_ID , ContHead.ID CONTRACT_ID , ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID , ActEvt.CONTRACT_LINE_ID CONTRACT_LINE_ID , ActEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID , ActEvt.ACCOUNTING_DATE ACCOUNTING_DATE , ActEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG , DECODE(ActEvt.REVERSED_ACCOUNTING_EVENT_ID, NULL, 'N', 'Y') CREDITING_FLAG , trunc(ActEvt.CREATION_DATE) ACCT_TXN_CREATION_DATE , ActEvt.CREATED_BY ACCT_TXN_CREATED_BY , trunc(ActEvt.LAST_UPDATE_DATE) ACCT_TXN_LAST_UPDATED_DATE , ActEvt.LAST_UPDATED_BY ACCT_TXN_LAST_UPDATED_BY FROM PJB_ACCOUNTING_EVENTS ActEvt, 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 = ActEvt.ACCOUNTING_EVENT_ID AND ActEvt.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ','PERFORM_OB_ACCR') AND ActEvt.CONTRACT_ID = ContHead.ID AND ActEvt.SLA_EVENT_ID IS NOT NULL AND ContHead.TEMPLATE_YN = 'N' AND ContHead.Version_type = 'C' AND ContHead.sts_code ='UNDER_AMENDMENT' AND ContHead.under_amend_version_flag ='N' AND ActEvt.Org_Id = BU.Org_Id AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT'

SQL_Statement

SELECT

Xev.EVENT_ID EVENT_ID

, ContHead.ID CONTRACT_ID

, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID

, ActEvt.CONTRACT_LINE_ID CONTRACT_LINE_ID

, ActEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID

, ActEvt.ACCOUNTING_DATE ACCOUNTING_DATE

, ActEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG

, DECODE(ActEvt.REVERSED_ACCOUNTING_EVENT_ID, NULL, 'N', 'Y') CREDITING_FLAG

, trunc(ActEvt.CREATION_DATE) ACCT_TXN_CREATION_DATE

, ActEvt.CREATED_BY ACCT_TXN_CREATED_BY

, trunc(ActEvt.LAST_UPDATE_DATE) ACCT_TXN_LAST_UPDATED_DATE

, ActEvt.LAST_UPDATED_BY ACCT_TXN_LAST_UPDATED_BY

FROM

PJB_ACCOUNTING_EVENTS ActEvt,

OKC_K_HEADERS_ALL_B ContHead,

XLA_EVENTS_GT Xev

WHERE

Xev.APPLICATION_ID = 10036

AND Xev.SOURCE_ID_INT_1 = ActEvt.ACCOUNTING_EVENT_ID

AND (ActEvt.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ','PERFORM_OB_ACCR')

AND ActEvt.CONTRACT_ID = ContHead.ID

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

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

AND ActEvt.SLA_EVENT_ID IS NOT NULL

AND ContHead.TEMPLATE_YN = 'N'

UNION ALL