Clarity

 View Only
  • 1.  Staff OBS = Resource OBS

    Posted Dec 19, 2016 10:06 AM

    Hello,

     

    is there a way to add Resource OBS column to the Team - Staff - Layout on a project?

     

    If not, has somebody created process or job which fills Staff OBS with value from Resource OBS?



  • 2.  Re: Staff OBS = Resource OBS

    Posted Dec 19, 2016 10:49 AM

    We've created a process that populates the Staff OBS from the Resource's OBS only if it is NULL.  While I would generally not consider this best practice, we needed to fulfill business need.  The job can be run on demand by the user by selecting one or more team members and then executing the process from the actions tab, or, we have setup a nightly job that executes the process for all projects.



  • 3.  Re: Staff OBS = Resource OBS
    Best Answer

    Posted Dec 19, 2016 10:52 AM

    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>



  • 4.  Re: Staff OBS = Resource OBS

    Posted Jan 29, 2018 11:51 AM

    Hi Peter,

     

    I want to do the same thing updating the Staff OBS Unit to Resource OBS when its empty in a project. But I am having an error with your 2nd query with the merge function. Is your process worked fine in your environment ?

     

    Thank you in advance to your response

     

    Regards,

    Karim



  • 5.  Re: Staff OBS = Resource OBS

    Posted Jan 30, 2018 02:35 AM

    Karim, database updates are not supported, don't use the above script.

     

    Updating the Staff OBS on the Project Team is possible via GEL script using XOG, but does require it to be undertaken via an XML template, as you need to pull out the Hard Allocation and Allocation 'curves', which if you don't, will be lost if you just update the Project Team record.  Please search again through the community, as I believe I have previously posted how to achieve this.