This is for Projects only. We have a similar script for Ideas.
<gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:u="jelly:util" xmlns:util="jelly:util" xmlns:x="jelly:xml"
xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- Paramters Declaration -->
<gel:setDataSource dbId="niku" var="nikudb"/>
<!--Retrieve Project code-->
<core:catch var="v_exception">
<sql:query dataSource="${nikudb}" escapeText="0" var="v_queryresult">
SELECT inv.code prjcode,
inv.name prjname
FROM inv_investments inv
WHERE inv.id = ${gel_objectInstanceId}
</sql:query>
</core:catch>
<core:if test="${v_exception != Null}">
<gel:log level="ERROR">SQL ERROR = ${v_exception} </gel:log>
</core:if>
<!-- Query to retrieve the team members of the project -->
<core:catch var="v_exception1">
<sql:query dataSource="${nikudb}" var="to_data">
SELECT sr.unique_name rescode,
ii.code prjcode
FROM inv_investments ii,
prteam pt,
srm_resources sr,
prj_obs_associations poa,
prj_obs_units pou,
prj_obs_types obstyp,
prj_resources pres
WHERE ii.odf_object_code = 'project'
AND pt.prprojectid = ii.id
AND sr.id = pt.prresourceid
AND poa.record_id = sr.id
AND poa.table_name = 'SRM_RESOURCES'
AND pou.id = poa.unit_id
AND obstyp.unique_name= 'met_dept_obs' --custom
AND pou.type_id = obstyp.id
AND pres.prid = sr.id
AND pres.prisrole = 0
AND sr.resource_type = 0
AND pt.rsf_obsunit_id is null
AND ii.id = ?
<sql:param value="${gel_objectInstanceId}"/>
</sql:query>
</core:catch>
<core:if test="${v_exception1 != Null}">
<gel:log level="ERROR">SQL ERROR = ${v_exception1} </gel:log>
</core:if>
<core:set value="" var="comma"/>
<core:set value="" var="resids"/>
<core:forEach items="${to_data.rowsByIndex}" var="rowasgn">
<core:set value="${resids}${comma}${rowasgn[0]}" var="resids"/>
<core:set value="," var="comma"/>
</core:forEach>
<gel:log level="INFO">Resource IDs: ${resids}</gel:log>
<core:catch var="v_exception2">
<sql:update dataSource="${nikudb}" var="to_data">
MERGE INTO prteam t
USING
(SELECT pou.id obsid,
ii.id invid,
pt.prid teamid,
pt.prresourceid resid
FROM inv_investments ii,
prteam pt,
srm_resources sr,
prj_obs_associations poa,
prj_obs_units pou,
prj_obs_types obstyp,
prj_resources pres
WHERE ii.odf_object_code = 'project'
AND pt.prprojectid = ii.id
AND sr.id = pt.prresourceid
AND poa.record_id = sr.id
AND poa.table_name = 'SRM_RESOURCES'
AND pou.id = poa.unit_id
AND obstyp.unique_name = 'met_dept_obs' --custom
AND pou.type_id = obstyp.id
AND pres.prid = sr.id
AND pres.prisrole = 0
AND sr.resource_type = 0
AND pt.rsf_obsunit_id IS NULL
AND ii.id = ?) q
ON (t.prprojectid = q.invid AND t.prresourceid = q.resid AND t.prid = q.teamid)
WHEN MATCHED THEN UPDATE
SET t.rsf_obsunit_id = q.obsid
WHERE 1=1
AND t.rsf_obsunit_id IS NULL
<sql:param value="${gel_objectInstanceId}"/>
</sql:update>
</core:catch>
<core:if test="${v_exception2 != Null}">
<gel:log level="ERROR">SQL ERROR = ${v_exception2} </gel:log>
</core:if>
</gel:script>