--UPDATE FROM 8.13.2.0 TO 8.14.0.0
 	
DROP TABLE VERSION_8_13_2_0;


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


alter table wmlg add wmlg_time_update_db date;
update wmlg set wmlg_time_update_db=wmlg_time_update where wmlg_time_update_db is null;
alter table wmlg modify wmlg_time_update_db not null;


delete from wlic where wlic_longdesc = 'CRS ONE WAY - Alean';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - Alean';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - BOOKING_COM';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - BOOKING_COM';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - BookingCOM';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - BookingCOM';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - EXPEDIA';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - EXPEDIA';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - HILTON';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - IDEAL';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - MAGIC LIFE';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - MAGIC LIFE';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - myfidelio.net';
delete from wlic where wlic_longdesc = 'CRS ONE WAY - ReservIT';
delete from wlic where wlic_longdesc = 'CRS TWO WAY - ReservIT';

alter table ybom add ybom_postinginna number(1);
comment on column ybom.ybom_postinginna is '1 - booking should be posted in NA process';

alter table mmod add mmod_use_cc_tokenization number(1) default 0;
comment on column mmod.mmod_use_cc_tokenization  is 'Indicates whether real tokenization is used for credit card handling';

alter table xced add xced_hash varchar2(60);

create table wcfg (
   wcfg_id number(30) not null
  ,wcfg_text varchar2(200)
  ,constraint wcfg_pk primary key (wcfg_id) using index tablespace v8index	
) tablespace v8conf;

create sequence seq_wcfg increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;  
comment on table wcfg is 'Client file hash group';

create table wcfh (
   wcfh_id number(30) not null
  ,wcfh_fileName varchar2(2000) not null
  ,wcfh_hash varchar2(60) not null
  ,wcfh_xced_id number(30) not null
  ,wcfh_timestamp date not null
  ,wcfh_wpca_id number(30) not null
  ,wcfh_wcfg_id number(30) not null
  ,constraint wcfh_pk primary key (wcfh_id) using index tablespace v8index	
) tablespace v8conf;
  
create sequence seq_wcfh increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;  
comment on table wcfh is 'Client file hash';

alter table wcfh add wcfh_wcfg_id number(30);
alter table wcfh modify wcfh_wcfg_id not null;
alter table wcfh add wcfh_wcfg_id number(30);
alter table wcfh modify wcfh_wcfg_id not null;

CREATE TABLE XPMN (
    XPMN_ID NUMBER(30) NOT NULL
   ,XPMN_WRCD_ID NUMBER(30) NOT NULL
   ,XPMN_XCOU_ID NUMBER(30) NOT NULL
   ,CONSTRAINT XPMN_PK PRIMARY KEY (XPMN_ID ) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;
   
CREATE SEQUENCE SEQ_XPMN INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;
COMMENT ON TABLE XPMN IS 'PASSPORT MANDATORY NATIONS';
COMMENT ON COLUMN XPMN.XPMN_WRCD_ID IS 'PRE CheckIn config ID';
COMMENT ON COLUMN XPMN.XPMN_XCOU_ID IS 'National config ID';

alter table xcap add xcap_rrnnumber varchar2(30);
alter table xcap add xcap_initialauth number (1);
alter table xcap add xcap_addlauthamount number(30,2);
alter table xcap add xcap_ifcmessage varchar2(200);
alter table xcap add xcap_successstatus number(1) default 1 not null;
alter table xcap add xcap_xced_id number(30);
alter table xcap add xcap_terminal varchar2(60);

comment on column xcap.xcap_rrnnumber is 'Stores RRN Number received from EFT';
comment on column xcap.xcap_initialauth is '1.Intial Autorization ';
comment on column xcap.xcap_addlauthamount is 'Authoriazation Top up amount';
comment on column xcap.xcap_ifcmessage is 'Clear text received from EFT';
comment on column xcap.xcap_successstatus is 'Success status 1.Success 2. Failed';
comment on column xcap.xcap_xced_id is 'User ID';
comment on column xcap.xcap_terminal is 'Terminal/Workstation name';

ALTER TABLE WRCD ADD WRCD_ALLOWEDPRECHECKINTIME NUMBER(5);
COMMENT ON COLUMN WRCD.WRCD_ALLOWEDPRECHECKINTIME IS 'Hours in advance precheckin can be done';

ALTER TABLE WRCD ADD WRCD_ROOMASSIGNMENT NUMBER(1);
COMMENT ON COLUMN WRCD.WRCD_ROOMASSIGNMENT IS 'Assign Room during Pre Check in'; 

ALTER TABLE WRCD ADD WRCD_AUTOROOMASSIGNMENT NUMBER(1);
COMMENT ON COLUMN WRCD.WRCD_AUTOROOMASSIGNMENT IS 'Auto Assign Room during Pre Check in'; 

ALTER TABLE WRCD ADD WRCD_DISPLAYREGISTRATIONCARD NUMBER(1);
COMMENT ON COLUMN WRCD.WRCD_DISPLAYREGISTRATIONCARD IS 'Display Registration Card during Pre Check in';

ALTER TABLE WRCD ADD WRCD_REGISTRATIONCARDNUMBER NUMBER(10);
COMMENT ON COLUMN WRCD.WRCD_REGISTRATIONCARDNUMBER IS 'Registration Card template used for Pre Check in';

ALTER TABLE ZINV ADD ZINV_EXPORTSTATUS NUMBER(1) DEFAULT 0;
COMMENT ON COLUMN ZINV.ZINV_EXPORTSTATUS IS 'Invoice Export status. 0 : Export not required, 1 : Exportable, 2 : Exported';

ALTER TABLE ZDCO ADD ZDCO_EINVOICEXML NUMBER(1) DEFAULT 0;
COMMENT ON COLUMN ZDCO.ZDCO_EINVOICEXML IS 'Allows eInvoice XML export if payment method is marked 1';

alter table zdco add zdco_tenderlimit number(30,4) default 0;
comment on column zdco.zdco_tenderlimit is 'Tender limit setup';

alter table zcca add zcca_authorization_reversal number(1);
comment on column xcap.xcap_role is '0 = Authorization 1 = Settlement 2 = Authorization Reversal';

COMMENT ON COLUMN WRPS.WRPS_ROLE IS '1=Confirmation Letter| 2=Folio Layout| 3=Message Print| 4=Shift report| 5=Conference Confirmation| 6=Registration Card| 7=Currency Exchange| 8=AR Reminders| 9=Paid Outs| 10=Activities| 11=Deposit Receipts| 12=Group Confirmation| 13=Special Reports| 14=AR Statements| 15=Mailing Hitlists| 16=CCM Participant Lists| 17=AR Compression| 18=Petty Cash | 19=Profile Letters (Correspondence) | 20=ProfileStatistic | 21=TableReservation | 22=TableReservationList | 23=TAP | 24=NotaCorrection | 25=EmailTemplate | 26=RoomKeyLabel | 27=Logbook | 28=AREgyptianInvoice | 29=menucards | 30=ar payment receipt | 31=Barcode Ticket| ...( stCertificate, stMaintenance)...  | 34=Payment Receipt | 55=Leisure Confirmation Letter | 56=Leisure Print List | 58=Non-Editable Reports';

alter table yfsc add yfsc_inactive number(1) default 0;