--UPDATE FROM 8.9.6.2 TO 8.9.6.3


DROP TABLE VERSION_8_9_6_2;


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

ALTER TABLE WCIP ADD WCIP_INTERFACES VARCHAR2(20);

INSERT INTO WUSR (WUSR_ID,WUSR_XCMS_ID,WUSR_WEGR_ID,WUSR_NAME,WUSR_VALUE)
SELECT SEQ_WUSR.NEXTVAL,WUSR_XCMS_ID,WUSR_WEGR_ID
  ,WUSR_NAME||'Access'
  ,WUSR_VALUE
FROM WUSR w
WHERE  
w.WUSR_NAME IN (
  'Configuration'
 ,'ConfigurationCashiering'
 ,'ConfigurationReservations'
 ,'ConfigurationReservationsRate Management'
 ,'ConfigurationUsers'
 ,'ConfigurationMiscellaneous'
 ) 
AND NOT EXISTS (
  SELECT 1 
  FROM WUSR ww
  WHERE (w.WUSR_XCMS_ID=ww.WUSR_XCMS_ID or w.WUSR_XCMS_ID IS NULL) 
  AND (w.WUSR_WEGR_ID=ww.WUSR_WEGR_ID or w.WUSR_WEGR_ID IS NULL) 
  AND ww.WUSR_NAME=w.WUSR_NAME||'Access' 
);

ALTER TABLE ZPOS ADD ZPOS_FLAG NUMBER(1);

COMMENT ON COLUMN ZPOS.ZPOS_FLAG IS '1=Inverted by billing period';

create TABLE WSYN (
    WSYN_ID NUMBER(30) not null
   ,WSYN_PROVIDER NUMBER(30) not null
   ,WSYN_TABLENAME VARCHAR2(4) not null
   ,WSYN_TABLE_ID NUMBER(30) not null
   ,WSYN_THIRDPARTY_ID VARCHAR2(30)
   ,WSYN_DOWNLOADTIMESTAMP DATE
   ,CONSTRAINT WSYN_PK PRIMARY KEY (WSYN_ID) USING INDEX TABLESPACE v8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE WSYN IS 'sync table between suite8 / third parties resources';

COMMENT ON COLUMN WSYN.WSYN_ID IS 'ID';

COMMENT ON COLUMN WSYN.WSYN_PROVIDER IS 'Provider identif: 0 - Micros/Alshaya';

COMMENT ON COLUMN WSYN.WSYN_TABLENAME IS 'Suite8 TABLE name';

COMMENT ON COLUMN WSYN.WSYN_TABLE_ID IS 'Suite8 TABLE ID';

COMMENT ON COLUMN WSYN.WSYN_THIRDPARTY_ID IS 'Thirdparty ID';

ALTER TABLE AACC ADD AACC_CREDSUMM NUMBER(30,4);

COMMENT ON COLUMN AACC.AACC_CREDSUMM IS 'Credit summ';
                 
ALTER TABLE AACC ADD AACC_CREDIT_FLAG NUMBER(1) default 0;

COMMENT ON COLUMN AACC.AACC_CREDIT_FLAG IS 'Credit flag';

ALTER TABLE AACC ADD AACC_SITE_NUMBER VARCHAR2(20);

COMMENT ON COLUMN AACC.AACC_SITE_NUMBER IS 'Site NUMBER';

ALTER TABLE WMWP ADD WMWP_PARA_DESC VARCHAR2(30);

COMMENT ON COLUMN WMWP.WMWP_PARA_DESC IS 'readable description of the parameter';

ALTER TABLE ZDCO ADD ZDCO_SECURITYDEPOSIT NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE YDEP ADD YDEP_ZCUR_ID NUMBER(30);

ALTER TABLE YDEP ADD YDEP_ZDCO_ID NUMBER(30);

ALTER TABLE YDEP ADD YDEP_COMMENT VARCHAR2(60);

ALTER TABLE YCDR ADD YCDR_ZCUR_ID NUMBER(30);

ALTER TABLE YCDR ADD YCDR_ZDCO_ID NUMBER(30);

create TABLE XREM (
    XREM_ID NUMBER(30) not null
   ,XREM_XMTY_ID NUMBER(30) not null
   ,XREM_LEVEL VARCHAR2(30)
   ,XREM_INFO VARCHAR2(120)   
   ,CONSTRAINT XREM_PK PRIMARY KEY (XREM_ID) USING INDEX TABLESPACE v8INDEX) TABLESPACE V8CONF;

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

COMMENT ON TABLE XREM IS 'Reminder popup for membership types';

COMMENT ON COLUMN XREM.XREM_ID IS 'ID';

COMMENT ON COLUMN XREM.XREM_XMTY_ID IS 'Link to membership type';

COMMENT ON COLUMN XREM.XREM_INFO IS 'Reminder Information';

ALTER TABLE ZFAG ADD ZFAG_DEPDATE_DAY NUMBER(2);

COMMENT ON COLUMN ZFAG.ZFAG_DEPDATE_DAY IS 'the day of the month on which the FA should be checked out';

ALTER TABLE YLSP ADD YLSP_PROFORMATIME DATE;

ALTER TABLE ZDCO ADD ZDCO_SECDEPRETURN_ZDCO_ID NUMBER(30);

COMMENT ON COLUMN ZDCO.ZDCO_SECDEPRETURN_ZDCO_ID IS 'Security deposit return department code';

ALTER TABLE ZTTL ADD ZTTL_FORM NUMBER(1);

COMMENT ON COLUMN ZTTL.ZTTL_FORM IS '0 = Full-Form, 1 = Abbreviated Form';

ALTER TABLE WPCA ADD WPCA_THAIWSNUMBER VARCHAR2(5 char);

COMMENT ON COLUMN WPCA.WPCA_THAIWSNUMBER IS 'Workstation NUMBER for Abbreviated Form Thai Tax';

ALTER TABLE ZTTL MODIFY ZTTL_TAXNUMBER VARCHAR2(20 CHAR);

ALTER TABLE ZFAC ADD ZFAC_STATUS NUMBER(1);

COMMENT ON COLUMN ZFAC.ZFAC_STATUS IS '0 =  FA Open for change, 1 = FA  Closed for change';

ALTER TABLE ZTTL ADD ZTTL_WPCA_ID NUMBER(30);

ALTER TABLE YRES ADD YRES_SPLIT_CONFIRMATIONNO VARCHAR2(10);

create TABLE ZAML (
   ZAML_ID NUMBER(30) not null
  ,ZAML_WACT_ID NUMBER(30)
  ,ZAML_YRMS_ID NUMBER(30)
  ,ZAML_YHAT_ID NUMBER(30)
  ,ZAML_RESOLVED_YHKS_ID NUMBER(30)
  ,CONSTRAINT ZAML_PK PRIMARY KEY (ZAML_ID) USING INDEX TABLESPACE v8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE ZAML IS 'Link between attendants and tasks created for them on the attendant plan';

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

ALTER TABLE YBOM ADD YBOM_STATUS NUMBER(1);

COMMENT ON COLUMN YBOM.YBOM_STATUS IS '0-Not closed; 1-Booking closed';

ALTER TABLE WATG DROP COLUMN WATG_ZFAC;

COMMENT ON COLUMN XCLR.XCLR_TYPE IS '1-Spouse, 2-next geneneration (descendant), 3-prev gen (predecessor), 4-siblings, 5-partners, 6-Relative, 7-Distant Relative | for parents should be 3, for children 2';

ALTER TABLE XCLR ADD XCLR_LEVEL NUMBER(1);

COMMENT ON COLUMN XCLR.XCLR_LEVEL IS '0 - parents/childs; 1 - grandparents/grandchilds';

ALTER TABLE ZOTA ADD ZOTA_INCL_ADD_TO_RATE NUMBER(1);

COMMENT ON COLUMN ZOTA.ZOTA_INCL_ADD_TO_RATE IS 'indicates whether ADD to rate packages should be uploaded within the reservation rate';

ALTER TABLE MQU1 ADD MQU1_UNIQUE_MSG_ID VARCHAR2(200 CHAR); 

COMMENT ON COLUMN MQU1.MQU1_UNIQUE_MSG_ID IS 'new field for unique identify of a message reqeust to avoid duplicates if CRS resends message';

ALTER TABLE MCFG ADD MCFG_IFC_IP VARCHAR2(20) null;

COMMENT ON COLUMN MCFG.MCFG_IFC_IP IS 'Local IP address of the machine on which the interface IS installed';

DELETE FROM WFLS WHERE (SELECT WUSS_VALUE FROM WUSS WHERE WUSS_NAME LIKE 'v8lictype') <> 1 AND (SELECT COUNT(*) FROM WFLS) < 100;

DELETE FROM WLIC WHERE WLIC.WLIC_LICENSENAME IN 
(
'v8pms',
'Ticketing',  
'Opus2YieldIFC',
'Promotions',
'TourOperatorInvoice',
'BillingComponent',
'CCMMCInterface',
'CRS ONE WAY - Suite 8 Channel Manager',
'CRS TWO WAY - Suite 8 Channel Manager',
'CRS ONE WAY - MFNET ORS',
'CRS TWO WAY - MFNET ORS',
'AddressDoctorIntegration',
'MAILING',
'Synchronization',
'AVSRegCard',
'BILL_VIEW',
'CHECK_IN_OUT',
'EFT_SYSTEM',
'ENHANCED_POSTING',
'FULL_CAS',
'KEY_SYSTEM',
'PARTIAL_CAS',
'CHECK_DETAILS',
'REMOTE_CHECK_OUT',
'SIMPLE_POSTING',
'TEXT_MESSAGE',
'VIRTUAL_NUMBERS',
'VOICEMAIL',
'WAKEUP',
'blocks'
);
