/************************************************************************************************************************
*
*
*  Date: September 22, 2016  12:14:46
*
*  This upgrade script was automatically generated by DBDiff for Oracle (www.dbdiff.com)
*
*  SOURCE DATABASE:  VERSION_8_10_1@V8
*  TARGET DATABASE:  VERSION_8_10_0@V8
*
*  This script should be executed as VERSION_8_10_0 in database VERSION_8_10_0@V8 except where otherwise noted.
*
*  IMPORTANT:  As with any script, you should always perform a sanity check before executing it in your target database.
*
*
************************************************************************************************************************/






/************************************************************************************************************************
*
*  tables
*
*  1091 tables were found in VERSION_8_10_1@V8
*  1073 tables were found in VERSION_8_10_0@V8
*
************************************************************************************************************************/




/************************************************************************************************************************
*
*  The following statements will drop tables that DO NOT exist in VERSION_8_10_1@V8
*
************************************************************************************************************************/


DROP TABLE VERSION_8_10_0_10;


/************************************************************************************************************************
*
*  The following statements will create tables that DO NOT exist in VERSION_8_10_0@V8
*
************************************************************************************************************************/


/*  YSGI  */
CREATE TABLE YSGI (
	 YSGI_ID	NUMBER(30)	NOT NULL
	,YSGI_YRES_ID	NUMBER(30)	NOT NULL
	,YSGI_XCMS_ID	NUMBER(30)	    NULL
	,YSGI_YRPE_ID	NUMBER(30)	    NULL
) 
TABLESPACE V8MAIN;

ALTER TABLE YSGI ADD (
	CONSTRAINT YSGI_PK
	PRIMARY KEY
	            (
	             YSGI_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;


/*  YRPE  */
CREATE TABLE YRPE (
	 YRPE_ID	NUMBER(30)	NOT NULL
	,YRPE_YRES_ID	NUMBER(30)	NOT NULL
	,YRPE_XCAC_ID	NUMBER(30)	NOT NULL
	,YRPE_XCACINDEX	NUMBER(3)	NOT NULL
	,YRPE_YAST_ID	NUMBER(30)	    NULL
	,YRPE_YBRC_ID	NUMBER(30)	    NULL
	,YRPE_YASG_ID	NUMBER(30)	    NULL
	,YRPE_XCMS_ID	NUMBER(30)	    NULL
	,YRPE_NAME	VARCHAR2(100)	    NULL
	,YRPE_ACTARRTIME	DATE	    NULL
	,YRPE_ACTDEPTIME	DATE	    NULL
	,YRPE_YRMS_ID	NUMBER(30)	    NULL
	,YRPE_YBED_ID	NUMBER(30)	    NULL
	,YRPE_DOCUMENTID	VARCHAR2(50)	    NULL
	,YRPE_XPDG_ID	NUMBER(30)	    NULL
	,YRPE_XMTY_ID	NUMBER(30)	    NULL
	,YRPE_MEMBERSHIPNUMBER	VARCHAR2(100)	    NULL
	,YRPE_NOSHOW	NUMBER(1) DEFAULT 0 	NOT NULL
)
TABLESPACE V8MAIN
;

ALTER TABLE YRPE ADD (
	CONSTRAINT YRPE_PK
	PRIMARY KEY
	            (
	             YRPE_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YRMA  */
CREATE TABLE YRMA (
	 YRMA_ID	NUMBER(30)	NOT NULL
	,YRMA_YRMS_ID	NUMBER(30)	NOT NULL
	,YRMA_XPDA_ID	NUMBER(30)	    NULL
	,YRMA_MIX	NUMBER(1) DEFAULT 0 	NOT NULL
	,YRMA_XPAC_ID	NUMBER(30)	    NULL
)
TABLESPACE V8CONF
;

ALTER TABLE YRMA ADD (
	CONSTRAINT YRMA_PK
	PRIMARY KEY
	            (
	             YRMA_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX);

/*  YPAS  */
CREATE TABLE YPAS (
	 YPAS_ID	NUMBER(30)	NOT NULL
	,YPAS_SHORTDESC	VARCHAR2(5)	NOT NULL
	,YPAS_LONGDESC	VARCHAR2(30)	NOT NULL
	,YPAS_XML	CLOB	NOT NULL
	,YPAS_DISPLORDER	NUMBER(4)	NOT NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YPAS ADD (
	CONSTRAINT YPAS_PK
	PRIMARY KEY
	            (
	             YPAS_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

ALTER TABLE YPAS ADD (
	CONSTRAINT SYS_C0038859
	UNIQUE
	       (
	        YPAS_SHORTDESC
	       )
	USING INDEX
	TABLESPACE V8MAIN
)
;

ALTER TABLE YPAS ADD (
	CONSTRAINT SYS_C0038860
	UNIQUE
	       (
	        YPAS_LONGDESC
	       )
	USING INDEX
	TABLESPACE V8MAIN
)
;

/*  YMPR  */
CREATE TABLE YMPR (
	 YMPR_ID	NUMBER(30)	NOT NULL
	,YMPR_YRES_ID	NUMBER(30)	NOT NULL
	,YMPR_YPRR_ID	NUMBER(30)	    NULL
	,YMPR_REMOVED	NUMBER(1) DEFAULT 0 	NOT NULL
	,YMPR_ORDER	NUMBER(3) DEFAULT 0 	NOT NULL
	,YMPR_XCED_ID	NUMBER(30)	NOT NULL
	,YMPR_TIMEADDED	DATE	NOT NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YMPR ADD (
	CONSTRAINT YMPR_PK
	PRIMARY KEY
	            (
	             YMPR_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YMPL  */
CREATE TABLE YMPL (
	 YMPL_ID	NUMBER(30)	NOT NULL
	,YMPL_XMTY_ID	NUMBER(30)	NOT NULL
	,YMPL_YPAC_ID	NUMBER(30)	NOT NULL
	,YMPL_MEMBBRINGSPACK	NUMBER(1)	NOT NULL
	,YMPL_PACKREQMEMB	NUMBER(1)	NOT NULL
)
TABLESPACE V8CONF;

ALTER TABLE YMPL ADD (
	CONSTRAINT YMPL_PK
	PRIMARY KEY
	            (
	             YMPL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YIRM  */
CREATE TABLE YIRM (
	 YIRM_ID	NUMBER(30)	NOT NULL
	,YIRM_DAY	DATE	NOT NULL
	,YIRM_YCAT_ID	NUMBER(30)	    NULL
	,YIRM_NEXT_YIRM_ID	NUMBER(30)	    NULL
	,YIRM_PREV_YIRM_ID	NUMBER(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YIRM ADD (
	CONSTRAINT YIRM_PK
	PRIMARY KEY
	            (
	             YIRM_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YGRF  */
CREATE TABLE YGRF (
	 YGRF_ID	NUMBER(30)	NOT NULL
	,YGRF_LINKTYPE	NUMBER(1)	NOT NULL
	,YGRF_LINK_ID	NUMBER(30)	NOT NULL
	,YGRF_ALONE	NUMBER(1) DEFAULT 0 	NOT NULL
	,YGRF_NEARROOM	NUMBER(1) DEFAULT 0 	NOT NULL
	,YGRF_NEARROOMIMPORTANCE	NUMBER(1) DEFAULT 0 	NOT NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YGRF ADD (
	CONSTRAINT YGRF_PK
	PRIMARY KEY
	            (
	             YGRF_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YGLT  */
CREATE TABLE YGLT (
	 YGLT_ID	NUMBER(30)	NOT NULL
	,YGLT_YASL_ID	NUMBER(30)	NOT NULL
	,YGLT_DATAROLE	NUMBER(1)	NOT NULL
	,YGLT_DATA	CLOB	NOT NULL
	,YGLT_TIMESTAMP	DATE	NOT NULL
	,YGLT_XCED_ID	NUMBER(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YGLT ADD (
	CONSTRAINT YGLT_PK
	PRIMARY KEY
	            (
	             YGLT_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YDRB  */
CREATE TABLE YDRB (
	 YDRB_ID	NUMBER(30)	NOT NULL
	,YDRB_YDET_ID	NUMBER(30)	NOT NULL
	,YDRB_XCAC_ID	NUMBER(30)	NOT NULL
	,YDRB_XCACINDEX	NUMBER(3)	NOT NULL
	,YDRB_YRMS_ID	NUMBER(30)	    NULL
	,YDRB_YBED_ID	NUMBER(30)	    NULL
	,YDRB_XCMS_ID	NUMBER(30)	    NULL
	,YDRB_NOOFPERSONS	NUMBER(3)	NOT NULL
	,YDRB_YIRM_ID	NUMBER(30)	    NULL
	,YDRB_YAST_ID	NUMBER(30)	    NULL
	,YDRB_YBRC_ID	NUMBER(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YDRB ADD (
	CONSTRAINT YDRB_PK
	PRIMARY KEY
	            (
	             YDRB_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YBRC  */
CREATE TABLE YBRC (
	 YBRC_ID	NUMBER(30)	NOT NULL
	,YBRC_YASL_ID	NUMBER(30)	NOT NULL
	,YBRC_NOOFPERSONS	NUMBER(30)	    NULL
	,YBRC_DISPLORDER	NUMBER(3)	    NULL
	,YBRC_DESC	VARCHAR2(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YBRC ADD (
	CONSTRAINT YBRC_PK
	PRIMARY KEY
	            (
	             YBRC_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YBED  */
CREATE TABLE YBED (
	 YBED_ID	NUMBER(30)	NOT NULL
	,YBED_SHORTDESC	VARCHAR2(10)	    NULL
	,YBED_LONGDESC	VARCHAR2(30)	    NULL
	,YBED_DISPLORDER	NUMBER(3)	    NULL
	,YBED_YCAT_ID	NUMBER(30)	NOT NULL
	,YBED_YRMS_ID	NUMBER(30)	    NULL
	,YBED_INACTIVE	NUMBER(1) DEFAULT 0 	NOT NULL
	,YBED_YHKS_ID	NUMBER(30)	    NULL
)
TABLESPACE V8CONF;

ALTER TABLE YBED ADD (
	CONSTRAINT YBED_PK
	PRIMARY KEY
	            (
	             YBED_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YBDA  */
CREATE TABLE YBDA (
	 YBDA_ID	NUMBER(30)	NOT NULL
	,YBDA_YBED_ID	NUMBER(30)	NOT NULL
	,YBDA_XPDA_ID	NUMBER(30)	    NULL
)
TABLESPACE V8CONF;

ALTER TABLE YBDA ADD (
	CONSTRAINT YBDA_PK
	PRIMARY KEY
	            (
	             YBDA_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YAVL  */
CREATE TABLE YAVL (
	 YAVL_ID	NUMBER(30)	NOT NULL
	,YAVL_YAST_ID	NUMBER(30)	NOT NULL
	,YAVL_XPDA_ID	NUMBER(30)	NOT NULL
	,YAVL_ONLY	NUMBER(1) DEFAULT 0 	NOT NULL
)
TABLESPACE V8CONF;

ALTER TABLE YAVL ADD (
	CONSTRAINT YAVL_PK
	PRIMARY KEY
	            (
	             YAVL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YAST  */
CREATE TABLE YAST (
	 YAST_ID	NUMBER(30)	NOT NULL
	,YAST_YASL_ID	NUMBER(30)	NOT NULL
	,YAST_NOOFPERSONS	NUMBER(30)	    NULL
	,YAST_DISPLORDER	NUMBER(3)	    NULL
	,YAST_DESC	VARCHAR2(30)	    NULL
)
TABLESPACE V8CONF;

ALTER TABLE YAST ADD (
	CONSTRAINT YAST_PK
	PRIMARY KEY
	            (
	             YAST_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YASL  */
CREATE TABLE YASL (
	 YASL_ID	NUMBER(30)	NOT NULL
	,YASL_LINKTYPE	NUMBER(1)	NOT NULL
	,YASL_LINK_ID	NUMBER(30)	NOT NULL
)
TABLESPACE V8CONF;

ALTER TABLE YASL ADD (
	CONSTRAINT YASL_PK
	PRIMARY KEY
	            (
	             YASL_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  YASG  */
CREATE TABLE YASG (
	 YASG_ID	NUMBER(30)	NOT NULL
	,YASG_YASL_ID	NUMBER(30)	NOT NULL
	,YASG_NAME	VARCHAR2(100)	NOT NULL
	,YASG_YAST_ID	NUMBER(30)	    NULL
	,YASG_YBRC_ID	NUMBER(30)	    NULL
	,YASG_XCMS_ID	NUMBER(30)	    NULL
)
TABLESPACE V8MAIN;

ALTER TABLE YASG ADD (
	CONSTRAINT YASG_PK
	PRIMARY KEY
	            (
	             YASG_ID
	            )
	USING INDEX
	TABLESPACE V8INDEX
)
;

/*  WSCS  */
CREATE TABLE WSCS (
	 WSCS_ID	NUMBER(30)	NOT NULL
	,WSCS_XCED_ID	NUMBER(30)	NOT NULL
	,WSCS_NAME	VARCHAR2(60)	NOT NULL
	,WSCS_SCREENNAME	VARCHAR2(60)	NOT NULL
	,WSCS_XML	CLOB	    NULL
)
TABLESPACE V8MAIN;


/*  VERSION_8_10_1_0  */
CREATE TABLE VERSION_8_10_1_0 (
	 DUMMY	NUMBER	    NULL
)
TABLESPACE V8MAIN;




/************************************************************************************************************************
*
*  The following statements will upgrade the COMMON tables
*
************************************************************************************************************************/


ALTER TABLE ZTXT MODIFY ( ZTXT_DATA BLOB);

ALTER TABLE ZOTA MODIFY ( ZOTA_HTTP_PWD VARCHAR2(100));

ALTER TABLE ZOTA ADD ( ZOTA_XMLIFC_COMP NUMBER(1) DEFAULT 0 NULL  );

ALTER TABLE ZINB MODIFY ( ZINB_DATA BLOB);

ALTER TABLE ZFST ADD ( ZFST_YCAD_ID NUMBER(30) NULL  );

ALTER TABLE ZDAT MODIFY ( ZDAT_DATA BLOB);

ALTER TABLE YRST ADD ( YRST_BEDCOUNT NUMBER(3) NULL  );

ALTER TABLE YRST ADD ( YRST_YBED_ID NUMBER(30) NULL  );

ALTER TABLE YRMS ADD ( YRMS_COUNT_YBED NUMBER(3) DEFAULT 0 NOT NULL  );

ALTER TABLE YRMS ADD ( YRMS_COUNTBED NUMBER(4) NULL  );

ALTER TABLE YRES ADD ( YRES_BED NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YRES ADD ( YRES_PAYPERGUEST NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE YPRR ADD ( YPRR_MINADULTAGE NUMBER(3) NULL  );

ALTER TABLE YPRR ADD ( YPRR_MAXADULTAGE NUMBER(3) NULL  );

ALTER TABLE YDET ADD ( YDET_BED_YCAT_ID NUMBER(30) NULL  );

ALTER TABLE YDET ADD ( YDET_NOOFPERSON NUMBER(3) DEFAULT 0 NOT NULL  );

ALTER TABLE XPDA ADD ( XPDA_IMPORTANCE NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_APPLBEDRES NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_MANDATORYBEDRES NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_ONLYFLAG NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_APPLROOM NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_IMPORTANCE NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE XPAC ADD ( XPAC_APPLBED NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE WTXT MODIFY ( WTXT_DATA BLOB);

ALTER TABLE WFTP MODIFY ( WFTP_PASSWORD VARCHAR2(100));

ALTER TABLE SMBD MODIFY ( SMBD_ADULTS_D NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_ADULTS_M NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_ADULTS_Y NUMBER(9,2));

ALTER TABLE SMBD MODIFY ( SMBD_CHILDS_D NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_CHILDS_M NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_CHILDS_Y NUMBER(9,2));

ALTER TABLE SMBD MODIFY ( SMBD_ROOMS_OCC_D NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_ROOMS_OCC_M NUMBER(7,2));

ALTER TABLE SMBD MODIFY ( SMBD_ROOMS_OCC_Y NUMBER(9,2));

ALTER TABLE SHIS ADD ( SHIS_BEDRESERVATION NUMBER(1) DEFAULT 0 NOT NULL  );

ALTER TABLE SHIS ADD ( SHIS_YBED_ID NUMBER(30) NULL  );
	
ALTER TABLE SHIS ADD ( SHIS_YBED_SHORTDESC VARCHAR2(30) NULL  );

ALTER TABLE SHIS ADD ( SHIS_MAIN_XCMS_ID NUMBER(30) NULL  );

ALTER TABLE SHIS ADD ( SHIS_BED_YCAT_ID NUMBER(30) NULL  );

ALTER TABLE SHIS ADD ( SHIS_BED_YCAT_SHORTDESC VARCHAR2(30) NULL  );

ALTER TABLE MMOD MODIFY ( MMOD_HTTP_PROXYPWD VARCHAR2(100));

ALTER TABLE MMOD MODIFY ( MMOD_HTTP_PWD VARCHAR2(100));

ALTER TABLE MMOD MODIFY ( MMOD_AUTH_PWD VARCHAR2(100));

COMMENT ON COLUMN XPAC.XPAC_APPLROOM IS 'Applies to room as fixed attribute (instead of defined by reservations occupying it)';

COMMENT ON COLUMN XPAC.XPAC_ONLYFLAG IS 'Allows to set "only" flag for attribute values';

COMMENT ON COLUMN XPAC.XPAC_MANDATORYBEDRES IS 'Mandatory for bed reservations';

COMMENT ON COLUMN XPDA.XPDA_IMPORTANCE IS '0=information only 1=use if possible 2=mandatory';

COMMENT ON COLUMN XPAC.XPAC_APPLBEDRES IS 'Applies to bed reservations';

COMMENT ON COLUMN XPAC.XPAC_IMPORTANCE IS '0=information only 1=use if possible 2=mandatory';

COMMENT ON COLUMN YRMS.YRMS_COUNT_YBED IS 'count of ybed records';

COMMENT ON COLUMN ZFST.ZFST_YCAD_ID IS 'Link to Hotel Segment for which the folio style is available';

COMMENT ON COLUMN ZOTA.ZOTA_XMLIFC_COMP IS 'Indicates, whether OTA XML Interface uploads message from IXMLDocument text attribute or directly from the OTA XML object. Makes difference in handling of german umlaute -> UTF-8 versus ANSI'; 

COMMENT ON COLUMN XPAC.XPAC_APPLBED IS 'Applies to beds';

COMMENT ON TABLE YBED IS 'Beds';

COMMENT ON TABLE YDRB IS 'Reservation Detail Room and Bed link';

COMMENT ON TABLE YASL IS 'Attribute Set List';

COMMENT ON COLUMN YASL.YASL_LINKTYPE IS '0=reservation yres 1=group header ygrp 2=imaginary room yirm';

COMMENT ON TABLE YAST IS 'Attribute Set';

COMMENT ON TABLE YAVL IS 'Attribute Values';

COMMENT ON COLUMN YAVL.YAVL_ONLY IS '1=accept room with reservations having occupants with the same attribute only'; 

COMMENT ON TABLE YRMA IS 'Room Attributes';

COMMENT ON COLUMN YRMA.YRMA_MIX IS '0=forbidden 1=allowed (Makes sense if multiple xpda are linked or no xpda is linked and then xpac is used)';

COMMENT ON COLUMN YRMA.YRMA_XPAC_id IS 'Needed if we want to use yrma_mix without yrma_xpda_id';

COMMENT ON TABLE YBDA IS 'Bed Attributes';

COMMENT ON TABLE YGRF IS 'Group Requirement flags';

COMMENT ON COLUMN YGRF.YGRF_LINKTYPE IS '1=YAST 2=YASL';

COMMENT ON COLUMN YGRF.YGRF_ALONE IS '1=would like to stay alone 2=must stay alone';

COMMENT ON COLUMN YGRF.YGRF_NEARROOM IS '1=same room 2=next room 3=near room(?) 4=same floor 5=same building';

COMMENT ON COLUMN YGRF.YGRF_NEARROOMIMPORTANCE IS '1=wish 2=one lower level is accepted 3=must have';

COMMENT ON TABLE YIRM IS 'Imaginary Room';

COMMENT ON TABLE YBRC IS 'Room Collective';

COMMENT ON TABLE YASG IS 'Attribute Set Guest';

COMMENT ON TABLE WSCS IS 'Screen settings';

COMMENT ON COLUMN WSCS.WSCS_XCED_ID IS 'null means public';

COMMENT ON TABLE YMPR IS 'Reservation manual promotion';

COMMENT ON COLUMN YPRR.YPRR_MINADULTAGE IS 'minimum adult age rule is appied in case pax xontrol "adult by age" is selected';

COMMENT ON COLUMN YPRR.YPRR_MAXADULTAGE IS 'maximum adult age rule is appied in case pax xontrol "adult by age" is selected';

COMMENT ON COLUMN YPRR.YPRR_XCAC_id IS ' xcac_id=0 : no pax control,  xcac_id=-1 : ADULT Control , xcac_id=-2 : ADULT BY AGE Control ,  xcac_id>0 : xcac_id Child control';

COMMENT ON COLUMN YRES.YRES_CALCSTATUS IS '0=expected(future) 1=expected today 2=checked in today 3=in house 4=exp dep today 5=departed today 6=history 7=day use exp 8=day use in house 9=day use history 10=waitlist 11=offer 12=cancelled 13=noshow 14=voucher template 15=day use exp today 16=day use departed today';

COMMENT ON TABLE YMPL IS 'Membership type - Package link';

COMMENT ON COLUMN YMPL.YMPL_MEMBBRINGSPACK IS '1=Membership brings package';

COMMENT ON COLUMN YMPL.YMPL_PACKREQMEMB IS '1=Membership is mandatory for package';

COMMENT ON TABLE YRPE IS 'Persons';

COMMENT ON TABLE YGLT IS 'Reservation bed Guest List Text';

COMMENT ON TABLE YPAS IS 'Predefined attribute set (for bed reservations)';

COMMENT ON TABLE YSGI IS 'Standalone Guest Id for Interface';

UPDATE YDET SET YDET_NOOFPERSON=YDET_ADULTNO+NVL((SELECT SUM(YRGL_NUMBER) FROM YRGL WHERE YRGL_YDET_ID=YDET_ID),0);

UPDATE YRMS SET YRMS_COUNT_YBED=(SELECT COUNT(1) FROM YBED WHERE YBED_YRMS_ID=YRMS_ID);

UPDATE YRMS SET YRMS_COUNTBED= NVL(DECODE(YRMS_COUNT_YBED,0,YRMS_MAX_BEDS,YRMS_COUNT_YBED),0);

UPDATE XCNO SET XCNO_XCNC_ID=(SELECT XCNC_ID FROM XCNC WHERE XCNC_TABLELINKEDTO=12 AND ROWNUM=1) WHERE XCNO_TABLELINKEDTO='ZFAC';

exec checkOneSequence('YRES','YRES_ID','SEQ_YSGI');

insert into ysgi (ysgi_id,ysgi_yres_id,ysgi_xcms_id) ( 
select
   yres_id 
  ,yres_id
  ,yres_xcms_id
from yres
where yres_bed=0
and not exists (select 1 from ysgi where ysgi_yres_id=yres_id)
);

insert into ysgi (ysgi_id,ysgi_yres_id,ysgi_xcms_id,ysgi_yrpe_id) ( 
select
   seq_ysgi.nextval 
  ,yres_id
  ,yrpe_xcms_id
  ,yrpe_id 
from yres,yrpe
where yres_xcms_id is not null
and yres_bed=1
and yrpe_yres_id=yres_id 
and yrpe_xcms_id is not null
and not exists (select 1 from ysgi where ysgi_yres_id=yres_id)
);




/************************************************************************************************************************
*
*  sequences
*
*  The following statements will upgrade the sequences
*
************************************************************************************************************************/

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

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

CREATE SEQUENCE SEQ_YASL INCREMENT BY 1 START WITH 1001 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE SEQUENCE SEQ_YSGI INCREMENT BY 1 START WITH 1000 MAXVALUE 9999999999999999999999999999 NOCYCLE NOCACHE NOORDER;







--- End of DBDiff for Oracle --- Elapsed Time: 00:13:52 ---


