--502807
--Extra I-table checks, to improve the conversion.

--1. New LOCKER/GARMENTDISPENSER check on I_WEARER
--correct wrong entries in I_WEARER so constraint can be added
update i_wearer i
set    i.locker = null
where  i.locker is not null and i.garmentdispensercode is not null;

commit;

-- Create check constraints on I_WEARER
alter table I_WEARER
  add constraint CHK_I_WEARER
  check ((LOCKER is not null and GARMENTDISPENSERCODE is null ) or 
         (LOCKER is null and GARMENTDISPENSERCODE is not null ) or
         (LOCKER is null and GARMENTDISPENSERCODE is null ));

--2. New TOM check on I_UNIQUEPOOL;
--delete to make check constraint always work!
delete from I_UNIQUEPOOL;

-- Add columns 
alter table I_UNIQUEPOOL add TYPEOFMERCHANDISE number default 2 not null;

-- Recreate foreign key constraints now with TOM 
alter table I_UNIQUEPOOL
  drop constraint I_UNIQUEPOOL_PRODUCTCODE;
alter table I_UNIQUEPOOL
  add constraint I_UNIQUEPOOL_PRODUCTCODE foreign key (PRODUCTCODE, TYPEOFMERCHANDISE)
  references I_PRODUCT (CODE, TYPEOFMERCHANDISE);


-- Order 502807

-- I_UNIQUEPOOL
-- Add comments to the columns 
comment on column I_UNIQUEPOOL.TYPEOFMERCHANDISE
  is 'TYPEOFMERCHANDISE Can be 1 or 2 only';
-- Create check constraints 
alter table I_UNIQUEPOOL
  add constraint I_UNIQUEPOOL_TYPEOFMERCHANDISE
  check (TYPEOFMERCHANDISE IN (1,2));

-- I_UNIQUEITEM  
-- Add comments to the columns 
comment on column I_UNIQUEITEM.TYPEOFMERCHANDISE
  is 'TYPEOFMERCHANDISE Can 1 or 2, DEFAULT=2, 1=FOR POOL';  
-- Create check constraints 
alter table I_UNIQUEITEM
  add constraint I_UNIQUEITEM_TYPEOFMERCHANDISE
  check (TYPEOFMERCHANDISE=2 OR (TYPEOFMERCHANDISE=1 AND POOLITEM=1));
-- Add comments to the columns 
comment on column I_UNIQUEITEM.POOLITEM
  is '0 = Stock
1 = Pool
2 = Non Pool
3 = OR Pack';  
-- Create/Recreate check constraints 
alter table I_UNIQUEITEM
  add constraint I_UNIQUEITEM_POOLITEM
  check (POOLITEM IN (0,1,2,3));  
  
-- I_CUSTOMER  
-- Add column
alter table I_CUSTOMER add SPLITINVOICESPERACTIVITY varchar2(1) default 'N' not null;
-- Add comments to the column
comment on column I_CUSTOMER.SPLITINVOICESPERACTIVITY
  is 'Possible values ''N'',''Y''';  
-- Create/Recreate check constraints 
alter table I_CUSTOMER
  add constraint I_CUST_SPLITINVOICES
  check (SPLITINVOICES IN (0,1,2,3));
alter table I_CUSTOMER
  drop constraint I_CUST_SPLITINVOICESPERACT;
alter table I_CUSTOMER
  add constraint I_CUST_SPLITINVOICESPERACT
  check (SPLITINVOICESPERACTIVITY IN ('Y', 'N'));
