--Order 20012683.70

--GARMENTDISPENSERMESSAGE table

CREATE SEQUENCE SEQ_GARMENTDISPENSERMESSAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GARMENTDISPENSERMESSAGE
(
	GARMENTDISPENSERMESSAGE_ID NUMBER NOT NULL ,
	INTERFACE            NUMBER(2) NOT NULL  CONSTRAINT  Interface_valid1_1 CHECK (INTERFACE IN (1, 2, 3, 4, 5)),
	CODE                 VARCHAR2(6) NOT NULL ,
	MESSAGE              VARCHAR2(30) NOT NULL ,
	DESCRIPTION          VARCHAR2(300) NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN GARMENTDISPENSERMESSAGE.INTERFACE IS '1 = LCT KUA
2 = Kannegiesser
3 = UNIMAT
4 = UNIMAT - Check in
5 = LCT New';

CREATE UNIQUE INDEX XPKGARMENTDISPENSERMESSAGE ON GARMENTDISPENSERMESSAGE
(GARMENTDISPENSERMESSAGE_ID   ASC);

COMMENT ON COLUMN CUSTOMERGARMENTDISPENSER.INTERFACE IS '1 = LCT KUA
2 = Kannegiesser
3 = UNIMAT
4 = UNIMAT - Check in
5 = ABS Standard';

ALTER TABLE GARMENTDISPENSERMESSAGE
ADD CONSTRAINT  XPKGARMENTDISPENSERMESSAGE PRIMARY KEY (GARMENTDISPENSERMESSAGE_ID);

CREATE UNIQUE INDEX XAK1GARMENTDISPENSERMESSAGE ON GARMENTDISPENSERMESSAGE
(INTERFACE   ASC,CODE   ASC);


--GDTRANSACTION table

ALTER TABLE GDTRANSACTION
	ADD (PRODUCTTRANSACTIONDATE DATE NULL);

ALTER TABLE GDTRANSACTION
	ADD (PRODUCTDESCRIPTION VARCHAR2(40) NULL);

ALTER TABLE GDTRANSACTION
	ADD (COSTCENTERCODE VARCHAR2(6) NULL);

ALTER TABLE GDTRANSACTION
	ADD (CUSTOMEROWNED VARCHAR2(1) NULL CONSTRAINT YN_VALIDATIONFIELD208 CHECK (CUSTOMEROWNED IN ('Y', 'N')));

ALTER TABLE GDTRANSACTION
	ADD (PRIORITY NUMBER NULL);

ALTER TABLE GDTRANSACTION
	ADD (GARMENTDISPENSERMESSAGE_ID NUMBER NULL);

ALTER TABLE GDTRANSACTION
	ADD (FIELDINDEX NUMBER(3)  DEFAULT 0 NOT NULL);

ALTER TABLE GDTRANSACTION
	ADD (REMARK CLOB NULL);

COMMENT ON COLUMN GDTRANSACTION.GDTRANSCTIONTYPE IS '10 = New Wearer
11 = Change wearer
12 = Delete wearer
13 = Disconnect Wearer
20 = Issue garment
21 = Return Stock
22 = Rag
23 = Disconect Wearer''s garment
30 = New product in contract
32 = delete product from contract
33 = New size per product
34 = Delete size
40 = new function or new product in function
42 = delete function
50 = Add product in wearer inventory
52 = Delete product in wearer inventory
60 = Add product to customer
62 = Delete product from customer
90 = Outscan
92 = Inscan';

COMMENT ON COLUMN GDTRANSACTION.PRODUCTTRANSACTIONDATE IS 'Date from which the product is added or deleted.';

COMMENT ON COLUMN GDTRANSACTION.PRIORITY IS '0 = OK
1 = Warning
2 = Error';

COMMENT ON COLUMN GDTRANSACTION.FIELDINDEX IS 'This attribute can be used to specify the field number for a warning or error.';

CREATE INDEX XIF4GDTRANSACTION ON GDTRANSACTION
(GARMENTDISPENSERMESSAGE_ID   ASC);

ALTER TABLE GDTRANSACTION
ADD CONSTRAINT GDTRANSACTION_GDTRANSMESSAGE FOREIGN KEY (GARMENTDISPENSERMESSAGE_ID) REFERENCES GARMENTDISPENSERMESSAGE (GARMENTDISPENSERMESSAGE_ID) ON DELETE SET NULL;


--insert into AUTOMATICFLAG table

insert into automaticflag
          (automaticflag_id,
           event,
           systemuser_id,
           timestamp,
           selectable,
           deletable,
           transferable,
           flagtype)
        values
          (23,
           'Garment Dispenser',
           getminsystemuser(),
           sysdate,
           'Y',
           'Y',
           'Y',
            1);
commit;

--insert into GARMENTDISPENSERMESSAGE table

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'G1',
       'Unknown error',
       'General Error Message, when generated by an LCT Application then contact LCT (check the log files of the application for more information)',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'G2',
       'IO Error',
       'file IO Error',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'F1',
       'Field Count Error',
       null,
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'F2',
       'Field Format Error',
       'e.g. date format, or string when a number is expected, and so on',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'F3',
       'Field Length Error',
       null,
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'F4',
       'Field Value Error',
       'e.g. smaller than the minimum or greater than  the maximum allowed value',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'F5',
       'Invalid Characters',
       null,
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'T1',
       'Mandatory Field Missing',
       null,
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'T2',
       'In use',
       'In use or already defined',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'T3',
       'Not defined',
       'Unknown field value (not defined)',
       getminsystemuser(),
       sysdate
from dual;       
commit;

insert into garmentdispensermessage
select seq_garmentdispensermessage.nextval,
       5,
       'T4',
       'Type Error',
       'e.g. attempt to assign a PG garment to a Pool Wearer',
       getminsystemuser(),
       sysdate
from dual;       
commit;