PA Reporting Pack Data Definition
Description
- Categories
- BI Publisher
- Application
- Projects
- Source
- Short Name
- PROJ_MGR_DT
DB package:
SQL 378 lines
SELECT project_element_id task_id ,MAX(task_name) task_name ,MAX(task_number) task_number ,MAX(wbs_level) wbs_level ,MAX(wbs_number) task_outline_number ,MAX(task_display_sequence) task_display_sequence ,MAX(DECODE(wbs_level,1,'Y','N')) top_task_flag ,rbs_element_id rbs_element_id ,MAX(rbs_element_name) rbs_element_name ,MAX(rbs_outline_number) rbs_outline_number ,MAX(rbs_level) rbs_level ,MAX(resource_type_id ) resource_type_id ,SUM(acb_cur_mask*itd_mask*RAW_COST/factorBy) itd_acb_cur_raw_cost ,SUM(acb_cur_mask*itd_mask*BRDN_COST/factorBy) itd_acb_cur_brdn_cost ,SUM(acb_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_acb_cur_equip_brdn_cost ,SUM(acb_cur_mask*itd_mask*equipment_hours) itd_acb_cur_equip_hours ,SUM(acb_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_acb_cur_labor_brdn_cost ,SUM(acb_cur_mask*itd_mask*LABOR_HRS) itd_acb_cur_labor_hrs ,SUM(acb_cur_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_acb_cur_tot_hours ,SUM(acb_cur_mask*itd_mask*REVENUE/factorBy) itd_acb_cur_revenue ,SUM(acb_cur_mask*itd_mask*BILL_RAW_COST/factorBy) itd_acb_cur_bllbl_raw_cost ,SUM(acb_cur_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_acb_cur_bllbl_brdn_cost ,SUM(acb_cur_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_acb_cur_cap_raw_cost ,SUM(acb_cur_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_acb_cur_cap_brdn_cost ,SUM(acb_orig_mask*itd_mask*RAW_COST/factorBy) itd_acb_orig_raw_cost ,SUM(acb_orig_mask*itd_mask*BRDN_COST/factorBy) itd_acb_orig_brdn_cost ,SUM(acb_orig_mask*itd_mask*equipment_brdn_cost/factorBy) itd_acb_orig_equip_brdn_cost ,SUM(acb_orig_mask*itd_mask*equipment_hours) itd_acb_orig_equip_hours ,SUM(acb_orig_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_acb_orig_labor_brdn_cost ,SUM(acb_orig_mask*itd_mask*LABOR_HRS) itd_acb_orig_labor_hrs ,SUM(acb_orig_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_acb_orig_tot_hours ,SUM(acb_orig_mask*itd_mask*REVENUE/factorBy) itd_acb_orig_revenue ,SUM(acb_orig_mask*itd_mask*BILL_RAW_COST/factorBy) itd_acb_orig_bllbl_raw_cost ,SUM(acb_orig_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_acb_orig_bllbl_brdn_cost ,SUM(acb_orig_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_acb_orig_cap_raw_cost ,SUM(acb_orig_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_acb_orig_cap_brdn_cost ,SUM(arb_cur_mask*itd_mask*RAW_COST/factorBy) itd_arb_cur_raw_cost ,SUM(arb_cur_mask*itd_mask*BRDN_COST/factorBy) itd_arb_cur_brdn_cost ,SUM(arb_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_arb_cur_equip_brdn_cost ,SUM(arb_cur_mask*itd_mask*equipment_hours) itd_arb_cur_equip_hours ,SUM(arb_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_arb_cur_labor_brdn_cost ,SUM(arb_cur_mask*itd_mask*LABOR_HRS) itd_arb_cur_labor_hrs ,SUM(arb_cur_mask*itd_mask*REVENUE/factorBy) itd_arb_cur_revenue ,SUM(arb_orig_mask*itd_mask*RAW_COST/factorBy) itd_arb_orig_raw_cost ,SUM(arb_orig_mask*itd_mask*BRDN_COST/factorBy) itd_arb_orig_brdn_cost ,SUM(arb_orig_mask*itd_mask*equipment_brdn_cost/factorBy) itd_arb_orig_equip_brdn_cost ,SUM(arb_orig_mask*itd_mask*equipment_hours) itd_arb_orig_equip_hours ,SUM(arb_orig_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_arb_orig_labor_brdn_cost ,SUM(arb_orig_mask*itd_mask*LABOR_HRS) itd_arb_orig_labor_hrs ,SUM(arb_orig_mask*itd_mask*REVENUE/factorBy) itd_arb_orig_revenue ,SUM(pcf_cur_mask*itd_mask*RAW_COST/factorBy) itd_pcf_cur_raw_cost ,SUM(pcf_cur_mask*itd_mask*BRDN_COST/factorBy) itd_pcf_cur_brdn_cost ,SUM(pcf_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_pcf_cur_equip_brdn_cost ,SUM(pcf_cur_mask*itd_mask*equipment_hours) itd_pcf_cur_equip_hours ,SUM(pcf_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_pcf_cur_labor_brdn_cost ,SUM(pcf_cur_mask*itd_mask*LABOR_HRS) itd_pcf_cur_labor_hrs ,SUM(pcf_cur_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_pcf_cur_tot_hours ,SUM(pcf_cur_mask*itd_mask*REVENUE/factorBy) itd_pcf_cur_revenue ,SUM(pcf_cur_mask*itd_mask*BILL_RAW_COST/factorBy) itd_pcf_cur_bllbl_raw_cost ,SUM(pcf_cur_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_pcf_cur_bllbl_brdn_cost ,SUM(pcf_cur_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_pcf_cur_cap_raw_cost ,SUM(pcf_cur_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_pcf_cur_cap_brdn_cost ,SUM(pcf_prior_mask*itd_mask*RAW_COST/factorBy) itd_pcf_prior_raw_cost ,SUM(pcf_prior_mask*itd_mask*BRDN_COST/factorBy) itd_pcf_prior_brdn_cost ,SUM(pcf_prior_mask*itd_mask*equipment_brdn_cost/factorBy) itd_pcf_prior_equip_brdn_cost ,SUM(pcf_prior_mask*itd_mask*equipment_hours) itd_pcf_prior_equip_hours ,SUM(pcf_prior_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_pcf_prior_labor_brdn_cost ,SUM(pcf_prior_mask*itd_mask*LABOR_HRS) itd_pcf_prior_labor_hrs ,SUM(pcf_prior_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_pcf_prior_tot_hours ,SUM(pcf_prior_mask*itd_mask*REVENUE/factorBy) itd_pcf_prior_revenue ,SUM(pcf_prior_mask*itd_mask*BILL_RAW_COST/factorBy) itd_pcf_prior_bllbl_raw_cost ,SUM(pcf_prior_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_pcf_prior_bllbl_brdn_cost ,SUM(pcf_prior_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_pcf_prior_cap_raw_cost ,SUM(pcf_prior_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_pcf_prior_cap_brdn_cost ,SUM(prf_cur_mask*itd_mask*RAW_COST/factorBy) itd_prf_cur_raw_cost ,SUM(prf_cur_mask*itd_mask*BRDN_COST/factorBy) itd_prf_cur_brdn_cost ,SUM(prf_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_prf_cur_equip_brdn_cost ,SUM(prf_cur_mask*itd_mask*equipment_hours) itd_prf_cur_equip_hours ,SUM(prf_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_prf_cur_labor_brdn_cost ,SUM(prf_cur_mask*itd_mask*LABOR_HRS) itd_prf_cur_labor_hrs ,SUM(prf_cur_mask*itd_mask*REVENUE/factorBy) itd_prf_cur_revenue ,SUM(prf_prior_mask*itd_mask*RAW_COST/factorBy) itd_prf_prior_raw_cost ,SUM(prf_prior_mask*itd_mask*BRDN_COST/factorBy) itd_prf_prior_brdn_cost ,SUM(prf_prior_mask*itd_mask*equipment_brdn_cost/factorBy) itd_prf_prior_equip_brdn_cost ,SUM(prf_prior_mask*itd_mask*equipment_hours) itd_prf_prior_equip_hours ,SUM(prf_prior_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_prf_prior_labor_brdn_cost ,SUM(prf_prior_mask*itd_mask*LABOR_HRS) itd_prf_prior_labor_hrs ,SUM(prf_prior_mask*itd_mask*REVENUE/factorBy) itd_prf_prior_revenue ,SUM(cost_cur_mask*itd_mask*RAW_COST/factorBy) itd_cost_cur_raw_cost ,SUM(cost_cur_mask*itd_mask*BRDN_COST/factorBy) itd_cost_cur_brdn_cost ,SUM(cost_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_cost_cur_equip_brdn_cost ,SUM(cost_cur_mask*itd_mask*equipment_hours) itd_cost_cur_equip_hours ,SUM(cost_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_cost_cur_labor_brdn_cost ,SUM(cost_cur_mask*itd_mask*LABOR_HRS) itd_cost_cur_labor_hrs ,SUM(cost_cur_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_cost_cur_tot_hours ,SUM(cost_cur_mask*itd_mask*REVENUE/factorBy) itd_cost_cur_revenue ,SUM(cost_cur_mask*itd_mask*BILL_RAW_COST/factorBy) itd_cost_cur_bllbl_raw_cost ,SUM(cost_cur_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_cost_cur_bllbl_brdn_cost ,SUM(cost_cur_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_cost_cur_cap_raw_cost ,SUM(cost_cur_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_cost_cur_cap_brdn_cost ,SUM(cost_orig_mask*itd_mask*RAW_COST/factorBy) itd_cost_orig_raw_cost ,SUM(cost_orig_mask*itd_mask*BRDN_COST/factorBy) itd_cost_orig_brdn_cost ,SUM(cost_orig_mask*itd_mask*equipment_brdn_cost/factorBy) itd_cost_orig_equip_brdn_cost ,SUM(cost_orig_mask*itd_mask*equipment_hours) itd_cost_orig_equip_hours ,SUM(cost_orig_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_cost_orig_labor_brdn_cost ,SUM(cost_orig_mask*itd_mask*LABOR_HRS) itd_cost_orig_labor_hrs ,SUM(cost_orig_mask*itd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) itd_cost_orig_tot_hours ,SUM(cost_orig_mask*itd_mask*REVENUE/factorBy) itd_cost_orig_revenue ,SUM(cost_orig_mask*itd_mask*BILL_RAW_COST/factorBy) itd_cost_orig_bllbl_raw_cost ,SUM(cost_orig_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_cost_orig_bllbl_brdn_cost ,SUM(cost_orig_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_cost_orig_cap_raw_cost ,SUM(cost_orig_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_cost_orig_cap_brdn_cost ,SUM(rev_cur_mask*itd_mask*RAW_COST/factorBy) itd_rev_cur_raw_cost ,SUM(rev_cur_mask*itd_mask*BRDN_COST/factorBy) itd_rev_cur_brdn_cost ,SUM(rev_cur_mask*itd_mask*equipment_brdn_cost/factorBy) itd_rev_cur_equip_brdn_cost ,SUM(rev_cur_mask*itd_mask*equipment_hours) itd_rev_cur_equip_hours ,SUM(rev_cur_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_rev_cur_labor_brdn_cost ,SUM(rev_cur_mask*itd_mask*LABOR_HRS) itd_rev_cur_labor_hrs ,SUM(rev_cur_mask*itd_mask*REVENUE/factorBy) itd_rev_cur_revenue ,SUM(rev_orig_mask*itd_mask*RAW_COST/factorBy) itd_rev_orig_raw_cost ,SUM(rev_orig_mask*itd_mask*BRDN_COST/factorBy) itd_rev_orig_brdn_cost ,SUM(rev_orig_mask*itd_mask*equipment_brdn_cost/factorBy) itd_rev_orig_equip_brdn_cost ,SUM(rev_orig_mask*itd_mask*equipment_hours) itd_rev_orig_equip_hours ,SUM(rev_orig_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_rev_orig_labor_brdn_cost ,SUM(rev_orig_mask*itd_mask*LABOR_HRS) itd_rev_orig_labor_hrs ,SUM(rev_orig_mask*itd_mask*REVENUE/factorBy) itd_rev_orig_revenue ,SUM(act_mask*itd_mask*RAW_COST/factorBy) itd_act_raw_cost ,SUM(act_mask*itd_mask*BRDN_COST/factorBy) itd_act_brdn_cost ,SUM(act_mask*itd_mask*equipment_brdn_cost/factorBy) itd_act_equip_brdn_cost ,SUM(act_mask*itd_mask*equipment_hours) itd_act_equip_hours ,SUM(act_mask*itd_mask*LABOR_BRDN_COST/factorBy) itd_act_labor_brdn_cost ,SUM(act_mask*itd_mask*LABOR_HRS) itd_act_labor_hrs ,SUM(act_mask*itd_mask*(NVL(equipment_hours,0) + NVL(labor_hrs,0))) itd_act_tot_hours ,SUM(act_mask*itd_mask*REVENUE/factorBy) itd_act_revenue ,SUM(act_mask*itd_mask*BILL_RAW_COST/factorBy) itd_act_bllbl_raw_cost ,SUM(act_mask*itd_mask*BILL_BRDN_COST/factorBy) itd_act_bllbl_brdn_cost ,SUM(act_mask*itd_mask*CAPITALIZABLE_RAW_COST/factorBy) itd_act_cap_raw_cost ,SUM(act_mask*itd_mask*CAPITALIZABLE_BRDN_COST/factorBy) itd_act_cap_brdn_cost ,SUM(act_mask*itd_mask*COMMITTED_COST/factorBy) itd_total_commitment_cost ,SUM((arb_cur_mask*itd_mask*NVL(REVENUE,0) - acb_cur_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_ab_cur_mrgn ,DECODE(SUM(arb_cur_mask*itd_mask*REVENUE),0,NULL,SUM(arb_cur_mask*itd_mask*NVL(REVENUE,0) - acb_cur_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(arb_cur_mask*itd_mask*REVENUE)) itd_ab_cur_mrgn_pct ,SUM((arb_orig_mask*itd_mask*NVL(REVENUE,0) - acb_orig_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_ab_orig_mrgn ,DECODE(SUM(arb_orig_mask*itd_mask*REVENUE),0,NULL,SUM(arb_orig_mask*itd_mask*NVL(REVENUE,0) - acb_orig_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(arb_orig_mask*itd_mask*REVENUE)) itd_ab_orig_mrgn_pct ,SUM((prf_cur_mask*itd_mask*NVL(REVENUE,0) - pcf_cur_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_pf_cur_mrgn ,DECODE(SUM(prf_cur_mask*itd_mask*REVENUE),0,NULL,SUM(prf_cur_mask*itd_mask*NVL(REVENUE,0) - pcf_cur_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(prf_cur_mask*itd_mask*REVENUE)) itd_pf_cur_mrgn_pct ,SUM((prf_prior_mask*itd_mask*NVL(REVENUE,0) - pcf_prior_mask*itd_mask*NVL(BRDN_COST,0))/factorBy) itd_pf_prior_mrgn ,DECODE(SUM(prf_prior_mask*itd_mask*REVENUE),0,NULL,SUM(prf_prior_mask*itd_mask*NVL(REVENUE,0) - pcf_prior_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(prf_prior_mask*itd_mask*REVENUE)) itd_pf_prior_mrgn_pct ,SUM((rev_cur_mask*itd_mask*NVL(REVENUE,0) - cost_cur_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_Cmprsn_Plan_cur_mrgn ,DECODE(SUM(rev_cur_mask*itd_mask*REVENUE),0,NULL,SUM(rev_cur_mask*itd_mask*NVL(REVENUE,0) - cost_cur_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(rev_cur_mask*itd_mask*REVENUE)) itd_Cmprsn_Plan_cur_mrgn_pct ,SUM((rev_orig_mask*itd_mask*NVL(REVENUE,0) - cost_orig_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_cmprsn_plan_orig_mrgn ,DECODE(SUM(rev_orig_mask*itd_mask*REVENUE),0,NULL,SUM(rev_orig_mask*itd_mask*NVL(REVENUE,0) - cost_orig_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(rev_orig_mask*itd_mask*REVENUE)) itd_comprsn_plan_orig_mrgn_pct ,SUM((act_mask*itd_mask*NVL(REVENUE,0) - act_mask*itd_mask*NVL(BRDN_COST,0))/factorBy )itd_act_mrgn ,DECODE(SUM(act_mask*itd_mask*REVENUE),0,NULL,SUM(act_mask*itd_mask*NVL(REVENUE,0) - act_mask*itd_mask*NVL(BRDN_COST,0)) * 100/SUM(act_mask*itd_mask*REVENUE)) itd_act_mrgn_pct ,SUM(acb_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_acb_cur_raw_cost ,SUM(acb_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_acb_cur_brdn_cost ,SUM(acb_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_acb_cur_equip_brdn_cost ,SUM(acb_cur_mask*ptd_mask*equipment_hours) ptd_acb_cur_equip_hours ,SUM(acb_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_acb_cur_labor_brdn_cost ,SUM(acb_cur_mask*ptd_mask*LABOR_HRS) ptd_acb_cur_labor_hrs ,SUM(acb_cur_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_acb_cur_tot_hours ,SUM(acb_cur_mask*ptd_mask*REVENUE/factorBy) ptd_acb_cur_revenue ,SUM(acb_cur_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_acb_cur_bllbl_raw_cost ,SUM(acb_cur_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_acb_cur_bllbl_brdn_cost ,SUM(acb_cur_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_acb_cur_cap_raw_cost ,SUM(acb_cur_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_acb_cur_cap_brdn_cost ,SUM(acb_orig_mask*ptd_mask*RAW_COST/factorBy) ptd_acb_orig_raw_cost ,SUM(acb_orig_mask*ptd_mask*BRDN_COST/factorBy) ptd_acb_orig_brdn_cost ,SUM(acb_orig_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_acb_orig_equip_brdn_cost ,SUM(acb_orig_mask*ptd_mask*equipment_hours) ptd_acb_orig_equip_hours ,SUM(acb_orig_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_acb_orig_labor_brdn_cost ,SUM(acb_orig_mask*ptd_mask*LABOR_HRS) ptd_acb_orig_labor_hrs ,SUM(acb_orig_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_acb_orig_tot_hours ,SUM(acb_orig_mask*ptd_mask*REVENUE/factorBy) ptd_acb_orig_revenue ,SUM(acb_orig_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_acb_orig_bllbl_raw_cost ,SUM(acb_orig_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_acb_orig_bllbl_brdn_cost ,SUM(acb_orig_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_acb_orig_cap_raw_cost ,SUM(acb_orig_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_acb_orig_cap_brdn_cost ,SUM(arb_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_arb_cur_raw_cost ,SUM(arb_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_arb_cur_brdn_cost ,SUM(arb_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_arb_cur_equip_brdn_cost ,SUM(arb_cur_mask*ptd_mask*equipment_hours) ptd_arb_cur_equip_hours ,SUM(arb_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_arb_cur_labor_brdn_cost ,SUM(arb_cur_mask*ptd_mask*LABOR_HRS) ptd_arb_cur_labor_hrs ,SUM(arb_cur_mask*ptd_mask*REVENUE/factorBy) ptd_arb_cur_revenue ,SUM(arb_orig_mask*ptd_mask*RAW_COST/factorBy) ptd_arb_orig_raw_cost ,SUM(arb_orig_mask*ptd_mask*BRDN_COST/factorBy) ptd_arb_orig_brdn_cost ,SUM(arb_orig_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_arb_orig_equip_brdn_cost ,SUM(arb_orig_mask*ptd_mask*equipment_hours) ptd_arb_orig_equip_hours ,SUM(arb_orig_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_arb_orig_labor_brdn_cost ,SUM(arb_orig_mask*ptd_mask*LABOR_HRS) ptd_arb_orig_labor_hrs ,SUM(arb_orig_mask*ptd_mask*REVENUE/factorBy) ptd_arb_orig_revenue ,SUM(pcf_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_pcf_cur_raw_cost ,SUM(pcf_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_pcf_cur_brdn_cost ,SUM(pcf_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_pcf_cur_equip_brdn_cost ,SUM(pcf_cur_mask*ptd_mask*equipment_hours) ptd_pcf_cur_equip_hours ,SUM(pcf_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_pcf_cur_labor_brdn_cost ,SUM(pcf_cur_mask*ptd_mask*LABOR_HRS) ptd_pcf_cur_labor_hrs ,SUM(pcf_cur_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_pcf_cur_tot_hours ,SUM(pcf_cur_mask*ptd_mask*REVENUE/factorBy) ptd_pcf_cur_revenue ,SUM(pcf_cur_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_pcf_cur_bllbl_raw_cost ,SUM(pcf_cur_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_pcf_cur_bllbl_brdn_cost ,SUM(pcf_cur_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_pcf_cur_cap_raw_cost ,SUM(pcf_cur_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_pcf_cur_cap_brdn_cost ,SUM(pcf_prior_mask*ptd_mask*RAW_COST/factorBy) ptd_pcf_prior_raw_cost ,SUM(pcf_prior_mask*ptd_mask*BRDN_COST/factorBy) ptd_pcf_prior_brdn_cost ,SUM(pcf_prior_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_pcf_prior_equip_brdn_cost ,SUM(pcf_prior_mask*ptd_mask*equipment_hours) ptd_pcf_prior_equip_hours ,SUM(pcf_prior_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_pcf_prior_labor_brdn_cost ,SUM(pcf_prior_mask*ptd_mask*LABOR_HRS) ptd_pcf_prior_labor_hrs ,SUM(pcf_prior_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_pcf_prior_tot_hours ,SUM(pcf_prior_mask*ptd_mask*REVENUE/factorBy) ptd_pcf_prior_revenue ,SUM(pcf_prior_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_pcf_prior_bllbl_raw_cost ,SUM(pcf_prior_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_pcf_prior_bllbl_brdn_cost ,SUM(pcf_prior_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_pcf_prior_cap_raw_cost ,SUM(pcf_prior_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_pcf_prior_cap_brdn_cost ,SUM(prf_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_prf_cur_raw_cost ,SUM(prf_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_prf_cur_brdn_cost ,SUM(prf_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_prf_cur_equip_brdn_cost ,SUM(prf_cur_mask*ptd_mask*equipment_hours) ptd_prf_cur_equip_hours ,SUM(prf_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_prf_cur_labor_brdn_cost ,SUM(prf_cur_mask*ptd_mask*LABOR_HRS) ptd_prf_cur_labor_hrs ,SUM(prf_cur_mask*ptd_mask*REVENUE/factorBy) ptd_prf_cur_revenue ,SUM(prf_prior_mask*ptd_mask*RAW_COST/factorBy) ptd_prf_prior_raw_cost ,SUM(prf_prior_mask*ptd_mask*BRDN_COST/factorBy) ptd_prf_prior_brdn_cost ,SUM(prf_prior_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_prf_prior_equip_brdn_cost ,SUM(prf_prior_mask*ptd_mask*equipment_hours) ptd_prf_prior_equip_hours ,SUM(prf_prior_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_prf_prior_labor_brdn_cost ,SUM(prf_prior_mask*ptd_mask*LABOR_HRS) ptd_prf_prior_labor_hrs ,SUM(prf_prior_mask*ptd_mask*REVENUE/factorBy) ptd_prf_prior_revenue ,SUM(cost_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_cost_cur_raw_cost ,SUM(cost_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_cost_cur_brdn_cost ,SUM(cost_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_cost_cur_equip_brdn_cost ,SUM(cost_cur_mask*ptd_mask*equipment_hours) ptd_cost_cur_equip_hours ,SUM(cost_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_cost_cur_labor_brdn_cost ,SUM(cost_cur_mask*ptd_mask*LABOR_HRS) ptd_cost_cur_labor_hrs ,SUM(cost_cur_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_cost_cur_tot_hours ,SUM(cost_cur_mask*ptd_mask*REVENUE/factorBy) ptd_cost_cur_revenue ,SUM(cost_cur_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_cost_cur_bllbl_raw_cost ,SUM(cost_cur_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_cost_cur_bllbl_brdn_cost ,SUM(cost_cur_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_cost_cur_cap_raw_cost ,SUM(cost_cur_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_cost_cur_cap_brdn_cost ,SUM(cost_orig_mask*ptd_mask*RAW_COST/factorBy) ptd_cost_orig_raw_cost ,SUM(cost_orig_mask*ptd_mask*BRDN_COST/factorBy) ptd_cost_orig_brdn_cost ,SUM(cost_orig_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_cost_orig_equip_brdn_cost ,SUM(cost_orig_mask*ptd_mask*equipment_hours) ptd_cost_orig_equip_hours ,SUM(cost_orig_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_cost_orig_labor_brdn_cost ,SUM(cost_orig_mask*ptd_mask*LABOR_HRS) ptd_cost_orig_labor_hrs ,SUM(cost_orig_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(LABOR_HRS,0))) ptd_cost_orig_tot_hours ,SUM(cost_orig_mask*ptd_mask*REVENUE/factorBy) ptd_cost_orig_revenue ,SUM(cost_orig_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_cost_orig_bllbl_raw_cost ,SUM(cost_orig_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_cost_orig_bllbl_brdn_cost ,SUM(cost_orig_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_cost_orig_cap_raw_cost ,SUM(cost_orig_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_cost_orig_cap_brdn_cost ,SUM(rev_cur_mask*ptd_mask*RAW_COST/factorBy) ptd_rev_cur_raw_cost ,SUM(rev_cur_mask*ptd_mask*BRDN_COST/factorBy) ptd_rev_cur_brdn_cost ,SUM(rev_cur_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_rev_cur_equip_brdn_cost ,SUM(rev_cur_mask*ptd_mask*equipment_hours) ptd_rev_cur_equip_hours ,SUM(rev_cur_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_rev_cur_labor_brdn_cost ,SUM(rev_cur_mask*ptd_mask*LABOR_HRS) ptd_rev_cur_labor_hrs ,SUM(rev_cur_mask*ptd_mask*REVENUE/factorBy) ptd_rev_cur_revenue ,SUM(rev_orig_mask*ptd_mask*RAW_COST/factorBy) ptd_rev_orig_raw_cost ,SUM(rev_orig_mask*ptd_mask*BRDN_COST/factorBy) ptd_rev_orig_brdn_cost ,SUM(rev_orig_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_rev_orig_equip_brdn_cost ,SUM(rev_orig_mask*ptd_mask*equipment_hours) ptd_rev_orig_equip_hours ,SUM(rev_orig_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_rev_orig_labor_brdn_cost ,SUM(rev_orig_mask*ptd_mask*LABOR_HRS) ptd_rev_orig_labor_hrs ,SUM(rev_orig_mask*ptd_mask*REVENUE/factorBy) ptd_rev_orig_revenue ,SUM(act_mask*ptd_mask*RAW_COST/factorBy) ptd_act_raw_cost ,SUM(act_mask*ptd_mask*BRDN_COST/factorBy) ptd_act_brdn_cost ,SUM(act_mask*ptd_mask*equipment_brdn_cost/factorBy) ptd_act_equip_brdn_cost ,SUM(act_mask*ptd_mask*equipment_hours) ptd_act_equip_hours ,SUM(act_mask*ptd_mask*LABOR_BRDN_COST/factorBy) ptd_act_labor_brdn_cost ,SUM(act_mask*ptd_mask*LABOR_HRS) ptd_act_labor_hrs ,SUM(act_mask*ptd_mask*(NVL(equipment_hours,0) + NVL(labor_hrs,0))) ptd_act_tot_hours ,SUM(act_mask*ptd_mask*REVENUE/factorBy) ptd_act_revenue ,SUM(act_mask*ptd_mask*BILL_RAW_COST/factorBy) ptd_act_bllbl_raw_cost ,SUM(act_mask*ptd_mask*BILL_BRDN_COST/factorBy) ptd_act_bllbl_brdn_cost ,SUM(act_mask*ptd_mask*CAPITALIZABLE_RAW_COST/factorBy) ptd_act_cap_raw_cost ,SUM(act_mask*ptd_mask*CAPITALIZABLE_BRDN_COST/factorBy) ptd_act_cap_brdn_cost ,SUM(act_mask*ptd_mask*COMMITTED_COST/factorBy) ptd_total_commitment_cost ,SUM((arb_cur_mask*ptd_mask*NVL(REVENUE,0) - acb_cur_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_ab_cur_mrgn ,DECODE(SUM(arb_cur_mask*ptd_mask*REVENUE),0,NULL,SUM(arb_cur_mask*ptd_mask*NVL(REVENUE,0) - acb_cur_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(arb_cur_mask*ptd_mask*REVENUE)) ptd_ab_cur_mrgn_pct ,SUM((arb_orig_mask*ptd_mask*NVL(REVENUE,0) - acb_orig_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_ab_orig_mrgn ,DECODE(SUM(arb_orig_mask*ptd_mask*REVENUE),0,NULL,SUM(arb_orig_mask*ptd_mask*NVL(REVENUE,0) - acb_orig_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(arb_orig_mask*ptd_mask*REVENUE)) ptd_ab_orig_mrgn_pct ,SUM((prf_cur_mask*ptd_mask*NVL(REVENUE,0) - pcf_cur_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_pf_cur_mrgn ,DECODE(SUM(prf_cur_mask*ptd_mask*REVENUE),0,NULL,SUM(prf_cur_mask*ptd_mask*NVL(REVENUE,0) - pcf_cur_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(prf_cur_mask*ptd_mask*REVENUE)) ptd_pf_cur_mrgn_pct ,SUM((prf_prior_mask*ptd_mask*NVL(REVENUE,0) - pcf_prior_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy) ptd_pf_prior_mrgn ,DECODE(SUM(prf_prior_mask*ptd_mask*REVENUE),0,NULL,SUM(prf_prior_mask*ptd_mask*NVL(REVENUE,0) - pcf_prior_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(prf_prior_mask*ptd_mask*REVENUE)) ptd_pf_prior_mrgn_pct ,SUM((rev_cur_mask*ptd_mask*NVL(REVENUE,0) - cost_cur_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_Cmprsn_Plan_cur_mrgn ,DECODE(SUM(rev_cur_mask*ptd_mask*REVENUE),0,NULL,SUM(rev_cur_mask*ptd_mask*NVL(REVENUE,0) - cost_cur_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(rev_cur_mask*ptd_mask*REVENUE)) ptd_Cmprsn_Plan_cur_mrgn_pct ,SUM((rev_orig_mask*ptd_mask*NVL(REVENUE,0) - cost_orig_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_cmprsn_plan_orig_mrgn ,DECODE(SUM(rev_orig_mask*ptd_mask*REVENUE),0,NULL,SUM(rev_orig_mask*ptd_mask*NVL(REVENUE,0) - cost_orig_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(rev_orig_mask*ptd_mask*REVENUE)) ptd_comprsn_plan_orig_mrgn_pct ,SUM((act_mask*ptd_mask*NVL(REVENUE,0) - act_mask*ptd_mask*NVL(BRDN_COST,0))/factorBy )ptd_act_mrgn ,DECODE(SUM(act_mask*ptd_mask*REVENUE),0,NULL,SUM(act_mask*ptd_mask*NVL(REVENUE,0) - act_mask*ptd_mask*NVL(BRDN_COST,0)) * 100/SUM(act_mask*ptd_mask*REVENUE)) ptd_act_mrgn_pct FROM ( select :pFactorBy factorBy, fact.project_element_id, ppe.name task_name, DECODE(ppe.object_type,'PA_TASKS',ppe.element_number,ppa.segment1) task_number, NVL(ppv.wbs_level,0) wbs_level, ppv.wbs_number, NVL(ppv.display_sequence,0) task_display_sequence, fact.rbs_element_id rbs_element_id, rbs_elems.outline_number rbs_outline_number, rbs_names.resource_name rbs_element_name, rbs_elems.rbs_level rbs_level, rbs_elems.resource_type_id resource_type_id, RAW_COST, BRDN_COST, equipment_brdn_cost, equipment_hours, LABOR_BRDN_COST, LABOR_HRS, REVENUE, BILL_RAW_COST, BILL_BRDN_COST, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST, (NVL(SUP_INV_COMMITTED_COST,0) + NVL(PO_COMMITTED_COST,0) + NVL(PR_COMMITTED_COST,0) + NVL(OTH_COMMITTED_COST,0)) COMMITTED_COST, DECODE(fact.plan_version_id,:pApprCostBudgetCurrPVId,1,0) acb_cur_mask, DECODE(fact.plan_version_id,:pApprCostBudgetOrigPVId,1,0) acb_orig_mask, DECODE(fact.plan_version_id,:pApprRevBudgetCurrPVId,1,0) arb_cur_mask, DECODE(fact.plan_version_id,:pApprRevBudgetOrigPVId,1,0) arb_orig_mask, DECODE(fact.plan_version_id,:pPrimCostFcstCurrPVId,1,0) pcf_cur_mask, DECODE(fact.plan_version_id,:pPrimCostFcstPriorPVId,1,0) pcf_prior_mask, DECODE(fact.plan_version_id,:pPrimRevFcstCurrPVId,1,0) prf_cur_mask, DECODE(fact.plan_version_id,:pPrimRevFcstPriorPVId,1,0) prf_prior_mask, DECODE(fact.plan_version_id,:pCostCurrPVId,1,0) cost_cur_mask, DECODE(fact.plan_version_id,:pCostOrigPVId,1,0) cost_orig_mask, DECODE(fact.plan_version_id,:pRevCurrPVId,1,0) rev_cur_mask, DECODE(fact.plan_version_id,:pRevOrigPVId,1,0) rev_orig_mask, DECODE(fact.plan_version_id,:pActualVersionId,1,0) act_mask, SIGN(bitand(cal.record_type_id,256)) ptd_mask, SIGN(bitand(cal.record_type_id,1376)) itd_mask FROM pa_plan_actuals_fact_v fact,-- refactoring change pa_proj_elements ppe,pa_proj_element_versions ppv,pa_projects_all ppa ,pa_rbs_elements rbs_elems,pa_rbs_element_names_tl rbs_names,pji_time_cal_rpt_struct cal WHERE 1=1 and fact.pjt_rollup_flag=:pPjtRollupFlag AND fact.PROJECT_ID=:pProjectId AND (('Y'=:pPjtRollupFlag and( (fact.time_id=cal.time_id AND fact.calendar_type=:pCalendarType and 'A'<>:pCalendarType) OR (fact.time_id=-1 AND fact.calendar_type='G' AND cal.TIME_ID=-1 and 'A'=:pCalendarType) ) ) or ('N'=:pPjtRollupFlag AND fact.calendar_type=:pCalendarType AND fact.TIME_ID=cal.TIME_ID)) AND cal.report_date=:pReportDate AND cal.calendar_id=:pCalendarId AND (('Y'=:pPjtRollupFlag ) or ('N'=:pPjtRollupFlag and cal.period_type_id=fact.period_type_id AND fact.RBS_AGGR_LEVEL IN ('R','L') AND bitand(fact.CURR_RECORD_TYPE_ID,:pCurrRecordTypeId)=:pCurrRecordTypeId)) AND fact.PROJECT_ID=ppe.PROJECT_ID AND fact.PROJECT_ID=ppa.PROJECT_ID AND fact.PROJECT_ELEMENT_ID=ppe.proj_element_id AND fact.CURRENCY_CODE=:pCurrencyCode AND fact.prg_rollup_flag='N' AND fact.RBS_VERSION_ID=:pRBSVersionId AND fact.PLAN_VERSION_ID IN ( :pApprCostBudgetCurrPVId,:pApprCostBudgetOrigPVId,:pApprRevBudgetCurrPVId,:pApprRevBudgetOrigPVId, :pPrimCostFcstCurrPVId,:pPrimCostFcstPriorPVId,:pPrimRevFcstCurrPVId,:pPrimRevFcstPriorPVId, :pCostCurrPVId,:pCostOrigPVId,:pRevCurrPVId,:pRevOrigPVId ,:pActualVersionId) AND fact.PLAN_TYPE_ID IN (:pApprCostBudgetPTId,:pApprRevBudgetPTId,:pPrimCostFcstPTId,:pPrimRevFcstPTId, :pCostPTId,:pRevPTId ,:pActualPTId) AND ppv.project_id= ppe.project_id AND ppv.parent_structure_version_id=:pFinStrucVerId AND ppv.proj_element_id=ppe.proj_element_id AND rbs_elems.RBS_version_id=fact.RBS_VERSION_ID AND rbs_elems.rbs_element_id=fact.rbs_element_id AND rbs_elems.USER_CREATED_FLAG='N' AND rbs_elems.rbs_element_name_id=rbs_names.rbs_element_name_id AND rbs_names.LANGUAGE=USERENV('LANG') ) GROUP BY project_element_id,rbs_element_id ORDER BY task_display_sequence |