PJC_CDL_BURDEN_DETAIL_V

Details

• Schema: FUSION

• Object owner: PJC

• Object type: VIEW

Columns

Name

PROJECT_ID TASK_ID ORGANIZATION_ID PRVDR_GL_DATE PA_DATE PA_PERIOD_NAME ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE_CATEGORY PERSON_TYPE PO_LINE_ID SYSTEM_LINKAGE_FUNCTION IND_COST_CODE EI_EXPENDITURE_TYPE_ID ICC_EXPENDITURE_TYPE_ID COST_BASE COMPILED_MULTIPLIER IND_RATE_SCH_ID IND_RATE_SCH_REVISION_ID EXPENDITURE_ITEM_ID LINE_NUM EXPENDITURE_ITEM_DATE BURDEN_COST DENOM_BURDENED_COST ACCT_BURDENED_COST PROJECT_BURDENED_COST BURDEN_SUM_SOURCE_RUN_ID BURDEN_SUM_REJECTION_CODE DENOM_CURRENCY_CODE ACCT_CURRENCY_CODE PROJECT_CURRENCY_CODE PROJFUNC_CURRENCY_CODE BILLABLE_FLAG REQUEST_ID ADJUSTMENT_TYPE JOB_ID NON_LABOR_RESOURCE_ID NON_LABOR_RESOURCE_ORG_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 CONTRACT_ID CONTRACT_LINE_ID CAPITALIZABLE_FLAG CONTEXT_CATEGORY USER_DEF_ATTRIBUTE1 USER_DEF_ATTRIBUTE2 USER_DEF_ATTRIBUTE3 USER_DEF_ATTRIBUTE4 USER_DEF_ATTRIBUTE5 USER_DEF_ATTRIBUTE6 USER_DEF_ATTRIBUTE7 USER_DEF_ATTRIBUTE8 USER_DEF_ATTRIBUTE9 USER_DEF_ATTRIBUTE10 QUANTITY DENOM_RAW_COST IND_RATE_SCHEDULE_TYPE COST_IND_SCH_FIXED_DATE IND_STRUCTURE_NAME PRECEDENCE IND_COMPILED_SET_ID MULTIPLIER_NUM PREV_IND_COMPILED_SET_ID LINE_NUM_REVERSED BURDEN_REVERSED_FLAG DATA_SET_ID RESERVED_ATTRIBUTE1 EXPENDITURE_ITEM_DATE2

Query

SQL_Statement

SELECT cdl.project_id, cdl.task_id, ics.organization_id, cdl.prvdr_gl_date, cdl.prvdr_pa_date pa_date, DECODE(cdl.prev_ind_compiled_set_id, NULL, cdl.prvdr_pa_period_name, NVL(p.period_name, cdl.prvdr_pa_period_name)) pa_period_name, ei.attribute1, ei.attribute2, ei.attribute3, ei.attribute4, ei.attribute5, ei.attribute6, ei.attribute7, ei.attribute8, ei.attribute9, ei.attribute10, ei.attribute_category, ei.person_type person_type, NULL po_line_id, ei.system_linkage_function, cm.ind_cost_code, ei.expenditure_type_id ei_expenditure_type_id, icc.expenditure_type_id icc_expenditure_type_id, cm.cost_base, cm.compiled_multiplier, irsr.ind_rate_sch_id, irsr.ind_rate_sch_revision_id, ei.expenditure_item_id, cdl.line_num, nvl(ei.prvdr_accrual_date, ei.expenditure_item_date) expenditure_item_date, pjf_currency_grp.round_currency_amount(cdl.projfunc_raw_cost*cm.compiled_multiplier ,cdl.projfunc_currency_code) burden_cost, pjf_currency_grp.round_currency_amount(cdl.denom_raw_cost*cm.compiled_multiplier ,cdl.denom_currency_code) denom_burdened_cost, pjf_currency_grp.round_currency_amount(cdl.acct_raw_cost*cm.compiled_multiplier ,cdl.acct_currency_code) acct_burdened_cost, pjf_currency_grp.round_currency_amount(cdl.project_raw_cost*cm.compiled_multiplier ,cdl.project_currency_code) project_burdened_cost, cdl.burden_sum_source_run_id, cdl.burden_sum_rejection_code, cdl.denom_currency_code, cdl.acct_currency_code, cdl.project_currency_code, cdl.projfunc_currency_code, cdl.billable_flag, cdl.request_id, DECODE(ei.adjustment_type, 'BURDEN_RESUMMARIZE', ei.adjustment_type, NULL) adjustment_type, ei.person_job_id job_id, ei.non_labor_resource_id, ei.non_labor_resource_org_id, NULL wip_resource_id, ei.incurred_by_person_id, ei.inventory_item_id, ei.org_id, cdl.acct_rate_date, cdl.acct_rate_type, cdl.acct_exchange_rate, cdl.project_rate_date, cdl.project_rate_type, cdl.project_exchange_rate, cdl.projfunc_cost_rate_date, cdl.projfunc_cost_rate_type, cdl.projfunc_cost_exchange_rate, ei.contract_id, /*Uncommented for bug 17977823 to use ei level contract instead of null*/ NULL contract_line_id, cdl.capitalizable_flag, ei. context_category, ei.user_def_attribute1, ei.user_def_attribute2, ei.user_def_attribute3, ei.user_def_attribute4, ei.user_def_attribute5, ei.user_def_attribute6, ei.user_def_attribute7, ei.user_def_attribute8, ei.user_def_attribute9, ei.user_def_attribute10, ei.quantity, cdl.denom_raw_cost, irs.ind_rate_schedule_type, DECODE(irs.ind_rate_schedule_type, 'F', irsr.start_date_active, NULL) cost_ind_sch_fixed_date, irsr.ind_structure_name, cm.precedence, ics.ind_compiled_set_id, cm.multiplier_num, cdl.prev_ind_compiled_set_id, cdl.line_num_reversed, 'N' burden_reversed_flag, cdl.data_set_id, ei.reserved_attribute1, ei.expenditure_item_date expenditure_item_date2 FROM pjc_cost_dist_lines_all cdl, pjc_exp_items_all ei, pjf_ind_compiled_sets ics, pjf_irs_revisions irsr, pjf_cost_base_exp_types cbet, pjf_compiled_multipliers cm, pjf_ind_cost_codes icc, pjf_txn_document_b td, pjf_p_periods_all_v p, pjf_ind_rate_sch_b irs WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id AND irsr.ind_structure_name = cbet.ind_structure_name AND cbet.cost_base = cm.cost_base AND cbet.cost_base_type = 'INDIRECT COST' AND cbet.expenditure_type_id = ei.expenditure_type_id AND ics.organization_id = NVL(ei.override_to_organization_id, ei.incurred_by_organization_id) AND ics.cost_base = cbet.cost_base AND ics.ind_compiled_set_id = cdl.ind_compiled_set_id AND icc.ind_cost_code = cm.ind_cost_code AND cm.ind_compiled_set_id = DECODE(cbet.cost_base, NULL, NULL, ics.ind_compiled_set_id) AND cdl.burden_sum_rejection_code IS NULL AND cdl.line_type IN ('R', 'B') AND cdl.burden_sum_source_run_id = pjc_burden_costing.get_current_run_id() AND cdl.expenditure_item_id = ei.expenditure_item_id AND cdl.project_id = pjc_burden_costing.get_current_project_id() AND ei.document_id = td.document_id AND NVL(td.allow_burden_flag, 'N') <> 'Y' AND ei.org_id = p.org_id (+) AND ei.expenditure_item_date BETWEEN p.start_date(+) AND p.end_date(+) AND irsr.ind_rate_sch_id = irs.ind_rate_sch_id UNION ALL SELECT cdl.project_id, cdl.task_id, ics.organization_id, cdl.prvdr_gl_date, cdl.prvdr_pa_date pa_date, DECODE(cdl.prev_ind_compiled_set_id, NULL, cdl.prvdr_pa_period_name, NVL(p.period_name, cdl.prvdr_pa_period_name)) pa_period_name, ei.attribute1, ei.attribute2, ei.attribute3, ei.attribute4, ei.attribute5, ei.attribute6, ei.attribute7, ei.attribute8, ei.attribute9, ei.attribute10, ei.attribute_category, ei.person_type person_type, NULL po_line_id, ei.system_linkage_function system_linkage_function, cm.ind_cost_code, ei.expenditure_type_id ei_expenditure_type_id, icc.expenditure_type_id icc_expenditure_type_id, cm.cost_base, cm.compiled_multiplier, irsr.ind_rate_sch_id, irsr.ind_rate_sch_revision_id, ei.expenditure_item_id, cdl.line_num, nvl(ei.prvdr_accrual_date, ei.expenditure_item_date) expenditure_item_date, pjf_currency_grp.round_currency_amount(cdl.projfunc_raw_cost*cm.compiled_multiplier*-1 ,cdl.projfunc_currency_code) burden_cost, pjf_currency_grp.round_currency_amount(cdl.denom_raw_cost*cm.compiled_multiplier*-1 ,cdl.denom_currency_code) denom_burdened_cost, pjf_currency_grp.round_currency_amount(cdl.acct_raw_cost*cm.compiled_multiplier*-1 ,cdl.acct_currency_code) acct_burdened_cost, pjf_currency_grp.round_currency_amount(cdl.project_raw_cost*cm.compiled_multiplier*-1 ,cdl.project_currency_code) project_burdened_cost, cdl.burden_sum_source_run_id, cdl.burden_sum_rejection_code, cdl.denom_currency_code, cdl.acct_currency_code, cdl.project_currency_code, cdl.projfunc_currency_code, cdl.billable_flag, cdl.request_id, DECODE(ei.adjustment_type, 'BURDEN_RESUMMARIZE', ei.adjustment_type, NULL) adjustment_type, ei.person_job_id job_id, ei.non_labor_resource_id, ei.non_labor_resource_org_id, NULL wip_resource_id, ei.incurred_by_person_id, ei.inventory_item_id, ei.org_id, cdl.acct_rate_date, cdl.acct_rate_type, cdl.acct_exchange_rate, cdl.project_rate_date, cdl.project_rate_type, cdl.project_exchange_rate, cdl.projfunc_cost_rate_date, cdl.projfunc_cost_rate_type, cdl.projfunc_cost_exchange_rate, ei.contract_id, /*Uncommented for bug 17977823 to use ei level contract instead of null*/ NULL contract_line_id, cdl.capitalizable_flag, ei.context_category, ei.user_def_attribute1, ei.user_def_attribute2, ei.user_def_attribute3, ei.user_def_attribute4, ei.user_def_attribute5, ei.user_def_attribute6, ei.user_def_attribute7, ei.user_def_attribute8, ei.user_def_attribute9, ei.user_def_attribute10, ei.quantity, -cdl.denom_raw_cost, irs.ind_rate_schedule_type, DECODE(irs.ind_rate_schedule_type, 'F', irsr.start_date_active, NULL) cost_ind_sch_fixed_date, irsr.ind_structure_name, cm.precedence, ics.ind_compiled_set_id, cm.multiplier_num, cdl.prev_ind_compiled_set_id, cdl.line_num_reversed, 'Y' burden_reversed_flag, cdl.data_set_id, ei.reserved_attribute1, ei.expenditure_item_date expenditure_item_date2 FROM pjc_cost_dist_lines_all cdl, pjc_exp_items_all ei, pjf_ind_compiled_sets ics, pjf_irs_revisions irsr, pjf_cost_base_exp_types cbet, pjf_compiled_multipliers cm, pjf_ind_cost_codes icc, pjf_txn_document_b td, pjf_p_periods_all_v p, pjf_ind_rate_sch_b irs WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id AND irsr.ind_structure_name = cbet.ind_structure_name AND cbet.cost_base = cm.cost_base AND cbet.cost_base_type = 'INDIRECT COST' AND cbet.expenditure_type_id = ei.expenditure_type_id AND ics.organization_id = NVL(ei.override_to_organization_id,ei.incurred_by_organization_id) AND ics.cost_base = cbet.cost_base AND ics.ind_compiled_set_id = cdl.prev_ind_compiled_set_id AND cdl.prev_ind_compiled_set_id IS NOT NULL AND icc.ind_cost_code = cm.ind_cost_code AND cm.ind_compiled_set_id = DECODE(cbet.cost_base,NULL,NULL,ics.ind_compiled_set_id) AND cdl.burden_sum_rejection_code IS NULL AND cdl.line_type IN ('R', 'B') AND cdl.burden_sum_source_run_id = pjc_burden_costing.get_current_run_id() AND cdl.expenditure_item_id = ei.expenditure_item_id AND cdl.project_id = pjc_burden_costing.get_current_project_id() AND ei.document_id = td.document_id AND NVL(td.allow_burden_flag, 'N') <> 'Y' AND ei.org_id = p.org_id (+) AND ei.expenditure_item_date BETWEEN p.start_date(+) AND p.end_date(+) AND irsr.ind_rate_sch_id = irs.ind_rate_sch_id