CREATE VIEW TMSYSTEM.TMS110LIZV ( VIEWPRD,VIEWVER ) AS SELECT DISTINCT LIZPRD AS VIEWPRD, LIZVER AS VIEWVER FROM TMSYSTEM.TMS110LIZ ;
CREATE VIEW TMSYSTEM.TMS404ZBV ( ZBVNUM,ZBVTXT,ZBVTTP,ZBVZKO,ZBVZZZ,ZBVZTB,ZBVSTA,ZBVDAT,ZBVERF ) AS select zbknum,zbktxt,zbrttp,zbrzko,zbrzzz,zbrztb,zbrsta,zbrdat,zbrerf from tmsystem.tms403zbk,tmsystem.tms404zbr where zbknum = zbrzbk ;
CREATE VIEW TMSYSTEM.TMS402ZZV ( ZZVNUM,ZZVTXT,ZZVVON,ZZVBIS,ZZVSTA,ZZVDAT,ZZVERF ) AS select zzznum,zzztxt,zuzvon,zuzbis,zuzsta,zuzdat,zuzerf from tmsystem.tms402zzz,tmsystem.tms402zuz where zzznum = zuznum ;
CREATE VIEW TMSYSTEM.TMS118SST ( SSTNAM,SSTTTP,SSTDAU,SSTTXT,SSTSTA,SSTDAT,SSTERF ) AS SELECT MSTNAM, MSTTTP, MSTDAU, MSTTXN, MSTSTA, MSTDAT, MSTERF FROM TMSYSTEM.TMS119MST where MSTTXN > 0 AND MSTERF = 'TIMESYS' ;
CREATE VIEW TMSYSTEM.TMS110TTV ( TTVNAM,TTVART,TTVTAS,TTVTTP,TTVFST,TTVTXT,TTVSTA,TTVDAT,TTVERF ) AS SELECT TTBNAM, TTBART, TTBTAS, TTBTTP, TTBFST, TTBTXN, TTBSTA, TTBDAT, TTBERF FROM TMSYSTEM.Tms110Ttb where TTBTXN > 0 AND TTBERF = 'TIMESYS' ;
CREATE VIEW TMSYSTEM.TMS800SBEDWH ( SBEDWHNAM,SBEDWHLIZ,SBEDWHMND,SBEDWHFIR,SBEDWHTXT,SBEDWHKAT,SBEDWHSTT,SBEDWHDAT,SBEDWHERF,SBEDWHSTA,SBEDWHSBE ) AS select distinct dwhnam, DWHLIZ, DWHMND, DWHFIR, DWHTXT, DWHKAT, DWHSTT, dwhdat, dwherf,1, DWHSBE from tmsystem.tms800Dwh a where a.DWHSTT = 'A' and dwhdat = (select max(b.dwhdat) from tmsystem.tms800dwh B where a.dwhliz = b.dwhliz and a.dwhmnd = b.dwhmnd and a.dwhnam = b.dwhnam and a.dwhkat = b.dwhkat and a.dwhsbe = b.dwhsbe and a.dwhstt=b.dwhstt) order by dwhnam ;
CREATE VIEW TMSYSTEM.G2006LOGODNL ( LDLLIZ,LDLMND,LDLNAM,LDLTYP,LDLDNM,LDLDTV,LDLDTB,LDLUZV,LDLUZB,LDLSTA,LDLDAT,LDLERF,LDLZAG,LDLTRM,LDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 2 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.G2006NWSDNL ( NDLLIZ,NDLMND,NDLNAM,NDLTYP,NDLDNM,NDLDTV,NDLDTB,NDLUZV,NDLUZB,NDLSTA,NDLDAT,NDLERF,NDLZAG,NDLTRM,NDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 6 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.G2006PREDNL ( PDLLIZ,PDLMND,PDLNAM,PDLTYP,PDLDNM,PDLDTV,PDLDTB,PDLUZV,PDLUZB,PDLSTA,PDLDAT,PDLERF,PDLZAG,PDLTRM,PDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 4 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.G2006SCRDNL ( SDLLIZ,SDLMND,SDLNAM,SDLTYP,SDLDNM,SDLDTV,SDLDTB,SDLUZV,SDLUZB,SDLSTA,SDLDAT,SDLERF,SDLZAG,SDLTRM,SDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 3 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.G2006TKRDNL ( TDLLIZ,TDLMND,TDLNAM,TDLTYP,TDLDNM,TDLDTV,TDLDTB,TDLUZV,TDLUZB,TDLSTA,TDLDAT,TDLERF,TDLZAG,TDLTRM,TDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 7 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.G2006WLCDNL ( WDLLIZ,WDLMND,WDLNAM,WDLTYP,WDLDNM,WDLDTV,WDLDTB,WDLUZV,WDLUZB,WDLSTA,WDLDAT,WDLERF,WDLZAG,WDLTRM,WDLDLA ) AS SELECT DDLLIZ, DDLMND, DDLNAM, DDLTYP, DDLDNM + ' ' + TRDVER, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM TMSYSTEM.G2006DATDNLLST, TMSYSTEM.O2006TRMDATEI WHERE DDLTYP = 5 AND DDLDNM = TRDNAM AND DDLLIZ = TRDLIZ AND DDLMND = TRDMND ;
CREATE VIEW TMSYSTEM.KONSOKREIS ( KONKKRNUM,KONKKRBEZ,KONFIRNAM,KONKKRSTA ) AS SELECT KkrNum, KkrBez, MndNa1, KkrSta FROM TMSYSTEM.TMS107KKR a left join TMSYSTEM.TMS107KKZ b on b.KKZKKR=a.KKRNUM left join TMSYSTEM.TMS112MND d on d.MNDNUM=b.KKZMND WHERE (b.KKZMND = (SELECT MIN(e.KKZMND) FROM TMSYSTEM.TMS107KKZ e WHERE a.KKRNUM = e.KKZKKR) OR b.KKZKKR is null) ;
CREATE VIEW TMSYSTEM.SBEKONZERN ( SBKKSBLIZ,SBKKSBMND,SBKKSBNAM,SBKMNDNA1,SBKMNDSCH,SBKKSBSTA,SBKKSBDAT,SBKKSBERF,SBKKSBNA2,SBKKSBKON ) AS SELECT KsbLiz, KsbMnd, KsbNam, MndNa1, MndSch, KsbSta, KsbDat, KsbErf, KsbNa2, KsbKon FROM TMSYSTEM.TMS107KSB, TMSYSTEM.TMS112MND WHERE MNDNA1 <> 'TIMESYSAdmin' AND MNDNUM = KSBMND AND MNDLIZ = KSBLIZ AND KSBHOM = 1 ;
