AnsweredAssumed Answered

Gel-Script not fetching results

Question asked by nmanoha on Dec 31, 2013
Latest reply on Jan 1, 2014 by Dave

Hi All,

The below gel-script executes the sql copies it to .csv file and emails the attachement. But the attachement doesnot contain any sql results only the column headers.

If i execute the sql seperately in sql developer m getting resultset for the same query. Let me know if the script is missing something.

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core"
    xmlns:f="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:jemail="jelly:email"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:sql="jelly:sql" xmlns:util="jelly:util"
    xmlns:xog="http://www.niku.com/xog"
    <gel:log level="DEBUG" message="setting DB"/>
    <gel:parameter
        default="/apps/clarity/Timesheets_details" var="FILE_PATH"/>
    <gel:parameter default="mail.com" var="SMTP_SERVER"/>
    <gel:parameter default="XXXX" var="EMAIL_FROM"/>
    <gel:parameter default="YYYY" var="EMAIL_TO"/>
    <gel:parameter default="YYYY" var="ERROR_EMAIL_TO"/>
    <gel:parameter default="1" var="DATA_FOR_NO_OF_WEEKS_BEFORE"/>
    <gel:log level="DEBUG" message="Contractors Time details"/>
    <core:catch var="gel_exception">
        <!-- Set up datasource -->
        <gel:setDataSource dbId="Niku"/>
        <sql:query escapeText="false" var="v_file_name_query">
<![CDATA[SELECT  'Contractors Time details - '  || TO_CHAR(PRFINISH-1,'YYYYMMDD') || '.csv',TO_CHAR(PRSTART,'DD-MON-YY'), TO_CHAR(PRFINISH,'DD-MON-YY') FROM PRTIMEPERIOD WHERE (PRSTART, PRFINISH) IN (SELECT PRSTART-(1*7),PRFINISH-(1*7) FROM PRTIMEPERIOD WHERE PRSTART < SYSDATE AND PRFINISH > SYSDATE)]]></sql:query>
        <!-- New Code -->
        <core:forEach begin="0" 
            items="${v_file_name_query.rowsByIndex}" step="1"
            trim="false" var="v_file_name_queryrow">
            <core:set value="${v_file_name_queryrow[0]}" var="v_file_name"/>
            <core:set value="${v_file_name_queryrow[1]}" var="v_week_start"/>
            <core:set value="${v_file_name_queryrow[2]}" var="v_week_end"/>
           
            <gel:log>file name:${v_file_name}</gel:log>
            <core:new className="java.io.FileOutputStream" var="v_out">
            <core:arg type="java.lang.String" value="${FILE_PATH}/${v_file_name}"/>
<gel:log>file path:${FILE_PATH}/${v_file_name}</gel:log>
            </core:new>
            <core:new className="java.io.PrintStream" var="v_printstream">
                <core:arg type="java.io.FileOutputStream" value="${v_out}"/>
            </core:new>
            <core:invoke method="println" on="${v_printstream}">
                <core:arg type="java.lang.String" value="FULL_NAME,RES_UNIQUE_NAME,TS_START,TS_META_STATUS,TS_HOURS"/>
            </core:invoke>
            <gel:log level="DEBUG" message="got DB connection"/>
<gel:log level="DEBUG">v_week_start:'${v_week_start}'</gel:log>
<gel:log level="DEBUG">v_week_end:'${v_week_end}'</gel:log>
            <sql:query escapeText="false" var="v_contract_time">
<![CDATA[ SELECT  --SR.ID AS RES_ID,
                  SR.FULL_NAME,
                  SR.UNIQUE_NAME AS RES_UNIQUE_NAME,
                 -- RESTP.PRJRESOPEN AS RES_OPEN,
                  TP.PRSTART AS TS_START,
                --  TP.PRID AS TP_ID,
                 -- TP.PRISOPEN AS TP_OPEN,
                 -- SR.RESOURCE_TYPE AS RESOURCE_TYPE,
                 -- TS.PRID AS TS_ID,
                 -- TS.PRSTATUS AS TS_STATUS,
                  (CASE WHEN TS.PRSTATUS=0 THEN 'Open'
                       WHEN TS.PRSTATUS=1 THEN 'Submitted'
                       WHEN TS.PRSTATUS=2 THEN 'Returned'
                       WHEN TS.PRSTATUS=3 THEN 'Approved'
                       WHEN TS.PRSTATUS=4 THEN 'Posted'
                       WHEN TS.PRSTATUS=5 THEN 'Adjusted'
                       ELSE 'Open' END)
                    AS TS_META_STATUS,
                  CASE WHEN SR.RESOURCE_TYPE=0 THEN NVL(SUM(TE.PRACTSUM)/3600, 0)
                       WHEN SR.RESOURCE_TYPE=1 THEN NVL(SUM(TE.PRACTSUM)/3600, 0)
                       ELSE NVL(SUM(TE.PRACTSUM), 0) END
                    AS TS_HOURS
                  --TS.PRISADJUSTMENT AS TS_IS_ADJUSTMENT,
                  --CASE WHEN TS.PRSTATUS=5 THEN 1
                  --ELSE 0 END
                  --AS TS_IS_ADJUSTED
          FROM  /* Inline view: Full cartesion join between all open, tracked resources to which the user has rights and all open time periods. */
                (SELECT DISTINCT PR.PRID RESOURCE_ID, TP.PRID PRTIMEPERIODID, PR.PRISOPEN PRJRESOPEN
                   FROM   PRTIMEPERIOD TP, PRJ_RESOURCES PR
            WHERE 
                   PR.PRTRACKMODE <> 0 /* Exclude non-tracked, non-open resources. */
                   AND PR.PRISROLE <> 1 /* Exclude all roles from the results. */
                   AND TP.PRSTART >= '${v_week_start}' 
  AND TP.PRSTART < '${v_week_end}'
  ) RESTP
             ,PRTIMEPERIOD TP,
                PRTIMEENTRY TE,
                PRTIMESHEET TS,
                SRM_RESOURCES SR  ,
                odf_ca_resource od,
                cmn_sec_users cmn
          WHERE SR.ID = RESTP.RESOURCE_ID
          AND TP.PRID = RESTP.PRTIMEPERIODID
          AND   TS.PRRESOURCEID(+) = RESTP.RESOURCE_ID /* Outer join TP/resource view with resource and time period... */
          AND   RESTP.PRTIMEPERIODID = TS.PRTIMEPERIODID(+) /* ... so TP/resource pairs with no time sheets are not excluded. */
          AND   TE.PRTIMESHEETID (+) = TS.PRID /* Outer join so timesheets with no time entries are not excluded. */
          AND   (SR.DATE_OF_HIRE IS NULL OR SR.DATE_OF_HIRE < TP.PRFINISH) /* Exclude TP/resource pairs when the resource wasn't hired. */
          AND   (SR.DATE_OF_TERMINATION IS NULL OR TP.PRSTART < SR.DATE_OF_TERMINATION)
          AND   (RESTP.PRJRESOPEN <> 0 OR TS.PRID IS NOT NULL)
           AND SR.IS_ACTIVE = 1 AND (  (TS.PRSTATUS IN (0,2) OR TS.PRID IS NULL))
           and od.id = sr.id
           and od.gmac_res_manager = cmn.id
           AND SR.person_type = 5017004
          GROUP BY SR.ID,
                   SR.FULL_NAME,
                   SR.UNIQUE_NAME,
                   TP.PRSTART,
                   TP.PRID,
                   TP.PRISOPEN,
                   SR.RESOURCE_TYPE,
                   TS.PRID,
                   TS.PRSTATUS,
                   TS.PRISADJUSTMENT,
                   TS.PRSTATUS,
                   RESTP.PRJRESOPEN
          ORDER BY FULL_NAME asc, TS_START asc, TS_META_STATUS asc--, TS_ID desc]]></sql:query>
            <!-- New Code -->
            <core:forEach items="${v_contract_time.rowsByIndex}" trim="false" var="v_contract_timerow">
               <gel:log level="INFO">Printing...${v_contract_timerow[0]},${v_contract_timerow[1]},${v_contract_timerow[2]},${v_contract_timerow[3]},${v_contract_timerow[4]}</gel:log>
                <core:invoke method="println" on="${v_printstream}">
                    <core:arg type="java.lang.String" value="${v_contract_timerow[0]},${v_contract_timerow[1]},${v_contract_timerow[2]},${v_contract_timerow[3]},${v_contract_timerow[4]}"/>
                </core:invoke>
            </core:forEach>
            <core:invoke method="close" on="${v_printstream}"/>
            <core:invoke method="close" on="${v_out}"/>
            <gel:log> Emailing to ${EMAIL_TO} from ${EMAIL_FROM}  </gel:log>
            <jemail:email attach="${FILE_PATH}/${v_file_name}"
                from="${EMAIL_FROM}"
                message="Hi,Please find the Contractors time details for the week ending  ${v_week_end} ( ${v_week_start} - ${v_week_end}) "
                server="${SMTP_SERVER}"
                subject="Contractors Time Extract " to="${EMAIL_TO}"/>
        </core:forEach>
    </core:catch>
    <core:choose>
        <core:when test="${(gel_exception!=null)}">
            <gel:log
                    level="ERROR">"Error In Payroll Generation :<gel:formatDate format="yyyy-MM-dd-HH:mm:ss"/>: ${gel_exception.getMessage()}" </gel:log>
            <gel:email from="${EMAIL_FROM}"
                subject="Contractor time Extract Interface Error"
                    to="${ERROR_EMAIL_TO}">
Error In Contractors Time Extract  Generation:<gel:formatDate format="yyyy-MM-dd-HH:mm:ss"/>: ${gel_exception.getMessage()}
</gel:email>
        </core:when>
    </core:choose>
</gel:script>
 
 

Thanks

Pavithra

Outcomes