PJC_XLA_BTC_CDL_LINES

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
APPLICATION_ID EVENT_ID EXPENDITURE_ITEM_ID LINE_NUMBER CDL_LINE_NUMBER LEDGER_ID REVERSING_LINE_FLAG LINE_NUM_REVERSED LINE_TYPE ENTERED_CURRENCY_CODE ENTERED_RAW_COST ENTERED_BURDEN_COST ENTERED_BURDENED_COST ACCT_RAW_COST ACCT_BURDEN_COST ACCT_BURDENED_COST EXCHANGE_RATE EXCHANGE_RATE_TYPE EXCHANGE_RATE_DATE BILLABLE_FLAG CAPITALIZABLE_FLAG ACCOUNTING_PERIOD PROJECT_ACCOUNTING_DATE PROJECT_ACCOUNTING_PERIOD PO_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID CREATE_RAW_ACCT_JOURNALS CREATE_BURDEN_ACCT_JOURNALS CREATE_BURDENED_ACCT_JOURNALS IMPORTED_RAW_DR_ACCOUNT_CCID IMPORTED_RAW_CR_ACCOUNT_CCID IMPORTED_BURDEN_DR_ACC_CCID IMPORTED_BURDEN_CR_ACC_CCID IMPORTED_BURDENED_DR_ACC_CCID IMPORTED_BURDENED_CR_ACC_CCID ACCOUNTING_SOURCE_CODE ADJ_RAW_DR_ACCOUNT_CCID ADJ_RAW_CR_ACCOUNT_CCID ADJ_BURDEN_DR_ACC_CCID ADJ_BURDEN_CR_ACC_CCID ADJ_BURDENED_DR_ACC_CCID ADJ_BURDENED_CR_ACC_CCID BUS_FLOW_PO_APP_ID BUS_FLOW_PO_ENTITY_CODE BUS_FLOW_REQ_ENTITY_CODE BUS_FLOW_BURDEN_DIST_TYPE BUS_FLOW_BACKING_HEADER_ID1 BUS_FLOW_BACKING_HEADER_ID2 BUS_FLOW_BACKING_DIST_ID1 BUS_FLOW_BACKING_DIST_ID2 BUS_FLOW_BACKING_DIST_ID3 BUS_FLOW_BACKING_AMOUNT ENCUMBRANCE_TYPE_ID BC_COMPLETE_STATUS DATA_SET_ID WORK_TYPE_ID WORK_TYPE_NAME EXP_CDL_DIST_CREATION_DATE EXP_CDL_DIST_CREATED_BY EXP_CDL_LAST_UPDATED_DATE EXP_CDL_LAST_UPDATED_BY

Name

APPLICATION_ID

EVENT_ID

EXPENDITURE_ITEM_ID

LINE_NUMBER

CDL_LINE_NUMBER

LEDGER_ID

REVERSING_LINE_FLAG

LINE_NUM_REVERSED

LINE_TYPE

ENTERED_CURRENCY_CODE

ENTERED_RAW_COST

ENTERED_BURDEN_COST

ENTERED_BURDENED_COST

ACCT_RAW_COST

ACCT_BURDEN_COST

ACCT_BURDENED_COST

EXCHANGE_RATE

EXCHANGE_RATE_TYPE

EXCHANGE_RATE_DATE

BILLABLE_FLAG

CAPITALIZABLE_FLAG

ACCOUNTING_PERIOD

PROJECT_ACCOUNTING_DATE

PROJECT_ACCOUNTING_PERIOD

PO_DISTRIBUTION_ID

INVOICE_DISTRIBUTION_ID

CREATE_RAW_ACCT_JOURNALS

CREATE_BURDEN_ACCT_JOURNALS

CREATE_BURDENED_ACCT_JOURNALS

IMPORTED_RAW_DR_ACCOUNT_CCID

IMPORTED_RAW_CR_ACCOUNT_CCID

IMPORTED_BURDEN_DR_ACC_CCID

IMPORTED_BURDEN_CR_ACC_CCID

IMPORTED_BURDENED_DR_ACC_CCID

IMPORTED_BURDENED_CR_ACC_CCID

ACCOUNTING_SOURCE_CODE

ADJ_RAW_DR_ACCOUNT_CCID

ADJ_RAW_CR_ACCOUNT_CCID

ADJ_BURDEN_DR_ACC_CCID

ADJ_BURDEN_CR_ACC_CCID

ADJ_BURDENED_DR_ACC_CCID

ADJ_BURDENED_CR_ACC_CCID

BUS_FLOW_PO_APP_ID

BUS_FLOW_PO_ENTITY_CODE

BUS_FLOW_REQ_ENTITY_CODE

BUS_FLOW_BURDEN_DIST_TYPE

BUS_FLOW_BACKING_HEADER_ID1

BUS_FLOW_BACKING_HEADER_ID2

BUS_FLOW_BACKING_DIST_ID1

BUS_FLOW_BACKING_DIST_ID2

BUS_FLOW_BACKING_DIST_ID3

BUS_FLOW_BACKING_AMOUNT

ENCUMBRANCE_TYPE_ID

BC_COMPLETE_STATUS

DATA_SET_ID

WORK_TYPE_ID

WORK_TYPE_NAME

EXP_CDL_DIST_CREATION_DATE

EXP_CDL_DIST_CREATED_BY

EXP_CDL_LAST_UPDATED_DATE

EXP_CDL_LAST_UPDATED_BY

Query

SQL_Statement
( SELECT 10036 application_id, cdl.acct_event_id event_id, cdl.expenditure_item_id, cdl.line_num line_number, cdl.line_num cdl_line_number, imp.ledger_id ledger_id, DECODE(NVL(cdl.acct_source_code, 'UPG'), 'UPG', DECODE(cdl.line_type, 'R', 'Y', 'N'), 'PA', DECODE(cdl.line_num_reversed, NULL, 'N', 'Y'), 'N') Reversing_Line_Flag, to_number(DECODE(NVL(cdl.acct_source_code, 'UPG'), 'PA', DECODE(cdl.line_num_reversed, NULL, NULL, cdl.line_num_reversed), NULL)) line_num_reversed, cdl.line_type, cdl.denom_currency_code Entered_Currency_Code, NVL(cdl.denom_raw_cost,0) Entered_Raw_Cost, DECODE(NVL(cdl.denom_burdened_cost,0), 0, 0, NVL((cdl.denom_burdened_cost -cdl.denom_raw_cost),0)) entered_burden_cost, NVL(cdl.denom_burdened_cost,0) Entered_Burdened_Cost, NVL(cdl.acct_raw_cost,0) Acct_Raw_Cost, DECODE(NVL(cdl.acct_burdened_cost,0), 0, 0, NVL((cdl.acct_burdened_cost -cdl.acct_raw_cost),0)) acct_burden_cost, NVL(cdl.acct_burdened_cost,0) Acct_Burdened_Cost, cdl.acct_exchange_rate Exchange_Rate, cdl.acct_rate_type Exchange_Rate_Type, cdl.acct_rate_date Exchange_Rate_Date, cdl.Billable_flag, cdl.capitalizable_flag, cdl.PRVDR_GL_PERIOD_NAME Accounting_Period, cdl.prvdr_pa_date Project_Accounting_DATE, cdl.prvdr_pa_period_NAME Project_Accounting_period, EI.Parent_Dist_Id PO_Distribution_ID, EI.Parent_Dist_Id Invoice_Distribution_ID, DECODE(SUBSTR(accounting_status_code,2,1),'P','Y','N') CREATE_RAW_ACCT_JOURNALS, DECODE(SUBSTR(accounting_status_code,4,1),'P','Y','N') CREATE_BURDEN_ACCT_JOURNALS, DECODE(SUBSTR(accounting_status_code,5,1),'P','Y','N') CREATE_BURDENED_ACCT_JOURNALS, cdl.RAW_COST_DR_CCID IMPORTED_RAW_DR_ACCOUNT_CCID, cdl.RAW_COST_CR_CCID IMPORTED_RAW_CR_ACCOUNT_CCID, CDL.BURDEN_COST_DR_CCID IMPORTED_BURDEN_DR_ACC_CCID, CDL.BURDEN_COST_CR_CCID IMPORTED_BURDEN_CR_ACC_CCID, CDL.BURDENED_COST_DR_CCID IMPORTED_BURDENED_DR_ACC_CCID, CDL.BURDENED_COST_CR_CCID IMPORTED_BURDENED_CR_ACC_CCID, cdl.Acct_Source_Code accounting_source_code, to_number(null) adj_raw_dr_account_ccid, to_number(null)adj_raw_cr_account_ccid, to_number(null)adj_burden_dr_acc_ccid, to_number(null)adj_burden_cr_acc_ccid, to_number(null)adj_burdened_dr_acc_ccid, to_number(null)adj_burdened_cr_acc_ccid, null BUS_FLOW_PO_APP_ID, null BUS_FLOW_PO_ENTITY_CODE, null BUS_FLOW_REQ_ENTITY_CODE, null BUS_FLOW_BURDEN_DIST_TYPE, null BUS_FLOW_BACKING_HEADER_ID1, null BUS_FLOW_BACKING_HEADER_ID2, null BUS_FLOW_BACKING_DIST_ID1, null BUS_FLOW_BACKING_DIST_ID2, null BUS_FLOW_BACKING_DIST_ID3, null BUS_FLOW_BACKING_AMOUNT, null ENCUMBRANCE_TYPE_ID, decode (CDL.DATA_SET_ID, NULL, 'N', 'Y') BC_COMPLETE_STATUS, CDL.DATA_SET_ID DATA_SET_ID, cdl.work_type_id WORK_TYPE_ID, cdl.work_type_id WORK_TYPE_NAME, trunc(cdl.CREATION_DATE) EXP_CDL_DIST_CREATION_DATE, cdl.CREATED_BY EXP_CDL_DIST_CREATED_BY, trunc(cdl.LAST_UPDATE_DATE) EXP_CDL_LAST_UPDATED_DATE, cdl.LAST_UPDATED_BY EXP_CDL_LAST_UPDATED_BY FROM pjc_exp_items_all ei , pjc_cost_dist_lines_all cdl, xla_events_gt imp WHERE ei.expenditure_item_id = cdl.expenditure_item_id AND imp.event_id = cdl.acct_event_id AND imp.source_id_int_1 = cdl.expenditure_item_id) UNION ALL ( SELECT 10036 application_id, burden.burden_event_id event_id, burden.btc_exp_id expenditure_item_id, burden.burden_distribution_id line_number, burden.burden_distribution_id cdl_line_number, imp.ledger_id ledger_id, 'N' Reversing_Line_Flag, NULL line_num_reversed, 'B' line_type, burden.entered_currency Entered_Currency_Code, 0 Entered_Raw_Cost, 0 Entered_Burden_Cost, 0 Entered_Burdened_Cost, 0 Acct_Raw_Cost, 0 Acct_Burden_Cost, 0 Acct_Burdened_Cost, null Exchange_Rate, null Exchange_Rate_Type, null Exchange_Rate_Date, null Billable_flag, null capitalizable_flag, null Accounting_Period, null Project_Accounting_DATE, null Project_Accounting_period, null PO_Distribution_ID, null Invoice_Distribution_ID, null CREATE_RAW_ACCT_JOURNALS, null CREATE_BURDEN_ACCT_JOURNALS, null CREATE_BURDENED_ACCT_JOURNALS, null IMPORTED_RAW_DR_ACCOUNT_CCID, null IMPORTED_RAW_CR_ACCOUNT_CCID, null IMPORTED_BURDEN_DR_ACC_CCID, null IMPORTED_BURDEN_CR_ACC_CCID, null IMPORTED_BURDENED_DR_ACC_CCID, null IMPORTED_BURDENED_CR_ACC_CCID, 'PA' accounting_source_code, to_number(null) adj_raw_dr_account_ccid, to_number(null)adj_raw_cr_account_ccid, to_number(null)adj_burden_dr_acc_ccid, to_number(null)adj_burden_cr_acc_ccid, to_number(null)adj_burdened_dr_acc_ccid, to_number(null)adj_burdened_cr_acc_ccid, 201 BUS_FLOW_PO_APP_ID, 'PO' BUS_FLOW_PO_ENTITY_CODE, DECODE(burden.source_action_code,'PPM_RCV_IMT_EXP_VALIDATE','REQ',null) BUS_FLOW_REQ_ENTITY_CODE, 'PJC_XCC_BURDEN_DISTS' BUS_FLOW_BURDEN_DIST_TYPE, DECODE(burden.source_action_code,'PPM_RCV_INVENTORY_VALIDATE',to_number(null),'PPM_RCV_WORK_ORDER_VALIDATE',to_number(null),burden.BACKING_HEADER_ID1) BUS_FLOW_BACKING_HEADER_ID1, burden.BACKING_HEADER_ID1 BUS_FLOW_BACKING_HEADER_ID2, burden.BACKING_DIST_ID1 BUS_FLOW_BACKING_DIST_ID1, burden.BACKING_DIST_ID2 BUS_FLOW_BACKING_DIST_ID2, burden.BACKING_BURDEN_DIST_ID BUS_FLOW_BACKING_DIST_ID3, burden.BACKING_COST BUS_FLOW_BACKING_AMOUNT, GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID, decode(burden.FUNDS_STATUS, 'PASS', 'Y', 'N') BC_COMPLETE_STATUS, burden.data_set_id DATA_SET_ID, null WORK_TYPE_ID, null WORK_TYPE_NAME, null EXP_CDL_DIST_CREATION_DATE, null EXP_CDL_DIST_CREATED_BY, null EXP_CDL_LAST_UPDATED_DATE, null EXP_CDL_LAST_UPDATED_BY FROM pjc_xcc_burden_dists burden, xla_events_gt imp, GL_ENCUMBRANCE_TYPES_VL GLET WHERE imp.event_id = burden.burden_event_id AND burden.transaction_type_code = 'PROJECT_EXPENDITURE' AND GLET.encumbrance_type_code = 'Obligation' AND GLET.enabled_flag = 'Y' )

SQL_Statement

( SELECT 10036 application_id,

cdl.acct_event_id event_id,

cdl.expenditure_item_id,

cdl.line_num line_number,

cdl.line_num cdl_line_number,

imp.ledger_id ledger_id,

DECODE(NVL(cdl.acct_source_code, 'UPG'), 'UPG', DECODE(cdl.line_type, 'R', 'Y', 'N'), 'PA', DECODE(cdl.line_num_reversed, NULL, 'N', 'Y'), 'N') Reversing_Line_Flag,

to_number(DECODE(NVL(cdl.acct_source_code, 'UPG'), 'PA', DECODE(cdl.line_num_reversed, NULL, NULL, cdl.line_num_reversed), NULL)) line_num_reversed,

cdl.line_type,

cdl.denom_currency_code Entered_Currency_Code,

NVL(cdl.denom_raw_cost,0) Entered_Raw_Cost,

DECODE(NVL(cdl.denom_burdened_cost,0), 0, 0, NVL((cdl.denom_burdened_cost -cdl.denom_raw_cost),0)) entered_burden_cost,

NVL(cdl.denom_burdened_cost,0) Entered_Burdened_Cost,

NVL(cdl.acct_raw_cost,0) Acct_Raw_Cost,

DECODE(NVL(cdl.acct_burdened_cost,0), 0, 0, NVL((cdl.acct_burdened_cost -cdl.acct_raw_cost),0)) acct_burden_cost,

NVL(cdl.acct_burdened_cost,0) Acct_Burdened_Cost,

cdl.acct_exchange_rate Exchange_Rate,

cdl.acct_rate_type Exchange_Rate_Type,

cdl.acct_rate_date Exchange_Rate_Date,

cdl.Billable_flag,

cdl.capitalizable_flag,

cdl.PRVDR_GL_PERIOD_NAME Accounting_Period,

cdl.prvdr_pa_date Project_Accounting_DATE,

cdl.prvdr_pa_period_NAME Project_Accounting_period,

EI.Parent_Dist_Id PO_Distribution_ID,

EI.Parent_Dist_Id Invoice_Distribution_ID,

DECODE(SUBSTR(accounting_status_code,2,1),'P','Y','N') CREATE_RAW_ACCT_JOURNALS,

DECODE(SUBSTR(accounting_status_code,4,1),'P','Y','N') CREATE_BURDEN_ACCT_JOURNALS,

DECODE(SUBSTR(accounting_status_code,5,1),'P','Y','N') CREATE_BURDENED_ACCT_JOURNALS,

cdl.RAW_COST_DR_CCID IMPORTED_RAW_DR_ACCOUNT_CCID,

cdl.RAW_COST_CR_CCID IMPORTED_RAW_CR_ACCOUNT_CCID,

CDL.BURDEN_COST_DR_CCID IMPORTED_BURDEN_DR_ACC_CCID,

CDL.BURDEN_COST_CR_CCID IMPORTED_BURDEN_CR_ACC_CCID,

CDL.BURDENED_COST_DR_CCID IMPORTED_BURDENED_DR_ACC_CCID,

CDL.BURDENED_COST_CR_CCID IMPORTED_BURDENED_CR_ACC_CCID,

cdl.Acct_Source_Code accounting_source_code,

to_number(null) adj_raw_dr_account_ccid,

to_number(null)adj_raw_cr_account_ccid,

to_number(null)adj_burden_dr_acc_ccid,

to_number(null)adj_burden_cr_acc_ccid,

to_number(null)adj_burdened_dr_acc_ccid,

to_number(null)adj_burdened_cr_acc_ccid,

null BUS_FLOW_PO_APP_ID,

null BUS_FLOW_PO_ENTITY_CODE,

null BUS_FLOW_REQ_ENTITY_CODE,

null BUS_FLOW_BURDEN_DIST_TYPE,

null BUS_FLOW_BACKING_HEADER_ID1,

null BUS_FLOW_BACKING_HEADER_ID2,

null BUS_FLOW_BACKING_DIST_ID1,

null BUS_FLOW_BACKING_DIST_ID2,

null BUS_FLOW_BACKING_DIST_ID3,

null BUS_FLOW_BACKING_AMOUNT,

null ENCUMBRANCE_TYPE_ID,

decode (CDL.DATA_SET_ID, NULL, 'N', 'Y') BC_COMPLETE_STATUS,

CDL.DATA_SET_ID DATA_SET_ID,

cdl.work_type_id WORK_TYPE_ID,

cdl.work_type_id WORK_TYPE_NAME,

trunc(cdl.CREATION_DATE) EXP_CDL_DIST_CREATION_DATE,

cdl.CREATED_BY EXP_CDL_DIST_CREATED_BY,

trunc(cdl.LAST_UPDATE_DATE) EXP_CDL_LAST_UPDATED_DATE,

cdl.LAST_UPDATED_BY EXP_CDL_LAST_UPDATED_BY

FROM pjc_exp_items_all ei ,

pjc_cost_dist_lines_all cdl,

xla_events_gt imp

WHERE ei.expenditure_item_id = cdl.expenditure_item_id

AND imp.event_id = cdl.acct_event_id

AND imp.source_id_int_1 = cdl.expenditure_item_id)

UNION ALL

( SELECT 10036 application_id,

burden.burden_event_id event_id,

burden.btc_exp_id expenditure_item_id,

burden.burden_distribution_id line_number,

burden.burden_distribution_id cdl_line_number,

imp.ledger_id ledger_id,

'N' Reversing_Line_Flag,

NULL line_num_reversed,

'B' line_type,

burden.entered_currency Entered_Currency_Code,

0 Entered_Raw_Cost,

0 Entered_Burden_Cost,

0 Entered_Burdened_Cost,

0 Acct_Raw_Cost,

0 Acct_Burden_Cost,

0 Acct_Burdened_Cost,

null Exchange_Rate,

null Exchange_Rate_Type,

null Exchange_Rate_Date,

null Billable_flag,

null capitalizable_flag,

null Accounting_Period,

null Project_Accounting_DATE,

null Project_Accounting_period,

null PO_Distribution_ID,

null Invoice_Distribution_ID,

null CREATE_RAW_ACCT_JOURNALS,

null CREATE_BURDEN_ACCT_JOURNALS,

null CREATE_BURDENED_ACCT_JOURNALS,

null IMPORTED_RAW_DR_ACCOUNT_CCID,

null IMPORTED_RAW_CR_ACCOUNT_CCID,

null IMPORTED_BURDEN_DR_ACC_CCID,

null IMPORTED_BURDEN_CR_ACC_CCID,

null IMPORTED_BURDENED_DR_ACC_CCID,

null IMPORTED_BURDENED_CR_ACC_CCID,

'PA' accounting_source_code,

to_number(null) adj_raw_dr_account_ccid,

to_number(null)adj_raw_cr_account_ccid,

to_number(null)adj_burden_dr_acc_ccid,

to_number(null)adj_burden_cr_acc_ccid,

to_number(null)adj_burdened_dr_acc_ccid,

to_number(null)adj_burdened_cr_acc_ccid,

201 BUS_FLOW_PO_APP_ID,

'PO' BUS_FLOW_PO_ENTITY_CODE,

DECODE(burden.source_action_code,'PPM_RCV_IMT_EXP_VALIDATE','REQ',null) BUS_FLOW_REQ_ENTITY_CODE,

'PJC_XCC_BURDEN_DISTS' BUS_FLOW_BURDEN_DIST_TYPE,

DECODE(burden.source_action_code,'PPM_RCV_INVENTORY_VALIDATE',to_number(null),'PPM_RCV_WORK_ORDER_VALIDATE',to_number(null),burden.BACKING_HEADER_ID1) BUS_FLOW_BACKING_HEADER_ID1,

burden.BACKING_HEADER_ID1 BUS_FLOW_BACKING_HEADER_ID2,

burden.BACKING_DIST_ID1 BUS_FLOW_BACKING_DIST_ID1,

burden.BACKING_DIST_ID2 BUS_FLOW_BACKING_DIST_ID2,

burden.BACKING_BURDEN_DIST_ID BUS_FLOW_BACKING_DIST_ID3,

burden.BACKING_COST BUS_FLOW_BACKING_AMOUNT,

GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID,

decode(burden.FUNDS_STATUS, 'PASS', 'Y', 'N') BC_COMPLETE_STATUS,

burden.data_set_id DATA_SET_ID,

null WORK_TYPE_ID,

null WORK_TYPE_NAME,

null EXP_CDL_DIST_CREATION_DATE,

null EXP_CDL_DIST_CREATED_BY,

null EXP_CDL_LAST_UPDATED_DATE,

null EXP_CDL_LAST_UPDATED_BY

FROM pjc_xcc_burden_dists burden,

xla_events_gt imp,

GL_ENCUMBRANCE_TYPES_VL GLET

WHERE imp.event_id = burden.burden_event_id

AND burden.transaction_type_code = 'PROJECT_EXPENDITURE'

AND GLET.encumbrance_type_code = 'Obligation'

AND GLET.enabled_flag = 'Y' )