CREATE TABLE SEMV_SONDERDATA ( ID_SEMV_SONDERDATA bigint IDENTITY(1,1) PRIMARY KEY, IN_VIEW int, RECH_ACTIVE SMALLINT NOT NULL, RECH_MODUL varchar(35) NOT NULL, RECH_VIEW varchar(35) NOT NULL, RECH_OBJECT varbinary(MAX), RECH_TABLE varbinary(MAX), RECH_FORMAT varchar(35) NOT NULL, MITEPOS SMALLINT, MITBEMERKUNG SMALLINT, MITGKU SMALLINT, MITNST SMALLINT, MITABWEICHUNG SMALLINT, NEW_U_ID bigint, NEW_DAT datetime, EDIT_U_ID bigint, EDIT_DAT datetime);
ALTER TABLE SEMV ADD SEMV_SONDERDATA_ID bigint;
ALTER TABLE EPZ ADD GEGEN_ID bigint;
ALTER TABLE RECH_JJJJ_EDI_DATEI ADD CH_TYP CHAR(1);
CREATE OR ALTER VIEW SEMV_VIEW (ID, IN_VIEW, VERSANDDATUM, MAILSTATUS, USERNAME, CH_RECHJAHR, CH_RECHMONAT, BETREFFTEXT, BCC, HTML, ZIP, RECH_ACTIVE, RECH_MODUL, RECH_VIEW, RECH_FORMAT, MITGKU, EPOS_ACTIVE, EPOS_RECH_MODUL, EPOS_RECH_VIEW, EPOS_VIEW, EPOS_FORMAT, EVN_ACTIVE, EVN_RECH_MODUL, EVN_RECH_VIEW, EVN_VIEW, EVN_FORMAT, SONDER_ACTIVE, SONDER_MODUL, SONDER_VIEW, SONDER_FORMAT, C_USER_NEW, NEW_DAT, C_USER_EDIT, EDIT_DAT) AS select semv.id_semv, semv.IN_VIEW AS IN_VIEW, semv.versanddatum, semv.mailstatus, tel_user.ch_login, semv_view_info.CH_RECHJAHR, semv_view_info.CH_RECHMONAT, semv_mailtext.betrefftext, semv.bcc, semv_mailtext.formathtml, semv_mailtext.formatzip, semv_rechdata.rech_active, semv_rechdata.rech_modul, semv_rechdata.rech_view, semv_rechdata.rech_format, semv_rechdata.mitgku, semv_eposdata.epos_active, semv_eposdata.epos_rech_modul, semv_eposdata.epos_rech_view, semv_eposdata.epos_view, semv_eposdata.epos_format, semv_evndata.evn_active, semv_evndata.evn_rech_modul, semv_evndata.evn_rech_view, semv_evndata.evn_view, semv_evndata.evn_format, SEMV_SONDERDATA.rech_active, SEMV_SONDERDATA.rech_modul, SEMV_SONDERDATA.rech_view, SEMV_SONDERDATA.rech_format, T_USER_NEW.CH_LOGIN AS C_USER_NEW, semv.new_dat, T_USER_EDIT.CH_LOGIN AS C_USER_EDIT, semv.edit_dat from semv left join tel_user on tel_user.id_user = semv.user_id left join semv_view_info on semv.semv_view_info_id = semv_view_info.id_semv_view_info left join semv_mailtext on semv.semv_mailtext_id = semv_mailtext.id_semv_mailtext left join semv_rechdata on semv.semv_rechdata_id = semv_rechdata.id_semv_rechdata left join semv_eposdata on semv.semv_eposdata_id = semv_eposdata.id_semv_eposdata left join semv_evndata on semv.semv_evndata_id = semv_evndata.id_semv_evndata left join SEMV_SONDERDATA on semv.SEMV_SONDERDATA_ID = SEMV_SONDERDATA.ID_SEMV_SONDERDATA left join TEL_USER AS T_USER_NEW ON SEMV.NEW_U_ID = T_USER_NEW.ID_USER left join TEL_USER AS T_USER_EDIT ON SEMV.EDIT_U_ID = T_USER_EDIT.ID_USER;
CREATE OR ALTER VIEW EPZ_VIEW (ID, IN_VIEW, ARTIKELNR, CH_RFN, TEXT, PRODFAMNAME, GEBGRUPPENAME, EPREIS, EPZGRUPPE1, EPZGRUPPE2, EPZGRUPPEG, GEGEN_ID, GEGEN_ANR, GEGEN_PTEXT, C_USER_NEW, NEW_DAT, C_USER_EDIT, EDIT_DAT) AS SELECT DISTINCT epz.id_epz, epz.in_view as IN_VIEW, epz.artikelnr, rech_jjjj_edi_rfn.ch_rfn, /*epz.ch_kennung,*/ epz.text, epos_ctpf.vc_name, epos_ctggruppe.vc_name, epz.epreis, epz.vc_epzgruppe1, epz.vc_epzgruppe2, epz.vc_epzgruppe3, gegen.GEGEN_ID, gegen.artikelnr as GEGEN_ANR, gegen.TEXT as GEGEN_PTEXT, NewU.ch_login, epz.new_dat, EditU.ch_login, epz.edit_dat FROM EPZ LEFT JOIN RECH_JJJJ_EDI_RFN ON EPZ.rfn_id = rech_jjjj_edi_rfn.id_rfn LEFT JOIN TEL_USER AS NewU ON EPZ.new_u_id = NewU.id_user LEFT JOIN TEL_USER AS EditU ON EPZ.edit_u_id = EditU.id_user LEFT JOIN EPOS ON EPOS.TEXT = EPZ.TEXT AND EPOS.artikelnr = epz.artikelnr LEFT JOIN epos_ctggruppe ON EPOS.ch_gebgruppe_id = EPOS_CTGGRUPPE.id_epos_gebgruppe LEFT JOIN epos_ctpf ON EPOS.ch_prodfam_id = EPOS_CTPF.id_epos_ctpf LEFT JOIN EPZ as gegen ON EPZ.GEGEN_ID = gegen.ID_EPZ;
ALTER TABLE SEMV_RECHDATA ADD RECH_MITDETAILRECH smallint;
ALTER TABLE EPOS ALTER COLUMN TEXT varchar(256);
ALTER TABLE GKU_EPOS ALTER COLUMN TEXT varchar(256);
UPDATE EPOS SET TEXT = trim(TEXT)
UPDATE GKU_EPOS SET TEXT = trim(TEXT)
UPDATE GL_CONFIG SET VC_WERT = '20231208' WHERE BI_EINTRAG = 1