search cancel

Stage Lookup Investment Process Not Displaying

book

Article ID: 243028

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

Within the Investment stage_code field, the lookup that is used will fail to display the values within the Static Dependent lookup.

All that show is the 'Top' level. There are no lookups to display.

The lookup does have values and active.

Click Expand (+)

It should show the following:

 

Cause

Run query:
    
SELECT * from cmn_lookups
where lookup_code LIKE '%Init%'

The record does not exist in the table: cmn_lookup_flat_hiers

select * from cmn_lookups_v l,
cmn_lookup_types_v t,
cmn_lookup_flat_hiers p
WHERE l.lookup_type = t.lookup_type
AND l.lookup_code LIKE '%Init%'
AND l.language_code = 'en'
AND t.language_code = 'en'
AND p.parent_lookup_id = 5067981
AND p.lookup_id != 5067981
--AND p.lookup_id = l.id


--5070119 init2

SELECT * from cmn_lookup_flat_hiers p
WHERE lookup_id IN (5067502 , 5070119)


In this example, the hierarchy is missing 2 rows and only referencing itself.

 

Environment

Release : 16.0.1

 

Resolution

The resolution is to add the missing records manually for each value that is not showing, e.g.

1. Make a backup of table: CMN_LOOKUP_FLAT_HIERS

2. Resolve the values not populating 

INSERT INTO CMN_LOOKUP_FLAT_HIERS(ID,
                          PARENT_LOOKUP_ID,
                          LOOKUP_ID,
                          LOOKUP_LEVEL,
                          CREATED_DATE,
                          CREATED_BY,
                          LAST_UPDATED_DATE,
                          LAST_UPDATED_BY
                         )  VALUES (nextval('cmn_lookup_flat_hiers_s1'),<PARENT_LOOKUP_ID>, <missing LOOKUP_ID>, clock_timestamp(),1, clock_timestamp(),1);

 

3. Resolve the values not populating within the properties

Attachments