NRO Decomissioning - generate temporary NR number and normal NR number

[ozamani9gh]

generate temporary NR number and normal NR number on Name Request/NAMEX API, instead of Legacy application creating it.

[eve-git]

Here is the basic logic in oracle to generate a new NR_NUM:

nro_util_pkg.get_new_nr_num
/*
**
** FUNCTION get_new_nr_num
**
** Purpose: to generate new NR number
**
** Comments:
** The following steps are taken:
** 1) considered number is randomly generated; number must be from the
** 2) if number of attempts is reached, exception is thrown (used to avoid infinite loop)
** 3) generated number must be from the range (default range is 1 to 9999999, or NR_GEN_FLOOR to NR_GEN_CEILING
**    values in system_variable if set), otherwise it gets regenerated (back to step 1)
** 4) generated NR number must not be alteady in nr_num_lifespan table (checked/set via set_nr_num_lifespan), otherwise it gets regenerated (back to step 1)
** 6) generated NR number is matched against NR numbers in the database; if already present, it gets regenerated (back to step 1)
**
*/

[eve-git]

SQL script to insert nr_number_exclude table (get the nr_numbers from namesd.nr_num_exclude)

DO $$
DECLARE
    nr_number TEXT;
    nr_numbers TEXT[] := ARRAY[
        'NR 0566920',
        'NR 8831724',
        'NR 7631423',
        ...
        'NR 6519583'
    ];
BEGIN
    FOREACH nr_number IN ARRAY nr_numbers
    LOOP
        IF NOT EXISTS (
            SELECT 1
            FROM nr_number_exclude
            WHERE nr_num = nr_number
        ) AND NOT EXISTS (
            SELECT 1
            FROM requests
            WHERE nr_num = nr_number
        ) THEN
            INSERT INTO nr_number_exclude (nr_num) VALUES (nr_number);
        END IF;
    END LOOP;
END $$;

[eve-git]

deployed and tested in DEV:

1. request a new NR. nr_num = NR 9764478
2. the NR number is in nr_number_lifespan, which is expected:

   select * from nr_number_lifespan;
   --- "nr_num"    "nr_timestamp"
   --- "NR 9764478"    "2024-07-10 16:50:48.348888+00"

3. the nr_num is not in nr_number_exclude:

   select count(*) from nr_number_exclude where nr_num='NR 9764478';
   --- "count"
   --- 0

4. A new requests record with nr_num = 'NR 9764478' created:

   select id, nr_num, request_id from requests where nr_num='NR 9764478'
   --- "id"    "nr_num"    "request_id"
   --- 2268752 "NR 9764478"    1457965

[eve-git]

deploy:

1. create new tables:

   
   CREATE TABLE nr_number_exclude
   (
   nr_num character varying(10) NOT NULL
   );

CREATE TABLE nr_number_lifespan ( nr_num character varying(10) NOT NULL, nr_timestamp timestamp with time zone NULL );

2. execute the script in my script which in the comments above to insert data in nr_number_exclude

[eve-git]

deployed code, table and data to test environment. Ready to test.

Test: to ensure that the creation of NR functions as it did previously.

[Mihai-QuickSilverDev]

Was able to create and examine NR with no issues.