GMS_CURRENT_WRK_BUDGET_SUM

Details

Schema: FUSION
Object owner: GMS
Object type: VIEW

Columns

Name
PROJECT_ID AWARD_ID PLAN_VERSION_ID BUDGET_PERIOD_ID START_DATE CURRENT_PLAN_STATUS_FLAG PLAN_STATUS_CODE 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

CURRENT_PLAN_STATUS_FLAG

PLAN_STATUS_CODE

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.current_plan_status_flag, h.plan_status_code, nvl(h.total_budget, 0) as total_budget, SUM(nvl(h.total_budget, 0)) OVER (PARTITION BY h.plan_version_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 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.current_plan_status_flag, e.plan_status_code, g.total_budget, g.pc_total_budget FROM (SELECT a.object_id1 AS AWARD_ID, a.project_id, a.plan_version_id, a.plan_status_code, a.current_plan_status_flag, d.id AS budget_period_id, d.start_date FROM PJO_PLAN_VERSIONS_VL a, gms_award_budget_periods d WHERE a.object_id1 = d.award_id ) e, (SELECT a1.plan_version_id, a1.project_id, b1.AWARD_PERIOD_ID, NVL(SUM(b1.PFC_BRDND_COST), 0) AS total_budget, NVL(SUM(b1.PC_BRDND_COST), 0) AS pc_total_budget, to_number(0) AS total_consumed, NVL(SUM(b1.PFC_BRDND_COST), 0) AS total_available, NVL(SUM(b1.PC_BRDND_COST), 0) AS pc_total_available, to_number(0) AS total_consumed, to_number(0) AS total_ei, to_number(0) AS total_cmt_pr, to_number(0) AS total_cmt_po, to_number(0) AS total_cmt_si, to_number(0) AS total_cmt_otr FROM pjo_plan_versions_vl a1, pjo_plan_line_details b1 WHERE a1.plan_version_id = b1.plan_version_id GROUP BY a1.plan_version_id, a1.project_id, b1.AWARD_PERIOD_ID ) g WHERE e.plan_version_id = g.plan_version_id(+) AND e.budget_period_id = g.award_period_id(+) ) h ORDER BY h.award_id, h.project_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.current_plan_status_flag,

h.plan_status_code,

nvl(h.total_budget, 0) as total_budget,

SUM(nvl(h.total_budget, 0)) OVER (PARTITION BY h.plan_version_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 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.current_plan_status_flag,

e.plan_status_code,

g.total_budget,

g.pc_total_budget

FROM

(SELECT a.object_id1 AS AWARD_ID,

a.project_id,

a.plan_version_id,

a.plan_status_code,

a.current_plan_status_flag,

d.id AS budget_period_id,

d.start_date

FROM PJO_PLAN_VERSIONS_VL a,

gms_award_budget_periods d

WHERE a.object_id1 = d.award_id

) e,

(SELECT a1.plan_version_id,

a1.project_id,

b1.AWARD_PERIOD_ID,

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

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

to_number(0) AS total_consumed,

NVL(SUM(b1.PFC_BRDND_COST), 0) AS total_available,

NVL(SUM(b1.PC_BRDND_COST), 0) AS pc_total_available,

to_number(0) AS total_consumed,

to_number(0) AS total_ei,

to_number(0) AS total_cmt_pr,

to_number(0) AS total_cmt_po,

to_number(0) AS total_cmt_si,

to_number(0) AS total_cmt_otr

FROM pjo_plan_versions_vl a1,

pjo_plan_line_details b1

WHERE a1.plan_version_id = b1.plan_version_id

GROUP BY a1.plan_version_id,

a1.project_id,

b1.AWARD_PERIOD_ID

) g

WHERE e.plan_version_id = g.plan_version_id(+)

AND e.budget_period_id = g.award_period_id(+)

) h

ORDER BY h.award_id,

h.project_id,

h.start_date