amrahman

PMO Accelerator installation failure owing to portlet import

Discussion created by amrahman on Dec 12, 2012
Latest reply on Dec 14, 2012 by Chris_Hackett
Hi all,

I'm running into an issue during the installation of PMO 2.1.1 on Clarity 12.06. The error log as per admin.log indicates the following:

[exec] - Exception due to DB Connection failure
[exec] ERROR 2012-12-12 09:06:34,910 [main] persistence.PersistenceControllert Exception due to DB Connection failure
[exec] Content Pack Installation - XOG Install failed: Failure occurred while applying queries/cop_qry_pflFinCapExByPeriod.xml
[exec] Check e:\clarity12\META-INF\content\xog\csk\queries/cop_qry_pflFinCapExByPeriod_out.xml for errors
[exec] Failed to install content pack: Content Pack Installation - XOG Install failed Failure occurred while applying queries/cop_qry_pflFinCapExByPeriod.xml
[exec] Check e:\clarity12\META-INF\content\xog\csk\queries/cop_qry_pflFinCapExByPeriod_out.xml for errors
[exec] Error occurred: com.niku.nsa.service.ExecutableException: Content Pack Installation - XOG Install failed Failure occurred while applying queries/cop_qry_pflFinCapExByPeriod.xml
[exec] Check e:\clarity12\META-INF\content\xog\csk\queries/cop_qry_pflFinCapExByPeriod_out.xml for errors
[exec] Check admin.log or use -verbose for more information.

I understand it's failing to import the query identified in the cop_qry_pflFinCapExByPeriod.xml file.
So I decided to add the query into Clarity via admin user-interface, and now I get the following error message:

NPT-0120: There was an error while handling your request. Make sure your NSQL/SQL syntax is correct. Note that the : and @ delimiter characters used in expressions must be escaped with \.

I've tried checking for syntax, but I don't find any (to my knowledge). Any help is appreciated!

Here's the query just for reference.
SELECT @select:dim:user_def:implied:investment:q.inv_invest_id:investment_int_id@,
       @select:dim_prop:user_def:implied:investment:q.unique_code:investment_id@,
       @select:dim_prop:user_def:implied:investment:q.name:investment_name@,
       @select:dim_prop:user_def:implied:investment:q.investment_code:investment_objcode_upcase@,
       @select:dim_prop:user_def:implied:investment:q.portfolio_id:portfolio_id@,
       @select:dim_prop:user_def:implied:investment:q.scenario_id:scenario_id@,
       @select:dim_prop:user_def:implied:investment:q.status:status@,
       @select:dim_prop:user_def:implied:investment:q.is_approved:is_approved@,   
       @select:dim_prop:user_def:implied:investment:SUM( CASE WHEN CCList.PrExternalId = 'Capital' THEN ROUND( @NVL@(fv.planned_cost,0),0 ) ELSE 0 END)  OVER (PARTITION BY q.inv_invest_id ):investment_capital@,
       @select:dim_prop:user_def:implied:investment:SUM( CASE WHEN CCList.PrExternalId = 'Expense' THEN ROUND( @NVL@(fv.planned_cost,0),0 ) ELSE 0 END)  OVER (PARTITION BY q.inv_invest_id ):investment_expense@,
       @select:dim_prop:user_def:implied:investment:SUM( ROUND( @NVL@(fv.planned_cost,0),0) )  OVER (PARTITION BY q.inv_invest_id ):investment_total@,
       @select:dim_prop:user_def:implied:investment:ROUND( @NVL@(q.PLANNED_CST_TOTAL,0),0):pfl_inv_plan_cost_total@,
       @select:dim_prop:user_def:implied:investment:ROUND( @NVL@(Q.ACTUAL_CST_TOTAL,0),0):pfl_inv_ACTUAL_COST_total@,   
       @select:dim:user_def:implied:period:@NVL@(nls.name,'9999'):financial_period_group_id@,
       @select:dim_prop:user_def:implied:period:@NVL@(b.start_date, TO_DATE('20991231', 'YYYYMMDD')):financial_start_date@,
       @select:dim_prop:user_def:implied:period:b.end_date-1:financial_end_date@,
       @select:dim_prop:user_def:implied:period:@NVL@(nls.name, NCP.Name):financial_period_name@,
       @select:dim_prop:user_def:implied:period:b.p_description:financial_period_desc@,
       @select:dim:user_def:implied:chargecode:CCList.PrExternalId:charge_code_id@,
       @select:dim_prop:user_def:implied:chargecode:CCList.PrName:charge_code_name@,
       @select:metric:user_def:implied:@NVL@(ROUND( @NVL@(fv.planned_cost,0), 0),0):planned_cost@,
       @select:metric:user_def:implied:@NVL@(SUM(ROUND( @NVL@(av.cost_amount,0),0)),0):actual_cost@,
       @select:metric:user_def:implied: @NVL@(ROUND(@NVL@(fv.planned_cost,0),0) - SUM(ROUND(@NVL@(av.cost_amount,0),0)),0):var_planned_actual_cost@
FROM  (@INVESTMENT_SQL@) q
      LEFT OUTER JOIN inv_investments inv ON inv.id = q.inv_invest_id
      LEFT OUTER JOIN pma_portfolios pfl ON pfl.id = q.portfolio_id

  LEFT OUTER JOIN cmn_lookups_v NCP ON NCP.language_code = @WHERE:PARAM:LANGUAGE@ AND Lookup_type = 'CUST_PORTLET_QUERY_TRANSLATIONS' AND Lookup_code = 'No Cost Plan'
      LEFT OUTER JOIN cmn_lookups_v status_lookup ON inv.status = status_lookup.lookup_enum AND status_lookup.lookup_type = 'INVESTMENT_OBJ_STATUS' AND status_lookup.language_code = 'en'
      LEFT OUTER JOIN entity e ON e.entity = inv.entity_code       
      LEFT OUTER JOIN biz_com_periods b ON b.entity_id = e.id AND b.period_type = NVL(@WHERE:PARAM:USER_DEF:STRING:PERIOD_TYPE@,'QUARTERLY')
              AND ( b.Start_date BETWEEN pfl.start_date AND pfl.finish_date -1 OR b.End_date -1  BETWEEN pfl.start_date AND pfl.finish_date )
      LEFT OUTER JOIN cmn_captions_nls nls ON nls.pk_id = b.id AND nls.table_name = 'BIZ_COM_PERIODS'
      

AND nls.language_code = @WHERE:PARAM:LANGUAGE@
      LEFT OUTER JOIN ( SELECT prId, PrName, PrExternalId 
                          FROM prchargecode
                         WHERE PrExternalId IN ('Capital', 'Expense' )
                      ) CCList ON 1=1


  
      LEFT OUTER JOIN ( SELECT fp.object_id inv_int_id, cpd.prchargecode_id charge_code_id, 
                        c.prexternalid charge_code, c.prname charge_code_name, 
                        SUM ( ROUND( CASE WHEN fp.plan_type_code = 'FORECAST'
                                  THEN cpd_ssl.slice
                                  ELSE 0 END * (COP_PORT_SLICE_PERIOD_DAYS_FCT( cpd_ssl.start_date, cpd_ssl.finish_date, PERIODS.start_date, PERIODS.End_Date, pfl.start_date, pfl.finish_date) ), 0) ) planned_cost,
                        PERIODS.id Period_id, PERIODS.Period_Name
                        FROM (@INVESTMENT_SQL@) qq
                        LEFT OUTER JOIN pma_portfolios pfl ON pfl.id = qq.portfolio_id
                        LEFT OUTER JOIN inv_investments I ON I.id = qq.inv_invest_id
                        LEFT OUTER JOIN Fin_Plans fp ON fp.object_id = i.id
                        LEFT OUTER JOIN Entity ENT ON ENT.entity = I.entity_code
                        LEFT OUTER JOIN FIN_COST_PLAN_DETAILS cpd ON cpd.plan_id = fp.id
                        LEFT OUTER JOIN prchargecode c ON c.prId = cpd.prchargecode_id
                        LEFT OUTER JOIN odf_ssl_cst_dtl_cost cpd_ssl ON cpd_ssl.prj_object_id = cpd.id
                        LEFT OUTER JOIN biz_com_periods PERIODS ON PERIODS.entity_id = ENT.id AND PERIODS.period_type = NVL(@WHERE:PARAM:USER_DEF:STRING:PERIOD_TYPE@,'QUARTERLY')  
                        AND (
                         ( cpd_ssl.start_date >= PERIODS.start_date AND cpd_ssl.finish_date <= PERIODS.end_date )
                         OR
                         ( cpd_ssl.start_date < PERIODS.start_date AND cpd_ssl.finish_date <=  PERIODS.end_date )
                         OR 
                         ( cpd_ssl.start_date >= PERIODS.start_date AND cpd_ssl.finish_date >  PERIODS.end_date )
                         OR 
                         ( cpd_ssl.start_date < PERIODS.start_date AND cpd_ssl.finish_date >  PERIODS.end_date )
                            )   
                        WHERE 1=1  
                          AND fp.object_id IS NOT NULL
                          AND PERIODS.Period_Name IS NOT NULL
                        GROUP BY fp.object_id , cpd.prchargecode_id , c.prexternalid , c.prname,                             PERIODS.id, PERIODS.Period_name  
                      ) fv ON fv.inv_int_id = inv.id AND fv.period_id = b.id AND fv.Charge_Code_id = CCList.PrId 
      LEFT OUTER JOIN ( SELECT w.investment_id inv_int_id, w.charge_code charge_code, SUM( ROUND( @NVL@(wv.totalcost,0),0) ) cost_amount,  PERIODS.id Period_Id, PERIODS.Period_name
                        FROM   ppa_wip w
                        LEFT OUTER JOIN ppa_wip_values wv ON w.transno = wv.transno AND wv.currency_type = 'HOME'
                        LEFT OUTER JOIN inv_investments I ON I.id = w.investment_id
                        LEFT OUTER JOIN Entity ENT ON ENT.entity = I.entity_code  
                        LEFT OUTER JOIN biz_com_periods PERIODS ON PERIODS.entity_id = ENT.id AND PERIODS.period_type = NVL(@WHERE:PARAM:USER_DEF:STRING:PERIOD_TYPE@,'QUARTERLY')
                             AND w.transdate BETWEEN PERIODS.start_Date AND PERIODS.end_date -1
                        WHERE w.status = 0  
                        GROUP BY w.investment_id, w.charge_code, PERIODS.id, PERIODS.Period_name 
                      ) av ON av.inv_int_id = inv.id AND av.charge_code = CCList.PrExternalId AND av.Period_id = b.id
      LEFT OUTER JOIN ( SELECT @WHERE:PARAM:USER_DEF:STRING:PERIOD_TYPE@ PERIOD_TYPE FROM dual WHERE 1=1 ) t ON 1=1
      WHERE  1=1
      AND    inv.id IS NOT NULL 
     AND    @WHERE:SECURITY:INV:q.inv_invest_id@
     AND    @FILTER@
     GROUP BY q.inv_invest_id, q.unique_code, q.name, q.investment_code, q.portfolio_id, q.scenario_id, q.status, q.is_approved,
              CCList.PrExternalId, CCList.PrName, CCList.PrId, 



  b.start_date, b.end_date-1, nls.name, b.p_description,
  fv.planned_cost, q.PLANNED_CST_TOTAL, q.ACTUAL_CST_TOTAL, NCP.Name
HAVING @HAVING_FILTER@
ORDER BY nls.name, CCList.PrExternalId, q.name
@column_list(@INVESTMENT_SQL@, INV_INVEST_ID, UNIQUE_CODE, NAME, INVESTMENT_CODE, PORTFOLIO_ID, SCENARIO_ID, STATUS, IS_APPROVED, PLANNED_CST_TOTAL, ACTUAL_CST_TOTAL)@

Outcomes