Clarity PPM

Expand all | Collapse all

Gel Writing Issue

  • 1.  Gel Writing Issue

    Posted 05-25-2017 09:14 AM

    Hi All,

    I am trying to write some data (around 13-14 columns) related to actuals on a csv file via gel scripting in a process

    It is writing all data except writing three fields which is for Timesheet Start Date, End Date and Actual Hours. These three columns are coming blank. Even when i moved their sequence from end to between nothing happens but the sql is generating the data and there is no issue with it for those columns

     

    I am attaching the write part for your check if you find any issues in it

     

      <!--Iterate rows in query and create rows in file-->
        <core:forEach items="${result.rows}" trim="true" var="row">
          <file:line>
            <file:column value="&quot;${row.EID.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.RF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.RMF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.PRF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.PTN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.DPC.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.FCN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.INC.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.INN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.CAR.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.MEN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.TPRS.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.TPRF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.TEH.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.PRN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.CPRN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
            <file:column value="&quot;${row.TSN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
          </file:line>
        </core:forEach>
        <!--Flush buffer and close file handle-->
      </file:writeFile>



  • 2.  Re: Gel Writing Issue

    Posted 05-25-2017 10:24 AM

    I don't understand the question - are you trying to write data from a XLS to PPM or trying to read data from PPM and write it out to an XLS?

     

    - if you are trying to write data into PPM, then date fields need to be in a particular format "YYYY-MM-DDThh:mi:ss" and actual hours are probably written as a slice - read out data via XOG to see the format you need to create to write it in.

     

    - if you are reading out data from PPM then you have not really told us anything to help, all you have shown is some GEL code to replace single quotes with two single quotes

     

     



  • 3.  Re: Gel Writing Issue

    Posted 05-25-2017 01:41 PM

    Hi Dave,

     

    I was talking about write data from PPM in a csv file with delimitor ","

     

    Sorry I missed to include it entirely

     

    Here is the details :

     

    <!--Create a file handle-->
    <file:writeFile delimiter="," embedded="false" fileName="${filePath}">
    <!--Set column names in file-->
    <file:comment value="Employee ID,Resource Name,Resource Manager,Primary Role,Employment Type,Cost Center,Financial Category,Investment ID,Investment Name,CAR #,Methodology,Task Name,Charge Code,Timesheet Status,Timesheet Start Date,Timesheet End Date,Time Entry Hours"/>
    <!--Get project data-->
    <sql:query dataSource="${clarityDS}" escapeText="0"
    var="result"><![CDATA[
    SELECT
    SI_ODF_CA_TEAM_RES.SI_EMPLOYEE_ID EID,
    RPT_TM_RESOURCES.FULL_NAME RF,
    RESOURCEMANAGER.FULL_NAME RMF,
    RPT_TM_PRIMARYROLE.FULL_NAME PRF,
    RPT_TM_PERSON_TYPE.NAME PTN,
    SI_TEAM_RES_DEPT.DEPARTCODE DPC,
    SI_PRJ_FINANCIAL_CATEGORY.NAME FCN,
    RPT_TM_INVESTMENTS.CODE INC,
    RPT_TM_INVESTMENTS.NAME INN,
    SI_ODF_CA_PROJECT.SI_CAR_NUMBER CAR,
    SI_PRJ_METHODOLOGY.NAME MEN,
    RPT_TM_TASKS.PRNAME PRN,
    RPT_TM_CHARGECODE.PRNAME CPRN,
    RPT_TM_TIMESHEET_STATUS.NAME TSN,
    RPT_TM_TIMEPERIOD.PRSTART TPRS,
    RPT_TM_TIMEPERIOD.PRFINISH TPRF,
    SUM(RPT_TM_TIMEENTRY.PRACTSUM / 3600) TEH
    FROM
    PRJ_RESOURCES RPT_TM_PRJ_RESOURCES
    JOIN SRM_RESOURCES RPT_TM_RESOURCES ON (RPT_TM_PRJ_RESOURCES.PRID=RPT_TM_RESOURCES.ID)
    LEFT JOIN SRM_RESOURCES RPT_TM_PRIMARYROLE ON (RPT_TM_PRJ_RESOURCES.PRPRIMARYROLEID=RPT_TM_PRIMARYROLE.ID)
    LEFT JOIN SRM_RESOURCES RESOURCEMANAGER ON RPT_TM_RESOURCES.MANAGER_ID = RESOURCEMANAGER.USER_ID
    LEFT JOIN (
    SELECT RECORD_ID RESOURCE_ID,
    OBST.NAME OBS_TYPE_NAME,
    OBSU.LEVEL1_NAME,
    OBSU.LEVEL2_NAME,
    OBSU.LEVEL3_NAME,
    OBSU.LEVEL4_NAME,
    OBSU.LEVEL5_NAME,
    OBSU.LEVEL6_NAME,
    OBSU.LEVEL7_NAME,
    OBSU.LEVEL8_NAME,
    OBSU.LEVEL9_NAME,
    OBSU.LEVEL10_NAME,
    OBSU.PATH OBS_PATH,
    OBSU.OBS_UNIT_ID
    FROM PRJ_OBS_ASSOCIATIONS OBSA, NBI_DIM_OBS OBSU, PRJ_OBS_TYPES OBST
    WHERE OBSA.TABLE_NAME = 'SRM_RESOURCES'
    AND OBSA.UNIT_ID = OBSU.OBS_UNIT_ID
    AND OBSU.OBS_TYPE_ID = OBST.ID
    AND OBST.UNIQUE_NAME = 'si_rm_obs'

    ) RPT_TM_OBS_STRUCTURE ON (RPT_TM_OBS_STRUCTURE.RESOURCE_ID=RPT_TM_RESOURCES.ID)
    RIGHT OUTER JOIN PRTIMESHEET RPT_TM_TIMESHEET ON (RPT_TM_RESOURCES.ID=RPT_TM_TIMESHEET.PRRESOURCEID)
    LEFT OUTER JOIN PRTIMEPERIOD RPT_TM_TIMEPERIOD ON (RPT_TM_TIMEPERIOD.PRID=RPT_TM_TIMESHEET.PRTIMEPERIODID)
    LEFT OUTER JOIN PRTIMEENTRY RPT_TM_TIMEENTRY ON (RPT_TM_TIMESHEET.PRID=RPT_TM_TIMEENTRY.PRTIMESHEETID)
    LEFT OUTER JOIN IMM_INCIDENTS RPT_TM_IMM_INCIDENTS ON (RPT_TM_IMM_INCIDENTS.ID=RPT_TM_TIMEENTRY.INCIDENT_ID)
    LEFT OUTER JOIN INV_INVESTMENTS RPT_TM_INCIDENT_INVESTMENT ON (RPT_TM_INCIDENT_INVESTMENT.ID=RPT_TM_IMM_INCIDENTS.INVESTMENT_ID)
    LEFT OUTER JOIN PRCHARGECODE RPT_TM_CHARGECODE ON (RPT_TM_CHARGECODE.PRID=RPT_TM_TIMEENTRY.PRCHARGECODEID)
    LEFT OUTER JOIN PRASSIGNMENT RPT_TM_PRASSIGNMENT ON (RPT_TM_TIMEENTRY.PRASSIGNMENTID=RPT_TM_PRASSIGNMENT.PRID)
    LEFT OUTER JOIN PRTASK RPT_TM_TASKS ON (RPT_TM_PRASSIGNMENT.PRTASKID=RPT_TM_TASKS.PRID)
    LEFT OUTER JOIN INV_INVESTMENTS RPT_TM_INVESTMENTS ON (RPT_TM_TASKS.PRPROJECTID=RPT_TM_INVESTMENTS.ID)
    LEFT OUTER JOIN ODF_CA_PROJECT SI_ODF_CA_PROJECT ON (SI_ODF_CA_PROJECT.ID=RPT_TM_INVESTMENTS.ID)

    LEFT OUTER JOIN (
    select name, lookup_code, lookup_enum from cmn_lookups_v where lookup_type = UPPER('SI_RUN_TYPE') and language_code = 'en'
    ) SI_PRJ_FINANCIAL_CATEGORY ON (SI_ODF_CA_PROJECT.SI_FIN_CAT=SI_PRJ_FINANCIAL_CATEGORY.LOOKUP_CODE)
    LEFT OUTER JOIN (
    select name, lookup_code, lookup_enum from cmn_lookups_v where lookup_type IN (UPPER('SI_INVEST_METHODOLOGY'),'SI_PRJ_TYPE') and language_code = 'en'
    ) SI_PRJ_METHODOLOGY ON (SI_ODF_CA_PROJECT.SI_INVEST_METHOD=SI_PRJ_METHODOLOGY.LOOKUP_CODE)
    LEFT OUTER JOIN (
    SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME
    FROM CMN_LOOKUPS_V
    WHERE LOOKUP_TYPE = 'TIMESHEET_STATUS' AND LANGUAGE_CODE = 'en'
    ) RPT_TM_TIMESHEET_STATUS ON (RPT_TM_TIMESHEET_STATUS.LOOKUP_ENUM=RPT_TM_TIMESHEET.PRSTATUS)
    LEFT OUTER JOIN (
    SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME
    FROM CMN_LOOKUPS_V
    WHERE LOOKUP_TYPE = 'SRM_RESOURCE_TYPE' AND LANGUAGE_CODE = 'en'
    ) RPT_TM_PERSON_TYPE ON (RPT_TM_PERSON_TYPE.ID=RPT_TM_RESOURCES.PERSON_TYPE)
    INNER JOIN ODF_CA_RESOURCE SI_ODF_CA_TEAM_RES ON (RPT_TM_RESOURCES.ID=SI_ODF_CA_TEAM_RES.ID)
    LEFT OUTER JOIN DEPARTMENTS SI_TEAM_RES_DEPT ON (SI_ODF_CA_TEAM_RES.SI_RES_CC=SI_TEAM_RES_DEPT.ID)
    WHERE
    RPT_TM_TIMEPERIOD.PRSTART >= ADD_MONTHS(TRUNC(SYSDATE),-6)-EXTRACT(DAY FROM TRUNC(SYSDATE))+1

    GROUP BY
    SI_ODF_CA_TEAM_RES.SI_EMPLOYEE_ID,
    RPT_TM_RESOURCES.FULL_NAME,
    RPT_TM_PRIMARYROLE.FULL_NAME,
    RPT_TM_TIMESHEET_STATUS.NAME,
    RPT_TM_TIMEPERIOD.PRFINISH,
    RPT_TM_INVESTMENTS.CODE,
    RPT_TM_INVESTMENTS.NAME,
    SI_PRJ_METHODOLOGY.NAME,
    RPT_TM_TIMEPERIOD.PRSTART,
    SI_PRJ_FINANCIAL_CATEGORY.NAME,
    SI_TEAM_RES_DEPT.DEPARTCODE,
    RPT_TM_TASKS.PRNAME,
    RPT_TM_CHARGECODE.PRNAME,
    RPT_TM_PERSON_TYPE.NAME,
    SI_ODF_CA_PROJECT.SI_CAR_NUMBER,
    RESOURCEMANAGER.FULL_NAME
    HAVING
    SUM(RPT_TM_TIMEENTRY.PRACTSUM / 3600) > 0
    ]]></sql:query>
    <!--Iterate rows in query and create rows in file-->
    <core:forEach items="${result.rows}" trim="true" var="row">
    <file:line>
    <file:column value="&quot;${row.EID.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.RF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.RMF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.PRF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.PTN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.DPC.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.FCN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.INC.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.INN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.CAR.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.MEN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.PRN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.CPRN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.TSN.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.TPRS.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.TPRF.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    <file:column value="&quot;${row.TEH.replaceAll('&quot;', '&quot;&quot;')}&quot;"/>
    </file:line>
    </core:forEach>
    <!--Flush buffer and close file handle-->
    </file:writeFile>



  • 4.  Re: Gel Writing Issue

    Posted 05-26-2017 03:01 AM

    OK well I don't know why you think you might need to do the "replace" on the date fields and a numeric value ; have you tried just putting in the unadulterated TEH, TPRS, TRPF values as they are returned from the query?

     

    You could try LOGing the returned values out as well, to see if there is anything strange going on with them?



  • 5.  Re: Gel Writing Issue

    Posted 05-26-2017 06:00 AM

    Hi Dave,

     

    When used without replace they are producing data then why using replace it's not working but in data the formats for the date fields are coming in a different way

     

    But when I changed the format cells for Timesheet Start Date and End Date to Date in excel it is showing the date in a right way

     

    Could you please help me how to fix that while writing the file it should show date in proper date format instead of attached dataDate Format Issue



  • 6.  Re: Gel Writing Issue

    Posted 05-26-2017 07:02 AM

    If you open your file in a text editor not Excel you should see what is actually being written - and its probably Excel that is doing the funny formatting not anything to do with the GEL script - if you need to force a specific format do that in the query (return a character string not a date format from the SQL).

     

    (possibly something to do with trying to use string functions (replace) on a date datatype in GEL was the original problem?)



  • 7.  Re: Gel Writing Issue

    Posted 05-26-2017 07:13 AM

    Yes Dave I already saw that but I was confused since when we run it against toad / sql developer and export it it does show up in correct format in excel when opened but while doing the writing via gel things changed. Hence I was thinking there could be something else

     

    Also yes I was also thinking to force it to character string

    Regarding my original problem i probably have to ask CA support about the strange behavior



  • 8.  Re: Gel Writing Issue

    Posted 05-26-2017 09:26 AM

    You have to think about what you are running your SQL in ; SQL*Developer or TOAD will handle native DATE formats coming back from the datbase in a specific way, they will treat them as DATE formats and then display them as you have configured SQL*Developer or TOAD to display them (in the Options for those tools). You can not rely on a SQL statement running in GEL returning date formats in the same way because GEL will handle that DATE field how it needs to and then display it how it needs to.

     

    Point is ; if you are doing things in different tools then you can't just assume that the format of complex data types (i.e. DATEs) is going to be the same!

     

    However, if you convert the DATE for a known character-format in the SQL, then SQL*Developer or TOAD or your GEL script will handle the string the same way (its just a string).