PJS_AWARD_RESOURCE

Details

Schema: FUSION
Object owner: PJS
Object type: VIEW

Columns

Name
CONTRACT_ID PROJECT_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 TTL_CMT_RAW_COST TTL_CMT_BRDND_COST ITD_TO_CMT_RAW_COST ITD_TO_CMT_BRDND_COST ITD_TTL_CMT_RAW_COST ITD_TTL_CMT_BRDND_COST

Name

CONTRACT_ID

PROJECT_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

TTL_CMT_RAW_COST

TTL_CMT_BRDND_COST

ITD_TO_CMT_RAW_COST

ITD_TO_CMT_BRDND_COST

ITD_TTL_CMT_RAW_COST

ITD_TTL_CMT_BRDND_COST

Query

SQL_Statement
SELECT f.contract_id, f.project_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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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.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.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

SQL_Statement

SELECT f.contract_id,

f.project_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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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,

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,

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,

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.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.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