AnsweredAssumed Answered

'com.niku.union.persistence.PersistenceException: ' in process

Question asked by Deepika_J Employee on Feb 17, 2014
Latest reply on Feb 19, 2014 by Deepika_J

Hello All,

I'm getting an error in the process which is created on Change Request object.

The code snippet in script is:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            <sql:query var="cr">
select
        inv.code myProjCode,
        inv.id myProjID,
        mr1.id myPM,
        mr2.unique_name myCFR,
        mr3.unique_name myLM,
        mr4.unique_name myOwner,
        rim.id myCRID,
        rim.rim_risk_issue_code myCRCode,
        rim.name myCRName,
        to_char(sysdate, 'YYYY-MM-DD') mySysDate,
        cr.benefits benefits,
        cr.impact_on_baseline iob,
        round(cr.effect_on_cost, 0) eoc,
        round(cr.effect_on_schedule,0) eos,
        round(cr.effect_on_resources, 0) eor,
        to_char(cr.approved_date, 'YYYY-MM-DD') appd,
        apprv.user_name appb,
        to_char(cr.review_date, 'YYYY-MM-DD')  revd,
        to_char(cr.closure_date, 'YYYY-MM-DD')  clsd,
        assr.user_name assessor,
        to_char(cr.assessment_date, 'YYYY-MM-DD')  assmtd
        from
        inv_investments inv
        ,odf_ca_project ocp
        ,rim_risks_and_issues rim
        ,cmn_sec_users csu
        ,srm_resources mr1
        ,srm_resources mr2
        ,srm_resources mr3
        ,cmn_sec_users csu2
        ,srm_resources mr4
        ,rim_change_requests cr
        ,cmn_sec_users assr
        ,cmn_sec_users apprv

        where
            inv.id = ocp.id
        and inv.id = rim.pk_id
        and rim.id =  ${myObjectId}
        and cr.id = rim.id
        and inv.manager_id = csu.id
        and csu.id = mr1.user_id
        and ocp.eri_cfr = mr2.id
        and ocp.eri_line_manager = mr3.id
        and rim.assigned_to = csu2.id
        and csu2.user_name = mr4.unique_name
        and inv.ODF_OBJECT_CODE = 'project'
        and rim.type_code = 'CHANGE'
        and cr.assessor = assr.id (+)
        and cr.approved_by = apprv.id (+)
</sql:query>
            <!-- tg is the name of the xml doc returned by the query -->
            <core:choose>
                <core:when test="${cr.rowCount != 0}">
                    <core:forEach items="${cr.rows}" var="row">
                        <core:set value="${row.get('myProjCode')}" var="myProjCode"/>
                        <core:set value="${row.get('myProjID')}" var="myProjID"/>
                        <core:set value="${row.get('myPM')}" var="myPM"/>
                        <core:set value="${row.get('myCFR')}" var="myCFR"/>
                        <core:set value="${row.get('myLM')}" var="myLM"/>
                        <core:set value="${row.get('myOwner')}" var="myOwner"/>
                        <core:set value="${row.get('myCRID')}" var="myCRID"/>
                        <core:set value="${row.get('myCRName')}" var="myCRName"/>
                        <core:set value="${row.get('myCRCode')}" var="myCRCode"/>
                        <core:set value="${row.get('mySysDate')}" var="mySysDate"/>
                        <core:set value="${row.get('benefits')}" var="myBenefits"/>
                        <core:set value="${row.get('iob')}" var="myIOB"/>
                        <core:set value="${row.get('eoc')}" var="myEOC"/>
                        <core:set value="${row.get('eos')}" var="myEOS"/>
                        <core:set value="${row.get('eor')}" var="myEOR"/>
                        <core:set value="${row.get('appd')}" var="myApprvD"/>
                        <core:set value="${row.get('appb')}" var="myApprvB"/>
                        <core:set value="${row.get('revd')}" var="myRevDate"/>
                        <core:set value="${row.get('clsd')}" var="myClosureDate"/>
                        <core:set value="${row.get('assessor')}" var="myAssessor"/>
                        <core:set value="${row.get('assmtd')}" var="myAssmtDate"/>
                        <!-- Log the activities from the master object query-->
                        <gel:log> myProjCode = ${myProjCode} myProjID = ${myProjID}  myPM = ${myPM}</gel:log>
                        <gel:log> myCFR = ${myCFR} myLM = ${myLM} myOwner = ${myOwner} myCRID = ${myCRID}</gel:log>
                        <gel:log> myCRCode = ${myCRCode} myCRName = ${myCRName} mySysDate = ${mySysDate} </gel:log>
                        <!--Parse the XOG -->
                        <gel:parse var="cr_xog">
                            <NikuDataBus
                                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_change.xsd">
                                <Header action="write"
                                    externalSource="NIKU"
                                    objectType="change" version="12.0.1.5063"/>
                                <changeRequests>
                                    <changeRequest
                                    approvedBy="${myApprvB}"
                                    approvedDate="${myApprvD}"
                                    assessmentDate="${myAssmtDate}"
                                    assessor="${myAssessor}"
                                    benefits="${myBenefits}"
                                    closureDate="${myClosureDate}"
                                    code="${myCRCode}"
                                    effectOnCost="${myEOC}"
                                    effectOnResources="${myEOR}"
                                    effectOnSchedule="${myEOS}"
                                    impactBaseline="${myIOB}"
                                    name="${myCRName}"
                                    ownerCode="${myOwner}"
                                    projectCode="${myProjCode}" reviewDate="${myRevDate}">
                                    <notes/>
                                    <CustomInformation>
                                    <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                                    <ColumnValue name="eri_cfr">${myCFR}</ColumnValue>
                                    <ColumnValue name="eri_cfr_appr_date">${mySysDate}</ColumnValue>
                                    </CustomInformation>
                                    </changeRequest>
                                </changeRequests>
                            </NikuDataBus>
                        </gel:parse>
                        <gel:log>
                            <gel:expr select="$cr_xog"/>
                        </gel:log>
                        <core:catch var="xogerr">
                            <soap:invoke endpoint="${myXogUrl}/niku/xog" var="runresult">
                                <soap:message>
                                    <soapenv:Envelope
                                    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                                    <soapenv:Header>
                                    <xog:Auth>
                                    <xog:SessionID>${sessionID}</xog:SessionID>
                                    </xog:Auth>
                                    </soapenv:Header>
                                    <soapenv:Body>
                                    <gel:include select="$cr_xog"/>
                                    </soapenv:Body>
                                    </soapenv:Envelope>
                                </soap:message>
                            </soap:invoke>
                        </core:catch>
                    </core:forEach>
                    <!-- Log the Results from XOG -->
                    <core:choose>
                        <core:when test="${xogerr != null}">
                            <core:set value="${xogerr.text()}" var="error_message"/>
                            <gel:log level="error" message="${error_message}"/>
                        </core:when>
                        <core:otherwise>
                            <gel:set asString="true"
                                select="$runresult/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Status/@state" var="state"/>
                            <gel:set asString="true" select="$runresult" var="errmsg"/>
                            <core:choose>
                                <core:when test="${state == 'FAILURE'}">
                                    <gel:log level="error" message="New matrix insert failed: $errmsg"/>
                                </core:when>
                                <core:when test="${state == 'SUCCESS'}"/>
                            </core:choose>
                        </core:otherwise>
                    </core:choose>
                 
</gel:script>

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The sql query has no error and it is executing,but when i add it to gel script of a process and try running it,following error appears.

'com.niku.bpm.BpmException: com.niku.union.odf.exception.ODFException: Error reading change instance with PK: 5672044
    at com.niku.bpm.services.oi.odf.BpmObjectImpl.readInstance(BpmObjectImpl.java:413)
    at com.niku.bpm.engine.statemgmt.RuntimeObject.getBpmObjectInstance(RuntimeObject.java:488)
    at com.niku.bpm.engine.statemgmt.RuntimeObject.getBpmObjectInstance(RuntimeObject.java:461)
    at com.niku.bpm.engine.statemgmt.RuntimeObject.refreshObject(RuntimeObject.java:251)
    at com.niku.bpm.engine.statemgmt.RuntimeObject.getLiveObjectId(RuntimeObject.java:224)
    at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:153)
    at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:727)
    at java.lang.Thread.run(Thread.java:619)
Caused by: com.niku.union.odf.exception.ODFException: Error reading change instance with PK: 5672044
    at com.niku.odf.object.ODFAbstractObjectImpl.readInstance(ODFAbstractObjectImpl.java:1878)
    at com.niku.odf.object.ODFAbstractObjectImpl.readInstance(ODFAbstractObjectImpl.java:1748)
    at com.niku.odf.object.ODFAbstractObjectImpl.readInstance(ODFAbstractObjectImpl.java:1736)
    at com.niku.bpm.services.oi.odf.BpmObjectImpl.readInstance(BpmObjectImpl.java:405)
    ... 7 more
Caused by: com.niku.union.persistence.PersistenceException:
SQL error code: 904
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "PK_ID"."ERI_LATE_UMPT": invalid identifier

Executed:
 select
 'x' pmd_analytical_partition_by,
 rim_risks_and_issues.id odf_pk,
 odf_ca_change.eri_wbs_form_guide  eri_wbs_form_guide,
 odf_ca_change.eri_cr_results  eri_cr_results,
 odf_ca_change.bill_cust bill_cust,
 pk_id.name  pk_name,
 ((CASE WHEN (nvl((odf_ca_change.eri_chg_hw_sw_p_rev*1.00),0)+nvl((odf_ca_change.chg_services_p_rev*1.00),0)+nvl((odf_ca_change.eri_cr_lt_p_re_hw_sw*1.00),0)+nvl((odf_ca_change.eri_cr_lt_pl_rev_ser*1.00),0))=0 THEN NULL ELSE ((nvl((odf_ca_change.eri_chg_hw_sw_p_rev*1.00),0)+nvl((odf_ca_change.chg_services_p_rev*1.00),0)+nvl((odf_ca_change.eri_cr_lt_p_re_hw_sw*1.00),0)+nvl((odf_ca_change.eri_cr_lt_pl_rev_ser*1.00),0))-(nvl((odf_ca_change.eri_ch_hw_sw_budget*1.00),0)+nvl((odf_ca_change.eri_ch_ser_budget*1.00),0)+nvl((odf_ca_change.eri_cr_lt_pl_bud_hw_*1.00),0)+nvl((odf_ca_change.eri_cr_lt_pl_bud_ser*1.00),0)))/(nvl((odf_ca_change.eri_chg_hw_sw_p_rev*1.00),0)+nvl((odf_ca_change.chg_services_p_rev*1.00),0)+nvl((odf_ca_change.eri_cr_lt_p_re_hw_sw*1.00),0)+nvl((odf_ca_change.eri_cr_lt_pl_rev_ser*1.00),0)) END )*(100.0*1.00)) eri_cr_prop_um_total,
 pk_id.unique_code  pk_unique_name,
 odf_ca_change.eri_in_scope eri_in_scope,
 rim_change_requests.IMPACT_ON_BASELINE  impact_baseline,
 odf_ca_change.eri_ch_ser_budget eri_ch_ser_budget,
 odf_ca_change.eri_cpm_appr_date eri_cpm_appr_date,
 (select cov.value from cmn_options co, cmn_option_values cov where co.id = cov.option_id and
        co.option_code = 'CMN_SYSTEM_CURRENCY')  currency_code, odf_ca_change.eri_rework eri_rework, (nvl((odf_ca_change.eri_cr_lt_pl_bud_hw_*1),0)+nv
        '

Can anyone help me out.

Thanks,

Deepika

 

 

 

 

Outcomes