--Conversion script

update i_uniqueitem i
set    i.stockroomcode = null
where  i.stockroomcode is not null
and    not exists (select 1 from stockroom st where st.code = i.stockroomcode);

commit;

-- Create/Recreate primary, unique and foreign key constraints 
alter table STOCKROOM
  add constraint STOCKROOMCODE unique (CODE);
alter table I_UNIQUEITEM
  add constraint FK_I_UNIQUEITEM_STOCKROOM foreign key (STOCKROOMCODE)
  references stockroom (CODE);

-- Create/Recreate check constraints 
alter table I_PRODUCT
  add constraint I_PROD_ORPACK_LABEL
  check ((PACKTYPE <> 2) OR (PACKTYPE=2 AND ASSEMBLYLAYOUT IS NOT NULL));

-- Add/modify columns 
alter table I_PRODUCT add MINACTIVESTOCKLEVEL number default 0 not null;

-- Add/modify columns 
alter table I_PACKCONTENT add UNIQUELYCODED varchar2(1) default 'N' not null;
-- Create/Recreate check constraints 
alter table I_PACKCONTENT
  add constraint CHK_I_PACKCONTENT_UNIQUECODED
  check (UNIQUELYCODED IN ('Y', 'N'));
  
-- Add/modify columns 
alter table I_UNIQUEORSTERILE add CARTTYPE varchar2(6) not null;
alter table I_UNIQUEORSTERILE add CARTNUMBER number not null; 