Clarity

-
Does anyone have a NSQL or SQL that shows the Timesheet NOTES for a user's Timesheet that you can share?
-
Thanks in advance
-
Stephen

Hi.

I use these SQL order to get notes for a specified user and a specified month

Hope it helps.
Fred

SELECT N.PRMODTIME AS HEURE, N.PRMODBY AS AUTEUR, N.PRVALUE AS NOTE 
FROM NIKU.PRTIMESHEET S,NIKU.PRTIMEPERIOD P,NIKU.PRNOTE N 
WHERE S.PRTIMEPERIODID = P.PRID 
AND TRUNC(P.PRSTART,\'MONTH\') = TO_DATE(\'{0}\',\'DD/MM/YY\') 
AND S.PRRESOURCEID = {1} 
AND UPPER(N.PRTABLENAME) = \'PRTIMESHEET\' 
AND N.PRRECORDID = S.PRID ORDER BY N.PRMODTIME

*********Please see if that works for you**********

NSQL for timesheet review with notes (V12):

SELECT @select:dim:user_def:implied:timeentry:te.prid:timeentry_id@,
@select:dim_prop:user_def:implied:timeentry:inv.id:investment_int_id@,
@select:dim_prop:user_def:implied:timeentry:inv.code:investment_id@,
@select:dim_prop:user_def:implied:timeentry:inv.name:investment_name@,
@select:dim_prop:user_def:implied:timeentry:inv.odf_object_code:investment_type@,
@select:dim_prop:user_def:implied:timeentry:UPPER(inv.odf_object_code):investment_type_upper@,
@select:dim_prop:user_def:implied:timeentry:inv.manager_id:investment_manager_int_id@,
@select:dim_prop:user_def:implied:timeentry:u.last_name||', '||u.first_name:investment_manager@,
@select:dim_prop:user_def:implied:timeentry:t.prid:task_int_id@,
@select:dim_prop:user_def:implied:timeentry:t.prexternalid:task_id@,
@select:dim_prop:user_def:implied:timeentry:t.prname:task_name@,
@select:dim_prop:user_def:implied:timeentry:t.prwbssequence:wbs_sequence@,
@select:dim_prop:user_def:implied:timeentry:a.prid:assignment_int_id@,
@select:dim_prop:user_def:implied:timeentry:r.id:resource_int_id@,
@select:dim_prop:user_def:implied:timeentry:r.last_name ||', '|| r.first_name:resource_name@,
@select:dim_prop:user_def:implied:timeentry:r.person_type:resource_type_id@,
@select:dim_prop:user_def:implied:timeentry:v.name:resource_type@,
@select:dim_prop:user_def:implied:timeentry:ts.prid:timesheet_int_id@,
@select:dim_prop:user_def:implied:timeentry:'/niku/app?action=timeadmin.editTimesheet&id=' || to_char(ts.prid) || '&' || 'resid=' || to_char(r.id) || '&' || 'tpid=' || to_char(tp.prid):timesheet_link@,
@select:dim_prop:user_def:implied:timeentry:ts.prstatus:timesheet_status_id@,
@select:dim_prop:user_def:implied:timeentry:v2.name:timesheet_status@,
@select:dim_prop:user_def:implied:timeentry:tp.prid:time_period_int_id@,
@select:dim_prop:user_def:implied:timeentry:tp.prstart:time_period_start@,
@select:dim_prop:user_def:implied:timeentry:tp.prfinish - 1:time_period_end@,
@select:dim_prop:user_def:boolean:timeentry:DECODE(NVL(note.note_id,0),0,0,1):note_attached@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END))):pending_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END),0)):posted_actuals@,
@select:metric:user_def:implied:SUM(DECODE(ts.prstatus,4,0,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END)))
+ SUM(DECODE(ts.prstatus,4,NVL(te.practsum,0)/(CASE WHEN r.resource_type <> 0 THEN 1 ELSE 3600 END),0)):total_actuals@,
@select:dim_prop:user_def:implied:timeentry:r2.full_name:team_leader_name@

FROM inv_investments inv,
prtask t,
cmn_sec_users u,
srm_resources r2,
prassignment a,
srm_resources r,
cmn_lookups_v v,
prtimeentry te,
srm_resources m,
(SELECT te.prid entry_id, MAX(tn.prid) note_id
FROM prtimeentry te, prnote tn
WHERE te.prid = tn.prrecordid
AND tn.prtablename = 'PRTimeEntry'
GROUP BY te.prid) note,
prtimesheet ts,
cmn_lookups_v v2,
prtimeperiod tp
WHERE inv.id = t.prprojectid
AND inv.is_active <> 0
AND inv.manager_id = u.id(+)
AND t.prid = a.prtaskid
AND a.prresourceid = r.id
AND r.person_type = v.id(+) AND v.lookup_type(+) = 'SRM_RESOURCE_TYPE' and v.language_code(+) = @WHERE:PARAM:LANGUAGE@
AND inv.id = DECODE(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,NULL,inv.id,@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
AND a.prid = te.prassignmentid
AND te.prid = note.entry_id(+)
AND te.prtimesheetid = ts.prid
AND ts.prstatus = v2.lookup_enum(+) and v2.lookup_type(+) = 'TIMESHEET_STATUS' AND v2.language_code(+) = @WHERE:PARAM:LANGUAGE@
AND ts.prtimeperiodid = tp.prid
AND inv.ODF_OBJECT_CODE='project'
AND r.MANAGER_ID=m.USER_ID(+)
AND (@WHERE:SECURITY:INV:inv.id@
OR @WHERE:SECURITY:RESOURCE:r.id@)
AND @WHERE:PARAM:USER_DEF:INTEGER:m.USER_ID:MANAGER_ID@
AND (@WHERE:PARAM:USER_DEF:INTEGER:inv.manager_id:INV_MANAGER_ID@)
AND (TP.PRID=@WHERE:PARAM:USER_DEF:INTEGER:TIMEPERIOD@)
AND r.manager_id = r2.user_id
AND @FILTER@
GROUP BY r2.full_name,te.prid, inv.id, inv.code, inv.name, inv.odf_object_code, inv.manager_id, u.id, u.last_name||', '||u.first_name, t.prid, t.prexternalid, t.prname, t.prwbssequence,
a.prid, r.id, r.last_name ||', '|| r.first_name, r.person_type, v.name, ts.prid, ts.prstatus, v2.name, tp.prid, tp.prstart, tp.prfinish - 1, note.note_id

Rishiraj -
-
Thanks for the NSQL.
-
the portlet does not return any data.
-
The NSQL was built, the portlet was attached to the NSQL - but no data returns.
-
Any ideas?
-
Stephen.forney@gohomeward.com

Start commenting out the where conditions in the query one at a time and test.

Martti K.

Stephen,

Please test commenting out the where condition one at a time. Use the SQL test first before the NSQL query and portlet to see the output.
Martti also suggested the same thing.

I used it one of my previous company so cannot check it on my system.
Just break the NSQL/SQL into small piece and execute.

Cheers,
Rishi

I just re frame the NSQL and use it in my system. Below is the Query i used.

SELECT @select:dim:user_def:implied:timenote:tn.prid:note_id@,
@select:dim_prop:user_def:implied:timenote:srm.name:investment_name@,
@select:dim_prop:user_def:implied:timenote:t.prname:task_name@,
@select:dim_prop:user_def:implied:timenote:r.last_name ||', '|| r.first_name:resource_name@,
@select:dim_prop:user_def:implied:timenote:tn.prcategory:note_category@,
@select:dim_prop:user_def:implied:timenote:tn.prvalue:note_text@,
@select:dim_prop:user_def:implied:timenote:u.last_name||', '||u.first_name:created_by@,
@select:dim_prop:user_def:implied:timenote:tn.prcreatedtime:created_dt@,
@select:dim_prop:user_def:implied:timenote:u2.last_name||', '||u2.first_name:last_modified_by@,
@select:dim_prop:user_def:implied:timenote:tn.prmodtime:last_modified_dt@,
@select:dim_prop:user_def:implied:timenote:tp.prstart:time_period_start@,
@select:dim_prop:user_def:implied:timenote:tp.prfinish - 1:time_period_end@
FROM srm_projects srm,
prtask t,
prassignment a,
srm_resources r,
prtimeentry te,
prnote tn,
cmn_sec_users u,
cmn_sec_users u2,
prtimesheet ts,
prtimeperiod tp
WHERE srm.id = t.prprojectid
AND srm.is_active <> 0
AND t.prid = a.prtaskid
AND a.prresourceid = r.id
AND a.prid = te.prassignmentid
AND te.prid = tn.prrecordid
AND tn.prtablename = 'PRTimeEntry'
AND tn.prrecordid = 6286222
AND tn.prcreatedby = u.user_name
AND tn.prmodby = u2.user_name
AND te.prtimesheetid = ts.prid
AND ts.prtimeperiodid = tp.prid
AND @FILTER@

--Kindly note below condition in the above NSQL is use to see for limited records. Please remove it when you would be doing it on your system
AND tn.prrecordid = 6286222

and i am getting the o/p correctly.

RESOLVED -
-
Thanks to all for your helpfull comments.
-
Stephen.Forney@gohomeward.com

PMD:STATEMENT-SET: timeadmin.getNotesList in timeadminPMD.xml
PMD:STATEMENT: timeadmin.GetNotes in timeadminPMD.xml at Wed Jan 25 10:02:22 CET 2012
NDE:PSTM@5ef8106d: Non-SQL Time: 0
SELECT (CASE WHEN PRCREATEDBY=? THEN 1 ELSE 0 END) AS EDITABLE,
U.LAST_NAME,
U.FIRST_NAME,
PRNOTE.*
FROM PRNOTE, CMN_SEC_USERS U
WHERE PRTABLENAME=?
AND PRRECORDID=?
AND U.USER_NAME = PRNOTE.PRCREATEDBY
ORDER BY PRCREATEDTIME desc
NDE:CONN@720d0710: prepareStatement(string,int,int)
NDE:PSTM@5ef8106d: setString(): 1, <user_name>
NDE:PSTM@5ef8106d: setObject(): 2, PRTimeEntry
NDE:PSTM@5ef8106d: setInt(): 3, <timesheetid>


PMD:STATEMENT-SET: timeadmin.NoteCategories in timeadminPMD.xml
PMD:STATEMENT: timeadmin.NoteCategories in timeadminPMD.xml at Wed Jan 25 10:02:22 CET 2012
NDE:PSTM@32dc1d21: Non-SQL Time: 15
SELECT LU.NAME
FROM CMN_LOOKUPS_V LU, CMN_LANGUAGES, CMN_SEC_USERS
WHERE UPPER(LU.LOOKUP_TYPE)='PRNOTESCATEGORY'
AND LU.LANGUAGE_CODE=CMN_LANGUAGES.LANGUAGE_CODE
AND CMN_LANGUAGES.ID=CMN_SEC_USERS.LANGUAGE_ID
AND CMN_SEC_USERS.ID=?
ORDER BY LU.NAME
NDE:CONN@720d0710: prepareStatement(string)
NDE:PSTM@32dc1d21: setInt(): 1, <cmn_sec_users id>


PMD:STATEMENT-SET: timeadmin.GetSystemProperty in timeadminPMD.xml
PMD:STATEMENT: timeadmin.GetSystemProperty in timeadminPMD.xml at Wed Jan 25 10:02:22 CET 2012
NDE:PSTM@532300b3: Non-SQL Time: 0
SELECT V.VALUE
FROM CMN_OPTION_VALUES V , CMN_OPTIONS O
WHERE O.OPTION_CODE = ?
AND V.OPTION_ID = O.ID
AND V.USER_ID IS NULL
NDE:CONN@720d0710: prepareStatement(string)
NDE:PSTM@532300b3: setObject(): 1, NKT.GEN.ENABLE_NOTE_DATE


NJ