FLS PA Budgets and Actuals - Totals, FLS PA Project, PA Fundings, PA Projects

select
fpbaat.function_code fc,
fpbaat.project_number,
pp.project_name,
pp.carrying_out_org_name dept,
pp.project_type,
pp.carrying_out_org_name "Dept.",
fpbaat.project_status,
fpp.project_controller,
fpp.project_manager,
pf.allocated_amount agreement_funding__amount,
fpbaat.revised_revenue revised_revenue_budget,
fpbaat.revised_cost revised_cost_budget,
decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft) forecast_revenue_budget,
decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft) forecast_cost_budget,
fpbaat.invoice_amount,
fpbaat.actual_cost,
decode(sign((fpbaat.forecast_cost_draft-fpbaat.actual_cost)),-1,'x',null) cst_overrun,
pp.function_code,
fpbaat.actual_revenue,
fpbaat.cost_accrual actual_cost_accrual,
to_number((fpbaat.actual_revenue-fpbaat.cost_accrual)) actual_margin,
decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)) draft_revenue_accrual,
decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)) draft_revenue_accrual2,
to_number((decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost))-decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)))))) "Draft Margin ",
to_number((decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost))-decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)))))) "Draft Margin 2",
decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)))) draft_cost_accrual,
decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)))) draft_cost_accrual2,
(decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost))-fpbaat.actual_revenue) revenue_change,
(decode(to_number(xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost)),-1,0,-9,0,xxpa_utils.pa_calc_draft_revenue(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost))-fpbaat.actual_revenue) revenue_change2,
to_number((decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),fpbaat.invoice_amount,fpbaat.actual_cost))))-fpbaat.cost_accrual)) cost_change,
to_number((decode(fpbaat.project_status,'Soft Closed',fpbaat.cost_accrual,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_revenue_draft,fpbaat.forecast_revenue_draft),0,0,decode(decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised',fpbaat.revised_cost_draft,fpbaat.forecast_cost_draft),0,0,xxpa_utils.pa_calc_draft_cost(fpbaat.project_id,decode(decode(sign(instr(fpbaat.project_type,'POC Projects')),1,'Forecast','Revised'),'Revised'<