--UPDATE FROM 8.7.4.7 TO 8.7.4.8


DROP TABLE VERSION_8_7_4_7;



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

ALTER TABLE XJET ADD XJET_SWAP_NATION NUMBER(1); 

CREATE TABLE WERR (
    WERR_ID NUMBER(30) NOT NULL
   ,WERR_HOTELID VARCHAR2(30) NOT NULL
   ,WERR_TIMESTAMP DATE NOT NULL
   ,WERR_HOTELDATE DATE NOT NULL
   ,WERR_APPNAME VARCHAR2(3) NOT NULL
   ,WERR_APPVERSION VARCHAR2(20) NOT NULL
   ,WERR_EXCEPTIONNAME VARCHAR2(50) NOT NULL
   ,WERR_EXCEPTIONTEXT VARCHAR2(2000)
   ,WERR_SESSIONERRORCOUNT NUMBER(4)
   ,WERR_SESSIONTIMESINCELASTERROR DATE
   ,WERR_ELFLOG CLOB 
   ,WERR_SHORTAPPLOG VARCHAR2(2000)
   ,WERR_FULLAPPLOG CLOB
   ,WERR_ORIGIN NUMBER(1) NOT NULL
   ,WERR_TRANSFERRED NUMBER(1)
   ,WERR_TRANSFERTIME DATE
   ,WERR_SESSION VARCHAR2(32) NOT NULL
   ,WERR_MACHINENAME VARCHAR2(30)
   ,WERR_FORM VARCHAR2(100)
   ,WERR_CONTROL VARCHAR2(100)
   ,WERR_DBID VARCHAR2(20)
   ,WERR_SOURCEFILE VARCHAR2(100)
   ,WERR_PROCEDURE VARCHAR2(100)
   ,WERR_COMMENT VARCHAR2(2000)
   ,WERR_UNIQUEID VARCHAR2(32)
   ,CONSTRAINT WERR_PK PRIMARY KEY (WERR_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8MAIN;

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

COMMENT ON TABLE WERR IS 'Application error log';

COMMENT ON COLUMN WERR.WERR_HOTELID IS 'Identifies the hotel';

COMMENT ON COLUMN WERR.WERR_APPNAME IS 'FV8=fidleiov8.exe FS=fidelioserver etc';

COMMENT ON COLUMN WERR.WERR_EXCEPTIONNAME IS 'Internal name of he exception (for programmers only)';

COMMENT ON COLUMN WERR.WERR_EXCEPTIONTEXT IS 'Text of the error message appearing on screen for the user';

COMMENT ON COLUMN WERR.WERR_SESSIONERRORCOUNT IS 'Number of errors logged since application startup';

COMMENT ON COLUMN WERR.WERR_SESSIONTIMESINCELASTERROR IS 'Time elapsed since last logged error of the same application session';

COMMENT ON COLUMN WERR.WERR_ELFLOG IS 'Generated eureka elf log file';

COMMENT ON COLUMN WERR.WERR_SHORTAPPLOG IS 'short version of applog to be able to use in simple sql queries';

COMMENT ON COLUMN WERR.WERR_FULLAPPLOG IS 'Application execution log. Focused controls, parent control, parent forms etc + any other program execution info logged by the programmer';

COMMENT ON COLUMN WERR.WERR_ORIGIN IS '0=error in the hotel 1=error imported from other hotel';

COMMENT ON COLUMN WERR.WERR_TRANSFERRED IS '1=transferred to central database';

COMMENT ON COLUMN WERR.WERR_TRANSFERTIME IS 'time of exporting or importing';

COMMENT ON COLUMN WERR.WERR_SESSION IS 'unique id of each program session';

COMMENT ON COLUMN WERR.WERR_DBID IS 'string to identify db instances of the same hotel database. ie: live/training/testing';

COMMENT ON COLUMN WERR.WERR_SOURCEFILE IS 'source file on top of call stack';

COMMENT ON COLUMN WERR.WERR_PROCEDURE IS 'procedure on top of call stack';

ALTER TABLE XPDC ADD XPDC_BIRTHDAY DATE;

COMMENT ON COLUMN XPDC.XPDC_BIRTHDAY IS 'Date of Birth';

UPDATE XCMS SET XCMS_XCOU_ID_NATION = (SELECT XPDC_XCOU_ID_NATION FROM XPDC WHERE XPDC_XCMS_ID = XCMS_ID AND NVL(XPDC_XCOU_ID_NATION, 0)<>0
AND ROWNUM = 1) WHERE EXISTS (SELECT XPDC_ID FROM XPDC,XPDG WHERE XPDC_XCMS_ID = XCMS_ID AND NVL(XPDC_XCOU_ID_NATION, 0)<>0 
AND XPDC_XPDG_ID = XPDG_ID AND XPDG_ROLE = 1) AND XCMS_XCOU_ID_NATION IS NULL;

UPDATE XCID SET XCID_BIRTHPLACE = (SELECT XPDC_BIRTHPLACE FROM XPDC WHERE XPDC_XCMS_ID = XCID_ID AND XPDC_BIRTHPLACE IS NOT NULL 
AND ROWNUM = 1) WHERE EXISTS (SELECT XPDC_ID FROM XPDC,XPDG WHERE XPDC_XCMS_ID = XCID_ID AND XPDC_BIRTHPLACE IS NOT NULL 
AND XPDC_XPDG_ID = XPDG_ID AND XPDG_ROLE = 1) AND XCID_BIRTHPLACE IS NULL;
