Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Wanted: Tool to update KNTA_LOOKUPS values to re-order SEQUENCE to display LEXICOGRAPHICALLY

Highlighted
EricLEdberg
Super Collector

Wanted: Tool to update KNTA_LOOKUPS values to re-order SEQUENCE to display LEXICOGRAPHICALLY

There are numerous validations that store values in the KNTA_LOOKUPS table such as the COMPANY (CRT - Company).  Values associated to this LOOKUP_TYPE are entered with sequence numbers that are not "lexicographically" sorted.

 

This means when you list companies in the Resource form they display sorted by their sequence number not lexicographically...  How irritating.

 

How does one correct this issue?

 

I will be populating the KNTA_LOOKUPS values automatically in the future based on corporate LDAP company values and need to dynamically update / reorder the sequence in an audit.

 

  • Does anybody have a script that will update the sequence values for a lookup_type so the values are sorted lexicographically?

  • Since the Resource edit page is a built-in page I suppose I can't (or should not) the default validation which I think is:  CRt - Company - All...

Comments?

 

Eric Edberg

 

ps: I will need to sort other fields too.  Somebody has to have to tool to resolve this issue...

2 REPLIES
Jim Esler
Honored Contributor

Re: Wanted: Tool to update KNTA_LOOKUPS values to re-order SEQUENCE to display LEXICOGRAPHICALLY

Check out the inquiry titled "Sorting validation rows" (http://h30499.www3.hp.com/t5/Project-Portfolio-Management/Sorting-validation-rows/td-p/4834429). That approach works well.

EricLEdberg
Super Collector

Re: Wanted: Tool to update KNTA_LOOKUPS values to re-order SEQUENCE to display LEXICOGRAPHICALLY

 

I cross-posted this artice in the article mentioned above...

 

One of our local HP PPM contractors, Francois Schneider,  provided this procedure example which is supposed to reorder the sequence of the rsc_location table. 

  • I have not tested this code so you may want to TEST it first and modify it to meet your needs

Eric Edberg

 

PROCEDURE refresh_rsc_location(p_lookup_type IN VARCHAR2) IS
  l_count      NUMBER;
  TYPE listCodes IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  l_list listCodes;
BEGIN
  -- Reorder the SEQ field
   SELECT lookup_code BULK COLLECT INTO l_list
     FROM knta_lookups_nls
    WHERE lookup_type = p_lookup_type
ORDER BY meaning;
  IF l_list.FIRST IS NOT NULL THEN
    l_count       := 0;
    FOR i         IN l_list.FIRST..l_list.LAST
    LOOP
      l_count := l_count + 1;
       UPDATE knta_lookups_nls
      SET last_update_date   = SYSDATE,
        seq                  = l_count
        WHERE lookup_type = p_lookup_type and lookup_code = l_list(i);
    END LOOP;
    COMMIT;
  END IF;
END refresh_table_component;

This is called with the code:
refresh_rsc_location('RSC – Location');

 

//Add this to "OnDomLoad" event