AnsweredAssumed Answered

12.3 Timesheet - Time Entry User Lookup - Change from Static Lookups to so

Question asked by Vic on Dec 31, 2009
Latest reply on Feb 4, 2014 by SunilPrasad
Is it possible to change the Time Entry Static Lookup (prtimeentry_user_lov1 and lov2) to a Dynamic Lookups and not break anything downstream?  I've opened a Support Ticket and have a meeting with a SME next week; however, in reviewing the 12.5 release notes, it looks like it is a possibility as a bug fix has been reported.   See the bottom of this message.  Here is what I'm trying to ultimately accomplish.  User Scenario:   When a user is allocated to a NPIO (i.e. Other Work OTW-xxxxx), I would like to display a discrete listing of detailed activities  associated with that NPIO investment only.   Our NPIO investments are large, non-project investments which are buckets for the purpose of budgeting.   The value add here is that we can get to an additional level of granularity in reporting; however, if a static list is used, the static list will be so large, across all NPIOs, we feel the data integrity will be jeopardized as we have a few hundred NPIOs.  Here is what I believe is a solution; however, any feedback would be appreciated. CLRT-46146 (S2) - Timesheet User Value 1 Lookup not working with Dynamic lookup  Synopsis:
Custom User Value 1 lookup for timesheets is not working. When trying to map activity code and name to prtimeentryids on timesheets with a custom table with a custom lookup, then assigning the custom lookup to admin side of Clarity under timesheet options -> User Value 1 lookup, it no longer creates a fourth attribute in browse window of lookup. The user value is also not displayed on the timesheet at all like it used to be in Clarity 7.5.3. Please see TIMESHEET_LOOKUP_ISSUE_20090901.doc and recreation of table and lookup.doc attachments. Steps to Reproduce:
1. Create custom SAP_ACTIVITY_LU_VW table:
a. Create a custom table SAP_ACTIVITY_LU_VW with the following columns (see
DYNAMIC_LOOKUP_ISSUE_-TABLEINFO_20090911.doc):
For Oracle: ACTIVITY_CODE varchar(40); NAME varchar(160); PRTIMESHEETID numeric(10,0); and
PRTIMEENTRYID numeric(10, 0).

For MS SQL Server: ACTIVITY_CODE varchar2(40); NAME varchar2(160); PRTIMESHEETID number(10,0); and
PRTIMEENTRYID number(10, 0).

b. You can use data in the sap_activity_lu_vw.xls spreadsheet to import data into your table created above.

2. Create the custom lookup named: SAP_ACTIVITY_LU2 (refer to recreation of table and lookup.doc for steps):
a. Log into Clarity with an administration account.
b. Click on the admin tool (wrench icon).
c. Click on the Lookups link.
d. Click on the New button to create a new lookup.
e. Type: SAP_ACTIVITY_LU2 in the Name field and select Dynamic Query as the Source.
f. Click the Save and Continue button.
g. Copy and Paste the following query into the Niku Query window (see recreation of table and lookup.doc for
formatting). Query listed below is for Oracle (change || to + if using MS SQL Server in query below):
SELECT
@SELECT:PRTIMEENTRYID:PRTIMEENTRYID@,
@SELECT:ACTIVITY_CODE:USERVALUE@,
@SELECT:(ACTIVITY_CODE || ’-’ ||NAME):USERDISPLAY@
FROM SAP_ACTIVITY_LU_VW
WHERE PRTIMESHEETID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND @FILTER@
h. Click the Save and Continue button.
i. Change Display Attribute to userdisplay and click the Save and Continue button.
This is where the expected result changes.
j. Click the Save and Exit button from the Browse Window page.

3. Change User Value 1 lookup to SAP_ACTIVITY_LU2 and add User Value 1 from Available Columns to Selected Columns of the Timesheet Options on the admin side of Clarity.
a. While still logged into Clarity with administrator account, click on the Timesheet Options link.
b. Click on the remove/delete (garbage can) icon next to the User Value 1 lookup to get rid of default value.
c. Click on the browse (binoculars) icon next to the User Value 1 lookup field.
d. Click on the radio button next to SAP_ACTIVITY_LU2 lookup to select our new lookup.
e. Click on the User Value 1 field under Available Columns.
f. Click on the Move Right button (arrow pointing to the right) to add it to the Selected Columns.
g. Click on the Save button.
h. Click on the Apply to All Resources button and click yes to the alert message.

4. Create a new resource that is open for time entry and has a track mode of Clarity as follows:
a. While still logged into Clarity with administrator account, click on Resources link.
b. Click on the New button. Provide all the information for required fields. Making sure to select Status as Active.
c. Click on the Global link under the Resource’s Access Rights section.
d. Click the Add button, type time in Access Right field, click Filter button, and click in check box next to Timesheet -
Navigate access right, and click the Add button.
e. Click on the Time Reporting Periods link. Make sure there are open time periods, if not, open recent time periods.
f. Click on the Home icon to go to application side of Clarity.
g. Click on the Resources link.
h. Enter Last Name of resource you created in step 4b, click the Filter button, and click on resource name link.
i. Make sure to set the Date of Hire, Track Mode is set to Clarity, Open for Time Entry is selected, Include in Datamart is
selected, and Active is selected. Then click on the Submit button.
j. Click on the Projects link. Find or create a new test project. Add resource created in step 4b. to Team tab -> Staff
page. Click on Schedule of Project and make sure date of project is within date range of open time reporting periods.
Click on the Tasks tab. Click on the Work Breakdown Structure link. Create new tasks if none exist with date ranges
within the project and open time period date ranges. Then assign the resource created in step 4b to the tasks.
k. Log out of Clarity with the administrator account.

5. Log into Clarity with your resource account created in step 4.
a. Click on the Timesheets link.
b. Click on the Populate button. If no tasks appear, please click the Add Tasks button to add the tasks the resource
was assigned to.
c. Once tasks are added to the timesheet, check to see if the User Value 1 field is being populated. If you cannot see
the User Value 1 field, please click on the [Configure] link. Click on User Value 1 field under the Available Columns.
Click on the Move Right button (arrow pointing right) to add field to the Selected Columns. Click the Submit button.
d. There should be a value in the User Value 1 field obtained from the custom SAP_ACTIVITY_LU2 lookup, but there is
not. Expected: To see 4 attributes (prtimeentryid, uservalue, userdisplay, and param_userdeftsid_param) listed in Browse Window when creating the custom SAP_ACTIVITY_LU2 lookup and lookup works. Actual: There are only 3 attributes (prtimeentryid, uservalue, and userdisplay) listed in Browse Window when creating the custom SAP_ACTIVITY_LU2 lookup and lookup doesn’t work.

Outcomes