Hierarchical Display Tab Query Example

A basic SQL query used in the Hierarchical Display tab consists of the following:

Select <PrimaryIDColumn> id,
<ParentIDColumn> parentId,
<FolderDefinition> isFolder,
<BusinessKeyColumn> name,
<DisplayInfo_1>, <DisplayInfo_2>, ... , <DisplayInfo_n>
from <Table_1>, <Table_2>, ... , <Table_n>
where <Condition_1> and <Condition_2> and ... and <Condition_n> and NVL(<ParentIDColumn>,0) = :parentId

where

<PrimaryIDColumn>

Required. Column name whose data type is numeric. Identifies the object that is displayed in the hierarchical selection list, such as a resource pool (rpl.resource_pool_id) or skill (sk.skill_id).

<ParentIDColumn>

Required. Column name whose data type is numeric and is a parent. Identifies the parent of the selected object such as a parent resource pool (rpl.parent_resource_pool_id) or parent skill (sk.parent_skill_id).
For detailed steps on how to create skills with hierarchical selection, see Hierarchical Display Tab Query Example.

<FolderDefinition>

Required. A column name (such as a flag) or sub-query that determines if the object is a folder. The value or result must be either `Y' or `N.'

An example of a column name is: sk.is_skill_folder_flag

An example of a sub-query is: decode(sign((select count(rpl2.resource_pool_id) from RSC_RESOURCE_POOLS rpl2 where rpl2.enabled_flag='Y' and rpl2.parent_resource_pool_id= rpl.resource_pool_id)),1,'Y','N')

<BusinessKeyColumn>

Required. The user-defined name of the object that is displayed in the hierarchical selection list.

<DisplayInfo_1>, ... , <DisplayInfo_n>

Optional. Additional information about the object to be displayed in the hierarchical selection list.

<Table_1>, ... , <Table_n>

Required. The name of the table from which to query. For example, RSC_RESOURCE_POOLS or RSC_SKILLS.

<Condition_1> and ... and <Condition_n>

Conditions to be met in order for the object to be selected to display in the hierarchical selection list.

At a minimum, you must include the following condition: NVL(<ParentIDColumn>,0) = :parentId

The following is an example of a hierarchical display query that could be used for a resource pool validation:

select rpl.resource_pool_id id,

rpl.parent_resource_pool_id parentId,

decode(sign((select count(rpl2.resource_pool_id) from
RSC_RESOURCE_POOLS rpl2 where rpl2.enabled_flag='Y' and rpl2.parent_resource_pool_id=rpl.resource_pool_id)),1,'Y','N') isFolder,

rpl.resource_pool_name name,

krsc_utils.get_pool_managers_list(rpl.resource_pool_id), rpl.description

from RSC_RESOURCE_POOLS rpl

where rpl.enabled_flag='Y' and
NVL(rpl.parent_resource_pool_id, 0) = :parentId

Create Skills with Hierarchical Selection

To create skills with hierarchical selection:

  1. Open createSkills Web service to create a parent skill and set folder to true.

  2. Create some sub-skills and set parent name to the name of the parent skill you created.

    Note: When a skill is created by the createSkills web service, whatever it is defined a folder or not, you cannot change it by the upddateSkills web service.

Currently, creating skills with hierarchical selection from the standard interface is not supported. To create those skills, you must use the Web service. For more information about Web Services, see Web Services Programmer Guide.