Анатомия системы НСИ

Данная статья основана на реальных событиях,
и все проблемы в ней не вымышленные. (С)

В начале хотелось бы отметить, что статья не призвана показать изобретение велосипеда, потому как многие приёмы уже давно существуют в культуре разработки баз данных. Однако обобщить, проанализировать проблемы, которые они могут решить и показать, как с ними можно работать. А проблем хватает несмотря на то, что нормативно-справочная информация (НСИ) не относится к бизнес-логике, а скорее находится в обслуживании у неё. Стандартный процесс по рисованию очередной таблички для хранения справочника очень скоро начинает обрастать костылями или трудоёмкими переделками.

Вот и в моём случае оказалась та же картина — система стоит на продуктиве более десяти лет, строилась по тому же принципу, если что нужно, рисуем и включаем в оборот. Таким образом были созданы несколько таблиц для хранения разного рода оборудования. Но вот пришёл час Х, когда стало необходимо добавить ещё пару таблиц для нового оборудования и при этом все (включая старые) должны входить в определённую группу. Это значит, что ссылки на разные таблицы должны быть включены в кросс-таблицу между группой и всеми пятью видами оборудования, то есть для каждого своё поля с констреинтом на соответствующую таблицу. А если ещё одно добавится, менять структуру. И обработку нужно делать в зависимости от того, какие поля заполнены. Вот и возникает первая проблема, как разные таблицы обобщить, что бы с ними одинаково можно было работать и не менять структуру, если добавляется ещё одна. Замечательная мысль, создаём отдельную табличку, которая призвана хранить абстрактное понятие оборудование с указанием типа, а тогда остальные таблички ссылаются по внешнему ключу на своего родителя. На этой радостной волне мы заливаем в созданную табличку записи из одной и пытаемся тоже сделать для другой. Но что-то пошло не так, сработало ограничение первичного ключа, к чему бы это? А к тому, что на заре бурной молодости системы для каждой табличке б��ли свои сиквенсы. Конечно, со временем это безобразие поправили, но старые ключи всё равно остались. Более того, они корнями проросли по внешним ключам с другими таблицам. Фиксируем вторую проблему, связанную со сквозной нумерацией всех справочников.

На этом мучения с таблицами оборудования не закончились. Потому как по последним требованиям оборудование имеет различные характеристики, более того их число переменно, а одна характеристика может иметь несколько значений. А значит появляется третья проблема, а именно иметь возможность хранить переменное число характеристик какой-то записи.

Вроде как с этим справились, но заказчик не дремлет, у него всегда есть наготове что-нибудь новенькое. И вот приходит требование — все справочники историчные (например, название продукта было одним, а потом его переименовали, и по документам на разные даты нужно показывать актуальное название). Само по себе требование нормальное, ничего не скажешь. А если ещё в отделе разработки есть кто-то, кто проходит испытательный срок, так вообще всё в шоколаде, можно и не заметить, что это проблема. Однако проходит всё, как обычно — с полным авралом, а тут ещё этим нужно заниматься. Создаём таблички, дублирующие таблицы соответствующих справочников для того, чтобы там хранить хронологию изменений справочника. Но, создавая эти таблицы, мы заодно создаём себе четвёртую проблему, теперь в коде нужно в зависимости от даты обращаться то ли к основной таблице, то ли к исторической.

Ну мы же молодцы, мы и это победили))) Теперь, попивая чай из своей кружки, начинаешь дискутировать с другими коллегами на тему, что им приходилось решать, и понимаешь, что список проблем пополняется. В обсуждении стоит вопрос как хранить версии одной и той же записи. Хочу оговорится, что версия, это не то, что укладывается в таблицу историчности. В историчности понятно, до такого-то числа было одно название, а начиная с этой даты актуальным становится другое. А в версионности подразумевается, что запись была сначала сохранена с ошибкой, а через несколько часов это поняли и её изменили, и нужно знать все состояния этой записи. Во-первых, здесь должно быть дробление на время, не только сутки. А во-вторых, такие следы нужны в случае разборок. Например, заполняли прайс, ошиблись, успели товар продать по такой цене, а потом поправили, но в конце дня случился дебаланс. Однако решение для таких ситуаций меня лично напрягло, предлагалась все такие изменения хранить в самой таблице. Не буду устраивать холивар на сколько так правильно, но для меня точно обозначилась пятая проблема, а именно хранение изменений записей.

Итак, обобщая вышесказанное мы видим перед собой пять увесистых грабель. Теперь наша задача определить стратегию, позволяющую обойти и не наступить на них.

Сколько можно наступать на одни и те же грабли, давайте скинимся и купим новые

Начиная проектировать систему с нуля, никто не может предугадать путь её развития, а значит не сможет сказать на каком уровне придётся обобщать, как в описанном примере с оборудованием. Поэтому имеет смысл сразу задать абстрактную сущность, распространяемую на все таблицы НСИ. Таким образом все справочники будут иметь прообраз в едином справочнике с разделением на типы.

CREATE TABLE nsi_type (
    nsi_type_id     NUMBER(10) NOT NULL,
    name            VARCHAR2(50) NOT NULL,
    descr           VARCHAR2(100),
    table_name      VARCHAR2(50) NOT NULL,
    CONSTRAINT nsi_type_pk PRIMARY KEY (nsi_type_id)
);

CREATE TABLE nsi (
    nsi_id      NUMBER(10) NOT NULL,
    nsi_type_id NUMBER(10) NOT NULL,
    descr       VARCHAR2(100),
    create_date DATE NOT NULL,
    modif_date  DATE NOT NULL,
    begin_date  DATE,
    CONSTRAINT nsi_nsi_type_fk FOREIGN KEY (nsi_type_id) REFERENCES nsi_type (nsi_type_id),
    CONSTRAINT nsi_uk UNIQUE(nsi_type_id, nsi_id)
);

CREATE SEQUENCE nsi_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Таблица nsi_type системная, заполняется по мере добавления новых справочников. Таблица nsi хранит ключи и системные поля. Заодно создаём собственный сиквенс и тем самым закрываем вторую проблему.

Так же создадим пакет, содержащий основную функциональ��ость по работе со справочниками и будем его постепенно заполнять.

create or replace NONEDITIONABLE PACKAGE BODY pkg_nsi
IS

    /* По названию таблицы возвращается тип НСИ
    *  @param p_table_name VARCHAR2 - название таблицы
    *  @return nsi.nsi_type_id%TYPE - тип из таблицы nsi_type
    */
    FUNCTION get_type_id(p_table_name IN VARCHAR2) 
    RETURN nsi_type.nsi_type_id%TYPE
    AS
       v_type_id 	nsi_type.nsi_type_id%TYPE; 
    BEGIN
        SELECT nsi_type_id INTO v_type_id 
        FROM nsi_type
        WHERE TRIM(LOWER(table_name)) = TRIM(LOWER(p_table_name));

        RETURN v_type_id;
    END get_type_id;

    /* Возвращает следующий id из nsi_seq
    *  @return nsi.nsi_id%TYPE - id из nsi_seq
    */
    FUNCTION get_nsi_id 
    RETURN nsi.nsi_id%TYPE
    AS
       v_id 	nsi.nsi_id%TYPE; 
    BEGIN    
        SELECT nsi_seq.NEXTVAL INTO v_id FROM DUAL;
        RETURN v_id;
    END get_nsi_id;

    /* По типу справочника возвращает наименование таблицы
    *  @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип из таблицы nsi_type 
    *  @return nsi_type.table_name%TYPE - название таблицы
    */
    FUNCTION get_table_name(p_nsi_type_id IN nsi_type.nsi_type_id%TYPE) 
    RETURN nsi_type.table_name%TYPE
    AS
       v_table_name nsi_type.table_name%TYPE; 
    BEGIN
        SELECT table_name INTO v_table_name 
        FROM nsi_type
        WHERE nsi_type_id = p_nsi_type_id;

        RETURN v_table_name;
    END get_table_name;

    /* Для определённого справчоника возвращает описание из таблицы nsi
    *  @param p_nsi_id nsi.nsi_id%TYPE - ключ справочника 
    *  @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника 
    *  @return nsi.descr%TYPE - описание
    */
    FUNCTION get_nsi_descr (
        p_nsi_id        IN nsi.nsi_id%TYPE,
        p_nsi_type_id   IN nsi.nsi_type_id%TYPE) 
    RETURN nsi.descr%TYPE
    AS
       v_nsi_descr  nsi.descr%TYPE; 
    BEGIN
        SELECT descr 
          INTO v_nsi_descr 
          FROM nsi
         WHERE nsi_id = p_nsi_id
           AND nsi_type_id = p_nsi_type_id;

        RETURN v_nsi_descr;
    END get_nsi_descr;
...
END pkg_nsi;

Здесь пока представлены вспомогательные функции для обеспечения необходимой инфраструктуры.

Итак стоит задача создать справочник организаций, куда же без него, любое предприятие контактирует со сторонними организациями — это и поставщики, и клиенты, и партнёры. Сразу добавим соответствующий тип в таблицу nsi_type и определим таблицу nsi_organization.

CREATE TABLE nsi_organization (
    nsi_id      NUMBER(10) NOT NULL,
    name        VARCHAR2(50) NOT NULL,
    full_name   VARCHAR2(100) NOT NULL,
    inn         VARCHAR2(12) NOT NULL,
    CONSTRAINT nsi_organization_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_organization_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

INSERT INTO nsi_type (nsi_type_id, name, descr, table_name) 
VALUES (11, 'Организация', 'Акционерное общество, компания, филиал, предприятие', 'nsi_organization');

Теперь, пока не поздно, нужно вспомнить про грабли с номером «пять». Если начнём добавлять записи в созданную таблицу организаций, то это событие нужно где-то фиксировать.

CREATE TABLE nsi_log (
    nsi_log_id        NUMBER(10) NOT NULL,
    nsi_id            NUMBER(10) NOT NULL,
    table_name        VARCHAR2(100),
    oper_num          NUMBER,
    descr             CLOB,
    create_date       DATE,
    CONSTRAINT nsi_log_pk PRIMARY KEY (nsi_log_id),
    CONSTRAINT nsi_log_oper_num_ch CHECK (oper_num IN (1, 2, 3, 4, 5, 6, 7))
);

COMMENT ON TABLE nsi_log IS 'НСИ. Логирование операций';
COMMENT ON COLUMN nsi_log.nsi_log_id IS 'Ключ';
COMMENT ON COLUMN nsi_log.nsi_id IS 'Справочник';
COMMENT ON COLUMN nsi_log.table_name IS 'Наименование таблицы';
COMMENT ON COLUMN nsi_log.oper_num IS 'Номер операции (1 - создание записи, 2 - изменение записи, 3 - удаление записи, 4 - добавление атрибута, 5 - изменение атрибута, 6 - удаление атрибута, 7 - создание версии истории).';
COMMENT ON COLUMN nsi_log.descr IS 'Описание';
COMMENT ON COLUMN nsi_log.create_date IS 'Дата создания';

А так же в пакет добавлена функция логирования.

    -- Ограничение CHECK nsi_log_oper_num_ch
    NSI_LOG_OPERNUM_INSERT          NUMBER := 1;
    NSI_LOG_OPERNUM_UPDATE          NUMBER := 2;
    NSI_LOG_OPERNUM_DELETE          NUMBER := 3;
    NSI_LOG_OPERNUM_ATTR_INSERT     NUMBER := 4;
    NSI_LOG_OPERNUM_ATTR_UPDATE     NUMBER := 5;
    NSI_LOG_OPERNUM_ATTR_DELETE     NUMBER := 6;
    NSI_LOG_OPERNUM_HISTORY_PUSH    NUMBER := 7;

    /* Добавление записи логирования операций.
    *  @param p_nsi_id nsi.nsi_id%TYPE - справочник
    *  @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника
    *  @param p_oper_num NUMBER - номер операции
    *  @param p_descr VARCHAR2 - описание
    */
    PROCEDURE log_oper (
        p_nsi_id        IN nsi.nsi_id%TYPE,
        p_nsi_type_id   IN nsi_type.nsi_type_id%TYPE,
        p_oper_num      IN NUMBER,
        p_descr         IN VARCHAR2)
    AS
    BEGIN
        INSERT INTO nsi_log 
        (nsi_log_id, nsi_id, table_name, oper_num, descr, create_date)
        VALUES 
        (get_nsi_id(), p_nsi_id, get_table_name(p_nsi_type_id), p_oper_num, p_descr, Sysdate);
    END;

Таким образом разрешена пятая проблема, теперь для любой записи НСИ можно посмотреть, что с ней происходило.

Пытаемся добавить туда организацию.

INSERT INTO nsi_organization (nsi_id, name, full_name, inn)
VALUES (1, 'АО "Рога и копыта"', 'Акционерное общество "Рога и копыта"', '11223344');

Конечно мы нарвёмся на констраинт nsi_organization_nsi_fk. Поэтому все справочные таблицы должны быть снабжены необходимой доработкой триггеров.

CREATE OR REPLACE TRIGGER nsi_organization_trg_insert 
BEFORE INSERT ON nsi_organization FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_organization');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_descr := 'name = ''' || :NEW.name || ''', full_name = ''' || :NEW.full_name || ''', inn = ''' || :NEW.inn || ''' ';
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_organization_trg_update 
BEFORE UPDATE ON nsi_organization FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_organization');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_descr := 'name = ''' || :NEW.name || ''', full_name = ''' || :NEW.full_name || ''', inn = ''' || :NEW.inn || ''' ';
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_organization_trg_delete 
AFTER DELETE ON nsi_organization FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_organization');

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_descr := 'name = ''' || :OLD.name || ''', full_name = ''' || :OLD.full_name || ''', inn = ''' || :OLD.inn || ''' ';
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

А теперь добавление записи пройдёт без проблем (ключ уже указывать не надо). Заодно в таблице nsi появится первая запись, а так же в таблице логирования будет зафиксировано это событие.

INSERT INTO nsi_organization (name, full_name, inn)
VALUES ('АО "Рога и копыта"', 'Акционерное общество "Рога и копыта"', '11223344');

Но пока можно заметить только дополнительные расходы на создание таблицы какого-то справочника, а никак не преимущество единого подхода. Тогда вспомним про четвёртую проблему — нам необходимо хранить историчность данных в таблицах справочника, а так же извлекать актуальное состояние на заданную дату.

CREATE TABLE nsi_history (
    nsi_history_id  NUMBER(10) NOT NULL,
    nsi_id          NUMBER(10) NOT NULL,
    nsi_type_id     NUMBER(10) NOT NULL,
    version         NUMBER(10) NOT NULL,
    content         CLOB NOT NULL,
    note            VARCHAR2(100),
    begin_date      DATE NOT NULL,
    end_date        DATE NOT NULL,     
    CONSTRAINT nsi_history_pk PRIMARY KEY (nsi_history_id),
    CONSTRAINT nsi_history_nsi_type_fk FOREIGN KEY (nsi_type_id) REFERENCES nsi_type (nsi_type_id),
    CONSTRAINT nsi_history_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id),
    CONSTRAINT nsi_history_content_json_chk CHECK (content IS JSON)
);

COMMENT ON TABLE nsi_history IS 'Историчность справочника';
COMMENT ON COLUMN nsi_history.nsi_history_id IS 'Ключ';
COMMENT ON COLUMN nsi_history.nsi_id IS 'Справочник';
COMMENT ON COLUMN nsi_history.nsi_type_id IS 'Тип справочника';
COMMENT ON COLUMN nsi_history.version IS 'Версия';
COMMENT ON COLUMN nsi_history.content IS 'Содержимое справочника';
COMMENT ON COLUMN nsi_history.note IS 'Пояснение';
COMMENT ON COLUMN nsi_history.begin_date IS 'Дата начала действия';
COMMENT ON COLUMN nsi_history.end_date IS 'Дата окончания действия';

В пакет pkg_nsi добавим функцию сохранения записи в историческую таблицу. Хранить запись будем в формате json, поэтому в пакете так же появится возможность получить json для переданного запроса.

    /* Для переданного запроса возвращается json
    *  @param p_query VARCHAR2 - запрос
    *  @return CLOB - нотация json
    */
    FUNCTION get_json(p_query IN VARCHAR2) 
    RETURN CLOB
    AS
      v_theCursor       integer default dbms_sql.open_cursor;
      v_columnValue     varchar2(4000);
      v_status          integer;
      v_descTbl         dbms_sql.desc_tab;
      v_colCnt          number;
      v_res             clob;
    BEGIN
        dbms_sql.parse(v_theCursor, p_query, dbms_sql.native);
        dbms_sql.describe_columns( v_theCursor, v_colCnt, v_descTbl);

        FOR i IN 1 .. v_colCnt LOOP
            dbms_sql.define_column(v_theCursor, i, v_columnValue, 4000);
        END LOOP;
      
        v_status := dbms_sql.execute(v_theCursor);
        WHILE ( dbms_sql.fetch_rows(v_theCursor) > 0 ) LOOP
            FOR i IN 1 .. v_colCnt LOOP
                dbms_sql.column_value( v_theCursor, i, v_columnValue );
                IF i > 1 THEN
                    v_res := v_res || ', ';
                END IF;
                v_res := v_res || '"' || v_descTbl(i).col_name || '" : "' || replace(v_columnValue, '"', '\"') || '"';
            END LOOP;
            
            -- Пока что подразумеваем, что возвращается только одна запись, поэтому берём её
            -- в случае необходимости изменим логику
            EXIT;
        END LOOP;
        
        RETURN '{' || v_res || '}';
      
    exception
        when others then dbms_sql.close_cursor( v_theCursor ); RAISE;
    END get_json;

    /* Сохранение текущего состояния справочника в таблицу историчности.
    *  @param p_nsi_id nsi.nsi_id%TYPE - справочник
    *  @param p_nsi_type_id nsi_type.nsi_type_id%TYPE - тип справочника
    *  @param p_end_date nsi_history.end_date%TYPE - дата окончания текущего содержимого справочника
    *  @param p_note nsi_history.note%TYPE - пояснение причины сохранения в истории
    */
    PROCEDURE nsi_history_push (
        p_nsi_id        IN nsi.nsi_id%TYPE,
        p_nsi_type_id   IN nsi_type.nsi_type_id%TYPE,
        p_end_date      IN nsi_history.end_date%TYPE,
        p_note          IN nsi_history.note%TYPE)
    AS
        v_table_name VARCHAR2(50);
        v_content CLOB;
        v_max_ver NUMBER;
        v_begin_date DATE;
    BEGIN
        IF (p_end_date IS NULL) THEN
            RAISE_APPLICATION_ERROR (NSI_ERROR_CODE, 
                '[nsi_history_push] Дата окончания должна быть определена.'); 
        END IF;

        IF (Trunc(p_end_date) > Trunc(Sysdate) ) THEN
            RAISE_APPLICATION_ERROR (NSI_ERROR_CODE, 
                '[nsi_history_push] Дата окончания не должна превышать текущую дату.'); 
        END IF;
        
        SELECT begin_date INTO v_begin_date
          FROM nsi
         WHERE nsi_id = p_nsi_id
           AND nsi_type_id = p_nsi_type_id;

        IF (Trunc(p_end_date) < Trunc(v_begin_date) ) THEN
            RAISE_APPLICATION_ERROR (NSI_ERROR_CODE, 
                '[nsi_history_push] Дата окончания не должна быть меньше даты начала действия текущей версии записи.'); 
        END IF;
        
        v_table_name := get_table_name(p_nsi_type_id);
        v_content := get_json ('select * from ' || v_table_name || ' where nsi_id=' || p_nsi_id);
        
        SELECT MAX(version) INTO v_max_ver
          FROM nsi_history
         WHERE nsi_id = p_nsi_id
           AND nsi_type_id = p_nsi_type_id;
          
        IF (v_max_ver IS NULL) THEN
            v_max_ver := 0;
        END IF;

        v_max_ver := v_max_ver + 1;
        
        UPDATE nsi
           SET begin_date = Trunc(p_end_date) + 1
         WHERE nsi_id = p_nsi_id
           AND nsi_type_id = p_nsi_type_id;
           
        INSERT INTO nsi_history
        (nsi_history_id, nsi_id, nsi_type_id, version, content, note, begin_date, end_date)
        VALUES (get_nsi_id, p_nsi_id, p_nsi_type_id, v_max_ver, v_content, p_note, v_begin_date, Trunc(p_end_date));
        
        log_oper(p_nsi_id, p_nsi_type_id, NSI_LOG_OPERNUM_HISTORY_PUSH, v_content);
    END nsi_history_push; 

Таким образом любой справочник может воспользоваться этой функцией, чтобы увести в историю текущее состояние. Уже хорошо, хоть что-то полезное появилось от такого обобщения))) Для извлечения актуального состояния справочника добавим в пакет соответствующую pipeline-функцию. Записи справочника будут возвращаться в тип, расширенный системными полями.

    -- Запись содержит поля таблицы nsi_organization и дополнены служебными полями nsi
    TYPE nsi_organization_rec IS RECORD(
        nsi_id          nsi_organization.nsi_id%TYPE, 
        name            nsi_organization.name%TYPE,
        full_name       nsi_organization.full_name%TYPE,
        inn             nsi_organization.inn%TYPE,
        nsi_type_id     nsi.nsi_type_id%TYPE, 
        create_date     nsi.create_date%TYPE,
        modif_date      nsi.create_date%TYPE,
        version         nsi_history.version%TYPE,
        begin_date      nsi.begin_date%TYPE,
        end_date        nsi_history.end_date%TYPE
    );
    TYPE nsi_organization_list IS TABLE OF nsi_organization_rec;

    /* Возвращает список, актуальный на указанную дату.
    *  Если дата не задана, актуальной считается текущая дата.
    *  @param p_date DATE - дата, на которую необходимо получить состояние справочника
    *  @return nsi_organization_table - таблица с записями nsi_organization_rec
    */
    FUNCTION nsi_organization_table(p_date IN DATE := null) 
    RETURN nsi_organization_list PIPELINED
    AS
        v_date date;
    BEGIN
        v_date := Trunc(Sysdate);
        IF p_date IS NOT NULL THEN
            v_date := Trunc(p_date);
        END IF;

        FOR rec IN (
            SELECT 
                o.nsi_id, o.name, o.full_name, o.inn,
                n.nsi_type_id, n.create_date, n.modif_date, 
                0 AS version, n.begin_date, to_date(null) AS end_date
            FROM 
                nsi_organization o INNER JOIN nsi n
                ON (o.nsi_id = n.nsi_id)
            WHERE 
                n.begin_date <= v_date
            UNION ALL
            SELECT 
                n.nsi_id, 
                json_value(h.content, '$.NAME') AS name, 
                json_value(h.content, '$.FULL_NAME') AS full_name,
                json_value(h.content, '$.INN') AS inn, 
                n.nsi_type_id, n.create_date, n.modif_date, 
                h.version, h.begin_date, h.end_date
            FROM 
                nsi_history h INNER JOIN nsi n
                ON (h.nsi_id = n.nsi_id AND h.nsi_type_id = n.nsi_type_id)
            WHERE 
                    h.begin_date <= v_date
                AND h.end_date >= v_date
        ) LOOP
            PIPE ROW (rec);
        END LOOP;
    END nsi_organization_table;

Применим к нашей таблице nsi_organization.

select * from nsi where nsi_id=1;
---------------------------------------------------------------------------------------
"NSI_ID"	"NSI_TYPE_ID"	"DESCR"	"CREATE_DATE"	"MODIF_DATE"	"BEGIN_DATE"
1	1	"АО ""Рога и копыта"""	11.03.20	11.03.20	11.03.20
---------------------------------------------------------------------------------------

begin
-- конечно это нереальная ситуация по смене инн, но для тестового примера вполне подойдёт
    pkg_nsi.nsi_history_push(202, 1, sysdate, 'смена инн');
end;
select * from nsi_history;
---------------------------------------------------------------------------------------
"NSI_HISTORY_ID"	"NSI_ID"	"NSI_TYPE_ID"	"VERSION"	"CONTENT"	"NOTE"	"BEGIN_DATE"	"END_DATE"
205	1	1	1	"{""NSI_ID"" : ""1"", ""NAME"" : ""АО \""Рога и копыта\"""", ""FULL_NAME"" : ""Акционерное общество \""Рога и копыта\"""", ""INN"" : ""11223344""}"	"смена инн"	11.03.20	11.03.20
---------------------------------------------------------------------------------------

-- следует обратить внимание на дату начала
-- так как был вызов сохранения в историю, то новая версия начала быть актуальной на следующий день
select * from nsi where nsi_id=1;
---------------------------------------------------------------------------------------
"NSI_ID"	"NSI_TYPE_ID"	"DESCR"	"CREATE_DATE"	"MODIF_DATE"	"BEGIN_DATE"
1	1	"АО ""Рога и копыта"""	11.03.20	11.03.20	12.03.20
---------------------------------------------------------------------------------------

-- обновим инн и посмотрим выборку на различные даты
-- различия присутствуют в полях inn, version, begin_date, end_date
-- текущая запись в таблице имеет версию 0
update nsi_organization set inn='99887766' where nsi_id=1;

select * from table(pkg_nsi.nsi_organization_table(sysdate));
---------------------------------------------------------------------------------------
"NSI_ID"	"NAME"	"FULL_NAME"	"INN"	"NSI_TYPE_ID"	"CREATE_DATE"	"MODIF_DATE"	"VERSION"	"BEGIN_DATE"	"END_DATE"
1	"АО ""Рога и копыта"""	"Акционерное общество ""Рога и копыта"""	"11223344"	1	11.03.20	11.03.20	1	11.03.20	11.03.20
---------------------------------------------------------------------------------------

select * from table(pkg_nsi.nsi_organization_table(sysdate+1));
---------------------------------------------------------------------------------------
"NSI_ID"	"NAME"	"FULL_NAME"	"INN"	"NSI_TYPE_ID"	"CREATE_DATE"	"MODIF_DATE"	"VERSION"	"BEGIN_DATE"	"END_DATE"
1	"АО ""Рога и копыта"""	"Акционерное общество ""Рога и копыта"""	"99887766"	1	11.03.20	11.03.20	0	12.03.20	
---------------------------------------------------------------------------------------

Функция nsi_organization_table очень полезна, потому как удовлетворяет нашим требованиям и окончательно уводит проблему номер четыре в прошлое.

Идём дальше. Раз у нас появилось такое преимущество с введением единого подхода для работы со всеми справочниками, то воспользуемся им и для хранения дополнительной информации, которой может быть наделена любая запись из любого справочника. Такое механизм уже давно существует, называется EAV-pattern, его и реализуем.

    -- Ограничение CHECK nsi_attribute_type_ch
    NSI_ATTRIBUTE_TYPE_STRING   NUMBER := 1;
    NSI_ATTRIBUTE_TYPE_INT      NUMBER := 2;
    NSI_ATTRIBUTE_TYPE_DOUBLE   NUMBER := 3;
    NSI_ATTRIBUTE_TYPE_DATE     NUMBER := 4;

CREATE TABLE nsi_attribute_type (
    nsi_attribute_type_id   NUMBER(10) NOT NULL,
    value_type              NUMBER NOT NULL,
    descr                   VARCHAR2(100) NOT NULL,
    CONSTRAINT nsi_attribute_type_pk PRIMARY KEY (nsi_attribute_type_id),
    CONSTRAINT nsi_attribute_type_ch CHECK (value_type IN (1, 2, 3, 4)),
    CONSTRAINT nsi_attribute_type_fk FOREIGN KEY (nsi_attribute_type_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_attribute_type IS 'НСИ. Тип атрибута';
COMMENT ON COLUMN nsi_attribute_type.nsi_attribute_type_id IS 'Ключ';
COMMENT ON COLUMN nsi_attribute_type.value_type IS 'Тип значения (1 - строка, 2 - целое, 3 - дробное, 4 - дата)';
COMMENT ON COLUMN nsi_attribute_type.descr IS 'Описание';

CREATE TABLE nsi_attribute (
    nsi_attribute_id        NUMBER(10) NOT NULL,
    nsi_attribute_type_id   NUMBER(10) NOT NULL,
    nsi_id                  NUMBER(10) NOT NULL,
    nsi_type_id             NUMBER(10) NOT NULL,
    value_1                 VARCHAR2(100),
    value_2_3               NUMBER,
    value_4                 DATE,
    begin_date              DATE,
    end_date                DATE,
    CONSTRAINT nsi_attribute_pk PRIMARY KEY (nsi_attribute_id),
    CONSTRAINT nsi_attribute_type_fk FOREIGN KEY (nsi_attribute_type_id) REFERENCES nsi_attribute_type (nsi_attribute_type_id),
    CONSTRAINT nsi_attribute_nsi_fk FOREIGN KEY (nsi_id, nsi_type_id) REFERENCES nsi (nsi_id, nsi_type_id)
);

COMMENT ON TABLE nsi_attribute IS 'НСИ. Тип атрибута';
COMMENT ON COLUMN nsi_attribute.nsi_attribute_id IS 'Ключ';
COMMENT ON COLUMN nsi_attribute.nsi_attribute_type_id IS 'Тип атрибута';
COMMENT ON COLUMN nsi_attribute.nsi_id IS 'Справочник';
COMMENT ON COLUMN nsi_attribute.nsi_type_id is 'Тип справочника';
COMMENT ON COLUMN nsi_attribute.value_1 IS 'Значение типа строка';
COMMENT ON COLUMN nsi_attribute.value_2_3 IS 'Значение типа целое или дробное';
COMMENT ON COLUMN nsi_attribute.value_4 IS 'Значение типа дата';
COMMENT ON COLUMN nsi_attribute.begin_date IS 'Дата начала действия атрибута';
COMMENT ON COLUMN nsi_attribute.end_date IS 'Дата окончания действия атрибута';

Очень часто в контексте документов имена собственные необходимо использовать в каком-то падеже, поэтому создадим новую таблицу с физическими лицами и по аналогии с организациями добавим обработку триггеров и тип для выборки.

CREATE TABLE nsi_person (
    nsi_id   NUMBER(10) NOT NULL,
    surname         VARCHAR2(30) NOT NULL,
    name            VARCHAR2(30) NOT NULL,
    patronymic      VARCHAR2(30) NOT NULL,
    birthday        DATE,
    CONSTRAINT nsi_person_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_person_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_person IS 'НСИ. Физическое лицо';
COMMENT ON COLUMN nsi_person.nsi_id IS 'Ключ';
COMMENT ON COLUMN nsi_person.surname IS 'Фамилия';
COMMENT ON COLUMN nsi_person.name IS 'Имя';
COMMENT ON COLUMN nsi_person.patronymic IS 'Отчество';
COMMENT ON COLUMN nsi_person.birthday IS 'Дата рождения';

CREATE OR REPLACE TRIGGER nsi_person_trg_insert 
BEFORE INSERT ON nsi_person FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_person');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_query := 'SELECT ''' || :NEW.surname || ''' AS surname, ''' || :NEW.name || ''' AS name, ''' || :NEW.patronymic || ''' AS patronymic, to_date(''' || :NEW.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_person_trg_update 
BEFORE UPDATE ON nsi_person FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_person');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :NEW.surname || ''' AS surname, ''' || :NEW.name || ''' AS name, ''' || :NEW.patronymic || ''' AS patronymic, to_date(''' || :NEW.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_person_trg_delete 
AFTER DELETE ON nsi_person FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_person');

    DELETE FROM nsi_history 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi_attribute 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.surname || ''' AS surname, ''' || :OLD.name || ''' AS name, ''' || :OLD.patronymic || ''' AS patronymic, to_date(''' || :OLD.birthday || ''', ''dd.mm.yy'') AS birthday FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

Осталось дополнить пакет pkg_nsi обработкой этой таблицы.

-- Запись содержит поля таблицы nsi_person и дополнены служебными полями nsi
    TYPE nsi_person_rec IS RECORD(
        nsi_id          nsi_person.nsi_id%TYPE, 
        surname         nsi_person.surname%TYPE,
        name            nsi_person.name%TYPE,
        patronymic      nsi_person.patronymic%TYPE,
        birthday        nsi_person.birthday%TYPE,
        nsi_type_id     nsi.nsi_type_id%TYPE, 
        create_date     nsi.create_date%TYPE,
        modif_date      nsi.create_date%TYPE,
        version         nsi_history.version%TYPE,
        begin_date      nsi.begin_date%TYPE,
        end_date        nsi_history.end_date%TYPE
    );
    TYPE nsi_person_list IS TABLE OF nsi_person_rec;

    /* Возвращает список, актуальный на указанную дату.
    *  Если дата не задана, актуальной считается текущая дата.
    *  @param p_date DATE - дата, на которую необходимо получить состояние справочника
    *  @return nsi_person_table - таблица с записями nsi_person_rec
    */
    FUNCTION nsi_person_table(p_date IN DATE := null) 
    RETURN nsi_person_list PIPELINED
    AS
        v_date date;
    BEGIN
        v_date := Trunc(Sysdate);
        IF p_date IS NOT NULL THEN
            v_date := Trunc(p_date);
        END IF;

        FOR rec IN (
            SELECT 
                p.nsi_id, p.surname, p.name, p.patronymic, p.birthday, 
                n.nsi_type_id, n.create_date, n.modif_date, 
                0 AS version, n.begin_date, to_date(null) AS end_date
            FROM 
                nsi_person p INNER JOIN nsi n
                ON (p.nsi_id = n.nsi_id)
            WHERE 
                n.begin_date <= v_date
            UNION ALL
            SELECT 
                n.nsi_id, 
                json_value(h.content, '$.SURNAME') AS surname, 
                json_value(h.content, '$.NAME') AS name,
                json_value(h.content, '$.PATRONYMIC') AS patronymic,
                to_date(json_value(h.content, '$.BIRTHDAY')) AS birthday,
                n.nsi_type_id, n.create_date, n.modif_date, 
                h.version, h.begin_date, h.end_date
            FROM 
                nsi_history h INNER JOIN nsi n
                ON (h.nsi_id = n.nsi_id AND h.nsi_type_id = n.nsi_type_id)
            WHERE 
                    h.begin_date <= v_date
                AND h.end_date >= v_date
        ) LOOP
            PIPE ROW (rec);
        END LOOP;
    END nsi_person_table;

И добавим кого-нибудь в эту таблицу.

INSERT INTO nsi_person
(surname, name, patronymic, birthday)
VALUES ('Деревянный', 'Буратино', 'Карлович', to_date('22.12.70', 'dd.mm.yy'));

Создадим атрибуты для самого востребованного родительного падежа.

INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr) 
VALUES (1, 1, 'Фамилия в род. падеже');
INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr) 
VALUES (2, 1, 'Имя в род. падеже');
INSERT INTO nsi_attribute_type (nsi_attribute_type_id, value_type, descr) 
VALUES (3, 1, 'Отчество в род. падеже');

В пакете pkg_nsi добавим функции для работы с атрибутами справочников.

    /* Для переданного id возвращает тип значения и описание атрибута.
    *  @param p_nsi_attribute_type_id nsi_attribute_type.nsi_attribute_type_id%TYPE - Тип атрибута
    *  @param p_value_type nsi_attribute_type.value_type%TYPE - Тип знаения
    *  @param p_descr nsi_attribute_type.descr%TYPE - Описание атрибута
    */
    PROCEDURE get_attribute_type (
        p_nsi_attribute_type_id IN nsi_attribute_type.nsi_attribute_type_id%TYPE,
        p_value_type            OUT nsi_attribute_type.value_type%TYPE,
        p_descr                 OUT nsi_attribute_type.descr%TYPE)
    AS
    BEGIN
        SELECT value_type, descr
          INTO p_value_type, p_descr
          FROM nsi_attribute_type
         WHERE nsi_attribute_type_id = p_nsi_attribute_type_id;
    END;


    /* Реализует вставку записи.
    *  @param p_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE - Тип атрибута
    *  @param p_nsi_id nsi_attribute.nsi_id%TYPE - Справочник
    *  @param p_nsi_type_id nsi_attribute.nsi_type_id%TYPE - Тип справочника
    *  @param p_value_1 nsi_attribute.value_1%TYPE - Значение строкового типа
    *  @param p_value_2_3 nsi_attribute.value_2_3%TYPE - Значение числового типа
    *  @param p_value_4 nsi_attribute.value_4%TYPE - Значение типа даты
    *  @param p_begin_date nsi_attribute.begin_date%TYPE - Дата начала действия атрибута
    *  @param p_end_date nsi_attribute.end_date%TYPE - Дата окончания действия атрибута
    */
    PROCEDURE nsi_attribute_insert (
        p_nsi_attribute_type_id IN nsi_attribute.nsi_attribute_type_id%TYPE,
        p_nsi_id                IN nsi_attribute.nsi_id%TYPE,
        p_nsi_type_id           IN nsi_attribute.nsi_type_id%TYPE,
        p_value_1               IN nsi_attribute.value_1%TYPE,
        p_value_2_3             IN nsi_attribute.value_2_3%TYPE,
        p_value_4               IN nsi_attribute.value_4%TYPE,
        p_begin_date            IN nsi_attribute.begin_date%TYPE,
        p_end_date              IN nsi_attribute.end_date%TYPE)
    AS
        v_id            NUMBER;
        v_log_descr     nsi_log.descr%TYPE;
        v_value_type    nsi_attribute_type.value_type%TYPE;
        v_descr         nsi_attribute_type.descr%TYPE;
    BEGIN
        v_id := get_nsi_id;
        get_attribute_type(p_nsi_attribute_type_id, v_value_type, v_descr);

        IF (v_value_type = NSI_ATTRIBUTE_TYPE_STRING) THEN
            INSERT INTO nsi_attribute 
                (nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id, 
                 value_1, value_2_3, value_4, begin_date, end_date)
            VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
                    p_value_1, null, null, p_begin_date, p_end_date);
                    
            v_log_descr := p_value_1;
                    
        ELSIF (v_value_type IN (NSI_ATTRIBUTE_TYPE_INT, NSI_ATTRIBUTE_TYPE_DOUBLE)) THEN
            INSERT INTO nsi_attribute 
                (nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id, 
                 value_1, value_2_3, value_4, begin_date, end_date)
            VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
                    null, p_value_2_3, null, p_begin_date, p_end_date);
                    
            v_log_descr := p_value_2_3;
                    
        ELSE
            INSERT INTO nsi_attribute 
                (nsi_attribute_id, nsi_attribute_type_id, nsi_id, nsi_type_id, 
                 value_1, value_2_3, value_4, begin_date, end_date)
            VALUES (v_id, p_nsi_attribute_type_id, p_nsi_id, p_nsi_type_id,
                    null, null, p_value_4, p_begin_date, p_end_date);
                    
            v_log_descr := p_value_4;
        END IF;
        
        v_log_descr := '[' || get_nsi_descr(p_nsi_id, p_nsi_type_id) || '] ' ||
                       ' Атрибут: ' || v_descr || 
                       ' Значение: ' || v_log_descr || 
                       ' Период: ' || p_begin_date || ' - ' || p_end_date;
        log_oper(p_nsi_id, p_nsi_type_id, NSI_LOG_OPERNUM_ATTR_INSERT, v_log_descr);
    END;

    /* Реализует обновление типа и значения атрибута.
    *  @param p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE - Ключ атрибута
    *  @param p_value_1 nsi_attribute.value_1%TYPE - Значение строкового типа
    *  @param p_value_2_3 nsi_attribute.value_2_3%TYPE - Значение числового типа
    *  @param p_value_4 nsi_attribute.value_4%TYPE - Значение типа даты
    */
    PROCEDURE nsi_attribute_value (
        p_nsi_attribute_id      IN nsi_attribute.nsi_attribute_id%TYPE,
        p_value_1               IN nsi_attribute.value_1%TYPE,
        p_value_2_3             IN nsi_attribute.value_2_3%TYPE,
        p_value_4               IN nsi_attribute.value_4%TYPE)
    AS
        v_nsi_id            nsi.nsi_id%TYPE;
        v_nsi_type_id       nsi.nsi_type_id%TYPE;
        v_log_descr         nsi_log.descr%TYPE;
        v_value_type        nsi_attribute_type.value_type%TYPE;
        v_descr             nsi_attribute_type.descr%TYPE;
        v_nsi_attribute_type_id  nsi_attribute.nsi_attribute_type_id%TYPE;
    BEGIN
        SELECT nsi_attribute_type_id, nsi_id, nsi_type_id
          INTO v_nsi_attribute_type_id, v_nsi_id, v_nsi_type_id
          FROM nsi_attribute
          WHERE nsi_attribute_id = p_nsi_attribute_id;
          
        get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
        
        IF (v_value_type = NSI_ATTRIBUTE_TYPE_STRING) THEN
            UPDATE nsi_attribute
               SET value_1 = p_value_1,
                   value_2_3 = null,
                   value_4 = null
            WHERE nsi_attribute_id = p_nsi_attribute_id;

            v_log_descr := p_value_1;
                    
        ELSIF (v_value_type IN (NSI_ATTRIBUTE_TYPE_INT, NSI_ATTRIBUTE_TYPE_DOUBLE)) THEN
            UPDATE nsi_attribute
               SET value_1 = null,
                   value_2_3 = p_value_2_3,
                   value_4 = null
            WHERE nsi_attribute_id = p_nsi_attribute_id;

            v_log_descr := p_value_2_3;
                    
        ELSE
            UPDATE nsi_attribute
               SET value_1 = null,
                   value_2_3 = null,
                   value_4 = p_value_4
            WHERE nsi_attribute_id = p_nsi_attribute_id;

            v_log_descr := p_value_4;
        END IF;

         
        v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
                       ' Атрибут: ' || v_descr || 
                       ' Изменение значения: ' || v_log_descr;
        log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_UPDATE, v_log_descr);
    END;

    /* Реализует обновление периода действия атрибута.
    *  @param p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE - Ключ атрибута
    *  @param p_begin_date nsi_attribute.begin_date%TYPE - Дата начала действия атрибута
    *  @param p_end_date nsi_attribute.end_date%TYPE - Дата окончания действия атрибута
    */
    PROCEDURE nsi_attribute_period (
        p_nsi_attribute_id  IN nsi_attribute.nsi_attribute_id%TYPE,
        p_begin_date        IN nsi_attribute.begin_date%TYPE,
        p_end_date          IN nsi_attribute.end_date%TYPE)
    AS
        v_nsi_id        nsi.nsi_id%TYPE;
        v_nsi_type_id   nsi.nsi_type_id%TYPE;
        v_log_descr     nsi_log.descr%TYPE;
        v_value_type    nsi_attribute_type.value_type%TYPE;
        v_descr         nsi_attribute_type.descr%TYPE;
        v_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE;
    BEGIN
        SELECT nsi_id, nsi_type_id, nsi_attribute_type_id
          INTO v_nsi_id, v_nsi_type_id, v_nsi_attribute_type_id
          FROM nsi_attribute
         WHERE nsi_attribute_id = p_nsi_attribute_id;
    
        get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
        
        UPDATE nsi_attribute
           SET begin_date = p_begin_date,
               end_date = p_end_date
         WHERE nsi_attribute_id = p_nsi_attribute_id;
         
        v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
                       ' Атрибут: ' || v_descr || 
                       ' Изменение периода: ' || p_begin_date || ' - ' || p_end_date;
        log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_UPDATE, v_log_descr);
    END;

    /* Реализует удаление записи.
    *  @param p_nsi_attribute_id nsi_person.nsi_attribute_id%TYPE - id записи nsi_attribute
    */
    PROCEDURE nsi_attribute_delete (p_nsi_attribute_id nsi_attribute.nsi_attribute_id%TYPE)
    AS
        v_nsi_id        nsi.nsi_id%TYPE;
        v_nsi_type_id   nsi.nsi_type_id%TYPE;
        v_log_descr     nsi_log.descr%TYPE;
        v_value_type    nsi_attribute_type.value_type%TYPE;
        v_descr         nsi_attribute_type.descr%TYPE;
        v_nsi_attribute_type_id nsi_attribute.nsi_attribute_type_id%TYPE;
    BEGIN
        SELECT nsi_id, nsi_type_id, nsi_attribute_type_id
          INTO v_nsi_id, v_nsi_type_id, v_nsi_attribute_type_id
          FROM nsi_attribute
         WHERE nsi_attribute_id = p_nsi_attribute_id;
    
        get_attribute_type(v_nsi_attribute_type_id, v_value_type, v_descr);
        
        DELETE FROM nsi_attribute
        WHERE nsi_attribute_id = p_nsi_attribute_id;
         
        v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ' ||
                       ' Атрибут: ' || v_descr;
        log_oper(v_nsi_id, v_nsi_type_id, NSI_LOG_OPERNUM_ATTR_DELETE, v_log_descr);
    END;

Теперь присвоим соответствующие атрибуты.

begin
    pkg_nsi.nsi_attribute_insert(1, 225, 6, 'деревянного', null, null, sysdate, null);
    pkg_nsi.nsi_attribute_insert(2, 225, 6, 'Буратино', null, null, sysdate, null);
    pkg_nsi.nsi_attribute_insert(3, 225, 6, 'Карловича', null, null, sysdate, null);
end;

-- но когда просматриваем результат видим ошибку, фамилия Буратино написана с маленькой буквы, нужно исправить
--------------------------------------------------------------------------------------------
"NSI_ATTRIBUTE_ID"	"NSI_ATTRIBUTE_TYPE_ID"	"NSI_ID"	"NSI_TYPE_ID"	"VALUE_1"	"VALUE_2_3"	"VALUE_4"	"BEGIN_DATE"	"END_DATE"
230	1	225	6	"деревянного"			11.03.20	
232	2	225	6	"Буратино"			11.03.20	
234	3	225	6	"Карловича"			11.03.20	
--------------------------------------------------------------------------------------------

begin
    pkg_nsi.nsi_attribute_value(230, 'Деревянного', null, null);
end;
--------------------------------------------------------------------------------------------
"NSI_ATTRIBUTE_ID"	"NSI_ATTRIBUTE_TYPE_ID"	"NSI_ID"	"NSI_TYPE_ID"	"VALUE_1"	"VALUE_2_3"	"VALUE_4"	"BEGIN_DATE"	"END_DATE"
230	1	225	6	"Деревянного"			11.03.20
232	2	225	6	"Буратино"			11.03.20	
234	3	225	6	"Карловича"			11.03.20	
--------------------------------------------------------------------------------------------

-- перенесём начало действия атрибута на день раньше
begin
    pkg_nsi.nsi_attribute_period(230, sysdate-1, null);
    pkg_nsi.nsi_attribute_period(232, sysdate-1, null);
    pkg_nsi.nsi_attribute_period(234, sysdate-1, null);
end;
--------------------------------------------------------------------------------------------
"NSI_ATTRIBUTE_ID"	"NSI_ATTRIBUTE_TYPE_ID"	"NSI_ID"	"NSI_TYPE_ID"	"VALUE_1"	"VALUE_2_3"	"VALUE_4"	"BEGIN_DATE"	"END_DATE"
230	1	225	6	"Деревянного"			10.03.20	
232	2	225	6	"Буратино"			10.03.20	
234	3	225	6	"Карловича"			10.03.20	
--------------------------------------------------------------------------------------------

Таким образом мы победим третью проблему.

Кроме таблиц справочников в системе НСИ также важны отношение между ними. Так, например крупные организации включают в себя различные подразделения, филиалы, отделы и т.п., которые можно выстроить в древовидную структуру. Для начала заведём в нашей системе ещё несколько организаций, которые будут в подчинении у уже существующей «Рога и копыта».

INSERT INTO nsi_organization (name, full_name, inn)
VALUES ('Подразделение по обслуживанию рогов', 'Подразделение по обслуживанию рогов', '1111111111');
INSERT INTO nsi_organization (name, full_name, inn)
VALUES ('Подразделение по обслуживанию копыт', 'Подразделение по обслуживанию копыт', '2222222222');
INSERT INTO nsi_organization (name, full_name, inn)
VALUES ('Отдел по изготовлению подков', 'Отдел по изготовлению подков', '3333333333');

----------------------------------------------------------------------------
281	1	Подразделение по обслуживанию рогов	13.03.20	13.03.20	13.03.20
283	1	Подразделение по обслуживанию копыт	13.03.20	13.03.20	13.03.20
285	1	Отдел по изготовлению подков	13.03.20	13.03.20	13.03.20
1	1	АО "Рога и копыта"	11.03.20	13.03.20	12.03.20
----------------------------------------------------------------------------

Теперь нужно показать в каком отношении эти организации находятся между собой. Для этого необходима таблица с древовидной структурой и указанием периода действия, потому как всё подвержено изменением во времени и нужно это учитывать.

CREATE TABLE nsi_structure (
    nsi_structure_id    NUMBER(10) NOT NULL,
    nsi_parent_structure_id NUMBER(10),
    nsi_id              NUMBER(10) NOT NULL,
    nsi_type_id         NUMBER(10) NOT NULL,
    ordnum              NUMBER,
    begin_date          DATE NOT NULL,
    end_date            DATE,
    CONSTRAINT nsi_structure_pk PRIMARY KEY (nsi_structure_id),
    CONSTRAINT nsi_parent_struct_fk FOREIGN KEY (nsi_parent_structure_id) REFERENCES nsi_structure (nsi_structure_id),
    CONSTRAINT nsi_struct_nsi_fk FOREIGN KEY (nsi_id, nsi_type_id) REFERENCES nsi (nsi_id, nsi_type_id)
);

COMMENT ON TABLE nsi_structure IS 'НСИ. Таблица структуры справочников';
COMMENT ON COLUMN nsi_structure.nsi_structure_id IS 'Ключ';
COMMENT ON COLUMN nsi_structure.nsi_parent_structure_id IS 'Ключ родителя';
COMMENT ON COLUMN nsi_structure.nsi_id IS 'Справочник';
COMMENT ON COLUMN nsi_structure.nsi_type_id IS 'Тип справочника';
COMMENT ON COLUMN nsi_structure.ordnum IS 'Порядковый номер';
COMMENT ON COLUMN nsi_structure.begin_date IS 'Дата начала действия';
COMMENT ON COLUMN nsi_structure.end_date IS 'Дата окончания действия';

Конечно, следует расширить возможности пакета pkg_nsi, чтобы можно было настраивать структуру для различных таблиц.

    /* Реализует вставку записи.
    *  @param p_nsi_parent_structure_id nsi_structure.nsi_parent_structure_id%TYPE - запись родителя
    *  @param p_nsi_id nsi_structure.nsi_id%TYPE - справочник
    *  @param p_nsi_type_id nsi_structure.nsi_type_id%TYPE - тип справочника
    *  @param p_ordnum nsi_structure.ordnum%TYPE - порядковый номер
    *  @param p_begin_date nsi_structure.begin_date%TYPE - дата начала действия записи
    *  @param p_end_date nsi_structure.end_date%TYPE - дата окончания действия записи
    */
    FUNCTION nsi_structure_insert (
        p_nsi_parent_structure_id   IN nsi_structure.nsi_parent_structure_id%TYPE,
        p_nsi_id                    IN nsi_structure.nsi_id%TYPE,
        p_nsi_type_id               IN nsi_structure.nsi_type_id%TYPE,
        p_ordnum                    IN nsi_structure.ordnum%TYPE,
        p_begin_date                IN nsi_structure.begin_date%TYPE, 
        p_end_date                  IN nsi_structure.end_date%TYPE)
    RETURN nsi_structure.nsi_structure_id%TYPE
    AS
        v_id        NUMBER;
        v_log_descr nsi_log.descr%TYPE;
        v_type_id 	nsi.nsi_type_id%TYPE;
    BEGIN
        v_id := get_nsi_id;
        v_type_id := get_type_id('nsi_structure');

        INSERT INTO nsi_structure (
            nsi_structure_id, nsi_parent_structure_id,
            nsi_id, nsi_type_id, ordnum, begin_date, end_date)
        VALUES (
            v_id, p_nsi_parent_structure_id, 
            p_nsi_id, p_nsi_type_id, p_ordnum, Trunc(p_begin_date), Trunc(p_end_date));
         
        v_log_descr := '[' || get_nsi_descr(p_nsi_id, p_nsi_type_id) || '] ';
        v_log_descr := v_log_descr || 'Период ' || p_begin_date || ' - ' || p_end_date;
        log_oper (v_id, v_type_id, NSI_LOG_OPERNUM_INSERT, v_log_descr);
        
        RETURN v_id;
    END nsi_structure_insert;


    /* Реализует обновление порядкового номера записи.
    *  @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
    *  @param p_ordnum nsi_structure.ordnum%TYPE - порядковый номер
    */
    PROCEDURE nsi_structure_ordnum (
        p_nsi_structure_id  IN nsi_structure.nsi_structure_id%TYPE,
        p_ordnum            IN nsi_structure.ordnum%TYPE)
    AS
        v_nsi_id 		    nsi_structure.nsi_id%TYPE;
        v_nsi_type_id 	    nsi_structure.nsi_type_id%TYPE;
        v_type_id 	        nsi.nsi_type_id%TYPE;
        v_log_descr         nsi_log.descr%TYPE;
    BEGIN
        v_type_id := get_type_id('nsi_structure');
        
        SELECT nsi_id, nsi_type_id
          INTO v_nsi_id, v_nsi_type_id
          FROM nsi_structure
         WHERE nsi_structure_id = p_nsi_structure_id;

        UPDATE nsi_structure
           SET ordnum = p_ordnum 
         WHERE nsi_structure_id = p_nsi_structure_id;
         
        v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ';
        v_log_descr := v_log_descr || 'Номер ' || p_ordnum;
        log_oper (p_nsi_structure_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
    END;


    /* Реализует обновление периода действия записи.
    *  @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
    *  @param p_begin_date nsi_structure.begin_date%TYPE - дата начала действия
    *  @param p_end_date nsi_structure.end_date%TYPE - дата окончания действия
    */
    PROCEDURE nsi_structure_period (
        p_nsi_structure_id  IN nsi_structure.nsi_structure_id%TYPE,
        p_begin_date        IN nsi_structure.begin_date%TYPE, 
        p_end_date          IN nsi_structure.end_date%TYPE)
    AS
        v_nsi_id 		    nsi_structure.nsi_id%TYPE;
        v_nsi_type_id 	    nsi_structure.nsi_type_id%TYPE;
        v_type_id 	        nsi.nsi_type_id%TYPE;
        v_log_descr         nsi_log.descr%TYPE;
    BEGIN
        v_type_id := get_type_id('nsi_structure');
        
        SELECT nsi_id, nsi_type_id
          INTO v_nsi_id, v_nsi_type_id
          FROM nsi_structure
         WHERE nsi_structure_id = p_nsi_structure_id;

        UPDATE nsi_structure
           SET begin_date = Trunc(p_begin_date),
               end_date = Trunc(p_end_date) 
         WHERE nsi_structure_id = p_nsi_structure_id;
         
        v_log_descr := '[' || get_nsi_descr(v_nsi_id, v_nsi_type_id) || '] ';
        v_log_descr := v_log_descr || 'Период ' || p_begin_date || ' - ' || p_end_date;
        log_oper (p_nsi_structure_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
    END;


    /* Реализует удаление записи.
    *  @param p_nsi_structure_id nsi_structure.nsi_structure_id%TYPE - ключ nsi_structure
    */
    PROCEDURE nsi_structure_delete (p_nsi_structure_id IN nsi_structure.nsi_structure_id%TYPE)
    AS
        v_type_id 	nsi.nsi_type_id%TYPE;
        v_log_descr nsi_log.descr%TYPE;
    BEGIN
        v_type_id := pkg_nsi.get_type_id('nsi_structure');
        
        FOR rec IN (
            SELECT nsi_structure_id, nsi_parent_structure_id,
                    nsi_id, nsi_type_id, ordnum, begin_date, end_date
            FROM nsi_structure
            START WITH nsi_structure_id = p_nsi_structure_id
            CONNECT BY PRIOR nsi_structure_id = nsi_parent_structure_id
        )
        LOOP
            v_log_descr := '[' || pkg_nsi.get_nsi_descr(rec.nsi_id, rec.nsi_type_id) || '] ';
            v_log_descr := v_log_descr || 'Период ' || rec.begin_date || ' - ' || rec.end_date;
            pkg_nsi.log_oper (rec.nsi_structure_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
        END LOOP;
        
        DELETE FROM nsi_structure
        WHERE nsi_structure_id = p_nsi_structure_id;
    END;

После появления такого инструмента можно смело выстраивать отношения между организациями.

declare
    id number;
    root_id number;
begin
    root_id := pkg_nsi.nsi_structure_insert(null, 1, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
    id := pkg_nsi.nsi_structure_insert(root_id, 281, 1, null, to_date('13.02.20', 'dd.mm.yy'), to_date('15.02.20', 'dd.mm.yy'));
    id := pkg_nsi.nsi_structure_insert(root_id, 283, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
    id := pkg_nsi.nsi_structure_insert(id, 285, 1, null, to_date('13.02.20', 'dd.mm.yy'), null);
end;

SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id);
-----------------------------------------------------------------------------------
316		1	1		13.02.20	
318	316	281	1		13.02.20	15.02.20
320	316	283	1		13.02.20	
322	320	285	1		13.02.20	
-----------------------------------------------------------------------------------

-- а если необходимо задать определённую сортировку
begin
    pkg_nsi.nsi_structure_ordnum(320, 1);
    pkg_nsi.nsi_structure_ordnum(318, 2);
end;

SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
-----------------------------------------------------------------------------------
316		1	1		13.02.20	
320	316	283	1	1	13.02.20	
322	320	285	1		13.02.20	
318	316	281	1	2	13.02.20	15.02.20
-----------------------------------------------------------------------------------

-- изменим периоды действия подразделения
begin
    pkg_nsi.nsi_structure_period(320, to_date('14.02.20', 'dd.mm.yy'), to_date('14.02.20', 'dd.mm.yy'));
end;

SELECT *
FROM nsi_structure
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
-----------------------------------------------------------------------------------
316		1	1		13.02.20	
320	316	283	1	1	14.02.20	14.02.20
322	320	285	1		13.02.20	
318	316	281	1	2	13.02.20	15.02.20
-----------------------------------------------------------------------------------

Так как справочники отделены от структуры, то каждый раз обращаться к организациям с учётом их отношений становится грамозко, поэтому немного упростим себе жизнь.

CREATE OR REPLACE VIEW V_NSI_ORGANIZATION AS
SELECT 
    s.nsi_structure_id, s.nsi_parent_structure_id,
    s.ordnum, s.begin_date, s.end_date,
    s.nsi_id, s.nsi_type_id, o.name, o.full_name, o.inn
FROM nsi_structure s INNER JOIN nsi_organization o
    ON (s.nsi_id = o.nsi_id)
START WITH (nsi_parent_structure_id IS NULL)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;

SELECT * FROM v_nsi_organization;
-----------------------------------------------------------------------------------
316			13.02.20		1	1	АО "Рога и копыта"	Акционерное общество "Рога и копыта"	99887766
320	316	1	14.02.20	14.02.20	283	1	Подразделение по обслуживанию копыт	Подразделение по обслуживанию копыт	2222222222
322	320		13.02.20		285	1	Отдел по изготовлению подков	Отдел по изготовлению подков	3333333333
318	316	2	13.02.20	15.02.20	281	1	Подразделение по обслуживанию рогов	Подразделение по обслуживанию рогов	1111111111
-----------------------------------------------------------------------------------

То, что мы строим дерево это замечательно, но все узлы этого дерева относятся к одной сущности, а наша задача реализовать построение отношения между разными сущностями. Это тоже не проблема, потому как структура не завязывается на какой-то определённый справочник, а работает в целом на всей системе НСИ. Для примера построим классификатор для должностей государственной гражданской службы и классификатор для должностей муниципалитета.

CREATE TABLE nsi_classifier (
    nsi_id      NUMBER(10) NOT NULL,
    code        VARCHAR2(10),
    name        VARCHAR2(200) NOT NULL,
    CONSTRAINT nsi_classifier_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_classifier_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_classifier IS 'НСИ. Классификатор';
COMMENT ON COLUMN nsi_classifier.nsi_id IS 'Ключ';
COMMENT ON COLUMN nsi_classifier.code IS 'Код';
COMMENT ON COLUMN nsi_classifier.name IS 'Наименование';


CREATE OR REPLACE TRIGGER nsi_classifier_trg_insert 
BEFORE INSERT ON nsi_classifier FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_classifier');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name, ''' || :NEW.code || ''' AS code FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_classifier_trg_update 
BEFORE UPDATE ON nsi_classifier FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_classifier');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name, ''' || :NEW.code || ''' AS code FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_classifier_trg_delete 
AFTER DELETE ON nsi_classifier FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_classifier');

    DELETE FROM nsi_history 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi_attribute 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code || ''' AS code FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

CREATE TABLE nsi_post_group (
    nsi_id   NUMBER(10) NOT NULL,
    name     VARCHAR2(50) NOT NULL,
    CONSTRAINT nsi_post_group_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_post_group_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_post_group is 'НСИ. Группа должности';
COMMENT ON COLUMN nsi_post_group.nsi_id is 'Ключ';
COMMENT ON COLUMN nsi_post_group.name is 'Наименование';


CREATE OR REPLACE TRIGGER nsi_post_group_trg_insert 
BEFORE INSERT ON nsi_post_group FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_group');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_group_trg_update 
BEFORE UPDATE ON nsi_post_group FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_group');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_group_trg_delete 
AFTER DELETE ON nsi_post_group FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_group');

    DELETE FROM nsi_history 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi_attribute 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

CREATE TABLE nsi_post_category (
    nsi_id   NUMBER(10) NOT NULL,
    name     VARCHAR2(50) NOT NULL,
    CONSTRAINT nsi_post_category_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_post_category_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_post_category is 'НСИ. Категория должности';
COMMENT ON COLUMN nsi_post_category.nsi_id is 'Ключ';
COMMENT ON COLUMN nsi_post_category.name is 'Наименование';


CREATE OR REPLACE TRIGGER nsi_post_category_trg_insert 
BEFORE INSERT ON nsi_post_category FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_category');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_category_trg_update 
BEFORE UPDATE ON nsi_post_category FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_category');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :NEW.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_category_trg_delete 
AFTER DELETE ON nsi_post_category FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post_category');

    DELETE FROM nsi_history 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi_attribute 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

CREATE TABLE nsi_post (
    nsi_id          NUMBER(10) NOT NULL,
    code_OKPDTR     VARCHAR2(10),
    name            VARCHAR2(50) NOT NULL,
    CONSTRAINT nsi_post_pk PRIMARY KEY (nsi_id),
    CONSTRAINT nsi_post_nsi_fk FOREIGN KEY (nsi_id) REFERENCES nsi (nsi_id)
);

COMMENT ON TABLE nsi_post IS 'НСИ. Должность';
COMMENT ON COLUMN nsi_post.nsi_id IS 'Ключ';
COMMENT ON COLUMN nsi_post.code_OKPDTR IS 'Код ОКПДТР';
COMMENT ON COLUMN nsi_post.name IS 'Наименование';


CREATE OR REPLACE TRIGGER nsi_post_trg_insert 
BEFORE INSERT ON nsi_post FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post');
    :NEW.nsi_id := pkg_nsi.get_nsi_id();

    INSERT INTO nsi (nsi_id, nsi_type_id, descr, create_date, modif_date, begin_date)
    VALUES (:NEW.nsi_id, v_type_id, :NEW.name, Trunc(Sysdate), Trunc(Sysdate), Trunc(Sysdate));
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_INSERT, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_trg_update 
BEFORE UPDATE ON nsi_post FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post');

    UPDATE nsi
       SET modif_date = Trunc(Sysdate)
     WHERE nsi_id = :NEW.nsi_id
       AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:NEW.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_UPDATE, v_log_descr);
END;

CREATE OR REPLACE TRIGGER nsi_post_trg_delete 
AFTER DELETE ON nsi_post FOR EACH ROW
DECLARE
    v_type_id 	nsi.nsi_type_id%TYPE;
    v_log_descr nsi_log.descr%TYPE;
    v_log_query VARCHAR(4000);
BEGIN
    v_type_id := pkg_nsi.get_type_id('nsi_post');

    DELETE FROM nsi_history 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi_attribute 
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;

    DELETE FROM nsi
    WHERE nsi_id = :OLD.nsi_id
      AND nsi_type_id = v_type_id;
    
    v_log_query := 'SELECT ''' || :OLD.name || ''' AS name, ''' || :OLD.code_OKPDTR || ''' AS code_OKPDTR FROM DUAL';
    v_log_descr := pkg_nsi.get_json(v_log_query);
    pkg_nsi.log_oper (:OLD.nsi_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
END;

Осталось только заполнить и собрать необходимые классификаторы.

INSERT INTO nsi_classifier (name) VALUES ('Классификатор должностей ГГС');
INSERT INTO nsi_classifier (name) VALUES ('Классификатор должностей муниципалитета');

INSERT INTO nsi_post_group (name) VALUES ('Высшие');
INSERT INTO nsi_post_group (name) VALUES ('Главные');
INSERT INTO nsi_post_group (name) VALUES ('Ведущие');
INSERT INTO nsi_post_group (name) VALUES ('Старшие');
INSERT INTO nsi_post_group (name) VALUES ('Младшие');

INSERT INTO nsi_post_category (name) VALUES ('Руководители');
INSERT INTO nsi_post_category (name) VALUES ('Помощники (советники)');
INSERT INTO nsi_post_category (name) VALUES ('Специалисты');
INSERT INTO nsi_post_category (name) VALUES ('Обеспечивающие специалист');

INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('24742', 'Начальник отдела');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26480', 'Советник');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('23509', 'Консультант');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('20419', 'Ведущий специалист');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26541', 'Специалист');
INSERT INTO nsi_post (code_OKPDTR, name)
VALUES ('26544', 'Специалист 2 разряда');

commit;

declare
    post_id number;
    classif_id number;
    categ_id number;
    group_id number;
begin
    -- Классификатор должностей ГГС
    classif_id := pkg_nsi.nsi_structure_insert(null, 331, 5, null, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Руководители
    categ_id := pkg_nsi.nsi_structure_insert(classif_id, 347, 4, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Высшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Главные
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Начальник отдела
    post_id := pkg_nsi.nsi_structure_insert(group_id, 335, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Ведущие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Помощники (советники)
    categ_id := pkg_nsi.nsi_structure_insert(classif_id, 349, 4, 2, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Высшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Главные
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Ведущие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Советник
    post_id := pkg_nsi.nsi_structure_insert(group_id, 337, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Специалисты
    categ_id := pkg_nsi.nsi_structure_insert(classif_id, 351, 4, 3, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Высшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 355, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Главные
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Ведущие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Ведущий специалист
    post_id := pkg_nsi.nsi_structure_insert(group_id, 341, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Старшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 361, 3, 4, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Обеспечивающие специалист
    categ_id := pkg_nsi.nsi_structure_insert(classif_id, 353, 4, 4, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Главные
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 357, 3, 1, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Ведущие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 359, 3, 2, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Старшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 361, 3, 3, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Младшие
    group_id := pkg_nsi.nsi_structure_insert(categ_id, 363, 3, 4, to_date('13.02.20', 'dd.mm.yy'), null);
    -- Специалист 2 разряда
    post_id := pkg_nsi.nsi_structure_insert(group_id, 345, 2, 1, to_date('13.02.20', 'dd.mm.yy'), null);

commit;
end;


SELECT *
FROM nsi_structure s
START WITH (nsi_id = 331)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;
----------------------------------------------------------------------------------
"NSI_STRUCTURE_ID"	"NSI_PARENT_STRUCTURE_ID"	"NSI_ID"	"NSI_TYPE_ID"	"ORDNUM"	"BEGIN_DATE"	"END_DATE"
385		331	5		13.02.20	
387	385	347	4	1	13.02.20	
389	387	355	3	1	13.02.20	
391	387	357	3	2	13.02.20	
393	391	335	2	1	13.02.20	
395	387	359	3	3	13.02.20	
397	385	349	4	2	13.02.20	
399	397	355	3	1	13.02.20	
401	397	357	3	2	13.02.20	
403	397	359	3	3	13.02.20	
405	403	337	2	1	13.02.20	
407	385	351	4	3	13.02.20	
409	407	355	3	1	13.02.20	
411	407	357	3	2	13.02.20	
413	407	359	3	3	13.02.20	
415	413	341	2	1	13.02.20	
417	407	361	3	4	13.02.20	
419	385	353	4	4	13.02.20	
421	419	357	3	1	13.02.20	
423	419	359	3	2	13.02.20	
425	419	361	3	3	13.02.20	
427	419	363	3	4	13.02.20	
429	427	345	2	1	13.02.20	
----------------------------------------------------------------------------------

Ой, как это не читабельно!

CREATE OR REPLACE VIEW V_NSI_CLASSIFIRE_GGS AS
SELECT 
    s.nsi_structure_id, s.nsi_parent_structure_id,
    s.ordnum, s.begin_date, s.end_date,
    n.nsi_id, n.nsi_type_id, n.descr
FROM nsi_structure s INNER JOIN nsi n
    ON (s.nsi_id = n.nsi_id)
START WITH (s.nsi_id = 331)
CONNECT BY (nsi_parent_structure_id = PRIOR nsi_structure_id)
ORDER SIBLINGS BY ordnum;


SELECT * FROM V_NSI_CLASSIFIRE_GGS ;
----------------------------------------------------------------------------------
"NSI_STRUCTURE_ID"	"NSI_PARENT_STRUCTURE_ID"	"NSI_ID"	"NSI_TYPE_ID"	"ORDNUM"	"BEGIN_DATE"	"END_DATE"
385			13.02.20		331	5	Классификатор должностей ГГС
387	385	1	13.02.20		347	4	Руководители
389	387	1	13.02.20		355	3	Высшие
391	387	2	13.02.20		357	3	Главные
393	391	1	13.02.20		335	2	Начальник отдела
395	387	3	13.02.20		359	3	Ведущие
397	385	2	13.02.20		349	4	Помощники (советники)
399	397	1	13.02.20		355	3	Высшие
401	397	2	13.02.20		357	3	Главные
403	397	3	13.02.20		359	3	Ведущие
405	403	1	13.02.20		337	2	Советник
407	385	3	13.02.20		351	4	Специалисты
409	407	1	13.02.20		355	3	Высшие
411	407	2	13.02.20		357	3	Главные
413	407	3	13.02.20		359	3	Ведущие
415	413	1	13.02.20		341	2	Ведущий специалист
417	407	4	13.02.20		361	3	Старшие
419	385	4	13.02.20		353	4	Обеспечивающие специалист
421	419	1	13.02.20		357	3	Главные
423	419	2	13.02.20		359	3	Ведущие
425	419	3	13.02.20		361	3	Старшие
427	419	4	13.02.20		363	3	Младшие
429	427	1	13.02.20		345	2	Специалист 2 разряда
----------------------------------------------------------------------------------

Следует не забывать, что кроме отношения включения (в том числе и древовидного), существует отношение пересечения, то есть кросс-таблиц. Здесь добавляется дополнительное условие проверки пересечения по времени.

CREATE TABLE nsi_cross (
    nsi_cross_id        NUMBER(10) NOT NULL,
    nsi_main_id         NUMBER(10) NOT NULL,
    nsi_main_type_id    NUMBER(10) NOT NULL,
    nsi_detail_id       NUMBER(10) NOT NULL,
    nsi_detail_type_id  NUMBER(10) NOT NULL,
    begin_date          DATE NOT NULL,
    end_date            DATE,
    CONSTRAINT nsi_cross_pk PRIMARY KEY (nsi_cross_id),
    CONSTRAINT nsi_cross_main_nsi_fk FOREIGN KEY (nsi_main_type_id, nsi_main_id) REFERENCES nsi (nsi_type_id, nsi_id),
    CONSTRAINT nsi_cross_detail_nsi_fk FOREIGN KEY (nsi_detail_type_id, nsi_detail_id) REFERENCES nsi (nsi_type_id, nsi_id)
);

COMMENT ON TABLE nsi_cross IS 'НСИ. Кросс-таблица справочников';
COMMENT ON COLUMN nsi_cross.nsi_cross_id IS 'Ключ';
COMMENT ON COLUMN nsi_cross.nsi_main_id IS 'Ключ основной таблицы';
COMMENT ON COLUMN nsi_cross.nsi_main_type_id IS 'Тип справочника основной таблицы';
COMMENT ON COLUMN nsi_cross.nsi_detail_id IS 'Ключ таблици детализации';
COMMENT ON COLUMN nsi_cross.nsi_detail_type_id IS 'Тип справочника таблици детализации';
COMMENT ON COLUMN nsi_cross.begin_date IS 'Дата начала действия';
COMMENT ON COLUMN nsi_cross.end_date IS 'Дата окончания действия';

    /* Реализует проверку на пересечения периода действия записи кросс-таблицы.
    *  @param p_nsi_main_id nsi_cross.nsi_main_id%TYPE - справочник основной записи
    *  @param p_nsi_main_type_id nsi_cross.nsi_main_type_id%TYPE - тип справочника основной записи
    *  @param p_nsi_detail_id nsi_cross.nsi_detail_id%TYPE - справочник записи детализации
    *  @param p_nsi_detail_type_id nsi_cross.nsi_detail_type_id%TYPE - ттип справочника записи детализации
    *  @param p_begin_date DATE - дата начала действия записи
    *  @param p_end_date DATE - дата окончания действия записи
    */
    PROCEDURE nsi_cross_check_period (
        p_nsi_cross_id          IN nsi_cross.nsi_cross_id%TYPE,
        p_begin_date            IN nsi_cross.begin_date%TYPE, 
        p_end_date              IN nsi_cross.end_date%TYPE)
    AS
        v_cnt NUMBER;
        v_nsi_main_id           nsi_cross.nsi_main_id%TYPE;
        v_nsi_main_type_id      nsi_cross.nsi_main_type_id%TYPE;
        v_nsi_detail_id         nsi_cross.nsi_detail_id%TYPE;
        v_nsi_detail_type_id    nsi_cross.nsi_detail_type_id%TYPE;
    BEGIN
        IF (p_end_date IS NOT NULL) AND (Trunc(p_begin_date) > Trunc(p_end_date)) THEN
            RAISE_APPLICATION_ERROR (NSI_ERROR_CODE, 
                '[nsi_cross_check_period] Дата начала не может быть больше даты окончания ' || Trunc(p_begin_date) || ' - ' || Trunc(p_end_date)); 
        END IF;
        
        SELECT MIN(nsi_main_id), MIN(nsi_main_type_id),
               MIN(nsi_detail_id), MIN(nsi_detail_type_id) 
          INTO v_nsi_main_id, v_nsi_main_type_id,
               v_nsi_detail_id, v_nsi_detail_type_id 
          FROM nsi_cross
         WHERE nsi_cross_id = p_nsi_cross_id;
         
        v_cnt := 0;
        
        IF (v_nsi_main_id IS NOT NULL) THEN
        
            IF (p_end_date IS NOT NULL) THEN
                SELECT COUNT(*)
                  INTO v_cnt
                  FROM nsi_cross
                 WHERE nsi_main_id = v_nsi_main_id
                   AND nsi_main_type_id = v_nsi_main_type_id
                   AND nsi_detail_id = v_nsi_detail_id
                   AND nsi_detail_type_id = v_nsi_detail_type_id
                   AND nsi_cross_id <> p_nsi_cross_id
                   AND begin_date <= Trunc(p_end_date)
                   AND ((end_date IS NULL) OR (end_date >= Trunc(p_end_date)));
            ELSE
                SELECT COUNT(*)
                  INTO v_cnt
                  FROM nsi_cross
                 WHERE nsi_main_id = v_nsi_main_id
                   AND nsi_main_type_id = v_nsi_main_type_id
                   AND nsi_detail_id = v_nsi_detail_id
                   AND nsi_detail_type_id = v_nsi_detail_type_id
                   AND nsi_cross_id <> p_nsi_cross_id
                   AND ((
                        (end_date IS NOT NULL) AND (end_date >= Trunc(p_begin_date))
                        ) OR (end_date IS NULL)
                        );
            END IF;
        END IF;

        IF (v_cnt > 0) THEN
            RAISE_APPLICATION_ERROR (NSI_ERROR_CODE, 
                '[nsi_cross_check_period] Присутствует пересечение с периодом ' || p_begin_date || ' - ' || p_end_date);    
        END IF;
    END;


    /* Реализует вставку записи.
    *  @param p_nsi_main_id nsi_cross.nsi_main_id%TYPE - справочник основной записи
    *  @param p_nsi_main_type_id nsi_cross.nsi_main_type_id%TYPE - тип справочника основной записи
    *  @param p_nsi_detail_id nsi_cross.nsi_detail_id%TYPE - справочник записи детализации
    *  @param p_nsi_detail_type_id nsi_cross.nsi_detail_type_id%TYPE - ттип справочника записи детализации
    *  @param p_begin_date DATE - дата начала действия записи
    *  @param p_end_date DATE - дата окончания действия записи
    */
    PROCEDURE nsi_cross_insert (
        p_nsi_main_id           IN nsi_cross.nsi_main_id%TYPE,
        p_nsi_main_type_id      IN nsi_cross.nsi_main_type_id%TYPE,
        p_nsi_detail_id         IN nsi_cross.nsi_detail_id%TYPE,
        p_nsi_detail_type_id    IN nsi_cross.nsi_detail_type_id%TYPE,
        p_begin_date            IN nsi_cross.begin_date%TYPE, 
        p_end_date              IN nsi_cross.end_date%TYPE)
    AS
        v_id        NUMBER;
        v_log_descr nsi_log.descr%TYPE;
        v_type_id 	nsi.nsi_type_id%TYPE;
    BEGIN
        v_id := get_nsi_id;
        v_type_id := get_type_id('nsi_cross');

        INSERT INTO nsi_cross (
            nsi_cross_id, nsi_main_id, nsi_main_type_id,
            nsi_detail_id, nsi_detail_type_id, 
            begin_date, end_date)
        VALUES (
            v_id, p_nsi_main_id, p_nsi_main_type_id,
            p_nsi_detail_id, p_nsi_detail_type_id, 
            Trunc(p_begin_date), Trunc(p_end_date));

        nsi_cross_check_period (v_id, p_begin_date, p_end_date);

        v_log_descr := '[' || get_nsi_descr(p_nsi_main_id, p_nsi_main_type_id) || ' <=> ' || get_nsi_descr(p_nsi_detail_id, p_nsi_detail_type_id) || '] ';
        v_log_descr := v_log_descr || 'Период ' || p_begin_date || ' - ' || p_end_date;
        log_oper (v_id, v_type_id, NSI_LOG_OPERNUM_INSERT, v_log_descr);
    END nsi_cross_insert;


    /* Реализует обновление периода действия записи.
    *  @param p_nsi_cross_id nsi_cross.nsi_cross_id%TYPE - ключ nsi_cross
    *  @param p_begin_date nsi_cross.begin_date%TYPE - дата начала действия
    *  @param p_end_date nsi_cross.end_date%TYPE - дата окончания действия
    */
    PROCEDURE nsi_cross_period (
        p_nsi_cross_id  IN nsi_cross.nsi_cross_id%TYPE,
        p_begin_date    IN nsi_cross.begin_date%TYPE, 
        p_end_date      IN nsi_cross.end_date%TYPE)
    AS
        v_main_id 		    nsi_cross.nsi_main_id%TYPE;
        v_main_type_id 	    nsi_cross.nsi_main_type_id%TYPE;
        v_detail_id 		nsi_cross.nsi_detail_id%TYPE;
        v_detail_type_id 	nsi_cross.nsi_detail_type_id%TYPE;
        v_type_id 	        nsi.nsi_type_id%TYPE;
        v_log_descr         nsi_log.descr%TYPE;
    BEGIN
        v_type_id := get_type_id('nsi_cross');
        
        SELECT nsi_main_id, nsi_main_type_id,
               nsi_detail_id, nsi_detail_type_id
          INTO v_main_id, v_main_type_id,
               v_detail_id, v_detail_type_id
          FROM nsi_cross
         WHERE nsi_cross_id = p_nsi_cross_id;

        nsi_cross_check_period (p_nsi_cross_id, p_begin_date, p_end_date);

        UPDATE nsi_cross
           SET begin_date = Trunc(p_begin_date),
               end_date = Trunc(p_end_date) 
         WHERE nsi_cross_id = p_nsi_cross_id;
         
        v_log_descr := '[' || get_nsi_descr(v_main_id, v_main_type_id) || ' <=> ' || get_nsi_descr(v_detail_id, v_detail_type_id) || '] ';
        v_log_descr := v_log_descr || 'Период ' || p_begin_date || ' - ' || p_end_date;
        log_oper (p_nsi_cross_id, v_type_id, NSI_LOG_OPERNUM_UPDATE, v_log_descr);
    END;


    /* Реализует удаление записи.
    *  @param p_nsi_cross_id nsi_cross.nsi_cross_id%TYPE - ключ nsi_cross
    */
    PROCEDURE nsi_cross_delete (p_nsi_cross_id IN nsi_cross.nsi_cross_id%TYPE)
    AS
        v_type_id 	nsi.nsi_type_id%TYPE;
        v_log_descr nsi_log.descr%TYPE;
    BEGIN
        v_type_id := pkg_nsi.get_type_id('nsi_cross');
        
        FOR rec IN (
            SELECT nsi_cross_id, nsi_main_id, nsi_main_type_id,
                    nsi_detail_id, nsi_detail_type_id,
                    begin_date, end_date
            FROM nsi_cross
            WHERE nsi_cross_id = p_nsi_cross_id
        )
        LOOP
            v_log_descr := '[' || pkg_nsi.get_nsi_descr(rec.nsi_main_id, rec.nsi_main_type_id) || ' <=> ' || pkg_nsi.get_nsi_descr(rec.nsi_detail_id, rec.nsi_detail_type_id) || '] ';
            v_log_descr := v_log_descr || 'Период ' || rec.begin_date || ' - ' || rec.end_date;
            pkg_nsi.log_oper (rec.nsi_cross_id, v_type_id, pkg_nsi.NSI_LOG_OPERNUM_DELETE, v_log_descr);
        END LOOP;
        
        DELETE FROM nsi_cross
        WHERE nsi_cross_id = p_nsi_cross_id;
    END;

Всё, теперь мы с уверенностью можем сказать, что закрыли первую проблему.
Конечно можно много чего пытаться прикрутить к этой системе, но я думаю, что поставленную задачу в начале статьи я выполнила, а остальное уже можно рассмотреть в процессе дискуссии.

Материал подготавливался на версии Oracle 18c, хотя нативное поддержание формата json уже присутствует в версии 12. Здесь ссылка с архивом скриптов.