AnsweredAssumed Answered

NSQL issue to add total actuals of timesheet to portlet

Question asked by Mika_B on Dec 3, 2013
Latest reply on Jul 31, 2014 by janurva

Hi all,

 

I have been doing a little modification to My Timesheets portlet. We would like it to show the time entry history for users so they could inspect theirs own entries.

I took the code from Timesheet Review portlet, which shows the total actuals in the portlet and tried to add it to My Timesheets portlet but something is wrong in the SQL.

I am not too experienced with NQSL, so all help would be needed here. smiley

 

Here is the query at this point.

  • I have erased:

 AND    tp.prisopen = 1

so the timesheet doesn't have to be open

  • I have added:

AND      ts.posted_time IS NOT NULL

  • I have changed:

AND    ts.prstatus < 3   -----> AND    ts.prstatus > 3

so there will be only history timesheets that are posted

This far everything works well. I have error when I try to add a new column to show tha total actuals of timesheet.

Here is how the NSQL query looks now:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:TIMESHEETS:a.resource_int_id @+@ a.timeperiod_int_id @+@ @NVL@(a.timesheet_int_id,0):dim_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.timeperiod_int_id:timeperiod_int_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.timesheet_int_id:timesheet_int_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.resource_int_id:resource_int_id@,       
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.resource_id:resource_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.period_start_date:time_period_start@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.period_end_date:time_period_end@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:a.timesheet_status_name:timesheet_status@,       
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:@DBUSER@.CMN_DATEDIFF_FCT(a.period_end_date + 1,a.period_start_date):period_duration@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:
               CASE WHEN (a.period_end_date - a.period_start_date) = 2 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 1
                    WHEN (a.period_end_date - a.period_start_date) = 1 AND TO_CHAR(a.period_start_date,'D') = '1' THEN 1
                    WHEN (a.period_end_date - a.period_start_date) = 1 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 1
                    ELSE 0 END:is_weekend@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:
               CASE WHEN a.timesheet_status = 1 OR a.period_end_date > @SYSDATE@ THEN 0
                    WHEN (a.period_end_date - a.period_start_date) = 2 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 0
                    ELSE 1 END:action_required@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:TIMESHEETS:
               CASE WHEN a.timesheet_status = 1 THEN 1  -- submitted
                    WHEN a.period_end_date > @SYSDATE@ THEN 0  -- current                    
                    ELSE 2 END:status_sl@,
/*new*/ @select:metric:user_def:implied:SUM(@NVL@(te.practsum,0)/(CASE WHEN r.resource_type > 1 THEN 1 ELSE 3600 END)):total_actuals@      
FROM  (SELECT ts.prid timesheet_int_id,
              r.id resource_int_id,
              r.unique_name resource_id,
              r.full_name resource_name,              
              tp.prid timeperiod_int_id,
              tp.prstart period_start_date,
              tp.prfinish - 1 period_end_date,
              ts.prstatus timesheet_status,
              st.name timesheet_status_name,
/*new*/      te.practsum practsum              
       FROM   prtimesheet ts
       INNER  JOIN prtimeperiod tp ON ts.prtimeperiodid = tp.prid
       INNER  JOIN srm_resources r ON ts.prresourceid  r.id = te.prtimesheetid
/*new*/ INNER  JOIN  PRTIMEENTRY te ON  = ts.prid
       LEFT   OUTER JOIN cmn_lookups_v st ON  ts.prstatus = st.lookup_enum
                                          AND st.language_code = @WHERE:PARAM:LANGUAGE@
                                          AND st.lookup_type = 'TIMESHEET_STATUS'       
       WHERE  r.resource_type = 0
       AND    r.user_id = @WHERE:PARAM:USER_ID@
       AND    r.is_active = 1       
       AND    ts.prisadjustment = 0
       AND    ts.prstatus > 3
       AND      ts.posted_time IS NOT NULL
       AND    tp.prstart BETWEEN ADD_MONTHS(@SYSDATE@,@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:months@,-3)) AND @SYSDATE@
       UNION
       SELECT NULL timesheet_int_id,
              r.id resource_int_id,
              r.unique_name resource_id,
              r.full_name resource_name,              
              tp.prid timeperiod_int_id,
              tp.prstart period_start_date,
              tp.prfinish - 1 period_end_date,
              0 timesheet_status,
              st.name timesheet_status_name,
/*new*/      te.practsum practsum              
       FROM   srm_resources r
       INNER  JOIN prj_resources rm ON r.id = rm.prID
       LEFT   OUTER JOIN srm_resources rr ON rm.prprimaryroleid = rr.id
       LEFT   OUTER JOIN cmn_sec_users u  ON r.user_id = u.id
       INNER  JOIN (SELECT r1.id resource_id,
                           tp1.prid period_id
                    FROM   srm_resources r1
                    INNER  JOIN prj_resources rm1 ON  r1.id = rm1.prID
                    INNER  JOIN prtimeperiod tp1  ON  @NVL@(r1.date_of_hire,TO_DATE('01/01/1900','MM/DD/YYYY')) < tp1.prfinish - 1
                                                  AND @NVL@(r1.date_of_termination,TO_DATE('12/31/2100','MM/DD/YYYY')) > tp1.prstart
                    LEFT   OUTER JOIN prtimesheet ts1 ON  r1.id = ts1.prresourceid
                                                      AND tp1.prid = ts1.prtimeperiodid
           /*new*/ INNER  JOIN  PRTIMEENTRY te ON ts1.prid = te.prtimesheetid
                    WHERE  rm1.prisopen = 1
                    AND    rm1.prtrackmode IN (1,2)
                    AND    rm1.prisrole = 0                    
                    AND    @NVL@(ts1.prID,0) = 0
                    AND    r1.is_active = 1
                    AND    r1.user_id = @WHERE:PARAM:USER_ID@
                  ) miss ON r.id = miss.resource_id
       INNER  JOIN prtimeperiod tp ON miss.period_id = tp.prid
       LEFT   OUTER JOIN cmn_lookups_v st ON  st.lookup_enum = 0
                                          AND st.language_code = @WHERE:PARAM:LANGUAGE@
                                          AND st.lookup_type = 'TIMESHEET_STATUS'       
       WHERE  tp.prstart BETWEEN ADD_MONTHS(@SYSDATE@,(@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:months@,-3))) AND @SYSDATE@
       AND    tp.prisopen = 1       
) a
WHERE  @FILTER@

The error that I get is:

ErrorError:NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00920: invalid relational operator SQL Text: SELECT a.resource_int_id || a.timeperiod_int_id || NVL(a.timesheet_int_id,0) dim_id, a.timeperiod_int_id timeperiod_int_id, a.timesheet_int_id timesheet_int_id, a.resource_int_id resource_int_id, a.resource_id resource_id, a.period_start_date time_period_start, a.period_end_date time_period_end, a.timesheet_status_name timesheet_status, CMN_DATEDIFF_FCT(a.period_end_date + 1,a.period_start_date) period_duration, CASE WHEN (a.period_end_date - a.period_start_date) = 2 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 1 WHEN (a.period_end_date - a.period_start_date) = 1 AND TO_CHAR(a.period_start_date,'D') = '1' THEN 1 WHEN (a.period_end_date - a.period_start_date) = 1 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 1 ELSE 0 END is_weekend, CASE WHEN a.timesheet_status = 1 OR a.period_end_date > SYSDATE THEN 0 WHEN (a.period_end_date - a.period_start_date) = 2 AND TO_CHAR(a.period_start_date,'D') = '7' THEN 0 ELSE 1 END action_required, CASE WHEN a.timesheet_status = 1 THEN 1 -- submitted WHEN a.period_end_date > SYSDATE THEN 0 -- current ELSE 2 END status_sl, /*uusi*/ SUM(NVL(te.practsum,0)/(CASE WHEN r.resource_type > 1 THEN 1 ELSE 3600 END)) total_actuals FROM (SELECT ts.prid timesheet_int_id, r.id resource_int_id, r.unique_name resource_id, r.full_name resource_name, tp.prid timeperiod_int_id, tp.prstart period_start_date, tp.prfinish - 1 period_end_date, ts.prstatus timesheet_status, st.name timesheet_status_name, /*uusi*/ te.practsum practsum FROM prtimesheet ts INNER JOIN prtimeperiod tp ON ts.prtimeperiodid = tp.prid INNER JOIN srm_resources r ON ts.prresourceid r.id = te.prtimesheetid /*uusi*/ INNER JOIN PRTIMEENTRY te ON = ts.prid LEFT OUTER JOIN cmn_lookups_v st ON ts.prstatus = st.lookup_enum AND st.language_code = ? AND st.lookup_type = 'TIMESHEET_STATUS' WHERE r.resource_type = 0 AND r.user_id = ? AND r.is_active = 1 AND ts.prisadjustment = 0 AND ts.prstatus > 3 AND tp.prstart BETWEEN ADD_MONTHS(SYSDATE,NVL(?,-3)) AND SYSDATE UNION SELECT NULL timesheet_int_id, r.id resource_int_id, r.unique_name resource_id, r.full_name resource_name, tp.prid timeperiod_int_id, tp.prstart period_start_date, tp.prfinish - 1 period_end_date, 0 timesheet_status, st.name timesheet_status_name, /*uusi*/ te.practsum practsum FROM srm_resources r INNER JOIN prj_resources rm ON r.id = rm.prID LEFT OUTER JOIN srm_resources rr ON rm.prprimaryroleid = rr.id LEFT OUTER JOIN cmn_sec_users u ON r.user_id = u.id INNER JOIN (SELECT r1.id resource_id, tp1.prid period_id FROM srm_resources r1 INNER JOIN prj_resources rm1 ON r1.id = rm1.prID INNER JOIN prtimeperiod tp1 ON NVL(r1.date_of_hire,TO_DATE('01/01/1900','MM/DD/YYYY')) < tp1.prfinish - 1 AND NVL(r1.date_of_termination,TO_DATE('12/31/2100','MM/DD/YYYY')) > tp1.prstart LEFT OUTER JOIN prtimesheet ts1 ON r1.id = ts1.prresourceid AND tp1.prid = ts1.prtimeperiodid /*uusi*/ INNER JOIN PRTIMEENTRY te ON ts1.prid = te.prtimesheetid WHERE rm1.prisopen = 1 AND rm1.prtrackmode IN (1,2) AND rm1.prisrole = 0 AND NVL(ts1.prID,0) = 0 AND r1.is_active = 1 AND r1.user_id = ? ) miss ON r.id = miss.resource_id INNER JOIN prtimeperiod tp ON miss.period_id = tp.prid LEFT OUTER JOIN cmn_lookups_v st ON st.lookup_enum = 0 AND st.language_code = ? AND st.lookup_type = 'TIMESHEET_STATUS' WHERE tp.prstart BETWEEN ADD_MONTHS(SYSDATE,(NVL(?,-3))) AND SYSDATE AND tp.prisopen = 1 ) a WHERE 1=? and 1=1 .

 

All help is appreciated here :-)

Best regards,

Mika Bäckström

Outcomes