Re-order KNTA_LOOKUPS table enteries by 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.

Here is this a procedure that sort given lookup_type by lexicographically and update KNTA_LOOKUPS table.
Please note that, I have not tested this code so you may want to TEST it first and modify it to meet your needs

Sample usage;   refresh_rsc_location(‘RSC – Location’);


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;

Leave a Reply

Skip to toolbar