PJB_XLA_BILLOFF_V

Details

• Schema: FUSION

• Object owner: PJB

• Object type: VIEW

Columns

Name

CONTRACT_ID CONTRACT_NAME CONTRACT_NUMBER MAJOR_VERSION CONTRACT_TYPE_ID CONTRACT_TYPE CONTRACT_ORGANIZATION_NAME CONTRACT_ORGANIZATION_ID CONTRACT_LINE_ID CONTRACT_LINE ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_TYPE BO_LINE_NUMBER BILLING_TYPE_CODE ENTERED_CURRENCY_CODE ENTERED_CURR_AMT LEDGER_CURR_CODE LEDGER_CURR_AMT PROJECT_ACCOUNTING_PERIOD PROJECT_ACCOUNTING_DATE ACCOUNTING_DATE ACCOUNTING_PERIOD REVERSED_ACCOUNTING_EVENT_ID BO_LINE_REVERSED_FLAG BO_LINE_NUM_REVERSED

Query

SQL_Statement

SELECT ContHead.ID CONTRACT_ID ,ContHead.COGNOMEN CONTRACT_NAME ,ContHead.CONTRACT_NUMBER CONTRACT_NUMBER ,ContHead.MAJOR_VERSION MAJOR_VERSION ,ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID ,ContType.NAME CONTRACT_TYPE ,OrgUnit.NAME CONTRACT_ORGANIZATION_NAME ,ContHead.ORG_ID CONTRACT_ORGANIZATION_ID ,ActEvt.CONTRACT_LINE_ID CONTRACT_LINE_ID ,ContLine.LINE_NUMBER CONTRACT_LINE ,ActEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID ,ActEvt.ACCOUNTING_EVENT_TYPE ACCOUNTING_EVENT_TYPE ,ActEvt.LINE_NUM BO_LINE_NUMBER ,ActEvt.BILLING_TYPE_CODE BILLING_TYPE_CODE ,ActEvt.ENTERED_CURR_CODE ENTERED_CURRENCY_CODE ,ActEvt.ENTERED_CURR_AMT ENTERED_CURR_AMT ,ActEvt.LEDGER_CURR_CODE LEDGER_CURR_CODE ,ActEvt.LEDGER_CURR_AMT LEDGER_CURR_AMT ,ActEvt.PA_PERIOD PROJECT_ACCOUNTING_PERIOD ,ActEvt.PA_DATE PROJECT_ACCOUNTING_DATE ,ActEvt.ACCOUNTING_DATE ACCOUNTING_DATE ,ActEvt.ACCOUNTING_PERIOD ACCOUNTING_PERIOD ,ActEvt.REVERSED_ACCOUNTING_EVENT_ID REVERSED_ACCOUNTING_EVENT_ID ,ActEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG ,ActEvt.REVERSED_LINE_NUM BO_LINE_NUM_REVERSED FROM PJB_ACCOUNTING_EVENTS ActEvt ,OKC_K_HEADERS_VL ContHead ,OKC_CONTRACT_TYPES_VL ContType ,OKC_K_LINES_B ContLine ,HR_ALL_ORGANIZATION_UNITS OrgUnit WHERE 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 ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND ActEvt.CONTRACT_LINE_ID = ContLine.ID AND ContLine.VERSION_TYPE IN ('A','C') AND ContLine.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContHead.ORG_ID = OrgUnit.ORGANIZATION_ID AND ContHead.TEMPLATE_YN = 'N' AND ContHead.ID = ContLine.CHR_ID UNION ALL SELECT ContHead.ID CONTRACT_ID ,ContHead.COGNOMEN CONTRACT_NAME ,ContHead.CONTRACT_NUMBER CONTRACT_NUMBER ,ContHead.MAJOR_VERSION MAJOR_VERSION ,ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID ,ContType.NAME CONTRACT_TYPE ,OrgUnit.NAME CONTRACT_ORGANIZATION_NAME ,ContHead.ORG_ID CONTRACT_ORGANIZATION_ID ,ActEvt.CONTRACT_LINE_ID CONTRACT_LINE_ID ,ContLine.LINE_NUMBER CONTRACT_LINE ,ActEvt.ACCOUNTING_EVENT_ID ACCOUNTING_EVENT_ID ,ActEvt.ACCOUNTING_EVENT_TYPE ACCOUNTING_EVENT_TYPE ,ActEvt.LINE_NUM BO_LINE_NUMBER ,ActEvt.BILLING_TYPE_CODE BILLING_TYPE_CODE ,ActEvt.ENTERED_CURR_CODE ENTERED_CURRENCY_CODE ,ActEvt.ENTERED_CURR_AMT ENTERED_CURR_AMT ,ActEvt.LEDGER_CURR_CODE LEDGER_CURR_CODE ,ActEvt.LEDGER_CURR_AMT LEDGER_CURR_AMT ,ActEvt.PA_PERIOD PROJECT_ACCOUNTING_PERIOD ,ActEvt.PA_DATE PROJECT_ACCOUNTING_DATE ,ActEvt.ACCOUNTING_DATE ACCOUNTING_DATE ,ActEvt.ACCOUNTING_PERIOD ACCOUNTING_PERIOD ,ActEvt.REVERSED_ACCOUNTING_EVENT_ID REVERSED_ACCOUNTING_EVENT_ID ,ActEvt.REVERSED_FLAG BO_LINE_REVERSED_FLAG ,ActEvt.REVERSED_LINE_NUM BO_LINE_NUM_REVERSED FROM PJB_ACCOUNTING_EVENTS ActEvt ,OKC_K_HEADERS_VL ContHead ,OKC_CONTRACT_TYPES_VL ContType ,OKC_K_LINES_B ContLine ,HR_ALL_ORGANIZATION_UNITS OrgUnit ,OKC_BU_IMPL_OPTIONS_ALL BU WHERE ActEvt.ACCOUNTING_EVENT_TYPE IN ('BILLING_OFFSETS','BILLING_OFFSETS_ADJ','PERFORM_OB_ACCR') AND ActEvt.CONTRACT_ID = ContHead.ID AND ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND ActEvt.CONTRACT_LINE_ID = ContLine.ID AND ContHead.ORG_ID = OrgUnit.ORGANIZATION_ID AND ContHead.TEMPLATE_YN = 'N' AND ContHead.VERSION_TYPE ='C' AND ContHead.STS_CODE ='UNDER_AMENDMENT' AND ContLine.VERSION_TYPE ='C' AND ContLine.STS_CODE IN ( 'UNDER_AMENDMENT','CLOSED') AND ContHead.under_amend_version_flag ='N' AND ActEvt.Org_id = BU.org_id AND BU.attribute15 ='INCLUDE_UNDER_AMENDMENT'