AnsweredAssumed Answered

Adding protlet security NSQL

Question asked by lonnie.lowe on Dec 13, 2010
Latest reply on Feb 4, 2011 by Angeline
I have a query that works fine when I do not have the @Where: security clasue but it gets a 500 error when I add the securioty clause.
The query is for a protlet that gets the time sheet infomation for users, including both project and non-project time.


The NSQL does preview the SQL. My goal would be to have a person only see the timesheets for people they have the view or edit timesheets rights.

CODE
select @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:resource_name||project_name||task_name||date_worked||hours_worked||Resource_Manager||rownum:unique_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:resource_name:Resource_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:project_name:project_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:task_name:task_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:date_worked:date_worked@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:hours_worked:hours_worked@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:Resource_Manager:Resource_Manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:project_id:project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:task_id:task_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:resource_id:resource_id@
FROM (
SELECT r.full_name resource_name,i.name project_name, t.prName task_name, s.slice_date date_worked, s.slice hours_worked,rm.Full_name Resource_Manager, i.id project_id, t.prID task_id, rm.user_ID user_id, R.ID resource_id
From inv_investments i
INNER JOIN prTask t ON i.id = t.prProjectID
INNER JOIN prAssignment a ON t.prID = A.PRTASKID
INNER JOIN srm_resources r ON a.prResourceID = r.ID
INNER JOIN prj_blb_slices s ON a.prID = s.prj_object_ID
INNER JOIN prj_blb_slicerequests sr ON s.slice_request_id = sr.id
INNER JOIN srm_resources rm ON rm.user_ID = r.MANAGER_ID
WHERE sr.request_name = 'DAILYRESOURCEACTCURVE'
AND PRSTATUS != 5
UNION ALL
SELECT SRM_RESOURCES.FULL_NAME, 'Indirect_Time', PRCHARGECODE.PRNAME, PRJ_BLB_SLICES.SLICE_DATE, PRJ_BLB_SLICES.SLICE, rm.Full_name, PRJ_BLB_SLICES.PRJ_OBJECT_ID, PRTIMEENTRY.PRID, rm.user_ID, SRM_RESOURCES.MANAGER_ID
FROM (((NIKU.PRJ_BLB_SLICES PRJ_BLB_SLICES
INNER JOIN NIKU.PRTIMEENTRY PRTIMEENTRY ON (PRJ_BLB_SLICES.PRJ_OBJECT_ID = PRTIMEENTRY.PRID))
INNER JOIN NIKU.PRTIMESHEET PRTIMESHEET ON (PRTIMEENTRY.PRTIMESHEETID = PRTIMESHEET.PRID))
INNER JOIN NIKU.SRM_RESOURCES SRM_RESOURCES ON (PRTIMESHEET.PRRESOURCEID = SRM_RESOURCES.ID))
INNER JOIN NIKU.PRCHARGECODE PRCHARGECODE ON (PRCHARGECODE.PRID = PRTIMEENTRY.PRCHARGECODEID)
INNER JOIN srm_resources rm ON rm.user_ID = SRM_RESOURCES.MANAGER_ID
where
slice_request_id = 5001001
and PRCHARGECODEID IS NOT NULL
AND PRNAME != 'Expense'
AND PRSTATUS != 5 )
where @FILTER@ AND
@WHERE:SECURITY:RESOURCE:RESOURCES.ID@
HAVING @HAVING_FILTER@

Outcomes