ECC Projects, Projects Costing: Transactions, SQL1
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Data set for information about expenditure items
Dataset Key: pa-ds-cost-eicdl
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_EICDL_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Description: Data set for information about expenditure items
Dataset Key: pa-ds-cost-eicdl
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_EICDL_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
select x.* from ( select * from (select /*+ push_pred(ei) */ record_type, ecc_spec_id, tmp.expenditure_item_id,'N' IS_WORKTYPE_BILLABLE_ENABLED ,tmp.expenditure_id, tmp.project_id, project_name, project_number, user_project_type, project_type_class_code, tmp.project_type, project_manager, project_start_date, project_completion_date, project_organization, project_status_name, project_status, project_budget, task_plan_capitalizable, task_non_plan_capitalize, proj_plan_capitalize, proj_non_plan_capitalize, tmp.proj_capitalizable, tmp.proj_non_capitalizable, actual_capitalizable, actual_non_capitalizable, actual_yet_to_capitalizable, project_revenue_budget, proj_grouping_method, proj_grp_supplier_invoice, proj_asset_cost_alloc_mhd, proj_enbl_burden_cost_acc, current_pa_period, current_gl_period, current_reporting_pa_period, tmp.projfunc_currency_code, tmp.number_of_assets, tmp.proj_capitalized_cost, (proj_capitalizable-proj_capitalized_cost)proj_cip_cost, tmp.task_id, tmp.task_number, tmp.task_name, tmp.top_task_id, tmp.top_task_name, tmp.top_task_number, tmp.task_start_date, tmp.task_finish_date, tmp.task_manager, tmp.task_organization, tmp.task_burden_schedule, tmp.project_burden_schedule, tmp.task_billable_flag, tmp.task_work_type, tmp.expenditure_item_date, tmp.expenditure_week_ending_date, tmp.expenditure_type, tmp.expenditure_category, tmp.expenditure_type_class, tmp.incurred_by_person_id, tmp.employee_name, tmp.employee_number, tmp.location_code, tmp.job_id, tmp.job_name, tmp.incurred_by_organization_id, tmp.override_to_organization_id, tmp.expenditure_organization_id, tmp.expenditure_org_name, tmp.non_labor_resource, tmp.system_linkage_function, tmp.transaction_source_code, nvl (tmp.transaction_source,'Projects') transaction_source, tmp.orig_transaction_ref, tmp.expenditure_group, tmp.ei_pend_burden_dist, decode(tmp.ei_pend_burden_dist, 'Y', 1, 0) ei_pend_burden_dist_count, tmp.ei_group_unreleased, decode(tmp.ei_group_unreleased, 'Y', 1, 0) ei_group_unreleased_count, tmp.ei_cost_exception, decode(tmp.ei_cost_exception, 'Y', 1, 0) ei_cost_exception_count, tmp.ei_uncosted, decode(tmp.ei_uncosted, 'Y', 1, 0) ei_uncosted_count, tmp.ei_costed, decode(tmp.ei_costed, 'Y', 1, 0) ei_costed_count, tmp.ei_costed_value, tmp.quantity, tmp.burden_cost, tmp.ei_labor_cost, tmp.ei_non_labor_cost, tmp.unit_of_measure, tmp.unit_of_measure_m, tmp.raw_cost, tmp.raw_cost_rate, tmp.cost_distributed_flag, tmp.cost_distributed, tmp.cost_dist_rejection_code, tmp.revenue_distributed, tmp.revenue_distributed_flag, tmp.billed, tmp.billable_flag, tmp.billable, tmp.bill_hold_flag, tmp.bill_hold, tmp.billed_cost, tmp.billable_unbilled_cost, tmp.billable_onhold_cost, tmp.bill_billable_cost, tmp.bill_nonbilable_flag, tmp.bill_nonbilable_cost, tmp.bill_billhold_cost, tmp.bill_billed_flag, tmp.bill_billed_cost, tmp.bill_not_billed_flag, tmp.bill_not_billed_cost, tmp.ei_work_burden_cost, tmp.burdened_cost, tmp.burdened_cost_rate, tmp.denom_currency_code, tmp.denom_raw_cost, tmp.denom_burdened_cost, tmp.acct_currency_code, tmp.acct_rate_type, tmp.acct_rate_date, tmp.acct_exchange_rate, tmp.acct_raw_cost, tmp.acct_burdened_cost, cip_cost, tmp.project_currency_code, tmp.project_raw_cost, tmp.project_burdened_cost, tmp.cost_bur_distributed_flag, tmp.capitalizable_flag, tmp.capitalizable, tmp.asset_line_generated, (decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'N',tmp.cdl_amount,0),0))non_capitalizable_cost, (decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'Y',tmp.cdl_amount,0),0))capitalizable_cost, (decode(project_type_class_code,'CAPITAL',decode(capitalized_flag,'Y',tmp.cdl_amount,0),0))capitalized_cost, tmp.capitalized_flag, tmp.capitalized, tmp.capitalizable_cap_hold_flag, tmp.capitalizable_cap_hold, tmp.adjusted_expenditure_item_id, tmp.net_zero_adjustment_flag, tmp.net_zero_adjustment, tmp.transferred_from_exp_item_id, tmp.transferred_item_flag, tmp.cc_cross_charge_code, tmp.cc_cross_charge_type, tmp.cc_bl_distributed_code, tmp.org_id, tmp.vendor_id, tmp.vendor_name, tmp.document_header_id, tmp.document_distribution_id, tmp.document_line_number, tmp.document_payment_id, tmp.document_type, tmp.document_distribution_type, tmp.Operating_Unit, tmp.capital_cost_code_amt, tmp.line_num, tmp.line_type_code, tmp.line_type, tmp.reversed_flag, tmp.transfer_status_code, tmp.pa_date, tmp.gl_date, tmp.recvr_pa_date, tmp.recvr_gl_date, tmp.pa_period_name, tmp.gl_period_name, tmp.recvr_pa_period_name, tmp.recvr_gl_period_name, tmp.acct_event_id, tmp.amount, tmp.cdl_burdened_cost, tmp.cdl_amount, tmp.cdl_acct_status, tmp.bl_acct_status, tmp.exp_group_status_code, tmp.exp_group_status, tmp.expenditure_status, tmp.precosting_status_code, tmp.precosting_status, asset_name, tmp.work_type, po_number, po_line_number, receipt_number, receipt_line_number, invoice_no, invoice_line_number, po_distribution_line_number, invoice_distribution_line_no, debit_account, credit_account, accounting_source, transfer_status, tmp.provider_le_name, tmp.receiver_le_name, tmp.receiver_operating_unit, tmp.transfer_price, tmp.labot_tp_sch_name, tmp.non_labor_tp_sch_name, accounting_status_code, project_sla_acc_status, sla_gl_acc_status, pre_accounting_status, tmp.project_exchange_rate, tmp.project_rate_date, tmp.project_rate_type, tmp.revenue_accrued, tmp.burden_schedule, tmp.transfer_rejection_reason, tmp.cc_rejection_reason, alert_type, alert_text, cost_identifier, pa_utils.get_lookup_values('COST_TYPE_DESC', cost_identifier, tmp.language) cost_identifier_desc, unaccounted, (decode(tmp.unaccounted,'Unaccounted','Y',null))ei_unaccounted, (decode(tmp.unaccounted,'Unaccounted',1,0))ei_unaccounted_count, (decode(tmp.unaccounted,'Unaccounted',tmp.raw_cost,null))ei_unaccounted_cost, accounting_exception, project_accounted, sla_accounted, gl_accounted, (decode(nvl(tmp.project_accounted,'N'),'Y',tmp.amount,0))project_accounted_cost, (decode(nvl(tmp.sla_accounted,'N'),'Y',tmp.amount,0))sla_accounted_cost, (decode(nvl(tmp.gl_accounted,'N'),'Y',tmp.amount,0))gl_accounted_cost, ei_account_exception, (decode(nvl(tmp.ei_account_exception,'N'),'Y',tmp.amount,0))ei_account_exception_cost, ei_accounted, (decode(nvl(tmp.ei_accounted,'N'),'Y',tmp.amount,0))ei_accounted_cost, sla_acct_code, language, NVL(tmp.sla_gl_acc_status,tmp.project_sla_acc_status) exp_accounting_stage, ( SELECT nvl( pa_utils.get_lookup_values ( 'PA_EI_ACCOUNTING_STATUS',(CASE WHEN(xea.gl_transfer_status_code = 'Y' OR nvl(tmp.historical_flag, 'Y') = 'Y') THEN 'GL' WHEN xea.gl_transfer_status_code <> 'Y' AND cdl.is_ei_final_accounted = 'Y' THEN 'PGL' /* Bug 30749534 */ END), tmp.language), pa_utils.get_lookup_values('PA_EI_ACCOUNTING_STATUS',(CASE WHEN DECODE(cdl.transfer_status_code, 'X', 'PSLA', 'R', 'PSLA', 'P', 'PSLA', 'A', DECODE(cdl.is_ei_final_accounted, 'N', 'PSLA', 'SLA')) = 'PSLA' THEN 'PSLA' WHEN cdl.transfer_status_code NOT IN( 'V', 'G') THEN 'SLA' END), tmp.language) ) FROM /* Commented for Bug#30749534 ( SELECT cd.* FROM ( SELECT c.expenditure_item_id, c.transfer_status_code, c.acct_event_id, c.line_num FROM pa_cost_dist_lines_v c WHERE c.line_type IN ( 'D' ) AND c.creation_date >= '12-DEC-21' and (c.expenditure_item_id, c.line_num) in (select expenditure_item_id, max(line_num) from pa_cost_dist_lines_v b where creation_date >='12-DEC-21' and line_type = 'D' group by b.expenditure_item_id ) ) cd ) cdl, */ /* Added Bug#30749534 */ ( select /*+ index (pecca pa_ecc_cost_cdl_account_n2) */ pecca.expenditure_item_id, pecca.transfer_status_code, pecca.acct_event_id, pecca.line_num, pecca.is_ei_final_accounted, pecca.project_id, pecca.task_id from pa_ecc_cost_cdl_account pecca where pecca.line_type = 'D' ) cdl, xla_ae_headers xea WHERE tmp.cost_distributed_flag = 'Y' AND cdl.expenditure_item_id = tmp.expenditure_item_id and cdl.acct_event_id is not null AND xea.balance_type_code (+) = 'A' AND xea.application_id (+) = 275 AND cdl.acct_event_id = xea.event_id(+) AND tmp.set_of_books_id = xea.ledger_id(+) /* Added for Bug#30749534 */ AND cdl.project_id = tmp.project_id AND cdl.task_id = tmp.task_id ) acct_status_tot_bur, decode(project_type_class_code, 'CAPITAL', (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B') AND(cdl.line_type = tmp.derive_tot_burden_flag OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag) ) AND( (cdl.line_type = 'I' AND cdl.transfer_status_code = 'G') OR cdl.line_type IN('R', 'D') AND cdl.transfer_status_code IN('P', 'V', 'A', 'B') ) AND cdl.billable_flag = 'Y' and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'N' then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0)) else 0 end from ( SELECT cd.* FROM ( SELECT c.expenditure_item_id, c.transfer_status_code, c.acct_event_id, c.line_num, c.amount, c.burdened_cost, c.billable_flag, c.line_type FROM pa_cost_dist_lines_v c WHERE c.creation_date >= '12-DEC-21' AND ( c.expenditure_item_id, c.line_num ) IN ( SELECT expenditure_item_id, MAX(line_num) FROM pa_cost_dist_lines_v b WHERE creation_date >= '12-DEC-21' AND line_type in ('R','D') GROUP BY b.expenditure_item_id, b.line_type ) ) cd ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id) , 0) pending_final_accounting, decode(project_type_class_code, 'CAPITAL', (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B') AND(cdl.line_type = tmp.derive_tot_burden_flag OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag) ) AND( (cdl.line_type = 'I' AND cdl.transfer_status_code = 'G') OR cdl.line_type IN('R', 'D') AND cdl.transfer_status_code IN('P', 'V', 'A', 'B') ) AND cdl.billable_flag = 'Y' AND tmp.capitalized_flag = 'N' and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'Y' then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0)) else 0 end from ( SELECT cd.* FROM ( SELECT c.expenditure_item_id, c.transfer_status_code, c.acct_event_id, c.line_num, c.amount, c.burdened_cost, c.billable_flag, c.line_type FROM pa_cost_dist_lines_v c WHERE c.creation_date >= '12-DEC-21' AND ( c.expenditure_item_id, c.line_num ) IN ( SELECT expenditure_item_id, MAX(line_num) FROM pa_cost_dist_lines_v b WHERE creation_date >= '12-DEC-21' AND line_type in ('R','D') GROUP BY b.expenditure_item_id, b.line_type ) ) cd ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id) , 0) proj_cost_pend_al_gen, tmp.asset_asgn_exists_code, DECODE(project_type_class_code, 'CAPITAL', pa_utils.get_lookup_values('PA_ASSET_ASG_LEVEL', tmp.asset_asgn_exists_code, tmp.language)) asset_assignment_exists, tmp.grouped_task_id, DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_number from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_number, DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_name from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_name, tmp.rate_source_id, /* V6 */ tmp.discount_percentage, tmp.rate_disc_reason_code, pa_utils.get_lookup_values('RATE AND DISCOUNT REASON', tmp.rate_disc_reason_code, tmp.language) rate_discount_reason, tmp.bill_markup_percentage, tmp.bill_group, tmp.projfunc_bill_amount, tmp.bill_amount, tmp.bill_rate, tmp.revenue_hold_flag, pa_utils.get_lookup_values('YES_NO', tmp.revenue_hold_flag, tmp.language) revenue_hold, tmp.accrual_rate, tmp.inventory_item_id, tmp.inventory_item, -9999 event_id, case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y') then 'EI-'||tmp.expenditure_item_id else '-9999' end ei_ev_reference, tmp.funding_task_id, tmp.funding_task_name, tmp.funding_task_number, decode(tmp.billing_project_id, null, 'N', 'Y') billproj_display_flag, bill_chart_period_name, display_planrev_chart, display_totexp_chart, billed_flag, case when tmp.project_type_class_code = 'CONTRACT' THEN case when tmp.billable_flag = 'N' then 'NOBILL' when tmp.cost_distributed_flag = 'N' THEN 'UCST' when tmp.cost_distributed_flag = 'Y' THEN case when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P') THEN 'GENREV' when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P') THEN 'REVREJ' when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N')) and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y')) THEN 'GENINV' when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y') THEN 'INVREJ' when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y' THEN 'BILLED' else NULL end else NULL end else NULL end wip_status_code, pa_utils.get_lookup_values('PA_BILL_ECC_WIP_STATUS', case when tmp.project_type_class_code = 'CONTRACT' THEN case when tmp.billable_flag = 'N' then 'NOBILL' when tmp.cost_distributed_flag = 'N' THEN 'UCST' when tmp.cost_distributed_flag = 'Y' THEN case when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P') THEN 'GENREV' when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P') THEN 'REVREJ' when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N')) and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y')) THEN 'GENINV' when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y') THEN 'INVREJ' when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y' THEN 'BILLED' else NULL end else NULL end else NULL end , tmp.language) wip_status, case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'Y' and tmp.billable_flag = 'Y' THEN tmp.billdb_pfc_bill_amount else 0 end billable_billed_pfc_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'N' and tmp.billable_flag = 'Y' THEN tmp.billdb_pfc_bill_amount else 0 end billable_nonbilled_pfc_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y' THEN tmp.billdb_pfc_bill_amount else 0 end billable_pfc_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.projfunc_bill_amount is not null then 'N' else 'Y' end derived_bill_amt_flag, tmp.billdb_pfc_bill_amount, case when tmp.project_type_class_code = 'CONTRACT' and tmp.revenue_hold_flag <> 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_revenue_hold_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is not null and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_rev_dist_rej_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is not null and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y' and tmp.bill_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_inv_gen_rej_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_rev_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is NULL and tmp.revenue_distributed = 'Y' and tmp.billable_flag = 'Y' and tmp.bill_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_inv_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and nvl(tmp.bill_hold_flag, 'N') = 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_bill_hold_amt, 0 evt_revenue_hold_amt, 0 evt_rev_dist_rej_amt, 0 evt_inv_gen_rej_amt, 0 evt_pend_gen_rev_amt, 0 evt_pend_gen_inv_amt, 0 evt_bill_hold_amt, 0 evt_pfc_rev_derived_amt, tmp.project_id||':'||tmp.funding_task_id||':'|| case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y') then 'EI-'||tmp.expenditure_item_id else '-9999' end DS_ASSOC_PRJ_TSK_EE_ATTR, tmp.project_id||':'||tmp.funding_task_id||':'|| case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y') then 'EI-'||tmp.expenditure_item_id else '-9999' end ||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR, tmp.project_id||':'||tmp.funding_task_id||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_PER_ATTR, case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed <> 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_ready_to_bill_amt, case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed = 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_unaccrued_amt, pa_ecc_bill_load_pkg.get_three_quarter_flag(tmp.set_of_books_id, tmp.bill_gl_period_name) period_disp_flag, gl_period_start_date, case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y' and tmp.revenue_distributed <> 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y' then 'Y' else 'N' end ready_to_bill_flag, case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y' and tmp.revenue_distributed = 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' then 'Y' else 'N' end ei_unaccrued_flag , dfv2.* from ( SELECT DISTINCT /*+ push_pred(ei) */ 'PROJ_DS_EICDL' record_type, 'PROJ-' || TO_CHAR(ei.expenditure_item_id) ecc_spec_id, ei.expenditure_item_id, ei.expenditure_id, ei.project_id, ei.project_name, ei.project_number, ei.project_type user_project_type, ei.project_type_class_code , ei.CAPITAL_COST_TYPE_CODE, pa_utils.get_lookup_values('PROJECT TYPE CLASS', ei.project_type_class_code, ei.language) project_type, ei.DERIVE_TOT_BURDEN_FLAG, ei.project_manager, ei.project_start_date, ei.project_completion_date, ei.project_organization, ei.task_burden_schedule, ei.project_burden_schedule, pa_utils.get_lookup_values('YES_NO',ei.task_billable_flag,ei.language) task_billable_flag, ei.task_work_type, ei.project_status_name, ei.project_status, (SELECT nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0 ))), 0) FROM pa_budget_versions pbv, pa_budget_lines pbl, pa_resource_assignments pra, pa_budget_types pbt WHERE pbv.project_id = ei.project_id AND pbv.budget_type_code = pbt.budget_type_code AND pbt.budget_amount_code = 'C' AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pra.budget_version_id = pbv.budget_version_id AND pra.project_id = pbv.project_id AND pbl.budget_version_id = pra.budget_version_id AND pbl.resource_assignment_id = pra.resource_assignment_id ) project_budget, (SELECT (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0 ))), 0),0)) FROM pa_budget_versions pbv, pa_budget_lines pbl, pa_resource_assignments pra, pa_budget_types pbt WHERE pbv.project_id = ei.project_id AND pra.task_id = ei.task_id AND ei.task_billable_flag = 'Y' AND pbv.budget_type_code = pbt.budget_type_code AND pbt.budget_amount_code = 'C' AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pra.budget_version_id = pbv.budget_version_id AND pra.project_id = pbv.project_id AND pbl.budget_version_id = pra.budget_version_id AND pbl.resource_assignment_id = pra.resource_assignment_id ) task_plan_capitalizable, (SELECT (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0 ))), 0),0)) FROM pa_budget_versions pbv, pa_budget_lines pbl, pa_resource_assignments pra, pa_budget_types pbt WHERE pbv.project_id = ei.project_id AND pra.task_id = ei.task_id AND ei.task_billable_flag = 'N' AND pbv.budget_type_code = pbt.budget_type_code AND pbt.budget_amount_code = 'C' AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pra.budget_version_id = pbv.budget_version_id AND pra.project_id = pbv.project_id AND pbl.budget_version_id = pra.budget_version_id AND pbl.resource_assignment_id = pra.resource_assignment_id ) task_non_plan_capitalize, ( (SELECT (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0 ))), 0),0)) FROM pa_budget_versions pbv, pa_budget_lines pbl, pa_resource_assignments pra, pa_budget_types pbt, pa_budget_entry_methods bem WHERE pbv.project_id = ei.project_id AND pbv.budget_type_code = pbt.budget_type_code AND pbt.budget_amount_code = 'C' AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pbv.budget_entry_method_code = bem.budget_entry_method_code and bem.entry_level_code = 'P' AND pra.budget_version_id = pbv.budget_version_id AND pra.project_id = pbv.project_id AND pbl.budget_version_id =< |