Configure Lookups

ccppmop1581
HID_admin_lookups
A lookup is a list of values that you want other users to see in drop-down and multi-select lists. Create and configure lookups in custom fields, reports, queries, and in list or chart portlet filters.
For example, a custom field named Document Status can contain a drop-down with lookup values such as
Draft
,
Review
, or
Approved
. Auto-suggest displays a list of possible matching values when the user enters the first few characters in a static list, dynamic query, or parameterized lookup data source.
2
Partitions and Lookups
Partitions help you create different views of a page for different users. Typically, the underlying structures of the item are object attributes and lookup values.
  • You cannot partition static dependent list or dynamic query lookup values.
  • You can create static list lookups that contain partitioned values.
For more information, see
Classic PPM
Studio Development
in the
Reference
section.
Lookup Types
You can create and use the following types of lookups:
  • Static Lists
    Use this type of lookup when you want a standard set of choices to appear as a drop-down list of values for a field.
  • Static Dependent Lists
    Use this type of lookup to create a hierarchy of lookups and values. Items that appear in the second and subsequent lists depend on a previously selected value in an initial field.
    Example:
    The user selects USA from a country list. A list of states appears from which the user can select an appropriate state. Another user selects Canada in the country list. A list of provinces appears in the second lookup list.
  • Dynamic Queries
    Use this type of lookup to fetch data from the database in real time to populate the drop-down. These lookups provide the most up-to-date values possible. Dynamic lookups limit the result of the query list. Their values are based on the values that are entered in other attributes in the view. Create dynamic NSQL queries to provide parameterized lookups. 
You can nest a static lookup inside a dynamic query lookup. You cannot nest a static dependent list lookup inside a dynamic query lookup. Auto-suggest does not work with static dependent lists.
The following table describes how each type of lookup can be edited or deleted, depending on who created it and how it is used:
Lookup Type
Edit
Delete
System-restricted lookups
You can only change the labels and layout. All stock dynamic query lookups are system-restricted. Other types of lookups can also be system-restricted.
No
System lookups
You can add and deactivate values. You cannot deactivate the default values provided with the application.
No
User-defined lookups
You can add and deactivate values. Use your own lookups to extend the stock lookups provided with the application.
Yes
Create Static List Lookups
Follow these steps:
  1. Click
    Administration
    ,
    Data Administration
    ,
    Lookups
    .
  2. Click
    New
    .
  3. Complete the following fields:
    • Lookup Name
      Defines the unique name for the lookup.
    • Lookup ID
      Defines the unique ID for the lookup. The value is typically entered in uppercase letters. The ID provides some information about the function of the lookup.
      Example:
      LOOKUP_ACTION_STATUS.
    • Content Source
      Specifies the data source for the lookup.
      Values:
      • Customer: Specifies that you are providing the data source.
      • FM Stock Reports.
      • CPIC Accelerator: Specifies that you are using the CPIC Accelerator add-in as your data source.
      • PMO Accelerator: Specifies that you are using the PMO Accelerator add-in as your data source.
      • Clarity
        Stock Reports.
      Contact your 
      Classic PPM
       representative for details about acquiring add-ins.
      Default:
      Customer
  4. Complete the following fields from the Source field:
    • Static List
      Select the Static List type of lookup when working with a standard set of choices. Also specify a sort order, including:
      • Alphanumeric. Displays items in alphanumeric order. You cannot reorder alphanumeric list lookups. Before you begin creating the values, decide the order.
      • Manual. Allows you to determine and change the sort order.
      Default:
      Alphanumeric
    • Static Dependent Lists
      Select the Static Dependent Lists type of lookup to create a hierarchy of lookups and values. Items that appear on the second and subsequent lists depend on your configuration. Specify one of the following sort orders:
      • Alphanumeric
        : Displays items in alphanumeric order. You cannot reorder alphanumeric list lookups. Before you begin creating the values, decide their order.
      • Manual
        : Allows you to determine and change the sort order.
      Default:
      Alphanumeric
    • Dynamic Query
      Select the Dynamic Query type of lookup to fetch data from the database in real time to display in the drop-down.
    • Description
      Specifies a brief description of the lookup in the Description field.
  5. Save the changes.
    The parent window appears for the new list lookup. Use the page to define the parent window association. For example, select lookup_code for strings and select lookup_enum for numbers.
Create Static List Lookup Values
You can create and edit the values that appear in lookups. You can create any number of values for a lookup. For example, you create a
Delivery Status 
lookup with 
High
,
Medium
, and
Low
values. 
Follow these steps:
  1. Open a static list lookup and click
    Values
    .
  2. Click
    New
    .
  3. Complete the following fields:
    • Lookup Value Name
      Defines the name for the lookup value.
    • ID (LOOKUP_CODE)
      Defines the ID for the lookup value. The ID could be a number or string depending on the configuration of the Hidden Key on the Parent Window page.
    • Partition
      Defines the partition for a lookup value. If partitioning is not enabled, System is the default value.
    • Partition Association Mode
      Defines the level for users to view the partition.
      Values:
      • Partition, ancestors, and descendants. Associates the lookup with the partition you selected in the previous step and its parent and child partitions.
      • Partition and ancestors. Associates the lookup with partition you selected in the previous step and its parents.
      • Partition and descendants. Associates the lookup with the partition you selected in the previous step and its children.
      • Partition only. Associates the lookup only with the partition, and not with its descendants and ancestors.
      Default:
      Partition, ancestors, and descendants
      For more information, see
      Classic PPM
      Studio Development
      .
    • Description
      Specifies additional information about the purpose of the lookup value. To avoid unexpected user results, specify a description for each lookup value.
    • Active
      Specifies if the value is visible on the page when displayed to the users. Clear the check box to hide the value from view.
      Default:
      Selected
  4. Save your changes.
Manually Reorder Static List Lookup Values
Use a manual sort order when you first create your static list lookup. You can then reorder the way that the values display in the drop-down.
Follow these steps:
  1. With the static list lookup open, click Reorder Values.
  2. Click the name of the value and use the arrows to move the value up and down in the list.
  3. Save the changes.
Static Dependent List Lookups
A static dependent list (SDL) lookup consists of a hierarchy of levels and values. To create the lookup, create the levels of the hierarchy, and then the values that can be available for selection at each level. Typically, each level offers multiple values. For example, a top-level Nation field offers several values in addition to USA, such as Argentina, Britain, Canada, Mexico, and Singapore.
Plan your hierarchy carefully. You cannot move the levels and values up or down in the structure. You can change properties for the levels and values you create. If necessary, you can delete and then re-create the values.
Create or Edit Static Dependent List Lookups
Use the create lookup page to create static dependent list lookups. The first lookup that you create appears on the levels page of the lookup. The first lookup serves as the top-level lookup in the hierarchy.
Follow these steps:
  1. Click 
    Administration
    Data Administration
    Lookups
    .
  2. Click
    New
    to create a new lookup, or click the lookup name to edit that lookup.
  3. For the
    Source
    , select
    Static Dependent Lists
    and complete the following fields:
    • Lookup Name
      Defines the unique name for the lookup.
    • Lookup ID
      Defines the unique ID for the lookup. The value is typically entered in uppercase letters. The ID provides some information about the function of the lookup.
      Example:
      LOOKUP_ACTION_STATUS.
    • Content Source
      Specifies the data source for the lookup.
      Values:
      • Customer: Specifies that you are providing the data source.
      • FM Stock Reports.
      • CPIC Accelerator: Specifies that you are using the CPIC Accelerator add-in as your data source.
      • PMO Accelerator: Specifies that you are using the PMO Accelerator add-in as your data source.
      • Clarity
        Stock Reports.
      Contact your 
      Classic PPM
       representative for details about acquiring add-ins.
      Default:
      Customer
    • Description
      Specifies a brief description of the lookup.
  4. Save the changes.
Create Dependency Levels for Static Dependent List Lookups
A static dependent list lookup consists of a hierarchy of levels and values. To create this type of lookup, you create the levels of the hierarchy. Then, create the values that can be available for selection at each level. Typically, each level offers multiple values. For example, a top level “Nation” field offers several values in addition to USA, such as Argentina, Britain, Canada, Mexico, and Singapore.
Follow these steps:
  1. Open the static dependent list lookup.
  2. Click
    Levels
    .
  3. Click
    New
    .
  4. Complete the following fields:
    • Lookup Name
      Defines the name for the lookup level.
    • Lookup ID
      Defines the unique ID for the lookup level.
    • Description
      Defines a brief description of the lookup level.
  5. Save the changes.
Create Lookup Values for Static Dependent Lists
Use this procedure to create the values you want resources to select from at each level. Do this procedure after you have created the dependency levels for the static dependent list lookup. The first-level values with plus signs (+) in front of them indicate that more levels follow. Click the plus sign (+) in front of an item to expand the hierarchy.
Follow these steps:
  1. Open a static dependent list lookup and click
    Values
    .
  2. Click
    New
    .
  3. Complete the following:
    • Lookup Value Name
      Defines the name for the lookup value.
    • Lookup Value ID
      Defines the unique ID for the lookup value.
    • Description
      Defines a brief description of the lookup value.
    • Active
      Specifies if the value is visible on the page when displayed to the users. Clear the check box to hide the value from view.
      Default:
      Selected
  4. Save the changes.
Dynamic Query Lookups
A dynamic query lookup contains a list of items that are displayed in a drop-down. Use
Classic PPM
 version of SQL, named NSQL, to create queries for dynamic lookups. In a dynamic query lookup, the drop-down, layout, and behavior are based on the content of the NSQL query you create.
Note:
You cannot modify the NSQL query for a dynamic query lookup if the lookup is associated with an object attribute.
Video: How to Create a Dynamic Lookup
The following third-party video is provided by Rego Consulting. This video is provided by CA Technologies “AS IS” and without warranty.

To play this video in full screen, click the YouTube logo to the right of Settings at the bottom of the video. 
Create or Edit Dynamic Query Lookups
Follow these steps:
  1. Click 
    Administration
    Data Administration
    Lookups
    .
  2. Click
    New
    to create a lookup, or click the lookup name to edit that lookup.
  3. Complete the following fields:
    • Lookup Name
      Defines the unique name for the lookup.
    • Lookup ID
      Defines the unique ID for the lookup. The value is typically entered in uppercase letters. The ID provides some information about the function of the lookup.
      Example:
      LOOKUP_ACTION_STATUS.
    • Content Source
      Specifies the data source for the lookup.
      Values:
      • Customer: Specifies that you are providing the data source.
      • FM Stock Reports
      • CPIC Accelerator: Specifies that you are using the CPIC Accelerator add-in as your data source.
      • PMO Accelerator: Specifies that you are using the PMO Accelerator add-in as your data source.
      • Clarity
        Stock Reports
      Contact your 
      Classic PPM
       representative for details about acquiring add-ins.
      Default:
      Customer
  4. Select
    Dynamic Query
    for the
    Source
    and complete the following fields:
    • Object
      If you want the hidden key value to return the primary key value of an object, select the object.
    • Description
      Enter a brief description of the lookup.
  5. Save the changes.
Create Dynamic Lookups and Export Configurations
If the purpose of a dynamic lookup is to link an object to a unique instance of another object or record, we recommend the following approach:
  • Use the internal ID that the
    Classic PPM
     environment generates as the hidden key.
  • Add the user-defined ID/unique code as the column UNIQUE_CODE in the query select clause.
Include the NSQL query column UNIQUE_CODE in a query to prevent the XOG from exporting the lookup attribute using the literal hidden key value. Instead of the hidden key value, the XOG uses the value of UNIQUE_CODE. Using this value avoids exporting primary key ID values in the XML that are impossible to import into another
Classic PPM
 environment.
For example, the following lookup query selects the SRM_RESOURCES.ID as its hidden key.
SELECT  @SELECT:r.id:id@ ,@SELECT:r.full_name:full_name@ ,@SELECT:r.unique_name:unique_name@ ,@SELECT:r.last_name:last_name@ ,@SELECT:r.first_name:first_name@ FROM srm_resources r WHERE 1=1 AND @FILTER@
When the preceding lookup is used, the hidden key is selected and stored as the lookup value. When this lookup query is exported using the XOG, the attribute that is associated with the lookup contains the internal id of the resource. When importing this data into a different
Classic PPM
 environment, the effort can fail. The exported record can even become associated with the wrong target record because the internal id in the target system differs from the source system.
By including the UNIQUE_CODE column in the select clause, the XOG export substitutes the unique user-defined id (SRM_RESOURCES.unique_name) into the attribute in the XOG output.
SELECT  @SELECT:r.id:id@ ,@SELECT:r.full_name:full_name@ ,@SELECT:r.unique_name:unique_name@ ,@SELECT:r.unique_name:unique_code@ ,@SELECT:r.last_name:last_name@ ,@SELECT:r.first_name:first_name@ FROM srm_resources r WHERE 1=1 AND @FILTER@
When the data is imported, the
Classic PPM
 architecture uses the UNIQUE_CODE value to query the system and store the SRM_RESOURCES.ID for the lookup value.
Include Dynamic Query Lookup Attributes in the Data Warehouse
To include an attribute that uses a dynamic query lookup in the Data Warehouse, complete the following steps:
  1. Create the dynamic query lookup definition with specific coding rules described later.
  2. Create an attribute of the data type "lookup" and enable it for the Data Warehouse. See 
    Configure the Data Warehouse 
    for more information.
  3. Run the Load Data Warehouse job with the Full Load option selected. 
Rules for Creating Dynamic Query Lookups for the Data Warehouse
Use the following rules to define your dynamic query lookup accurately and avoid error messages when enabling the attribute for the Data Warehouse:
  • Always include a select column named LAST_UPDATED_DATE
  • Set the alias or column field name to LAST_UPDATED_DATE
  • Verify that LAST_UPDATED_DATE is a date field that is populated with a valid date from any table included in the dynamic query
Example
: Use the following select statement to query the PRTIMEPERIOD table if the LAST_UPDATED_DATE field has a table column name PRMODTIME:
@SELECT:tp.PRMODTIME:LAST_UPDATED_DATE@
Rules for Including Language Translations in the Data Warehouse
Not all query tables have language translations associated with their data. Hence, adding language translations is optional for using a query lookup with the Data Warehouse. Use the following rules to include translations for query lookups in the Data Warehouse:
  • Always include a select column named LANGUAGE_CODE
  • Always include a select column named LANGUAGE_ID
  • Always include the following statement in the WHERE clause of the Data Warehouse to recognize the language translations available in the query lookup:
@WHERE:PARAM:LANGUAGE@
Example
: The following dynamic query lookup applies the correct rules for defining the last updated date and the language translations:
SELECT @SELECT:a.name:country_name@, @SELECT:c.currency_code:currency_code@, @SELECT:c.currency_code || ' (' || c.currency_symbol || ')':currency_name@, @SELECT:c.LAST_UPDATED_DATE:LAST_UPDATED_DATE@, @SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@, @SELECT:LANG.ID:LANGUAGE_ID@ FROM cmn_countries b, cmn_captions_nls a, cmn_currencies c, CMN_LANGUAGES LANG WHERE a.table_name = 'CMN_COUNTRIES' AND @FILTER@ AND a.pk_id = b.id AND a.language_code = @WHERE:PARAM:LANGUAGE@ AND b.currency_id = c.id AND LANG.LANGUAGE_CODE=a.LANGUAGE_CODE
Define Queries
Use the Query tab of a lookup to define lookup queries.
Classic PPM
 only supports a special version of SQL statements named NSQL.
We recommend that you always include the phrase “@FILTER@” in the WHERE clause of the query as shown in the following example.
Example NSQL Statement
SELECT  @SELECT:c.currency_code:currency_code@, @SELECT:c.currency_code:currency_name@ FROM cmn_currencies c WHERE c.is_active = 1 AND @FILTER@
For more information, see
Classic PPM
Studio Development
.
Create Query Statements
Use the following procedure to compose a query to extract the values from the lookup.
Follow these steps:
  1. If the Query page for a lookup does not display, select the desired dynamic query lookup, and click Query.
  2. In the Niku Query field, enter NSQL statements.
  3. Save the changes.
BROWSE-ONLY Construct for Dynamic Query Lookups
For dynamic query lookups, browse works in a specific way. When you display a record 
Classic PPM
, the value does not appear in the lookup field. In this case, no match exists between the value that is stored on the record and the list value in the lookup. The previous values are retained within the database table for the instance record. If a record with the previous value is retrieved and not seen in the product, click Save to remove the previous value from the record.
To retain inactivated values in the record and see them in the application, change the lookup query definition. For this case, include the specific NSQL construct @BROWSE-ONLY with the definition. When a user uses the lookup on new records, active values appear. Moreover, when an existing record is shown with inactive values, the inactive value remains on the record.
This construct has the following syntax:
@BROWSE-ONLY: /* include SQL statement here to look at active results only */ :BROWSE-ONLY@
The following example shows the BROWSE-ONLY construct with a value that defines the following results:
SELECT... FROM... WHERE @FILTER@ @BROWSE-ONLY: AND IS_ACTIVE = 1 :BROWSE-ONLY@
Define Browse Window Characteristics for Dynamic Query Lookups
The fields on the browse window page of a lookup, display based on your query the selections on the parent window page. You can change most of these fields in a user-created dynamic query lookup.
Do not include an ORDER BY clause on a dynamic query that is used in a lookup. The Browse Window tab for the dynamic query already includes a Default Sorting Column field. The query automatically appends your selected sort field as an ORDER BY clause.
Follow these steps:
  1. Open a dynamic query lookup and click
    Browse Window
    .
  2. In the
    Filter Layout
    section, select the fields to display in the
    Available Filter Fields
    list. 
  3. Click the right arrow to move them to the
    Selected Filter Fields
    list.
  4. In the
    Selected Filter Fields
    list, select fields, and use the up and down arrows to set the field order.
  5. In the List Layout section, use the arrow keys to select and arrange the list columns for displaying in the lookup. Move the columns from the Available Columns list to the Selected Columns list.
  6. Click Apply to update and prepare the next two fields.
  7. If you use more than one column, select a default sorting column from the Default Sorting Column field. 
  8. Select either Ascending (default) or Descending in the Default Sort Order field.
  9. Click
    Preview
    to preview the browse window.
  10. Perform one of the following substeps:
    • If the lookup does not contain pull-down values, click
      Save and Exit
      .
    • If the lookup contains pull-down values, click
      Save
      . Click
      Lookup Filters
      on the top menu and associate the filter with the appropriate lookup.
Define Lookup Filters for Dynamic Query Lookups
If you specified a pull-down list for the Field Element Type field, use the following procedure to associate it with a lookup.
Follow these steps:
  1. With the dynamic query open, click
    Lookup Filters
    .
  2. For each field, click the Browse icon in the Lookup column to select and associate the lookup with that filter.
  3. Click
    Exit
    .
Set Filter Options for Browse Lookups
You can set filtering options for a dynamic query lookup. Display all results automatically either when you select the lookup, or enable the option to allow filtering before displaying the results. If you enable the option to filter before displaying results, click Filter for the lookup to display any records. The task helps reduce the number of records that are displayed and improve performance.
Follow these steps:
  1. With the lookup open, click
    Browse Window
    .
  2. In the Filter Options section, select one of the following:
    • Automatically show results
      Displays all results for the lookup immediately.
    • Do not show results until I filter
      Only displays results for the lookup after the user specifies the filter options.
  3. Save the changes.
Create Parameterized Lookups
Parameterized lookups are based on dynamic query lookups. Parameterized lookups provide the ability to control the results that display in a dynamic query, which is based on other values that are entered in a view. For example, suppose a filter section that has two attributes that are named Country and State. You can filter the query results in a way to display only States, when a country is a lookup value.
For a parameterized lookup, you cannot use a multi-value lookup as the source that determines the display in the secondary target lookup. Use a single-value lookup for the source.
For more information, see
Classic PPM
Studio Development
.
Use the following process to create parameterized lookups:
  1. Create a dynamic query lookup.
  2. Create an NSQL query with lookup parameters in the Where clause that references attributes in the object.
  3. Create a parameterized lookup attribute on the object and map the NSQL query lookup parameters to the appropriate object attributes.
Define Parent Window Characteristics for Lookups
Use the Parent Window page of a static list or dynamic query lookup to define parent window characteristics for dynamic query lookups.
Follow these steps:
  1. With a dynamic query lookup open, click
    Parent Window
    .
  2. Complete the requested information:
    • Internal Name
      Displays the internal name value.
    • Hidden Key
      Displays the hidden key value.
    • Display Attribute
      Specifies a display attribute value.
  3. Save the changes.
Configure Auto-Suggest Settings for Lookups
Auto suggest displays a list of values that match the characters that a user enters in lookup text fields. By default, auto suggest is enabled for all system lookups and any new lookups that you create.
The following lookup sources work with auto suggest:
  • Static List
  • Dynamic Query
  • Parameterized
Auto suggest does not work with Static Dependent List lookup sources.
You can perform the following actions to configure auto suggest:
  • Enable or disable auto suggest
  • Specify the search values for the lookup
  • Specify the values that display in the suggestion list for the lookup
  • Indicate the number of items to display in the suggestion list
Follow these steps:
  1. With the lookup open, click
    Auto Suggest
    .
  2. Complete the following fields, and save:
    • Auto Suggest Enabled
      Indicates if auto suggest is enabled.
      Default:
      Selected
    • Number of displayed suggestions
      Defines the number of auto suggest results to display.
      Default:
      20
    • Attribute Search Keys
      Specifies the search keys that are based on attributes (such as, Name) to use when the user enters text to search for a lookup value.
      Example:
      Specify Name as a search key. When you enter Bo, only the values whose last name begins with Bo display in the suggestion list.
      Select the attribute from the Search Keys Available list and add it to the Search Keys Selected list. If you add multiple keys to the list, auto suggest uses an OR expression to filter the results.
      Example:
      If Name and Description are added as search keys, auto suggest displays results that match the name OR description. The order from top to bottom in the Search Keys Selected list defines the auto suggest precedence for the search keys of a lookup.
      Default:
      Name
      The only available search key or suggestion list values for static lists are Name and Description. For dynamic queries, you can add more values by adding the values to the query. Once the values are added to the query, they appear in the available list for the search key or suggestion list.
    • Attribute Suggestion List Display
      Specifies the keys that are based on attributes that display in the suggestion list.
      Example:
      Specify Name as the Attribute Search Key for a resource lookup. But you can display the Name and Description in the suggestion list.
      Default:
      Name
  3. Select the attribute from the Attributes Available list. Add it to the Attributes Selected list. If you specify multiple values in the field, the search values display in the suggestion list that is separated by the pipe (|) character (for example, Bob Smith | Employee).
Create Dynamic Lookups Directly Against the Data WareHouse
You can create lookup queries against the data warehouse instead of the application database. 
Classic PPM
 includes a default datasource for creating lookups against the data warehouse. 
Data warehouse data source.
The following conditions apply:
  • As a best practice, use these lookups only for creating report parameters. Data warehouse lookups are not available for attribute creation using Studio portlets or objects. This option does not support portlet queries. 
  • This option is only available for creating dynamic lookup queries for Jaspersoft reports. Static lookups are not supported.
  • Clarity
    DB Link is not supported for creating dynamic lookup queries using the
    Data Warehouse
    source. 
  • Lookups created using the
    Data Warehouse
    source are not available for selection while creating an attribute of type
    lookup
    .
The default security clause (
@security@)
is not supported with the default data warehouse data source. To implement your own security, create your query with all the required
WHERE
clauses.
Lookup NSQL query examples:
4
4
Language Code Parameter (PARAM:LANGUAGE):
SELECT DISTINCT @SELECT:lkp.investment_status_key:investment_status_key@, @SELECT:lkp.investment_status:investment_status@, @SELECT:lkp.sort_order:sort_order@ FROM dwh_lkp_investment_status lkp INNER JOIN dwh_inv_investment i ON i.investment_status_key = lkp.investment_status_key WHERE lkp.is_active = 1 AND i.investment_type_key = 'project' AND lkp.language_code = @WHERE:PARAM:LANGUAGE@ AND @FILTER@
USER ID Parameter (PARAM:USER_ID):
SELECT DISTINCT @SELECT:r.resource_key:resource_key@,
@SELECT:r.resource_id:resource_id@,
@SELECT:r.resource_name:resource_name@
FROM dwh_res_resource r
INNER JOIN dwh_res_resource rm ON r.resource_key = rm.resource_manager_key
WHERE r.is_role = 0
AND r.is_active = 1
AND EXISTS (SELECT 1
FROM dwh_res_security_v
WHERE user_key = @WHERE:PARAM:USER_ID@
AND resource_key = r.resource_key)
AND @FILTER@
USER_DEF Parameter (PARAM:USER_DEF):
SELECT @SELECT:PHASE.TASK_ID:PHASE_INT_ID@, @SELECT:PHASE.TASK_NAME:PHASE_NAME@, @SELECT:PHASE.WBS_SEQUENCE:PHASE_WBS_SEQUENCE@ FROM DWH_INV_TASK PHASE WHERE PHASE.WBS_LEVEL = 1 AND (@WHERE:PARAM:USER_DEF:INTEGER:PROJECT_ID_CONSTRAIN@ IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:PROJECT_ID_CONSTRAIN@ = PHASE.INVESTMENT_KEY) AND @FILTER@
NVL Function (ISNULL/NVL):
The following example query can be used to check for a null value:
SELECT @SELECT:'T'+ team.TEAM_KEY:UNIQUE_KEY@, @SELECT:@NVL@(team.REQUIREMENT_NAME,1):NAME@, @SELECT:team.DW_UPDATED_DATE:last_updated_date@ FROM dwh_inv_team team WHERE 1=1 AND @FILTER@
Rules for Changing Lookups
The following table summarizes the rules that apply when changing lookups:
Lookup Type
Rule
System-restricted
You can only change labels.
User-defined
You can change any field.
System lookup
You can add values.
You cannot deactivate system lookups.
You can activate and deactivate the values that you add to the lookup.
Static List Lookups
The following table summarizes the rules that apply when changing static list lookups:
Function
System-restricted
System
User-defined
Change Lookup Name and Description
Yes
Yes
Yes
Change Sort Order
No
Yes
Yes
Change Content Source
No
No
Yes
Deactivate or Activate Lookup
No
No
Yes
Delete Lookup
No
No
Yes
Change Lookup Value Name and Description
Yes
Yes
Yes
Change Parent Window Display Attribute
No
No
Yes
Change Partition and Partition Associations
No
No
Yes
Deactivate and Activate Lookup Values
No
No
Yes
Reorder Manual Lookup Values
Yes
Yes
Yes
Change Auto Suggest Settings
Yes
Yes
Yes
Static Dependent List Lookups
The following table summarizes the rules that apply when changing static dependent list lookups:
Function
System-restricted
System
User-defined
Change Lookup Name and Description
Yes
Yes
Yes
Change Sort Order
No
Yes
Yes
Change Content Source
No
No
Yes
Deactivate or Activate Lookup
No
No
Yes
Delete Lookup
No
No
Yes
Change Lookup Value Name and Description
Yes
Yes
Yes
Change Parent Window Display Attribute
Not Applicable
Not Applicable
Not Applicable
Deactivate and Activate Lookup Values
No
Yes
Yes
Reorder Manual Lookup Values
Yes
Yes
Yes
Dynamic Niku Query Lookups
The following table summarizes the rules that apply when changing dynamic Niku query lookups:
Function
System-restricted
System
User-defined
Change Lookup Name and Description
Yes
Not Applicable
Yes
Edit Query
No
Not Applicable
Yes
Change Parent Window Display Attribute
No
Not Applicable
Yes
Change Browse Window Name and Label fields
Yes
Not Applicable
Yes
Change Browse Window Field Element Type
No
Not Applicable
Yes
Change Browse Window Selected Filter and List fields
No
Not Applicable
Yes
Change Browse Window Filter field, List Column Order
Yes
Not Applicable
Yes
Change Browse Window Default Sort Column/Order
Yes
Not Applicable
Yes
Change Auto Suggest Settings
Yes
Yes
Yes
Resource Lookups for Action Items
Classic PPM
 provides the following stock lookups for use with the Action Item Assignees functionality:
  • Resource Browse (SCH_BROWSE_RESOURCE)
  • All Resources Browse (LOOKUP_RESOURCES_ALL)
The lookup definitions contain the following general properties:
  • Object = Resource and the Parent Window
  • Hidden Key = ID value from SRM_RESOURCES table
The
Classic PPM
 process Action Item Assignees functionality expects the internal record ID for a Resource record, which is stored on the SRM_RESOURCES table. The functionality does not expect a user record ID. You can create a custom attribute that uses a stock
Resource Browse
lookup type, or you can create your own dynamic query lookup definition.
The most commonly used system-supplied lookup that meets this criteria is the Lookup ID = SCH_BROWSE_RESOURCE. Using this lookup, you can create your own dynamic lookup that returns the SRM_RESOURCE.ID value as the hidden key value in the lookup.
Do not confuse the Resource Browse lookups with similarly named resource or user lookups.
Activate and Deactivate Lookups
Lookups are activated by default when you create them. A lookup must be active before you can use it in reports, or other objects. You can deactivate the lookups that you created, but cannot deactivate system or system-restricted lookups.
Follow these steps:
  1. Open the lookup.
  2. Complete the following field, and save:
    • Active
      Specifies if the value is visible on the page when displayed to the users. Clear the check box to hide the value from view.
      Default:
      Selected
Activate and Deactivate Lookup Values
Lookup values are activated by default when you create them. A lookup value must be active to appear in a lookup. You can activate or deactivate user-defined static list and static dependent list values. But you cannot deactivate dynamic query values.
Follow these steps:
  1. Open the lookup that contains the values to activate or deactivate.
  2. Click
    Values
    .
  3. Select the check box next to the values to activate or deactivate.
  4. Click
    Activate
    or
    Deactivate
    .
    A check mark appears in the Active column of the lookups page for the active values.
Apply Filters to Find Lookups
Use the lookup filter to find a specific lookup or family of lookups.
Follow these steps:
  1. Click 
    Administration
    Data Administration
    Lookups
    .
  2. Use any of the following parameters to define search criteria for lookups:
    • Lookup Name
      Specifies all or part of the lookup name. For example, you can enter 
      Active Resources
      or
      Active*
      .
    • Lookup ID
      Specifies all or part of the Lookup ID.
    • Description
      Specifies all or part of the lookup description.
    • Source
      Specifies the lookup source.
      Values:
      Static List, Static Dependent List, Dynamic Query, or All.
    • System Type
      Specifies the lookup type.
      Values:
      System, System-restricted, User-defined, or All.
    • Status
      Indicates if you want to search for active or inactive lookups.
    • Object
      Indicates whether the search is by object (for Dynamic queries only) if you selected an object while creating the lookup.
  3. Click
    Filter
    .
Filters support your use of wildcard characters.
Limit the OBS Types That Appear in OBS Browse Lists
Use the Hide Financial OBS option to filter and limit the number of organizational breakdown structure (OBS) types that appear in the OBS browse lists. The option is useful for businesses with many OBS entries in the browse lookup lists, owing to many entities. Applying the option removes the OBS listings that are financial (Department or Location types) from the OBS browse lists.
The option is applied in addition to any existing customization for a browse. You can see different results than you expect for some customized browses. A List Filter view of a custom object, by default, contains the OBS Unit attribute. The enhancement affects a browse on this attribute.
Hide Financial OBS Option
The option to filter the OBS browse lists affect the following lookup IDs:
  • OBS_BROWSE_FLT_ALL
  • OBS_BROWSE_FLT_BPM_DEF_PROCESSES
  • OBS_BROWSE_FLT_CMP
  • OBS_BROWSE_FLT_CMN_PAGES
  • OBS_BROWSE_FLT_CMN_PORTLETS
  • OBS_BROWSE_FLT_CMN_SCH_JOB_DEFINITIONS
  • OBS_BROWSE_DMART_FLT_RES
  • OBS_BROWSE_DMART_FLT_PRJ
  • OBS_BROWSE_FLT_INV_APPLICATION
  • OBS_BROWSE_FLT_INV_ASSET
  • OBS_BROWSE_FLT_INV_OTHER
  • OBS_BROWSE_FLT_INV_PRODUCT
  • OBS_BROWSE_FLT_PRJ
  • OBS_BROWSE_FLT_RES
The option affects filtering of the following areas:
  • Reports
    Reports using OBS Unit as a filter do not provide the Financial OBS drill-down within the Show OBS drop-down list. To run a report against an investment that is assigned to a financial OBS, use different filter options. For example, Project Manager, or leave the OBS filter option empty.
  • Resource List
    The Resource filter, within the resource list or within Project Team, does not provide the Financial OBS drill-down within the Show OBS drop-down list. To search for resources to assign to a financial OBS and cannot display, use different filter options. For example, part of the resource name. In most cases, resources are assigned to an enterprise OBS and the impact is minimal.
  • Investment Filter
    Investments that are associated with a financial OBS cannot be filtered using the Show OBS drop-down list. Use other filter options to reduce the list.
  • OBS Access Rights
    Associate access rights through the OBS Unit for nonfinancial OBS types. Access rights for financial OBS types on portlets, objects, processes, or other items must be handled through a separate security OBS. You can also temporarily turn off the Hide Financial OBS option.
Set the Hide Financial OBS Option
Follow these steps:
  1. Click
    Administration
    ,
    Finance
    ,
    Processing
    .
  2. Select the
    Hide Financial OBS
    check box in the
    OBS Filter
    section.
  3. Click
    Save
    .