PJS_AWARD_NONROLLUP_V

Details

  • Schema: FUSION

  • Object owner: PJS

  • Object type: VIEW

Columns

Name

CONTRACT_ID

PROJECT_ID

TOP_TASK_ID

TASK_ID

RBS_VERSION_ID

RBS_ELEMENT_ID

MID_RBS_ELEMENT_ID

TOP_RBS_ELEMENT_ID

AWARD_PERIOD_ID

START_DATE

END_DATE

CURRENCY_TYPE

CURRENCY_CODE

FUNDING_SOURCE_ID

RAW_COST

BRDND_COST

ACTUAL_RAW_COST

ACTUAL_BRDND_COST

SUP_INV_CMT_RAW_COST

SUP_INV_CMT_BRDND_COST

PO_CMT_RAW_COST

PO_CMT_BRDND_COST

PR_CMT_RAW_COST

PR_CMT_BRDND_COST

OTH_CMT_RAW_COST

OTH_CMT_BRDND_COST

ITD_RAW_COST

ITD_BRDND_COST

ITD_ACTUAL_RAW_COST

ITD_ACTUAL_BRDND_COST

ITD_SUP_INV_CMT_RAW_COST

ITD_SUP_INV_CMT_BRDND_COST

ITD_PO_CMT_RAW_COST

ITD_PO_CMT_BRDND_COST

ITD_PR_CMT_RAW_COST

ITD_PR_CMT_BRDND_COST

ITD_OTH_CMT_RAW_COST

ITD_OTH_CMT_BRDND_COST

TO_CMT_RAW_COST

TO_CMT_BRDND_COST

ITD_TO_CMT_RAW_COST

ITD_TO_CMT_BRDND_COST

TTL_CMT_RAW_COST

TTL_CMT_BRDND_COST

ITD_TTL_CMT_RAW_COST

ITD_TTL_CMT_BRDND_COST

Query

SQL_Statement

SELECT contract_id,

project_id,

top_task_id,

task_id,

rbs_version_id,

rbs_element_id,

mid_rbs_element_id,

top_rbs_element_id,

award_period_id,

start_date,

end_date,

currency_type,

currency_code,

funding_source_id,

SUM(raw_cost) raw_cost,

SUM(brdnd_cost) brdnd_cost,

SUM(actual_raw_cost) actual_raw_cost,

SUM(actual_brdnd_cost) actual_brdnd_cost,

SUM(sup_inv_cmt_raw_cost) sup_inv_cmt_raw_cost,

SUM(sup_inv_cmt_brdnd_cost) sup_inv_cmt_brdnd_cost,

SUM(po_cmt_raw_cost) po_cmt_raw_cost,

SUM(po_cmt_brdnd_cost) po_cmt_brdnd_cost,

SUM(pr_cmt_raw_cost) pr_cmt_raw_cost,

SUM(pr_cmt_brdnd_cost) pr_cmt_brdnd_cost,

SUM(oth_cmt_raw_cost) oth_cmt_raw_cost,

SUM(oth_cmt_brdnd_cost) oth_cmt_brdnd_cost,

SUM(itd_raw_cost) itd_raw_cost,

SUM(itd_brdnd_cost) itd_brdnd_cost,

SUM(itd_actual_raw_cost) itd_actual_raw_cost,

SUM(itd_actual_brdnd_cost) itd_actual_brdnd_cost,

SUM(itd_sup_inv_cmt_raw_cost) itd_sup_inv_cmt_raw_cost,

SUM(itd_sup_inv_cmt_brdnd_cost) itd_sup_inv_cmt_brdnd_cost,

SUM(itd_po_cmt_raw_cost) itd_po_cmt_raw_cost,

SUM(itd_po_cmt_brdnd_cost) itd_po_cmt_brdnd_cost,

SUM(itd_pr_cmt_raw_cost) itd_pr_cmt_raw_cost,

SUM(itd_pr_cmt_brdnd_cost) itd_pr_cmt_brdnd_cost,

SUM(itd_oth_cmt_raw_cost) itd_oth_cmt_raw_cost,

SUM(itd_oth_cmt_brdnd_cost) itd_oth_cmt_brdnd_cost,

SUM(to_cmt_raw_cost) to_cmt_raw_cost,

SUM(to_cmt_brdnd_cost) to_cmt_brdnd_cost,

SUM(itd_to_cmt_raw_cost) itd_to_cmt_raw_cost,

SUM(itd_to_cmt_brdnd_cost) itd_to_cmt_brdnd_cost,

SUM(ttl_cmt_raw_cost) ttl_cmt_raw_cost,

SUM(ttl_cmt_brdnd_cost) ttl_cmt_brdnd_cost,

SUM(itd_ttl_cmt_raw_cost) itd_ttl_cmt_raw_cost,

SUM(itd_ttl_cmt_brdnd_cost) itd_ttl_cmt_brdnd_cost

FROM

(SELECT f.contract_id,

f.project_id,

f.top_task_id,

f.task_id,

f.rbs_version_id,

f.rbs_element_id,

f.mid_rbs_element_id,

f.top_rbs_element_id,

f.award_period_id,

t.start_date,

t.end_date,

f.currency_type,

f.currency_code,

f.funding_source_id,

f.raw_cost,

f.brdnd_cost,

f.actual_raw_cost,

f.actual_brdnd_cost,

f.sup_inv_cmt_raw_cost,

f.sup_inv_cmt_brdnd_cost,

f.po_cmt_raw_cost,

f.po_cmt_brdnd_cost,

f.pr_cmt_raw_cost,

f.pr_cmt_brdnd_cost,

f.oth_cmt_raw_cost,

f.oth_cmt_brdnd_cost,

SUM(f.raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_raw_cost,

SUM(f.brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_brdnd_cost,

SUM(f.actual_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_actual_raw_cost,

SUM(f.actual_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_actual_brdnd_cost,

SUM(f.sup_inv_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_sup_inv_cmt_raw_cost,

SUM(f.sup_inv_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_sup_inv_cmt_brdnd_cost,

SUM(f.po_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_po_cmt_raw_cost,

SUM(f.po_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_po_cmt_brdnd_cost,

SUM(f.pr_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_pr_cmt_raw_cost,

SUM(f.pr_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_pr_cmt_brdnd_cost,

SUM(f.oth_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_oth_cmt_raw_cost,

SUM(f.oth_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_oth_cmt_brdnd_cost,

f.to_cmt_raw_cost,

f.to_cmt_brdnd_cost,

f.ttl_cmt_raw_cost,

f.ttl_cmt_brdnd_cost,

SUM(f.to_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_to_cmt_raw_cost,

SUM(f.to_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_to_cmt_brdnd_cost,

SUM(f.ttl_cmt_raw_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_ttl_cmt_raw_cost,

SUM(f.ttl_cmt_brdnd_cost) OVER (PARTITION BY contract_id, project_id, top_task_id, task_id, rbs_version_id, rbs_element_id, mid_rbs_element_id, top_rbs_element_id, currency_type, currency_code, funding_source_id ORDER BY t.start_date ROWS UNBOUNDED PRECEDING) AS itd_ttl_cmt_brdnd_cost

FROM

(SELECT contract_id,

project_id,

top_task_id,

task_id,

rbs_version_id,

rbs_element_id,

mid_rbs_element_id,

top_rbs_element_id,

award_period_id,

currency_type,

currency_code,

funding_source_id,

SUM(DECODE(plan_version_id,-1,to_number(NULL),raw_cost)) raw_cost,

SUM(DECODE(plan_version_id,-1,to_number(NULL),brdnd_cost)) brdnd_cost,

SUM(DECODE(plan_version_id,-1,raw_cost,to_number(NULL))) actual_raw_cost,

SUM(DECODE(plan_version_id,-1,brdnd_cost,to_number(NULL))) actual_brdnd_cost,

SUM(sup_inv_cmt_raw_cost) sup_inv_cmt_raw_cost,

SUM(sup_inv_cmt_brdnd_cost) sup_inv_cmt_brdnd_cost,

SUM(po_cmt_raw_cost) po_cmt_raw_cost,

SUM(po_cmt_brdnd_cost) po_cmt_brdnd_cost,

SUM(pr_cmt_raw_cost) pr_cmt_raw_cost,

SUM(pr_cmt_brdnd_cost) pr_cmt_brdnd_cost,

SUM(oth_cmt_raw_cost) oth_cmt_raw_cost,

SUM(oth_cmt_brdnd_cost) oth_cmt_brdnd_cost,

SUM(to_cmt_raw_cost) to_cmt_raw_cost,

SUM(to_cmt_brdnd_cost) to_cmt_brdnd_cost,

SUM(ttl_cmt_raw_cost) ttl_cmt_raw_cost,

SUM(ttl_cmt_brdnd_cost) ttl_cmt_brdnd_cost

FROM pjs_award_fact

GROUP BY contract_id,

project_id,

top_task_id,

task_id,

rbs_version_id,

rbs_element_id,

mid_rbs_element_id,

top_rbs_element_id,

award_period_id,

currency_type,

currency_code,

funding_source_id

UNION ALL

SELECT contract_id,

project_id,

top_task_id,

task_id,

rbs_version_id,

rbs_element_id,

mid_rbs_element_id,

top_rbs_element_id,

award_period_id,

currency_type,

currency_code,

funding_source_id,

NULL raw_cost,

NULL brdnd_cost,

NULL actual_raw_cost,

NULL actual_brdnd_cost,

NULL sup_inv_cmt_raw_cost,

NULL sup_inv_cmt_brdnd_cost,

NULL po_cmt_raw_cost,

NULL po_cmt_brdnd_cost,

NULL pr_cmt_raw_cost,

NULL pr_cmt_brdnd_cost,

NULL oth_cmt_raw_cost,

NULL oth_cmt_brdnd_cost,

NULL to_cmt_raw_cost,

NULL to_cmt_brdnd_cost,

NULL ttl_cmt_raw_cost,

NULL ttl_cmt_brdnd_cost

FROM

(SELECT DISTINCT c.contract_id,

c.project_id,

c.top_task_id,

c.task_id,

c.rbs_version_id,

c.rbs_element_id,

c.mid_rbs_element_id,

c.top_rbs_element_id,

a.id award_period_id,

c.currency_type,

c.currency_code,

c.funding_source_id

FROM gms_award_budget_periods a,

pjs_award_fact c

WHERE a.award_id = c.contract_id

and not exists (SELECT 1

FROM pjs_award_fact fact

WHERE fact.award_period_id = a.id

AND fact.contract_id = a.award_id

AND fact.project_id = c.project_id

AND fact.task_id = c.task_id

AND fact.rbs_version_id = c.rbs_version_id

AND fact.rbs_element_id = c.rbs_element_id

AND fact.currency_type = c.currency_type

AND fact.currency_code = c.currency_code

AND fact.funding_source_id = c.funding_source_id

)

)

) f,

gms_award_budget_periods t

WHERE f.award_period_id = t.id

)

GROUP BY contract_id,

project_id,

top_task_id,

task_id,

rbs_version_id,

rbs_element_id,

mid_rbs_element_id,

top_rbs_element_id,

award_period_id,

start_date,

end_date,

currency_type,

currency_code,

funding_source_id