PJC_XLA_CDL_LINES_DR_CR

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 RAW_DR_SOURCE_DIST_ID_NUM_1 RAW_CR_SOURCE_DIST_ID_NUM_1 RAW_SOURCE_DIST_ID_NUM_2 RAW_SOURCE_DIST_TYPE RAW_APPLICATION_ID RAW_LEDGER_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

RAW_DR_SOURCE_DIST_ID_NUM_1

RAW_CR_SOURCE_DIST_ID_NUM_1

RAW_SOURCE_DIST_ID_NUM_2

RAW_SOURCE_DIST_TYPE

RAW_APPLICATION_ID

RAW_LEDGER_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'), DECODE(cdl.line_num_reversed, NULL, 'N', 'Y')) Reversing_Line_Flag, to_number(DECODE(NVL(cdl.acct_source_code, 'UPG'), 'UPG', null, cdl.line_num_reversed)) 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, 201 BUS_FLOW_PO_APP_ID, 'PO' BUS_FLOW_PO_ENTITY_CODE, DECODE(cdl.BACKING_HEADER_ID2, NULL,'REQ',NULL) BUS_FLOW_REQ_ENTITY_CODE, 'PJC_XCC_BURDEN_DISTS' BUS_FLOW_BURDEN_DIST_TYPE, DECODE(cdl.BACKING_HEADER_ID2,NULL,cdl.BACKING_HEADER_ID1,to_number(null)) BUS_FLOW_BACKING_HEADER_ID1, cdl.BACKING_HEADER_ID2 BUS_FLOW_BACKING_HEADER_ID2, cdl.BACKING_DIST_ID1 BUS_FLOW_BACKING_DIST_ID1, cdl.BACKING_DIST_ID2 BUS_FLOW_BACKING_DIST_ID2, cdl.BACKING_DIST_ID3 BUS_FLOW_BACKING_DIST_ID3, cdl.BACKING_AMOUNT BUS_FLOW_BACKING_AMOUNT, GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID, CASE WHEN (CDL.BUDGETARY_CONTROL_VAL_STATUS LIKE 'RESERVED%' AND CDL.BUDGETARY_CONTROL_VAL_STATUS <> 'RESERVED_NOT_APPLICABLE') THEN 'Y' WHEN CDL.BUDGETARY_CONTROL_VAL_STATUS = 'RESERVED_NOT_APPLICABLE' then 'N' WHEN CDL.BUDGETARY_CONTROL_VAL_STATUS = 'NOT_ATTEMPTED' then NULL ELSE NULL END BC_COMPLETE_STATUS, CDL.data_set_id DATA_SET_ID, /* Following columns are used to retrieve the original ccid from SLA, it will have values only if the original transaction was accounted by Projects */ DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code, 'AP_INV', ei.Original_Dist_Id, 'AP_PAY', ei.Original_Dist_Id, 'AP_APP', ei.Parent_Dist_Id, 'INV', ei.Original_Dist_Id, 'WIP', ei.Original_Dist_Id, 'RCV', ei.Original_Dist_Id, 'PA',cdl.expenditure_item_id)) raw_dr_source_dist_id_num_1, DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code, 'AP_INV', ei.Original_Dist_Id, 'AP_PAY', ei.Original_Dist_Id, 'AP_APP', ei.Parent_Dist_Id, 'INV', ei.Original_Dist_Id2, 'WIP', ei.Original_Dist_Id2, 'RCV', ei.Original_Dist_Id2, 'PA',cdl.expenditure_item_id)) raw_cr_source_dist_id_num_1, DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code,'PA', cdl.line_num,-1 )) raw_source_dist_id_num_2, DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, NVL(ei.DOC_REF_ID5, DECODE(cdl.Acct_Source_Code, 'AP_PAY','AP_PMT_DIST', 'AP_INV','AP_INV_DIST', 'AP_APP','AP_PREPAY', 'RCV', DECODE(doc.document_code,'TRANSFER_ORDER','TRO_RECEIPTS','DELIVER'), 'INV', DECODE(doc.document_code, 'MISC_INV', 'MISCELLANEOUS_TRANSACTION', 'SALES_ORDER_ISSUE','SALES_ORDER_ISSUE' , 'MNT_WORK_ORDER','WIP_MATERIAL_TRANSACTION' , 'MFG_WORK_ORDER', 'WIP_MATERIAL_TRANSACTION' ) , 'WIP', DECODE(doc.document_code, 'MNT_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION', 'MFG_WORK_ORDER', 'WIP_RESOURCE_TRANSACTION', 'WIP_TRANSACTION_ACCOUNTS'), 'PA',CDL.line_type))) raw_source_dist_type, DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, NVL(ei.REFERENCE_ID5, DECODE(cdl.Acct_Source_Code, 'AP_INV', 200 , 'AP_PAY', 200 ,'AP_APP', 200 , 'INV', 707 ,'WIP', 707 ,'RCV', 10096,'PA', 10036))) raw_application_id, DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, TO_NUMBER(imp.ledger_id)) raw_ledger_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, GL_ENCUMBRANCE_TYPES_VL GLET, pjf_txn_document_b doc 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 AND doc.document_id = ei.document_id 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'), DECODE(cdl.line_num_reversed, NULL, 'N', 'Y')) Reversing_Line_Flag,

to_number(DECODE(NVL(cdl.acct_source_code, 'UPG'), 'UPG', null, cdl.line_num_reversed)) 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,

201 BUS_FLOW_PO_APP_ID,

'PO' BUS_FLOW_PO_ENTITY_CODE,

DECODE(cdl.BACKING_HEADER_ID2, NULL,'REQ',NULL) BUS_FLOW_REQ_ENTITY_CODE,

'PJC_XCC_BURDEN_DISTS' BUS_FLOW_BURDEN_DIST_TYPE,

DECODE(cdl.BACKING_HEADER_ID2,NULL,cdl.BACKING_HEADER_ID1,to_number(null)) BUS_FLOW_BACKING_HEADER_ID1,

cdl.BACKING_HEADER_ID2 BUS_FLOW_BACKING_HEADER_ID2,

cdl.BACKING_DIST_ID1 BUS_FLOW_BACKING_DIST_ID1,

cdl.BACKING_DIST_ID2 BUS_FLOW_BACKING_DIST_ID2,

cdl.BACKING_DIST_ID3 BUS_FLOW_BACKING_DIST_ID3,

cdl.BACKING_AMOUNT BUS_FLOW_BACKING_AMOUNT,

GLET.encumbrance_type_id ENCUMBRANCE_TYPE_ID,

CASE WHEN (CDL.BUDGETARY_CONTROL_VAL_STATUS LIKE 'RESERVED%' AND CDL.BUDGETARY_CONTROL_VAL_STATUS <> 'RESERVED_NOT_APPLICABLE') THEN 'Y'

WHEN CDL.BUDGETARY_CONTROL_VAL_STATUS = 'RESERVED_NOT_APPLICABLE' then 'N'

WHEN CDL.BUDGETARY_CONTROL_VAL_STATUS = 'NOT_ATTEMPTED' then NULL

ELSE NULL

END BC_COMPLETE_STATUS,

CDL.data_set_id DATA_SET_ID,

/* Following columns are used to retrieve the original ccid from SLA, it will have

values only if the original transaction was accounted by Projects */

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code, 'AP_INV', ei.Original_Dist_Id, 'AP_PAY', ei.Original_Dist_Id, 'AP_APP', ei.Parent_Dist_Id,

'INV', ei.Original_Dist_Id, 'WIP', ei.Original_Dist_Id, 'RCV', ei.Original_Dist_Id,

'PA',cdl.expenditure_item_id)) raw_dr_source_dist_id_num_1,

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code, 'AP_INV', ei.Original_Dist_Id, 'AP_PAY', ei.Original_Dist_Id, 'AP_APP', ei.Parent_Dist_Id,

'INV', ei.Original_Dist_Id2, 'WIP', ei.Original_Dist_Id2, 'RCV', ei.Original_Dist_Id2,

'PA',cdl.expenditure_item_id)) raw_cr_source_dist_id_num_1,

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, DECODE(cdl.Acct_Source_Code,'PA', cdl.line_num,-1 )) raw_source_dist_id_num_2,

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, NVL(ei.DOC_REF_ID5, DECODE(cdl.Acct_Source_Code, 'AP_PAY','AP_PMT_DIST',

'AP_INV','AP_INV_DIST',

'AP_APP','AP_PREPAY',

'RCV', DECODE(doc.document_code,'TRANSFER_ORDER','TRO_RECEIPTS','DELIVER'),

'INV', DECODE(doc.document_code, 'MISC_INV', 'MISCELLANEOUS_TRANSACTION',

'SALES_ORDER_ISSUE','SALES_ORDER_ISSUE' ,

'MNT_WORK_ORDER','WIP_MATERIAL_TRANSACTION' ,

'MFG_WORK_ORDER', 'WIP_MATERIAL_TRANSACTION' ) ,

'WIP', DECODE(doc.document_code, 'MNT_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION',

'MFG_WORK_ORDER', 'WIP_RESOURCE_TRANSACTION',

'WIP_TRANSACTION_ACCOUNTS'),

'PA',CDL.line_type))) raw_source_dist_type,

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, NVL(ei.REFERENCE_ID5, DECODE(cdl.Acct_Source_Code, 'AP_INV', 200 ,

'AP_PAY', 200 ,'AP_APP', 200 ,

'INV', 707 ,'WIP', 707 ,'RCV', 10096,'PA', 10036))) raw_application_id,

DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),'N', NULL, TO_NUMBER(imp.ledger_id)) raw_ledger_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,

GL_ENCUMBRANCE_TYPES_VL GLET,

pjf_txn_document_b doc

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

AND doc.document_id = ei.document_id

AND GLET.encumbrance_type_code = 'Obligation'

AND GLET.enabled_flag = 'Y'