PA Reporting Pack Data Definition

Description
Categories: BI Publisher
Application: Projects
Source:
Short Name: PROJ_MGR_DT
DB package:
Run PA Reporting Pack Data Definition and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™