--UPDATE FROM 8.10.2.50 TO 8.10.2.60


 	
DROP TABLE VERSION_8_10_2_50;


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

alter table zfcf add ZFCF_RT_LOTTERY VARCHAR2(16);
comment on column zfcf.ZFCF_RT_LOTTERY is 'Stores lottery code for fiscal printer';

ALTER TABLE MMOD ADD MMOD_COMPUTER_NAME VARCHAR2(20);

COMMENT ON COLUMN MMOD.MMOD_COMPUTER_NAME IS 'Computername of the IFC maschine max. size of windows computer name is 15 character';

ALTER TABLE MMSG ADD MMSG_COMPUTER_NAME VARCHAR2(20);

COMMENT ON COLUMN MMSG.MMSG_COMPUTER_NAME IS 'Identifies the log from specific workstation';

ALTER TABLE MLOG ADD MLOG_COMPUTER_NAME VARCHAR2(20);

COMMENT ON COLUMN MLOG.MLOG_COMPUTER_NAME IS 'Identifies the log from specific workstation';

ALTER TABLE MQU5 ADD MQU5_COMPUTER_NAME VARCHAR2(20);

COMMENT ON COLUMN MQU5.MQU5_COMPUTER_NAME IS 'Identifies the log from specific workstation';

create table litr(
  litr_id number(30) not null
  ,litr_tablename varchar2(4) not null
  ,litr_tableid number(30)
  ,litr_type number(1) not null
  ,litr_time date not null
  ,litr_fromdate date
  ,litr_todate date
  ,litr_tableid2 number(30)
  ,litr_tname1 varchar2(4)
  ,litr_tname2 varchar2(4)
  ,litr_info varchar2(200)
  ,constraint litr_pk primary key (litr_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_litr increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table litr is 'Legal triggers table';
comment on column litr.litr_id is 'table id';
comment on column litr.litr_tablename is 'source table name';
comment on column litr.litr_tableid is 'source table id';
comment on column litr.litr_type is '0 = new  1 = modify  2 = delete';
comment on column litr.litr_time is 'time of trigger';

create table lipf(
  lipf_id number(30) not null
  ,lipf_litr_id number(30) not null
  ,lipf_processed number(1) not null
  ,lipf_ziti_id number(30) not null
  ,constraint lipf_pk primary key (lipf_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_lipf increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table lipf is 'link table between triger and interface ';
comment on column lipf.lipf_id is 'table id';
comment on column lipf.lipf_litr_id is 'trigger id';
comment on column lipf.lipf_processed is ' 0 = not processed  1 = processed ';
comment on column lipf.lipf_ziti_id is 'interface id';

create table fres(
  fres_id number(30) not null
  ,fres_tablename varchar2(4) not null
  ,fres_tableid number(30) not null
  ,fres_workstation varchar2(20) not null
  ,fres_start_transaction date not null
  ,fres_finish_transaction date not null
  ,fres_transaction_id varchar2(20) not null
  ,fres_tse_serial_id varchar2(100) not null
  ,fres_response_code varchar2(200) not null
  ,fres_transaction_location varchar2(128) not null
  ,fres_transaction_number varchar2(20)
  ,fres_transaction_total number(10,4)
  ,fres_transaction clob
  ,fres_signcnt number(30) not null
  ,fres_qrcode clob
  ,fres_main_tblid number(30) not null
  ,fres_main_tblname varchar2(4) not null
  ,fres_transaction_reason varchar2(150)
  ,fres_first_transaction date
  ,fres_tse_online number(1,0)
  ,constraint fres_pk primary key (fres_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_fres increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table fres is 'Legal triggers response table';
comment on column fres.fres_id is 'table id';
comment on column fres.fres_tablename is 'source table name either yres or zpos';
comment on column fres.fres_tableid is 'source table id';
comment on column fres.fres_workstation is 'name of the workstion, which fired the fiscal trigger';
comment on column fres.fres_start_transaction is 'timestamp of the start transaction';
comment on column fres.fres_finish_transaction is 'timestamp of the finish transaction';
comment on column fres.fres_transaction_id is 'trasaction id provide by the fical unit';
comment on column fres.fres_tse_serial_id is 'serial id of the TSE provided by the fiscal unit';
comment on column fres.fres_response_code is 'unique response code provided by the fiscal unit';
comment on column fres.fres_transaction_location is 'comes from the field mmod_hotel_key';
comment on column fres.fres_transaction_number is 'TSE transaction number recieved with the response';
comment on column fres.fres_transaction_total is 'Total of a payment';
comment on column fres.fres_transaction is 'Contains the finishTransaction message of the transaction ';
comment on column fres.fres_signcnt is 'Signature counter provided by the TSE';
comment on column fres.fres_qrcode is 'QR Code sent by the TSE';
comment on column fres.fres_main_tblid is 'Main table id either yres_id or zfac_id used to identify during checkout the first and last transaction';
comment on column fres.fres_main_tblname is 'Main table name either zfac or yres used to identify during checkout the first and last transaction';
comment on column fres.fres_transaction_reason is 'Contains the reason of the transaction as short description';
comment on column fres.fres_first_transaction is 'timestamp of the first transaction'; 
comment on column fres.fres_tse_online is 'Indicates whether this transaction was made with onlien or offline TSE'; 

create table fred(
  fred_id number(30) not null
  ,fred_fres_id number(30) not null
  ,fred_tableid number(30) not null
  ,constraint fred_pk primary key (fred_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_fred increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table fred  IS 'Linked table to Legal triggers response table fres, which holds in case of accumulated posting triggers all zpos_ids involved in the trigger';
comment on column fred.fred_id IS 'table id';
comment on column fred.fred_fres_id IS 'linked id to fres';
comment on column fred.fred_tableid IS 'zpos id';

create table freo(
  freo_id number(30) not null
  ,freo_fres_start_id number(30) not null
  ,freo_fres_end_id number(30) not null
  ,constraint freo_pk primary key (freo_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_freo increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table freo is 'Protocols offline situation of the TSE';
comment on column freo.freo_id is 'table id';
comment on column freo.freo_fres_start_id is 'first offline transaction message';
comment on column freo.freo_fres_end_id is 'first online transaction message after the TSE was offline';

create table xfdt(
  xfdt_id number(30) not null
  ,xfdt_type number(2) not null
  ,xfdt_shortdesc varchar2(3)
  ,xfdt_longdesc varchar2(30) not null
  ,xfdt_inactive number(1)
  ,constraint xfdt_pk primary key (xfdt_id) using index tablespace v8index
) tablespace v8main;

create sequence seq_xfdt increment by 1 start with 1000 nomaxvalue nocycle nocache noorder;

comment on table xfdt is 'Fiscal Document Types used for german legal functionality';
comment on column xfdt.xfdt_id is 'table id';
comment on column xfdt.xfdt_type is 'type of document i.e. 1 = Bestellung, 2 = Lieferschein, 3 = Beleg, etc.';
comment on column xfdt.xfdt_shortdesc is 'code of document type';
comment on column xfdt.xfdt_longdesc is 'name of the document type';
comment on column xfdt.xfdt_inactive is 'inactice flag';

alter table fres add fres_transaction_provider number(1,0) default 0 not null;
comment on column fres.fres_transaction_provider is 'Indicates, which fiscal interface is used 0 = efsta, 1 = fiskalTrust, 2 = epson'; 

ALTER TABLE ZTXC ADD ZTXC_TAXGROUP VARCHAR2(1);
COMMENT ON COLUMN ZTXC.ZTXC_TAXGROUP is 'Predefined Tax Group for German TSE Signature. Possible values A .. G';