How can we use our own user-defined lookup with the Time Entry User Value 1 field for Timesheet Entries?

Document ID : KB000022205
Last Modified Date : 13/07/2018
Show Technical Document Details
Question:

When configuring the Timesheet Options, the browse window for selecting a lookup on the User Value 1 field only shows a choice of one lookup definition and that is the definition for the stock 'Time Entry User Value 1' Static Lookup. We want to develop our own user-defined dynamic query lookup for use on this field.

How can we get this to work properly?  Are there any special requirements or configuration considerations that should be reviewed in understanding this functionality for using a custom Time Entry User Value 1 dynamic query lookup?

Answer:
FOR USE WITH TIMESHEET ENTRY ONLY
The use of a custom, dynamic lookup on the Timesheet Entry is available with specific requirements and special considerations for storing and displaying data for this field.  It is recommended that you use the stock, 'Time Entry User Value 1' Static Lookup if possible.

Data Considerations:
The use of a user-defined lookup is only applicable to the configuration of the User Value 1 field on the Timesheet Entry.  
This configuration does not apply to other areas of the application.  It does not apply to Financial Transaction Entry or WIP Adjustment pages.
This configuration does not apply to any other database views or stock functions that utilize the default Time Entry User Value 1 lookup.
The USERVALUE can be a 30-character string value or an integer value from a dynamic query lookup.  
The USERVALUE data is stored on the PRTIMEENTRY.USER_LOV1 field.  
The DISPLAYVALUE can be a 30-character string value or an integer value from a dynamic query lookup.  
The value that is saved from the USERVALUE field into the PRTIMEENTRY.USER_LOV1 field is displayed directly on the page after the timesheet is submitted into the workflow.  After the timesheet is submitted into the workflow, the saved value is not converted into the lookup display attribute.  The lookup display attribute is only shown when the field is open for editing on the timesheet.

Reporting Query considerations:  
It is important to use a CODE or ID field to store your USERVALUE if you want to link it back to a lookup list in reporting or portlets. (See Example #3)  
Otherwise, if you use a 30-character string value ; it will be simple to query and display the native value from the saved field. 
 
Query Requirements:
The dynamic query must have the following requirements to function successfully.
  • The Select statement must create an attribute with the alias name of 'PRTIMEENTRYID' 
  • The PRTIMEENTRYID select column attribute must return the PRTIMEENTRY.PRID value as any column defined in the SQL statement 
    • This provides the current time entry row unique ID which is mapped to the PRTIMEENTRY table for saving the data
  • The Select statement must create an attribute with the alias name of 'USERVALUE'
  • The value returned for the 'USERVALUE' is saved and stored on the PRTIMEENTRY.USER_LOV1 field 
  • The USERVALUE select column attribute can return an integer data type if desired
  • The USERVALUE select column attribute can return a string data type if desired
    • If the USERVALUE returns a string - the returned value must be no more than 30 characters in length
  • The DISPLAYVALUE can be a 30-character string value or an integer value from a dynamic query lookup.
    • The DISPLAYVALUE needs to be included in the query for the lookup values to display on submitted and posted timesheets in the New UX.  
  • The WHERE clause must have a specific parameter alias name of 'USERDEFTSID_PARAM' to map the current Timesheet internal ID value for saving the data
  
  • Using Custom Lookup User Value1 lookup along with Data Warehouse:
    • The Select statement must create an attribute with the alias name of 'LANGUAGE_CODE'. The value of this attribute must be from LANGUAGE_CODE column of CMN_LANGUAGES.
    • The Select statement must create an attribute with the alias name of 'LANGUAGE_ID'. The value of this attribute must be from ID column of CMN_LANGUAGES.
    • The query must return unique USERVALUE for the combination of 'PRTIMEENTRYID' & 'LANGUAGE_CODE' attribute.
By default, the “User Value1” attribute is pointing to Static lookup - PRTIMEENTRY_USER_LOV1 so the lookup values are localized. Therefore, in Data Warehouse schema, USER_VALUE1_KEY is a column of DWH_TME_ENTRY while USER_VALUE1 is a column of DWH_TME_ENTRY_LN table.
When selecting a custom lookup as “User Value1 Lookup” there is a possibility that the values returned by custom lookup is not localized.
Make sure to return the values for every language PPM supports, otherwise it would break the OOTB schema, Jaspersoft Domains and any “Reports & Ad-hoc View” using USER_VALUE1 column of DWH_TME_ENTRY_LN table.

This can be done by modifying the lookup query to return the same value for every language when the value is not localized (examples below).

Example #1: User Value Dynamic Lookup required structure, content and format:  
Below is an example of a query that can be used to test this functionality. The query is simple and will return the Internal Task Id (hidden key) value for each time entry row on a timesheet and display the first 30 characters of the Task Name. The CA PPM administrator can use the structure of this query to create a definition for other data. It is important to keep the structure of the query so that it adheres to the requirements as outlined above.  You could also retrieve lookup values from other pre-defined lookups using this method as well. The possibilities for creating user-defined data values on this field is expanded with this concept.  
1.    Log into the application as an administrator user
2.    Navigate Administration, Data Administration: Lookups, click 'New' button
3.    Create a new user-defined, dynamic lookup definition
 
Lookup Properties     

4.    Enter the Query with the following code using the requirement guidelines noted above:
The example below uses Oracle SUBSTR function to resize the Task Name in the event the Task Name is greater than 30 characters.  If you do not reduce the resulting value, a System Error will be generated on the Timesheet Entry page when attempting to save as the USERVALUE is saved to the PRTIMEENTRY.USER_LOV1 field. 

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):USERVALUE@,

@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@

FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND @FILTER@ 

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@,
@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:LANG.ID:LANGUAGE_ID@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK, CMN_LANGUAGES LANG
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND @FILTER@ 
​5.    Click 'Save and Continue' button
6.    Based on the structure of the query, the 'Parent Window' will show the Hidden Key = prtimeentryid and Display Attribute = uservalue

Lookup Parent Window


7.    Click 'Save and Exit' button
8.    Administration, Project Management: Timesheet Options
Default Content and Layout Section:
Select and move 'User Value 1' into the Timesheet Layout
Default Time Entry Options: 
Click the 'Browse' icon for the 'User Value 1 Lookup' field (Select Lookup)
Select 'Custom User Value 1', Click 'Add' button


 Default Time Entry Options Select Lookup   


9.    Click 'Save' button
10. Click 'Apply to All Resources' so that all users will have the same configuration (clears user configurations)
Save Timesheet Options 

Note: After changing “User Value 1 Lookup” value, “Load Data Warehouse” job must be run with “Full Load” option checked.


11.Check out the new functionality
Navigate to Home, Timesheets, locate and open a Timesheet
Add at least one 'Assigned' Task Entry
Go to 'User Value 1' pull-down and see the Task Name value that appears (if the task is not assigned to the resource, no internal record id value will appear)


Note: the User Value 1 display value is a short version of the Task Name as defined on the Lookup query.
 Timesheet Example
This is just one example of how the application can be configured to use a different lookup for the Timesheet User Value 1 field. If the Administrator follows this example, any data can be used. The 'USERVALUE' field in the select statement does not have to be a string data type; it can be an integer value of a record ID or it can be a Lookup Code value.  The value that is saved from the USERVALUE field into the PRTIMEENTRY.USER_LOV1 field is displayed directly on the page after the timesheet is submitted into the workflow.  After the timesheet is submitted into the workflow, the saved value is not converted into the lookup display attribute.
NOTE: The ability to configure the LOV2 is not possible. The LOV2 field on the Timesheet entries will always reference the 'Time Entry User Lookup Field 2' (ID=PRTIMEENTRY_USER_LOV2). The Administration, Timesheet Options page only shows the ability to configure the LOV1 field to select a valid lookup.


Example #2: User Value Dynamic Lookup referencing a stock, static list using Lookup Name in USERVALUE
Now that you see how this functionality works, here is an example that pulls the list of active values from a predefined static list lookup already in the system.
This example uses the values from the 'Other Category' Lookup (ID = INV_OTHER_CATEGORY_TYPE). The following article is helpful in understanding how to query for the static list of lookup values with selections.  Reference TEC438946 : How to Query Static List Lookup Values
1.      Create a new dynamic lookup (Custom User Value 1 - Category Types) with the following query. This query will display the LOOKUP_CODE. Note, the query select fields and WHERE clause parameter are the same as in Example #1.  This must remain the same for it to work.
 SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):DISPLAYVALUE@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = 'en'
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@,
@SELECT:L.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:L.LANGUAGE_ID:LANGUAGE_ID@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE 
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@
 
2.      Change the Administration, Timesheet Options, User Value Lookup 1 to use this new query, click 'Save' button  
3.      Then click 'Apply to all Resources' button  
4.      Test the new configuration    
Timesheet Example #2 
 
The value that is displayed from the Lookup Query in the 'USERVALUE' field stored on the PRTIMEENTRY.USER_LOV1 field in the database.


Example #3: User Value Dynamic Lookup referencing a stock, static list using LOOKUP CODE in USERVALUE
1. Create a lookup that uses the LOOKUP_CODE value in the USERVALUE attribute
 SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):DISPLAYVALUE@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = 'en'
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@,
@SELECT:L.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:L.LANGUAGE_ID:LANGUAGE_ID@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE 
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@
 
2. Select and save a value on the Timesheet (note the values shown are from USERVALUE for the LOOKUP_CODE)
 
Timesheet Example #3
Reporting Query Example:
If you use Example #3 where you store the LOOKUP_CODE from query listing existing lookup values, you can get output of labels associated with the codes.
If you pick 'SECURITY_ADMINISTRATION' as the lookup_code value in this example, the table will store this value.  It is important to use a CODE or ID field to store your user value if you want to link it back to a lookup list in reporting or portlets.  The following query shows you how to link back to the Lookup Name and Description and because the lookups allow for language labels, the query can be modified to check the user account settings or hard-coded for a specific language.  This is a simple join query and will only show entries that have matching rows to the lookup codes. 
SELECT TE.PRTIMESHEETID, TE.PRID TIMEENTRYID, TE.USER_LOV1,
L.LOOKUP_NAME, L.LOOKUP_DESCRIPTION, L.IS_LOOKUP_ACTIVE
FROM PRTIMESHEET TS, PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, 
 N.DESCRIPTION LOOKUP_DESCRIPTION, L.IS_ACTIVE IS_LOOKUP_ACTIVE
     FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
     WHERE L.ID = N.PK_ID
     AND N.LANGUAGE_CODE = 'en'
     AND N.TABLE_NAME = 'CMN_LOOKUPS'
     AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE 1=1
AND TS.PRID = TE.PRTIMESHEETID
AND TE.USER_LOV1 = L.LOOKUP_CODE


When using Data Warehouse, the above query should be modified as:

SELECT TE.PRTIMESHEETID, TE.PRID TIMEENTRYID, TE.USER_LOV1,
L.LOOKUP_NAME, L.LOOKUP_DESCRIPTION, L.IS_LOOKUP_ACTIVE, L.LANGUAGE_ID LANGUAGE_ID, L.LANGUAGE_CODE LANGUAGE_CODE
FROM PRTIMESHEET TS, PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE,  
 N.DESCRIPTION LOOKUP_DESCRIPTION, L.IS_ACTIVE IS_LOOKUP_ACTIVE
     FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
     WHERE L.ID = N.PK_ID
     AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
     AND N.TABLE_NAME = 'CMN_LOOKUPS'
     AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L
WHERE 1=1
AND TS.PRID = TE.PRTIMESHEETID
AND TE.USER_LOV1 = L.LOOKUP_CODE


   Query Results