/*order 502356*/
/*From PVD*/

execute pack_import.cfbdelete_itables;


declare
begin
  execute immediate 'alter table I_CUSTOMER modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_CUSTOMER add constraint I_CUST_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_CONTACTCALLS modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_CONTACTCALLS add constraint I_CONTACTCALLS_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_INVOICEHEADER modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_INVOICEHEADER add constraint I_INVOICEHEADER_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_MISCCHARGE modify BUSINESSUNITNUMBER varchar2(6)';
  execute immediate 'alter table I_MISCCHARGE add constraint I_MISCCHARGE_BU
                     check (TRIM(UPPER(BUSINESSUNITNUMBER))=BUSINESSUNITNUMBER)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_ARTRANSACTION modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_ARTRANSACTION add constraint I_ARTRANSACTION_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_SOILTICKET modify BUSINESSUNITNUMBER varchar2(6)';
  execute immediate 'alter table I_SOILTICKET add constraint I_SOILTICKET_BU
                     check (TRIM(UPPER(BUSINESSUNITNUMBER))=BUSINESSUNITNUMBER)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_SOILREASONABILITY modify BUSINESSUNITNUMBER varchar2(6)';
  execute immediate 'alter table I_SOILREASONABILITY add constraint I_SOILREASONABILITY_BU
                     check (TRIM(UPPER(BUSINESSUNITNUMBER))=BUSINESSUNITNUMBER)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_DELIVERYHISTORY modify BUSINESSUNITNUMBER varchar2(6)';
  execute immediate 'alter table I_DELIVERYHISTORY add constraint I_DELIVERYHISTORY_BU
                     check (TRIM(UPPER(BUSINESSUNITNUMBER))=BUSINESSUNITNUMBER)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_UNIQUEPOOL drop constraint I_UNQUEPOOL_ACTIVESTOCK';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION drop constraint PKI_ACTIVESTOCKLLOCATION';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION drop constraint UN_I_ACTIVESTOCKLLOCATION';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION add constraint I_ACTIVESTKLOC_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
  execute immediate 'alter table I_UNIQUEPOOL modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_UNIQUEPOOL add constraint I_UNIQUEPOOL_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION add constraint PKI_ACTIVESTOCKLLOCATION 
                     primary key (BUSINESSUNIT, SECTION, LOCATION)';
  execute immediate 'alter table I_UNIQUEPOOL add constraint I_UNQUEPOOL_ACTIVESTOCK foreign key (BUSINESSUNIT, SECTION, LOCATION)
                     references I_ACTIVESTOCKLOCATION (BUSINESSUNIT, SECTION, LOCATION)';
  execute immediate 'alter table I_ACTIVESTOCKLOCATION add constraint UN_I_ACTIVESTOCKLLOCATION unique (BUSINESSUNIT, UNIQUEID)';
exception
  when others then
    null;
end;
/

declare
begin
  execute immediate 'alter table I_WORKORDER modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_WORKORDER add constraint I_WORKORDER_BU
                     check (TRIM(UPPER(BUSINESSUNIT))=BUSINESSUNIT)';
exception
  when others then
    null;
end;
/

alter table I_CUSTOMER add TAXEXEMPTCODE varchar2(18);
comment on column I_CUSTOMER.TAXEXEMPTCODE
  is 'Optional US field for Tax Exempt customers';

alter table I_PRODUCTLISTPERCUSTOMER add EMBLEMTEMPLATE varchar2(6);
comment on column I_PRODUCTLISTPERCUSTOMER.EMBLEMTEMPLATE
  is 'Optional default emblem template.';

alter table I_WEARERINVENTORY add EMBLEMTEMPLATE varchar2(6);
comment on column I_WEARERINVENTORY.EMBLEMTEMPLATE
  is 'Optional emblem template';

alter table I_DELIVERYDAYS add ROUTEMANAGER number;
comment on column I_DELIVERYDAYS.ROUTEMANAGER
  is 'Optional ''employee'' thats the route manager';
alter table I_DELIVERYDAYS add ROUTEMANAGERNAME VARCHAR2(40);

alter table I_DELIVERYDAYS add DISTRICT varchar2(6);
comment on column I_DELIVERYDAYS.DISTRICT
  is 'Optional district thats linked to the route';
alter table I_DELIVERYDAYS add DISTRICTDESCRIPTION   VARCHAR2(30);

alter table I_SPECIALCHARGESPERCUSTOMER add OVERRULED VARCHAR2(1) default 'Y' not null;
comment on column I_SPECIALCHARGESPERCUSTOMER.OVERRULED
  is 'Customer specific Y/N, default Y';

declare
begin
  --drop pk and fk's to be able to change BU 
  execute immediate 'alter table I_UNIQUEITEMFLAGS drop constraint FK_I_UNIQUEITEMFLAGS';
  execute immediate 'alter table I_UNIQUEITEMFLAGS drop constraint PK_I_UNIQUEITEMFLAGS';
  execute immediate 'alter table I_SCAN drop constraint FK_I_SCAN_UNIQUEITEM';
  execute immediate 'alter table I_SCAN drop constraint PK_I_SCAN';
  execute immediate 'alter table I_UNIQUEITEM drop constraint PK_I_UNIQUEITEM';
exception
  when others then
    null;
end;
/

declare
begin
  --change the bu's 
  execute immediate 'alter table I_UNIQUEITEM modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_UNIQUEITEMFLAGS modify BUSINESSUNIT varchar2(6)';
  execute immediate 'alter table I_SCAN modify BUSINESSUNIT varchar2(6)';
exception
  when others then
    null;
end;
/

declare
begin
  --add the checks
  execute immediate 'alter table I_UNIQUEITEM add constraint I_UNIQUEITEM_BU check (BUSINESSUNIT=UPPER(TRIM(BUSINESSUNIT)))';
  execute immediate 'alter table I_UNIQUEITEM add constraint I_UNIQUEITEMFLAGS_BU check (BUSINESSUNIT=UPPER(TRIM(BUSINESSUNIT)))';
  execute immediate 'alter table I_UNIQUEITEM add constraint I_SCAN_BU check (BUSINESSUNIT=UPPER(TRIM(BUSINESSUNIT)))';
exception
  when others then
    null;
end;
/

declare
begin
  --add again the fk's and pk
  execute immediate 'alter table I_UNIQUEITEM add constraint PK_I_UNIQUEITEM primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)';
  execute immediate 'alter table I_UNIQUEITEMFLAGS add constraint PK_I_UNIQUEITEMFLAGS primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER, FLAGCODE)';
  execute immediate 'alter table I_UNIQUEITEMFLAGS add constraint FK_I_UNIQUEITEMFLAGS foreign key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER) references I_UNIQUEITEM (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)';
  execute immediate 'alter table I_SCAN add constraint PK_I_SCAN primary key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER, STAY, SCANDATETIME)';
  execute immediate 'alter table I_SCAN add constraint FK_I_SCAN_UNIQUEITEM foreign key (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)  references I_UNIQUEITEM (BUSINESSUNIT, PRIMARYID, IDCODESEQUENCENUMBER)';
exception
  when others then
    null;
end;
/
