Clarity

 View Only
Expand all | Collapse all

Getting Timesheet Data using NSQL

Jump to Best Answer
  • 1.  Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 05:12 AM
    Hi All,

    Can I get the Timesheet Data of a resource for all open time timeperiods by using an NSQL query.

    Actually when I access the timesheetApprovals webservice of Clarity, it gives data only for the current week, which is a limitation.
    Can someone help me with the NSQL query, I tried to look into the databse but could not find the appropriate tables to get the data from.

    Regards,
    Divyang Agrawal


  • 2.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 05:17 AM
    Resource (PRJ_RESOURCES.ID) and Time Period (PRTIMEPERIOD.ID) link to PRTIMESHEET (prresourceid and prtimeperiodid

    prtimesheet links to PRTIMEENTRY for the rows in a timesheet

    and PRTIMEENTRY links to the PRASSIGNMENT (and then to the TASK, PROJECT etc)

    You can only get the daily time values on the timesheet via the timeslicing data - a "Daily Time Entries" slice gives you this if you need it


  • 3.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 05:26 AM
    Hi,

    Thanks for the reply Dave.

    I will now start working on the query. Can you please elaborate on the Daily Time Entries..
    I didn't get you completely.

    Regards,
    Divyang Agrawal


  • 4.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 05:38 AM
    " Can you please elaborate on the Daily Time Entries"

    When you fill the timesheet, you fill hours against each day - this timeentry data (data for each day) is stored in slice table, the prrecordid in the prj_blb_slices (I guess), is linked to prtimeentry.prid (I guess) - NOTE:- I do not have the reference guide in front of me. You can check the tech ref guide

    What Dave meant is that if you want to check the data daily for a task, you can check the prj_blb_slices table, which can be linked to the prj_blb_slicerequests table (which holds the slice info).

    If you get the data from prtimeentry table, it will give the sum of actuals that have been filled on the task

    @ Dave - Please correct me if I am wrong

    NJ


  • 5.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 05:44 AM

    navzjoshi00 wrote:

    You can check the tech ref guide...
    @NJ - no you are correct of course :grin: ; only that the "tech ref guide" does not have a nice ERD diagram in it for this area :sad (I know this because I just went looking for one to refer to in this thread!!!!)


  • 6.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 06:38 AM
    If you wanna go below the bottom line it is
    PRTIMEENTRYID.PRACTSUM for the time entry period on one row on the timesheet.

    Daily Time Entries slice definition which usually has 90 days comes with the PMO accelerator (according to NJ) so you are better of creating your own and you have to if you don't have PMO isntgallted.

    Martti K.


  • 7.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 06:55 AM
    In my (test) v13 instance (I don't have the PMO accelerator installed and this was the base 13 database) I have an DAILYRESOURCETIMECURVE slice with id 55555 over "Time Entries" (there are a bunch of slices in the same id range)

    Could be that the PMO accelerator has its own slices as well though and as said we can create our our slices too....

    ....the answer is just to "check first" then!

    (We don't want to make time slicing do any more work than it already does by duplicating slices :tongue !!!!)


  • 8.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 07:33 AM
      |   view attached
    Hi,

    Here is an sample timehseet sql (oracle) - considers posted status and also an ER diagram of timesheets.

    SELECT T.PRRESOURCEID , T.PRTIMEPERIODID , P.PRSTART,P.PRFINISH,E.PRACTSUM,
    E.PRASSIGNMENTID, E.PRTIMESHEETID, T.PRSTATUS,
    C.PRID,C.PRNAME, C.PREXTERNALID ,
    PRT.PRNAME TYPECODE_NAME, PRT.PREXTERNALID TYPECODE_UNAME
    FROM PRTIMEPERIOD P,PRTIMESHEET T,PRTIMEENTRY E,PRCHARGECODE C , PRTYPECODE PRT
    WHERE
    P.PRID=T.PRTIMEPERIODID AND T.PRID=E.PRTIMESHEETID AND
    E.PRCHARGECODEID = C.PRID AND E.PRTYPECODEID = PRT.PRID
    AND T.PRSTATUS=4



    cheers,
    sundar


  • 9.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 07:51 AM
    ^ nice. cool)

    (no slice tables though! :blink: )


  • 10.  RE: Getting Timesheet Data using NSQL

    Posted Nov 05, 2012 01:27 AM
    Hi Sundar,

    The query yields records but it shows data of all the timeperiods which occur before my current open Timeperiod.

    My other doubt is regarding what table contains the status of the timeperiod whether it is open or closed. I tried checking
    TIMEPERIODS table but it does not contain any status column which tells us whether the timeperiod is open or closed.

    I am using the following NSQL:

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:RTE:R.ID:RESOURCE_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:R.FULL_NAME:RESOURCE_FULLNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRSTART:PERIOD_STARTDATE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:TP.PRFINISH-1:PERIOD_FINISHDATE@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:L.NAME:TIMESHEET_STATUS@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RTE:I.NAME:INVESTMENT_NAME@,
    @SELECT:METRIC:USER_DEF:IMPLIED:SUM(TE.PRACTSUM/3600):RESOURCE_HOURS@
    FROM PRTIMESHEET TS
    INNER JOIN SRM_RESOURCES R on TS.PRRESOURCEID = R.ID
    INNER JOIN PRTIMEPERIOD TP on TS.PRTIMEPERIODID = TP.PRID
    INNER JOIN CMN_LOOKUPS_V L on TS.PRSTATUS = L.LOOKUP_ENUM
    AND L.LOOKUP_TYPE = 'Timesheet_status'
    AND L.LANGUAGE_CODE = 'en'
    INNER JOIN PRTIMEENTRY TE on TS.PRID = TE.PRTIMESHEETID
    LEFT OUTER JOIN PRASSIGNMENT A on TE.PRASSIGNMENTID = A.PRID
    LEFT OUTER JOIN PRTASK T on A.PRTASKID = T.PRID
    LEFT OUTER JOIN INV_INVESTMENTS I on T.PRPROJECTID = I.ID
    LEFT OUTER JOIN PRCHARGECODE CD on TE.PRCHARGECODEID = CD.PRID
    AND @FILTER@
    GROUP BY R.ID, TE.USER_LOV1, I.NAME, R.FULL_NAME,
    TP.PRSTART, TP.PRFINISH-1,L.NAME, CD.PRNAME, TS.PRUID
    HAVING @HAVING_FILTER@

    Kindly provide some inputs.

    Regards,
    Divyang Agrawal


  • 11.  RE: Getting Timesheet Data using NSQL

    Posted Nov 05, 2012 03:54 AM
    Tech ref for prtimeperiod

    PRISOPEN



    Indicates whether actuals can be entered for a time period. Values are:

    0 = Actuals can be entered
    1 = Actuals cannot be entered

    Did you miss that or is there some other problem?

    Martti K.


  • 12.  RE: Getting Timesheet Data using NSQL

    Posted Nov 05, 2012 08:00 AM
    Hi,

    If you want to show only the open and current timesheet you can add the below conditions.


    AND TP.PRISOPEN=1
    AND SYSDATE BETWEEN TP.PRSTART AND TP.PRFINISH-1

    cheers,
    sundar


  • 13.  RE: Getting Timesheet Data using NSQL

    Posted Nov 05, 2012 10:49 PM
    Hi,

    Thanks all for the valuable inputs.
    I am able to get the timesheet data for all open timeperiods, but I could not find the slices i.e. daily time entries of the user in the PRJ_BLB_SLICES table

    Is there any E-R Diagram that defines where are the slices stored in the database, because I looked through many slice tables but could not find the desired result.

    Regards,
    Divyang Agrawal


  • 14.  RE: Getting Timesheet Data using NSQL

    Posted Nov 06, 2012 07:35 AM
      |   view attached
    Divyang,

    Hope for daily time sheet entries you can use the assignment slice.we can use the daily sliceid 2- DAILYRESOURCEACTCURVE itself.The join is between the prj_object_id in slice with the prid column in prassignment table.
    It will be the same.

    Note:- For Indirect (Not aginst project)- That is for vacation etc the entries are not captured.

    SELECT
    i.name Project, i.code "Project ID", t.prname "Task",r.unique_name "Resource ID",r.full_name "Resource",
    to_char(tp.prstart,'dd/mm/yy') || ' - ' || to_char(tp.prfinish-1,'dd/mm/yy') "TimePeriod",
    to_char(to_date(slice_date),'Day') || ' - ' || to_char(slice_date,'dd/mm/yy') "Day", p.slice Hours
    FROM
    PRTASK t, PRAssignment a, prj_blb_slices p, srm_resources r, inv_investments i,
    prtimesheet tst, prtimeperiod tp
    WHERE
    a.prtaskid=t.prid AND r.id=a.prresourceid AND p.prj_object_ID=a.prid AND t.PRPROJECTID=i.id
    AND r.id=tst.prresourceid And tst.prtimeperiodid=tp.prid
    AND r.unique_name ='Enter Resource Id'
    AND tst.prstatus=4
    AND p.slice_request_id=2
    AND p.slice_date between '08-oct-12' And '12-oct-12'
    AND tp.prstart between '06-oct-12' And '12-oct-12'
    order by p.slice_date

    If you purposely want to extract from timeentry stuff then you can create an custom timeslice as below in diagram and use it.

    SELECT
    r.full_name resource_name,
    r.unique_name,
    to_char(tp.prstart,'dd/mm/yy') || ' - ' || to_char(tp.prfinish-1,'dd/mm/yy') "TimePeriod",
    to_char(to_date(slice_date),'Day') || ' - ' || to_char(slice_date,'dd/mm/yy') "Day",
    Slice
    FROM
    prj_blb_slices s,
    prj_blb_slicerequests b,
    prtimeentry te,
    prtimesheet ts,
    prtimeperiod tp,
    srm_resources r
    WHERE
    s.slice_request_id = b.id and
    s.prj_object_id = te.prid and
    te.prtimesheetid = ts.prid and
    ts.prtimeperiodid = tp.prid and
    ts.prresourceid = r.id AND
    r.unique_name = 'Enter Resource Id' AND
    s.slice_request_id='Enter Custom Slice id' and
    tp.prstart between '01-oct-12' And '31-oct-12'
    order by slice_date

    cheers,
    sundar


  • 15.  RE: Getting Timesheet Data using NSQL

    Posted Nov 07, 2012 06:26 AM
      |   view attached
    Hi,

    Thanks for the reply Sundar.
    I will try getting the relevant data for my requirement.

    I have one more bottleneck.The scenario is as follows:-

    When we use the timesheetApprovals web Service of clarity, we get the data only for the current week.
    I am using the following string:-

    <?xml version="1.0" encoding="UTF-8"?>
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
    <Header version="12.1.1.1208" action="read" objectType="timesheetApprovals" externalSource="NIKU"/>
    </NikuDataBus>

    from which I get the following data for current week like result.xml file attached.

    Now if i use the same xml to write the data for an open timeperiod other than the timeperiod of the current week like

    <?xml version="1.0" encoding="UTF-8"?>
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_timesheetApproval.xsd">
    <Header action="write" externalSource="NIKU" objectType="timesheetApproval" version="12.1.1.1208"/>
    <timesheetApprovals>
    <timesheetApproval isAdjusted="false" isAdjustment="false" resourceDbId="db_id" resourceId="r_ID" resourceName="r_name"
    start="2012-09-23T00:00:00" status="3" timeperiodDbId="db_id" timesheetDbId="db_id" total="11" approved="true"/>
    </timesheetApprovals>
    </NikuDataBus>

    It gives me an error saying can't approve timesheet. You should have access rights to approve the timeshhets.

    I have tried this by logging from admin and by giving the admin instance rights to approve time for that particular resource.

    Regards,
    Divyang Agrawal

    Attachment(s)

    xml
    result.xml   1 KB 1 version


  • 16.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 08:01 AM
    "Daily Time Entries slice definition which usually has 90 days comes with the PMO accelerator (according to NJ) so you are better of creating your own and you have to if you don't have PMO isntgallted" - Correct

    However, should be careful when defining the time-frame of the custom ones.

    NJ


  • 17.  RE: Getting Timesheet Data using NSQL

    Posted Nov 02, 2012 11:17 AM
    We don't want to make time slicing do any more work than it already does by duplicating slices
    is one guideline. another is that we do not want use for custom purposes anything which may change uannounced in any version upgrade or patch.

    On the other hand the user configurable timeslices are something that you should verify after every upgrade that you have them configured as you need.


    Martti K.


  • 18.  RE: Getting Timesheet Data using NSQL
    Best Answer

    Posted Nov 02, 2012 05:42 AM
    PRTIMEENTRY record will contain the total actuals entered in the timesheet row for the period (eg a weekly total)

    To get the DAILY values separately you can only do this via a "time slice".

    Depending on your Clarity version there may be one set up by-default to provide that data (look in the timeslices configuration in the admin tool for a "Daily Time Entries" timeslice) - later Clarity versions do have a slice set up by default, older Clarity versions do not (and so we can just create our own)

    Either-way, the daily timeentry data will then be in the slice table (PRJ_BLB_SLICES) where the object_id is just the PRTIMEENTRYID

    (Note that there are some complications around timeslicing (many many many discussions on these boards around that, it is not a simple answer/discussion I'm afraid) - e.g. the data is not available in the table immediately, the "timeslicing" job populates that table - and because the data is a "slice" there is only data present for the specific date range that the slice is configured for (and there are other technical&functional implications of the slice configuration configuration).

    --

    Bottom line ; if you are not interested in the DAILY values, then just get the data from PRTIMEENTRY :grin: , if you are interested in daily values then you need to understand how time slicing works and you have some reading to do! :sad