PJO_DIS_PD_BASE_DTL

Details

Schema: FUSION
Object owner: PJO
Object type: VIEW

Columns

Name
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 MARGIN_DERIVED_FROM_CODE 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 TC_MARGIN_PCT PC_MARGIN_PCT PFC_MARGIN_PCT TC_BILL_RATE TC_RAW_COST_RATE TC_BRDND_COST_RATE TC_ACT_RAW_COST_RATE TC_ACT_BRDND_COST_RATE TC_ACT_BILL_RATE

Name

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

MARGIN_DERIVED_FROM_CODE

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

TC_MARGIN_PCT

PC_MARGIN_PCT

PFC_MARGIN_PCT

TC_BILL_RATE

TC_RAW_COST_RATE

TC_BRDND_COST_RATE

TC_ACT_RAW_COST_RATE

TC_ACT_BRDND_COST_RATE

TC_ACT_BILL_RATE

Query

SQL_Statement
select ppd.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, 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(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)))) as TC_MARGIN_PCT, (decode(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)))) as PC_MARGIN_PCT, (decode(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)))) as PFC_MARGIN_PCT, (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.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 from pjo_plan_line_details ppld, pjo_dis_pd_period_all_v ppd, pjo_plan_versions_b pvr 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 and ppd.plan_Version_id = pvr.plan_Version_id and pvr.PLAN_STATUS_CODE = 'B' and pvr.current_plan_status_flag = 'Y' group by ppd.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 order by ppd.start_date

SQL_Statement

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

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(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.tc_revenue),0,to_number(NULL),sum(ppld.tc_revenue)))) as TC_MARGIN_PCT,

(decode(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.pc_revenue),0,to_number(NULL),sum(ppld.pc_revenue)))) as PC_MARGIN_PCT,

(decode(nvl(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))), 0),0, to_number(NULL), 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)))) / decode(nvl(decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)), 0),0, to_number(NULL), decode(sum(ppld.pfc_revenue),0,to_number(NULL),sum(ppld.pfc_revenue)))) as PFC_MARGIN_PCT,

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

from pjo_plan_line_details ppld, pjo_dis_pd_period_all_v ppd, pjo_plan_versions_b pvr

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

and ppd.plan_Version_id = pvr.plan_Version_id

and pvr.PLAN_STATUS_CODE = 'B'

and pvr.current_plan_status_flag = 'Y'

group by ppd.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

order by ppd.start_date