--UPDATE FROM 8.6.2.1 TO 8.6.2.3


DROP TABLE VERSION_8_6_2_1;



/*  VERSION_8_6_2_3  */
CREATE TABLE VERSION_8_6_2_3 (
	 DUMMY	NUMBER	    NULL
)
;

DROP INDEX YRES_RESSTATUS;

DROP INDEX ZFAC_ACTVALIDUNTILNULL;

DROP INDEX ZFAC_ACTVALIDFROMNULL;

DROP INDEX YRES_ACTDEPTIMENULL;

DROP INDEX YRES_ACTARRTIMENULL;

ALTER TABLE WPCA ADD (WPCA_IFCBUSI_LAST_TIME DATE);

COMMENT ON COLUMN WPCA.WPCA_IFCBUSI_LAST_TIME IS 'Timestamp, when ifcbusi loaded last time in this workstation';

ALTER TABLE WPCA ADD (WPCA_IFCBUSI_LAST_VERSION VARCHAR2(10));

COMMENT ON COLUMN WPCA.WPCA_IFCBUSI_LAST_VERSION IS 'Version Number of the last IfcBusi dll';

ALTER TABLE WPCA ADD (WPCA_BarcodeReaderPort NUMBER(1));

COMMENT ON COLUMN WPCA.WPCA_BarcodeReaderPort IS 'Barcode Reader COM Port number';

ALTER TABLE WPCA ADD (WPCA_BarcodeReaderBaud NUMBER(6));

COMMENT ON COLUMN WPCA.WPCA_BarcodeReaderBaud IS 'Barcode Reader Baud, 1=300 2=1200 3=2400 4=4800 5=9600 6=19200 7=38400 8=57600';

CREATE TABLE CART
(
  CART_ID           NUMBER(30)             NOT NULL,
  CART_NAME         VARCHAR2(100)          NOT NULL,
  CART_TYPE         NUMBER(3),
  CART_PRICE        NUMBER(30,4),
  CART_COUPONTEXT   VARCHAR2(200),
  CART_COUPONDESCR  VARCHAR2(500),
  CART_PAYMENT_ZDCO_ID NUMBER(30),
  CART_DISPLORDER NUMBER(3,0),
  constraint CART_PK primary key (CART_ID) using index tablespace v8index  
)
TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_CART INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE CART IS 'Certificate articles';

COMMENT ON COLUMN CART.CART_ID IS 'Article ID';

COMMENT ON COLUMN CART.CART_NAME IS 'Article name';

COMMENT ON COLUMN CART.CART_TYPE IS 'Article type (depends on redemption system)';

COMMENT ON COLUMN CART.CART_PRICE IS 'Article price';

COMMENT ON COLUMN CART.CART_COUPONTEXT IS 'Coupon text';

COMMENT ON COLUMN CART.CART_COUPONDESCR IS 'Coupon description';

COMMENT ON COLUMN CART.CART_PAYMENT_ZDCO_ID IS 'Department code for payments';

CREATE TABLE CRVD
(
  CRVD_ID           NUMBER(30)          NOT NULL,
  CRVD_CART_ID      NUMBER(30)          NOT NULL,
  CRVD_ZDCO_ID      NUMBER(30)          NOT NULL,
  constraint CRVD_PK primary key (CRVD_ID) using index tablespace v8index  
)
TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_CRVD INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE CRVD IS 'Certificate''s revenue departments';

COMMENT ON COLUMN CRVD.CRVD_ID IS 'Primary key';

COMMENT ON COLUMN CRVD.CRVD_CART_ID IS 'Link to the certificate';

COMMENT ON COLUMN CRVD.CRVD_ZDCO_ID IS 'Link to the department';

CREATE TABLE COUP
(
  COUP_ID             NUMBER(30)            NOT NULL,
  COUP_NUMBER         VARCHAR2(30),
  COUP_DESCRIPTION    VARCHAR2(30),
  COUP_TEXT           VARCHAR2(1000),
  COUP_ISSUE          DATE NOT NULL,
  COUP_VALIDFROM      DATE,
  COUP_VALIDUNTIL     DATE,
  COUP_CLEARINGDATE   DATE,
  COUP_XCMS_ID        NUMBER(30),
  COUP_CART_ID        NUMBER(30),
  COUP_AMOUNT         NUMBER(30,4) NOT NULL,
  COUP_AMOUNTREDEEMED NUMBER(30,4),
  COUP_IsValueCoupon  NUMBER(1),
  constraint COUP_PK primary key (COUP_ID) using index tablespace v8index  
)
TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_COUP INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

CREATE UNIQUE INDEX COUP_U01 ON COUP (COUP_NUMBER) TABLESPACE V8INDEX;

COMMENT ON TABLE COUP IS 'Gift coupon info';

COMMENT ON COLUMN COUP.COUP_ID IS 'Coupon ID';

COMMENT ON COLUMN COUP.COUP_NUMBER IS 'Complete number of the gift certificate ';

COMMENT ON COLUMN COUP.COUP_DESCRIPTION IS 'gift certificate description';

COMMENT ON COLUMN COUP.COUP_TEXT IS 'Text for the gift certificate ';

COMMENT ON COLUMN COUP.COUP_ISSUE IS 'Date of issue of the gift certificate ';

COMMENT ON COLUMN COUP.COUP_VALIDFROM IS 'Start of date where the gift certificate is valid ';

COMMENT ON COLUMN COUP.COUP_VALIDUNTIL IS 'End of date where the gift certificate is valid ';

COMMENT ON COLUMN COUP.COUP_CLEARINGDATE IS 'Date of clearing ';

COMMENT ON COLUMN COUP.COUP_XCMS_ID IS 'Guest Link';

COMMENT ON COLUMN COUP.COUP_CART_ID IS 'Article Link';

COMMENT ON COLUMN COUP.COUP_AMOUNT IS 'Amount of gift certificates ';

COMMENT ON COLUMN COUP.COUP_AMOUNTREDEEMED IS 'Amount which has been redeemed';

CREATE TABLE CPOS
(
  CPOS_ID           NUMBER(30)          NOT NULL,
  CPOS_COUP_ID      NUMBER(30)          NOT NULL,
  CPOS_ZPOS_ID      NUMBER(30)          NOT NULL,
  constraint CPOS_PK primary key (CPOS_ID) using index tablespace v8index  
)
TABLESPACE V8MAIN;

CREATE SEQUENCE SEQ_CPOS INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE CPOS IS 'List of postings linked to the voucher';

COMMENT ON COLUMN CPOS.CPOS_ID IS 'Primary key';

COMMENT ON COLUMN CPOS.CPOS_COUP_ID IS 'Voucher link';

COMMENT ON COLUMN CPOS.CPOS_ZPOS_ID IS 'Posting link';

ALTER TABLE ZRDF ADD (ZRDF_BARCODE VARCHAR2(30));

CREATE UNIQUE INDEX ZRDF_BARCODE ON ZRDF(ZRDF_BARCODE) TABLESPACE V8INDEX;

COMMENT ON COLUMN ZRDF.ZRDF_BARCODE IS 'String associated with barcode (full string will contain global rebate prefix)';

CREATE TABLE CBCC
(
  CBCC_ID      NUMBER(30)                       NOT NULL,
  CBCC_DESC    VARCHAR(30),
  CBCC_ROLE    NUMBER(1)                        NOT NULL,
  CBCC_REGEXP  VARCHAR2(60)                     NOT NULL,
  CBCC_DISPLORDER Number(4),
  constraint CBCC_PK primary key (CBCC_ID) using index tablespace v8index   
)
TABLESPACE V8MAIN;

COMMENT ON TABLE CBCC IS 'Barcode prefixes configuration';

COMMENT ON COLUMN CBCC.CBCC_ID IS 'CBCC ID';

COMMENT ON COLUMN CBCC.CBCC_ROLE IS '1 = Voucher 2 = Rebate';

COMMENT ON COLUMN CBCC.CBCC_REGEXP IS 'Regular expression to test';

COMMENT ON COLUMN CBCC.CBCC_DESC IS 'Description';

CREATE SEQUENCE SEQ_CBCC INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

INSERT INTO WUSS ( WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE) VALUES ( seq_WUSS.nextval, 0, 'LostAndFound', 1 );

