Clarity

I am banging my head today - how can I "Add Overall Status (indicator - from Status Report object) to Project List"

NJ

We have a process that "copies up" all major dimensions/data from SRs at SR completion. Having all this information "in the bank" on the project object is one of the best things we've ever done with the tool.

Can ACAs do more than aggregate? I'd love to be able to simply write some logic into an attribute to have it pull data without having to use a process. I've been kicking a JSON attribute idea around in my head for awhile. If you work anything out let me know.

"Can ACAs do more than aggregate? I'd love to be able to simply write some logic into an attribute to have it pull data without having to use a process. I've been kicking a JSON attribute idea around in my head for awhile. If you work anything out let me know." - Agree

I will check on that

Happy Friday, and have a great weekend, Rob

Regards

NJ

Here's our copy GEL. We're running a custom SR sub-object (we had ours before CA had theirs). 15.x will change things, we'll roll 'back in the box' with our 15.x upgrade, but conceptually, 'we want current SR data on the project object' will probably not change - it's a great thing to have (Gartner's PPM Sweet Spot).

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:jxml="jelly:xml"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql" xmlns:util="jelly:util"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <gel:parameter default="your_xog_username" var="xogUsername"/>
    <gel:parameter default="your_xog_password" secure="true" var="xogPassword"/>
    <!-- Get status report data -->
    <gel:setDataSource dbId="niku"/>
    <sql:query escapeText="false" var="data">
               <![CDATA[SELECT
               I.NAME INV_NAME
               , I.CODE INV_CODE
               , ISNULL(SR.STATUS_INDICATOR, 0) AS STATUS_INDICATOR
               , ISNULL(SR.STATUS_COMMENT, 'No status report filed for period.') AS STATUS_COMMENT
               , SR.OSUMC_COST_IND
               , SR.OSUMC_COST_COM
               , SR.OSUMC_RESOURCE_IND
               , SR.OSUMC_RESOURCE_COM
               , SR.OSUMC_SCHEDULE_IND
               , SR.OSUMC_SCHEDULE_COM
               , SR.OSUMC_SCOPE_IND
               , SR.OSUMC_SCOPE_COM
               , SR.OSUMC_ACCOMPLISH
               , SR.OSUMC_BRAVOS
               , SR.OSUMC_UPCOMING_ACT
               , CONVERT(VARCHAR(10), SR.CREATED_DATE, 120) AS CREATED_DATE
               FROM ODF_CA_OSUMC_PROJECT_SR SR
               JOIN INV_INVESTMENTS I ON SR.ODF_PARENT_ID = I.ID
               WHERE SR.ID = ${gel_objectInstanceId}]]>
 </sql:query>
    <!--  Get xog URL from application by fpena -->
    <core:invokeStatic
        className="com.niku.union.config.ConfigurationManager"
        method="getInstance" var="config"/>
    <core:set var="v_IsLocal">0</core:set>
    <core:set
        value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
    <core:if test="${WebServerid == 'app'}">
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslEntryUrl()}" var="EntryURL"/>
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslPort()}" var="Port"/>
        <core:if test="${EntryURL == 'http://localhost' }">
            <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
            <core:set var="v_IsLocal">1</core:set>
            <core:set var="AddressIp">${EntryURL}</core:set>
        </core:if>
    </core:if>
    <core:if test="${WebServerid == 'nsa'}">
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
        <core:set
            value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
        <core:if test="${EntryURL == 'http://localhost' }">
            <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
            <core:set var="v_IsLocal">1</core:set>
            <core:set var="AddressIp">${EntryURL}</core:set>
        </core:if>
    </core:if>
    <core:if test="${Port != null }">
        <core:set value="${EntryURL}" var="EntryURL"/>
    </core:if>
    <core:if test="${v_IsLocal == 0 }">
        <core:if test="${AddressIp != '' }">
            <core:set value="http://${AddressIp}" var="EntryURL"/>
        </core:if>
    </core:if>
    <core:set
        value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
    <core:set value="${EntryURL}/niku/xog" var="xogURL"/>
    <!-- Log into xog and get a session ID -->
    <soap:invoke endpoint="${xogURL}" var="auth">
        <soap:message>
            <soapenv:Envelope
                xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                <soapenv:Header/>
                <soapenv:Body>
                    <xog:Login>
                        <xog:Username>${xogUsername}</xog:Username>
                        <xog:Password>${xogPassword}</xog:Password>
                    </xog:Login>
                </soapenv:Body>
            </soapenv:Envelope>
        </soap:message>
    </soap:invoke>
    <!-- Checking whether a sessionID is returned. If not, it means that login was unsuccessful -->
    <gel:set asString="true"
        select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
    <core:choose>
        <core:when test="${sessionID == null}">
            <gel:log>Couldn't log in. Please check the hostname/username/password.</gel:log>
        </core:when>
        <core:otherwise>
            <gel:log>Login successful. Session ID is ${sessionID}</gel:log>
        </core:otherwise>
    </core:choose>
    <!-- Xog in risk, issue and change request edit permissions on the parent object for the Assigned To user -->
    <soap:invoke endpoint="${xogURL}" 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>
                    <NikuDataBus
                        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
                        <Header action="write" externalSource="NIKU"
                            objectType="project" version="6.0.11"/>
                        <Projects>
                            <Project name="${data.rows.0.inv_name}"
                                projectID="${data.rows.0.inv_code}"
                                statusComment="${data.rows.0.status_comment}" statusIndicator="${data.rows.0.status_indicator}">
                                <Resources/>
                                <CustomInformation>
                                    <ColumnValue name="osumc_cost_ind">${data.rows.0.osumc_cost_ind}</ColumnValue>
                                    <ColumnValue name="osumc_cost_com">${data.rows.0.osumc_cost_com}</ColumnValue>
                                    <ColumnValue name="osumc_resource_ind">${data.rows.0.osumc_resource_ind}</ColumnValue>
                                    <ColumnValue name="osumc_resource_com">${data.rows.0.osumc_resource_com}</ColumnValue>
                                    <ColumnValue name="osumc_schedule_ind">${data.rows.0.osumc_schedule_ind}</ColumnValue>
                                    <ColumnValue name="osumc_schedule_com">${data.rows.0.osumc_schedule_com}</ColumnValue>
                                    <ColumnValue name="osumc_scope_ind">${data.rows.0.osumc_scope_ind}</ColumnValue>
                                    <ColumnValue name="osumc_scope_com">${data.rows.0.osumc_scope_com}</ColumnValue>
                                    <ColumnValue name="osumc_accomplish">${data.rows.0.osumc_accomplish}</ColumnValue>
                                    <ColumnValue name="osumc_bravos">${data.rows.0.osumc_bravos}</ColumnValue>
                                    <ColumnValue name="osumc_upcoming_act">${data.rows.0.osumc_upcoming_act}</ColumnValue>
                                    <ColumnValue name="osumc_current_sr_cd">${data.rows.0.created_date}</ColumnValue>
                                    <ColumnValue name="osumc_current_sr_id">${gel_objectInstanceId}</ColumnValue>
                                </CustomInformation>
                            </Project>
                        </Projects>
                    </NikuDataBus>
                </soapenv:Body>
            </soapenv:Envelope>
        </soap:message>
    </soap:invoke>
    <!-- Log out of xog -->
    <soap:invoke endpoint="${xogURL}" var="logoutresult">
        <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>
                    <xog:Logout/>
                </soapenv:Body>
            </soapenv:Envelope>
        </soap:message>
    </soap:invoke>
    <!-- comment out below log output once in production  -->
    <gel:log> ${xogURL}, ${xogUsername}, ${xogPassword}, ${sessionID}, ${gel_objectInstanceId}, ${data.rows.0.inv_name}, ${data.rows.0.inv_code}, ${data.rows.0.status_indicator}, ${data.rows.0.status_comment}, ${data.rows.0.osumc_cost_ind}, ${data.rows.0.osumc_cost_com}, ${data.rows.0.osumc_resource_ind}, ${data.rows.0.osumc_resource_com}, ${data.rows.0.osumc_schedule_ind}, ${data.rows.0.osumc_schedule_com}, ${data.rows.0.osumc_scope_ind}, ${data.rows.0.osumc_scope_com}, ${data.rows.0.created_date}</gel:log>
</gel:script>

We love the reliability of the process engine so much we've given this a little stored procedure friend.

CREATE PROCEDURE [Niku].[Z_OSUWMC_PROJECT_STATUS_REPORT_COPY_BACK_BACKSTOP_SP]
(
@P_JOB_RUN_ID NUMERIC,
@P_JOB_USER_ID NUMERIC
)
AS
BEGIN

UPDATE INV_INVESTMENTS
SET
STATUS_INDICATOR = ISNULL(SR.STATUS_INDICATOR, 0)
, STATUS_COMMENT = ISNULL(SR.STATUS_COMMENT,  'No status report filed for period.')

FROM INV_INVESTMENTS I
JOIN ODF_CA_PROJECT IA ON IA.ID = I.ID
JOIN (SELECT MAX_SR.ODF_PARENT_ID,
     MAX(MAX_SR.ID) MAX_SR_ID
     , MAX_SR.CREATED_DATE
     FROM ODF_CA_OSUMC_PROJECT_SR MAX_SR
     GROUP BY
     MAX_SR.ODF_PARENT_ID
     , MAX_SR.CREATED_DATE) MAX_SR ON MAX_SR.ODF_PARENT_ID = I.ID
JOIN ODF_CA_OSUMC_PROJECT_SR SR ON SR.ID = MAX_SR.MAX_SR_ID
WHERE I.IS_ACTIVE = 1
AND (MAX_SR.MAX_SR_ID > IA.OSUMC_CURRENT_SR_ID) OR ((IA.OSUMC_CURRENT_SR_ID IS NULL) AND (MAX_SR.MAX_SR_ID IS NOT NULL))


UPDATE ODF_CA_PROJECT
SET
OSUMC_COST_IND = ISNULL(SR.OSUMC_COST_IND, 0)
, OSUMC_COST_COM = SR.OSUMC_COST_COM
, OSUMC_RESOURCE_IND = ISNULL(SR.OSUMC_RESOURCE_IND, 0)
, OSUMC_RESOURCE_COM = SR.OSUMC_RESOURCE_COM
, OSUMC_SCHEDULE_IND = ISNULL(SR.OSUMC_SCHEDULE_IND, 0)
, OSUMC_SCHEDULE_COM = SR.OSUMC_SCHEDULE_COM
, OSUMC_SCOPE_IND = ISNULL(SR.OSUMC_SCOPE_IND, 0)
, OSUMC_SCOPE_COM = SR.OSUMC_SCOPE_COM
, OSUMC_ACCOMPLISH = SR.OSUMC_ACCOMPLISH
, OSUMC_BRAVOS = SR.OSUMC_BRAVOS
, OSUMC_UPCOMING_ACT = SR.OSUMC_UPCOMING_ACT
, OSUMC_CURRENT_SR_ID = MAX_SR.MAX_SR_ID
, OSUMC_CURRENT_SR_CD = SR.CREATED_DATE

FROM INV_INVESTMENTS I
JOIN ODF_CA_PROJECT IA ON IA.ID = I.ID
JOIN (SELECT MAX_SR.ODF_PARENT_ID,
     MAX(MAX_SR.ID) MAX_SR_ID
     , MAX_SR.CREATED_DATE
     FROM ODF_CA_OSUMC_PROJECT_SR MAX_SR
     GROUP BY
     MAX_SR.ODF_PARENT_ID
     , MAX_SR.CREATED_DATE) MAX_SR ON MAX_SR.ODF_PARENT_ID = I.ID
JOIN ODF_CA_OSUMC_PROJECT_SR SR ON SR.ID = MAX_SR.MAX_SR_ID
WHERE I.IS_ACTIVE = 1
AND (MAX_SR.MAX_SR_ID > IA.OSUMC_CURRENT_SR_ID) OR ((IA.OSUMC_CURRENT_SR_ID IS NULL) AND (MAX_SR.MAX_SR_ID IS NOT NULL))

END

GO

Wow

Thanks for sharing these

Regards

NJ