ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Slow Performance On Project Properties Opening

book

Article ID: 237401

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

When clicking or landing on the modern or classic project properties tab, it is taking a long time to load the content.

 

Cause

In one case study, the following query takes a long time even when running directly against the database:

select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_projects.RCF_ORG_CULTURE as rcf_org_culture, odf_ca_project.agile_sync_log as agile_sync_log, fin_financials.FORECAST_CST_TOTAL as forecast_cst_total, PAC_MNT_PROJECTS.APPROVED as approved, inv_investments.RCF_HUMAN_INTERFACE as inv_rcf_human_intrf, fin_financials.FORECAST_BREAKEVEN as forecast_breakeven, inv_investments.code COLLATE "en-x-icu"  as unique_code, PAC_MNT_PROJECTS.TRANSRATESOURCEMATERIALS as trans_rate_mtl_src, fin_financials.BUDGET_NPV as budget_npv, PAC_MNT_PROJECTS.BILLING_CURRENCY_CODE COLLATE "en-x-icu"  as bill_currency_code, inv_investments.RCF_SPONSORSHIP as inv_rcf_sponsorship, fin_financials.total_cost_of_capital as override_val_for_tcc, odf_ca_project.obj_est_factor1 as obj_est_factor1, odf_ca_project.obj_est_factor2 as obj_est_factor2, fin_financials.BUDGET_CST_FINISH as budget_cst_finish, fin_financials.BUDGET_MIRR as budget_mirr, EVREC.ITD_BCWS as ev_bcws, EVREC.ITD_BCWP as ev_bcwp, ODF_CA_INV.odf_object_code COLLATE "en-x-icu"  as investment_type, inv_investments.labor_etc_curve as labor_etc, PAC_MNT_PROJECTS.AFFILIATEPROJECT COLLATE "en-x-icu"  as affiliate_project, PAC_MNT_PROJECTS.MASTER_PROJECT_CODE COLLATE "en-x-icu"  as master_project_code, UPDATED.FULL_NAME COLLATE "en-x-icu"  as updated_by, inv_investments.STAGE_CODE COLLATE "en-x-icu"  as stage_code, odf_ca_inv.obj_align_factor2 as obj_align_factor2, odf_ca_inv.obj_align_factor1 as obj_align_factor1, PAC_MNT_PROJECTS.MATERIALS_EXCHANGE_RATE_TYPE COLLATE "en-x-icu"  as mtl_xchg_rate_type, UPDATED.ID as updated_by_id, odf_ca_inv.obj_align_factor4 as obj_align_factor4, odf_ca_inv.obj_align_factor3 as obj_align_factor3, odf_ca_inv.obj_align_factor6 as obj_align_factor6, odf_ca_inv.obj_align_factor5 as obj_align_factor5, odf_ca_project.av_wbs_last_sync as av_wbs_last_sync, (COALESCE(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as ttl_effort, fin_financials.BUDGET_REV_FINISH as budget_rev_finish, inv_projects.ev_bcwp_ovr as ev_bcwp_ovr, fin_financials.FORECAST_ROI as forecast_roi, odf_ca_project.obj_request_category COLLATE "en-x-icu"  as obj_request_category, ( SELECT CASE CMN_PAGES.PRINCIPAL_TYPE WHEN 'SYSTEM' THEN CMN_PAGES.ID ELSE
          COALESCE(CMN_PAGES.ORIGINATING_PAGE_ID,CMN_PAGES.ID) END
          FROM CMN_INSTANCE_PAGES, CMN_PAGES
          WHERE CMN_INSTANCE_PAGES.PAGE_FRAME_ID = CMN_PAGES.ID
          AND CMN_INSTANCE_PAGES.OBJECT_INSTANCE_ID = INV_INVESTMENTS.ID
          AND CMN_INSTANCE_PAGES.OBJECT_TYPE = 'SRM_PROJECTS' ) as page_layout, odf_ca_inv.stnx_fiscalyr COLLATE "en-x-icu"  as stnx_fiscalyr, inv_investments.labor_variance_curve as labor_variance, fin_financials.PLANNED_CST_TOTAL as planned_cst_total, PAC_MNT_PROJECTS.BILLCYCLE COLLATE "en-x-icu"  as batch_cycle, (case when inv_investments.schedule_start is not null and inv_investments.schedule_finish is not null THEN
        (COALESCE(INV_INVESTMENTS.LABOR_EACSUM,0) / 3600 / PRJ_HPD_FACTOR_FCT())
    ELSE 0 end) as labor_eacsum, PAC_MNT_PROJECTS.TRANSCOSTSOURCEEXPENSES as trans_cost_exp_src, EVREC.BAC as ev_bac, inv_investments.RCF_IMPLEMENTATION as inv_rcf_impl, odf_ca_inv.stx_cost as stx_cost, odf_ca_project.stnx_available as stnx_available, fin_financials.PLANNED_ROI as planned_roi, fin_financials.PLANNED_BEN_FINISH as planned_ben_finish, fin_financials.PLANNED_COST_OPERATING as plan_cost_op, inv_investments.GOAL_CODE COLLATE "en-x-icu"  as goal_code, NULL::TEXT  COLLATE "en-x-icu"  as stnx_promo, odf_ca_inv.intl_costestimate as intl_costestimate, BASEREC.COST_SUM as baseline_cost, inv_investments.schedule_start as schedule_start, NULL::TEXT  COLLATE "en-x-icu"  as stnx_products, odf_ca_project.stnx_promotehow as stnx_promotehow, PAC_MNT_PROJECTS.PROJECT_CODE COLLATE "en-x-icu"  as project_code, inv_investments.ASSGN_POOL as assgn_pool, CREATED.FULL_NAME COLLATE "en-x-icu"  as created_by, (SELECT count(id) FROM cmn_lookups_v WHERE parent_lookup_code = INV_INVESTMENTS.process_code AND is_active = 1 AND language_code = 'en') as stage_count, odf_ca_project.sf_cmpn COLLATE "en-x-icu"  as sf_cmpn, odf_ca_inv.intl_char_depend COLLATE "en-x-icu"  as intl_char_depend, (CASE WHEN (CASE WHEN (odf_ca_inv.obj_align_factor1*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor2*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor3*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor4*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor5*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor6*1.0) IS NOT NULL THEN 1 ELSE 0 END)=0 THEN NULL ELSE (COALESCE((odf_ca_inv.obj_align_factor1*1.0),0)+COALESCE((odf_ca_inv.obj_align_factor2*1.0),0)+COALESCE((odf_ca_inv.obj_align_factor3*1.0),0)+COALESCE((odf_ca_inv.obj_align_factor4*1.0),0)+COALESCE((odf_ca_inv.obj_align_factor5*1.0),0)+COALESCE((odf_ca_inv.obj_align_factor6*1.0),0))/(CASE WHEN (odf_ca_inv.obj_align_factor1*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor2*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor3*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor4*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor5*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (odf_ca_inv.obj_align_factor6*1.0) IS NOT NULL THEN 1 ELSE 0 END) END ) as obj_alignment, inv_investments.ALIGNMENT as alignment, COALESCE((SELECT count(ID) FROM cmn_lookups_v WHERE parent_lookup_code = INV_INVESTMENTS.process_code AND language_code = 'en' AND IS_ACTIVE = 1 AND SORT_ORDER <= (select sort_order from cmn_lookups_v WHERE lookup_code = INV_INVESTMENTS.stage_code AND lookup_type='INV_STAGE_TYPE' AND language_code = 'en')),0) as stage_number, odf_ca_inv.stx_spendtype as stx_spendtype, odf_ca_inv.intl_port_owner as intl_port_owner, ((CASE WHEN (EVREC.BAC*1.00)=0 THEN NULL ELSE (((EVREC.ETC*1.00)+(EVREC.ITD_ACWP*1.00))-(EVREC.BAC*1.00))/(EVREC.BAC*1.00) END )*(100.0*1.00)) as obj_cost_pct_var, inv_projects.prDepartment COLLATE "en-x-icu"  as prdepartment, fin_financials.BUDGET_REV_START as budget_rev_start, odf_ca_project.obj_methodology as obj_methodology, EVREC.ITD_ACWP as ev_acwp, PAC_MNT_PROJECTS.LOCATIONID COLLATE "en-x-icu"  as location_code, PAC_MNT_PROJECTS.TRANSCOSTSOURCEEQUIPMENT as trans_cost_eqp_src, odf_ca_project.agile_release_id COLLATE "en-x-icu"  as agile_release_id, inv_investments.RSF_OBS_UNIT as rsf_obs_unit, inv_investments.RCF_TECHNICAL as inv_rcf_technical, odf_ca_inv.obj_int_mappings COLLATE "en-x-icu"  as obj_int_mappings, fin_financials.BUDGET_BENEFIT as budget_benefit, ((CASE WHEN (EVREC.ITD_BCWS*1.00)=0 THEN NULL ELSE ((EVREC.ITD_BCWP*1.00)-(EVREC.ITD_BCWS*1.00))/(EVREC.ITD_BCWS*1.00) END )*(100.0*1.00)) as obj_ev_sv_pct, fin_financials.FORECAST_REV_TOTAL as forecast_rev_total, inv_projects.percent_complete as percent_complete, odf_ca_inv.intl_char_del COLLATE "en-x-icu"  as intl_char_del, inv_investments.ODF_BLUEPRINT_ID as odf_blueprint_id, PAC_MNT_PROJECTS.STATUS COLLATE "en-x-icu"  as npio_status, inv_investments.ODF_TEMPLATE as odf_template, odf_ca_project.stnx_apprneeded as stnx_apprneeded, (SELECT
            CASE WHEN COALESCE(F.BUDGET_CST_TOTAL,0) > 0
              THEN COALESCE(F.BUDGET_CST_OPERATING_TOTAL,0) / F.BUDGET_CST_TOTAL
              ELSE NULL END
        FROM FIN_FINANCIALS F 
        WHERE F.ID = FIN_FINANCIALS.ID) as budget_cst_operating_pct, (CASE WHEN INV_INVESTMENTS.STATUS = 1 THEN 1 WHEN INV_INVESTMENTS.STATUS = 5 THEN 1 WHEN INV_INVESTMENTS.STATUS = 8 THEN 1 ELSE 0 END) as is_approved, odf_ca_project.partition_code COLLATE "en-x-icu"  as partition_code, (COALESCE(INV_INVESTMENTS.LABOR_BASESUM,0) / 3600 / PRJ_HPD_FACTOR_FCT()) as labor_basesum, inv_investments.APPROVEDBY_ID as approvedby_id, odf_ca_inv.intl_char_ba as intl_char_ba, inv_investments.RCF_FLEXIBILITY as inv_rcf_flexibility, inv_projects.prSponsoredBy COLLATE "en-x-icu"  as prsponsoredby, fin_financials.FORECAST_COST as forecast_cost, odf_ca_project.intl_newuiduedate as intl_newuiduedate, inv_projects.prFormat as prformat, (COALESCE(((SELECT COALESCE(SUM (b.totalcost), 0) FROM ppa_wip a, ppa_wip_values b
         WHERE a.transno = b.transno AND b.currency_type = 'HOME'
        AND a.status = 0
        AND a.investment_id = inv_investments.id GROUP BY a.investment_id)*1),0)) as intl_wip_projectcost, inv_projects.PROCESS_SCALE_BDGT as process_scale_bdgt, fin_financials.FORECAST_REV_FINISH as forecast_rev_finish, inv_projects.RCF_INTERDEPENDENCY as rcf_interdependency, (case when inv_investments.schedule_start is not null and inv_investments.schedule_finish is not null THEN
        (COALESCE(INV_INVESTMENTS.LABOR_ACTSUM,0) / 3600 / PRJ_HPD_FACTOR_FCT())
    ELSE 0 end) as labor_actsum, inv_projects.prBudget as prbudget, odf_ca_project.agile_linked as agile_linked, (case when inv_investments.schedule_start is not null and inv_investments.schedule_finish is not null THEN
        (COALESCE(INV_INVESTMENTS.LABOR_ETCSUM,0) / 3600 / PRJ_HPD_FACTOR_FCT())
    ELSE 0 end) as labor_etcsum, fin_financials.BUDGET_COST as budget_cost, PAC_MNT_PROJECTS.resource_code COLLATE "en-x-icu"  as resource_code, NULL::TEXT  COLLATE "en-x-icu"  as stnx_mrktinitiative, (SELECT e.ID FROM ENTITY e WHERE e.ENTITY = INV_INVESTMENTS.ENTITY_CODE) as entity_id, inv_projects.prGuidelines COLLATE "en-x-icu"  as prguidelines, odf_ca_project.agile_project_id COLLATE "en-x-icu"  as agile_project_id, odf_ca_inv.obj_pfl_category1 COLLATE "en-x-icu"  as obj_pfl_category1, odf_ca_inv.obj_pfl_category2 COLLATE "en-x-icu"  as obj_pfl_category2, inv_investments.labor_act_curve as labor_act, odf_ca_inv.obj_pfl_category3 COLLATE "en-x-icu"  as obj_pfl_category3, odf_ca_inv.obj_pfl_category4 COLLATE "en-x-icu"  as obj_pfl_category4, ( select count('x') 
          from INV_INVESTMENTS bpInv, INV_PROJECTS ivp
          where INV_INVESTMENTS.ODF_OBJECT_CODE = 'project'
          and INV_INVESTMENTS.id = ivp.prid 
          and ivp.is_template = 1 
          and bpInv.odf_template_id = INV_INVESTMENTS.id ) as odf_invs_using_tmplt, odf_ca_project.sync_status COLLATE "en-x-icu"  as sync_status, fin_financials.PLANNED_BEN_TOTAL as planned_ben_total, odf_ca_project.sync_detail_inform COLLATE "en-x-icu"  as sync_detail_inform, inv_investments.STATUS as status, inv_projects.ev_calc_method as ev_calc_method, fin_financials.BUDGET_BREAKEVEN as budget_breakeven, odf_ca_inv.intl_bus_ben COLLATE "en-x-icu"  as intl_bus_ben, inv_projects.prClosedTime as prclosedtime, inv_projects.prUID COLLATE "en-x-icu"  as pruid, inv_projects.RCF_SPONSORSHIP as rcf_sponsorship, odf_ca_project.stnx_campaignaccess as stnx_campaignaccess, fin_financials.PLANNED_NPV as planned_npv, PAC_MNT_PROJECTS.COMPANY_CODE COLLATE "en-x-icu"  as company_code, inv_projects.prStartImposed as prstartimposed, inv_investments.labor_baseline_curve as labor_base, inv_investments.IS_ACTIVE as is_active, PAC_MNT_PROJECTS.TRANSCOSTSOURCELABOR as trans_cost_lbr_src, fin_financials.PLANNED_CST_START as planned_cst_start, CASE WHEN COALESCE((SELECT PRLOCK.PRUSERID FROM PRLOCK WHERE PRLOCK.PRRECORDID = INV_INVESTMENTS.ID AND PRLOCK.PRNAME = 'prImportExport' AND PRLOCK.PRTABLENAME = 'SRM_PROJECTS'), 0) != 0
             THEN (SELECT CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) FROM CMN_SEC_USERS WHERE ID = (SELECT PRLOCK.PRUSERID FROM PRLOCK WHERE PRLOCK.PRRECORDID = INV_INVESTMENTS.ID AND PRLOCK.PRNAME = 'prImportExport' AND PRLOCK.PRTABLENAME = 'SRM_PROJECTS'))
             ELSE NULL END COLLATE "en-x-icu"  as locked_by, inv_investments.RCF_ORG_CULTURE as inv_rcf_org_culture, (COALESCE(EVREC.ITD_BCWP,0) - COALESCE(EVREC.ITD_BCWS,0)) as ev_sv, (SELECT
            CASE WHEN COALESCE(F.PLANNED_CST_TOTAL,0) > 0
              THEN COALESCE(F.PLANNED_CST_CAPITAL_TOTAL,0) / F.PLANNED_CST_TOTAL
              ELSE NULL END
        FROM FIN_FINANCIALS F 
        WHERE F.ID = FIN_FINANCIALS.ID) as planned_cst_capital_pct, inv_investments.entity_code COLLATE "en-x-icu"  as entity, BASEREC.FINISH_DATE as baseline_finish, ((CASE WHEN (EVREC.ITD_BCWP*1.00)=0 THEN NULL ELSE ((EVREC.ITD_BCWP*1.00)-(EVREC.ITD_ACWP*1.00))/(EVREC.ITD_BCWP*1.00) END )*(100.0*1.00)) as obj_ev_cv_pct, inv_investments.ODF_TEMPLATE_ID as odf_template_id, fin_financials.FORECAST_PAYBACK_PERIOD as forecast_payback_period, (COALESCE(INV_INVESTMENTS.labor_etcsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as etc, PAC_MNT_PROJECTS.CLASS COLLATE "en-x-icu"  as investment_class, (EVREC.ITD_ACWP + EVREC.ETC) as ev_eac_t, ((inv_projects.percent_complete*1)*(100.0*1)) as intl_pct, BASEREC.LAST_UPDATED_DATE as baseline_time, PAC_MNT_PROJECTS.LABOR_EXCHANGE_RATE_TYPE COLLATE "en-x-icu"  as lbr_xchg_rate_type, odf_ca_inv.intl_sys_replace as intl_sys_replace, inv_investments.IDEA_ID as idea_id, inv_investments.CI_PROGRESS as ci_progress, odf_ca_project.agile_project_name COLLATE "en-x-icu"  as agile_project_name, inv_investments.RCF_SUPPORTABILITY as inv_rcf_support, CREATED.ID as created_by_id, case when exists( select 1 from odf_object_favorites where object_code='project' and object_id=inv_investments.id and user_id = 5008002) THEN 1 ELSE  0 END as odf_is_favorite, (CASE WHEN COALESCE(EVREC.ITD_ACWP,0) > 0
            AND COALESCE(EVREC.ITD_BCWP,0) > 0
            THEN EVREC.ITD_ACWP + ( ( (EVREC.BAC - EVREC.ITD_BCWP) * EVREC.ITD_ACWP ) / ( EVREC.ITD_BCWP ))
        ELSE NULL END) as ev_eac, inv_projects.prVersion as prversion, fin_financials.BUDGET_REV_TOTAL as budget_rev_total, inv_projects.REQ_APPROVAL_REQD as req_approval_reqd, inv_projects.RCF_FLEXIBILITY as rcf_flexibility, (SELECT COALESCE(SUM (b.totalcost), 0) FROM ppa_wip a, ppa_wip_values b
         WHERE a.transno = b.transno AND b.currency_type = 'HOME'
        AND a.status = 0
        AND a.investment_id = inv_investments.id GROUP BY a.investment_id) as aggr_actual_cost, inv_projects.EV_PERIOD_ID as ev_period_id, inv_investments.APPROVEDTIME as approvedtime, fin_financials.Initial_Investment as initial_investment, inv_investments.BASELINE_ID as baseline_id, inv_projects.prUserText1 COLLATE "en-x-icu"  as prusertext1, inv_projects.prUserText2 COLLATE "en-x-icu"  as prusertext2, fin_financials.reinvestment_rate as override_val_for_rr, inv_projects.prUserText3 COLLATE "en-x-icu"  as prusertext3, inv_projects.prUserText4 COLLATE "en-x-icu"  as prusertext4, inv_projects.prUserText5 COLLATE "en-x-icu"  as prusertext5, inv_projects.prUserText6 COLLATE "en-x-icu"  as prusertext6, (BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT() as baseline_usage, inv_projects.prUserText7 COLLATE "en-x-icu"  as prusertext7, inv_projects.RCF_SUPPORTABILITY as rcf_supportability, fin_financials.FORECAST_IRR as forecast_irr, EVREC.LAST_UPDATED_DATE as ev_last_calc, (((EVREC.ETC*1.00)+(EVREC.ITD_ACWP*1.00))-(EVREC.BAC*1.00)) as obj_cost_amt_var, inv_investments.chargecodeid as chargecodeid, odf_ca_project.agile_synced as agile_synced, inv_projects.RCF_TECHNICAL as rcf_technical, NULL::TEXT  COLLATE "en-x-icu"  as intl_bus_capb, ( select max(bpInv.created_date) 
          from INV_INVESTMENTS bpInv, INV_PROJECTS ivp
          where INV_INVESTMENTS.ODF_OBJECT_CODE = 'project'
          and INV_INVESTMENTS.id = ivp.prid 
          and ivp.is_template = 1 
          and bpInv.odf_template_id = INV_INVESTMENTS.id ) as odf_tmplt_last_used, odf_ca_inv.obj_int_ext_object COLLATE "en-x-icu"  as obj_int_ext_object, inv_investments.bill_expense_type COLLATE "en-x-icu"  as bill_expense_type, INV_INVESTMENTS.CREATED_DATE as created_date, inv_projects.prRequestedBy COLLATE "en-x-icu"  as prrequestedby, fin_financials.is_sys_val_for_tcc as is_sys_val_for_tcc, fin_financials.FORECAST_MIRR as forecast_mirr, EVREC.ETC as ev_etc, fin_financials.PLANNED_CST_OPERATING_TOTAL as planned_cst_operating_total, NULL::TEXT  COLLATE "en-x-icu"  as stnx_targetaud, fin_financials.FORECAST_BENEFIT as forecast_benefit, (CASE WHEN COALESCE(EVREC.ITD_ACWP,0) > 0
             THEN COALESCE(EVREC.ITD_BCWP,0) / EVREC.ITD_ACWP
             ELSE NULL END) as ev_cpi, odf_ca_project.obj_objective COLLATE "en-x-icu"  as obj_objective, inv_projects.prCPMType as prcpmtype, fin_financials.PLANNED_BEN_START as planned_ben_start, inv_investments.STATUS_INDICATOR as status_indicator, (CASE WHEN COALESCE(EVREC.ITD_BCWS,0) > 0
             THEN COALESCE(EVREC.ITD_BCWP,0) / EVREC.ITD_BCWS
             ELSE NULL END) as ev_spi, ( (select COALESCE(t.intl_agg_trend,0) from ( select inv_investments.id,  ROUND(SUM(t.intl_agg_trend),0) intl_agg_trend from inv_investments left outer join ( select (odf_ca_cop_prj_statusrpt.odf_parent_id) odf_parent_id, (odf_ca_cop_prj_statusrpt.intl_future_trend*1) intl_agg_trend from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id  where 1=1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1 and  ( 1 = 1 )  and  ( ODF_CA_COP_PRJ_STATUSRPT.ID in (select qual.ID from (select ODF_CA_COP_PRJ_STATUSRPT.ID, qtab.odf_parent_id, row_number() over (partition by qtab.odf_parent_id order by qtab.ID desc) as row_num  from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id , (select ODF_CA_COP_PRJ_STATUSRPT.ID, odf_ca_cop_prj_statusrpt.odf_parent_id, target.cop_report_date from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id , (select qrows.odf_parent_id, max(qrows.cop_report_date) cop_report_date from ( select * from (select ODF_CA_COP_PRJ_STATUSRPT.ID, odf_ca_cop_prj_statusrpt.odf_parent_id, cmn_date_trunc_fct(odf_ca_cop_prj_statusrpt.cop_report_date) cop_report_date from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id  where  ( 1 = 1 )  and (1 = 1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1)) cond_table) qrows group by odf_parent_id) target  where odf_ca_cop_prj_statusrpt.odf_parent_id = target.odf_parent_id and cmn_date_trunc_fct(odf_ca_cop_prj_statusrpt.cop_report_date) = target.cop_report_date and  ( 1 = 1 )  and (1 = 1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1)) qtab  where ODF_CA_COP_PRJ_STATUSRPT.ID = qtab.ID and odf_ca_cop_prj_statusrpt.odf_parent_id=qtab.odf_parent_id  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1) qual  where qual.row_num = 1 ))) t on inv_investments.id =  t.odf_parent_id group by inv_investments.id) t  where t.id = inv_investments.id) ) as intl_agg_trend, fin_financials.PLANNED_COST_CAPITAL as plan_cost_cap, fin_financials.BUDGET_ACTUAL_BENEFIT as budget_actual_benefit, PAC_MNT_PROJECTS.BILL_TO_COMPANY_CODE COLLATE "en-x-icu"  as bill_to_id, inv_projects.RCF_OBJECTIVES as rcf_objectives, (CMN_DATE_TIME_DIFF_FCT('Day',inv_investments.schedule_finish,BASEREC.FINISH_DATE)) as obj_schedule_var, (SELECT bp.NAME FROM INV_INVESTMENTS bp WHERE bp.ID = INV_INVESTMENTS.ODF_TEMPLATE_ID) COLLATE "en-x-icu"  as odf_template_name, odf_ca_inv.stnx_planned as stnx_planned, inv_projects.prRequestedTime as prrequestedtime, odf_ca_inv.odf_period_type COLLATE "en-x-icu"  as odf_period_type, fin_financials.BUDGET_CST_CAPITAL_TOTAL as budget_cst_capital_total, ( select poa.unit_id::text from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot, prj_obs_object_types poot where  UPPER(poa.table_name) = UPPER('SRM_PROJECTS') and poa.record_id = INV_INVESTMENTS.ID and poa.unit_id = pou.id and pou.type_id = pot.id   and pot.id = poot.type_id and UPPER(poa.table_name) = UPPER(poot.table_name) and poot.assoc_att_code = 'odf_reso_979494317' ) COLLATE "en-x-icu"  as odf_reso_979494317, odf_ca_inv.intl_buildtype COLLATE "en-x-icu"  as intl_buildtype, fin_financials.IS_CALC_PV_INFO as is_calc_pv_info, PAC_MNT_PROJECTS.TRANSRATESOURCELABOR as trans_rate_lbr_src, odf_ca_inv.odf_period_start as odf_period_start, inv_investments.MANAGER_ID as manager_id, (COALESCE(INV_INVESTMENTS.labor_actsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) as actuals, INV_INVESTMENTS.MANAGER_ID as manager, fin_financials.PLANNED_CST_CAPITAL_TOTAL as planned_cst_capital_total, PAC_MNT_PROJECTS.TRANSRATESOURCEEQUIPMENT as trans_rate_eqp_src, inv_investments.STATUS_COMMENT COLLATE "en-x-icu"  as status_comment, fin_financials.PLANNED_COST as plan_cost, odf_ca_project.stx_audience_action COLLATE "en-x-icu"  as stx_audience_action, PAC_MNT_PROJECTS.WIPCLASS COLLATE "en-x-icu"  as wip_class, odf_ca_project.rmt_integration COLLATE "en-x-icu"  as rmt_integration, inv_investments.PROGRESS as progress, odf_ca_project.stnx_leadassignment as stnx_leadassignment, PAC_MNT_PROJECTS.DEPARTCODE COLLATE "en-x-icu"  as department_code, PAC_MNT_PROJECTS.TRANSRATESOURCEEXPENSES as trans_rate_exp_src, inv_projects.IS_TEMPLATE as is_template, inv_investments.schedule_finish as schedule_finish, ( select poa.unit_id::text from prj_obs_associations poa, prj_obs_units pou, prj_obs_types pot, entity ent where  UPPER(poa.table_name) = UPPER('SRM_PROJECTS') and poa.record_id = INV_INVESTMENTS.ID and poa.unit_id = pou.id and pou.type_id = pot.id  and pot.id = ent.org_chart_obs_type_id ) COLLATE "en-x-icu"  as odf_obs_fin_dept, inv_investments.RCF_FUNDING as inv_rcf_funding, fin_financials.BUDGET_IRR as budget_irr, inv_investments.SLA_COMPLIANCE as sla_compliance, inv_projects.IS_PROGRAM as is_program, inv_projects.RCF_HUMAN_INTERFACE as rcf_human_interface, inv_projects.RCF_RESOURCE_AVAIL as rcf_resource_avail, COALESCE(INV_INVESTMENTS.PURGE_FLAG,0) as purge_flag, odf_ca_inv.intl_char_outscope COLLATE "en-x-icu"  as intl_char_outscope, PAC_MNT_PROJECTS.EXPENSE_EXCHANGE_RATE_TYPE COLLATE "en-x-icu"  as exp_xchg_rate_type, inv_projects.RCF_FUNDING as rcf_funding, inv_projects.prFinishImposed as prfinishimposed, (CASE WHEN COALESCE(EVREC.BAC,0) > 0
          AND COALESCE(EVREC.ITD_BCWP,0) > 0
          AND COALESCE(EVREC.ITD_ACWP,0) > 0
          THEN ( ( EVREC.BAC - EVREC.ITD_BCWP ) * EVREC.ITD_ACWP )  / ( EVREC.ITD_BCWP )
        ELSE NULL END) as ev_etc_t, inv_investments.TARGET_MANAGER_ID as target_manager_id, inv_investments.RCF_INTERDEPENDENCY as inv_rcf_interdepend, inv_investments.labor_variancesum as labor_variancesum, inv_projects.prFiscalStart as prfiscalstart, inv_investments.parent_id as odf_assoc_parent, inv_investments.name COLLATE "en-x-icu"  as name, ( (select COALESCE(t.intl_agg_status,0) from ( select inv_investments.id,  ROUND(SUM(t.intl_agg_status),0) intl_agg_status from inv_investments left outer join ( select (odf_ca_cop_prj_statusrpt.odf_parent_id) odf_parent_id, (COALESCE((odf_ca_cop_prj_statusrpt.cop_schedule_status*1),0)+COALESCE((odf_ca_cop_prj_statusrpt.cop_scope_status*1),0)+COALESCE((odf_ca_cop_prj_statusrpt.cop_cost_eft_status*1),0)) intl_agg_status from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id  where 1=1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1 and  ( 1 = 1 )  and  ( ODF_CA_COP_PRJ_STATUSRPT.ID in (select qual.ID from (select ODF_CA_COP_PRJ_STATUSRPT.ID, qtab.odf_parent_id, row_number() over (partition by qtab.odf_parent_id order by qtab.ID desc) as row_num  from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id , (select ODF_CA_COP_PRJ_STATUSRPT.ID, odf_ca_cop_prj_statusrpt.odf_parent_id, target.cop_report_date from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id , (select qrows.odf_parent_id, max(qrows.cop_report_date) cop_report_date from ( select * from (select ODF_CA_COP_PRJ_STATUSRPT.ID, odf_ca_cop_prj_statusrpt.odf_parent_id, cmn_date_trunc_fct(odf_ca_cop_prj_statusrpt.cop_report_date) cop_report_date from odf_ca_cop_prj_statusrpt odf_ca_cop_prj_statusrpt, (select odf_inv.odf_pk,odf_inv.unique_code,odf_inv.manager_id,odf_inv.name,odf_project.entity,odf_project.odf_period_type,odf_project.odf_period_start,odf_project.odf_period_end,odf_project.status_indicator from (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.code as unique_code, inv_investments.MANAGER_ID as manager_id, inv_investments.name as name from inv_investments inv_investments where 1 = 1 ) odf_inv left outer join (select   'x' pmd_analytical_partition_by, inv_investments.id odf_pk, inv_investments.entity_code as entity, inv_investments.STATUS_INDICATOR as status_indicator, odf_ca_inv.odf_period_type as odf_period_type, odf_ca_inv.odf_period_start as odf_period_start, odf_ca_inv.odf_period_end as odf_period_end, oim2.secondary_object_instance_id odf_fin_financials_SK from inv_investments inv_investments, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim2, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS where 1 = 1  and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim2.primary_object_instance_id  and oim2.primary_object_instance_code = 'project' and fin_financials.id = oim2.secondary_object_instance_id  and oim2.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id) odf_project on odf_inv.odf_pk = odf_project.odf_pk )  odf_parent_id  where  ( 1 = 1 )  and (1 = 1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1)) cond_table) qrows group by odf_parent_id) target  where odf_ca_cop_prj_statusrpt.odf_parent_id = target.odf_parent_id and cmn_date_trunc_fct(odf_ca_cop_prj_statusrpt.cop_report_date) = target.cop_report_date and  ( 1 = 1 )  and (1 = 1  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1)) qtab  where ODF_CA_COP_PRJ_STATUSRPT.ID = qtab.ID and odf_ca_cop_prj_statusrpt.odf_parent_id=qtab.odf_parent_id  and odf_ca_cop_prj_statusrpt.odf_parent_id = odf_parent_id.odf_pk and 1 = 1) qual  where qual.row_num = 1 ))) t on inv_investments.id =  t.odf_parent_id group by inv_investments.id) t  where t.id = inv_investments.id) ) as intl_agg_status, odf_ca_inv.intl_char_customer COLLATE "en-x-icu"  as intl_char_customer, NULL::TEXT  COLLATE "en-x-icu"  as intl_res_team_needs, inv_investments.description COLLATE "en-x-icu"  as description, NULL::TEXT  COLLATE "en-x-icu"  as stnx_custsegm, inv_investments.CI_STAGE COLLATE "en-x-icu"  as ci_stage, fin_financials.PLANNED_PAYBACK_PERIOD as planned_payback_period, fin_financials.BUDGET_CST_TOTAL as budget_cst_total, inv_investments.SYNC_INV_N_BDG_DATES as sync_inv_n_bdg_dates, fin_financials.FORECAST_CST_START as forecast_cst_start, (select reinvestment_rate from prsite) as system_value_for_rr, ((COALESCE(INV_INVESTMENTS.LABOR_ETCSUM,0) + COALESCE(INV_INVESTMENTS.LABOR_ACTSUM,0)) / 3600 / PRJ_HPD_FACTOR_FCT()) as labor_effort, inv_investments.track_mode as trackmode, (CASE WHEN (CASE WHEN (inv_investments.RCF_FLEXIBILITY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_FUNDING*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_HUMAN_INTERFACE*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_IMPLEMENTATION*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_INTERDEPENDENCY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_OBJECTIVES*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_ORG_CULTURE*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_RESOURCE_AVAIL*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_SPONSORSHIP*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_SUPPORTABILITY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_TECHNICAL*1.0) IS NOT NULL THEN 1 ELSE 0 END)=0 THEN NULL ELSE (COALESCE((inv_investments.RCF_FLEXIBILITY*1.0),0)+COALESCE((inv_investments.RCF_FUNDING*1.0),0)+COALESCE((inv_investments.RCF_HUMAN_INTERFACE*1.0),0)+COALESCE((inv_investments.RCF_IMPLEMENTATION*1.0),0)+COALESCE((inv_investments.RCF_INTERDEPENDENCY*1.0),0)+COALESCE((inv_investments.RCF_OBJECTIVES*1.0),0)+COALESCE((inv_investments.RCF_ORG_CULTURE*1.0),0)+COALESCE((inv_investments.RCF_RESOURCE_AVAIL*1.0),0)+COALESCE((inv_investments.RCF_SPONSORSHIP*1.0),0)+COALESCE((inv_investments.RCF_SUPPORTABILITY*1.0),0)+COALESCE((inv_investments.RCF_TECHNICAL*1.0),0))/(CASE WHEN (inv_investments.RCF_FLEXIBILITY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_FUNDING*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_HUMAN_INTERFACE*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_IMPLEMENTATION*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_INTERDEPENDENCY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_OBJECTIVES*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_ORG_CULTURE*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_RESOURCE_AVAIL*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_SPONSORSHIP*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_SUPPORTABILITY*1.0) IS NOT NULL THEN 1 ELSE 0 END+CASE WHEN (inv_investments.RCF_TECHNICAL*1.0) IS NOT NULL THEN 1 ELSE 0 END) END ) as risk_score, odf_ca_inv.intl_exec_spons as intl_exec_spons, NULL::TEXT  COLLATE "en-x-icu"  as stnx_teamdep, odf_ca_project.intl_dataretention2 COLLATE "en-x-icu"  as intl_dataretention2, fin_financials.FORECAST_CST_FINISH as forecast_cst_finish, fin_financials.IS_FCST_EQ_BDGT as is_fcst_eq_bdgt, odf_ca_inv.odf_asr_template as odf_asr_template, (inv_projects.RCF_FLEXIBILITY * 1.000000 + inv_projects.RCF_FUNDING * 1.000000 + inv_projects.RCF_HUMAN_INTERFACE * 1.000000 + inv_projects.RCF_IMPLEMENTATION * 1.000000 + inv_projects.RCF_INTERDEPENDENCY * 1.000000 + inv_projects.RCF_OBJECTIVES * 1.000000 + inv_projects.RCF_ORG_CULTURE * 1.000000 + inv_projects.RCF_RESOURCE_AVAIL * 1.000000 + inv_projects.RCF_SPONSORSHIP * 1.000000 + inv_projects.RCF_SUPPORTABILITY * 1.000000 + inv_projects.RCF_TECHNICAL * 1.000000) / (1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000) as risk, fin_financials.PLANNED_ACTUAL_BENEFIT as plan_actual_benefit, odf_ca_financials.odf_entity_code COLLATE "en-x-icu"  as odf_entity_code, (SELECT ix.NAME FROM INV_INVESTMENTS ix WHERE ix.ID = INV_INVESTMENTS.IDEA_ID) COLLATE "en-x-icu"  as idea_name, (SELECT
            CASE WHEN COALESCE(F.BUDGET_CST_TOTAL,0) > 0
              THEN COALESCE(F.BUDGET_CST_CAPITAL_TOTAL,0) / F.BUDGET_CST_TOTAL
              ELSE NULL END
        FROM FIN_FINANCIALS F 
        WHERE F.ID = FIN_FINANCIALS.ID) as budget_cst_capital_pct, COALESCE(EVREC.ITD_BCWP,0) - COALESCE(EVREC.ITD_ACWP,0) as ev_cv, inv_projects.prAsOf as prasof, odf_ca_inv.stx_duration as stx_duration, inv_investments.IS_CUSTOM as is_custom, fin_financials.BUDGET_CST_OPERATING_TOTAL as budget_cst_operating_total, odf_ca_inv.intl_char_inscope COLLATE "en-x-icu"  as intl_char_inscope, PAC_MNT_PROJECTS.TRANSCOSTSOURCEMATERIALS as trans_cost_mtl_src, odf_ca_inv.odf_asr_reports as odf_asr_reports, fin_financials.BUDGET_COST_OPERATING as budget_cost_op, (CASE WHEN COALESCE(EVREC.BAC,0) > 0 AND COALESCE(EVREC.ITD_BCWP,0) > 0
        THEN EVREC.BAC - EVREC.ITD_BCWP
        ELSE NULL END) as ev_etc_at, inv_investments.priority as priority, odf_ca_inv.intl_char_sme COLLATE "en-x-icu"  as intl_char_sme, fin_financials.BUDGET_ROI as budget_roi, BASEREC.START_DATE as baseline_start, COALESCE(inv_investments.odf_blueprint_id, (select id from odf_blueprints b where b.type = UPPER(inv_investments.odf_object_code) and b.is_default = 1)) as odf_blp_active_id, odf_ca_project.obj_status_reporting COLLATE "en-x-icu"  as obj_status_reporting, fin_financials.PLANNED_BENEFIT as plan_benefit, (COALESCE((select fin_financials.planned_cst_total
        from fin_financials, odf_object_instance_mapping
        where odf_object_instance_mapping.primary_object_instance_id = inv_investments.id
        and odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id
    and odf_object_instance_mapping.primary_object_instance_code = inv_investments.odf_object_code
    and odf_object_instance_mapping.secondary_object_instance_code = 'financials'  ), 0))
    -(COALESCE((SELECT SUM(b.totalcost) FROM ppa_wip a, ppa_wip_values
        b WHERE a.transno = b.transno AND b.currency_type = 'HOME'
        and a.status = 0
    AND a.investment_id = inv_investments.id GROUP BY
        a.investment_id),0)) as cost_variance, ((CASE WHEN ((BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT()*1.00)=0 THEN NULL ELSE (((COALESCE(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v)*1.00)-((BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT()*1.00))/((BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT()*1.00) END )*(100.0*1.00)) as obj_effort_var, NULL::TEXT  COLLATE "en-x-icu"  as intl_appimpacts, inv_investments.CURRENCY_CODE COLLATE "en-x-icu"  as currency_code, odf_ca_project.obj_request_type COLLATE "en-x-icu"  as obj_request_type, inv_projects.RCF_IMPLEMENTATION as rcf_implementation, inv_projects.percent_calc_mode as percent_calc_mode, (CASE WHEN COALESCE(EVREC.BAC,0) > 0 AND COALESCE(EVREC.ITD_BCWP,0) > 0
        THEN EVREC.ITD_ACWP + (EVREC.BAC - EVREC.ITD_BCWP)
        ELSE NULL END) as ev_eac_at, odf_ca_inv.odf_object_code COLLATE "en-x-icu"  as odf_object_code, (select total_cost_of_capital from prsite) as system_value_for_tcc, fin_financials.PLANNED_MIRR as planned_mirr, fin_financials.FORECAST_NPV as forecast_npv, fin_financials.PLANNED_CST_FINISH as planned_cst_finish, PAC_MNT_PROJECTS.EQUIPMENT_EXCHANGE_RATE_TYPE COLLATE "en-x-icu"  as eqp_xchg_rate_type, fin_financials.BUDGET_COST_CAPITAL as budget_cost_cap, PAC_MNT_PROJECTS.COST_TYPE COLLATE "en-x-icu"  as cost_type, (SELECT
          CASE WHEN COALESCE(F.PLANNED_CST_TOTAL,0) > 0
              THEN COALESCE(F.PLANNED_CST_OPERATING_TOTAL,0) / F.PLANNED_CST_TOTAL
              ELSE NULL END
        FROM FIN_FINANCIALS F 
        WHERE F.ID = FIN_FINANCIALS.ID) as planned_cst_operating_pct, inv_investments.labor_eac_curve as labor_eac, odf_ca_project.intl_char_inst COLLATE "en-x-icu"  as intl_char_inst, fin_financials.BUDGET_PAYBACK_PERIOD as budget_payback_period, odf_ca_inv.stx_mds as stx_mds, inv_investments.cbk_type COLLATE "en-x-icu"  as cbk_type, NULL::TEXT  COLLATE "en-x-icu"  as stnx_services, PAC_MNT_PROJECTS.billing_project_id as billing_project_id, odf_ca_project.agile_release_name COLLATE "en-x-icu"  as agile_release_name, inv_investments.RCF_OBJECTIVES as inv_rcf_objectives, odf_ca_inv.obj_work_status COLLATE "en-x-icu"  as obj_work_status, fin_financials.PLANNED_IRR as planned_irr, inv_investments.is_open_for_te as isopen, inv_investments.RCF_RESOURCE_AVAIL as inv_rcf_res_avail, odf_ca_inv.odf_period_end as odf_period_end, inv_investments.TRACK_ASSGN_ONLY as track_assgn_only, fin_financials.is_sys_val_for_rr as is_sys_val_for_rr, CASE WHEN COALESCE(INV_INVESTMENTS.labor_eacsum,0) > 0
             THEN COALESCE(INV_INVESTMENTS.labor_actsum,0)/COALESCE(INV_INVESTMENTS.labor_eacsum,0)
             ELSE 0 END as pct_complete, 'project' COLLATE "en-x-icu"  as odf_obj_type, inv_projects.TYPE COLLATE "en-x-icu"  as inv_fin_type, inv_investments.IS_REQUIRED as is_required, PAC_MNT_PROJECTS.EX_BILL_EXPENSES as bill_expenses, odf_ca_project.agile_mr_id COLLATE "en-x-icu"  as agile_mr_id, INV_INVESTMENTS.LAST_UPDATED_DATE as last_updated_date, ODF_CA_INV.odf_object_code COLLATE "en-x-icu"  as inv_type, fin_financials.BUDGET_CST_START as budget_cst_start, odf_ca_project.obj_stakeholder1 as obj_stakeholder1, odf_ca_project.obj_stakeholder2 as obj_stakeholder2, odf_ca_project.obj_stakeholder3 as obj_stakeholder3, inv_projects.prPctComplete as prpctcomplete, fin_financials.PLANNED_BREAKEVEN as planned_breakeven, (((COALESCE(INV_INVESTMENTS.labor_eacsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v)*1.00)-((BASEREC.USAGE_SUM / 3600) / PRJ_HPD_FACTOR_FCT()*1.00)) as obj_effort_amt_var, inv_projects.PROCESS_SCALE_WORK as process_scale_work, odf_ca_inv.intl_prim_strat_init COLLATE "en-x-icu"  as intl_prim_strat_init, fin_financials.FORECAST_REV_START as forecast_rev_start   from inv_investments inv_investments LEFT OUTER JOIN prj_ev_history EVREC ON EVREC.OBJECT_ID = INV_INVESTMENTS.ID AND EVREC.OBJECT_TYPE='PROJECT' AND EVREC.PERIOD_NUMBER=0 LEFT OUTER JOIN prj_baseline_details BASEREC ON BASEREC.BASELINE_ID = INV_INVESTMENTS.BASELINE_ID AND BASEREC.OBJECT_TYPE='PROJECT', inv_projects inv_projects, srm_resources CREATED, srm_resources UPDATED, odf_ca_inv odf_ca_inv, fin_financials fin_financials, odf_object_instance_mapping oim7, odf_ca_financials odf_ca_financials, pac_mnt_projects PAC_MNT_PROJECTS, odf_ca_projfinproperties odf_ca_projfinproperties, odf_ca_project odf_ca_project  where 1 = 1 and inv_investments.id = inv_projects.prid and inv_investments.created_by = CREATED.user_id and inv_investments.last_updated_by = UPDATED.user_id and inv_investments.id = odf_ca_inv.id and inv_investments.id = oim7.primary_object_instance_id  and oim7.primary_object_instance_code = 'project' and fin_financials.id = oim7.secondary_object_instance_id  and oim7.secondary_object_instance_code = 'financials'  and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = PAC_MNT_PROJECTS.id and inv_investments.id = odf_ca_projfinproperties.id and inv_investments.id = odf_ca_project.id and odf_ca_inv.odf_object_code = 'project'  and inv_investments.id = 5126938

Resolution

If this issue is repeatedly happening, please open a case with Support.