--UPDATE FROM 8.8.4.1 TO 8.8.4.2


DROP TABLE VERSION_8_8_4_1;



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


COMMENT ON COLUMN XMTY.XMTY_ROLE IS '1 = A/R Number     2 = IATA Number     3 = VAT Number  4 = Frequent Flyer Cards and Loyalty Cards and Bonus point cards (differentiation will not be handled on this level)     5 = Corporate ID (also used for V8Online IFC)     6 = Unique 3rd party Profile ID for Property Interface     7 = Rebate cards     8 = Contract number    9 = Fiscal Code (Legge Bersani, needed in Italy)    ... to be continued for special numbers';

ALTER TABLE XMTY ADD XMTY_BONUSPOINT NUMBER(1);

COMMENT ON COLUMN XMTY.XMTY_BONUSPOINT IS '1=bonus point functionality is enabled for these cards. same as xmty_role=4 but this enables to combine bonus point feature with rebate card feature.'; 

ALTER TABLE XMTY ADD XMTY_WEBENABLED NUMBER(1);

COMMENT ON COLUMN XMTY.XMTY_WEBENABLED IS '1=available over web interface when creating new bonus point card';

ALTER TABLE XMTY ADD XMTY_NUMBEROFLEVELS NUMBER(1);

CREATE TABLE XMTL (
     XMTL_ID NUMBER(30) NOT NULL
    ,XMTL_XMTY_ID NUMBER(30) NOT NULL
    ,XMTL_LEVEL NUMBER(1) NOT NULL
    ,XMTL_SHORTDESC VARCHAR2(5) NOT NULL 
    ,XMTL_LONGDESC VARCHAR2(30) NOT NULL
    ,XMTL_POINTLIMIT NUMBER(30)
    ,CONSTRAINT XMTL_PK PRIMARY KEY (XMTL_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
    
CREATE SEQUENCE SEQ_XMTL INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE XMTL IS 'membership type levels';

ALTER TABLE XMNR ADD XMNR_LEVELNUMBER NUMBER(1);

CREATE TABLE ZBCF (
   ZBCF_ID NUMBER(30) NOT NULL
  ,ZBCF_XMTY_ID NUMBER(30) NOT NULL
  ,ZBCF_STAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_FIRSTSTAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_NIGHTPERADULT NUMBER(1) NOT NULL
  ,ZBCF_RATECODESTAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_RATECODEFIRSTSTAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_RATECODENIGHTPERADULT NUMBER(1) NOT NULL
  ,ZBCF_ROOMTYPESTAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_ROOMTYPEFIRSTSTAYPERADULT NUMBER(1) NOT NULL
  ,ZBCF_ROOMTYPENIGHTPERADULT NUMBER(1) NOT NULL
,CONSTRAINT ZBCF_PK PRIMARY KEY (ZBCF_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZBCF IS 'bonus point membership type config'; 

CREATE TABLE ZBPC (
   ZBPC_ID         NUMBER(30) NOT NULL
  ,ZBPC_XMTY_ID    NUMBER(30) NOT NULL
  ,ZBPC_XMTL_ID    NUMBER(30) 
  ,ZBPC_ZDCO_ID    NUMBER(30) 
  ,ZBPC_CARDROLE   NUMBER(1) NOT NULL
  ,ZBPC_TYPE       NUMBER(1) NOT NULL
  ,ZBPC_VALUE      NUMBER(30,4) NOT NULL  
  ,CONSTRAINT ZBPC_PK PRIMARY KEY (ZBPC_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZBPC IS 'bonus point configuration';

COMMENT ON COLUMN ZBPC.ZBPC_TYPE IS '0=% of total revenue  1=% of revenue per department code  2=per stay  3=for first stay  4=per night 5=redemption (payment rules with points)';

COMMENT ON COLUMN ZBPC.ZBPC_CARDROLE IS '0=rules for guest card linked to reservation 1=rules for bookers card';

COMMENT ON COLUMN ZBPC.ZBPC_ZDCO_ID IS 'filled only if zbpc_type=5 (payment department code)';

CREATE TABLE ZBPD (
   ZBPD_ID         NUMBER(30) NOT NULL
  ,ZBPD_XMTY_ID    NUMBER(30) NOT NULL
  ,ZBPD_XMTL_ID    NUMBER(30)
  ,ZBPD_CARDROLE   NUMBER(1) NOT NULL
  ,ZBPD_ZDCO_ID    NUMBER(30)
  ,ZBPD_VALUE      NUMBER(20)
  ,ZBPD_FLATVALUE  NUMBER(20)  
  ,CONSTRAINT ZBPD_PK PRIMARY KEY (ZBPD_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZBPD IS 'bonus point earning configuration for department codes';

COMMENT ON COLUMN ZBPD.ZBPD_CARDROLE IS '0=rules for guest card linked to reservation 1=rules for bookers card';

CREATE TABLE ZBPX (
   ZBPX_ID         NUMBER(30) NOT NULL
  ,ZBPX_XMTY_ID    NUMBER(30) NOT NULL
  ,ZBPX_XMTL_ID    NUMBER(30)
  ,ZBPX_CARDROLE   NUMBER(1) NOT NULL
  ,ZBPX_YRCH_ID    NUMBER(30)
  ,ZBPX_YCAT_ID    NUMBER(30)
  ,ZBPX_PERSTAY    NUMBER(20)
  ,ZBPX_PERNIGHT   NUMBER(20)
  ,ZBPX_FIRSTSTAY  NUMBER(20)
  ,CONSTRAINT ZBPX_PK PRIMARY KEY (ZBPX_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZBPX IS 'bonus point earning configuration for ratecode and roomtype';

COMMENT ON COLUMN ZBPX.ZBPX_CARDROLE IS '0=rules for guest card linked to reservation 1=rules for bookers card';

CREATE TABLE ZBPS (
   ZBPS_ID         NUMBER(30) NOT NULL
  ,ZBPS_XMNR_ID    NUMBER(30) NOT NULL
  ,ZBPS_VALUE      NUMBER(30) NOT NULL
  ,ZBPS_TIMESTAMP  DATE NOT NULL
  ,ZBPS_MANUAL     NUMBER(1) NOT NULL
  ,ZBPS_YRES_ID    NUMBER(30)
  ,ZBPS_XCED_ID    NUMBER(30) NOT NULL
  ,ZBPS_CARDROLE   NUMBER(1)
  ,ZBPS_EXPIRY     DATE
  ,ZBPS_CONSUMED   NUMBER(30) NOT NULL
  ,ZBPS_TYPE       NUMBER(1)
  ,ZBPS_WPHS_ID    NUMBER(30)
  ,ZBPS_UPLOADSTATUS NUMBER(1) 
  ,ZBPS_UPLOADTIMESTAMP DATE
  ,ZBPS_ARRIVAL    DATE 
  ,ZBPS_DEPARTURE  DATE 
  ,ZBPS_ZBPP_ID    NUMBER(30)
  ,ZBPS_QUANTITY   NUMBER(3)
  ,ZBPS_COMMENT    VARCHAR2(200)
  ,ZBPS_TRANSFERFROM_XMNR_ID NUMBER(30)
  ,CONSTRAINT ZBPS_PK PRIMARY KEY (ZBPS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZBPS IS 'bonus points (earned)';

COMMENT ON COLUMN ZBPS.ZBPS_CARDROLE IS '0=guest card linked to reservation 1=bookers card';

COMMENT ON COLUMN ZBPS.ZBPS_CONSUMED IS 'number of consumed point. to be able to calculate balance considering expiry dates';

COMMENT ON COLUMN ZBPS.ZBPS_TYPE IS 'copied from zbpc type when zbps is created automatically';

COMMENT ON COLUMN ZBPS.ZBPS_UPLOADSTATUS IS '1=upload in progress 2=upload successful 3=upload failed';

COMMENT ON COLUMN ZBPS.ZBPS_TRANSFERFROM_XMNR_ID IS 'transferred from'; 

CREATE TABLE ZBPP (
   ZBPP_ID        NUMBER(30) NOT NULL
  ,ZBPP_SHORTDESC VARCHAR2(5) NOT NULL
  ,ZBPP_LONGDESC  VARCHAR2(60) NOT NULL
  ,ZBPP_PRICE     NUMBER(30) NOT NULL
  ,ZBPP_ONSTOCK   NUMBER(30)
  ,ZBPP_INACTIVE  NUMBER(30)
  ,ZBPP_CHECKAVL  NUMBER(1) NOT NULL 
  ,ZBPP_TYPE      NUMBER(1) NOT NULL
  ,CONSTRAINT ZBPP_PK PRIMARY KEY (ZBPP_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE ZBPP IS 'bonus point product catalog';

COMMENT ON COLUMN ZBPP.ZBPP_PRICE IS 'price in points';

COMMENT ON COLUMN ZBPP.ZBPP_ONSTOCK IS 'number of products available';

COMMENT ON COLUMN ZBPP.ZBPP_CHECKAVL IS '1=check availability 0=dont';

COMMENT ON COLUMN ZBPP.ZBPP_TYPE IS '0=earning 1=redemption';

CREATE TABLE ZBPR (
   ZBPR_ID         NUMBER(30) NOT NULL
  ,ZBPR_XMNR_ID    NUMBER(30) NOT NULL
  ,ZBPR_VALUE      NUMBER(30) NOT NULL
  ,ZBPR_TIMESTAMP  DATE NOT NULL
  ,ZBPR_MANUAL     NUMBER(1) NOT NULL
  ,ZBPR_XCED_ID    NUMBER(30) NOT NULL
  ,ZBPR_ZPOS_ID    NUMBER(30)
  ,ZBPR_ZBPP_ID    NUMBER(30)
  ,ZBPR_QUANTITY   NUMBER(3)   
  ,ZBPR_WPHS_ID    NUMBER(30) 
  ,ZBPR_COMMENT    VARCHAR2(200)
  ,ZBPR_TRANSFERTO_XMNR_ID NUMBER(30)
  ,CONSTRAINT ZBPR_PK PRIMARY KEY (ZBPR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZBPR IS 'bonus point redemption';

COMMENT ON COLUMN ZBPR.ZBPR_ZPOS_ID IS 'payment posting';

COMMENT ON COLUMN ZBPR.ZBPR_ZBPP_ID IS 'product id';

COMMENT ON COLUMN ZBPR.ZBPR_QUANTITY IS 'product quantity';   

COMMENT ON COLUMN ZBPR.ZBPR_TRANSFERTO_XMNR_ID IS 'transferred to';

ALTER TABLE YRES ADD YRES_BONUS_XMNR_ID NUMBER(30);

COMMENT ON COLUMN YRES.YRES_BONUS_XMNR_ID IS 'link to bonus point card';

ALTER TABLE XMNR ADD XMNR_INACTIVE NUMBER(1);

COMMENT ON COLUMN XMNR.XMNR_INACTIVE IS '1 = inactive';

CREATE TABLE ZBST (
   ZBST_ID NUMBER(30) NOT NULL
  ,ZBST_XMNR_ID NUMBER(30) NOT NULL
  ,ZBST_TIMESTAMP DATE NOT NULL
  ,ZBST_BALANCE NUMBER(15) NOT NULL
  ,ZBST_STATEMENTNUMBER NUMBER(15) NOT NULL
  ,ZBST_LAST_ZBPS_ID NUMBER(30)
  ,ZBST_WTXT_ID NUMBER(30)
  ,ZBST_WRPT_ID NUMBER(30)
  ,ZBST_DELIVERYMETHOD NUMBER(1)
  ,ZBST_PREV_ZBST_ID NUMBER(30)
  ,ZBST_WMAI_ID NUMBER(30)
  ,CONSTRAINT ZBST_PK PRIMARY KEY (ZBST_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZBST IS 'bonus point card statement';

ALTER TABLE XMNR ADD XMNR_ISSUEDBY_XCED_ID NUMBER(30);

COMMENT ON COLUMN XMNR.XMNR_ISSUEDBY_XCED_ID IS 'an employee who earns awards for generating a new guest membership'; 

ALTER TABLE SHIS ADD SHIS_XSTA_ID NUMBER(30);

ALTER TABLE XMNR ADD XMNR_INACTIVEREASON VARCHAR2(60);

COMMENT ON COLUMN XMNR.XMNR_INACTIVEREASON IS 'reason why card is inactive/active';

ALTER TABLE WPCA ADD WPCA_SKIPMAKEKEYFORTRANSPONDER NUMBER(1) DEFAULT 0;

COMMENT ON COLUMN WPCA.WPCA_SKIPMAKEKEYFORTRANSPONDER IS '1. Makekey during Transponder check-in will be skiped in this workstation ';

ALTER TABLE ZOTA ADD ZOTA_ZCNV_ID NUMBER(30);

ALTER TABLE MQU2 ADD MQU2_RESEND NUMBER(1,0);

COMMENT ON COLUMN MQU2.MQU2_RESEND IS 'Indicates whether message could not yet be sent';

ALTER TABLE MURL ADD MURL_INACTIVE NUMBER(1,0);

COMMENT ON COLUMN MURL.MURL_INACTIVE IS 'Indicates which setup URL is really used';

ALTER TABLE MWSV ADD MWSV_INACTIVE NUMBER(1,0);

COMMENT ON COLUMN MWSV.MWSV_INACTIVE IS 'Indicates which setup webservice is really used';

CREATE TABLE MIMP
(
  MIMP_ID        NUMBER(30)                     NOT NULL,
  MIMP_MMOD_ID   NUMBER(30)                     NOT NULL,
  MIMP_RECEIVED  DATE                           NOT NULL,
  MIMP_FILENAME  VARCHAR2(50 BYTE)              NOT NULL,
  MIMP_MSG_LINE  VARCHAR2(3000 BYTE),
  CONSTRAINT MIMP_PK PRIMARY KEY (MIMP_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

COMMENT ON COLUMN MIMP.MIMP_ID IS 'unique id';

COMMENT ON COLUMN MIMP.MIMP_MMOD_ID IS 'link to mmod';

COMMENT ON COLUMN MIMP.MIMP_RECEIVED IS 'timestamp of incomming ASCII message';

COMMENT ON COLUMN MIMP.MIMP_FILENAME IS 'name of ASCII request file';

COMMENT ON COLUMN MIMP.MIMP_MSG_LINE IS 'one booking from the reqeust store in one line';

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

CREATE TABLE ZINS (
    ZINS_ID NUMBER(30) NOT NULL
   ,ZINS_ZINV_ID NUMBER(30) NULL
   ,ZINS_ZINN_ID NUMBER(30) 
   ,ZINS_NUMBER VARCHAR2(60) 
   ,ZINS_AMOUNT NUMBER(30, 4) 
   ,ZINS_SIGNATURE VARCHAR2(172) NOT NULL
   ,ZINS_KEYVERSION NUMBER(1) NOT NULL
   ,CONSTRAINT ZINS_PK PRIMARY KEY (ZINS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE ZINS IS 'Portuguese Invoice Signatures';

ALTER TABLE MIMP ADD MIMP_MSG_TYPE NUMBER(1,0);

COMMENT ON COLUMN MIMP.MIMP_MSG_TYPE IS 'Indicates type of message. 1 = Reservation, 2 = Profile';

ALTER TABLE MIMP ADD MIMP_TABLE_ID NUMBER(30,0);

COMMENT ON COLUMN MIMP.MIMP_TABLE_ID IS 'record id of reservation, profile, depending on msg type';

ALTER TABLE XMTY ADD XMTY_ZINN_ID NUMBER(30);

COMMENT ON COLUMN XMTY.XMTY_ZINN_ID IS 'membership numbers from zinn';

CREATE TABLE YFSP
(
    YFSP_ID NUMBER(30) NOT NULL,
    YFSP_DISPLAY NUMBER(3),
    YFSP_FLOOR VARCHAR2(3) NOT NULL,
    YFSP_FLOORNAME VARCHAR2(30),
    YFSP_PICTURE LONG RAW,
    YFSP_PICTURE_C VARCHAR2(2000),
    YFSP_SHORTDESC VARCHAR2(3),
    CONSTRAINT YFSP_PK PRIMARY KEY (YFSP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8LARGE;

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

COMMENT ON TABLE YFSP IS 'FUNCTION SPACES FLOOR PLAN';

COMMENT ON COLUMN YFSP.YFSP_ID IS 'YFSP_ID';

COMMENT ON COLUMN YFSP.YFSP_DISPLAY IS 'DISPLAY ORDER';

COMMENT ON COLUMN YFSP.YFSP_FLOOR IS 'FLOOR CODE';

COMMENT ON COLUMN YFSP.YFSP_FLOORNAME IS 'FLOOR NAME';

COMMENT ON COLUMN YFSP.YFSP_PICTURE IS 'FLOOR PLAN ';

COMMENT ON COLUMN YFSP.YFSP_PICTURE_C IS 'FLOOR PLAN FUNCTION SPACES';

COMMENT ON COLUMN YFSP.YFSP_SHORTDESC IS 'SHORTDESC'; 

ALTER TABLE YFSC ADD YFSC_FLOOR NUMBER(3);

COMMENT ON COLUMN YFSC.YFSC_FLOOR IS 'FUNCTION SPACE FLOOR';

CREATE TABLE SRP2
(
  SRP2_ID                NUMBER(30)             NOT NULL,
  SRP2_DATE              DATE                   NOT NULL,
  SRP2_ZDCO_ID           NUMBER(30)             NOT NULL,
  SRP2_NETREV_D          NUMBER(30,4)               NULL,
  SRP2_GROSSREV_D        NUMBER(30,4)               NULL,
  SRP2_NETREV_M          NUMBER(30,4)               NULL,
  SRP2_GROSSREV_M        NUMBER(30,4)               NULL,
  SRP2_NETREV_Y          NUMBER(30,4)               NULL,
  SRP2_GROSSREV_Y        NUMBER(30,4)               NULL,
  SRP2_PARTNER_ID        NUMBER(30)                 NULL,
  SRP2_IMPORTED          NUMBER(1)                  NULL,
  SRP2_WPHS_ID           NUMBER(30)                 NULL,
  SRP2_ZDCO_LONGDESC     VARCHAR2(30 BYTE)          NULL,
  SRP2_ZDCO_NUMERICDESC  NUMBER(5)                  NULL,
  SRP2_ZDCG_LONGDESC     VARCHAR2(30 BYTE)          NULL,
  SRP2_ZDCO_CDT          NUMBER(1)                  NULL,
  SRP2_TYPE              NUMBER(30)             DEFAULT 0                         NULL
) TABLESPACE V8MAIN ; 

ALTER TABLE SRP2 ADD CONSTRAINT SRP2_PK PRIMARY KEY (SRP2_ID) USING INDEX TABLESPACE V8INDEX;

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

CREATE TABLE SRD2
(
  SRD2_ID                NUMBER(30)             NOT NULL,
  SRD2_DATE              DATE                   NOT NULL,
  SRD2_ZDCO_ID           NUMBER(30)             NOT NULL,
  SRD2_YCAD_ID           NUMBER(30)             NOT NULL,
  SRD2_NETREV_D          NUMBER(30,4)               NULL,
  SRD2_GROSSREV_D        NUMBER(30,4)               NULL,
  SRD2_NETREV_M          NUMBER(30,4)               NULL,
  SRD2_GROSSREV_M        NUMBER(30,4)               NULL,
  SRD2_NETREV_Y          NUMBER(30,4)               NULL,
  SRD2_GROSSREV_Y        NUMBER(30,4)               NULL,
  SRD2_PARTNER_ID        NUMBER(30)                 NULL,
  SRD2_IMPORTED          NUMBER(1)                  NULL,
  SRD2_WPHS_ID           NUMBER(30)                 NULL,
  SRD2_ZDCO_LONGDESC     VARCHAR2(30 BYTE)          NULL,
  SRD2_ZDCO_NUMERICDESC  NUMBER(5)                  NULL,
  SRD2_ZDCG_LONGDESC     VARCHAR2(30 BYTE)          NULL,
  SRD2_ZDCO_CDT          NUMBER(1)                  NULL,
  SRD2_TYPE              NUMBER(30)             DEFAULT 0                         NULL
) TABLESPACE V8MAIN ; 

ALTER TABLE SRD2 ADD CONSTRAINT SRD2_PK PRIMARY KEY (SRD2_ID) USING INDEX TABLESPACE V8INDEX;

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

COMMENT ON COLUMN ZINN.ZINN_TYPE IS ' 1 = invoices, 2 = user definable number cycles, 3 = currency exchange (zfag_type 3), 4 = autonumber for booth telephone charges paid cash, 5 = City Ledger Folio, 11 = autonumber zfag_type 1 (ar postings), 12 = autonumber zfag_type 2 (ar payments), 13 = void invoice, 14 = fattura (for italy), 15 = nota di credito (for Italy or credit note for Greece, Portugal or Spain), 16 = proforma, 17 = fattura and nota di credito (for italy), 18 = nota korekta (for poland), 19 = Egyptian AR Invoice, 20 = AR Payment Receipt, 21 = registration cards, 22 = Ticket BarCode, 23 = Contract Numbers, 31 = automatic fa numbers (zfac_autonumber), 32 = lost and found, 33 = police export, 34 = tourist export, 35 = jetweb interface, 36 = Voucher, 37 = membership numbers';

CREATE TABLE YCRT (
   YCRT_ID NUMBER(30) NOT NULL
  ,YCRT_SHORTDESC VARCHAR2(5) NOT NULL
  ,YCRT_LONGDESC VARCHAR2(200) NOT NULL 
  ,YCRT_XCMA_ID NUMBER(30)
  ,YCRT_YCAT_ID NUMBER(30)
  ,YCRT_YRCH_ID NUMBER(30)
  ,YCRT_CONTRACT_YRCH_ID NUMBER(30)
  ,YCRT_WEEKSTARTTYPE NUMBER(1)
  ,YCRT_WEEKSTARTVALUE NUMBER(1)
  ,YCRT_INACTIVE NUMBER(1)
  ,YCRT_DISPLORDER NUMBER(3) NOT NULL
  ,YCRT_WCOL_ID NUMBER(30)
  ,YCRT_MANDATORYCONTRACTNUMBER NUMBER(1)
  ,YCRT_SOURCE_YRAS_ID NUMBER(30)
  ,YCRT_COMPANY_XCMS_ID NUMBER(30)
  ,YCRT_SOURCE_XCMS_ID NUMBER(30)
  ,YCRT_TRAVELAGENT_XCMS_ID NUMBER(30)
  ,CONSTRAINT YCRT_PK PRIMARY KEY (YCRT_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE YCRT IS 'Vacation sharing template (reservation contract template)';

COMMENT ON COLUMN YCRT.YCRT_WEEKSTARTTYPE IS '0=n. day of the year according to calendar 1=first instance of n. day of the week (n=ycrt_weekstartvalue)';

COMMENT ON COLUMN YCRT.YCRT_MANDATORYCONTRACTNUMBER IS '1=yrcs_contractnumber is mandatory';

COMMENT ON COLUMN YCRT.YCRT_YRCH_ID IS 'ratecode for reservations';

COMMENT ON COLUMN YCRT.YCRT_CONTRACT_YRCH_ID IS 'ratecode for contract yearly charges';

CREATE TABLE YRCS (
   YRCS_ID NUMBER(30) NOT NULL
  ,YRCS_XCMS_ID NUMBER(30) NOT NULL
  ,YRCS_VALIDFROM DATE NOT NULL
  ,YRCS_VALIDUNTIL DATE
  ,YRCS_STAYFROM DATE
  ,YRCS_STAYUNTIL DATE
  ,YRCS_YCRT_ID NUMBER(30)
  ,YRCS_WEEK NUMBER(2)
  ,YRCS_LENGTHWEEKS NUMBER(2)
  ,YRCS_FLOATING NUMBER(1)
  ,YRCS_YCAT_ID NUMBER(30)
  ,YRCS_YRMS_ID NUMBER(30)
  ,YRCS_YRCH_ID NUMBER(30)
  ,YRCS_CONTRACT_YRCH_ID NUMBER(30)
  ,YRCS_XCMA_ID NUMBER(30)
  ,YRCS_COMMENT VARCHAR2(2000)
  ,YRCS_NOOFPERSONS NUMBER(3)
  ,YRCS_ARRIVALDAYOFWEEK NUMBER(1)
  ,YRCS_ACTIONTIMESTAMP DATE
  ,YRCS_ERRORTEXT VARCHAR2(2000)
  ,YRCS_LASTACTIONYEAR NUMBER(4)
  ,YRCS_CONTRACTNUMBER VARCHAR2(50)
  ,YRCS_SOURCE_YRAS_ID NUMBER(30)
  ,CONSTRAINT YRCS_PK PRIMARY KEY (YRCS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YRCS IS 'Vacation sharer contracts (reservation contracts)';

COMMENT ON COLUMN YRCS.YRCS_STAYFROM IS 'year part is ignored. also ignored if week values are filled';

COMMENT ON COLUMN YRCS.YRCS_STAYUNTIL IS 'year part is ignored. also ignored if week values are filled';

COMMENT ON COLUMN YRCS.YRCS_WEEK IS 'on which week the stay begins';

COMMENT ON COLUMN YRCS.YRCS_LENGTHWEEKS IS 'length of stay in weeks';

COMMENT ON COLUMN YRCS.YRCS_FLOATING IS '1=week value needs to be defined for every year separately. yrcs_week is ignored';

COMMENT ON COLUMN YRCS.YRCS_YCAT_ID IS 'filled only if different from template';

COMMENT ON COLUMN YRCS.YRCS_YRMS_ID IS 'fixed room';

COMMENT ON COLUMN YRCS.YRCS_YRCH_ID IS 'reservation rate code';

COMMENT ON COLUMN YRCS.YRCS_CONTRACT_YRCH_ID IS 'contract yearly charges rate code';

COMMENT ON COLUMN YRCS.YRCS_ARRIVALDAYOFWEEK IS '1=sunday 7=saturday';

COMMENT ON COLUMN YRCS.YRCS_ERRORTEXT IS 'error information from last automatic reservation creation procedure';

ALTER TABLE YRES ADD YRES_YRCS_ID NUMBER(30);

COMMENT ON COLUMN YRES.YRES_YRCS_ID IS 'Link to vacation sharer';

CREATE TABLE YRCY (
   YRCY_ID NUMBER(30) NOT NULL
  ,YRCY_YRCS_ID NUMBER(30) NOT NULL
  ,YRCY_YEAR NUMBER(4) NOT NULL
  ,YRCY_WEEK NUMBER(2) 
  ,YRCY_WEEKTO NUMBER(2)
  ,YRCY_YEARLYFEE_PERSTAY NUMBER(30,4)
  ,YRCY_YCAT_ID NUMBER(30)
  ,YRCY_YRMS_ID NUMBER(30)
  ,CONSTRAINT YRCY_PK PRIMARY KEY (YRCY_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YRCY IS 'Vacation sharing years for exception and floating weeks';

CREATE TABLE YRCZ (
   YRCZ_ID NUMBER(30) NOT NULL
  ,YRCZ_YRCS_ID NUMBER(30) NOT NULL
  ,YRCZ_YEAR NUMBER(4) NOT NULL
  ,YRCZ_WEEKFROM NUMBER(2) NOT NULL
  ,YRCZ_WEEKTO NUMBER(2) NOT NULL
  ,YRCZ_FEE NUMBER(30,4) NOT NULL
  ,YRCZ_TIMESTAMP DATE NOT NULL
  ,YRCZ_XCED_ID NUMBER(30) NOT NULL
  ,YRCZ_XCMS_ID NUMBER(30) NOT NULL
  ,YRCZ_ZFAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT YCRZ_PK PRIMARY KEY (YRCZ_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN; 

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

COMMENT ON TABLE YRCZ IS 'Vacation sharing yearly fee postings to zfac';

ALTER TABLE YRCO ADD YRCO_YRCS_ID NUMBER(30);

ALTER TABLE YRCO ADD YRCO_YRCS_YEAR NUMBER(4);

COMMENT ON COLUMN SRPD.SRPD_TYPE IS 'Obsolete column, new table SRP2 used instead to differentiate';

COMMENT ON COLUMN SRDS.SRDS_TYPE IS 'Obsolete column, new table SRD2 used instead to differentiate';

COMMENT ON COLUMN SRP2.SRP2_TYPE IS '0 Standard (all revenue), 1 Hotel, 2 Deposit, 3 Voucher';

COMMENT ON COLUMN SRD2.SRD2_TYPE IS '0 Standard (all revenue), 1 Hotel, 2 Deposit, 3 Voucher';

CREATE TABLE YRCM (
   YRCM_ID NUMBER(30)
  ,YRCM_YRCS_ID NUMBER(30) NOT NULL
  ,YRCM_XCMS_ID NUMBER(30) NOT NULL
  ,CONSTRAINT YRCM_PK PRIMARY KEY (YRCM_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE YRCM IS 'res (vacation) contract members';

CREATE TABLE WCRN (
    WCRN_ID NUMBER(30)
   ,WCRN_WLAN_ID NUMBER(30) NOT NULL
   ,WCRN_GRIDNAME VARCHAR2(200) NOT NULL
   ,WCRN_ORIGINALCOLUMNNAME VARCHAR2(60) NOT NULL
   ,WCRN_NEWCOLUMNNAME VARCHAR2(60) NOT NULL
   ,CONSTRAINT WCRN_PK PRIMARY KEY (WCRN_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WCRN IS 'column rename in grids';

ALTER TABLE MMOD ADD MMOD_USE_FREETEXT NUMBER(1,0);

COMMENT ON COLUMN MMOD.MMOD_USE_FREETEXT IS 'Indicates whether comment also stored in Freetext2 field in yres';

ALTER TABLE MMOD ADD MMOD_FTP_PASSIVE NUMBER(1,0);

ALTER TABLE MMOD ADD MMOD_FTP_BACKUP NUMBER(1,0);

ALTER TABLE MMOD ADD MMOD_FTP_BACKUPPATH VARCHAR2(100);

COMMENT ON COLUMN MMOD.MMOD_FTP_PASSIVE IS 'Indicates whether the ftp is used in passive mode';

COMMENT ON COLUMN MMOD.MMOD_FTP_BACKUP IS 'Indicates whether the interface makes a backup on the ftp of received file';

COMMENT ON COLUMN MMOD.MMOD_FTP_BACKUPPATH IS 'backup path on the ftp';
