create or replace view repair_basic_stock_view as
select  1  as STOCK_TYPE,  -- Указатель, что это ОФ
       BS.*,
       I.INVNUM as STOCK_NO,
  from INVNUMBERS I,
       (select E.INVNUM_CODE as STOCK_CODE,
               N.TEXT as NAME,
               M.TEXT as MODEL,
               ...
          from EQUIPMENTS         E,
               EQUIPMENTDIRECTORY N,
               EQUIPMENTDIRECTORY M
         where E.NAME_CODE = N.CODE
           and E.MODEL_CODE = M.CODE
        union all
        select A.INVNUM_CODE as STOCK_CODE,
               A.NAME,
               M.TEXT as MODEL,
               ...
          from AUTOMATICLINES     A,
               EQUIPMENTDIRECTORY M
         where A.MODEL_CODE = M.CODE) BS
 where BS.STOCK_CODE = I.CODE

-- Create table
create table REPAIR_EXTRA_STOCK
(
  CODE          NUMBER not null,
  NAME          VARCHAR2( 200 ) not null,
  MODEL         VARCHAR2( 50 ),
  ...
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table REPAIR_EXTRA_STOCK
  add constraint PK_REPAIR_EXTRA_STOCK primary key (CODE)
  using index 
alter table REPAIR_EXTRA_STOCK
  add constraint FK_REPAIR_EXTRA_STOCK_BASIC foreign key (PARENT_CODE)  -- НОФ могут наследоваться от ОФ (практически - быть узлами ОФ, например состав автоматической линии)
  references INVNUMBERS (CODE) on delete cascade;
...

create or replace view repair_extra_stock_view as
select  2  as STOCK_TYPE,
       ES.CODE as STOCK_CODE,
       ES.NAME,
       ES.MODEL,
       DECODE(ES.PARENT_CODE, null, ES.STOCK_NO, BS.STOCK_NO) as STOCK_NO,
       ...
  from REPAIR_EXTRA_STOCK      ES,
       REPAIR_BASIC_STOCK_VIEW BS
 where ES.PARENT_CODE = BS.STOCK_CODE(+)

select RI.*,
       ...
  from (
select RI.CODE,
       BS.STOCK_TYPE,
       BS.STOCK_CODE,
       BS.NAME,
       BS.MODEL,
       ...
  from REPAIR_INVENTORY        RI,
       REPAIR_BASIC_STOCK_VIEW BS
 where RI.BASIC_STOCK_CODE = BS.STOCK_CODE
union all
select RI.CODE,
       ES.STOCK_TYPE,
       ES.STOCK_CODE,
       ES.NAME,
       ES.MODEL,
       ...
  from REPAIR_INVENTORY        RI,
       REPAIR_EXTRA_STOCK_VIEW ES
 where RI.EXTRA_STOCK_CODE = ES.STOCK_CODE
) RI,
       ...

-- Create table
create table REPAIR_INDEX
(
  CODE             NUMBER not null,
  BASIC_STOCK_CODE NUMBER,
  EXTRA_STOCK_CODE NUMBER
)
alter table REPAIR_INDEX
  add constraint PK_REPAIR_INDEX primary key (CODE)
  using index 
alter table REPAIR_INDEX
  add constraint PK_REPAIR_INDEX_BASIC foreign key (BASIC_STOCK_CODE)
  references INVNUMBERS (CODE) on delete cascade;
alter table REPAIR_INDEX
  add constraint PK_REPAIR_INDEX_EXTRA foreign key (EXTRA_STOCK_CODE)
  references REPAIR_EXTRA_STOCK (CODE) on delete cascade;
-- Create/Recreate indexes 
create index FK_REPAIR_INDEX_STOCK on REPAIR_INDEX (BASIC_STOCK_CODE, EXTRA_STOCK_CODE)

create or replace view repair_index_view as
select RI.CODE,
       BS.STOCK_TYPE,
       BS.STOCK_CODE,
       BS.NAME,
       BS.MODEL,
       ...
  from REPAIR_INDEX            RI,
       REPAIR_BASIC_STOCK_VIEW BS
 where RI.BASIC_STOCK_CODE = BS.STOCK_CODE
union all
select RI.CODE,
       ES.STOCK_TYPE,
       ES.STOCK_CODE,
       ES.NAME,
       ES.MODEL,
       ...
  from REPAIR_INDEX            RI,
       REPAIR_EXTRA_STOCK_VIEW ES
 where RI.EXTRA_STOCK_CODE = ES.STOCK_CODE;

create or replace view repair_inventory_view as -- Вьюшка журнала ремонтов
select RI.*,
       ...
  from (select RI.*,
               RX.*
          from REPAIR_INVENTORY  RI,
               REPAIR_INDEX_VIEW RX
         where RI.INDEX_CODE = RX.CODE) RI,
       ...