GMS_CURRENT_WRK_BUDGET_FDTL

Details

Schema: FUSION
Object owner: GMS
Object type: VIEW

Columns

Name
PROJECT_ID AWARD_ID PLAN_VERSION_ID BUDGET_PERIOD_ID START_DATE TASK_ID PLANNING_ELEMENT_ID RBS_ELEMENT_ID FUNDING_SOURCE_ID TOTAL_BUDGET ITD_TOTAL_BUDGET PC_TOTAL_BUDGET PC_ITD_TOTAL_BUDGET

Name

PROJECT_ID

AWARD_ID

PLAN_VERSION_ID

BUDGET_PERIOD_ID

START_DATE

TASK_ID

PLANNING_ELEMENT_ID

RBS_ELEMENT_ID

FUNDING_SOURCE_ID

TOTAL_BUDGET

ITD_TOTAL_BUDGET

PC_TOTAL_BUDGET

PC_ITD_TOTAL_BUDGET

Query

SQL_Statement
SELECT h.project_id, h.award_id, h.plan_version_id, h.budget_period_id, h.start_date, h.task_id, h.planning_element_id, h.rbs_element_id, h.funding_source_id, nvl(h.total_budget, 0) as total_budget, SUM(nvl(h.total_budget, 0)) OVER (PARTITION BY h.plan_version_id, h.task_id, h.planning_element_id, h.rbs_element_id ORDER BY h.start_date ROWS UNBOUNDED PRECEDING) AS itd_total_budget, nvl(h.pc_total_budget, 0) as pc_total_budget, SUM( nvl(h.pc_total_budget, 0)) OVER (PARTITION BY h.plan_version_id, h.task_id, h.planning_element_id, h.rbs_element_id ORDER BY h.start_date ROWS UNBOUNDED PRECEDING) AS pc_itd_total_budget FROM (SELECT e.project_id, e.plan_version_id, e.award_id, e.budget_period_id, e.start_date, e.task_id, e.planning_element_id, e.rbs_element_id, e.funding_source_id, g.total_budget, g.pc_total_budget FROM (SELECT i.object_id1 AS AWARD_ID, a.project_id, a.task_id, a.plan_version_id, a.planning_element_id, a.rbs_element_id, a.FUNDING_SOURCE_ID, d.id AS budget_period_id, d.start_date FROM pjo_planning_elements a, pjo_plan_versions_b i, gms_award_budget_periods d WHERE a.plan_version_id = i.plan_version_id AND i.object_id1 = d.award_id )e, (SELECT a.plan_version_id, a.project_id, a.task_id, a.planning_element_id, a.rbs_element_id, a.FUNDING_SOURCE_ID, b.AWARD_PERIOD_ID, NVL(SUM(b.PFC_BRDND_COST), 0) AS total_budget, NVL(SUM(b.PC_BRDND_COST), 0) AS pc_total_budget FROM pjo_planning_elements a, pjo_plan_line_details b WHERE a.planning_element_id = b.planning_element_id AND a.plan_version_id = b.plan_version_id GROUP BY a.plan_version_id, a.project_id, a.task_id, a.planning_element_id, a.rbs_element_id, a.FUNDING_SOURCE_ID, b.AWARD_PERIOD_ID ) g WHERE e.plan_version_id = g.plan_version_id(+) AND e.budget_period_id = g.award_period_id(+) AND e.project_id = g.project_id(+) AND e.task_id = g.task_id(+) AND e.planning_element_id = g.planning_element_id(+) AND e.rbs_element_id = g.rbs_element_id(+) AND e.funding_source_id = g.funding_source_id(+) ) h ORDER BY h.award_id, h.project_id, h.task_id, h.planning_element_id, h.rbs_element_id, h.start_date

SQL_Statement

SELECT h.project_id,

h.award_id,

h.plan_version_id,

h.budget_period_id,

h.start_date,

h.task_id,

h.planning_element_id,

h.rbs_element_id,

h.funding_source_id,

nvl(h.total_budget, 0) as total_budget,

SUM(nvl(h.total_budget, 0)) OVER (PARTITION BY h.plan_version_id, h.task_id, h.planning_element_id, h.rbs_element_id ORDER BY h.start_date ROWS UNBOUNDED PRECEDING) AS itd_total_budget,

nvl(h.pc_total_budget, 0) as pc_total_budget,

SUM( nvl(h.pc_total_budget, 0)) OVER (PARTITION BY h.plan_version_id, h.task_id, h.planning_element_id, h.rbs_element_id ORDER BY h.start_date ROWS UNBOUNDED PRECEDING) AS pc_itd_total_budget

FROM

(SELECT e.project_id,

e.plan_version_id,

e.award_id,

e.budget_period_id,

e.start_date,

e.task_id,

e.planning_element_id,

e.rbs_element_id,

e.funding_source_id,

g.total_budget,

g.pc_total_budget

FROM

(SELECT i.object_id1 AS AWARD_ID,

a.project_id,

a.task_id,

a.plan_version_id,

a.planning_element_id,

a.rbs_element_id,

a.FUNDING_SOURCE_ID,

d.id AS budget_period_id,

d.start_date

FROM pjo_planning_elements a,

pjo_plan_versions_b i,

gms_award_budget_periods d

WHERE a.plan_version_id = i.plan_version_id

AND i.object_id1 = d.award_id

)e,

(SELECT a.plan_version_id,

a.project_id,

a.task_id,

a.planning_element_id,

a.rbs_element_id,

a.FUNDING_SOURCE_ID,

b.AWARD_PERIOD_ID,

NVL(SUM(b.PFC_BRDND_COST), 0) AS total_budget,

NVL(SUM(b.PC_BRDND_COST), 0) AS pc_total_budget

FROM pjo_planning_elements a,

pjo_plan_line_details b

WHERE a.planning_element_id = b.planning_element_id

AND a.plan_version_id = b.plan_version_id

GROUP BY a.plan_version_id,

a.project_id,

a.task_id,

a.planning_element_id,

a.rbs_element_id,

a.FUNDING_SOURCE_ID,

b.AWARD_PERIOD_ID

) g

WHERE e.plan_version_id = g.plan_version_id(+)

AND e.budget_period_id = g.award_period_id(+)

AND e.project_id = g.project_id(+)

AND e.task_id = g.task_id(+)

AND e.planning_element_id = g.planning_element_id(+)

AND e.rbs_element_id = g.rbs_element_id(+)

AND e.funding_source_id = g.funding_source_id(+)

) h

ORDER BY h.award_id,

h.project_id,

h.task_id,

h.planning_element_id,

h.rbs_element_id,

h.start_date