PJS_AWARD_SUMMARY

Details

Schema: FUSION
Object owner: PJS
Object type: VIEW

Columns

Name
CONTRACT_ID PROJECT_ID AWARD_PERIOD_ID START_DATE END_DATE CURRENCY_TYPE CURRENCY_CODE 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 REVENUE INVOICE_AMT UNPAID_AMT 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 ITD_REVENUE ITD_INVOICE_AMT ITD_UNPAID_AMT 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

AWARD_PERIOD_ID

START_DATE

END_DATE

CURRENCY_TYPE

CURRENCY_CODE

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

REVENUE

INVOICE_AMT

UNPAID_AMT

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

ITD_REVENUE

ITD_INVOICE_AMT

ITD_UNPAID_AMT

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 fact.contract_id, fact.project_id, all_periods.id award_period_id, all_periods.start_date, all_periods.end_date, fact.currency_type, fact.currency_code, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1,to_number(NULL), fact.raw_cost), to_number(null))) raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1,to_number(NULL), fact.brdnd_cost), to_number(null))) brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1, fact.raw_cost,to_number(NULL)), to_number(null))) actual_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1, fact.brdnd_cost,to_number(NULL)), to_number(null))) actual_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.sup_inv_cmt_raw_cost, to_number(null))) sup_inv_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.sup_inv_cmt_brdnd_cost, to_number(null))) sup_inv_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.po_cmt_raw_cost, to_number(null))) po_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.po_cmt_brdnd_cost, to_number(null))) po_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.pr_cmt_raw_cost, to_number(null))) pr_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.pr_cmt_brdnd_cost, to_number(null))) pr_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.oth_cmt_raw_cost, to_number(null))) oth_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.oth_cmt_brdnd_cost, to_number(null))) oth_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.revenue, to_number(null))) revenue, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.invoice_amt, to_number(null))) invoice_amt, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.unpaid_amt, to_number(null))) unpaid_amt, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1,to_number(NULL), fact.raw_cost))) AS itd_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1,to_number(NULL), fact.brdnd_cost))) AS itd_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1, fact.raw_cost,to_number(NULL)))) AS itd_actual_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1, fact.brdnd_cost,to_number(NULL)))) AS itd_actual_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.sup_inv_cmt_raw_cost)) AS itd_sup_inv_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.sup_inv_cmt_brdnd_cost)) AS itd_sup_inv_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.po_cmt_raw_cost)) AS itd_po_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.po_cmt_brdnd_cost)) AS itd_po_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.pr_cmt_raw_cost)) AS itd_pr_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.pr_cmt_brdnd_cost)) AS itd_pr_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.oth_cmt_raw_cost)) AS itd_oth_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.oth_cmt_brdnd_cost)) AS itd_oth_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.revenue)) AS itd_revenue, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.invoice_amt)) AS itd_invoice_amt, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.unpaid_amt)) AS itd_unpaid_amt, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.to_cmt_raw_cost, to_number(null))) to_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.to_cmt_brdnd_cost, to_number(null))) to_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.ttl_cmt_raw_cost, to_number(null))) ttl_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.ttl_cmt_brdnd_cost, to_number(null))) ttl_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.to_cmt_raw_cost)) AS itd_to_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.to_cmt_brdnd_cost)) AS itd_to_cmt_brdnd_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.ttl_cmt_raw_cost)) AS itd_ttl_cmt_raw_cost, SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.ttl_cmt_brdnd_cost)) AS itd_ttl_cmt_brdnd_cost from pjs_award_fact fact , gms_award_budget_periods all_periods , gms_award_budget_periods fact_period where fact.contract_id = all_periods.award_id and fact.contract_id = fact_period.award_id and fact.award_period_id = fact_period.id GROUP BY fact.contract_id, fact.project_id, all_periods.id, all_periods.start_date, all_periods.end_date, fact.currency_type, fact.currency_code

SQL_Statement

select fact.contract_id,

fact.project_id,

all_periods.id award_period_id,

all_periods.start_date,

all_periods.end_date,

fact.currency_type,

fact.currency_code,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1,to_number(NULL), fact.raw_cost), to_number(null))) raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1,to_number(NULL), fact.brdnd_cost), to_number(null))) brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1, fact.raw_cost,to_number(NULL)), to_number(null))) actual_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, DECODE(fact.plan_version_id,-1, fact.brdnd_cost,to_number(NULL)), to_number(null))) actual_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.sup_inv_cmt_raw_cost, to_number(null))) sup_inv_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.sup_inv_cmt_brdnd_cost, to_number(null))) sup_inv_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.po_cmt_raw_cost, to_number(null))) po_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.po_cmt_brdnd_cost, to_number(null))) po_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.pr_cmt_raw_cost, to_number(null))) pr_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.pr_cmt_brdnd_cost, to_number(null))) pr_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.oth_cmt_raw_cost, to_number(null))) oth_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.oth_cmt_brdnd_cost, to_number(null))) oth_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.revenue, to_number(null))) revenue,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.invoice_amt, to_number(null))) invoice_amt,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.unpaid_amt, to_number(null))) unpaid_amt,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1,to_number(NULL), fact.raw_cost))) AS itd_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1,to_number(NULL), fact.brdnd_cost))) AS itd_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1, fact.raw_cost,to_number(NULL)))) AS itd_actual_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), DECODE(fact.plan_version_id,-1, fact.brdnd_cost,to_number(NULL)))) AS itd_actual_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.sup_inv_cmt_raw_cost)) AS itd_sup_inv_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.sup_inv_cmt_brdnd_cost)) AS itd_sup_inv_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.po_cmt_raw_cost)) AS itd_po_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.po_cmt_brdnd_cost)) AS itd_po_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.pr_cmt_raw_cost)) AS itd_pr_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.pr_cmt_brdnd_cost)) AS itd_pr_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.oth_cmt_raw_cost)) AS itd_oth_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.oth_cmt_brdnd_cost)) AS itd_oth_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.revenue)) AS itd_revenue,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.invoice_amt)) AS itd_invoice_amt,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.unpaid_amt)) AS itd_unpaid_amt,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.to_cmt_raw_cost, to_number(null))) to_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.to_cmt_brdnd_cost, to_number(null))) to_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.ttl_cmt_raw_cost, to_number(null))) ttl_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 0, fact.ttl_cmt_brdnd_cost, to_number(null))) ttl_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.to_cmt_raw_cost)) AS itd_to_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.to_cmt_brdnd_cost)) AS itd_to_cmt_brdnd_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.ttl_cmt_raw_cost)) AS itd_ttl_cmt_raw_cost,

SUM(DECODE(sign(fact_period.start_date-all_periods.start_date), 1, to_number(NULL), fact.ttl_cmt_brdnd_cost)) AS itd_ttl_cmt_brdnd_cost

from pjs_award_fact fact

, gms_award_budget_periods all_periods

, gms_award_budget_periods fact_period

where

fact.contract_id = all_periods.award_id

and fact.contract_id = fact_period.award_id

and fact.award_period_id = fact_period.id

GROUP BY fact.contract_id,

fact.project_id,

all_periods.id,

all_periods.start_date,

all_periods.end_date,

fact.currency_type,

fact.currency_code