Lookups

After you have set up LMS groups and divided users, employees, and metrics among them, you need to configure the appropriate lookup on the pages the employees use. For example, if the employees use the Daily Timesheet to enter their time, and you have set up labor metric security for time codes, then you need to configure the Time Code lookups on the Daily Timesheet to filter the available time codes based on labor metric security.

Note: Configuring lookups requires SQL knowledge. You will need to be familiar with writing SELECT statements and constructing WHERE clauses.

Although the specific SELECT statements that are used to configure lookups depends on which labor metric is being filtered, whether you are using user- or employee-based labor metric security, and how users and employees are assigned to LMS groups, your statement needs to have these elements:

A SELECT FROM statement to select the labor metric name from the LABMET_SEC_GRP labor metric table, and either the EMPLOYEE or WORKBRAIN_USER table. Select from the EMPLOYEE table when using employee-based labor metric security. For user-based labor metric security, select from the WORKBRAIN_USER table. For example:
```
SELECT TIME_CODE.TCODE_NAME, TIME_CODE.TCODE_NAME Name 
FROM EMPLOYEE, LABMET_SEC_GRP, TIME_CODE,	 
```
This example replaces the references to time codes with the appropriate labor metric in your statement.
A WHERE clause that determines the employee’s LMS group and matches that group to the labor metric’s LMS group, if you are using employee-based labor metric security. You will have to compare the ID of the employee’s team, calculation group, or pay group to the LMS group’s team, calculation group, or pay group ID, depending on how employees are assigned to LMS groups. For example:
```
WHERE EMPLOYEE.EMP_ID = #request.EMP_ID# AND EMPLOYEE.CALCGRP_ID = 
LABMET_SEC_GRP.CALCGRP_ID AND TIME_CODE.LMS_ID = LABMET_SEC_GRP.LMS_ID,	 
```
This example compares the employee’s calculation group ID to the calculation group IDs that are assigned to the LMS group.

If you are using a combination of an employee’s team, calculation group, or pay group to assign employees to LMS groups, combine the SQL queries using the UNION condition between them. This condition returns employees that are assigned to LMS groups by team, calculation group, or pay group.
A WHERE clause that determines the logged-in user’s LMS group. The user’s LMS group is determined by being matched to either a security group ID or a team ID. Then the user’s LMS group is matched to the labor metric’s LMS group, if you are using user-based labor metric security. For example:
```
WHERE WORKBRAIN_USER.WBG_ID = LABMET_SEC_GRP.WBG_ID AND
WORKBRAIN_USER.WBU_ID = #page.property.userId#	 
```
This example compares the logged-in user’s security group ID to the group IDs that are assigned to the LMS group.
Additional criteria for the WHERE clause to ensure the current date and the timesheet date fall within the date ranges that are specified in the LMS group. For example:
```
AND (TO_TIMESTAMP(concat(substr('#request.WORK_DATE#', 1, 8),
substr('#request.WORK_DATE#', 10, 6))) BETWEEN LMSG_START_WRKS AND
LMSG_END_WRKS) AND (SYSDATE BETWEEN LMSG_START_SYSDATE AND	
LMSG_END_SYSDATE),	 
```
This example checks that the timesheet is being modified between the WRKS date range and the current calendar date falls within the LMS group’s start and end date.
Optionally, you can include an ORDER BY clause at the end of the query to order the final list that is returned.
The SELECT statements that are used to configure lookups to use labor metric security with localization are similar. These statements require the tag ‘VL_’ before the references to the labor metric and the addition of ‘_LOC_’ on the second labor metric name. For example:
```
SELECT VL_TIME_CODE.TCODE_NAME, VL_TIME_CODE.TCODE_LOC_NAME Name FROM	
EMPLOYEE, LABMET_SEC_GRP, VL_TIME_CODE,	
```
In this example, the ‘VL_’ tag is used wherever the labor metric is referenced. The ‘_LOC’ tag is only used once.