     
      /*
      CHANGE REPORT for Table STATUSQUOHEADER
          - Adding column SUBTYPE
      ACTION is ALTER or modify Table STATUSQUOHEADER
      */

ALTER TABLE STATUSQUOHEADER ADD (SUBTYPE              NUMBER DEFAULT 0 NOT NULL
                                   CHECK (SUBTYPE IN ('0', '1', '2 ')));


insert into process (process_id, description, active)
        values (71, 'Reference SQ items in circulation', 'Y');
insert into process (process_id, description, active)
        values (72, 'Reference SQ mats in circulation', 'Y');


insert into automaticflag (automaticflag_id, event, systemuser_id, timestamp, selectable)
        values (17,'Product contract ended for customer',getminsystemuser(), getcurrentdatetime(),'Y');
insert into automaticflag (automaticflag_id, event, systemuser_id, timestamp, selectable)
        values (18, 'Customer no longer active', getminsystemuser(), getcurrentdatetime(), 'Y');


     
ALTER TABLE BUSINESSUNIT ADD (DEFAULTBANKINSTITUTION_ID   NUMBER NULL);

CREATE INDEX XIF7BUSINESSUNIT ON BUSINESSUNIT
(
       DEFAULTBANKINSTITUTION_ID             ASC
);

alter table BUSINESSUNIT
  add constraint BUSINESSUNIT_BANKINSTITUTION foreign key (DEFAULTBANKINSTITUTION_ID)
  references BANKINSTITUTION (BANKINSTITUTION_ID);

update businessunit b
set b.defaultbankinstitution_id = (select bi.bankinstitution_id from bankinstitution bi where bi.laundrybankaccount='Y' and bi.defaultlaundrybank='Y')
where b.plant_id in (select s.plant_id from systemsetting s where s.name = 'BANKINSTITUTION' and s.value='Y');


create sequence SEQ_WEBSERVICELOG
start with 1 increment by 1;


create table WEBSERVICELOG
(
  WEBSERVICELOG_ID NUMBER not null,
  REQUESTTIMESTAMP DATE not null,
  MESSAGEID        VARCHAR2(20) not null,
  TRANSACTIONTYPE  VARCHAR2(10) not null,
  FUNCTION         VARCHAR2(40) not null,
  APPLICATION      VARCHAR2(20) not null,
  CUSTOMER         VARCHAR2(20) not null,
  WEBUSER          VARCHAR2(20) not null,
  PASSWORD         VARCHAR2(80) not null,
  DATA             VARCHAR2(3000) not null,
  RETURNCODE       VARCHAR2(10) not null,
  CODE             VARCHAR2(20),
  MESSAGE          VARCHAR2(1000)
);

alter table WEBSERVICELOG
  add constraint XPKWEBSERVICELOG primary key (WEBSERVICELOG_ID) using index;
 

create index XIF734WEBSERVICELOG on WEBSERVICELOG (APPLICATION, MESSAGEID);

create index XIF735WEBSERVICELOG on WEBSERVICELOG (APPLICATION, REQUESTTIMESTAMP);