Clarity

We are trying to sync Portfolios in CA PPM, using  Synchronize portfolio investments job,  But jobs are failing with error in v15.2 version for some portfolio. In bg log   the error is , does anyone seen this issue before?

ERROR 2017-08-31 09:59:51,393 [Dispatch Synchronize portfolio investments : bg@zldv8312.vci.att.com (tenant=clarity)] job.PFMSyncManager (clarity:ks649c:22197807__CC3654E5-69CE-4B91-A604-A4C0A41CE839:Synchronize portfolio investments)
com.niku.union.odf.exception.ODFException: com.niku.union.persistence.PersistenceException:
SQL error code: 957
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00957: duplicate column name

Executed:
 update pfm_investments set code = decode(?, 2, code, ?), BUDGET_NPV = decode(?, 2, BUDGET_NPV, ?), REINVESTMENT_RATE = decode(?, 2, REINVESTMENT_RATE, ?), BUDGET_MIRR = decode(?, 2, BUDGET_MIRR, ?), STAGE_CODE = decode(?, 2, STAGE_CODE, ?), BUDGET_CST_START = decode(?, 2, BUDGET_CST_START, ?), BUDGET_CST_FINISH = decode(?, 2, BUDGET_CST_FINISH, ?), PLANNED_ROI = decode(?, 2, PLANNED_ROI, ?), GOAL_CODE = decode(?, 2, GOAL_CODE, ?), schedule_start = decode(?, 2, schedule_start, ?), ALIGNMENT = decode(?, 2, ALIGNMENT, ?), RSF_OBS_UNIT = decode(?, 2, RSF_OBS_UNIT, ?), APPROVEDBY_ID = decode(?, 2, APPROVEDBY_ID, ?), BUDGET_BENEFIT_FINISH = decode(?, 2, BUDGET_BENEFIT_FINISH, ?), PORTFOLIO_ID = decode(?, 2, PORTFOLIO_ID, ?), BUDGET_BENEFIT_START = decode(?, 2, BUDGET_BENEFIT_START, ?), STATUS = decode(?, 2, STATUS, ?), BUDGET_BREAKEVEN = decode(?, 2, BUDGET_BREAKEVEN, ?), PLANNED_NPV = decode(?, 2, PLANNED_NPV, ?), IS_ACTIVE = decode(?, 2, IS_ACTIVE, ?), RISK = decode(?, 2, RISK, ?), INITIAL_INVESTMENT = decode(?, 2, INITIAL_INVESTMENT, ?), PLAN_CST_START = decode(?, 2, PLAN_CST_START, ?), chargecodeid = decode(?, 2, chargecodeid, ?), PLAN_CST_FINISH = decode(?, 2, PLAN_CST_FINISH, ?), PORT_INV_STATUS = decode(?, 2, PORT_INV_STATUS, ?), STATUS_INDICATOR = decode(?, 2, STATUS_INDICATOR, ?), PLANNED_PAYBACK_PERIOD = decode(?, 2, PLANNED_PAYBACK_PERIOD, ?), TOTAL_COST_OF_CAPITAL = decode(?, 2, TOTAL_COST_OF_CAPITAL, ?), MANAGER_ID = decode(?, 2, MANAGER_ID, ?), STATUS_COMMENT = decode(?, 2, STATUS_COMMENT, ?), PROGRESS = decode(?, 2, PROGRESS, ?), schedule_finish = decode(?, 2, schedule_finish, ?), BUDGET_IRR = decode(?, 2, BUDGET_IRR, ?), SLA_COMPLIANCE = decode(?, 2, SLA_COMPLIANCE, ?), PURGE_FLAG = decode(?, 2, PURGE_FLAG, ?), PLAN_BENEFIT_FINISH = decode(?, 2, PLAN_BENEFIT_FINISH, ?), TARGET_MANAGER_ID = decode(?, 2, TARGET_MANAGER_ID, ?), labor_variancesum = decode(?, 2, labor_variancesum, ?), name = decode(?, 2, name, ?), description = decode(?, 2, description, ?), BUDGET_PAYBACK_PERIOD = decode(?, 2, BUDGET_PAYBACK_PERIOD, ?), SYNC_INV_N_BDG_DATES = decode(?, 2, SYNC_INV_N_BDG_DATES, ?), track_mode = decode(?, 2, track_mode, ?), RISK = decode(?, 2, RISK, ?), priority = decode(?, 2, priority, ?), BUDGET_ROI = decode(?, 2, BUDGET_ROI, ?), PLAN_BENEFIT_START = decode(?, 2, PLAN_BENEFIT_START, ?), CURRENCY_CODE = decode(?, 2, CURRENCY_CODE, ?), ODF_OBJECT_CODE = decode(?, 2, ODF_OBJECT_CODE, ?), PLANNED_MIRR = decode(?, 2, PLANNED_MIRR, ?), sync_date = decode(?, 2, sync_date, ?), PLANNED_IRR = decode(?, 2, PLANNED_IRR, ?), is_open_for_te = decode(?, 2, is_open_for_te, ?), TRACK_ASSGN_ONLY = decode(?, 2, TRACK_ASSGN_ONLY, ?), IS_REQUIRED = decode(?, 2, IS_REQUIRED, ?), INVESTMENT_ID = decode(?, 2, INVESTMENT_ID, ?), PLANNED_BREAKEVEN = decode(?, 2, PLANNED_BREAKEVEN, ?), last_updated_date = ?, last_updated_by = ?    where  1 = 1  and id = ?
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement id="odf-pmd.pfm_investment.update.pfm_investments" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
  <errorMapping applicationCode="odf.uniqueIndexViolation">
    <dbError dbCode="1" dbVendor="oracle"/>
    <dbError dbCode="2601" dbVendor="mssql"/>
  </errorMapping>
  <sql dbVendor="oracle">

But jobs are failing with error in v15.2 version for some portfolio

ORA-00957 means it tried to insert twice in the same column..

Quickly checking through the insert statement, risk is being inserted twice. 

Have you made any changes recently? Particularly to the investment risk properties?

Check the portfolio attribute registry, if possible remove "risk" and re-sync.

As Andrew mentioned there could be a issue with Attribute Registry, please remove all the attributes and add them back and resync the portfolio

Thanks for your reply,

Do you mean to remove attributes from 'Investment' object? Risk is OOB and this job is also OOB , how can I remove attributes?

Please elaborate so I can try and fix the issue.

No.

The portfolio investment object has a selection mechanism, this determines what is copied into the pfm tables when the sync is run.

Remove risk attributes from the "selected attributes" and then re-sync.

Thanks,   I removed Risk  and resync, it worked.

I added again and tried for one portfolio, it worked let me try for few others.

Great please mark the post as answered so that other community member are benefitted

I suspect this is a defect that an attribute is listed twice. Great to hear you sorted it!

Hi Kritee,

There is an object called Portfolio Investment and under that object there is a tab called "Attribute Registry" and under that you can select attributes, try removing those and see if it works.

Regards

Suman Pramanik