VIEW: APPS.PA_CDL_BURDEN_DETAIL_V

Source

View Text - Preformatted

SELECT Project_id ,Task_id ,Organization_id ,Pa_date ,Pa_period_name ,GTEMP.Attribute1 ,GTEMP.Attribute2 ,GTEMP.Attribute3 ,GTEMP.Attribute4 ,GTEMP.Attribute5 ,GTEMP.Attribute6 ,GTEMP.Attribute7 ,GTEMP.Attribute8 ,GTEMP.Attribute9 ,GTEMP.Attribute10 ,GTEMP.Attribute_category ,Person_type ,Po_line_id ,System_linkage_function ,CM.Ind_cost_code ,Ei_expenditure_type ,(SELECT ICC.Expenditure_type FROM PA_IND_COST_CODES ICC WHERE ICC.Ind_cost_code = CM.Ind_cost_code) Icc_expenditure_type ,CM.Cost_base ,CM.Compiled_multiplier ,CM.Ind_compiled_set_id ,Expenditure_item_id ,Line_num ,Expenditure_item_date ,pa_currency.round_trans_currency_amt1((Cdl_amount * CM.Compiled_multiplier),projfunc_currency_code ) Burden_cost ,pa_currency.round_trans_currency_amt1((Cdl_denom_raw_cost * CM.Compiled_multiplier),denom_currency_code) Denom_burdened_cost ,pa_currency.round_trans_currency_amt1((Cdl_acct_raw_cost * CM.Compiled_multiplier),acct_currency_code) Acct_burdened_cost ,pa_currency.round_trans_currency_amt1((Cdl_project_raw_cost * CM.Compiled_multiplier),project_currency_code) Project_burdened_cost ,Burden_sum_source_run_id ,Burden_sum_rejection_code ,System_reference1 ,Denom_currency_code ,Acct_currency_code ,Project_currency_code ,Projfunc_currency_code ,Billable_flag ,GTEMP.Request_id ,Adjustment_type ,Job_id ,Non_labor_resource ,Non_labor_resource_orgn_id ,Wip_resource_id ,Incurred_by_person_id ,Inventory_item_id ,org_id ,acct_rate_date ,acct_rate_type ,acct_exchange_rate ,project_rate_date ,project_rate_type ,project_exchange_rate ,projfunc_cost_rate_date ,projfunc_cost_rate_type ,projfunc_cost_exchange_rate ,cbs_element_id FROM PA_EI_CDL_CM_GTEMP GTEMP, PA_COMPILED_MULTIPLIERS CM WHERE CM.Ind_compiled_set_id = GTEMP.Ind_compiled_set_id AND CM.Cost_base = GTEMP.Cost_base AND CM.Compiled_multiplier <> 0 AND GTEMP.Burden_sum_rejection_code IS NULL AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'N' UNION ALL SELECT Project_id ,Task_id ,Organization_id ,Pa_date ,Pa_period_name ,GTEMP.Attribute1 ,GTEMP.Attribute2 ,GTEMP.Attribute3 ,GTEMP.Attribute4 ,GTEMP.Attribute5 ,GTEMP.Attribute6 ,GTEMP.Attribute7 ,GTEMP.Attribute8 ,GTEMP.Attribute9 ,GTEMP.Attribute10 ,GTEMP.Attribute_category ,Person_type ,Po_line_id ,System_linkage_function ,CM.Ind_cost_code ,Ei_expenditure_type , (SELECT ICC.Expenditure_type FROM PA_IND_COST_CODES ICC WHERE ICC.Ind_cost_code = CM.Ind_cost_code) Icc_expenditure_type ,CM.Cost_base ,CM.Compiled_multiplier ,CM.Ind_compiled_set_id ,Expenditure_item_id ,Line_num ,Expenditure_item_date ,pa_currency.round_trans_currency_amt1(((Cdl_amount * CM.Compiled_multiplier)*-1),projfunc_currency_code) Burden_cost ,pa_currency.round_trans_currency_amt1(((Cdl_denom_raw_cost * CM.Compiled_multiplier)*-1),denom_currency_code) Denom_burdened_cost ,pa_currency.round_trans_currency_amt1(((Cdl_acct_raw_cost * CM.Compiled_multiplier)*-1),acct_currency_code) Acct_burdened_cost ,pa_currency.round_trans_currency_amt1(((Cdl_project_raw_cost * CM.Compiled_multiplier)*-1),project_currency_code) Project_burdened_cost ,Burden_sum_source_run_id ,Burden_sum_rejection_code ,System_reference1 ,Denom_currency_code ,Acct_currency_code ,Project_currency_code ,Projfunc_currency_code ,Billable_flag ,GTEMP.Request_id ,Adjustment_type ,Job_id ,Non_labor_resource ,Non_labor_resource_orgn_id ,Wip_resource_id ,Incurred_by_person_id ,Inventory_item_id ,org_id ,acct_rate_date ,acct_rate_type ,acct_exchange_rate ,project_rate_date ,project_rate_type ,project_exchange_rate ,projfunc_cost_rate_date ,projfunc_cost_rate_type ,projfunc_cost_exchange_rate ,cbs_element_id FROM PA_EI_CDL_CM_GTEMP GTEMP, PA_COMPILED_MULTIPLIERS CM WHERE GTEMP.Prev_ind_compiled_set_id IS NOT NULL AND CM.Ind_compiled_set_id = GTEMP.Prev_ind_compiled_set_id AND CM.Cost_base = GTEMP.Cost_base AND CM.Compiled_multiplier <> 0 AND GTEMP.Burden_sum_rejection_code IS NULL AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'N' UNION ALL SELECT GTEMP.Project_id ,GTEMP.Task_id ,GTEMP.Organization_id ,GTEMP.Pa_date ,GTEMP.Pa_period_name ,To_char(Adl.Award_id) ,GTEMP.Attribute2 ,GTEMP.Attribute3 ,GTEMP.Attribute4 ,GTEMP.Attribute5 ,GTEMP.Attribute6 ,GTEMP.Attribute7 ,GTEMP.Attribute8 ,GTEMP.Attribute9 ,GTEMP.Attribute10 ,GTEMP.Attribute_category ,GTEMP.Person_type ,GTEMP.Po_line_id ,GTEMP.System_linkage_function ,CM.Ind_cost_code ,GTEMP.Ei_expenditure_type ,(SELECT ICC.EXPENDITURE_TYPE FROM PA_IND_COST_CODES ICC WHERE ICC.IND_COST_CODE = CM.IND_COST_CODE) Icc_expenditure_type ,CM.Cost_base ,CM.Compiled_multiplier ,CM.Ind_compiled_set_id ,GTEMP.Expenditure_item_id ,GTEMP.Line_num ,GTEMP.Expenditure_item_date ,pa_currency.round_trans_currency_amt1((DECODE(Adl.Award_id, NULL, GTEMP.Cdl_amount, NVL(Adl.Burdenable_raw_cost,0)) * CM.Compiled_multiplier ),acct_currency_code) Burden_cost ,pa_currency.round_trans_currency_amt1((Decode(Adl.Award_id, NULL, GTEMP.Cdl_denom_raw_cost, NVL(Adl.Burdenable_raw_cost,0)) * CM.Compiled_multiplier),acct_currency_code) Denom_burdened_cost ,pa_currency.round_trans_currency_amt1((DECODE(Adl.Award_id, NULL, GTEMP.Cdl_acct_raw_cost, NVL(Adl.Burdenable_raw_cost,0)) * CM.Compiled_multiplier), acct_currency_code) Acct_burdened_cost ,pa_currency.round_trans_currency_amt1((DECODE(Adl.Award_id, NULL, GTEMP.Cdl_acct_raw_cost, NVL(Adl.Burdenable_raw_cost,0)) * CM.Compiled_multiplier),acct_currency_code) Project_burdened_cost ,GTEMP.Burden_sum_source_run_id ,GTEMP.Burden_sum_rejection_code ,GTEMP.System_reference1 ,Decode(Adl.Award_id,NULL,GTEMP.Denom_currency_code,GTEMP.Projfunc_currency_code) ,Decode(Adl.Award_id,NULL,GTEMP.Acct_currency_code,GTEMP.Projfunc_currency_code) ,Decode(Adl.Award_id,NULL,GTEMP.Project_currency_code,GTEMP.Projfunc_currency_code) ,GTEMP.Projfunc_currency_code ,GTEMP.Billable_flag ,GTEMP.Request_id ,NULL ,GTEMP.Job_id ,GTEMP.Non_labor_resource ,GTEMP.Non_labor_resource_orgn_id ,GTEMP.Wip_resource_id ,GTEMP.Incurred_by_person_id ,GTEMP.Inventory_item_id ,org_id ,acct_rate_date ,acct_rate_type ,acct_exchange_rate ,project_rate_date ,project_rate_type ,project_exchange_rate ,projfunc_cost_rate_date ,projfunc_cost_rate_type ,projfunc_cost_exchange_rate ,cbs_element_id FROM PA_EI_CDL_CM_GTEMP GTEMP, PA_COMPILED_MULTIPLIERS CM, GMS_AWARD_DISTRIBUTIONS ADL WHERE ADL.Expenditure_item_id = GTEMP.Expenditure_item_id AND ADL.Cdl_line_num = GTEMP.Line_num AND ADL.Adl_status = 'A' AND ADL.Document_type = 'EXP' AND CM.Ind_compiled_set_id = GTEMP.Ind_compiled_set_id AND CM.Cost_base = GTEMP.Cost_base AND CM.Compiled_multiplier <> 0 AND GTEMP.Burden_sum_rejection_code IS NULL AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'Y'

View Text - HTML Formatted

SELECT PROJECT_ID
, TASK_ID
, ORGANIZATION_ID
, PA_DATE
, PA_PERIOD_NAME
, GTEMP.ATTRIBUTE1
, GTEMP.ATTRIBUTE2
, GTEMP.ATTRIBUTE3
, GTEMP.ATTRIBUTE4
, GTEMP.ATTRIBUTE5
, GTEMP.ATTRIBUTE6
, GTEMP.ATTRIBUTE7
, GTEMP.ATTRIBUTE8
, GTEMP.ATTRIBUTE9
, GTEMP.ATTRIBUTE10
, GTEMP.ATTRIBUTE_CATEGORY
, PERSON_TYPE
, PO_LINE_ID
, SYSTEM_LINKAGE_FUNCTION
, CM.IND_COST_CODE
, EI_EXPENDITURE_TYPE
, (SELECT ICC.EXPENDITURE_TYPE
FROM PA_IND_COST_CODES ICC
WHERE ICC.IND_COST_CODE = CM.IND_COST_CODE) ICC_EXPENDITURE_TYPE
, CM.COST_BASE
, CM.COMPILED_MULTIPLIER
, CM.IND_COMPILED_SET_ID
, EXPENDITURE_ITEM_ID
, LINE_NUM
, EXPENDITURE_ITEM_DATE
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((CDL_AMOUNT * CM.COMPILED_MULTIPLIER)
, PROJFUNC_CURRENCY_CODE ) BURDEN_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((CDL_DENOM_RAW_COST * CM.COMPILED_MULTIPLIER)
, DENOM_CURRENCY_CODE) DENOM_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((CDL_ACCT_RAW_COST * CM.COMPILED_MULTIPLIER)
, ACCT_CURRENCY_CODE) ACCT_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((CDL_PROJECT_RAW_COST * CM.COMPILED_MULTIPLIER)
, PROJECT_CURRENCY_CODE) PROJECT_BURDENED_COST
, BURDEN_SUM_SOURCE_RUN_ID
, BURDEN_SUM_REJECTION_CODE
, SYSTEM_REFERENCE1
, DENOM_CURRENCY_CODE
, ACCT_CURRENCY_CODE
, PROJECT_CURRENCY_CODE
, PROJFUNC_CURRENCY_CODE
, BILLABLE_FLAG
, GTEMP.REQUEST_ID
, ADJUSTMENT_TYPE
, JOB_ID
, NON_LABOR_RESOURCE
, NON_LABOR_RESOURCE_ORGN_ID
, WIP_RESOURCE_ID
, INCURRED_BY_PERSON_ID
, INVENTORY_ITEM_ID
, ORG_ID
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, PROJECT_RATE_DATE
, PROJECT_RATE_TYPE
, PROJECT_EXCHANGE_RATE
, PROJFUNC_COST_RATE_DATE
, PROJFUNC_COST_RATE_TYPE
, PROJFUNC_COST_EXCHANGE_RATE
, CBS_ELEMENT_ID
FROM PA_EI_CDL_CM_GTEMP GTEMP
, PA_COMPILED_MULTIPLIERS CM
WHERE CM.IND_COMPILED_SET_ID = GTEMP.IND_COMPILED_SET_ID
AND CM.COST_BASE = GTEMP.COST_BASE
AND CM.COMPILED_MULTIPLIER <> 0
AND GTEMP.BURDEN_SUM_REJECTION_CODE IS NULL
AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'N' UNION ALL SELECT PROJECT_ID
, TASK_ID
, ORGANIZATION_ID
, PA_DATE
, PA_PERIOD_NAME
, GTEMP.ATTRIBUTE1
, GTEMP.ATTRIBUTE2
, GTEMP.ATTRIBUTE3
, GTEMP.ATTRIBUTE4
, GTEMP.ATTRIBUTE5
, GTEMP.ATTRIBUTE6
, GTEMP.ATTRIBUTE7
, GTEMP.ATTRIBUTE8
, GTEMP.ATTRIBUTE9
, GTEMP.ATTRIBUTE10
, GTEMP.ATTRIBUTE_CATEGORY
, PERSON_TYPE
, PO_LINE_ID
, SYSTEM_LINKAGE_FUNCTION
, CM.IND_COST_CODE
, EI_EXPENDITURE_TYPE
, (SELECT ICC.EXPENDITURE_TYPE
FROM PA_IND_COST_CODES ICC
WHERE ICC.IND_COST_CODE = CM.IND_COST_CODE) ICC_EXPENDITURE_TYPE
, CM.COST_BASE
, CM.COMPILED_MULTIPLIER
, CM.IND_COMPILED_SET_ID
, EXPENDITURE_ITEM_ID
, LINE_NUM
, EXPENDITURE_ITEM_DATE
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(((CDL_AMOUNT * CM.COMPILED_MULTIPLIER)*-1)
, PROJFUNC_CURRENCY_CODE) BURDEN_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(((CDL_DENOM_RAW_COST * CM.COMPILED_MULTIPLIER)*-1)
, DENOM_CURRENCY_CODE) DENOM_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(((CDL_ACCT_RAW_COST * CM.COMPILED_MULTIPLIER)*-1)
, ACCT_CURRENCY_CODE) ACCT_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(((CDL_PROJECT_RAW_COST * CM.COMPILED_MULTIPLIER)*-1)
, PROJECT_CURRENCY_CODE) PROJECT_BURDENED_COST
, BURDEN_SUM_SOURCE_RUN_ID
, BURDEN_SUM_REJECTION_CODE
, SYSTEM_REFERENCE1
, DENOM_CURRENCY_CODE
, ACCT_CURRENCY_CODE
, PROJECT_CURRENCY_CODE
, PROJFUNC_CURRENCY_CODE
, BILLABLE_FLAG
, GTEMP.REQUEST_ID
, ADJUSTMENT_TYPE
, JOB_ID
, NON_LABOR_RESOURCE
, NON_LABOR_RESOURCE_ORGN_ID
, WIP_RESOURCE_ID
, INCURRED_BY_PERSON_ID
, INVENTORY_ITEM_ID
, ORG_ID
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, PROJECT_RATE_DATE
, PROJECT_RATE_TYPE
, PROJECT_EXCHANGE_RATE
, PROJFUNC_COST_RATE_DATE
, PROJFUNC_COST_RATE_TYPE
, PROJFUNC_COST_EXCHANGE_RATE
, CBS_ELEMENT_ID
FROM PA_EI_CDL_CM_GTEMP GTEMP
, PA_COMPILED_MULTIPLIERS CM
WHERE GTEMP.PREV_IND_COMPILED_SET_ID IS NOT NULL
AND CM.IND_COMPILED_SET_ID = GTEMP.PREV_IND_COMPILED_SET_ID
AND CM.COST_BASE = GTEMP.COST_BASE
AND CM.COMPILED_MULTIPLIER <> 0
AND GTEMP.BURDEN_SUM_REJECTION_CODE IS NULL
AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'N' UNION ALL SELECT GTEMP.PROJECT_ID
, GTEMP.TASK_ID
, GTEMP.ORGANIZATION_ID
, GTEMP.PA_DATE
, GTEMP.PA_PERIOD_NAME
, TO_CHAR(ADL.AWARD_ID)
, GTEMP.ATTRIBUTE2
, GTEMP.ATTRIBUTE3
, GTEMP.ATTRIBUTE4
, GTEMP.ATTRIBUTE5
, GTEMP.ATTRIBUTE6
, GTEMP.ATTRIBUTE7
, GTEMP.ATTRIBUTE8
, GTEMP.ATTRIBUTE9
, GTEMP.ATTRIBUTE10
, GTEMP.ATTRIBUTE_CATEGORY
, GTEMP.PERSON_TYPE
, GTEMP.PO_LINE_ID
, GTEMP.SYSTEM_LINKAGE_FUNCTION
, CM.IND_COST_CODE
, GTEMP.EI_EXPENDITURE_TYPE
, (SELECT ICC.EXPENDITURE_TYPE
FROM PA_IND_COST_CODES ICC
WHERE ICC.IND_COST_CODE = CM.IND_COST_CODE) ICC_EXPENDITURE_TYPE
, CM.COST_BASE
, CM.COMPILED_MULTIPLIER
, CM.IND_COMPILED_SET_ID
, GTEMP.EXPENDITURE_ITEM_ID
, GTEMP.LINE_NUM
, GTEMP.EXPENDITURE_ITEM_DATE
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((DECODE(ADL.AWARD_ID
, NULL
, GTEMP.CDL_AMOUNT
, NVL(ADL.BURDENABLE_RAW_COST
, 0)) * CM.COMPILED_MULTIPLIER )
, ACCT_CURRENCY_CODE) BURDEN_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((DECODE(ADL.AWARD_ID
, NULL
, GTEMP.CDL_DENOM_RAW_COST
, NVL(ADL.BURDENABLE_RAW_COST
, 0)) * CM.COMPILED_MULTIPLIER)
, ACCT_CURRENCY_CODE) DENOM_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((DECODE(ADL.AWARD_ID
, NULL
, GTEMP.CDL_ACCT_RAW_COST
, NVL(ADL.BURDENABLE_RAW_COST
, 0)) * CM.COMPILED_MULTIPLIER)
, ACCT_CURRENCY_CODE) ACCT_BURDENED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((DECODE(ADL.AWARD_ID
, NULL
, GTEMP.CDL_ACCT_RAW_COST
, NVL(ADL.BURDENABLE_RAW_COST
, 0)) * CM.COMPILED_MULTIPLIER)
, ACCT_CURRENCY_CODE) PROJECT_BURDENED_COST
, GTEMP.BURDEN_SUM_SOURCE_RUN_ID
, GTEMP.BURDEN_SUM_REJECTION_CODE
, GTEMP.SYSTEM_REFERENCE1
, DECODE(ADL.AWARD_ID
, NULL
, GTEMP.DENOM_CURRENCY_CODE
, GTEMP.PROJFUNC_CURRENCY_CODE)
, DECODE(ADL.AWARD_ID
, NULL
, GTEMP.ACCT_CURRENCY_CODE
, GTEMP.PROJFUNC_CURRENCY_CODE)
, DECODE(ADL.AWARD_ID
, NULL
, GTEMP.PROJECT_CURRENCY_CODE
, GTEMP.PROJFUNC_CURRENCY_CODE)
, GTEMP.PROJFUNC_CURRENCY_CODE
, GTEMP.BILLABLE_FLAG
, GTEMP.REQUEST_ID
, NULL
, GTEMP.JOB_ID
, GTEMP.NON_LABOR_RESOURCE
, GTEMP.NON_LABOR_RESOURCE_ORGN_ID
, GTEMP.WIP_RESOURCE_ID
, GTEMP.INCURRED_BY_PERSON_ID
, GTEMP.INVENTORY_ITEM_ID
, ORG_ID
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, PROJECT_RATE_DATE
, PROJECT_RATE_TYPE
, PROJECT_EXCHANGE_RATE
, PROJFUNC_COST_RATE_DATE
, PROJFUNC_COST_RATE_TYPE
, PROJFUNC_COST_EXCHANGE_RATE
, CBS_ELEMENT_ID
FROM PA_EI_CDL_CM_GTEMP GTEMP
, PA_COMPILED_MULTIPLIERS CM
, GMS_AWARD_DISTRIBUTIONS ADL
WHERE ADL.EXPENDITURE_ITEM_ID = GTEMP.EXPENDITURE_ITEM_ID
AND ADL.CDL_LINE_NUM = GTEMP.LINE_NUM
AND ADL.ADL_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'EXP'
AND CM.IND_COMPILED_SET_ID = GTEMP.IND_COMPILED_SET_ID
AND CM.COST_BASE = GTEMP.COST_BASE
AND CM.COMPILED_MULTIPLIER <> 0
AND GTEMP.BURDEN_SUM_REJECTION_CODE IS NULL
AND PA_BURDEN_COSTING.GET_CURRENT_SPONSORED_FLAG() = 'Y'