Hi Senthil,
We were in V13.3 before upgrade.
Here is one of the script. highlighted the line which error out.
<gel:script xmlns:bpm="jelly:com.niku.bpm.gel.BPMTagLibrary" xmlns:core="jelly:core"
xmlns:fileutil="jelly:com.ca.clarity.services.utils.FileUtilsTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- ** Get all parameter values ** -->
<gel:setDataSource dbId="niku"/>
<gel:parameter default="os_set_ts_approve" var="timesheetsetapprove"/>
<gel:parameter default="os_set_timesheet_status" var="timesheetsetstatus"/>
<gel:parameter default="os_set_ts_return" var="timesheetsetreturn"/>
<gel:parameter default="os_financially_enable" var="financialstatusupdateprocessid"/>
<gel:parameter default="os_timesheet_approval_mob_v1" var="timesheetprocessid"/>
<!-- ** Get the XML messages from inbound table ** -->
<sql:query var="messages">
SELECT convert(nvarchar(max),Message) msg, inboundMessageID id
FROM dbo.z_inbound
WHERE messagetypeid = 6
AND Status=1
</sql:query>
<!-- create a new temporary file we can write the XML to -->
<fileutil:createTempFile prefix="MTS" suffix="mts" var="filename"/>
<!-- XMLLoop ** Iterate through all the unprocessed xml records retrieved from the inbound table ** -->
<core:forEach items="${messages.rowsByIndex}" trim="true" var="inrow">
<core:set value="2" var="updateflag"/>
<!-- ** write the xml to a file ** -->
<fileutil:writeFile fileName="${filename}" value="${inrow[0]}"/>
<core:catch var="pexep">
<gel:parse file="${filename}" var="inbound"/>
</core:catch>
<!-- ** Proceed if there are no exceptions ** -->
<core:if test="${pexep == null}">
<!-- IDLoop ** Iterate through the mobilerequest ids and set the timesheet status accordingly ** -->
<gel:forEach select="$inbound/Requests/Request" var="typeid">
<gel:set asString="true" select="$typeid/RequestId/text()" var="gettypeid"/>
<gel:set asString="true" select="$typeid/status/text()" var="getStatus"/>
<!-- Tokenize the status field to extract current step id,assignee response & resource id -->
<util:tokenize delim="*" var="token">${getStatus}</util:tokenize>
<core:set value="${token[0]}" var="type"/>
<!-- **** Process all TIMESHEET RESPONSES **** -->
<core:if test="${type == 'ts'}">
<core:set value="2" var="updateflag"/>
<core:set value="${token[1]}" var="getStatus"/>
<gel:out>getStatus=${getStatus}</gel:out>
<!-- ** Check if the Timesheet Approval process is currently running on the Timesheet instance and also if the timesheet is in submitted status ** -->
<core:catch var="getts_excep">
<sql:query var="gettsdetails">
SELECT count(*) cnt
FROM BPM_DEF_PROCESSES bdp
, BPM_DEF_PROCESS_VERSIONS bdpv
, BPM_RUN_PROCESSES brp
, BPM_RUN_OBJECTS bro
, PRTIMESHEET prt
WHERE bdp.id = bdpv.process_id
AND brp.process_version_id = bdpv.id
AND brp.status_code in ('BPM_PIS_RUNNING','BPM_PIS_ERROR')
and bdp.process_code = '${timesheetprocessid}'
AND bro.pk_id = brp.id
AND bro.table_name = 'BPM_RUN_PROCESSES'
AND bro.type_code = 'BPM_POT_PRIMARY'
AND bro.object_type_code = 'timesheet'
AND bro.object_id = ${gettypeid}
and prt.prid = bro.object_id
and prt.prstatus = 1
</sql:query>
</core:catch>
<!-- **** Enter only if there is a Timesheet process running and the timesheet status is "submitted" **** -->
<core:if test="${gettsdetails.rows[0].cnt > 0}">
<core:if test="${getStatus == 'A'}">
<core:set value="3" var="statusvalue"/>
<core:set value="${timesheetsetapprove}" var="tsprocvalue"/>
</core:if>
<core:if test="${getStatus == 'R'}">
<core:set value="2" var="statusvalue"/>
<core:set value="${timesheetsetreturn}" var="tsprocvalue"/>
</core:if>
<!-- ** Check if a proper value has been returned from the webservice ** -->
<sql:update var="setst">
UPDATE PRTIMESHEET
SET PRSTATUS = ${statusvalue}
WHERE PRID = ${gettypeid}
AND PRSTATUS = 1
</sql:update>
<!-- ** Kick off the process on the TS using the above timesheet id ** -->
<sql:query var="getProcId">
SELECT
BDPV.ID PROCID,
BDO.OBJECT_NAME OBJNAME
FROM
BPM_DEF_PROCESSES BDP,
BPM_DEF_PROCESS_VERSIONS BDPV,
BPM_DEF_OBJECTS BDO
WHERE BDPV.PROCESS_ID = BDP.ID
AND BDO.PK_ID = BDPV.ID
AND BDP.PROCESS_CODE = '${timesheetsetstatus}'
AND (BDO.IS_SYSTEM = 0 OR BDO.IS_SYSTEM IS NULL)
</sql:query>
<!-- ** Kick of the process that sets the Ace Completed flag so that the checkpoint C process will progress ** -->
<core:catch var="excep">
<core:forEach items="${getProcId.rowsByIndex}" trim="true" var="irow">
<bpm:startProcess initObjectId="${gettypeid}" initObjectKey="${getProcId.rows[0].objName}" initUserId="1" processVersionId="${getProcId.rows[0].procId}"/>
</core:forEach>
</core:catch>
<core:if test="${excep != null}">
<gel:log level="info" message="Unable to Kick off the Set Timesheet Status process. Exception is ${excep} for ${inrow[1]}"/>
</core:if>
</core:if>
<!-- ** End of Timesheet running process count ** -->
</core:if>
<!-- ** End of Timesheet processing ** -->
<!-- ** Process all Action Item responses ** -->
<core:if test="${type == 'ai'}">
<core:set value="${token[1]}" var="stepid"/>
<core:set value="${token[2]}" var="statusid"/>
<core:set value="${token[3]}" var="resourceid"/>
<core:set value="${token[4]}" var="calassigneeid"/>
<core:set value="${token[5]}" var="actiontype"/>
<core:set value="${gettypeid}" var="getactid"/>
<core:set value="2" var="updateflag"/>
<!-- ** Check if the stepid is same as the current running step of the action item.
This is to make sure that the process did not progess to the next steps before one or more assignees responded via email **#2 -->
<core:if test="${getactid == null or stepid == null} or calassigneeid == null">
<gel:log level="info" message="Key values missing for ${inrow[1]} - ActID=${getactid} StepID=${stepid} CalID=${calassigneeid}"/>
</core:if>
<core:if test="${getactid != null and stepid != null and calassigneeid != null}">
<core:set value="2" var="updateflag"/>
<sql:query var="checkcurstep">
SELECT DISTINCT RESULTS.id
FROM BPM_RUN_STEP_ACTION_RESULTS RESULTS
INNER JOIN NIKU.BPM_DEF_STEP_ACTIONS ACTIONS ON (
RESULTS.STEP_ACTION_ID = ACTIONS.ID
AND ACTIONS.TYPE_CODE = 'BPM_SAT_MANUAL'
)
INNER JOIN niku.bpm_run_steps rstep ON ACTIONS.STEP_ID = rstep.step_id
AND RESULTS.STEP_INSTANCE_ID = RSTEP.ID
LEFT OUTER JOIN niku.BPM_RUN_ASSIGNEES asn ON asn.PK_ID = RESULTS.id
WHERE rstep.status_code IN (
'BPM_SIS_READY_TO_EVAL_POSTCON'
,'BPM_SIS_ERROR'
)
AND rstep.id = ${ stepid}
AND RESULTS.AI_ID = ${ getactid}
AND asn.AI_STATUS_CODE = 'CAL_OPEN'
UNION
SELECT RESULTS.ID
FROM BPM_RUN_STEP_ACTION_RESULTS RESULTS
INNER JOIN NIKU.BPM_DEF_STEP_ACTIONS ACTIONS ON (
RESULTS.STEP_ACTION_ID = ACTIONS.ID
AND ACTIONS.TYPE_CODE = 'BPM_SAT_MANUAL'
)
INNER JOIN niku.bpm_run_steps rstep ON ACTIONS.STEP_ID = rstep.step_id
AND RESULTS.STEP_INSTANCE_ID = RSTEP.ID
INNER JOIN niku.BPM_RUN_STEP_TRANSITIONS TRANS ON TRANS.STEP_INSTANCE_ID = RSTEP.ID
INNER JOIN niku.bpm_run_steps rstep2 ON RSTEP2.ID = TRANS.NEXT_STEP_INSTANCE_ID
AND rstep2.status_code IN (
'BPM_SIS_READY_TO_EVAL_POSTCON'
,'BPM_SIS_ERROR'
)
WHERE RESULTS.AI_ID = ${ getactid}
AND rstep.id = ${ stepid}
</sql:query>
<!--** If the current running step is still the same then check if a NMS Message even has already been raised If so ignore and if not then create a message event for the assignee response ** #3 -->
<core:if test="${checkcurstep.rows[0].ID == NULL}">
<gel:out>${inrow[1]} inside checkcurstep null</gel:out>
<core:set value="2" var="updateflag"/>
</core:if>
<core:if test="${checkcurstep.rows[0].ID != NULL}">
<sql:query var="msgexist">
SELECT COUNT(ID) msgcnt
FROM NMS_MESSAGES
WHERE TOPIC = 'BPM'
AND MESSAGE_CODE = 'update'
AND OBJECT_ID = ${getactid}
AND OBJECT_CODE = 'actionitem'
AND USER_ID = ${resourceid}
AND (GETDATE() BETWEEN SEND_DATE AND EXPIRATION_DATE)
</sql:query>
<!-- Check if the NMS Message already exist #4 -->
<core:if test="${msgexist.rows[0].msgcnt != 0}">
<core:set value="2" var="updateflag"/>
</core:if>
<!-- ** New NMS Insert Flag ** -->
<core:if test="${msgexist.rows[0].msgcnt == 0}">
<!-- ** Update the assignee action status -->
<core:catch var="asgexcep">
<sql:update var="asg">
UPDATE niku.BPM_RUN_ASSIGNEES
SET ai_status_type_code = 'PROCESS_DEFAULT_AI_STATUS',
ai_status_code = '${statusid}',
last_updated_date = getdate()
WHERE table_name = 'BPM_RUN_STEP_ACTION_RESULTS'
AND user_id = ${resourceid}
AND pk_id = ${checkcurstep.rows[0].ID}
</sql:update>
<core:if test="${asg != 1 or asg == null}">
<core:set value="2" var="updateflag"/>
</core:if>
<!-- ASG Flag ** If BPM_RUN_ASSIGNEES Update succeeds ** -->
<core:if test="${asg == 1}">
<core:set value="2" var="updateflag"/>
<sql:update var="cal">
UPDATE niku.CAL_ACTION_ITEM_ASSIGNEES
SET STATUS_CODE = '${statusid}',
LAST_UPDATED_DATE=getdate(),
LAST_UPDATED_BY=1
WHERE CAL_ACTION_ITEM_ID = ${getactid} AND ASSIGNEE_ID = ${resourceid}
AND ID = ${calassigneeid} AND STATUS_CODE != 'CAL_CLOSED'
</sql:update>
<core:if test="${cal != 1 or cal == null}">
<core:set value="2" var="updateflag"/>
</core:if>
<!-- ** CAL Flag If CAL_ACTION_ITEM_ASSIGNEES Update succeeds ** -->
<core:if test="${cal==1}">
<core:set value="2" var="updateflag"/>
<core:catch var="msgexcep">
<gel:log level="info" message="inside cal1 actiontype=${actiontype}"/>
<!--** If the response is a financial status then update the financial status value in the Financial subpage too **-->
<core:if test="${actiontype == 'O'}">
<gel:log level="info" message="inside if action type actiontype=${actiontype}"/>
<sql:query var="getProcId">
SELECT
BDPV.ID PROCID,
BDO.OBJECT_NAME OBJNAME,
(select object_id from CAL_ACTION_ITEM_ASSIGNEES cala
inner join cal_action_items cal on cal.id = cala.cal_action_item_id
where cala.id = ${calassigneeid} and object_type = 'CAL_PROJECT') prjid
FROM
BPM_DEF_PROCESSES BDP,
BPM_DEF_PROCESS_VERSIONS BDPV,
BPM_DEF_OBJECTS BDO
WHERE BDPV.PROCESS_ID = BDP.ID
AND BDO.PK_ID = BDPV.ID
AND BDP.PROCESS_CODE = '${financialstatusupdateprocessid}'
AND (BDO.IS_SYSTEM = 0 OR BDO.IS_SYSTEM IS NULL)
</sql:query>
<!-- ** Kick of the process that sets the Financial status to Open on the project ** -->
<core:catch var="excep">
<bpm:startProcess initObjectId="${getProcId.rows[0].prjid}" initObjectKey="${getProcId.rows[0].objName}" initUserId="1" processVersionId="${getProcId.rows[0].procId}"/>
</core:catch>
<gel:log level="info" message="excep = ${excep}"/>
</core:if>
<sql:update var="msg">
niku.Z_CREATE_NMS_MESSAGES "BPM","update", ${getactid},"actionitem",${resourceid}, "NONE","-",1,1
</sql:update>
</core:catch>
</core:if>
</core:if>
<!-- ** End of ASG Flag ** -->
</core:catch>
<!-- ** End of NMS Exception ** -->
</core:if>
<!-- ** End of New NMS Insert Flag ** -->
<core:if test="${msgexcep != null}">
<core:set value="2" var="updateflag"/>
</core:if>
</core:if>
<!-- End of #3 -->
</core:if>
<!-- End of #2 -->
</core:if>
<!-- ** End of Action Item responses ** -->
</gel:forEach>
<!-- ** End of IDLoop ** -->
</core:if>
<!-- ** End of pexep nest**-->
<!-- ** If there are exceptions while reading the file ** -->
<core:if test="${pexep != null}">
<core:set value="200" var="updateflag"/>
</core:if>
<!-- ** Update the inbound message status ** -->
<core:catch var="inb_upd">
<sql:update var="updinb">
update z_inbound set status=${updateflag}, statuschangedon=getdate() where inboundMessageID = ${inrow[1]}
</sql:update>
</core:catch>
<gel:log level="info" message="Z_INBOUND STATUS UPDATE FOR ${inrow[1]} = ${inb_upd}"/>
</core:forEach>
<!-- ** End of XMLLoop ** -->
</gel:script>
Thanks,
Shiva