Clarity

Expand all | Collapse all

Bulk upload of timesheets?

Jump to Best Answer
  • 1.  Bulk upload of timesheets?

    Posted 05-16-2016 06:08 AM

    My company has around 100 users who's time sheets will almost always be exactly the same week to week. We wanted to upload these in bulk so that they didn't need to do this so am trying to find out the best way to go about this from a position of knowing very little about the system!

     

    Browsing some of the knowledge base articles it seems like this should be possible with the XOG, should I be looking at any other methods?

     

    In terms of using XOG would there be any other prerequisites than the below?

     

    XOG client

    Administration - Access

    Administration - XOG

    Knowledge of XML?

     

    I have to say from what I read it seems like XOG is probably quite a steep learning curve.



  • 2.  Re: Bulk upload of timesheets?

    Posted 05-16-2016 08:14 AM


  • 3.  Re: Bulk upload of timesheets?

    Posted 05-16-2016 08:15 AM


  • 4.  Re: Bulk upload of timesheets?

    Posted 05-17-2016 11:31 AM

    Many thanks, I had had a look at some of those, will go through the others you have included.



  • 5.  Re: Bulk upload of timesheets?
    Best Answer

    Posted 05-16-2016 08:14 AM

    You are correct, XOG is the way to do this.

     

    Look at the 'prj_timeperiods_write.xml' file provided with the XOG client downloads from the application for example on how you need to format the XML to write timesheet(s).



  • 6.  Re: Bulk upload of timesheets?

    Posted 05-17-2016 11:30 AM

    Many thanks, I had a look at that template. I'll read through some of the other posts just to get a better idea of it all.



  • 7.  Re: Bulk upload of timesheets?

    Posted 05-17-2016 02:30 PM

    You can XOG in timesheets.  I wrote a whole process if you're interested.



  • 8.  Re: Bulk upload of timesheets?

    Posted 05-20-2016 05:27 AM

    Hi Lowell, thanks for your reply. Yes I would be interested in what you did. I'm still looking into it all so any guidance is appreciated.



  • 9.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:02 PM

    Hi -

    I had some trouble getting back to this conversation.

     

    How do I attach text files?  I see others have some "inserts" in their discussions or attachments.  But, I can't find that option in my menu.

     

    Thanks,

    Lowell



  • 10.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:03 PM

    Here is import of data into a staging table:

     

     

    <gel:script xmlns:core="jelly:core"
        xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
        xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
        xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <!--Parameters section-->
        <gel:parameter default="True" var="PUseToday"/>
        <gel:parameter default="Import_Hours_01112013.csv" var="PFileName"/>
        <gel:parameter default="Import_Hours" var="PFileType"/>
        <gel:parameter default="\\fileshare\filefolder\file" var="PLocalDirectory"/>
        <gel:parameter default="TriZ-Gen" var="PooledResource"/>
        <gel:parameter default="5231225" var="PooledResource_ID"/>
        <gel:parameter default="True" var="PoolHours"/>
       

        <!--PoolHours variable sets all hours to roll up to PooledResource -->
        <core:choose>
            <core:when test="${PUseToday}">
                <gel:setDataSource dbId="Niku"/>

                <sql:query var="vnow_qry">
                 select to_char(sysdate -1,'MMDDYYYY') v_now from dual
               </sql:query>
                <core:set var="vnow">${vnow_qry.rows[0].v_now}</core:set>
                <core:set value="${PFileType}_${vnow}.csv" var="vFile"/>
                <gel:out>"${vFile}"</gel:out>
    <!--            <gel:log level="INFO">Date Set vnow: ${vnow}</gel:log>
                <gel:log level="INFO">Date Set v_now: ${vnow_qry.v_now}</gel:log>
                <gel:log level="INFO">Date Set v_now: ${vnow_qry.rows[0].v_now}</gel:log>-->
            </core:when>
            <core:otherwise>
                <core:set value="${PFileName}" var="vFile"/>
            </core:otherwise>
        </core:choose>
        <core:catch var="ex">
            <file:readFile commentIndicator="#" delimiter=","
                embedded="false" fileName="${PLocalDirectory}/${vFile}" var="input"/>
        </core:catch>
        <gel:log level="INFO">Reading file named - ${PLocalDirectory}/${vFile}</gel:log>
        <!--Verify that a file was obtained and load into staging table-->
        <core:choose>
            <core:when test="${ex == null}">
                <gel:setDataSource dbId="Niku"/>
                <!--Delete records from last run-->
            <sql:update>
               delete from trg_trz_time_stage
            </sql:update>

        <!--PoolHours variable sets all hours to roll up to PooledResource -->
        <core:choose>
            <core:when test="${PoolHours}">
                <gel:log level="INFO">select R.id from srm_resources R where R.UNIQUE_NAME = '${PooledResource}'</gel:log>
                <sql:query var="v_pooledresource">
                 select R.id from srm_resources R where R.UNIQUE_NAME = '${PooledResource}'
                </sql:query>
             ${v_pooledresource.rows[0].id}
                <core:set value="${v_pooledresource.rows[0].id}" var="PooledResourceID"/>
                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
            </core:when>
            <core:otherwise>
             <!--Set Pooled Resource ID manually from variable if query fails-->
                <core:set value="${PooledResource_ID}" var="PooledResourceID"/>
                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
            </core:otherwise>
        </core:choose>

                <gel:log level="INFO">"v_pooledresource.rows0.id ${v_pooledresource.rows[0].id}"</gel:log>
                <gel:log level="INFO">"PooledResourceID ${PooledResourceID}"</gel:log>
                <gel:log level="INFO">"PooledResource ${PooledResource}"</gel:log>${PooledResource}

                <core:forEach begin="0" items="${input.rows}" trim="true" var="row">
                    <core:catch var="ex2">
    <gel:log level="INFO">"row0: ${row[0]}"</gel:log><gel:log level="INFO">"row1: ${row[1]}"</gel:log><gel:log level="INFO">"row2: ${row[2]}"</gel:log><gel:log level="INFO">"row3: ${row[3]}"</gel:log><gel:log level="INFO">"row4: ${row[4]}"</gel:log><gel:log level="INFO">"row5: ${row[5]}"</gel:log><gel:log level="INFO">"row6: ${row[6]}"</gel:log><gel:log level="INFO">"row7: ${row[7]}"</gel:log><gel:log level="INFO">"row8: ${row[8]}"</gel:log><gel:log level="INFO">"row9: ${row[9]}"</gel:log><gel:log level="INFO">"row10: ${row[10]}"</gel:log><gel:log level="INFO">"row11: ${row[11]}"</gel:log><gel:log level="INFO">"row12: ${row[12]}"</gel:log><gel:log level="INFO">"row13: ${row[13]}"</gel:log><gel:log level="INFO">"row14: ${row[14]}"</gel:log><gel:log level="INFO">"row15: ${row[15]}"</gel:log><gel:log level="INFO">"row16: ${row[16]}"</gel:log><gel:log level="INFO">"row17: ${row[17]}"</gel:log><gel:log level="INFO">"row18: ${row[18]}"</gel:log><gel:log level="INFO">"row19: ${row[19]}"</gel:log><gel:log level="INFO">"PooledResourceID: ${PooledResourceID}"</gel:log><gel:log level="INFO">"PooledResource: ${PooledResource}"</gel:log>

                        <sql:update>
                 INSERT INTO trg_trz_time_stage
                (FIRST_NAME,LAST_NAME,TRZ_RESOURCE_ID,PCR,Week_Start,Week_end,day_1,day_1_hours,day_2,day_2_hours,day_3,day_3_hours,day_4,day_4_hours,day_5,day_5_hours,day_6,day_6_hours,day_7,day_7_hours,prresourceid,resourceid,load_date)
                 VALUES (?,?,?,?,to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,?,?,sysdate)
                            <sql:param value="${row[0]}"/>
                            <sql:param value="${row[1]}"/>
                            <sql:param value="${row[2]}"/>
                            <sql:param value="${row[3]}"/>
                            <sql:param value="${row[4]}"/>
                            <sql:param value="${row[5]}"/>
                            <sql:param value="${row[6]}"/>
                            <sql:param value="${row[7]}"/>
                            <sql:param value="${row[8]}"/>
                            <sql:param value="${row[9]}"/>
                            <sql:param value="${row[10]}"/>
                            <sql:param value="${row[11]}"/>
                            <sql:param value="${row[12]}"/>
                            <sql:param value="${row[13]}"/>
                            <sql:param value="${row[14]}"/>
                            <sql:param value="${row[15]}"/>
                            <sql:param value="${row[16]}"/>
                            <sql:param value="${row[17]}"/>
                            <sql:param value="${row[18]}"/>
                            <sql:param value="${row[19]}"/>
                            <sql:param value="${PooledResourceID}"/>
                            <sql:param value="${PooledResource}"/>
                        </sql:update>
                    </core:catch>
                    <gel:out>${ex2}</gel:out>
                </core:forEach>
                <sql:query var="vrec_count">
                 select count(*) as num
                 from trg_trz_time_stage
               </sql:query>
                <gel:log level="INFO">Number of records insert into trg_trz_time_stage -- "${vrec_count.rows[0].num}"</gel:log>
            </core:when>
            <core:otherwise>
                <!--Log the exception-->
                <gel:log level="INFO">Error locating or reading file -- "${ex}"</gel:log>
            </core:otherwise>
        </core:choose>
    </gel:script>



  • 11.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:05 PM

    Email warnings for duplicate entries in data:

     

    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--Parameters section-->
      <gel:parameter default="reciptient@recipient.com" var="email_addresses"/>
      <!-- The script sends email to recipient


                      CUSTOMER:

                       AUTHOR: Lowell Wetzel

                       DATE:  2/1/2013
        -->
      <gel:parse file="../config/properties.xml" var="properties"/>
      <gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@entryUrl" var="eURL"/>
      <gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@sslEntryUrl" var="ssleURL"/>
      <gel:log>Entry URL = ${eURL}</gel:log>
      <gel:log>SSL Entry URL  = ${ssleURL}</gel:log>
      <gel:setDataSource dbId="Niku"/>
      <gel:log level="INFO">"Fixing Duplicate Tasks per PCR"</gel:log>
      <!--Query for Duplicate Count-->
      <sql:query escapeText="False" var="count_duplicaterecords">                
              Select count(T.PRNAME) Duplicates
              from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
              inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
              inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
              from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
              inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
              Where T.IS_OPEN_TE = 1
              AND TSK.TRG_PCR is not null
              AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
              group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
              WHERE DUP.Duplicate &gt; 1
              ORDER BY TSK.TRG_PCR
            </sql:query>
      <gel:log level="INFO">"PCR Duplicates: ${count_duplicaterecords.rows[0].Duplicates}"</gel:log>
      <!--Reset emailtextbody variable for each pass-->
      <core:set value="" var="emailtextbody"/>
      <core:choose>
        <core:when test="${count_duplicaterecords.rows[0].Duplicates > 1}">
          <!--Query Project Name, Project ID, Task Name, Task ID, PCR -->
          <sql:query escapeText="False" var="v_duplicaterecords">                
            Select P.NAME, P.UNIQUE_NAME, P.ID, T.PRID, T.PRNAME, T.PREXTERNALID, TSK.TRG_PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
              inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
              inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
              from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
              inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
              Where T.IS_OPEN_TE = 1
              AND TSK.TRG_PCR is not null
              AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
              group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
              WHERE DUP.Duplicate &gt; 1
              ORDER BY TSK.TRG_PCR
        </sql:query>
          <!--Loop through duplicates and build email variable. -->
          <core:forEach begin="0" items="${v_duplicaterecords.rows}" trim="true" var="pcr_row">
            <core:set escapeText="false" trim="false"
              value="${emailtextbody} &lt;br> &lt;br> -------------------------------------------------------------------- &lt;br> To access this Project Task with Duplicate Work Order, click on : ${eURL}/niku/app?action=projmgr.taskProperties&amp;id=${pcr_row.prid}&amp;returnAction=projmgr.keyTaskListReturn&amp;projectId=${pcr_row.id}  &lt;br> &lt;br>    Project Name:   ${pcr_row.NAME} &lt;br>      Project ID:   ${pcr_row.UNIQUE_NAME} &lt;br>       Task Name:   ${pcr_row.PRNAME} &lt;br>         Task ID:   ${pcr_row.PREXTERNALID} &lt;br> Task Work Order:   ${pcr_row.TRG_PCR} &lt;br> &lt;br> Please, fix the PCR number on this Active task. &lt;br> " var="emailtextbody"/>
            <gel:log level="INFO">${emailtextbody}</gel:log>
          </core:forEach>
          <sql:update escapeText="False">
    update (select TRZ.PCR, TRZ.DUPLICATE from TRG_TRZ_TIME_STAGE TRZ
    WHERE TRZ.PCR in (Select DISTINCT TSK.TRG_PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
                 inner join ODF_CA_TASK tsk on TSK.ID = T.PRID
                 inner join (select count(TSK.TRG_PCR) Duplicate, TSK.TRG_PCR PCR
                 from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
                 inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
                 Where T.IS_OPEN_TE = 1
                 AND TSK.TRG_PCR is not null
                 AND TSK.TRG_PCR IN (select distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ)
                 group by TSK.TRG_PCR) DUP on DUP.PCR = TSK.TRG_PCR
                 WHERE DUP.Duplicate &gt; 1))
    SET Duplicate = 1
        </sql:update>
          <core:catch var="v_mail_exception">
            <gel:email from="clarity@ca.com"
              subject="The following ${count_duplicaterecords.rows[0].Duplicates} Work Orders are assigned to more than 1 task" to="${email_addresses}" trim="false">
    The following ${count_duplicaterecords.rows[0].Duplicates} Work Orders are assigned to more than 1 task.

    ${emailtextbody}

               </gel:email>
          </core:catch>
          <core:otherwise>
            <!--Log the exception-->
            <gel:log level="INFO">Error Emailing email@email.com -- "${ex}"</gel:log>
          </core:otherwise>
        </core:when>
        <otherwise>
          <gel:log level="INFO">No Duplicates</gel:log>
        </otherwise>
      </core:choose>
    </gel:script>



  • 12.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:06 PM

    Here to find Resources with no tasks:

     

     

    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--Parameters section-->
      <gel:parameter default="recipient@recipient.com" var="email_addresses"/>
      <!-- The script finds PCRs not associated with tasks and sends notification email to recipient@recipient.com


                      CUSTOMER:

                       AUTHOR: Lowell Wetzel

                       DATE:  2/1/2013
        -->
      <gel:parse file="../config/properties.xml" var="properties"/>
      <gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@entryUrl" var="eURL"/>
      <gel:set asString="true" select="$properties/properties/webServer/webServerInstance/@sslEntryUrl" var="ssleURL"/>
      <gel:log>Entry URL = ${eURL}</gel:log>
      <gel:log>SSL Entry URL  = ${ssleURL}</gel:log>
      <gel:setDataSource dbId="Niku"/>
      <gel:log level="INFO">"Fixing PCRs with no Task"</gel:log>
      <!--Query for Duplicate Count-->
      <sql:query escapeText="False" var="count_nullrecords">                
              Select count(TRI.PCR) Duplicates FROM
         (SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
          inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
         WHERE PC.PCR is null) TRI
            </sql:query>
      <gel:log level="INFO">"PCR Nulls: ${count_nullrecords.rows[0].Duplicates}"</gel:log>
      <!--Reset emailtextbody variable for each pass-->
      <core:set value="" var="emailtextbody"/>
      <core:choose>
        <core:when test="${count_nullrecords.rows[0].Duplicates > 0}">
          <!--Query PCR with no Project or Task -->
          <sql:query escapeText="False" var="v_nullrecords">                
         SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
          inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
         WHERE PC.PCR is null
        </sql:query>
          <!--Loop through duplicates and build email variable. -->
          <core:forEach begin="0" items="${v_nullrecords.rows}" trim="true" var="pcr_row">
            <core:set escapeText="false" trim="false"
              value="${emailtextbody} &lt;br> &lt;br> -------------------------------------------------------------------- &lt;br> The Following Work Order has no Task associated with it:  &lt;br> &lt;br> Work Order:   ${pcr_row.PCR} &lt;br> &lt;br> Please, fix the Work Order number on this Active task. &lt;br> " var="emailtextbody"/>
            <gel:log level="INFO">${emailtextbody}</gel:log>
          </core:forEach>
          <sql:update escapeText="False">
       update (select TRZ.PCR, TRZ.DUPLICATE from TRG_TRZ_TIME_STAGE TRZ
       WHERE TRZ.PCR in (SELECT distinct TRZ.PCR from TRG_TRZ_TIME_STAGE TRZ left outer join (Select TSK.TRG_PCR PCR from srm_projects p inner join prtask t on T.PRPROJECTID = P.ID
        inner join ODF_CA_TASK tsk on TSK.ID = T.PRID) PC on TRZ.PCR = PC.PCR
       WHERE PC.PCR is null))
       SET Duplicate = 1
        </sql:update>
          <core:catch var="v_mail_exception">
            <gel:email from="clarity@regence.com" subject="The following ${count_nullrecords.rows[0].Duplicates} Work Orders are orphans."
              to="${email_addresses}" trim="false">

    The following ${count_nullrecords.rows[0].Duplicates} Work Orders are orphans.

    ${emailtextbody}

               </gel:email>
          </core:catch>
          <core:otherwise>
            <!--Log the exception-->
            <gel:log level="INFO">Error Emailing ITVendorManagement@regence.com -- "${v_mail_exception}"</gel:log>
          </core:otherwise>
        </core:when>
        <otherwise>
          <gel:log level="INFO">No Null Tasks</gel:log>
        </otherwise>
      </core:choose>
    </gel:script>



  • 13.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:07 PM

    *command to execute stored procedure

    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:rem="jelly:com.niku.union.gel.RemedyTagLibrary" xmlns:remedy="urn:HelpDesk_Query_Service" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
      xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <gel:setDataSource dbId="niku"/>
      <!--Run Data Massage for values-->
      <!--Execute timesheet data manipulation stored procedure-->
      <sql:update>call SP_name()
       </sql:update>
      <!--Inform of Stored Procedure Finish-->
      <gel:log level="INFO">SP_name Completed.</gel:log>
    </gel:script>



  • 14.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:07 PM

    Stored procedure:

     

    CREATE OR REPLACE PROCEDURE NIKU.SP_name
       AS
    /******************************************************************************
       NAME:       SP_name
       PURPOSE:    To fill in the needed fields prior to XOG of TriZetto Timesheet

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        1/20/2013   r616330       1. Created this procedure.

       NOTES:
    --SP_name created by Lowell Wetzel 01/12/2013
    --This populates the columns needed in order to XOG timesheet values into the system

       Automatically available Auto Replace Keywords:
          Object Name:     SP_name
          Sysdate:         1/20/2013
          Date and Time:   1/20/2013, 7:08:40 PM, and 1/20/2013 7:08:40 PM
          Username:       
          Table Name:      Table_name

    ******************************************************************************/

    --First Populate the PRTIMEPERIOD
    cursor c_timeperiod is
      select WEEK_START
      from TRG_TRZ_TIME_STAGE
      where WEEK_START is not null;

    --Change in Requirements removed "TriZetto resources" from Cambia system
    --Removed, Resource ID is a "static" Pooled Resource and all hours are to be grouped into this pooled resource
    --Next Populate the PRRESOURCEID
    --cursor c_prresourceid is
    --  select RESOURCEID
    --  from TRG_TRZ_TIME_STAGE
    --  where RESOURCEID is not null;

    --Change in Requirements populates "project_id" from PCR (Work Order) value
    --Next Populate the PRPROJECTID
    cursor c_prproject is
      select PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID, PCR
      from TRG_TRZ_TIME_STAGE
      where PCR is not null;

    --Next Populate the TASKINTERNALTASKID, TASKPRCHARGECODEID
    --Removed due to values being set by PCR (Work Order) value
    --cursor c_prtask is
    --  select TASK_ID, PROJECT_ID
    --  from TRG_TRZ_TIME_STAGE
    --  where TASKID is not null
    --  and PRPROJECTID is not null;

    --Next Populate the TASKCHARGECODEID
    cursor c_prchargecode is
      select TASKPRCHARGECODEID
      from TRG_TRZ_TIME_STAGE
      where TASKPRCHARGECODEID is not null;

    --Next Populate the ASSIGNMENTID
    cursor c_prassignment is
      select TASKINTERNALTASKID, PRRESOURCEID
      from TRG_TRZ_TIME_STAGE
      where TASKINTERNALTASKID is not null
      and PRRESOURCEID is not null;

    --Next Populate the PR_ROLE
    cursor c_pr_role is
      select PRROLE_ID
      from TRG_TRZ_TIME_STAGE
      where PRROLE_ID is not null;

    --Next Populate the TIMESHEETID
    cursor c_timesheetid is
      select PRTIMEPERIODID, PRRESOURCEID
      from TRG_TRZ_TIME_STAGE
      where PRTIMEPERIODID is not null
      and PRRESOURCEID is not null;

    --Next Populate the TIMESHEETID
    cursor c_timeshtstatver is
      select TIMESHEETID
      from TRG_TRZ_TIME_STAGE
      where TIMESHEETID is not null;

    --Finally, Populate TYPECODEID, TYPECODENAME
    cursor c_typecode is
      select PRRESOURCEID
      from TRG_TRZ_TIME_STAGE
      where PRRESOURCEID is not null;

    BEGIN

    --PRTIMEPERIOD
    for c_timeperiod_rec in c_timeperiod loop
    UPDATE (select T.week_start, T.prtimeperiodid FROM TRG_TRZ_TIME_STAGE T where T.week_start = c_timeperiod_rec.WEEK_START)
    SET PRTIMEPERIODID = (select P.prid from prtimeperiod P where P.prstart = c_timeperiod_rec.WEEK_START);
    end loop;

    COMMIT;

    --RESOURCEID
    --Removed Due to use of "Pooled" Resource
    --for c_prresourceid_rec in c_prresourceid loop
    --UPDATE (select T.resourceid, T.prresourceid FROM TRG_TRZ_TIME_STAGE T where T.resourceid = c_prresourceid_rec.resourceid)
    --SET PRRESOURCEID = (select R.id from srm_resources R where R.unique_name = c_prresourceid_rec.resourceid);
    --end loop;

    --COMMIT;

    --PRPROJECTID
    --Original
    --for c_prproject_rec in c_prproject loop
    --UPDATE (select T.PRPROJECTID, T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID, T.PCR FROM TRG_TRZ_TIME_STAGE T
    --where T.PCR = c_prproject_rec.PCR)
    --SET (PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID) = (select p.ID, t.PRID, t.PRCHARGECODEID
    --from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
    --inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
    --Where t.IS_OPEN_TE = 1
    --and tsk.TRG_PCR = c_prproject_rec.PCR);
    --end loop;
    for c_prproject_rec in c_prproject loop
    UPDATE (select T.PRPROJECTID, T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID, T.PCR, T.PROJECTID, T.TASKID FROM TRG_TRZ_TIME_STAGE T
    where T.PCR = c_prproject_rec.PCR)
    SET (PRPROJECTID, TASKINTERNALTASKID, TASKPRCHARGECODEID, PROJECTID, TASKID) = (select p.ID, t.PRID, t.PRCHARGECODEID, P.UNIQUE_NAME, T.PREXTERNALID
    from srm_projects p inner join prtask t on t.PRPROJECTID = p.ID
    inner join ODF_CA_TASK tsk on tsk.ID = t.PRID
    Where t.IS_OPEN_TE = 1
    and tsk.TRG_PCR = c_prproject_rec.PCR);
    end loop;

    COMMIT;

    --Removed due to tasks identified by "PCR" field
    --TASKINTERNALTASKID, TASKPRCHARGECODEID
    --for c_prtask_rec in c_prtask loop
    --UPDATE (select T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID  FROM TRG_TRZ_TIME_STAGE T
    --     where T.taskid = c_prtask_rec.TASKID and T.prprojectid = c_prtask_rec.PRPROJECTID)
    --SET (TASKINTERNALTASKID, TASKPRCHARGECODEID) = (select TSK.prid, TSK.prchargecodeid from prtask TSK
    --     WHERE TSK.prexternalid = c_prtask_rec.TASKID AND TSK.prprojectid = c_prtask_rec.PRPROJECTID);
    --end loop;

    --COMMIT;

    --TASKCHARGECODEID
    for c_prchargecode_rec in c_prchargecode loop
    UPDATE (select T.taskprchargecodeid, T.taskchargecodeid FROM TRG_TRZ_TIME_STAGE T where T.taskprchargecodeid = c_prchargecode_rec.TASKPRCHARGECODEID)
    SET TASKCHARGECODEID = (select PC.prexternalid from prchargecode PC where PC.prid = c_prchargecode_rec.TASKPRCHARGECODEID);
    end loop;

    COMMIT;

    --ASSIGNMENTID
    for c_prassignment_rec in c_prassignment loop
    UPDATE (select T.taskinternaltaskid, T.prresourceid, T.taskassignmentid, T.prrole_id FROM TRG_TRZ_TIME_STAGE T
         where T.taskinternaltaskid = c_prassignment_rec.TASKINTERNALTASKID AND T.prresourceid = c_prassignment_rec.PRRESOURCEID)
    SET (TASKASSIGNMENTID, PRROLE_ID) = (select PA.prid, PA.role_id from prassignment PA
         where PA.prtaskid = c_prassignment_rec.TASKINTERNALTASKID and PA.prresourceid = c_prassignment_rec.PRRESOURCEID);
    end loop;

    COMMIT;

    --ROLE_ID
    for c_pr_role_rec in c_pr_role loop
    UPDATE (select T.prrole_id, T.role_id FROM TRG_TRZ_TIME_STAGE T
         where T.prrole_id = c_pr_role_rec.PRROLE_ID)
    SET ROLE_ID = (select S.unique_name from PRJ_RESOURCES R, srm_resources S where S.id = R.prid
    and R.prid = c_pr_role_rec.PRROLE_ID);
    end loop;

    COMMIT;

    --TIMESHEETID
    for c_timesheetid_rec in c_timesheetid loop
    UPDATE (select T.timesheetid, T.prtimeperiodid, T.prresourceid, T.prstatus, T.prversion FROM TRG_TRZ_TIME_STAGE T
         where T.prtimeperiodid = c_timesheetid_rec.PRTIMEPERIODID AND T.prresourceid = c_timesheetid_rec.PRRESOURCEID)
    SET TIMESHEETID = (select max(TS.prid) from prtimesheet TS where TS.prtimeperiodid = c_timesheetid_rec.PRTIMEPERIODID
         and TS.prresourceid = c_timesheetid_rec.PRRESOURCEID);
    end loop;

    COMMIT;

    --Timesheet(PRSTATUS, PRVERSION)
    for c_timeshtstatver_rec in c_timeshtstatver loop
    UPDATE (select T.prstatus, T.prversion, T.timesheetid FROM TRG_TRZ_TIME_STAGE T
         where T.timesheetid = c_timeshtstatver_rec.TIMESHEETID)
    SET (PRSTATUS, PRVERSION) = (select TS.prstatus, TS.prversion from prtimesheet TS
         where TS.prid = c_timeshtstatver_rec.TIMESHEETID);
    end loop;

    COMMIT;

    --(TYPECODEID, TYPECODENAME)
    for c_typecode_rec in c_typecode loop
    UPDATE (select T.prresourceid, T.typecodeid, T.typecodename FROM TRG_TRZ_TIME_STAGE T
         where T.prresourceid = c_typecode_rec.PRRESOURCEID)
    SET (TYPECODEID, TYPECODENAME) = (SELECT P.prexternalid, P.prname
    FROM PRJ_RESOURCES R left outer join PRTYPECODE P on R.prtypecodeid = P.prid
         where R.prid = c_typecode_rec.PRRESOURCEID);
    end loop;

    COMMIT;

    EXCEPTION
       WHEN OTHERS
       THEN
          raise_application_error (-20000,
                                   'Error in SP_name - ' || SQLERRM
                                  );
    END SP_name; -- Procedure
    /



  • 15.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:10 PM

    XOG in Timesheets (& record success v/failure)

     

     

    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql"
      xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--Parameters section-->

      <gel:parameter default="admin" var="XOGUsername"/>
      <gel:parameter default="admin" secure="true" var="XOGPassword"/>
      <!--End Parameters Section-->
      <!-- Only used with GEL Serialize command at end -->
      <gel:parameter default="./outputfiles" var="PLocalDirectory"/>
      <!--vnow used for output file-->
      <core:set var="vnow">
        <gel:formatDate format="MMddyy"/>
      </core:set>
      <!-- Initialize some counters.  lastModified stores the latest Last_Modified_time. -->
      <core:set value="0" var="totalCount"/>
      <core:set value="0" var="insertCount"/>
      <core:set value="0" var="updateCount"/>
      <!-- ****************************************GET THE XOG URL FROM THE APPLICATION **********************************************-->
      <core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
      <core:set value="0" var="v_IsLocal"/>
      <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).getEntryUrl()}" var="EntryURL"/>
        <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getPort()}" var="Port"/>
        <core:if test="${EntryURL == 'http://localhost'}">
          <gel:log level="INFO" message="NULL ADDRESS IS LOCAL... "/>
          <core:set value="1" var="v_IsLocal"/>
          <core:set value="${EntryURL}" var="AddressIp"/>
        </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 value="1" var="v_IsLocal"/>
          <core:set value="${EntryURL}" var="AddressIp"/>
        </core:if>
      </core:if>
      <core:if test="${Port != null }">
        <core:set value="${EntryURL}:${Port}" 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>

      <!--Set value for XOGURLAUTO (auto detected using above code)-->
        <core:set value="${EntryURL}/niku/xog" var="XOGURLAUTO"/>
      <!-- ***************************************END GET XOG URL ***********************************-->
      <gel:log level="INFO">Make sure environment is correct: XOGURLAUTO: "${XOGURLAUTO}"</gel:log>
      <!-- Log into XOG first. -->
      <!--original    <soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">-->
      <soap:invoke endpoint="${XOGURLAUTO}" var="auth">
        <soap:message>
          <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
            <soap-env:Header/>
            <soap-env:Body>
              <xog:Login>
                <xog:Username>${XOGUsername}</xog:Username>
                <xog:Password>${XOGPassword}</xog:Password>
              </xog:Login>
            </soap-env:Body>
          </soap-env:Envelope>
        </soap:message>
      </soap:invoke>
      <!-- Checking whether a sessionID is returned.  If not, it means that Login was not successful. -->
      <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 level="ERROR">Cannot login to Clarity XOG. Check username and password.</gel:log>
        </core:when>
        <!--Otherwise-->
        <core:otherwise>
          <gel:setDataSource dbId="niku"/>
          <core:set var="vnow">
            <gel:formatDate format="yyyy-MM-dd"/>
          </core:set>
          <!--Count Rows to process-->
          <sql:query var="v_toprocess">
    select count(*) as num
    from trg_trz_time_stage t
    where
    t.timesheetid is not null
    and t.taskassignmentid is not null
    and t.prresourceid is not null
    and t.prtimeperiodid is not null
    and t.taskinternaltaskid is not null
    and (T.prstatus = 0 or T.prstatus = 2)
    AND T.DUPLICATE = 0
       </sql:query>
          <gel:log level="INFO">Number of timesheets to process: ${v_toprocess.rows[0].num}</gel:log>
          <!--Time Periods and Resource-->
          <sql:query var="v_ts1">
    SELECT distinct T.prtimeperiodid, T.prresourceid
    FROM trg_trz_time_stage T
    where
    T.timesheetid is not null
    and T.taskassignmentid is not null
    and T.prresourceid is not null
    and T.prtimeperiodid is not null
    and T.taskinternaltaskid is not null
    and (T.prstatus = 0 or T.prstatus = 2)
    and T.DUPLICATE = 0
      </sql:query>
          <!--Step1, For Each Time Period/PRResourceID-->
          <core:forEach items="${v_ts1.rows}" trim="true" var="tp1">
            <!--Parse-->
            <gel:parse var="xogBody">
              <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_timeperiod.xsd">
                <Header action="write" externalSource="NIKU" objectType="timeperiod" version="8.1.3.4545"/>
                <TimePeriods>
                  <!--First start with the Time Period -->
                  <sql:query var="v_ts2">
    SELECT to_char(prstart,'yyyy-mm-dd') || 'T00:00:00' prstart, to_char(prfinish,'yyyy-mm-dd') || 'T00:00:00' prfinish,
    to_char(prstart,'mm/dd/yyyy') vardate,
    case prisopen
    when 1 then 'true'
    when 0 then 'false' end prisopen
    FROM prtimeperiod
    where prid = ${tp1.prtimeperiodid}
          </sql:query>
                  <core:set value="${v_ts2.rows[0].vardate}" var="vtimePeriod"/>
                  <TimePeriod finish="${v_ts2.rows[0].prfinish}" openForTimeEntry="${v_ts2.rows[0].prisopen}" start="${v_ts2.rows[0].prstart}">
                    <TimeSheets>
                      <!--Step3, Query to Populate the Timesheet Header based on the Time Period from above and by Resource-->
                      <sql:query var="v_ts4">
    SELECT distinct T.timesheetid, T.resourceid, T.prresourceid, T.prstatus, T.prversion
    from trg_trz_time_stage T
    where T.prtimeperiodid = ${tp1.prtimeperiodid}
    and T.prresourceid = ${tp1.prresourceid}
    and T.taskassignmentid is not null
    and T.timesheetid is not null
    and T.taskinternaltaskid is not null
    and T.prresourceid is not null
    and T.prprojectid is not null
    and (T.prstatus = 0 or T.prstatus = 2)
    and T.DUPLICATE = 0
              </sql:query>
                      <!--Step3, Loop through to populate the time sheet by time period, by resource -->
                      <core:forEach items="${v_ts4.rows}" trim="true" var="tp3">
                        <TimeSheet ID="${tp3.timesheetid}" resourceID="${tp3.resourceid}" status="1" submittedBy="${tp3.prresourceid}" version="${tp3.prversion}">
                          <TimeSheetEntries>
                            <!--Step4, Query to Populate the Timesheets based on the Time Period from above and by Resource-->
                            <!--Step4, Resource is "Pooled", so "sum" and "group by" added to query-->
                            <sql:query var="v_ts5">
    SELECT T.PRRESOURCEID, T.PROJECTID, T.TASKID, T.PRPROJECTID, T.TASKINTERNALTASKID, to_char(T.DAY_1,'yyyy-mm-dd') DAY_1, SUM(T.DAY_1_HOURS) DAY_1_HOURS,
    to_char(T.DAY_2,'yyyy-mm-dd') DAY_2, SUM(T.DAY_2_HOURS) DAY_2_HOURS, to_char(T.DAY_3,'yyyy-mm-dd') DAY_3, SUM(T.DAY_3_HOURS) DAY_3_HOURS,
    to_char(T.DAY_4,'yyyy-mm-dd') DAY_4, SUM(T.DAY_4_HOURS) DAY_4_HOURS, to_char(T.DAY_5,'yyyy-mm-dd') DAY_5, SUM(T.DAY_5_HOURS) DAY_5_HOURS,
    to_char(T.DAY_6,'yyyy-mm-dd') DAY_6, SUM(T.DAY_6_HOURS) DAY_6_HOURS, to_char(T.DAY_7,'yyyy-mm-dd') DAY_7, SUM(T.DAY_7_HOURS) DAY_7_HOURS,
    T.TASKASSIGNMENTID, T.TASKCHARGECODEID, T.TIMESHEETID, T.prtimeperiodid, T.prresourceid, T.TASKPRCHARGECODEID, T.prprojectid,
    T.prstatus, T.prversion, T.PRROLE_ID, T.ROLE_ID, T.typecodeid, T.typecodename
    from trg_trz_time_stage T
    where T.prtimeperiodid = ${tp1.prtimeperiodid}
    and T.prresourceid = ${tp1.prresourceid}
    and T.taskassignmentid is not null
    and T.timesheetid is not null
    and T.taskinternaltaskid is not null
    and T.prresourceid is not null
    and T.prprojectid is not null
    and (T.prstatus = 0 or T.prstatus = 2)
    and T.DUPLICATE = 0
    GROUP BY
    T.PRRESOURCEID, T.PROJECTID, T.TASKID, T.PRPROJECTID, T.TASKINTERNALTASKID, T.DAY_1, T.DAY_2, T.DAY_3, T.DAY_4, T.DAY_5, T.DAY_6, T.DAY_7,
    T.TASKASSIGNMENTID, T.TASKCHARGECODEID, T.TIMESHEETID, T.prtimeperiodid, T.prresourceid, T.TASKPRCHARGECODEID, T.prprojectid,
    T.prstatus, T.prversion, T.PRROLE_ID, T.ROLE_ID, T.typecodeid, T.typecodename
              </sql:query>
                            <!--Step4, Loop through to Populate the Timesheets based on the Time Period from above and by Resource-->
                            <core:forEach items="${v_ts5.rows}" trim="true" var="tp4">
                              <TimeSheetEntry assignmentID="${tp4.taskassignmentid}" chargeCodeID="${tp4.taskchargecodeid}"
                                internalTaskID="${tp4.taskinternaltaskid}" projectID="${tp4.projectid}" roleID="${tp4.role_id}" taskID="${tp4.taskid}"
                                typeCodeID="${tp4.typecodeid}" typeCodeName="${tp4.typecodename}">
                                <DailyActuals>
                                  <Actual actualDate="${tp4.DAY_1}" amount="${tp4.DAY_1_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_2}" amount="${tp4.DAY_2_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_3}" amount="${tp4.DAY_3_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_4}" amount="${tp4.DAY_4_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_5}" amount="${tp4.DAY_5_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_6}" amount="${tp4.DAY_6_HOURS}"/>
                                  <Actual actualDate="${tp4.DAY_7}" amount="${tp4.DAY_7_HOURS}"/>
                                </DailyActuals>
                              </TimeSheetEntry>
                              <!--Step4 END-->
                            </core:forEach>
                          </TimeSheetEntries>
                          <Notes>
                            <NoteData createdBy="${tp3.resourceid}" noteText="TRZ TIME Data"/>
                          </Notes>
                        </TimeSheet>
                        <!--Step3 END-->
                      </core:forEach>
                    </TimeSheets>
                  </TimePeriod>
                </TimePeriods>
              </NikuDataBus>
            </gel:parse>
            <gel:serialize fileName="${PLocalDirectory}/XOG_EPMLive_${vnow}_${tp3.resourceid}.xml" var="${xogBody}"/>
            <!--<<<<<<<<<<<<<< XOG VALUES>>>>>>>>>>>>>>>>>>>-->
            <!-- Send a XOG request. -->
            <gel:log level="INFO">Sending XOG Request.</gel:log>
            <!--<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">-->
            <soap:invoke endpoint="${XOGURLAUTO}" var="result">
              <soap:message>
                <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                  <soap-env:Header>
                    <xog:Auth>
                      <xog:SessionID>${sessionID}</xog:SessionID>
                    </xog:Auth>
                  </soap-env:Header>
                  <soap-env:Body>
                    <gel:include select="$xogBody"/>
                  </soap-env:Body>
                </soap-env:Envelope>
              </soap:message>
            </soap:invoke>
            <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Exception/text()" var="error"/>
            <core:choose>
              <core:when test="${error!=null}">
                <!-- If the XOG request failed, we will log the error, using the error severity as the log level,
                                                            and skip this user/time-period.  -->
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Severity/text()" var="errorLevel"/>
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Description/text()" var="errorDesc"/>
                <gel:log level="${errorLevel}">${errorDesc}</gel:log>
                <core:set value="true" var="noMore"/>
              </core:when>
              <!-- If no error detected, log the result, update counts, and jump out of this loop for the next user/time period. -->
              <core:otherwise>
                <core:set value="true" var="noMore"/>
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Status/@state" var="xogStatus"/>
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Statistics/@insertedRecords" var="inserted"/>
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Statistics/@updatedRecords" var="updated"/>
                <core:choose>
                  <core:when test="${inserted>0}">
                    <gel:log level="INFO">XOG status is "${xogStatus}". Timesheet: ${tp1.resourceid} is created for time period ${vtimePeriod}.</gel:log>
                    <core:forEach items="${v_ts5.rows}" trim="true" var="tplog">
                      <sql:update>

    update (SELECT T.resourceid, T.projectid, T.taskid, T.xogstatus from  TRG_TRZ_TIME_STAGE T
    where T.prtimeperiodid = ${tplog.prtimeperiodid}
    and T.prresourceid = ${tplog.prresourceid}
    and T.taskassignmentid = ${tplog.taskassignmentid}
    and T.timesheetid = ${tplog.timesheetid}
    and T.taskinternaltaskid = ${tplog.taskinternaltaskid}
    and T.prprojectid = ${tplog.prprojectid}
    and T.prstatus = ${tplog.prstatus}) O
    set O.xogstatus = '${xogStatus}'

        </sql:update>
                    </core:forEach>
                  </core:when>
                  <core:when test="${updated>0}">
                    <gel:log level="INFO">XOG status is "${xogStatus}". Timesheet: ${tp1.resourceid} is updated for time period ${vtimePeriod}.</gel:log>
                  </core:when>
                  <core:otherwise>
                    <gel:log level="INFO">XOG status is "${xogStatus}".</gel:log>
                    <core:forEach items="${v_ts5.rows}" trim="true" var="tplog">
                      <sql:update>

    update (SELECT T.resourceid, T.projectid, T.taskid, T.xogstatus from  TRG_TRZ_TIME_STAGE T
    where T.prtimeperiodid = ${tplog.prtimeperiodid}
    and T.prresourceid = ${tplog.prresourceid}
    and T.taskassignmentid = ${tplog.taskassignmentid}
    and T.timesheetid = ${tplog.timesheetid}
    and T.taskinternaltaskid = ${tplog.taskinternaltaskid}
    and T.prprojectid = ${tplog.prprojectid}
    and T.prstatus = ${tplog.prstatus}) O
    set O.xogstatus = '${xogStatus}'

        </sql:update>
                    </core:forEach>
                  </core:otherwise>
                </core:choose>
              </core:otherwise>
            </core:choose>
            <!--Step1 END-->
          </core:forEach>
        </core:otherwise>
        <!-- Logout XOG -->
        <!--<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">-->
        <soap:invoke endpoint="${XOGURLAUTO}" var="result">
          <soap:message>
            <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
              <soap-env:Header>
                <xog:Auth>
                  <xog:SessionID>${sessionID}</xog:SessionID>
                </xog:Auth>
              </soap-env:Header>
              <soap-env:Body>
                <xog:Logout/>
              </soap-env:Body>
            </soap-env:Envelope>
          </soap:message>
        </soap:invoke>
      </core:choose>
      <gel:log level="INFO">Number of timesheet rows to process is -- ${v_toprocess.rows[0].num}</gel:log>
      <gel:log level="INFO">TRG TriZetto xog: Finished</gel:log>
    </gel:script>



  • 16.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:11 PM

    XOG in to "custom" object to have a way for admins to verify data imported:

     

     

    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql"
      xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <!--
      XOG Custom Object Instances to the trg_trz_time object.
    Written 2013-02-20 by Lowell Wetzel
      -->
      <!--Parameters section-->

      <gel:parameter default="admin" var="XOGUsername"/>
      <gel:parameter default="admin" secure="true" var="XOGPassword"/>
      <gel:parameter default="616330@regence.com" var="Recipientemail"/>
      <!--End Parameters Section-->
      <!-- Only used with GEL Serialize command at end -->
      <gel:parameter default="./outputfiles" var="PLocalDirectory"/>
      <!--vnow used for output file-->
      <core:set var="vnow">
        <gel:formatDate format="MMddyy"/>
      </core:set>
      <!-- Initialize some counters.  lastModified stores the latest Last_Modified_time. -->
      <core:set value="1" var="vtrizcount"/>
      <core:set value="0" var="totalCount"/>
      <core:set value="0" var="insertCount"/>
      <core:set value="0" var="updateCount"/>
      <!-- ****************************************GET THE XOG URL FROM THE APPLICATION **********************************************-->
      <core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
      <core:set value="0" var="v_IsLocal"/>
      <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).getEntryUrl()}" var="EntryURL"/>
        <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getPort()}" var="Port"/>
        <core:if test="${EntryURL == 'http://localhost'}">
          <gel:log level="INFO" message="NULL ADDRESS IS LOCAL... "/>
          <core:set value="1" var="v_IsLocal"/>
          <core:set value="${EntryURL}" var="AddressIp"/>
        </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 value="1" var="v_IsLocal"/>
          <core:set value="${EntryURL}" var="AddressIp"/>
        </core:if>
      </core:if>
      <core:if test="${Port != null }">
        <core:set value="${EntryURL}:${Port}" 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>
      <gel:setDataSource dbId="niku"/>
      <sql:query var="r_act">
                select
                R.is_active
           from srm_resources R where R.ID = 5165906
       </sql:query>
      <!--Set value for XOGURLAUTO (auto detected using above code)-->
      <core:if test="${r_act.rows[0].is_active != 0}">
        <core:set value="${EntryURL}/niku/xog" var="XOGURLAUTO"/>
      </core:if>
      <core:if test="${r_act.rows[0].is_active == 0}">
        <core:set value="http://10.22.33.232/niku/xog" var="XOGURLAUTO"/>
        <gel:log level="INFO" message="Setting XOG LOGIN: ${XOGURLAUTO}"/>
      </core:if>
      <!-- ***************************************END GET XOG URL ***********************************-->
      <!--RemoveXXX-->
        <gel:log level="INFO">Make sure environment is correct: XOGURLAUTO: "${XOGURLAUTO}"</gel:log>
      <!-- Log into XOG first. -->
      <!--original    <soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">-->
      <soap:invoke endpoint="${XOGURLAUTO}" var="auth">
        <soap:message>
          <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
            <soap-env:Header/>
            <soap-env:Body>
              <xog:Login>
                <xog:Username>${XOGUsername}</xog:Username>
                <xog:Password>${XOGPassword}</xog:Password>
              </xog:Login>
            </soap-env:Body>
          </soap-env:Envelope>
        </soap:message>
      </soap:invoke>
      <!-- Checking whether a sessionID is returned.  If not, it means that Login was not successful. -->
      <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 level="ERROR">Cannot login to Clarity XOG. Check username and password.</gel:log>
        </core:when>
        <!--Otherwise-->
        <core:otherwise>
          <gel:setDataSource dbId="niku"/>
          <core:set var="vnow">
            <gel:formatDate format="yyyy-MM-dd"/>
          </core:set>
          <core:set var="vuniqueid">
            <gel:formatDate format="yyyyMMddhhmmss"/>
          </core:set>
          <gel:log level="INFO">vuniqueid:  "${vuniqueid}"</gel:log>
          <!--Count Rows to process-->
          <sql:query var="v_toprocess">
    select count(*) as num
    from trg_trz_time_stage t
       </sql:query>
          <gel:log level="INFO">Number of TriZetto individual timesheets to process: ${v_toprocess.rows[0].num}</gel:log>
          <!--Time Periods and Resource-->
          <core:choose>
            <core:when test="${v_toprocess.rows[0].num>0}">
              <sql:query var="v_ts1">
                select
                to_char(T.DAY_1,'YYYY-MM-DD') as DAY1, T.DAY_1_HOURS,
                to_char(T.DAY_2,'YYYY-MM-DD') as DAY2, T.DAY_2_HOURS,
                to_char(T.DAY_3,'YYYY-MM-DD') as DAY3, T.DAY_3_HOURS,
                to_char(T.DAY_4,'YYYY-MM-DD') as DAY4, T.DAY_4_HOURS,
                to_char(T.DAY_5,'YYYY-MM-DD') as DAY5, T.DAY_5_HOURS,
                to_char(T.DAY_6,'YYYY-MM-DD') as DAY6, T.DAY_6_HOURS,
                to_char(T.DAY_7,'YYYY-MM-DD') as DAY7, T.DAY_7_HOURS,
                T.DUPLICATE, T.FIRST_NAME, T.LAST_NAME, to_char(T.LOAD_DATE,'YYYY-MM-DD') as LOADDATE, T.PCR,
                T.PROJECTID, T.PRPROJECTID, T.PRRESOURCEID, T.PRROLE_ID,
                T.PRSTATUS, T.PRTIMEPERIODID, T.PRVERSION, T.RESOURCEID,
                T.ROLE_ID, T.TASKASSIGNMENTID, T.TASKCHARGECODEID, T.TASKID,
                T.TASKINTERNALTASKID, T.TASKPRCHARGECODEID, T.TIMESHEETID,
                T.TRZ_RESOURCE_ID, T.TYPECODEID, T.TYPECODENAME, to_char(T.WEEK_END,'YYYY-MM-DD') as WEEKEND,
                to_char(T.WEEK_START,'YYYY-MM-DD') as WEEKSTART, T.XOGSTATUS as XOGTXT   
                from trg_trz_time_stage t
             </sql:query>
              <core:forEach items="${v_ts1.rows}" trim="true" var="triz">
                <core:set value="${vuniqueid}${vtrizcount}" var="vtrizettocustid"/>
                <!--
    <core:set value="${vuniqueid}${vtrizcount}" var="vtrizettocustid"/>
    <gel:log level="INFO">Counter: ${vtrizcount}</gel:log>
    <gel:log level="INFO">vuniqueid vtrizcount: ${vuniqueid}${vtrizcount}</gel:log>
    <gel:log level="INFO">vtrizettocustid: ${vtrizettocustid}</gel:log>
    <gel:log level="INFO">"trg_d1": ${triz.DAY1}</gel:log>
    <gel:log level="INFO">"trg_d3": ${triz.DAY3}</gel:log>
    <gel:log level="INFO">"trg_d3_hrs": ${triz.DAY_3_HOURS}</gel:log>
    <gel:log level="INFO">"trg_d4": ${triz.DAY4}</gel:log>
    <gel:log level="INFO">"trg_d4_hrs": ${triz.DAY_4_HOURS}</gel:log>
    <gel:log level="INFO">"trg_first": ${triz.FIRST_NAME}</gel:log>
    <gel:log level="INFO">"trg_prtimeperiodid": ${triz.PRTIMEPERIODID}</gel:log>
    <gel:log level="INFO">"trg_prprojectid": ${triz.PRPROJECTID}</gel:log>
    <gel:log level="INFO">"trg_tskinttskid": ${triz.TASKINTERNALTASKID}</gel:log>
    <gel:log level="INFO">"trg_timesheetid": ${triz.TIMESHEETID}</gel:log>
    <gel:log level="INFO">"trg_prstatus": ${triz.PRSTATUS}</gel:log>
    <gel:log level="INFO">"trg_tskid": ${triz.TASKID}</gel:log>
    <gel:log level="INFO">"trg_loaddt": ${triz.LOADDATE}</gel:log>
    <gel:log level="INFO">"trg_duplicate": ${triz.DUPLICATE}</gel:log>
    <gel:log level="INFO">"trg_tskchrgcodeid": ${triz.TASKCHARGECODEID}</gel:log>
    <gel:log level="INFO">"trg_prresourceid": ${triz.PRRESOURCEID}</gel:log>
    <gel:log level="INFO">"tsk_assgn_id": ${triz.TASKASSIGNMENTID}</gel:log>
    <gel:log level="INFO">"trg_typecodename": ${triz.TYPECODENAME}</gel:log>
    <gel:log level="INFO">"trg_role_id": ${triz.ROLE_ID}</gel:log>
    <gel:log level="INFO">"trg_xogstatus": ${triz.XOGTXT}</gel:log>
    <gel:log level="INFO">"partition_code": NIKU.ROOT</gel:log>
    <gel:log level="INFO">"name": ${vtrizettocustid}</gel:log>
    <gel:log level="INFO">"code": ${vtrizettocustid}</gel:log>
    <gel:log level="INFO">"page_layout": odf.trg_trz_timeFrame</gel:log>
    <gel:log level="INFO">"trg_wk_st_dt": ${triz.WEEKSTART}</gel:log>
    <gel:log level="INFO">"trg_d6_hrs": ${triz.DAY_6_HOURS}</gel:log>
    <gel:log level="INFO">"trg_d7": ${triz.DAY7}</gel:log>
    <gel:log level="INFO">"trg_d7_hrs": ${triz.DAY_7_HOURS}</gel:log>
    <gel:log level="INFO">"trg_tskprchrgcodeid": ${triz.TASKPRCHARGECODEID}</gel:log>
    <gel:log level="INFO">"trg_prroleid": ${triz.PRROLE_ID}</gel:log>
    <gel:log level="INFO">"trg_prversion": ${triz.PRVERSION}</gel:log>
    <gel:log level="INFO">"trg_typecodeid": ${triz.TYPECODEID}</gel:log>
    <gel:log level="INFO">"trg_wk_end_dt": ${triz.WEEKEND}</gel:log>
    <gel:log level="INFO">"trg_resid": ${triz.PRRESOURCEID}</gel:log>
    <gel:log level="INFO">"trg_projid": ${triz.PROJECTID}</gel:log>
    <gel:log level="INFO">"trg_wo": ${triz.PCR}</gel:log>
    <gel:log level="INFO">"trg_res_id": ${triz.RESOURCEID}</gel:log>
    <gel:log level="INFO">"trg_d1_hrs": ${triz.DAY_1_HOURS}</gel:log>
    <gel:log level="INFO">"trg_d2": ${triz.DAY2}</gel:log>
    <gel:log level="INFO">"trg_d2_hrs": ${triz.DAY_2_HOURS}</gel:log>
    <gel:log level="INFO">"trg_d5": ${triz.DAY5}</gel:log>
    <gel:log level="INFO">"trg_d5_hrs": ${triz.DAY_5_HOURS}</gel:log>
    <gel:log level="INFO">"trg_d6": ${triz.DAY6}</gel:log>
    <gel:log level="INFO">"trg_last": ${triz.LAST_NAME}</gel:log>
    -->
                <!--Parse-->
                <gel:parse var="xogBody">
                  <!--=================================================-->
                  <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
                    <Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="8.1.3.4545"/>
                    <customObjectInstances objectCode="trg_trz_time">
                      <instance instanceCode="${vtrizettocustid}" objectCode="trg_trz_time">
                        <CustomInformation>
                          <ColumnValue name="trg_d1">${triz.DAY1}</ColumnValue>
                          <ColumnValue name="trg_d3">${triz.DAY3}</ColumnValue>
                          <ColumnValue name="trg_d3_hrs">${triz.DAY_3_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d4">${triz.DAY4}</ColumnValue>
                          <ColumnValue name="trg_d4_hrs">${triz.DAY_4_HOURS}</ColumnValue>
                          <ColumnValue name="trg_first">${triz.FIRST_NAME}</ColumnValue>
                          <ColumnValue name="trg_prtimeperiodid">${triz.PRTIMEPERIODID}</ColumnValue>
                          <ColumnValue name="trg_prprojectid">${triz.PRPROJECTID}</ColumnValue>
                          <ColumnValue name="trg_tskinttskid">${triz.TASKINTERNALTASKID}</ColumnValue>
                          <ColumnValue name="trg_timesheetid">${triz.TIMESHEETID}</ColumnValue>
                          <ColumnValue name="trg_prstatus">${triz.PRSTATUS}</ColumnValue>
                          <ColumnValue name="trg_tskid">${triz.TASKID}</ColumnValue>
                          <ColumnValue name="trg_loaddt">${triz.LOADDATE}</ColumnValue>
                          <ColumnValue name="trg_duplicate">${triz.DUPLICATE}</ColumnValue>
                          <ColumnValue name="trg_tskchrgcodeid">${triz.TASKCHARGECODEID}</ColumnValue>
                          <ColumnValue name="trg_prresourceid">${triz.PRRESOURCEID}</ColumnValue>
                          <ColumnValue name="tsk_assgn_id">${triz.TASKASSIGNMENTID}</ColumnValue>
                          <ColumnValue name="trg_typecodename">${triz.TYPECODENAME}</ColumnValue>
                          <ColumnValue name="trg_role_id">${triz.ROLE_ID}</ColumnValue>
                          <ColumnValue name="trg_xogstatus">${triz.XOGTXT}</ColumnValue>
                          <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                          <ColumnValue name="name">${vtrizettocustid}</ColumnValue>
                          <ColumnValue name="code">${vtrizettocustid}</ColumnValue>
                          <ColumnValue name="page_layout">odf.trg_trz_timeFrame</ColumnValue>
                          <ColumnValue name="trg_wk_st_dt">${triz.WEEKSTART}</ColumnValue>
                          <ColumnValue name="trg_d6_hrs">${triz.DAY_6_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d7">${triz.DAY7}</ColumnValue>
                          <ColumnValue name="trg_d7_hrs">${triz.DAY_7_HOURS}</ColumnValue>
                          <ColumnValue name="trg_tskprchrgcodeid">${triz.TASKPRCHARGECODEID}</ColumnValue>
                          <ColumnValue name="trg_prroleid">${triz.PRROLE_ID}</ColumnValue>
                          <ColumnValue name="trg_prversion">${triz.PRVERSION}</ColumnValue>
                          <ColumnValue name="trg_typecodeid">${triz.TYPECODEID}</ColumnValue>
                          <ColumnValue name="trg_wk_end_dt">${triz.WEEKEND}</ColumnValue>
                          <ColumnValue name="trg_resid">${triz.PRRESOURCEID}</ColumnValue>
                          <ColumnValue name="trg_projid">${triz.PROJECTID}</ColumnValue>
                          <ColumnValue name="trg_wo">${triz.PCR}</ColumnValue>
                          <ColumnValue name="trg_res_id">${triz.RESOURCEID}</ColumnValue>
                          <ColumnValue name="trg_d1_hrs">${triz.DAY_1_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d2">${triz.DAY2}</ColumnValue>
                          <ColumnValue name="trg_d2_hrs">${triz.DAY_2_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d5">${triz.DAY5}</ColumnValue>
                          <ColumnValue name="trg_d5_hrs">${triz.DAY_5_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d6">${triz.DAY6}</ColumnValue>
                          <ColumnValue name="trg_last">${triz.LAST_NAME}</ColumnValue>
                        </CustomInformation>
                        <OBSAssocs completed="false"/>
                        <Security>
                          <UserSecurity rightCode="odf_cst_trg_trz_time_edit" userName="xoguser"/>
                        </Security>
                      </instance>
                    </customObjectInstances>
                  </NikuDataBus>
                </gel:parse>
                <!--Write "complete" XOG file to have 1 file for manual XOG-->
                <core:set value="${vtrizcount+1}" var="vtrizcount"/>
                <!--CA Troubleshooting  <gel:set asString="true" var="xm" select="$xogBody"  />  <gel:log>${xm}</gel:log> -->
                <!-- the information should be in nikubg-system.log -->
                <!--<gel:out>${xm}</gel:out>-->
                <gel:serialize fileName="${PLocalDirectory}/XOG_EPMLive_${vnow}_${vtrizettocustid}.xml" var="${xogBody}"/>
                <!--<<<<<<<<<<<<<< XOG VALUES>>>>>>>>>>>>>>>>>>>-->
                <!-- Send a XOG request. -->
                <gel:set asString="true" select="$xogBody" var="xm"/>
                <gel:log>${xm}</gel:log>
                <!-- the information should be in nikubg-system.log -->
                <gel:out>${xm}</gel:out>
                <gel:log level="INFO">Sending XOG Request.</gel:log>
                <!--<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">-->
                <soap:invoke endpoint="${XOGURLAUTO}" var="result">
                  <soap:message>
                    <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                      <soap-env:Header>
                        <xog:Auth>
                          <xog:SessionID>${sessionID}</xog:SessionID>
                        </xog:Auth>
                      </soap-env:Header>
                      <soap-env:Body>
                        <gel:include select="$xogBody"/>
                      </soap-env:Body>
                    </soap-env:Envelope>
                  </soap:message>
                </soap:invoke>
                <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Exception/text()" var="error"/>
                <core:choose>

                  <core:when test="${error!=null}">
                    <!-- If the XOG request failed, we will log the error, using the error severity as the log level,
                                                            and skip this user/time-period.  -->
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Severity/text()" var="errorLevel"/>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Description/text()" var="errorDesc"/>
                    <gel:log level="${errorLevel}">${errorDesc}</gel:log>
                  </core:when>
                  <!-- If no error detected, log the result, update counts, and jump out of this loop for the next user/time period. -->
                  <core:otherwise>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Severity/text()" var="errorLevel"/>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/ErrorInformation/Description/text()" var="errorDesc"/>
                    <gel:log level="${errorLevel}">${errorDesc}</gel:log>
                    <gel:log level="INFO">"ERRORLVL: ${errorLevel} ERRORDSC: ${errorDesc}"</gel:log>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Statistics/@failureRecords" var="failurerecs"/>
                    <gel:log level="INFO">"FAILURE ${failurerecs}"</gel:log>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Status/@state" var="xogStatus"/>
                    <gel:log level="INFO">"XOGSTATuS ${xogStatus}"</gel:log>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Statistics/@insertedRecords" var="inserted"/>
                    <gel:log level="INFO">"INSERTED: ${inserted}"</gel:log>
                    <gel:set asString="true" select="$result/SOAP-ENV:Envelope/SOAP-ENV:Body/XOGOutput/Statistics/@updatedRecords" var="updated"/>
                    <gel:log level="INFO">"UPDATED: ${updated}"</gel:log>
                    <core:choose>
                      <core:when test="${inserted>0}">
                        <gel:log level="INFO">XOG status is "${xogStatus}". Timesheet: ${tp1.resourceid} is created for time period ${vtimePeriod}.</gel:log>
                      </core:when>
                      <core:when test="${updated>0}">
                        <gel:log level="INFO">XOG status is "${xogStatus}". Timesheet: ${tp1.resourceid} is updated for time period ${vtimePeriod}.</gel:log>
                      </core:when>
                      <core:otherwise>
                        <gel:log level="INFO">XOG status is "${xogStatus}".</gel:log>
                      </core:otherwise>
                    </core:choose>
                  </core:otherwise>
                </core:choose>
                <!--Step1 END-->
              </core:forEach>
              <core:set value="0" var="vtrizcount"/>
              <!--write complete XOG file for 1 XOG command-->
              <gel:parse var="xogBodyManual">
                <!--=================================================-->
                <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
                  <Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="8.1.3.4545"/>
                  <customObjectInstances objectCode="trg_trz_time">
                    <!--Loop through and build XOG file-->
                    <core:forEach items="${v_ts1.rows}" trim="true" var="triz">
                      <core:set value="${vuniqueid}${vtrizcount}" var="vtrizettocustid"/>
                      <instance instanceCode="${vtrizettocustid}" objectCode="trg_trz_time">
                        <CustomInformation>
                          <ColumnValue name="trg_d1">${triz.DAY1}</ColumnValue>
                          <ColumnValue name="trg_d3">${triz.DAY3}</ColumnValue>
                          <ColumnValue name="trg_d3_hrs">${triz.DAY_3_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d4">${triz.DAY4}</ColumnValue>
                          <ColumnValue name="trg_d4_hrs">${triz.DAY_4_HOURS}</ColumnValue>
                          <ColumnValue name="trg_first">${triz.FIRST_NAME}</ColumnValue>
                          <ColumnValue name="trg_prtimeperiodid">${triz.PRTIMEPERIODID}</ColumnValue>
                          <ColumnValue name="trg_prprojectid">${triz.PRPROJECTID}</ColumnValue>
                          <ColumnValue name="trg_tskinttskid">${triz.TASKINTERNALTASKID}</ColumnValue>
                          <ColumnValue name="trg_timesheetid">${triz.TIMESHEETID}</ColumnValue>
                          <ColumnValue name="trg_prstatus">${triz.PRSTATUS}</ColumnValue>
                          <ColumnValue name="trg_tskid">${triz.TASKID}</ColumnValue>
                          <ColumnValue name="trg_loaddt">${triz.LOADDATE}</ColumnValue>
                          <ColumnValue name="trg_duplicate">${triz.DUPLICATE}</ColumnValue>
                          <ColumnValue name="trg_tskchrgcodeid">${triz.TASKCHARGECODEID}</ColumnValue>
                          <ColumnValue name="trg_prresourceid">${triz.PRRESOURCEID}</ColumnValue>
                          <ColumnValue name="tsk_assgn_id">${triz.TASKASSIGNMENTID}</ColumnValue>
                          <ColumnValue name="trg_typecodename">${triz.TYPECODENAME}</ColumnValue>
                          <ColumnValue name="trg_role_id">${triz.ROLE_ID}</ColumnValue>
                          <ColumnValue name="trg_xogstatus">${triz.XOGTXT}</ColumnValue>
                          <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                          <ColumnValue name="name">${vtrizettocustid}</ColumnValue>
                          <ColumnValue name="code">${vtrizettocustid}</ColumnValue>
                          <ColumnValue name="page_layout">odf.trg_trz_timeFrame</ColumnValue>
                          <ColumnValue name="trg_wk_st_dt">${triz.WEEKSTART}</ColumnValue>
                          <ColumnValue name="trg_d6_hrs">${triz.DAY_6_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d7">${triz.DAY7}</ColumnValue>
                          <ColumnValue name="trg_d7_hrs">${triz.DAY_7_HOURS}</ColumnValue>
                          <ColumnValue name="trg_tskprchrgcodeid">${triz.TASKPRCHARGECODEID}</ColumnValue>
                          <ColumnValue name="trg_prroleid">${triz.PRROLE_ID}</ColumnValue>
                          <ColumnValue name="trg_prversion">${triz.PRVERSION}</ColumnValue>
                          <ColumnValue name="trg_typecodeid">${triz.TYPECODEID}</ColumnValue>
                          <ColumnValue name="trg_wk_end_dt">${triz.WEEKEND}</ColumnValue>
                          <ColumnValue name="trg_resid">${triz.PRRESOURCEID}</ColumnValue>
                          <ColumnValue name="trg_projid">${triz.PROJECTID}</ColumnValue>
                          <ColumnValue name="trg_wo">${triz.PCR}</ColumnValue>
                          <ColumnValue name="trg_res_id">${triz.RESOURCEID}</ColumnValue>
                          <ColumnValue name="trg_d1_hrs">${triz.DAY_1_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d2">${triz.DAY2}</ColumnValue>
                          <ColumnValue name="trg_d2_hrs">${triz.DAY_2_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d5">${triz.DAY5}</ColumnValue>
                          <ColumnValue name="trg_d5_hrs">${triz.DAY_5_HOURS}</ColumnValue>
                          <ColumnValue name="trg_d6">${triz.DAY6}</ColumnValue>
                          <ColumnValue name="trg_last">${triz.LAST_NAME}</ColumnValue>
          </CustomInformation>
                        <OBSAssocs completed="false"/>
                        <Security>
                          <UserSecurity rightCode="odf_cst_trg_trz_time_edit" userName="xoguser"/>
                        </Security>
                      </instance>
                      <!--Increment counter and loop-->
                      <core:set value="${vtrizcount+1}" var="vtrizcount"/>
                    </core:forEach>
                  </customObjectInstances>
                </NikuDataBus>
              </gel:parse>
              <!--Write out 1 single XOG file for all custom object values-->
              <!--<gel:serialize fileName="${PLocalDirectory}/XOG_TriZettoTS_${vnow}_${vtrizcount}.xml" var="${xogBodyManual}"/>-->
              <!--        </core:otherwise>-->
              <!-- Logout XOG -->
              <!--<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">-->
              <soap:invoke endpoint="${XOGURLAUTO}" var="result">
                <soap:message>
                  <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                    <soap-env:Header>
                      <xog:Auth>
                        <xog:SessionID>${sessionID}</xog:SessionID>
                      </xog:Auth>
                    </soap-env:Header>
                    <soap-env:Body>
                      <xog:Logout/>
                    </soap-env:Body>
                  </soap-env:Envelope>
                </soap:message>
              </soap:invoke>
    <!--<gel:serialize fileName="${PLocalDirectory}/XOG_TriZetto_result_${vnow}_${vtrizcount}.xml" var="${result}"/>-->
    <!--                <core:catch var="v_mail_exception">
                        <gel:email from="clarity@regence.com"
                            subject="XOG TriZetto ${vtrizcount} results" to="${Recipientemail}">
    XOG Attached file:  ${PLocalDirectory}/XOG_TriZetto_result_${vnow}_${vtrizcount}.xml
    &lt;br>
    &lt;br>
    attach="${PLocalDirectory}/XOG_TriZetto_result_${vnow}_${vtrizcount}.xml"
               </gel:email>-->
    <!--To be added later                         http://clarity.regence.com/niku/nu#action:projmgr.projectDefaultTab&id=5344794&classCode=project 
    To access this RITS Work Request click on : ${eURL}/niku/app?action=pma.ideaProperties&amp;id=${row.id}&amp;investment_code=IDEA
    -->

    <!--                </core:catch>-->
    <!--                <gel:log level="INFO"></gel:log>-->
    <!--                <core:choose>
                        <core:when test="${v_mail_exception != null}">
                            <gel:log level="INFO">"Sending Email failed"</gel:log>
                        </core:when>
                    </core:choose>-->

            </core:when>
            <core:otherwise>
              <gel:log level="INFO">"No TriZetto Timesheet values to import"</gel:log>
            </core:otherwise>
          </core:choose>
        </core:otherwise>
      </core:choose>
      <gel:log level="INFO">Number of timesheet rows to process is -- ${v_toprocess.rows[0].num}</gel:log>
      <gel:log level="INFO">TRG TriZetto xog: Finished</gel:log>
    </gel:script>



  • 17.  Re: Bulk upload of timesheets?

    Posted 06-23-2016 02:12 PM

    Copying data to history table:

     

     

    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:rem="jelly:com.niku.union.gel.RemedyTagLibrary" xmlns:remedy="urn:HelpDesk_Query_Service" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
      xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <gel:setDataSource dbId="niku"/>
      <gel:log level="INFO">Begin Load TRG_TRZ_TIME_STAGE_HIST</gel:log>
      <sql:update>

    INSERT INTO trg_trz_time_stage_hist (LOAD_DATE, TRZ_RESOURCE_ID, FIRST_NAME, LAST_NAME, WEEK_START, WEEK_END,
    DAY_1, DAY_1_HOURS, DAY_2, DAY_2_HOURS, DAY_3, DAY_3_HOURS, DAY_4, DAY_4_HOURS,
    DAY_5, DAY_5_HOURS, DAY_6, DAY_6_HOURS, DAY_7, DAY_7_HOURS, PRPROJECTID,
    TASKINTERNALTASKID, PRTIMEPERIODID, PCR, PRRESOURCEID, TASKPRCHARGECODEID,
    TASKASSIGNMENTID, PRROLE_ID, TIMESHEETID, PRSTATUS, PRVERSION, TYPECODEID,
    TYPECODENAME, TASKCHARGECODEID, RESOURCEID, TASKID, PROJECTID)

    SELECT LOAD_DATE, TRZ_RESOURCE_ID, FIRST_NAME, LAST_NAME, WEEK_START, WEEK_END,
    DAY_1, DAY_1_HOURS, DAY_2, DAY_2_HOURS, DAY_3, DAY_3_HOURS, DAY_4, DAY_4_HOURS,
    DAY_5, DAY_5_HOURS, DAY_6, DAY_6_HOURS, DAY_7, DAY_7_HOURS, PRPROJECTID,
    TASKINTERNALTASKID, PRTIMEPERIODID, PCR, PRRESOURCEID, TASKPRCHARGECODEID,
    TASKASSIGNMENTID, PRROLE_ID, TIMESHEETID, PRSTATUS, PRVERSION, TYPECODEID,
    TYPECODENAME, TASKCHARGECODEID, RESOURCEID, TASKID, PROJECTID
    from TRG_TRZ_TIME_STAGE

    </sql:update>
      <gel:log level="INFO">Load TRG_TRZ_TIME_STAGE_HIST Completed.</gel:log>
    </gel:script>