1. Tables and Views for Project Management
  2. PJO_DIS_PD_SUMMARY_L1_GL_V

PJO_DIS_PD_SUMMARY_L1_GL_V

Details

  • Schema: FUSION

  • Object owner: PJO

  • Object type: VIEW

Columns

Name

PERIOD_NAME

START_PERIOD_NAME

END_PERIOD_NAME

START_DATE

END_DATE

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

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

Query

SQL_Statement

select decode(ppd.from_anchor_start,'-99999','Preceding Period',decode(ppd.from_anchor_end,'99999','Succeeding Period',ppd.period_name)) PERIOD_NAME,

ppd.start_period_name,

ppd.end_period_name,

decode(ppd.from_anchor_start,-99999,ppe.start_date,ppd.start_date) START_DATE,

decode(ppd.from_anchor_end,99999,ppe.end_date,ppd.end_date) END_DATE,

ppd.from_anchor_start,

null planning_element_id,

ppd.period_profile_id,

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

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

from pjo_xbs_accum_f ppe,

pjo_dis_pd_prof_gl_v ppd,

pjo_planning_options ppo

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.time_phased_code = ppe.calendar_type

and ppo.time_phased_code = 'G'

group by

decode(ppd.from_anchor_start,'-99999','Preceding Period',decode(ppd.from_anchor_end,'99999','Succeeding Period',ppd.period_name)) ,

ppd.start_period_name,

ppd.end_period_name,

decode(ppd.from_anchor_start,-99999,ppe.start_date,ppd.start_date) ,

decode(ppd.from_anchor_end,99999,ppe.end_date,ppd.end_date) ,

ppd.from_anchor_start,

ppd.period_profile_id,

ppd.current_period_name,

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