--UPDATE FROM 8.9.5.3 TO 8.9.5.4


DROP TABLE VERSION_8_9_5_3;


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

CREATE TABLE VSTG (
   VSTG_ID NUMBER(30) NOT NULL
  ,VSTG_SHORTDESC VARCHAR2(12) NOT NULL UNIQUE
  ,VSTG_LONGDESC VARCHAR2(30) NOT NULL UNIQUE
  ,VSTG_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTG_DISPLORDER NUMBER(3) 
  ,CONSTRAINT VSTG_PK PRIMARY KEY (VSTG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSTG is 'Service type group';

CREATE TABLE VSTS (
   VSTS_ID NUMBER(30) NOT NULL
  ,VSTS_SHORTDESC VARCHAR2(12) NOT NULL
  ,VSTS_LONGDESC VARCHAR2(30) NOT NULL
  ,VSTS_DISPLORDER NUMBER(3) 
  ,VSTS_VSTG_ID NUMBER(30) NOT NULL
  ,VSTS_PROVIDER_XCMS_ID NUMBER(30)
  ,VSTS_THIRDPARTY NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_ALLOCATIONS NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_MAXNUMBER NUMBER(3)
  ,VSTS_CHARGETYPE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_WCOL_ID NUMBER(30)
  ,VSTS_SCHEDULED NUMBER(1) DEFAULT 1 NOT NULL
  ,VSTS_FROMTIME DATE 
  ,VSTS_UNTILTIME DATE
  ,VSTS_PICKUPNEEDED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_MINPERSON NUMBER(3)
  ,VSTS_MAXPERSON NUMBER(3)
  ,VSTS_TODATEALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TOTIMEALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_DESCRIPTIONALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_SIMPLEPRICESETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICE NUMBER(30,4)
  ,VSTS_PRICE_ZCUR_ID NUMBER(30)
  ,VSTS_PRICE_ZDCO_ID NUMBER(30)
  ,VSTS_PRICEFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICEPERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_PRICE_YRCH_ID NUMBER(30)
  ,VSTS_SIMPLECOSTSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_COST NUMBER(30,4)
  ,VSTS_COST_ZCUR_ID NUMBER(30)
  ,VSTS_COST_ZDCO_ID NUMBER(30)
  ,VSTS_COSTFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_COST_YRCH_ID NUMBER(30)
  ,VSTS_SIMPLETAXSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TAX NUMBER(30,4)
  ,VSTS_TAX_ZCUR_ID NUMBER(30)
  ,VSTS_TAX_ZDCO_ID NUMBER(30)
  ,VSTS_TAX_YRCH_ID NUMBER(30)
  ,VSTS_PRICEDESCALLOWED NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_TAXFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSTS_ALLOWWITHOUTCLASS NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT VSTS_PK PRIMARY KEY (VSTS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

COMMENT ON TABLE VSTS is 'Service type';

COMMENT ON COLUMN VSTS.VSTS_MAXNUMBER is 'For example: for hotel owned vehicles have a maximum NUMBER';

COMMENT ON COLUMN VSTS.VSTS_CHARGETYPE is '0=once 1=per hour 2=per day'; 

COMMENT ON COLUMN VSTS.VSTS_SCHEDULED is '0=service is available on demand. ie: taxi 1=available at prescheduled times';

COMMENT ON COLUMN VSTS.VSTS_FROMTIME is 'For non scheduled services this defines availability of service during the day';

COMMENT ON COLUMN VSTS.VSTS_UNTILTIME is 'For non scheduled services this defines availability of service during the day';

COMMENT ON COLUMN VSTS.VSTS_DESCRIPTIONALLOWED is '0=disabled 1=enabled 2=mandatory';

COMMENT ON COLUMN VSTS.VSTS_PRICEDESCALLOWED is '0=disabled 1=enabled 2=mandatory';

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

CREATE TABLE VSCL (
   VSCL_ID NUMBER(30) NOT NULL
  ,VSCL_SHORTDESC VARCHAR2(12) NOT NULL
  ,VSCL_LONGDESC VARCHAR2(30) NOT NULL
  ,VSCL_DISPLORDER NUMBER(3) 
  ,VSCL_VSTS_ID NUMBER(30) NOT NULL
  ,VSCL_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_SIMPLEPRICESETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICE NUMBER(30,4)
  ,VSCL_PRICE_ZCUR_ID NUMBER(30)
  ,VSCL_PRICE_ZDCO_ID NUMBER(30)
  ,VSCL_PRICEFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICEPERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_PRICE_YRCH_ID NUMBER(30)
  ,VSCL_SIMPLECOSTSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_COST NUMBER(30,4)
  ,VSCL_COST_ZCUR_ID NUMBER(30)
  ,VSCL_COST_ZDCO_ID NUMBER(30)
  ,VSCL_COSTFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_COST_YRCH_ID NUMBER(30)
  ,VSCL_SIMPLETAXSETUP NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_TAX NUMBER(30,4)
  ,VSCL_TAX_ZCUR_ID NUMBER(30)
  ,VSCL_TAX_ZDCO_ID NUMBER(30)
  ,VSCL_TAXFLAT NUMBER(1) DEFAULT 0 NOT NULL
  ,VSCL_TAX_YRCH_ID NUMBER(30)
  ,CONSTRAINT VSCL_PK PRIMARY KEY (VSCL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCL is 'Service class';

CREATE TABLE VSPL (
   VSPL_ID NUMBER(30) NOT NULL
  ,VSPL_VSTS_ID NUMBER(30) NOT NULL
  ,VSPL_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPL_PK PRIMARY KEY (VSPL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPL is 'Service - PRICE Package link';

CREATE TABLE VSPC (
   VSPC_ID NUMBER(30) NOT NULL
  ,VSPC_VSTS_ID NUMBER(30) NOT NULL
  ,VSPC_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPC_PK PRIMARY KEY (VSPC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPC is 'Service - Cost Package link';

CREATE TABLE VSPT (
   VSPT_ID NUMBER(30) NOT NULL
  ,VSPT_VSTS_ID NUMBER(30) NOT NULL
  ,VSPT_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSPT_PK PRIMARY KEY (VSPT_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSPT is 'Service - Tax Package link';

CREATE TABLE VSCP (
   VSCP_ID NUMBER(30) NOT NULL
  ,VSCP_VSCL_ID NUMBER(30) NOT NULL
  ,VSCP_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCP_PK PRIMARY KEY (VSCP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCP is 'Service Class - PRICE Package link';

CREATE TABLE VSCC (
   VSCC_ID NUMBER(30) NOT NULL
  ,VSCC_VSCL_ID NUMBER(30) NOT NULL
  ,VSCC_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCC_PK PRIMARY KEY (VSCC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCC is 'Service Class - Cost Package link';

CREATE TABLE VSCT (
   VSCT_ID NUMBER(30) NOT NULL
  ,VSCT_VSCL_ID NUMBER(30) NOT NULL
  ,VSCT_YPAC_ID NUMBER(30) NOT NULL
  ,CONSTRAINT VSCT_PK PRIMARY KEY (VSCT_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSCT is 'Service Class - Cost Package link';

CREATE TABLE VSAV (
   VSAV_ID NUMBER(30) NOT NULL
  ,VSAV_VSTS_ID NUMBER(30) NOT NULL
  ,VSAV_FROMDATE DATE NOT NULL
  ,VSAV_TODATE DATE NOT NULL
  ,VSAV_FROMHOUR DATE
  ,VSAV_TOHOUR DATE
  ,VSAV_MON NUMBER(1) 
  ,VSAV_TUE NUMBER(1) 
  ,VSAV_WED NUMBER(1) 
  ,VSAV_THU NUMBER(1) 
  ,VSAV_FRI NUMBER(1) 
  ,VSAV_SAT NUMBER(1) 
  ,VSAV_SUN NUMBER(1)
  ,VSAV_STATUS NUMBER(1) DEFAULT 1 NOT NULL 
  ,CONSTRAINT VSAV_PK PRIMARY KEY (VSAV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VSAV is 'Service availability';

COMMENT ON COLUMN VSAV.VSAV_STATUS is '0=not available 1=available';

CREATE TABLE VCTY (
   VCTY_ID NUMBER(30) NOT NULL
  ,VCTY_SHORTDESC VARCHAR2(12) NOT NULL
  ,VCTY_LONGDESC VARCHAR2(30) NOT NULL
  ,VCTY_DISPLORDER NUMBER(3) 
  ,VCTY_XCOU_ID VARCHAR2(30)
  ,VCTY_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT VCTY_PK PRIMARY KEY (VCTY_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VCTY is 'City';

CREATE TABLE VCAS (
    VCAS_ID NUMBER(30) NOT NULL
   ,VCAS_VCTY_ID NUMBER(30) NOT NULL 
   ,VCAS_VSTS_ID NUMBER(30) NOT NULL
   ,VCAS_NUMBER NUMBER(3) 
   ,CONSTRAINT VCAS_PK PRIMARY KEY (VCAS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VCAS is 'Available service list for hotels';

CREATE TABLE VLOC (
   VLOC_ID NUMBER(30) NOT NULL
  ,VLOC_SHORTDESC VARCHAR2(12) NOT NULL
  ,VLOC_LONGDESC VARCHAR2(30) NOT NULL
  ,VLOC_DISPLORDER NUMBER(3) 
  ,VLOC_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
  ,VLOC_VCTY_ID VARCHAR2(30)
  ,VLOC_WPHS_ID VARCHAR2(30)
  ,VLOC_WPHSREPR NUMBER(1) DEFAULT 0 NOT NULL
  ,VLOC_GPS VARCHAR2(50)
  ,CONSTRAINT VLOC_PK PRIMARY KEY (VLOC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VLOC is 'Location';

COMMENT ON COLUMN VLOC.VLOC_WPHSREPR is 'Hotel representation type: 0=hotel 1=location inside hotel 2=location near hotel';

ALTER TABLE WPHS ADD WPHS_SYSTEMTYPE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN WPHS.WPHS_SYSTEMTYPE is '0=fidelio pms 1=third party system'; 

ALTER TABLE WPHS ADD WPHS_PROPERTYTYPE NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN WPHS.WPHS_PROPERTYTYPE is '0=hotel 1=restaurant 2=spa'; 

ALTER TABLE WPHS ADD WPHS_VLOC_ID NUMBER(30);

CREATE TABLE VLAS (
    VLAS_ID NUMBER(30) NOT NULL
   ,VLAS_VLOC_ID NUMBER(30) NOT NULL 
   ,VLAS_VSTS_ID NUMBER(30) NOT NULL
   ,VLAS_NUMBER NUMBER(3) 
   ,CONSTRAINT VLAS_PK PRIMARY KEY (VLAS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE VLAS is 'Available service list for locations';

CREATE TABLE VRST (
    VRST_ID NUMBER(30) NOT NULL
   ,VRST_SHORTDESC VARCHAR2(10)
   ,VRST_LONGDESC VARCHAR2(50)
   ,VRST_VSTS_ID NUMBER(30) NOT NULL
   ,VRST_FROMDATE DATE NOT NULL
   ,VRST_TODATE DATE NOT NULL
   ,VRST_STARTTIME DATE
   ,VRST_ENDTIME DATE
   ,VRST_FROM_VLOC_ID NUMBER(30)
   ,VRST_TO_VLOC_ID NUMBER(30)
   ,VRST_WEEKLYSCHEDULE CHAR(14) DEFAULT '11111111111111' NOT NULL
   ,VRST_INACTIVE NUMBER(1) DEFAULT 0 NOT NULL
   ,VRST_WCOL_ID NUMBER(30)
   ,CONSTRAINT VRST_PK PRIMARY KEY (VRST_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE VRST is 'Regular service timetable (transport timetable. ie: flights)';

CREATE TABLE VSSV (
    VSSV_ID NUMBER(30) NOT NULL
   ,VSSV_SHORTDESC VARCHAR2(10)
   ,VSSV_LONGDESC VARCHAR2(50)
   ,VSSV_VSTG_ID NUMBER(30) NOT NULL
   ,VSSV_VSTS_ID NUMBER(30)
   ,VSSV_VRST_ID NUMBER(30)
   ,VSSV_FROM_VLOC_ID NUMBER(30)
   ,VSSV_TO_VLOC_ID NUMBER(30)
   ,VSSV_FROM DATE NOT NULL
   ,VSSV_TO DATE
   ,VSSV_FROMTIME DATE
   ,VSSV_TOTIME DATE
   ,VSSV_ORIGINALFROMTIME DATE
   ,VSSV_ORIGINALTOTIME DATE
   ,VSSV_DURATIONMINUTES NUMBER(4)
   ,VSSV_WCOL_ID NUMBER(30)
   ,VSSV_VALIDATIONTIME DATE
   ,VSSV_ORGANISEDTIME DATE
   ,VSSV_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSSV_CLOSEDFORINDEPENDENT DATE
   ,CONSTRAINT VSSV_PK PRIMARY KEY (VSSV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;   
   
CREATE SEQUENCE SEQ_VSSV INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE VSSV is 'Scheduled service (ie: taxi or flight)';

COMMENT ON COLUMN VSSV.VSSV_VSTG_ID is 'for drafts without VSTS id';

COMMENT ON COLUMN VSSV.VSSV_VALIDATIONTIME is '1=Resquest is valiDATEd for availability 0=Request/promise only';

COMMENT ON COLUMN VSSV.VSSV_ORGANISEDTIME is '1=Service is organised and booked to happen';

COMMENT ON COLUMN VSSV.VSSV_STATUS is '0=active 1=cancelled';

COMMENT ON COLUMN VSSV.VSSV_CLOSEDFORINDEPENDENT is '1=closed for independent booking. Allocation only';

CREATE TABLE VSAH (
    VSAH_ID NUMBER(30) NOT NULL
   ,VSAH_SHORTDESC VARCHAR2(10)
   ,VSAH_LONGDESC VARCHAR2(60)
   ,VSAH_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAH_FROMDATE DATE
   ,VSAH_TODATE DATE
   ,VSAH_CUTOFFDATE DATE
   ,VSAH_CUTOFFDAYS NUMBER(3)
   ,VSAH_PRICE NUMBER(30,4)
   ,VSAH_COST NUMBER(30,4)
   ,VSAH_TAX NUMBER(30,4)
   ,VSAH_PRICE_MANUAL NUMBER(30,4)
   ,VSAH_COST_MANUAL NUMBER(30,4)
   ,VSAH_TAX_MANUAL NUMBER(30,4)
   ,VSAH_PRICE_FOREIGN NUMBER(30,4)
   ,VSAH_COST_FOREIGN NUMBER(30,4)
   ,VSAH_TAX_FOREIGN NUMBER(30,4)
   ,VSAH_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAH_PRICE_ZDCO_ID NUMBER(30)
   ,VSAH_COST_ZDCO_ID NUMBER(30)
   ,VSAH_TAX_ZDCO_ID NUMBER(30)
   ,VSAH_PRICE_ZCUR_ID NUMBER(30)
   ,VSAH_COST_ZCUR_ID NUMBER(30)
   ,VSAH_TAX_ZCUR_ID NUMBER(30)
   ,VSAH_PRICE_EXCHRATE NUMBER(38,18)
   ,VSAH_COST_EXCHRATE NUMBER(38,18)
   ,VSAH_TAX_EXCHRATE NUMBER(38,18)
   ,VSAH_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSAH_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSAH_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_COST_FINALAMOUNT NUMBER(30,4)
   ,VSAH_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSAH_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAH_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAH_PRICE_PERCENTAGEVALUE NUMBER(30,4)
,CONSTRAINT VSAH_PK PRIMARY KEY (VSAH_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE VSAH is 'Service Allocation header';

COMMENT ON COLUMN VSAH.VSAH_STATUS is '0=definive 1=tentative 2=cancelled';

CREATE TABLE VSAD (
    VSAD_ID NUMBER(30) NOT NULL
   ,VSAD_VSAH_ID NUMBER(30) NOT NULL
   ,VSAD_VSSV_ID NUMBER(30)
   ,VSAD_VSCL_ID NUMBER(30)
   ,VSAD_NOOFPERSONS NUMBER(30) NOT NULL
   ,VSAD_PRICE NUMBER(30,4)
   ,VSAD_COST NUMBER(30,4)
   ,VSAD_TAX NUMBER(30,4)
   ,VSAD_PRICE_MANUAL NUMBER(30,4)
   ,VSAD_COST_MANUAL NUMBER(30,4)
   ,VSAD_TAX_MANUAL NUMBER(30,4)
   ,VSAD_PRICE_FOREIGN NUMBER(30,4)
   ,VSAD_COST_FOREIGN NUMBER(30,4)
   ,VSAD_TAX_FOREIGN NUMBER(30,4)
   ,VSAD_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSAD_PRICE_ZDCO_ID NUMBER(30)
   ,VSAD_COST_ZDCO_ID NUMBER(30)
   ,VSAD_TAX_ZDCO_ID NUMBER(30)
   ,VSAD_PRICE_ZCUR_ID NUMBER(30)
   ,VSAD_COST_ZCUR_ID NUMBER(30)
   ,VSAD_TAX_ZCUR_ID NUMBER(30)
   ,VSAD_PRICE_EXCHRATE NUMBER(38,18)
   ,VSAD_COST_EXCHRATE NUMBER(38,18)
   ,VSAD_TAX_EXCHRATE NUMBER(38,18)
   ,VSAD_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSAD_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSAD_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_COST_FINALAMOUNT NUMBER(30,4)
   ,VSAD_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSAD_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSAD_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSAD_PRICE_PERCENTAGEVALUE NUMBER(30,4)
   ,CONSTRAINT VSAD_PK PRIMARY KEY (VSAD_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
     
CREATE SEQUENCE SEQ_VSAD INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
     
COMMENT ON TABLE VSAD is 'Service allocation detail';

CREATE TABLE VBOK (
    VBOK_ID NUMBER(30) NOT NULL
   ,VBOK_XCMS_ID NUMBER(30) NOT NULL
   ,VBOK_YGCO_ID NUMBER(30)
   ,VBOK_STATUS NUMBER(1) NOT NULL
   ,VBOK_WCOL_ID NUMBER(30)
   ,VBOK_ZCUR_ID NUMBER(30)
   ,VBOK_YCDP_ID NUMBER(30)
   ,VBOK_BONUS_XMNR_ID NUMBER(30)
   ,VBOK_CONFIRMATIONNO VARCHAR2(10) NOT NULL UNIQUE
   ,VBOK_NOOFPERSONS NUMBER(4) NOT NULL
   ,VBOK_STARTDATE DATE
   ,VBOK_ENDDATE DATE
   ,VBOK_FREETEXT1 VARCHAR2(100)
   ,VBOK_FREETEXT2 VARCHAR2(100)
   ,VBOK_XCMA_ID NUMBER(30)
   ,VBOK_CHANNEL_YRAS_ID NUMBER(30)
   ,VBOK_SOURCE_YRAS_ID NUMBER(30)
   ,VBOK_READONLY DATE
   ,VBOK_OPTIONDATE DATE
   ,CONSTRAINT VBOK_PK PRIMARY KEY (VBOK_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
 
COMMENT ON TABLE VBOK is 'Booking. Collects room reservations and service reservations';

COMMENT ON COLUMN VBOK.VBOK_STATUS is '0=tentative 1=definitive 2=offer 3=cancelled';

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

CREATE TABLE VBOP (
    VBOP_ID NUMBER(30) NOT NULL
   ,VBOP_VBOK_ID NUMBER(30) NOT NULL
   ,VBOP_XCAC_ID NUMBER(30) NOT NULL
   ,VBOP_NUMBER NUMBER(3) NOT NULL
   ,CONSTRAINT VBOP_PK PRIMARY KEY (VBOP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBOP is 'Service Plan - Person NUMBER';

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

CREATE TABLE VBPL (
    VBPL_ID NUMBER(30) NOT NULL
   ,VBPL_VBOK_ID NUMBER(30) NOT NULL
   ,VBPL_XCMS_ID NUMBER(30)
   ,CONSTRAINT VBPL_PK PRIMARY KEY (VBPL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBPL is 'Booking - Profile link (guests, travel agent, booker etc)';

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

CREATE TABLE VBGL (
    VBGL_ID NUMBER(30) NOT NULL
   ,VBGL_VBOK_ID NUMBER(30) NOT NULL
   ,VBGL_XCMS_ID NUMBER(30)
   ,VBGL_XCAC_ID NUMBER(30)
   ,VBGL_XCAC_index NUMBER(2) 
   ,CONSTRAINT VBGL_PK PRIMARY KEY (VBGL_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBGL is 'Booking - Guest link';

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

CREATE TABLE VSRV (
    VSRV_ID NUMBER(30) NOT NULL
   ,VSRV_type NUMBER(1) NOT NULL 
   ,VSRV_NOOFPERSONS NUMBER(30)
   ,VSRV_VBOK_ID NUMBER(30)
   ,VSRV_NUMBER VARCHAR2(30)
   ,VSRV_INVOICEDINCENTRAL NUMBER(1) DEFAULT 1 NOT NULL
   ,VSRV_SYSTEMNAME VARCHAR2(10)
   ,VSRV_LINKID VARCHAR2(50)
   ,VSRV_START_WPHS_ID NUMBER(30)
   ,VSRV_END_WPHS_ID NUMBER(30)
   ,VSRV_INVOICE_WPHS_ID NUMBER(30)
   ,VSRV_FROMDATE DATE
   ,VSRV_TODATE DATE
   ,VSRV_FROMTIME DATE
   ,VSRV_TOTIME DATE
   ,VSRV_CONFIRMATIONNO VARCHAR2(30) UNIQUE
   ,VSRV_VSCL_ID NUMBER(30)
   ,VSRV_PRICE_ZCUR_ID NUMBER(30)
   ,VSRV_PRICE NUMBER(30,4)
   ,VSRV_PRICE_FOREIGN NUMBER(30,4)
   ,VSRV_PRICE_MANUAL NUMBER(30,4)
   ,VSRV_PRICE_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_PRICE_EXCHRATE NUMBER(38,18)
   ,VSRV_PRICE_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_PRICE_FINALAMOUNT NUMBER(30,4)
   ,VSRV_PRICE_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_PRICE_PERCENTAGE NUMBER(1) DEFAULT 0 NOT NULL
   ,VSRV_PRICE_PERCENTAGEVALUE NUMBER(30,4)
   ,VSRV_COST_ZCUR_ID NUMBER(30)
   ,VSRV_COST NUMBER(30,4)
   ,VSRV_COST_FOREIGN NUMBER(30,4)
   ,VSRV_COST_MANUAL NUMBER(30,4)
   ,VSRV_COST_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_COST_EXCHRATE NUMBER(38,18)
   ,VSRV_COST_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_COST_FINALAMOUNT NUMBER(30,4)
   ,VSRV_COST_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_TAX_ZCUR_ID NUMBER(30)
   ,VSRV_TAX NUMBER(30,4)
   ,VSRV_TAX_FOREIGN NUMBER(30,4)
   ,VSRV_TAX_MANUAL NUMBER(30,4)
   ,VSRV_TAX_FOREIGN_MANUAL NUMBER(30,4)
   ,VSRV_TAX_EXCHRATE NUMBER(38,18)
   ,VSRV_TAX_REVERSEEXCHRATE NUMBER(1)
   ,VSRV_TAX_FINALAMOUNT NUMBER(30,4)
   ,VSRV_TAX_FOREIGNFinalAmount NUMBER(30,4)
   ,VSRV_STATUS NUMBER(1) DEFAULT 0 NOT NULL
   ,VSRV_POSTTIME DATE
   ,VSRV_DESC VARCHAR2(200)
   ,VSRV_FROM_VLOC_ID NUMBER(30)
   ,VSRV_TO_VLOC_ID NUMBER(30)
   ,VSRV_VSAH_ID NUMBER(30)
   ,VSRV_AFFECTSAVAILABILITY NUMBER(1) NOT NULL
   ,CONSTRAINT VSRV_PK PRIMARY KEY (VSRV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRV is 'Service reservation (leg)';

COMMENT ON COLUMN VSRV.VSRV_type is '0=service 1=room reservation 2=third party service';

COMMENT ON COLUMN VSRV.VSRV_SYSTEMNAME is 'Third party related service. Empty=fidelio';

COMMENT ON COLUMN VSRV.VSRV_LINKID is 'Id in third party system';

COMMENT ON COLUMN VSRV.VSRV_STATUS is '0=tentative 1=definitive 2=cancelled';

COMMENT ON COLUMN VSRV.VSRV_FROM_VLOC_ID is 'Location info for non scheduled services (VSSV) ie reservations and third party services';

COMMENT ON COLUMN VSRV.VSRV_TO_VLOC_ID is 'Location info for non scheduled services (VSSV) ie reservations and third party services';

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

CREATE TABLE VSRP (
    VSRP_ID NUMBER(30) NOT NULL
   ,VSRP_VSRV_ID NUMBER(30) NOT NULL
   ,VSRP_XCAC_ID NUMBER(30) NOT NULL
   ,VSRP_NUMBER NUMBER(3) NOT NULL
   ,VSRP_VSCL_ID NUMBER(30)
   ,VSRP_VSAD_ID NUMBER(30) 
   ,CONSTRAINT VSRP_PK PRIMARY KEY (VSRP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRP is 'Service reservation - Person NUMBER';

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

CREATE TABLE VSRE (
    VSRE_ID NUMBER(30) NOT NULL
   ,VSRE_VSRV_ID NUMBER(30) NOT NULL
   ,VSRE_XCED_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSRE_PK PRIMARY KEY (VSRE_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRE is 'Service reservation - Employee link (ie: drivers)';

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

CREATE TABLE VSRG (
    VSRG_ID NUMBER(30) NOT NULL
   ,VSRG_VSRV_ID NUMBER(30) NOT NULL
   ,VSRG_ROLE NUMBER(1) NOT NULL 
   ,VSRG_XCMS_ID NUMBER(30)
   ,VSRG_XCAC_ID NUMBER(30)
   ,VSRG_XCAC_INDEX NUMBER(2) 
   ,CONSTRAINT VSRG_PK PRIMARY KEY (VSRG_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
   
COMMENT ON TABLE VSRG is 'Service reservation - Guest link';

COMMENT ON COLUMN VSRG.VSRG_ROLE is '0=guest 1=linked for internalcategory';

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

CREATE TABLE VPOS (
    VPOS_ID NUMBER(30) NOT NULL
   ,VPOS_VSRV_ID NUMBER(30) NOT NULL
   ,VPOS_XCMS_ID NUMBER(30) NOT NULL
   ,VPOS_DATE DATE NOT NULL
   ,VPOS_POSTINGDATE DATE NOT NULL
   ,VPOS_ZDCO_ID NUMBER(30) NOT NULL
   ,VPOS_UNITPRICE NUMBER(30,4) NOT NULL
   ,VPOS_QUANTITY NUMBER(10) NOT NULL
   ,VPOS_TAXLINK_ID NUMBER(30) NOT NULL
   ,VPOS_INERNALSPLIT_ID NUMBER(30) NOT NULL
   ,VPOS_DT NUMBER(1) NOT NULL
   ,VPOS_GROSSUNITPRICE NUMBER(30) NOT NULL
   ,VPOS_TRANSFERRED NUMBER(1) NOT NULL
   ,VPOS_POSTED NUMBER(1) NOT NULL
   ,VPOS_ZPOS_ID NUMBER(30)
   ,VPOS_WPHS_ID NUMBER(30)
   ,VPOS_FOREIGNUnitPRICE NUMBER(30,4)
   ,VPOS_FOREIGNGrossUnitPRICE NUMBER(30,4)
   ,VPOS_ZCUR_ID NUMBER(30) NOT NULL
   ,VPOS_TYPE NUMBER(1) NOT NULL
   ,VPOS_YPOS_type NUMBER(1)
   ,VPOS_M_ZCUR_ID NUMBER(30)
   ,VPOS_M_UNITPRICE NUMBER(30)
   ,VPOS_M_GROSSUNITPRICE NUMBER(30) 
   ,CONSTRAINT VPOS_PK PRIMARY KEY (VPOS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VPOS is 'Service charges (later transferred to zpos in central, or to partner, or to third party system)';

COMMENT ON COLUMN VPOS.VPOS_TRANSFERRED is '0=not transferred 1=transferred';

COMMENT ON COLUMN VPOS.VPOS_ZPOS_ID is 'In central postings only. Empty if transferred to partner. (Partner stores VPOS_ID for reference)';

COMMENT ON COLUMN VPOS.VPOS_WPHS_ID is 'Empty for central postings. Filled for items to be transferred';

COMMENT ON COLUMN VPOS.VPOS_TYPE is '0=PRICE 1=cost 2=tax';

COMMENT ON COLUMN VPOS.VPOS_YPOS_TYPE is 'Same as ypos_type. Data is copied from ypos';

COMMENT ON COLUMN VPOS.VPOS_M_ZCUR_ID is 'Mirror home currency';

COMMENT ON COLUMN VPOS.VPOS_M_UNITPRICE is 'Mirror unit PRICE in mirror home currency';

COMMENT ON COLUMN VPOS.VPOS_M_GROSSUNITPRICE is 'Mirror gross unit PRICE in mirror home currency'; 

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

CREATE TABLE VBOA (
    VBOA_ID NUMBER(30) NOT NULL
   ,VBOA_VBOK_ID NUMBER(30) NOT NULL  
   ,VBOA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VBOA_PK PRIMARY KEY (VBOA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VBOA is 'Attribute link to Service Plan';

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

CREATE TABLE VSRA (
    VSRA_ID NUMBER(30) NOT NULL
   ,VSRA_VSRV_ID NUMBER(30) NOT NULL  
   ,VSRA_xpda_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSRA_PK PRIMARY KEY (VSRA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSRA is 'Attribute link to Service Booking';

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

CREATE TABLE VSSA (
    VSSA_ID NUMBER(30) NOT NULL
   ,VSSA_VSSV_ID NUMBER(30) NOT NULL  
   ,VSSA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSSA_PK PRIMARY KEY (VSSA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSSA is 'Attribute link to Scheduled Service';

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

CREATE TABLE VSAA (
    VSAA_ID NUMBER(30) NOT NULL
   ,VSAA_VSAH_ID NUMBER(30) NOT NULL  
   ,VSAA_XPDA_ID NUMBER(30) NOT NULL 
   ,CONSTRAINT VSAA_PK PRIMARY KEY (VSAA_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE VSAA is 'Attribute link to Service Allocation';

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

ALTER TABLE ZDCO ADD ZDCO_CCCOMMISSION_ZDCO_ID NUMBER(30);

ALTER TABLE ZDCO ADD ZDCO_CCCOMMISSION_PERCENT NUMBER(4, 2);

COMMENT ON COLUMN ZDCO.ZDCO_CCCOMMISSION_ZDCO_ID IS 'Department code for Credit Card Commission posting';

COMMENT ON COLUMN ZDCO.ZDCO_CCCOMMISSION_PERCENT IS 'Percentage of Credit Card Commission';

ALTER TABLE ZDCL ADD(
    ZDCL_CALCINCLUDED NUMBER(1),
    ZDCL_UDF VARCHAR2(200 char)
);

COMMENT ON COLUMN ZDCL.ZDCL_CALCINCLUDED is '1 - indicates that the City Tax has to be calculated like it was an included tax'; 

COMMENT ON COLUMN ZDCL.ZDCL_UDF is 'User defined formula for the City Tax calculation';
