--Order 20010994.160


------------------PRODUCTWASHINGPROCESS----------------

CREATE SEQUENCE SEQ_PRODUCTWASHINGPROCESS
START WITH 1
INCREMENT BY 1;


CREATE TABLE PRODUCTWASHINGPROCESS
(
	PRODUCTWASHINGPROCESS_ID NUMBER NOT NULL,
	PRODUCT_ID           NUMBER NOT NULL ,
	WASHINGPROCESS_ID    NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE PRODUCTWASHINGPROCESS
ADD CONSTRAINT  XPKPRODUCTWASHINGPROCESS PRIMARY KEY (PRODUCTWASHINGPROCESS_ID) USING INDEX TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1PRODUCTWASHINGPROCESS ON PRODUCTWASHINGPROCESS
(PRODUCT_ID   ASC,WASHINGPROCESS_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF1PRODUCTWASHINGPROCESS ON PRODUCTWASHINGPROCESS
(PRODUCT_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2PRODUCTWASHINGPROCESS ON PRODUCTWASHINGPROCESS
(WASHINGPROCESS_ID   ASC) TABLESPACE INDX;


ALTER TABLE PRODUCTWASHINGPROCESS
ADD CONSTRAINT PRODUCTWASHINGPROCESS_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;



ALTER TABLE PRODUCTWASHINGPROCESS
ADD CONSTRAINT PRODUCTWASHINGPROCESS_WASHINGP FOREIGN KEY (WASHINGPROCESS_ID) REFERENCES WASHINGPROCESS (WASHINGPROCESS_ID);


-------------------CUSTOMERINTERNETOPTIONS-------------

ALTER TABLE CUSTOMERINTERNETOPTIONS
	ADD (GARMENTSWEBPORTAL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField42 CHECK (GARMENTSWEBPORTAL IN ('Y', 'N')));



ALTER TABLE CUSTOMERINTERNETOPTIONS
	ADD (RESIDENTIALWEBPORTAL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField43 CHECK (RESIDENTIALWEBPORTAL IN ('Y', 'N')));



ALTER TABLE CUSTOMERINTERNETOPTIONS
	ADD (STOCKCOUNTWEBPORTAL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField44 CHECK (STOCKCOUNTWEBPORTAL IN ('Y', 'N')));



ALTER TABLE CUSTOMERINTERNETOPTIONS
	ADD (ORDERBYDATEWEBPORTAL VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField45 CHECK (ORDERBYDATEWEBPORTAL IN ('Y', 'N')));



COMMENT ON COLUMN CUSTOMERINTERNETOPTIONS.ORDERGARMENTS IS 'Note: this will only be used for filtering in the stock count / order screen.';



COMMENT ON COLUMN CUSTOMERINTERNETOPTIONS.ORDERLINEN IS 'Note: this will only be used for filtering in the stock count / order screen.';


----------update CUSTOMERINTERNETOPTIONS------------

UPDATE CUSTOMERINTERNETOPTIONS CIO
   SET CIO.GARMENTSWEBPORTAL = CIO.ORDERGARMENTS;
commit;

UPDATE CUSTOMERINTERNETOPTIONS CIO
   SET CIO.STOCKCOUNTWEBPORTAL = CIO.ORDERLINEN;
commit;

--------------------CUSTOMERRESIDENTIALSETTINGS-----------

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (WEBPORTALBILLING NUMBER  DEFAULT 0 NOT NULL CONSTRAINT WebPortalBilling_valid1 CHECK (WEBPORTALBILLING IN (0, 1)));



ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (WEBPORTALDELAYEDPIECES NUMBER  DEFAULT 0 NOT NULL CONSTRAINT WebPortalDelayedPieces_valid1 CHECK (WEBPORTALDELAYEDPIECES IN (0, 1)));



ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (WEBPORTALLABELS NUMBER  DEFAULT 0 NOT NULL CONSTRAINT WebPortalLabels_valid1 CHECK (WEBPORTALLABELS IN (0, 1, 2)));



ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
	ADD (WEBPORTALLABELPRINTER_ID NUMBER NULL);


COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.WEBPORTALBILLING IS '0 = Not applicable / no billing tab
1 = Billing tab in web portal';

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.WEBPORTALDELAYEDPIECES IS '0 = Not applicable / No delayed pieces tab
1 = Delayed pieces tab in web portal';


COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.WEBPORTALLABELS IS '0 = Laundry prints labels
1 = Customer prints labels via own label printer on web portal
2 = Customer uses own preprinted labels';


CREATE  INDEX XIF6CUSTOMERRESIDENTIALSETTING ON CUSTOMERRESIDENTIALSETTINGS
(WEBPORTALLABELPRINTER_ID   ASC) TABLESPACE INDX;


ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
ADD CONSTRAINT CUSTOMERRESIDENTIALSETTINGS_LP FOREIGN KEY (WEBPORTALLABELPRINTER_ID) REFERENCES LABELPRINTER (LABELPRINTER_ID);

----------------------insert into WEBEVENTTYPE--------------------

insert into webeventtype
  select 100, 'Add Room', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 101, 'Edit Room', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 102, 'Remove Room', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 103, 'Add Client', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 104, 'Edit Client', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 105, 'Quit Client', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 106, 'Add Contact Person', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 107, 'Edit Contact Person', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 108, 'Remove Contact Person', getminsystemuser(), sysdate
    from dual;
insert into webeventtype
  select 109, 'Print Labels', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 110, 'Assign Labels', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 111, 'Add Flag', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 112, 'Edit Flag', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 113, 'Remove Flag', getminsystemuser(), sysdate from dual;
insert into webeventtype
  select 114, 'Take out of circulation', getminsystemuser(), sysdate
    from dual;

----------------------insert into PRODUCTWASHINGPROCESS------------

INSERT INTO PRODUCTWASHINGPROCESS
  SELECT SEQ_PRODUCTWASHINGPROCESS.NEXTVAL, P.PRODUCT_ID, WP.WASHINGPROCESS_ID, 0, sysdate
    FROM PRODUCT P, WASHINGPROCESS WP
   WHERE P.TYPEOFMERCHANDISE = 5
     AND P.WASHINGPROCESS_ID = WP.WASHINGPROCESS_ID;
