PJC_XLA_CDL_LINES_ADJ_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 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 BURDEN_SOURCE_DIST_ID_NUM_1 BURDEN_SOURCE_DIST_ID_NUM_2 BURDEN_SOURCE_DIST_TYPE BURDEN_LEDGER_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

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

BURDEN_SOURCE_DIST_ID_NUM_1

BURDEN_SOURCE_DIST_ID_NUM_2

BURDEN_SOURCE_DIST_TYPE

BURDEN_LEDGER_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 , 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, /* Following columns are used to retrieve the original ccid from SLA, it will have values only if the original transaction was accounted by Projects */ NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', DECODE(cdl.raw_line_num_reversed, NULL, NULL, DECODE(Decode(ei.transferred_from_exp_item_id, null, decode(ei.adjusted_expenditure_item_id, null, decode(cdl.line_num, 3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2), cdl.acct_source_code), cdl.acct_source_code), decode(cdl.line_num, 1,(select acct_source_code from pjc_cost_dist_lines_all where expenditure_item_id = ei.transferred_from_exp_item_id and line_num = cdl.raw_line_num_reversed), 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', decode(ei.transferred_from_exp_item_id, null,nvl(ei.adjusted_expenditure_item_id, ei.expenditure_item_id), decode(cdl.line_num, 1,ei.transferred_from_exp_item_id, ei.expenditure_item_id))) ) , NULL),-999999) raw_dr_source_dist_id_num_1, NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', DECODE(cdl.raw_line_num_reversed, NULL, NULL, DECODE(Decode(ei.transferred_from_exp_item_id, null, decode(ei.adjusted_expenditure_item_id, null, decode(cdl.line_num, 3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2), cdl.acct_source_code), cdl.acct_source_code), decode(cdl.line_num, 1,(select acct_source_code from pjc_cost_dist_lines_all where expenditure_item_id = ei.transferred_from_exp_item_id and line_num = cdl.raw_line_num_reversed), 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', decode(ei.transferred_from_exp_item_id, null,nvl(ei.adjusted_expenditure_item_id, ei.expenditure_item_id), decode(cdl.line_num, 1,ei.transferred_from_exp_item_id, ei.expenditure_item_id))) ) , NULL),-999999) raw_cr_source_dist_id_num_1, NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', DECODE(cdl.raw_line_num_reversed, NULL, NULL, DECODE(Decode(ei.transferred_from_exp_item_id, null, decode(ei.adjusted_expenditure_item_id, null, decode(cdl.line_num, 3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2), cdl.acct_source_code), cdl.acct_source_code), decode(cdl.line_num, 1,(select acct_source_code from pjc_cost_dist_lines_all where expenditure_item_id = ei.transferred_from_exp_item_id and line_num = cdl.raw_line_num_reversed), cdl.Acct_Source_Code) ) , 'PA',raw_line_num_reversed, -1) ) ,null), -999999) raw_source_dist_id_num_2, NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', DECODE(cdl.raw_line_num_reversed, NULL, NULL, DECODE(Decode(ei.transferred_from_exp_item_id, null, decode(ei.adjusted_expenditure_item_id, null, decode(cdl.line_num, 3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2), cdl.acct_source_code), cdl.acct_source_code), decode(cdl.line_num, 1,(select acct_source_code from pjc_cost_dist_lines_all where expenditure_item_id = ei.transferred_from_exp_item_id and line_num = cdl.raw_line_num_reversed), cdl.Acct_Source_Code) ) , 'AP_PAY','AP_PMT_DIST', 'AP_INV','AP_INV_DIST', 'AP_APP','AP_PREPAY', 'RCV', NVL(ei.DOC_REF_ID5, DECODE(doc.document_code,'TRANSFER_ORDER','TRO_RECEIPTS','DELIVER')), 'INV', NVL(ei.DOC_REF_ID5, 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', NVL(ei.DOC_REF_ID5, DECODE(doc.document_code, 'MNT_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION', 'MFG_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION', 'WIP_TRANSACTION_ACCOUNTS')), cdl.line_type ) ) , NULL),'ZZZ') raw_source_dist_type, NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', DECODE(cdl.raw_line_num_reversed, NULL, NULL, DECODE(Decode(ei.transferred_from_exp_item_id, null, decode(ei.adjusted_expenditure_item_id, null, decode(cdl.line_num, 3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3 where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2), cdl.acct_source_code), cdl.acct_source_code), decode(cdl.line_num, 1,(select acct_source_code from pjc_cost_dist_lines_all where expenditure_item_id = ei.transferred_from_exp_item_id and line_num = cdl.raw_line_num_reversed), cdl.Acct_Source_Code) ) , 'AP_INV', 200 ,'AP_PAY', 200 ,'AP_APP', 200 , 'INV', NVL( ei.REFERENCE_ID5, 707), 'WIP', NVL( ei.REFERENCE_ID5, 707), 'RCV', NVL( ei.REFERENCE_ID5, 10096), 'PA',10036 ) ) , NULL),-999999) raw_application_id, nvl(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1), 'P', decode(cdl.raw_line_num_reversed, null, to_number(null), TO_NUMBER(imp.ledger_id) ), to_number(null) ),-999999) raw_ledger_id, decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2), 'NN','N', 'EE','N', 'NE','N', 'EN','N', 'Y'), 'Y', decode(nvl(ei.adjusted_expenditure_item_id,ei.TRANSFERRED_FROM_EXP_ITEM_ID), null, decode(cdl.line_num,1,null,ei.expenditure_item_id), decode(ei.adjusted_expenditure_item_id, null, (decode(cdl.line_num,1,ei.TRANSFERRED_FROM_EXP_ITEM_ID,ei.expenditure_item_id)), ei.adjusted_expenditure_item_id) ), null ) burden_source_dist_id_num_1, decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2), 'NN','N', 'EE','N', 'NE','N', 'EN','N', 'Y'), 'Y', cdl.parent_line_num, null ) burden_source_dist_id_num_2, decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2), 'NN','N', 'EE','N', 'NE','N', 'EN','N', 'Y'), 'Y', decode(cdl.parent_line_num, null,null, (select line_type from pjc_cost_dist_lines_all cdl1 where cdl1.expenditure_item_id = decode(cdl.line_num,1,nvl(ei.adjusted_expenditure_item_id,ei.TRANSFERRED_FROM_EXP_ITEM_ID),cdl.expenditure_item_id) and cdl1.line_num = cdl.parent_line_num) ) ) burden_source_dist_type, decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2), 'NN','N', 'EE','N', 'NE','N', 'EN','N', 'Y'), 'Y', decode(nvl(cdl.parent_line_num,cdl.line_num_reversed), null,to_number(null), TO_NUMBER(imp.ledger_id) ), to_number(null) ) burden_ledger_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, 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, 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

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 ,

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,

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

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

NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', DECODE(cdl.raw_line_num_reversed,

NULL, NULL,

DECODE(Decode(ei.transferred_from_exp_item_id,

null, decode(ei.adjusted_expenditure_item_id,

null, decode(cdl.line_num,

3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3

where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2),

cdl.acct_source_code),

decode(cdl.line_num,

1,(select acct_source_code from pjc_cost_dist_lines_all

where expenditure_item_id = ei.transferred_from_exp_item_id

and line_num = cdl.raw_line_num_reversed),

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', decode(ei.transferred_from_exp_item_id,

null,nvl(ei.adjusted_expenditure_item_id, ei.expenditure_item_id),

decode(cdl.line_num,

1,ei.transferred_from_exp_item_id,

ei.expenditure_item_id)))

)

, NULL),-999999) raw_dr_source_dist_id_num_1,

NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', DECODE(cdl.raw_line_num_reversed,

NULL, NULL,

DECODE(Decode(ei.transferred_from_exp_item_id,

null, decode(ei.adjusted_expenditure_item_id,

null, decode(cdl.line_num,

3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3

where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2),

cdl.acct_source_code),

decode(cdl.line_num,

1,(select acct_source_code from pjc_cost_dist_lines_all

where expenditure_item_id = ei.transferred_from_exp_item_id

and line_num = cdl.raw_line_num_reversed),

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', decode(ei.transferred_from_exp_item_id,

null,nvl(ei.adjusted_expenditure_item_id, ei.expenditure_item_id),

decode(cdl.line_num,

1,ei.transferred_from_exp_item_id,

ei.expenditure_item_id)))

)

, NULL),-999999) raw_cr_source_dist_id_num_1,

NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', DECODE(cdl.raw_line_num_reversed,

NULL, NULL,

DECODE(Decode(ei.transferred_from_exp_item_id,

null, decode(ei.adjusted_expenditure_item_id,

null, decode(cdl.line_num,

3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3

where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2),

cdl.acct_source_code),

decode(cdl.line_num,

1,(select acct_source_code from pjc_cost_dist_lines_all

where expenditure_item_id = ei.transferred_from_exp_item_id

and line_num = cdl.raw_line_num_reversed),

cdl.Acct_Source_Code)

) ,

'PA',raw_line_num_reversed,

-1)

)

,null),

-999999) raw_source_dist_id_num_2,

NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', DECODE(cdl.raw_line_num_reversed,

NULL, NULL,

DECODE(Decode(ei.transferred_from_exp_item_id,

null, decode(ei.adjusted_expenditure_item_id,

null, decode(cdl.line_num,

3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3

where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2),

cdl.acct_source_code),

decode(cdl.line_num,

1,(select acct_source_code from pjc_cost_dist_lines_all

where expenditure_item_id = ei.transferred_from_exp_item_id

and line_num = cdl.raw_line_num_reversed),

cdl.Acct_Source_Code)

) ,

'AP_PAY','AP_PMT_DIST',

'AP_INV','AP_INV_DIST',

'AP_APP','AP_PREPAY',

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

'INV', NVL(ei.DOC_REF_ID5, 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', NVL(ei.DOC_REF_ID5, DECODE(doc.document_code, 'MNT_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION',

'MFG_WORK_ORDER' , 'WIP_RESOURCE_TRANSACTION',

'WIP_TRANSACTION_ACCOUNTS')),

cdl.line_type

)

, NULL),'ZZZ') raw_source_dist_type,

NVL(DECODE(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', DECODE(cdl.raw_line_num_reversed,

NULL, NULL,

DECODE(Decode(ei.transferred_from_exp_item_id,

null, decode(ei.adjusted_expenditure_item_id,

null, decode(cdl.line_num,

3,(select cdl3.acct_source_code from pjc_cost_dist_lines_all cdl3

where cdl3.expenditure_item_id = cdl.expenditure_item_id and cdl3.line_num=2),

cdl.acct_source_code),

decode(cdl.line_num,

1,(select acct_source_code from pjc_cost_dist_lines_all

where expenditure_item_id = ei.transferred_from_exp_item_id

and line_num = cdl.raw_line_num_reversed),

cdl.Acct_Source_Code)

) ,

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

'INV', NVL( ei.REFERENCE_ID5, 707),

'WIP', NVL( ei.REFERENCE_ID5, 707),

'RCV', NVL( ei.REFERENCE_ID5, 10096),

'PA',10036

)

, NULL),-999999) raw_application_id,

nvl(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 2,1),

'P', decode(cdl.raw_line_num_reversed,

null, to_number(null),

TO_NUMBER(imp.ledger_id)

),

to_number(null)

),-999999) raw_ledger_id,

decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2),

'NN','N',

'EE','N',

'NE','N',

'EN','N',

'Y'),

'Y',

decode(nvl(ei.adjusted_expenditure_item_id,ei.TRANSFERRED_FROM_EXP_ITEM_ID),

null, decode(cdl.line_num,1,null,ei.expenditure_item_id),

decode(ei.adjusted_expenditure_item_id,

null, (decode(cdl.line_num,1,ei.TRANSFERRED_FROM_EXP_ITEM_ID,ei.expenditure_item_id)),

ei.adjusted_expenditure_item_id)

),

null

) burden_source_dist_id_num_1,

decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2),

'NN','N',

'EE','N',

'NE','N',

'EN','N',

'Y'),

'Y', cdl.parent_line_num,

null

) burden_source_dist_id_num_2,

decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2),

'NN','N',

'EE','N',

'NE','N',

'EN','N',

'Y'),

'Y', decode(cdl.parent_line_num,

null,null,

(select line_type from pjc_cost_dist_lines_all cdl1

where cdl1.expenditure_item_id = decode(cdl.line_num,1,nvl(ei.adjusted_expenditure_item_id,ei.TRANSFERRED_FROM_EXP_ITEM_ID),cdl.expenditure_item_id)

and cdl1.line_num = cdl.parent_line_num)

)

burden_source_dist_type,

decode(decode(SUBSTR(CDL.ACCOUNTING_Status_code, 4,2),

'NN','N',

'EE','N',

'NE','N',

'EN','N',

'Y'),

'Y', decode(nvl(cdl.parent_line_num,cdl.line_num_reversed),

null,to_number(null),

TO_NUMBER(imp.ledger_id)

),

to_number(null)

) burden_ledger_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,

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,

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