--UPDATE FROM 8.9.6.0 TO 8.9.6.1


DROP TABLE VERSION_8_9_6_0;


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

ALTER TABLE YCEP ADD YCEP_yres_ID NUMBER(30);

CREATE TABLE YCEF (
   YCEF_ID NUMBER(30) NOT NULL  
  ,YCEF_YCEP_ID NUMBER(30) NOT NULL
  ,YCEF_YFXC_SCRID NUMBER(30) NOT NULL 
  ,CONSTRAINT YCEF_PK PRIMARY KEY (YCEF_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
   
CREATE SEQUENCE SEQ_YCEF INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YCEF IS 'Reservation Calendar Event Fixed Charge Link';

ALTER TABLE YCEV ADD YCEV_YPAC_ID NUMBER(30);

ALTER TABLE YCEV ADD YCEV_USEPACKAGE NUMBER(1);

COMMENT ON COLUMN YCEV.YCEV_USEPACKAGE IS 'Link Package to Calendar Event';

ALTER TABLE YCEP ADD YCEP_YPAC_ID NUMBER(30);

COMMENT ON COLUMN YCEP.YCEP_YPAC_ID IS 'Linked Package id';

CREATE TABLE YCPC (
   YCPC_ID NUMBER(30) NOT NULL  
  ,YCPC_YCEP_ID NUMBER(30) NOT NULL
  ,YCPC_XCAC_ID NUMBER(30)
  ,YCPC_TYPE NUMBER(1)
  ,YCPC_COUNT NUMBER(30)
  ,YCPC_PRICE NUMBER(30, 4)
  ,YCPC_AMOUNT NUMBER(30,4)
  ,CONSTRAINT YCPC_PK PRIMARY KEY (YCPC_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;
   
CREATE SEQUENCE SEQ_YCPC INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YCPC IS ' Calendar Event Participant - Age Category wise split details';

COMMENT ON COLUMN YCPC.YCPC_YCEP_ID IS 'Linked to ycep';

COMMENT ON COLUMN YCPC.YCPC_XCAC_ID IS 'Linked to xcac Child Categories value 0 is for Adults';

COMMENT ON COLUMN YCPC.YCPC_TYPE IS '0.Children 1.Adult';  

COMMENT ON COLUMN YCPC.YCPC_COUNT IS 'Number of persons';

COMMENT ON COLUMN YCPC.YCPC_PRICE IS 'Ticket Price';

COMMENT ON COLUMN YCPC.YCPC_AMOUNT IS 'Ticket Amount';

ALTER TABLE YPAL ADD YPAL_MASTER_YPAC_ID NUMBER(30,0);

ALTER TABLE YCEP ADD YCEP_REVENUE_POSTDATE DATE;

COMMENT ON COLUMN YCEP.YCEP_REVENUE_POSTDATE IS 'revenue post date';

CREATE TABLE WMWS (
   WMWS_ID NUMBER(30) NOT NULL
  ,WMWS_SHORTDESC VARCHAR2(3)
  ,WMWS_LONGDESC VARCHAR2(60)
  ,WMWS_REMARKS VARCHAR2(2000)
  ,WMWS_XCED_ID NUMBER(30)
  ,WMWS_WSQL_ID NUMBER(30)
  ,WMWS_TIMESTAMP DATE
  ,CONSTRAINT WMWS_PK PRIMARY KEY (WMWS_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WMWS IS 'Saved Mailing Wizard Settings';

CREATE TABLE WMWZ (
   WMWZ_ID NUMBER(30) NOT NULL
  ,WMWZ_WMWS_ID NUMBER(30)
  ,WMWZ_WMWD_ID NUMBER(30)
  ,WMWZ_WMWD_INCL NUMBER(1)
  ,WMWZ_WMWG_ID NUMBER(30)
  ,WMWZ_WMWF_ID NUMBER(30)
  ,CONSTRAINT WMWZ_PK PRIMARY KEY (WMWZ_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WMWZ IS 'Saved Mailing Wizard Settings link table';

ALTER TABLE WMWE DROP COLUMN WMWE_WMWC_ID;

ALTER TABLE WMWE ADD WMWE_WMWS_ID NUMBER(30);

ALTER TABLE WSQP ADD WSQP_INCLUDEPARAM NUMBER(1);

COMMENT ON COLUMN WSQP.WSQP_INCLUDEPARAM IS '1 = Parameter is included, 0 = excluded';

CREATE TABLE WMWX (
   WMWX_ID NUMBER(30) NOT NULL
  ,WMWX_WMWP_ID NUMBER(30)
  ,WMWX_WMWP_INCL NUMBER(1)
  ,WMWX_WMWE_ID NUMBER(30)
  ,WMWX_TIMESTAMP DATE
  ,WMWX_PARAMCONTENT VARCHAR2(2000)
  ,CONSTRAINT WMWX_PK PRIMARY KEY (WMWX_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8MAIN;

COMMENT ON TABLE WMWX IS 'Mailing Wizard SQL execution detail Log';

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

ALTER TABLE WSQP ADD WSQP_WMWP_ID NUMBER(30);

COMMENT ON COLUMN WSQP.WSQP_WMWP_ID IS 'Link to the parameters of the mailing that generated the sql with this parameter  ';

ALTER TABLE WNAE ADD WNAE_REPORT_TYPE NUMBER(2);

COMMENT ON COLUMN WNAE.WNAE_REPORT_TYPE IS 'Indicates the report type for ideas 1 = CurrentTotal, 2 = CurrentTotalRT, 3 = CurrentSegRT, 4 = PastTotal, 5 = PastTotalRT, 6 = PastSegRT';

ALTER TABLE YCEP ADD YCEP_RESCREATED NUMBER(1);

COMMENT ON COLUMN YCEP.YCEP_RESCREATED IS 'indicates this participant is created to link to a reservation ';

ALTER TABLE WLOP MODIFY WLOP_TYPE NUMBER(2);

ALTER TABLE YHKS MODIFY YHKS_GREENSTATUSDAYS NUMBER( 3);

COMMENT ON COLUMN YHKS.YHKS_GREENSTATUS IS 'One record in YHKS to be identified for Green Status (not used too long and review needed)';

COMMENT ON COLUMN YHKS.YHKS_GREENSTATUSDAYS IS 'Number of days for Green Status (not used too long and review needed)';

ALTER TABLE MMOD ADD MMOD_INCL_ADD_TO_RATE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_INCL_ADD_TO_RATE IS 'indicates whether add to rate packages should be uploaded within the reservation rate';

ALTER TABLE MAVL ADD MAVL_UPLOAD_RATE NUMBER(1);

ALTER TABLE MAVL ADD MAVL_UPLOAD_RAVL NUMBER(1);

COMMENT ON COLUMN MAVL.MAVL_UPLOAD_RATE IS 'indicates whether the rate code is used for rate upload';

COMMENT ON COLUMN MAVL.MAVL_UPLOAD_RAVL IS 'indicates whether the rate code is used for ravl upload';

UPDATE MAVL SET MAVL_UPLOAD_RATE = 1, MAVL_UPLOAD_RAVL = 1;

ALTER TABLE XCCS ADD XCCS_CARD_ID VARCHAR2(100 CHAR);

ALTER TABLE XCCS ADD XCCS_CARD_ID_TYPE NUMBER(1);

COMMENT ON COLUMN XCCS.XCCS_CARD_ID IS 'CC token used by Best Western';

COMMENT ON COLUMN XCCS.XCCS_CARD_ID_TYPE IS 'Source of Token 1 = local interface, 2 = CRS';

ALTER TABLE MMOD ADD MMOD_ALLOW_ZERO_PRICE_PACKAGE NUMBER(1);

COMMENT ON COLUMN MMOD.MMOD_ALLOW_ZERO_PRICE_PACKAGE IS 'indicates whether an interface accepts zero rates for packages. If not the price from Suite8 will be used';

ALTER TABLE WPCA MODIFY WPCA_BARCODEREADERPORT NUMBER(2);

COMMENT ON COLUMN WRPS.WRPS_TYPE IS '0 = report  1 = text format template 2 = Cube Reports templates 3 = Html templates 4= FastReport templates 5 = Word Merge templates ';

ALTER TABLE YHAT ADD YHAT_XCED_ID NUMBER(30);

COMMENT ON COLUMN YHAT.YHAT_XCED_ID IS 'Attendant link to user';

COMMENT ON TABLE WRTR IS ' Report and HTML Translation  Loading all static strings from Reports and HTMLs.';

UPDATE MMOD SET MMOD_INCL_ADD_TO_RATE = 1;

CREATE TABLE YLSP (
   YLSP_ID NUMBER(30) NOT NULL
  ,YLSP_YRES_ID NUMBER(30) NOT NULL 
  ,YLSP_START DATE NOT NULL
  ,YLSP_END DATE NOT NULL
  ,YLSP_MANUAL NUMBER(1) DEFAULT 0 NOT NULL
  ,YLSP_AMOUNT NUMBER(30,4) NOT NULL
  ,YLSP_FAMOUNT NUMBER(30,4)
  ,YLSP_PARTIAL NUMBER(1) DEFAULT 0 NOT NULL
  ,YLSP_ZINV_ID NUMBER(30)
  ,YLSP_POSTDATE DATE
  ,YLSP_ZWIN_ID NUMBER(30)
  ,CONSTRAINT YLSP_PK PRIMARY KEY (YLSP_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;
    
CREATE SEQUENCE SEQ_YLSP INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

COMMENT ON TABLE YLSP IS 'Lease period';

CREATE TABLE YRGV (
   YRGV_ID NUMBER(30) NOT NULL
  ,YRGV_YRMS_ID NUMBER(30) NOT NULL
  ,YRGV_FROM DATE NOT NULL
  ,YRGV_TO DATE NOT NULL
  ,YRGV_VALUE NUMBER(30,4) NOT NULL 
  ,CONSTRAINT YRGV_PK PRIMARY KEY (YRGV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE YRGV IS 'Government room value evaluation for tax exempt part of room charge';

CREATE TABLE YCGV (
   YCGV_ID NUMBER(30) NOT NULL
  ,YCGV_YCAT_ID NUMBER(30) NOT NULL
  ,YCGV_FROM DATE NOT NULL
  ,YCGV_TO DATE NOT NULL
  ,YCGV_VALUE NUMBER(30,4) NOT NULL 
  ,CONSTRAINT YCGV_PK PRIMARY KEY (YCGV_ID) USING INDEX TABLESPACE V8INDEX
) TABLESPACE V8CONF;

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

COMMENT ON TABLE YCGV IS 'Government room typevalue evaluation for tax exempt part of room charge';

ALTER TABLE YRCH ADD YRCH_ASB NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRCH.YRCH_ASB IS 'Apartment style billing';

ALTER TABLE YRCH ADD YRCH_POSTINGRHYTHM NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRCH.YRCH_POSTINGRHYTHM IS '0=Weekly 1=Monthly 2=Yearly 3=CustomDays';

ALTER TABLE YRCH ADD YRCH_POSTINGRHYTHMDAYS NUMBER(3);

COMMENT ON COLUMN YRCH.YRCH_POSTINGRHYTHMDAYS IS 'Number of days for "Custom" posting rhythm';

ALTER TABLE YRCH ADD YRCH_POSTINGRHYTHMSTART NUMBER(1) DEFAULT 0 NOT NULL;

COMMENT ON COLUMN YRCH.YRCH_POSTINGRHYTHMSTART IS '0=Check in date 1=First day of posting rhythm';

ALTER TABLE YRCH ADD YRCH_TAXEXEMPT_ZDCO_ID NUMBER(30);

COMMENT ON COLUMN YRCH.YRCH_TAXEXEMPT_ZDCO_ID IS 'Department code for tax exempt part of room charge';

ALTER TABLE YFOR ADD YFOR_CATEGORY NUMBER(1) DEFAULT 0;

INSERT INTO WUSS(WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE)
SELECT SEQ_WUSS.NEXTVAL, 0, 'MenuCourseSeparator', '***' 
FROM DUAL
WHERE NOT EXISTS (SELECT WUSS_ID FROM WUSS WHERE WUSS_NAME='MenuCourseSeparator');

commit;

INSERT INTO WUSS(WUSS_ID, WUSS_XCMS_ID, WUSS_NAME, WUSS_VALUE)
SELECT SEQ_WUSS.NEXTVAL, 0, 'TranslationMethod', '0' 
FROM DUAL
WHERE NOT EXISTS (SELECT WUSS_ID FROM WUSS WHERE WUSS_NAME='TranslationMethod');

commit;

COMMENT ON COLUMN WANT.WANT_NOTES_CLOB IS 'Obsolete field, not used anymore';

COMMENT ON COLUMN WANT.WANT_NOTES IS 'Actual note';
