AnsweredAssumed Answered

Time Entries with zero hours

Question asked by Rajani on Aug 11, 2015
Latest reply on Aug 11, 2015 by Dave_3.0

Hello,

 

We have a requirement where we send time entered in Clarity including zero entries to another program.  When hours are zero, slices do not have an entry. We need to send zeroes for all those days. Timesheet with all entries 0s is a valid scenario and i need your help with this.

 

Appreciate your help with the query. Thank you!

 

select

'"' || r.unique_name || '"' res_id,

tp1.prstart,

'"' || to_char(slice.slice_date,'MM/DD/YYYY') || '"' work_date,

'"' || 0 || '"' units_worked

FROM  prtimeperiod tp1

join prtimesheet ts1

on tp1.prid =  ts1.prtimeperiodid

join prtimeentry te1

on ts1.prid = te1.prtimesheetid

and ts1.prstatus = 4 -- Posted Timesheets

join srm_resources r

on r.id = ts1.prresourceid

left join prj_blb_slices slice

on slice.prj_object_id = te1.prid

and slice.slice_request_id = 55555

where

ts1.posted_time > sysdate - 7

and timesheet_sum(r.id, ts1.prid) = 0 -- timeentry is zero

 

 

Function

create or replace FUNCTION timesheet_sum (p_resource NUMBER, p_timesheet NUMBER)

   RETURN NUMBER

IS

   v_new_num NUMBER;

BEGIN

   select sum(practsum) INTO v_new_num

   from prtimeentry te, prtimesheet ts, srm_Resources r

   where ts.prid = te.prtimesheetid and r.id = ts.prresourceid

   and ts.prstatus =4

   and r.id =p_resource  and ts.prid = p_timesheet;

   if (v_new_num > 0) then

      v_new_num := v_new_num / 3600;

   End if ;

      return v_new_num;

EXCEPTION

WHEN VALUE_ERROR THEN

   RETURN v_new_num;

END timesheet_sum;

Outcomes