1. Tables and Views for Project Management
  2. PJO_DIS_PD_SUMMARY_V

PJO_DIS_PD_SUMMARY_V

Details

  • Schema: FUSION

  • Object owner: PJO

  • Object type: VIEW

Columns

Name

PERIOD_NAME

START_PERIOD_NAME

END_PERIOD_NAME

START_DATE

END_DATE

SESSION_ID

FROM_ANCHOR_START

PLANNING_ELEMENT_ID

PERIOD_PROFILE_ID

CURRENT_PERIOD_NAME

PROJECT_ID

OBJECT_ID1

TASK_ID

RBS_ELEMENT_ID

TXN_CURRENCY_CODE

PLAN_VERSION_ID

PLAN_TYPE_ID

UNIT_OF_MEASURE

MARGIN_DERIVED_FROM_CODE

PLAN_LINE_ID

CALENDAR_TYPE

QUANTITY

PC_RAW_COST

PC_BRDND_COST

PC_REVENUE

PC_MARGIN

ACT_QUANTITY

PC_ACT_RAW_COST

PC_ACT_BRDND_COST

PC_ACT_REVENUE

PC_ACT_MARGIN

ETC_QUANTITY

PC_ETC_RAW_COST

PC_ETC_BRDND_COST

PC_ETC_REVENUE

PC_ETC_MARGIN

CMT_SUP_INV_QUANTITY

CMT_PO_QUANTITY

CMT_PR_QUANTITY

CMT_OTH_QUANTITY

PC_SUP_INV_CMT_RAW_COST

PC_PO_CMT_RAW_COST

PC_PR_CMT_RAW_COST

PC_OTH_CMT_RAW_COST

PC_SUP_INV_CMT_BRDN_COST

PC_PO_CMT_BRDN_COST

PC_PR_CMT_BRDN_COST

PC_OTH_CMT_BRDN_COST

CMT_TO_QUANTITY

PC_TO_CMT_RAW_COST

PC_TO_CMT_BRDN_COST

CMT_RCPT_QUANTITY

PC_RCPT_CMT_RAW_COST

PC_RCPT_CMT_BRDN_COST

Query

SQL_Statement

SELECT ppd.period_prompt AS period_name,

ppd.start_period_name,

ppd.end_period_name,

ppd.start_date,

ppd.end_date,

ppd.session_id,

ppd.from_anchor_position AS

from_anchor_start,

NULL planning_element_id,

ppd.period_profile_id,

ppd.current_reporting_period AS current_period_name,

ppe.project_id,

NULL object_id1,

ppe.project_element_id task_id,

ppe.rbs_element_id,

NULL txn_currency_code,

ppo.plan_version_id,

ppo.plan_type_id,

NULL unit_of_measure,

ppo.margin_derived_from_code,

NULL plan_line_id,

ppe.calendar_type,

decode(SUM(ppe.quantity), 0, to_number(NULL), SUM(ppe.quantity)) AS quantity,

decode(SUM(ppe.pc_raw_cost), 0, to_number(NULL), SUM(ppe.pc_raw_cost)) AS pc_raw_cost,

decode(SUM(ppe.pc_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_brdn_cost)) AS pc_brdnd_cost,

decode(SUM(ppe.pc_revenue), 0, to_number(NULL), SUM(ppe.pc_revenue)) AS pc_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(SUM(ppe.pc_revenue) -SUM(ppe.pc_brdn_cost)), to_number(SUM(ppe.pc_revenue) -SUM(ppe.pc_raw_cost))) AS pc_margin,

decode(SUM(ppe.act_quantity), 0, to_number(NULL), SUM(ppe.act_quantity)) AS act_quantity,

decode(SUM(ppe.pc_act_raw_cost), 0, to_number(NULL), SUM(ppe.pc_act_raw_cost)) AS pc_act_raw_cost,

decode(SUM(ppe.pc_act_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_act_brdn_cost)) AS pc_act_brdnd_cost,

decode(SUM(ppe.pc_act_revenue), 0, to_number(NULL), SUM(ppe.pc_act_revenue)) AS pc_act_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(SUM(ppe.pc_act_revenue) -SUM(ppe.pc_act_brdn_cost)), to_number(SUM(ppe.pc_act_revenue) -SUM(ppe.pc_act_raw_cost))) AS pc_act_margin,

decode(SUM(ppe.etc_quantity), 0, to_number(NULL), SUM(ppe.etc_quantity)) AS etc_quantity,

decode(SUM(ppe.pc_etc_raw_cost), 0, to_number(NULL), SUM(ppe.pc_etc_raw_cost)) AS pc_etc_raw_cost,

decode(SUM(ppe.pc_etc_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_etc_brdn_cost)) AS pc_etc_brdnd_cost,

decode(SUM(ppe.pc_etc_revenue), 0, to_number(NULL), SUM(ppe.pc_etc_revenue)) AS pc_etc_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(SUM(ppe.pc_etc_revenue) -SUM(ppe.pc_etc_brdn_cost)), to_number(SUM(ppe.pc_etc_revenue) -SUM(ppe.pc_etc_raw_cost))) AS pc_etc_margin,

decode(SUM(ppe.cmt_sup_inv_quantity), 0, to_number(NULL), SUM(ppe.cmt_sup_inv_quantity)) AS cmt_sup_inv_quantity,

decode(SUM(ppe.cmt_po_quantity), 0, to_number(NULL), SUM(ppe.cmt_po_quantity)) AS cmt_po_quantity,

decode(SUM(ppe.cmt_pr_quantity), 0, to_number(NULL), SUM(ppe.cmt_pr_quantity)) AS cmt_pr_quantity,

decode(SUM(ppe.cmt_oth_quantity), 0, to_number(NULL), SUM(ppe.cmt_oth_quantity)) AS cmt_oth_quantity,

decode(SUM(ppe.pc_sup_inv_cmt_raw_cost), 0, to_number(NULL), SUM(ppe.pc_sup_inv_cmt_raw_cost)) AS pc_sup_inv_cmt_raw_cost,

decode(SUM(ppe.pc_po_cmt_raw_cost), 0, to_number(NULL), SUM(ppe.pc_po_cmt_raw_cost)) AS pc_po_cmt_raw_cost,

decode(SUM(ppe.pc_pr_cmt_raw_cost), 0, to_number(NULL), SUM(ppe.pc_pr_cmt_raw_cost)) AS pc_pr_cmt_raw_cost,

decode(SUM(ppe.pc_oth_cmt_raw_cost), 0, to_number(NULL), SUM(ppe.pc_oth_cmt_raw_cost)) AS pc_oth_cmt_raw_cost,

decode(SUM(ppe.pc_sup_inv_cmt_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_sup_inv_cmt_brdn_cost)) AS pc_sup_inv_cmt_brdn_cost,

decode(SUM(ppe.pc_po_cmt_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_po_cmt_brdn_cost)) AS pc_po_cmt_brdn_cost,

decode(SUM(ppe.pc_pr_cmt_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_pr_cmt_brdn_cost)) AS pc_pr_cmt_brdn_cost,

decode(SUM(ppe.pc_oth_cmt_brdn_cost), 0, to_number(NULL), SUM(ppe.pc_oth_cmt_brdn_cost)) AS pc_oth_cmt_brdn_cost,

decode(sum(ppe.CMT_TO_QUANTITY),0,to_number(NULL),sum(ppe.CMT_TO_QUANTITY)) as CMT_TO_QUANTITY,

decode(sum(ppe.PC_TO_CMT_RAW_COST ),0,to_number(NULL),sum(ppe.PC_TO_CMT_RAW_COST )) as PC_TO_CMT_RAW_COST,

decode(sum(ppe.PC_TO_CMT_BRDN_COST ),0,to_number(NULL),sum(ppe.PC_TO_CMT_BRDN_COST )) as PC_TO_CMT_BRDN_COST,

decode(sum(ppe.CMT_RCPT_QUANTITY),0,to_number(NULL),sum(ppe.CMT_RCPT_QUANTITY)) as CMT_RCPT_QUANTITY,

decode(sum(ppe.PC_RCPT_CMT_RAW_COST ),0,to_number(NULL),sum(ppe.PC_RCPT_CMT_RAW_COST )) as PC_RCPT_CMT_RAW_COST,

decode(sum(ppe.PC_RCPT_CMT_BRDN_COST ),0,to_number(NULL),sum(ppe.PC_RCPT_CMT_BRDN_COST )) as PC_RCPT_CMT_BRDN_COST

FROM pjo_xbs_accum_f ppe,

pjo_dis_session_periods ppd,

pjo_planning_options ppo,

pjf_projects_all_b ppj

WHERE ppd.end_date >= ppe.start_date

AND ppd.start_date <= ppe.end_date

AND ppe.plan_version_id = ppo.plan_version_id

AND ppe.project_id = ppo.project_id

and ppo.current_planning_period = ppd.current_reporting_period

and ppo.period_profile_id = ppd.period_profile_id

and ppo.project_id = ppj.project_id

and ppj.org_id = ppd.org_id

and ppo.time_phased_code <> 'A'

GROUP BY ppd.period_prompt,

ppd.start_period_name,

ppd.end_period_name,

ppd.start_date,

ppd.end_date,

ppd.session_id,

ppd.from_anchor_position,

ppd.period_profile_id,

ppd.current_reporting_period,

ppe.plan_version_id,

ppe.project_id,

ppe.project_element_id,

ppe.rbs_element_id,

ppo.plan_version_id,

ppo.plan_type_id,

ppo.margin_derived_from_code,

ppe.calendar_type

UNION

select ppd.period_prompt as period_name,

ppd.start_period_name,

ppd.end_period_name,

ppd.start_date,

ppd.end_date,

ppd.session_id,

ppd.from_anchor_position as from_anchor_start,

null planning_element_id,

ppd.period_profile_id,

ppd.current_reporting_period as current_period_name,

ppe.project_id,

null object_id1,

ppe.project_element_id task_id,

ppe.rbs_element_id,

null txn_currency_code,

ppo.plan_version_id,

ppo.plan_type_id,

null unit_of_measure,

ppo.margin_derived_from_code,

null plan_line_id,

ppe.calendar_type,

decode(sum(ppe.quantity), 0, to_number(null), sum(ppe.quantity)) as quantity,

decode(sum(ppe.pc_raw_cost), 0, to_number(null), sum(ppe.pc_raw_cost)) as pc_raw_cost,

decode(sum(ppe.pc_brdn_cost), 0, to_number(null), sum(ppe.pc_brdn_cost)) as pc_brdnd_cost,

decode(sum(ppe.pc_revenue), 0, to_number(null), sum(ppe.pc_revenue)) as pc_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(sum(ppe.pc_revenue) -sum(ppe.pc_brdn_cost)), to_number(sum(ppe.pc_revenue) -sum(ppe.pc_raw_cost))) as pc_margin,

decode(sum(ppe.act_quantity), 0, to_number(null), sum(ppe.act_quantity)) as act_quantity,

decode(sum(ppe.pc_act_raw_cost), 0, to_number(null), sum(ppe.pc_act_raw_cost)) as pc_act_raw_cost,

decode(sum(ppe.pc_act_brdn_cost), 0, to_number(null), sum(ppe.pc_act_brdn_cost)) as pc_act_brdnd_cost,

decode(sum(ppe.pc_act_revenue), 0, to_number(null), sum(ppe.pc_act_revenue)) as pc_act_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(sum(ppe.pc_act_revenue) -sum(ppe.pc_act_brdn_cost)), to_number(sum(ppe.pc_act_revenue) -sum(ppe.pc_act_raw_cost))) as pc_act_margin,

decode(sum(ppe.etc_quantity), 0, to_number(null), sum(ppe.etc_quantity)) as etc_quantity,

decode(sum(ppe.pc_etc_raw_cost), 0, to_number(null), sum(ppe.pc_etc_raw_cost)) as pc_etc_raw_cost,

decode(sum(ppe.pc_etc_brdn_cost), 0, to_number(null), sum(ppe.pc_etc_brdn_cost)) as pc_etc_brdnd_cost,

decode(sum(ppe.pc_etc_revenue), 0, to_number(null), sum(ppe.pc_etc_revenue)) as pc_etc_revenue,

decode(ppo.margin_derived_from_code, 'B', to_number(sum(ppe.pc_etc_revenue) -sum(ppe.pc_etc_brdn_cost)), to_number(sum(ppe.pc_etc_revenue) -sum(ppe.pc_etc_raw_cost))) as pc_etc_margin,

decode(sum(ppe.cmt_sup_inv_quantity), 0, to_number(null), sum(ppe.cmt_sup_inv_quantity)) as cmt_sup_inv_quantity,

decode(sum(ppe.cmt_po_quantity), 0, to_number(null), sum(ppe.cmt_po_quantity)) as cmt_po_quantity,

decode(sum(ppe.cmt_pr_quantity), 0, to_number(null), sum(ppe.cmt_pr_quantity)) as cmt_pr_quantity,

decode(sum(ppe.cmt_oth_quantity), 0, to_number(null), sum(ppe.cmt_oth_quantity)) as cmt_oth_quantity,

decode(sum(ppe.pc_sup_inv_cmt_raw_cost), 0, to_number(null), sum(ppe.pc_sup_inv_cmt_raw_cost)) as pc_sup_inv_cmt_raw_cost,

decode(sum(ppe.pc_po_cmt_raw_cost), 0, to_number(null), sum(ppe.pc_po_cmt_raw_cost)) as pc_po_cmt_raw_cost,

decode(sum(ppe.pc_pr_cmt_raw_cost), 0, to_number(null), sum(ppe.pc_pr_cmt_raw_cost)) as pc_pr_cmt_raw_cost,

decode(sum(ppe.pc_oth_cmt_raw_cost), 0, to_number(null), sum(ppe.pc_oth_cmt_raw_cost)) as pc_oth_cmt_raw_cost,

decode(sum(ppe.pc_sup_inv_cmt_brdn_cost), 0, to_number(null), sum(ppe.pc_sup_inv_cmt_brdn_cost)) as pc_sup_inv_cmt_brdn_cost,

decode(sum(ppe.pc_po_cmt_brdn_cost), 0, to_number(null), sum(ppe.pc_po_cmt_brdn_cost)) as pc_po_cmt_brdn_cost,

decode(sum(ppe.pc_pr_cmt_brdn_cost), 0, to_number(null), sum(ppe.pc_pr_cmt_brdn_cost)) as pc_pr_cmt_brdn_cost,

decode(sum(ppe.pc_oth_cmt_brdn_cost), 0, to_number(null), sum(ppe.pc_oth_cmt_brdn_cost)) as pc_oth_cmt_brdn_cost,

decode(sum(ppe.CMT_TO_QUANTITY),0,to_number(NULL),sum(ppe.CMT_TO_QUANTITY)) as CMT_TO_QUANTITY,

decode(sum(ppe.PC_TO_CMT_RAW_COST ),0,to_number(NULL),sum(ppe.PC_TO_CMT_RAW_COST )) as PC_TO_CMT_RAW_COST,

decode(sum(ppe.PC_TO_CMT_BRDN_COST ),0,to_number(NULL),sum(ppe.PC_TO_CMT_BRDN_COST )) as PC_TO_CMT_BRDN_COST,

decode(sum(ppe.CMT_RCPT_QUANTITY),0,to_number(NULL),sum(ppe.CMT_RCPT_QUANTITY)) as CMT_RCPT_QUANTITY,

decode(sum(ppe.PC_RCPT_CMT_RAW_COST ),0,to_number(NULL),sum(ppe.PC_RCPT_CMT_RAW_COST )) as PC_RCPT_CMT_RAW_COST,

decode(sum(ppe.PC_RCPT_CMT_BRDN_COST ),0,to_number(NULL),sum(ppe.PC_RCPT_CMT_BRDN_COST )) as PC_RCPT_CMT_BRDN_COST

from pjo_xbs_accum_f ppe,

pjo_dis_session_periods ppd,

pjo_planning_options ppo,

pjf_projects_all_b ppj

where ppd.end_date >= ppe.start_date

and ppd.start_date <= ppe.end_date

and ppe.plan_version_id = ppo.plan_version_id

and ppe.project_id = ppo.project_id

and ppo.object_id1 = ppd.award_id

and ppo.time_phased_code = 'A'

and ppo.project_id = ppj.project_id

and ppj.org_id = ppd.org_id

group by ppd.period_prompt,

ppd.start_period_name,

ppd.end_period_name,

ppd.start_date,

ppd.end_date,

ppd.session_id,

ppd.from_anchor_position,

ppd.period_profile_id,

ppd.current_reporting_period,

ppe.plan_version_id,

ppe.project_id,

ppe.project_element_id,

ppe.rbs_element_id,

ppo.plan_version_id,

ppo.plan_type_id,

ppo.margin_derived_from_code,

ppe.calendar_type