1. Tables and Views for Project Management
  2. PJO_DIS_PD_DETAIL_V

PJO_DIS_PD_DETAIL_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

PLANNING_START_DATE

PLANNING_END_DATE

UNIT_OF_MEASURE

SPREAD_CURVE_ID

MARGIN_DERIVED_FROM_CODE

PLAN_LINE_ID

QUANTITY

TC_RAW_COST

PC_RAW_COST

PFC_RAW_COST

TC_BRDND_COST

PC_BRDND_COST

PFC_BRDND_COST

TC_REVENUE

PC_REVENUE

PFC_REVENUE

TC_MARGIN

PC_MARGIN

PFC_MARGIN

ACT_QUANTITY

TC_ACT_RAW_COST

PC_ACT_RAW_COST

PFC_ACT_RAW_COST

TC_ACT_BRDND_COST

PC_ACT_BRDND_COST

PFC_ACT_BRDND_COST

TC_ACT_REVENUE

PC_ACT_REVENUE

PFC_ACT_REVENUE

TC_ACT_MARGIN

PC_ACT_MARGIN

PFC_ACT_MARGIN

QUANTITY_ORIG

TC_RAW_COST_ORIG

TC_BRDND_COST_ORIG

TC_REVENUE_ORIG

TC_BILL_RATE

TC_RAW_COST_RATE

TC_BRDND_COST_RATE

TC_ACT_RAW_COST_RATE

PC_ACT_RAW_COST_RATE

PFC_ACT_RAW_COST_RATE

TC_ACT_BRDND_COST_RATE

TC_ACT_BILL_RATE

PO_QUANTITY

TC_PO_RAW_COST

PC_PO_RAW_COST

PFC_PO_RAW_COST

TC_PO_BRDND_COST

PC_PO_BRDND_COST

PFC_PO_BRDND_COST

SI_QUANTITY

TC_SI_RAW_COST

PC_SI_RAW_COST

PFC_SI_RAW_COST

TC_SI_BRDND_COST

PC_SI_BRDND_COST

PFC_SI_BRDND_COST

EXT_QUANTITY

TC_EXT_RAW_COST

PC_EXT_RAW_COST

PFC_EXT_RAW_COST

TC_EXT_BRDND_COST

PC_EXT_BRDND_COST

PFC_EXT_BRDND_COST

REQ_QUANTITY

TC_REQ_RAW_COST

PC_REQ_RAW_COST

PFC_REQ_RAW_COST

TC_REQ_BRDND_COST

PC_REQ_BRDND_COST

PFC_REQ_BRDND_COST

TO_QUANTITY

TC_TO_RAW_COST

PC_TO_RAW_COST

PFC_TO_RAW_COST

TC_TO_BRDND_COST

PC_TO_BRDND_COST

PFC_TO_BRDND_COST

RCPT_QUANTITY

TC_RCPT_RAW_COST

PC_RCPT_RAW_COST

PFC_RCPT_RAW_COST

TC_RCPT_BRDND_COST

PC_RCPT_BRDND_COST

PFC_RCPT_BRDND_COST

TC_STD_BRDND_MULTIPLIER

TC_STD_BRDND_COST_RATE

TC_STD_RAW_COST_RATE

TC_STD_BILL_RATE

Query

SQL_Statement

select ppd.period_name,

ppd.start_period_name,

ppd.end_period_name,

ppd.start_date,

ppd.end_date,

ppd.from_anchor_start,

ppd.planning_element_id,

ppd.period_profile_id,

ppd.current_period_name,

ppd.project_id,

ppd.object_id1,

ppd.task_id,

ppd.rbs_element_id,

ppd.txn_currency_code,

ppd.plan_Version_id,

ppd.plan_type_id,

ppd.PLANNING_START_DATE,

ppd.PLANNING_END_DATE,

ppd.UNIT_OF_MEASURE,

ppd.spread_curve_id,

ppd.MARGIN_DERIVED_FROM_CODE,

ppd.PLAN_LINE_ID,

decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity)) as QUANTITY,

decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost)) as TC_RAW_COST,

decode(sum(ppld.pc_raw_cost),0,to_number(NULL),sum(ppld.pc_raw_cost)) as PC_RAW_COST,

decode(sum(ppld.pfc_raw_cost),0,to_number(NULL),sum(ppld.pfc_raw_cost)) as PFC_RAW_COST,

decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost)) as TC_BRDND_COST,

decode(sum(ppld.pc_brdnd_cost),0,to_number(NULL),sum(ppld.pc_brdnd_cost)) as PC_BRDND_COST,

decode(sum(ppld.pfc_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_brdnd_cost)) as PFC_BRDND_COST,

decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)) as TC_REVENUE,

decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)) as PC_REVENUE,

decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)) as PFC_REVENUE,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_revenue) - sum(ppld.tc_brdnd_cost)),to_number(sum(ppld.tc_revenue) - sum(ppld.tc_raw_cost))) as TC_MARGIN,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pc_revenue) - sum(ppld.pc_brdnd_cost)),to_number(sum(ppld.pc_revenue) - sum(ppld.pc_raw_cost))) as PC_MARGIN,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B', to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_brdnd_cost)),to_number(sum(ppld.pfc_revenue) - sum(ppld.pfc_raw_cost))) as PFC_MARGIN,

decode(sum(ppld.act_quantity),0,to_number(NULL),sum(ppld.act_quantity)) as ACT_QUANTITY,

decode(sum(ppld.tc_act_raw_cost),0,to_number(NULL),sum(ppld.tc_act_raw_cost)) as TC_ACT_RAW_COST,

decode(sum(ppld.pc_act_raw_cost),0,to_number(NULL),sum(ppld.pc_act_raw_cost)) as PC_ACT_RAW_COST,

decode(sum(ppld.pfc_act_raw_cost),0,to_number(NULL),sum(ppld.pfc_act_raw_cost)) as PFC_ACT_RAW_COST,

decode(sum(ppld.tc_act_brdnd_cost),0,to_number(NULL),sum(ppld.tc_act_brdnd_cost)) as TC_ACT_BRDND_COST,

decode(sum(ppld.pc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pc_act_brdnd_cost)) as PC_ACT_BRDND_COST,

decode(sum(ppld.pfc_act_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_act_brdnd_cost)) as PFC_ACT_BRDND_COST,

decode(sum(ppld.tc_act_revenue),0,to_number(null),sum(ppld.tc_act_revenue)) as TC_ACT_REVENUE,

decode(sum(ppld.pc_act_revenue),0,to_number(NULL),sum(ppld.pc_act_revenue)) as PC_ACT_REVENUE,

decode(sum(ppld.pfc_act_revenue),0,to_number(NULL),sum(ppld.pfc_act_revenue)) as PFC_ACT_REVENUE,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.tc_act_revenue) - sum(ppld.tc_act_brdnd_cost)),to_number(sum(ppld.tc_act_revenue) - sum(ppld.tc_act_raw_cost))) as TC_ACT_MARGIN,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.pc_act_revenue) - sum(ppld.pc_act_brdnd_cost)),to_number(sum(ppld.pc_act_revenue) - sum(ppld.pc_act_raw_cost))) as PC_ACT_MARGIN,

DECODE(ppd.MARGIN_DERIVED_FROM_CODE,'B',to_number(sum(ppld.pfc_act_revenue) - sum(ppld.pfc_act_brdnd_cost)),to_number(sum(ppld.pfc_act_revenue) - sum(ppld.pfc_act_raw_cost))) as PFC_ACT_MARGIN,

decode(sum(ppld.quantity),0,to_number(NULL),sum(ppld.quantity)) as QUANTITY_ORIG,

decode(sum(ppld.tc_raw_cost),0,to_number(NULL),sum(ppld.tc_raw_cost)) as TC_RAW_COST_ORIG,

decode(sum(ppld.tc_brdnd_cost),0,to_number(NULL),sum(ppld.tc_brdnd_cost)) as TC_BRDND_COST_ORIG,

decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)) as TC_REVENUE_ORIG,

(sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_bill_rate_override,nvl(ppld.tc_std_bill_rate,0)),0,null,(ppld.quantity)))))) as TC_BILL_RATE,

(sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_raw_cost_rate_override,nvl(ppld.tc_std_raw_cost_rate,0)),0,null,(ppld.quantity)))))) as TC_RAW_COST_RATE,

(sum( decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity) * nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)))))/ decode(sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity)))),0,NULL,sum(decode(ppld.quantity,0,null,decode(nvl(ppld.tc_brdnd_cost_rate_override,nvl(ppld.tc_std_brdnd_cost_rate,0)),0,null,(ppld.quantity)))))) as TC_BRDND_COST_RATE,

DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.tc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.tc_act_raw_cost))/ SUM(ppld.act_quantity))) as TC_ACT_RAW_COST_RATE,

DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.pc_act_raw_cost))/ SUM(ppld.act_quantity))) as PC_ACT_RAW_COST_RATE,

DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.pfc_act_raw_cost),0, TO_NUMBER (NULL),SUM (ppld.pfc_act_raw_cost))/ SUM(ppld.act_quantity))) as PFC_ACT_RAW_COST_RATE,

DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL), (DECODE (SUM (ppld.tc_act_brdnd_cost),0, TO_NUMBER (NULL),SUM (ppld.tc_act_brdnd_cost))/ SUM(ppld.act_quantity))) as TC_ACT_BRDND_COST_RATE,

DECODE (SUM (ppld.act_quantity),0, TO_NUMBER (NULL),NULL, TO_NUMBER (NULL),(DECODE (SUM (ppld.tc_act_revenue),0, TO_NUMBER (NULL),SUM (ppld.tc_act_revenue))/ SUM (ppld.act_quantity))) as TC_ACT_BILL_RATE,

decode(sum(ppld.po_quantity),0,to_number(NULL),sum(ppld.po_quantity)) as PO_QUANTITY,

decode(sum(ppld.tc_po_raw_cost),0,to_number(NULL),sum(ppld.tc_po_raw_cost)) as TC_PO_RAW_COST,

decode(sum(ppld.pc_po_raw_cost),0,to_number(NULL),sum(ppld.pc_po_raw_cost)) as PC_PO_RAW_COST,

decode(sum(ppld.pfc_po_raw_cost),0,to_number(NULL),sum(ppld.pfc_po_raw_cost)) as PFC_PO_RAW_COST,

decode(sum(ppld.tc_po_brdnd_cost),0,to_number(NULL),sum(ppld.tc_po_brdnd_cost)) as TC_PO_BRDND_COST,

decode(sum(ppld.pc_po_brdnd_cost),0,to_number(NULL),sum(ppld.pc_po_brdnd_cost)) as PC_PO_BRDND_COST,

decode(sum(ppld.pfc_po_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_po_brdnd_cost)) as PFC_PO_BRDND_COST,

decode(sum(ppld.si_quantity),0,to_number(NULL),sum(ppld.si_quantity)) as SI_QUANTITY,

decode(sum(ppld.tc_si_raw_cost),0,to_number(NULL),sum(ppld.tc_si_raw_cost)) as TC_SI_RAW_COST,

decode(sum(ppld.pc_si_raw_cost),0,to_number(NULL),sum(ppld.pc_si_raw_cost)) as PC_SI_RAW_COST,

decode(sum(ppld.pfc_si_raw_cost),0,to_number(NULL),sum(ppld.pfc_si_raw_cost)) as PFC_SI_RAW_COST,

decode(sum(ppld.tc_si_brdnd_cost),0,to_number(NULL),sum(ppld.tc_si_brdnd_cost)) as TC_SI_BRDND_COST,

decode(sum(ppld.pc_si_brdnd_cost),0,to_number(NULL),sum(ppld.pc_si_brdnd_cost)) as PC_SI_BRDND_COST,

decode(sum(ppld.pfc_si_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_si_brdnd_cost)) as PFC_SI_BRDND_COST,

decode(sum(ppld.ext_quantity),0,to_number(NULL),sum(ppld.ext_quantity)) as EXT_QUANTITY,

decode(sum(ppld.tc_ext_raw_cost),0,to_number(NULL),sum(ppld.tc_ext_raw_cost)) as TC_EXT_RAW_COST,

decode(sum(ppld.pc_ext_raw_cost),0,to_number(NULL),sum(ppld.pc_ext_raw_cost)) as PC_EXT_RAW_COST,

decode(sum(ppld.pfc_ext_raw_cost),0,to_number(NULL),sum(ppld.pfc_ext_raw_cost)) as PFC_EXT_RAW_COST,

decode(sum(ppld.tc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.tc_ext_brdnd_cost)) as TC_EXT_BRDND_COST,

decode(sum(ppld.pc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.pc_ext_brdnd_cost)) as PC_EXT_BRDND_COST,

decode(sum(ppld.pfc_ext_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_ext_brdnd_cost)) as PFC_EXT_BRDND_COST,

decode(sum(ppld.req_quantity),0,to_number(NULL),sum(ppld.req_quantity)) as REQ_QUANTITY,

decode(sum(ppld.tc_req_raw_cost),0,to_number(NULL),sum(ppld.tc_req_raw_cost)) as TC_REQ_RAW_COST,

decode(sum(ppld.pc_req_raw_cost),0,to_number(NULL),sum(ppld.pc_req_raw_cost)) as PC_REQ_RAW_COST,

decode(sum(ppld.pfc_req_raw_cost),0,to_number(NULL),sum(ppld.pfc_req_raw_cost)) as PFC_REQ_RAW_COST,

decode(sum(ppld.tc_req_brdnd_cost),0,to_number(NULL),sum(ppld.tc_req_brdnd_cost)) as TC_REQ_BRDND_COST,

decode(sum(ppld.pc_req_brdnd_cost),0,to_number(NULL),sum(ppld.pc_req_brdnd_cost)) as PC_REQ_BRDND_COST,

decode(sum(ppld.pfc_req_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_req_brdnd_cost)) as PFC_REQ_BRDND_COST,

decode(sum(ppld.to_quantity),0,to_number(NULL),sum(ppld.to_quantity)) as TO_QUANTITY,

decode(sum(ppld.tc_to_raw_cost),0,to_number(NULL),sum(ppld.tc_to_raw_cost)) as TC_TO_RAW_COST,

decode(sum(ppld.pc_to_raw_cost),0,to_number(NULL),sum(ppld.pc_to_raw_cost)) as PC_TO_RAW_COST,

decode(sum(ppld.pfc_to_raw_cost),0,to_number(NULL),sum(ppld.pfc_to_raw_cost)) as PFC_TO_RAW_COST,

decode(sum(ppld.tc_to_brdnd_cost),0,to_number(NULL),sum(ppld.tc_to_brdnd_cost)) as TC_TO_BRDND_COST,

decode(sum(ppld.pc_to_brdnd_cost),0,to_number(NULL),sum(ppld.pc_to_brdnd_cost)) as PC_TO_BRDND_COST,

decode(sum(ppld.pfc_to_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_to_brdnd_cost)) as PFC_TO_BRDND_COST,

decode(sum(ppld.rcpt_quantity),0,to_number(NULL),sum(ppld.rcpt_quantity)) as RCPT_QUANTITY,

decode(sum(ppld.tc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.tc_rcpt_raw_cost)) as TC_RCPT_RAW_COST,

decode(sum(ppld.pc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.pc_rcpt_raw_cost)) as PC_RCPT_RAW_COST,

decode(sum(ppld.pfc_rcpt_raw_cost),0,to_number(NULL),sum(ppld.pfc_rcpt_raw_cost)) as PFC_RCPT_RAW_COST,

decode(sum(ppld.tc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.tc_rcpt_brdnd_cost)) as TC_RCPT_BRDND_COST,

decode(sum(ppld.pc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.pc_rcpt_brdnd_cost)) as PC_RCPT_BRDND_COST,

decode(sum(ppld.pfc_rcpt_brdnd_cost),0,to_number(NULL),sum(ppld.pfc_rcpt_brdnd_cost)) as PFC_RCPT_BRDND_COST,

Avg(ppld.TC_STD_BRDND_MULTIPLIER) as TC_STD_BRDND_MULTIPLIER,

Avg(ppld.TC_STD_BRDND_COST_RATE) as TC_STD_BRDND_COST_RATE,

Avg(ppld.TC_STD_RAW_COST_RATE) as TC_STD_RAW_COST_RATE,

Avg(ppld.TC_STD_BILL_RATE) as TC_STD_BILL_RATE

from pjo_plan_line_details ppld, pjo_dis_pd_period_all_v ppd

WHERE ppld.start_date(+) >= ppd.start_date

and ppld.end_date(+) <= ppd.end_date

and ppld.planning_element_id(+) = ppd.planning_element_id

and ppld.txn_currency_code(+) = ppd.txn_currency_code

group by ppd.period_name, ppd.start_period_name, ppd.end_period_name, ppd.start_date, ppd.end_date, ppd.from_anchor_start, ppd.planning_element_id, ppd.period_profile_id, ppd.current_period_name, ppd.project_id, ppd.object_id1, ppd.task_id, ppd.rbs_element_id, ppd.txn_currency_code, ppd.plan_Version_id, ppd.plan_type_id,ppd.PLANNING_START_DATE, ppd.PLANNING_END_DATE, ppd.UNIT_OF_MEASURE, ppd.MARGIN_DERIVED_FROM_CODE, ppd.PLAN_LINE_ID,ppd.spread_curve_id

order by ppd.start_date