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...
ps: I will need to sort other fields too. Somebody has to have to tool to resolve this issue...
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
PROCEDURE refresh_rsc_location(p_lookup_type IN VARCHAR2) IS
TYPE listCodes IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
-- Reorder the SEQ field
SELECT lookup_code BULK COLLECT INTO l_list
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
l_count := l_count + 1;
SET last_update_date = SYSDATE,
seq = l_count
WHERE lookup_type = p_lookup_type and lookup_code = l_list(i);
This is called with the code:
refresh_rsc_location('RSC – Location');