CREATE VIEW %SCHEMA%.MITARBZUO ( ZUOPBVPNR,ZUOPBVGVO,ZUOPBVGBI,ZUOPBVESO,ZUOPBVESA,ZUOPBVTXT,ZUOPBVZRM,ZUOPBVNAM,ZUOPBVAWP,ZUOPBVSST,ZUOPBVSTA,ZUOPBVDAT,ZUOPBVERF ) AS SELECT PbvPnr, PbvGvo, PbvGbi, PbvEso, PbvEsa, PbvTxt, PbvZrm, PnrNam, PbvAwp, PbvSst, PbvSta, PbvDat, PbvErf FROM %SCHEMA%.TMS710PBV, %SCHEMA%.TMS300PNR WHERE PBVPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.TMS157BMV ( BMVNUM,BMVTXT,BMVKRZ,BMVLFD,BMVGVO,BMVGBI,BMVBSA,BMVEIN,BMVWTG,BMVTTP,BMVGEW,BMVGEE,BMVAZE,BMVUVO,BMVUBI,BMVDAT,BMVERF,BMVSTA,BMVKKZ,BMVZKT,BMVANW,BMVZKE ) AS select BMDNUM,BMDTXT,BMDKRZ,BM1LFD,BM1GVO,BM1GBI,BM1BSA,BM1EIN,BM1WTG, BM1TTP,BM1GEW,BM1GEE,BM1AZE,BM1UVO,BM1UBI,BM1DAT,BM1ERF,BM1STA, BMDKRZ,BM1ZKT,BM1ANW,BM1ZKE from %SCHEMA%.tms157bmd,%SCHEMA%.tms157bm1 where bmdnum=bm1num ;
CREATE VIEW %SCHEMA%.G2006ANWDNL ( ADLNAM,ADLTYP,ADLDNM,ADLDTV,ADLDTB,ADLUZV,ADLUZB,ADLSTA,ADLDAT,ADLERF,ADLZAG,ADLTRM,ADLDLA ) AS SELECT DDLNAM, DDLTYP, DDLDNM, DDLDTV, DDLDTB, DDLUZV, DDLUZB, DDLSTA, DDLDAT, DDLERF, DDLZAG, DDLTRM, DDLDLA FROM %SCHEMA%.G2006DATDNLLST WHERE DdlTyp=1 ;
CREATE VIEW %SCHEMA%.O2006ANWDATEI ( ANWNAM,ANWTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=1 ;
CREATE VIEW %SCHEMA%.O2006LOGODATEI ( LOGONAM,LOGTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=2 ;
CREATE VIEW %SCHEMA%.O2006NWSDATEI ( NWSNAM,NWSTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=6 ;
CREATE VIEW %SCHEMA%.O2006PREDATEI ( PRENAM,PRETXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=4 ;
CREATE VIEW %SCHEMA%.O2006SCRDATEI ( SCRNAM,SCRTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=3 ;
CREATE VIEW %SCHEMA%.O2006TKRDATEI ( TKRNAM,TKRTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=7 ;
CREATE VIEW %SCHEMA%.O2006WLCDATEI ( WLCNAM,WLCTXT ) AS SELECT TrdNam, (TrdNam + ' ' + TrdVer) AS TrdNamVer FROM %SCHEMA%.O2006TRMDATEI WHERE TrdTyp=5 ;
CREATE VIEW %SCHEMA%.AKTUELLEZUO ( ZUOPNR,ZUOGVO,ZUOGBI,ZUOABT,ZUOVRA,ZUOAGR,ZUOSDT,ZUOSTG,ZUOKST,ZUOKTR,ZUOKRT,ZUOSTA,ZUODAT,ZUOERF,ZUOKTA,ZUOABA,ZUOTRG,ZUOBRA,ZUOKSA,ZUOKAA,ZUOKSB,ZUOPNM,BAKDTM,BAKJHR,BAKMON,BAKTAG,BAKWCH,BAKWTG,BAKJTG,BAKDAT,BAKERF,BAKYOW,BAKCSM,BAKSTA,BAKQUA,BAKHJH ) AS select "ZUOPNR","ZUOGVO","ZUOGBI","ZUOABT","ZUOVRA","ZUOAGR","ZUOSDT","ZUOSTG","ZUOKST","ZUOKTR","ZUOKRT","ZUOSTA","ZUODAT","ZUOERF","ZUOKTA","ZUOABA","ZUOTRG","ZUOBRA","ZUOKSA","ZUOKAA","ZUOKSB","ZUOPNM","BAKDTM","BAKJHR","BAKMON","BAKTAG","BAKWCH","BAKWTG","BAKJTG","BAKDAT","BAKERF","BAKYOW","BAKCSM","BAKSTA","BAKQUA","BAKHJH" from %SCHEMA%.tms311zuo a,tmsystem.tms133bak where ( (a.zuogvo<=bakdtm and (a.zuogbi>=bakdtm or a.zuogbi is null)) or ( (select count(*) from %SCHEMA%.tms311zuo b where b.zuopnr=a.zuopnr and b.zuogvo<=bakdtm and (b.zuogbi>=bakdtm or b.zuogbi is null) ) = 0 and a.zuogvo = (select max(b.zuogvo) from %SCHEMA%.tms311zuo b where b.zuogvo<bakdtm and b.zuopnr = a.zuopnr) ) or ( (select count(*) from %SCHEMA%.tms311zuo c where c.zuogvo<=bakdtm and c.zuopnr=a.zuopnr and (a.zuogbi>=bakdtm or a.zuogbi is null) ) =0 and (select count(*) from %SCHEMA%.tms311zuo where (select count(*) from %SCHEMA%.tms311zuo b where b.zuopnr=a.zuopnr and b.zuogvo<=bakdtm and (b.zuogbi>=bakdtm or b.zuogbi is null) ) = 0 and a.zuogvo = (select max(b.zuogvo) from %SCHEMA%.tms311zuo b where b.zuogvo<bakdtm and b.zuopnr = a.zuopnr) ) = 0 and a.zuogvo = (select min(b.zuogvo) from %SCHEMA%.tms311zuo b where b.zuogvo>bakdtm and b.zuopnr = a.zuopnr) ) ) ;
CREATE VIEW %SCHEMA%.ARBEITSZEITERS ( ERSERSAGR,ERSERSAZT,ERSERSAZE,ERSERSVRA,ERSERSSTA,ERSERSDAT,ERSERSERF,ERSERSBSD,ERSERSTLZ,ERSERSTLA ) AS select ERSAGR,ERSAZT, (select ERSAZE from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F1, (select ERSVRA from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F2, (select ERSSTA from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F3, (select ERSDAT from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F4, (select ERSERF from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F5, (select ERSBSD from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F6, (select ERSTLZ from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F7, (select ERSTLA from %SCHEMA%.TMS162ERS a where a.ERSAGR= b.ERSAGR and a.ERSAZT = b.ERSAZT AND ROWNUM = 1) as F8 from %SCHEMA%.TMS162ERS b group by ERSAGR,ERSAZT ;
CREATE VIEW %SCHEMA%.ARBEITSZEITERS_ALL ( ERSERSAGR,ERSERSAZT,ERSERSAZE,ERSERSVRA,ERSERSSTA,ERSERSDAT,ERSERSERF,ERSERSBSD,ERSERSTLZ,ERSERSTLA ) AS SELECT ERSAGR,ERSAZT,ERSAZE, ERSVRA, ERSSTA,ERSDAT,ERSERF,ERSBSD,ERSTLZ,ERSTLA FROM %SCHEMA%.TMS162ERS ;
CREATE VIEW %SCHEMA%.ARBEITSZEITGRP ( AGRAG1NUM,AGRAG1GVO,AGRAG1GBI,AGRAG1TGE,AGRAG1SOL,AGRAG1STG,AGRAG1TXT,AGRAG1STA,AGRAG1DAT,AGRAG1ERF,AGRWPLAGR,AGRWPLTAG,AGRWPLAZT,AGRWPLVRA,AGRWPLAZE,AGRWPLBSD,AGRWPLSTA,AGRWPLDAT,AGRWPLERF ) AS SELECT AG1NUM,AG1GVO,AG1GBI,AG1TGE,AG1SOL,AG1STG,AG1TXT,AG1STA,AG1DAT,AG1ERF, WPLAGR, WPLTAG,WPLAZT,WPLVRA,WPLAZE,WPLBSD ,WPLSTA,WPLDAT,WPLERF FROM %SCHEMA%.TMS160AG1, %SCHEMA%.TMS161WPL WHERE AG1NUM = WPLAGR AND AG1GVO = WPLGVO AND WPLTAG = 1 ;
CREATE VIEW %SCHEMA%.ARBEITSZEITGRP_ALL ( AGRAG1NUM,AGRAG1GVO,AGRAG1GBI,AGRAG1TGE,AGRAG1SOL,AGRAG1STG,AGRAG1TXT,AGRAG1STA,AGRAG1DAT,AGRAG1ERF,AGRWPLAGR,AGRWPLTAG,AGRWPLAZT,AGRWPLVRA,AGRWPLAZE,AGRWPLBSD,AGRWPLSTA,AGRWPLDAT,AGRWPLERF ) AS SELECT AG1NUM,AG1GVO,AG1GBI,AG1TGE,AG1SOL,AG1STG,AG1TXT,AG1STA,AG1DAT,AG1ERF, WPLAGR, WPLTAG,WPLAZT,WPLVRA,WPLAZE,WPLBSD ,WPLSTA,WPLDAT,WPLERF FROM %SCHEMA%.TMS160AG1, %SCHEMA%.TMS161WPL WHERE AG1NUM = WPLAGR AND AG1GVO = WPLGVO ;
CREATE VIEW %SCHEMA%.ARBZEITMODELL_ALL ( AZMAZTNUM,AZMAZTTXT,AZMAZVGVO,AZMAZVGBI,AZMAZVATV,AZMAZVATB,AZMAZVMAV,AZMAZVMAB,AZMAZVNOV,AZMAZVNOB,AZMAZVGLV,AZMAZVGLB,AZMAZVKEV,AZMAZVKEB,AZMAZVSOL,AZMAZVSO2,AZMAZVVHS,AZMAZVHSO,AZMAZVHS2,AZMAZVHTV,AZMAZVSBG,AZMAZVAZP,AZMAZVPSM,AZMAZVGLT,AZMAZVZPZ,AZMAZVSKZ,AZMAZVKMA,AZMAZVDAT,AZMAZVERF,AZMAZVSTA,AZMAZVNUM,AZMAZVZTR ) AS SELECT AztNum, AztTxt, AzvGvo, AzvGbi, AzvAtv, AzvAtb, AzvMav, AzvMab, AzvNov, AzvNob, AzvGlv, AzvGlb, AzvKev, AzvKeb, AzvSol, AzvSo2, AzvVhs, AzvHso, AzvHs2, AzvHtv, AzvSbg, AzvAzp, AzvPsm, AzvGlt, AzvZpz, AzvSkz, AzvKma, AzvDat, AzvErf, AzvSta, AzvNum, NULL FROM %SCHEMA%.TMS140AZT, %SCHEMA%.TMS140AZV a WHERE AztNum = a.AzvNum ;
CREATE VIEW %SCHEMA%.LU1 ( LU1STZ,LU1ZTK,LU1LNA,LU1LGR,LU1GVO,LU1GBI,LU1RRE,LU1TGW,LU1KST,LU1KTR,LU1KRT,LU1TGE,LU1SOL,LU1SVK,LU1FLG,LU1LFN,LU1STS,LU1FKT,LU1ZTR,LU1MOG,LU1ART,LU1FKZ,LU1BAU,LU1STA,LU1DAT,LU1ERF,LU1NUM,LU1PRM ) AS SELECT Lu1Stz, Lu1Ztk, Lu1Lna, Lu1Lgr, Lu1Gvo, Lu1Gbi, Lu1Rre, Lu1Tgw, Lu1Kst, Lu1Ktr, Lu1Krt, Lu1Tge, Lu1Sol, Lu1Svk, Lu1Flg, Lu1Lfn, Lu1Sts, Lu1Fkt, Lu1Ztr, Lu1Mog, Lu1Art, Lu1Fkz, Lu1Bau, Lu1Sta, Lu1Dat, Lu1Erf, Lu1Num, NULL FROM %SCHEMA%.TMS171LU1 a where a.Lu1Stz = (SELECT MIN(b.Lu1Stz) FROM %SCHEMA%.TMS171LU1 b WHERE a.Lu1Num = b.Lu1Num) AND a.Lu1Lna = (SELECT MIN(c.Lu1Lna) FROM %SCHEMA%.TMS171LU1 c WHERE c.Lu1Num = a.Lu1Num AND c.Lu1Stz = a.Lu1Stz) AND a.Lu1Ztk = (SELECT MIN(d.Lu1Ztk) FROM %SCHEMA%.TMS171LU1 d WHERE d.Lu1Num = a.Lu1Num AND d.Lu1Stz = a.Lu1Stz AND d.Lu1Lna = a.Lu1Lna) AND a.Lu1Gvo = (SELECT MIN(e.Lu1Gvo) FROM %SCHEMA%.TMS171LU1 e WHERE e.Lu1Num = a.Lu1Num AND e.Lu1Stz = a.Lu1Stz AND e.Lu1Lna = a.Lu1Lna AND e.Lu1Ztk = a.Lu1Ztk) ;
CREATE VIEW %SCHEMA%.LU1_ALL ( LU1STZ,LU1ZTK,LU1LNA,LU1LGR,LU1GVO,LU1GBI,LU1RRE,LU1TGW,LU1KST,LU1KTR,LU1KRT,LU1TGE,LU1SOL,LU1SVK,LU1FLG,LU1LFN,LU1STS,LU1FKT,LU1ZTR,LU1MOG,LU1ART,LU1FKZ,LU1BAU,LU1STA,LU1DAT,LU1ERF,LU1NUM,LU1PRM ) AS SELECT Lu1Stz, Lu1Ztk, Lu1Lna, Lu1Lgr, Lu1Gvo, Lu1Gbi, Lu1Rre, Lu1Tgw, Lu1Kst, Lu1Ktr, Lu1Krt, Lu1Tge, Lu1Sol, Lu1Svk, Lu1Flg, Lu1Lfn, Lu1Sts, Lu1Fkt, Lu1Ztr, Lu1Mog, Lu1Art, Lu1Fkz, Lu1Bau, Lu1Sta, Lu1Dat, Lu1Erf, Lu1Num, NULL FROM %SCHEMA%.TMS171LU1 a ;
CREATE VIEW %SCHEMA%.LUGPROGRAMM ( LUGLGPNUM,LUGLGPTXT,LUGLG1STZ,LUGLG1TXT,LUGLG1STT,LUGLG1SVT,LUGLG1ZES,LUGLG1P4A,LUGLGPSTA,LUGLGPDAT,LUGLGPERF,LUGLG1NUM,LUGLG1PRM ) AS SELECT LgpNum, LgpTxt, Lg1Stz, Lg1Txt, Lg1Stt, Lg1Svt, Lg1Zes, Lg1P4a, LgpSta, LgpDat, LgpErf, Lg1Num, NULL FROM %SCHEMA%.TMS170LGP, %SCHEMA%.TMS170LG1 a WHERE LgpNum = a.Lg1Num AND a.Lg1Stz = (SELECT MIN(b.Lg1Stz) FROM %SCHEMA%.TMS170LG1 b WHERE LgpNum = b.Lg1Num) ;
CREATE VIEW %SCHEMA%.LUGPROGRAMM_ALL ( LUGLGPNUM,LUGLGPTXT,LUGLG1STZ,LUGLG1TXT,LUGLG1STT,LUGLG1SVT,LUGLG1ZES,LUGLG1P4A,LUGLGPSTA,LUGLGPDAT,LUGLGPERF,LUGLG1NUM,LUGLG1PRM ) AS SELECT LgpNum, LgpTxt, Lg1Stz, Lg1Txt, Lg1Stt, Lg1Svt, Lg1Zes, Lg1P4a, LgpSta, LgpDat, LgpErf, Lg1Num, NULL FROM %SCHEMA%.TMS170LGP, %SCHEMA%.TMS170LG1 a WHERE LgpNum = a.Lg1Num ;
CREATE VIEW %SCHEMA%.LUGSCHNSTELLE ( SSTLUGLUG,SSTLUGTXT,SSTLUGLGP,SSTLUGANZ,SSTLUGDTM,SSTLUGMON,SSTLUGUNT,SSTLUGFIR,SSTLUGAKR,SSTLUGPNR,SSTLUGKTX,SSTLU1STZ,SSTLU1ZTK,SSTLU1LNA,SSTLU1LGR,SSTLU1GVO,SSTLU1GBI,SSTLU1RRE,SSTLU1TGW,SSTLU1KST,SSTLU1KTR,SSTLU1KRT,SSTLU1TGE,SSTLU1SOL,SSTLU1SVK,SSTLU1FLG,SSTLU1LFN,SSTLU1STS,SSTLU1FKT,SSTLU1ZTR,SSTLU1MOG,SSTLU1ART,SSTLU1FKZ,SSTLU1BAU,SSTLUGSTA,SSTLUGDAT,SSTLUGERF,SSTLU1NUM,SSTLU1PRM ) AS SELECT LugLug, LugTxt, LugLgp, LugAnz, LugDtm, LugMon, LugUnt, LugFir, LugAkr, LugPnr, LugKtx, Lu1Stz, Lu1Ztk, Lu1Lna, Lu1Lgr, Lu1Gvo, Lu1Gbi, Lu1Rre, Lu1Tgw, Lu1Kst, Lu1Ktr, Lu1Krt, Lu1Tge, Lu1Sol, Lu1Svk, Lu1Flg, Lu1Lfn, Lu1Sts, Lu1Fkt, Lu1Ztr, Lu1Mog, Lu1Art, Lu1Fkz, Lu1Bau, LugSta, LugDat, LugErf, Lu1Num, NULL FROM %SCHEMA%.TMS171LUG, %SCHEMA%.LU1 a WHERE LugLug = a.Lu1Num (+) ;
CREATE VIEW %SCHEMA%.LUGSCHNSTELLE_ALL ( SSTLUGLUG,SSTLUGTXT,SSTLUGLGP,SSTLUGANZ,SSTLUGDTM,SSTLUGMON,SSTLUGUNT,SSTLUGFIR,SSTLUGAKR,SSTLUGPNR,SSTLUGKTX,SSTLU1STZ,SSTLU1ZTK,SSTLU1LNA,SSTLU1LGR,SSTLU1GVO,SSTLU1GBI,SSTLU1RRE,SSTLU1TGW,SSTLU1KST,SSTLU1KTR,SSTLU1KRT,SSTLU1TGE,SSTLU1SOL,SSTLU1SVK,SSTLU1FLG,SSTLU1LFN,SSTLU1STS,SSTLU1FKT,SSTLU1ZTR,SSTLU1MOG,SSTLU1ART,SSTLU1FKZ,SSTLU1BAU,SSTLUGSTA,SSTLUGDAT,SSTLUGERF,SSTLU1NUM,SSTLU1PRM ) AS SELECT LugLug, LugTxt, LugLgp, LugAnz, LugDtm, LugMon, LugUnt, LugFir, LugAkr, LugPnr, LugKtx, Lu1Stz, Lu1Ztk, Lu1Lna, Lu1Lgr, Lu1Gvo, Lu1Gbi, Lu1Rre, Lu1Tgw, Lu1Kst, Lu1Ktr, Lu1Krt, Lu1Tge, Lu1Sol, Lu1Svk, Lu1Flg, Lu1Lfn, Lu1Sts, Lu1Fkt, Lu1Ztr, Lu1Mog, Lu1Art, Lu1Fkz, Lu1Bau, LugSta, LugDat, LugErf, Lu1Num, NULL FROM %SCHEMA%.TMS171LUG, %SCHEMA%.LU1_ALL a WHERE LugLug = a.Lu1Num (+) ;
CREATE VIEW %SCHEMA%.PAUSENMODELL ( PAUPSMNUM,PAUPSMTXT,PAUPSMANZ,PAUPSMANT,PAUPSMOPS,PAUPSMUNT,PAUPSMANI,PAUPSMZSB,PAUPSMUZI,PAUPSMZUI,PAUPSMGKZ,PAUPSELFD,PAUPSEVON,PAUPSEBIS,PAUPSEMIN,PAUPSEINT,PAUPSEPSE,PAUPSEZKT,PAUPSEBPU,PAUPSEAFU,PAUPSMSTA,PAUPSMDAT,PAUPSMERF,PAUPSENUM,PAUPSMZPP,PAUPSMPRM,PAUPSMZKT ) AS SELECT PsmNum, PsmTxt, PsmAnz, PsmAnt, PsmOps, PsmUnt, PsmAni, PsmZsb, PsmUzi, PsmZui, PsmGkz, PseLfd, PseVon, PseBis, PseMin, PseInt, PsePse, PseZkt, PseBpu, PseAfu, PsmSta, PseDat, PseErf, PseNum, PsmZpp, NULL, PsmZkt FROM %SCHEMA%.TMS142PSM, %SCHEMA%.TMS143PSE a WHERE PsmNum = a.PseNum AND a.PseLfd = ( SELECT MIN(b.PseLfd) FROM %SCHEMA%.TMS143PSE b WHERE PsmNum = b.PseNum) ;
CREATE VIEW %SCHEMA%.PAUSENMODELL_ALL ( PAUPSMNUM,PAUPSMTXT,PAUPSMANZ,PAUPSMANT,PAUPSMOPS,PAUPSMUNT,PAUPSMANI,PAUPSMZSB,PAUPSMUZI,PAUPSMZUI,PAUPSMGKZ,PAUPSELFD,PAUPSEVON,PAUPSEBIS,PAUPSEMIN,PAUPSEINT,PAUPSEPSE,PAUPSEZKT,PAUPSEBPU,PAUPSEAFU,PAUPSMSTA,PAUPSMDAT,PAUPSMERF,PAUPSENUM,PAUPSMZPP,PAUPSMPRM ) AS SELECT PsmNum, PsmTxt, PsmAnz, PsmAnt, PsmOps, PsmUnt, PsmAni, PsmZsb, PsmUzi, PsmZui, PsmGkz, PseLfd, PseVon, PseBis, PseMin, PseInt, PsePse, PseZkt, PseBpu, PseAfu, PsmSta, PseDat, PseErf, PseNum, PsmZpp, NULL FROM %SCHEMA%.TMS142PSM, %SCHEMA%.TMS143PSE a WHERE PsmNum = a.PseNum ;
CREATE VIEW %SCHEMA%.SALDENMODELL ( SLMSALNUM,SLMSALTXT,SLMSALSTA,SLMSALDAT,SLMSALERF,SLMSA1ZKT,SLMSA1ZTR,SLMSA1LFN,SLMSA1MAX,SLMSA1KMX,SLMSA1MIN,SLMSA1KMN,SLMSA1PZX,SLMSA1PZN,SLMSA1BEG,SLMSA1GSX,SLMSA1GSN,SLMSA1FKX,SLMSA1FKN,SLMSA1TKX,SLMSA1TKN,SLMSA1SDT,SLMSA1EDT,SLMSA1WRT,SLMSA1BSV,SLMSA1NUL,SLMSA1MSB,SLMSA1ZKB,SLMSA1UST,SLMSA1SAM ) AS SELECT SALNUM,SALTXT,SALSTA,SALDAT,SALERF ,SA1ZKT,SA1ZTR,SA1LFN,SA1MAX,SA1KMX,SA1MIN,SA1KMN,SA1PZX,SA1PZN,SA1BEG, SA1GSX,SA1GSN,SA1FKX,SA1FKN,SA1TKX,SA1TKN,SA1SDT,SA1EDT, SA1WRT,SA1BSV,SA1NUL,SA1MSB,SA1ZKB,SA1UST,SA1SAM FROM %SCHEMA%.TMS153SAL, %SCHEMA%.TMS153SA1 WHERE SALNUM = SA1NUM AND SA1LFN = 1 ;
CREATE VIEW %SCHEMA%.SALDENMODELL_ALL ( SLMSALNUM,SLMSALTXT,SLMSALSTA,SLMSALDAT,SLMSALERF,SLMSA1ZKT,SLMSA1ZTR,SLMSA1LFN,SLMSA1MAX,SLMSA1KMX,SLMSA1MIN,SLMSA1KMN,SLMSA1PZX,SLMSA1PZN,SLMSA1BEG,SLMSA1GSX,SLMSA1GSN,SLMSA1FKX,SLMSA1FKN,SLMSA1TKX,SLMSA1TKN,SLMSA1SDT,SLMSA1EDT,SLMSA1WRT,SLMSA1BSV,SLMSA1NUL,SLMSA1MSB,SLMSA1ZKB,SLMSA1UST,SLMSA1SAM ) AS SELECT SALNUM,SALTXT,SALSTA,SALDAT,SALERF ,SA1ZKT,SA1ZTR,SA1LFN,SA1MAX,SA1KMX,SA1MIN,SA1KMN,SA1PZX,SA1PZN,SA1BEG, SA1GSX,SA1GSN,SA1FKX,SA1FKN,SA1TKX,SA1TKN,SA1SDT,SA1EDT, SA1WRT,SA1BSV,SA1NUL,SA1MSB,SA1ZKB,SA1UST,SA1SAM FROM %SCHEMA%.TMS153SAL, %SCHEMA%.TMS153SA1 WHERE SALNUM = SA1NUM ;
CREATE VIEW %SCHEMA%.SCHICHTFREIZEIT ( SFMSFRMOD,SFMSFRANZ,SFMSFRTKT,SFMSFRMAX,SFMSFRVMO,SFMSFRJMO,SFMSFRTXT,SFMSFRSTA,SFMSFRDAT,SFMSFRERF,SFMSF1NUM,SFMSF1AZT,SFMSF1ANT,SFMSF1MIN,SFMSF1STA,SFMSF1DAT,SFMSF1ERF ) AS SELECT SFRMOD, SFRANZ, SFRTKT, SFRMAX, SFRVMO, SFRJMO, SFRTXT, SFRSTA, SFRDAT, SFRERF, SF1NUM, SF1AZT, SF1ANT, SF1MIN, SF1STA, SF1DAT, SF1ERF FROM %SCHEMA%.TMS155SFR, %SCHEMA%.TMS155SF1 A WHERE SFRMOD = A.SF1NUM AND A.SF1AZT = (SELECT MIN(B.SF1AZT) FROM %SCHEMA%.TMS155SF1 B WHERE SFRMOD = B.SF1NUM ) ;
CREATE VIEW %SCHEMA%.SCHICHTFREIZEIT_ALL ( SFMSFRMOD,SFMSFRANZ,SFMSFRTKT,SFMSFRMAX,SFMSFRVMO,SFMSFRJMO,SFMSFRTXT,SFMSFRSTA,SFMSFRDAT,SFMSFRERF,SFMSF1NUM,SFMSF1AZT,SFMSF1ANT,SFMSF1MIN,SFMSF1STA,SFMSF1DAT,SFMSF1ERF ) AS SELECT SFRMOD, SFRANZ, SFRTKT, SFRMAX, SFRVMO, SFRJMO, SFRTXT, SFRSTA, SFRDAT, SFRERF, SF1NUM, SF1AZT, SF1ANT, SF1MIN, SF1STA, SF1DAT, SF1ERF FROM %SCHEMA%.TMS155SFR, %SCHEMA%.TMS155SF1 A WHERE SFRMOD = A.SF1NUM ;
CREATE VIEW %SCHEMA%.STUNDENSATZ ( KSTKSSNUM,KSTKSSTXT,KSTPSSNUM,KSTPSSGVO,KSTPSSGBI,KSTPSSTXT,KSTPSSWRT,KSTPSSLHN,KSTPSSDAZ,KSTKSSSTA,KSTKSSDAT,KSTKSSERF ) AS SELECT KSSNUM, KSSTXT, PSSNUM, PSSGVO, PSSGBI, PSSTXT, PSSWRT, PSSLHN, PSSDAZ, KSSSTA, KSSDAT, KSSERF FROM %SCHEMA%.TMS708KSS, %SCHEMA%.TMS708PSS WHERE KSSNUM = PSSKSS AND PSSGVO = (SELECT MAX(PSSGVO) FROM %SCHEMA%.TMS708PSS A WHERE A.PSSKSS = KSSNUM) ;
CREATE VIEW %SCHEMA%.STUNDENSATZ_ALL ( KSTKSSNUM,KSTKSSTXT,KSTPSSNUM,KSTPSSGVO,KSTPSSGBI,KSTPSSTXT,KSTPSSWRT,KSTPSSLHN,KSTPSSDAZ,KSTKSSSTA,KSTKSSDAT,KSTKSSERF ) AS SELECT KSSNUM, KSSTXT, PSSNUM, PSSGVO, PSSGBI, PSSTXT, PSSWRT, PSSLHN, PSSDAZ, KSSSTA, KSSDAT, KSSERF FROM %SCHEMA%.TMS708KSS, %SCHEMA%.TMS708PSS WHERE KSSNUM = PSSKSS ;
CREATE VIEW %SCHEMA%.TMA100TBS ( TBSTNA,TBSLFD,TBSFNA ) AS select TBSTNA, TBSLFD, TBSFNA from TMSYSTEM.TMS100TBS ;
CREATE VIEW %SCHEMA%.VERRECHNART ( VRGVRANUM,VRGVRATXT,VRGVRAKTX,VRGVR1TTP,VRGVR1FC1,VRGVR1FC2,VRGVR1FHL,VRGVR1ZGS,VRGVR1MZP,VRGVRASTA,VRGVRADAT,VRGVRAERF,VRGVR1NUM ) AS SELECT VraNum, VraTxt, VraKtx, Vr1Ttp, Vr1Fc1, Vr1Fc2, Vr1Fhl, Vr1Zgs, Vr1Mzp, VraSta, Vr1Dat, Vr1Erf, Vr1Num FROM %SCHEMA%.TMS151VRA, %SCHEMA%.TMS151VR1 a WHERE VraNum = a.Vr1Num AND a.Vr1Ttp = (SELECT MIN(b.Vr1Ttp) FROM %SCHEMA%.TMS151VR1 b WHERE b.Vr1Num = VraNum) ;
CREATE VIEW %SCHEMA%.VERRECHNART_ALL ( VRGVRANUM,VRGVRATXT,VRGVRAKTX,VRGVR1TTP,VRGVR1FC1,VRGVR1FC2,VRGVR1FHL,VRGVR1ZGS,VRGVR1MZP,VRGVRASTA,VRGVRADAT,VRGVRAERF,VRGVR1NUM ) AS SELECT VraNum, VraTxt, VraKtx, Vr1Ttp, Vr1Fc1, Vr1Fc2, Vr1Fhl, Vr1Zgs, Vr1Mzp, VraSta, Vr1Dat, Vr1Erf, Vr1Num FROM %SCHEMA%.TMS151VRA, %SCHEMA%.TMS151VR1 a WHERE VraNum = a.Vr1Num ;
CREATE VIEW %SCHEMA%.ZEITABZULAGEN ( ZAZZSBNUM,ZAZZSBTXT,ZAZZSBKTX,ZAZZS1LST,ZAZZS1FCD,ZAZZS1ZPZ,ZAZZS1VON,ZAZZS1BIS,ZAZZS1ZKT,ZAZZS1ZK2,ZAZZS1ZK3,ZAZZS1ZK4,ZAZZS1PRZ,ZAZZS1PR2,ZAZZS1PR3,ZAZZS1PR4,ZAZZS1MKT,ZAZZS1MZK,ZAZZS1MZP,ZAZZS1Z1G,ZAZZS1Z2G,ZAZZS1Z3G,ZAZZS1Z4G,ZAZZS1Z1N,ZAZZS1Z2N,ZAZZS1Z3N,ZAZZS1Z4N,ZAZZSBSTA,ZAZZSBDAT,ZAZZSBERF,ZAZZS1NUM ) AS SELECT ZsbNum, ZsbTxt, ZsbKtx, Zs1Lst, Zs1Fcd, Zs1Zpz, Zs1Von, Zs1Bis, Zs1Zkt, Zs1Zk2, Zs1Zk3, Zs1Zk4, Zs1Prz, Zs1Pr2, Zs1Pr3, Zs1Pr4, Zs1Mkt, Zs1Mzk, Zs1Mzp, Zs1Z1g, Zs1Z2g, Zs1Z3g, Zs1Z4g, Zs1Z1n, Zs1Z2n, Zs1Z3n, Zs1Z4n, ZsbSta, Zs1Dat, Zs1Erf, Zs1Num FROM %SCHEMA%.TMS152ZSB, %SCHEMA%.TMS152ZS1 a WHERE ZsbNum = a.Zs1Num AND a.Zs1Lst = (SELECT MIN(b.Zs1Lst) FROM %SCHEMA%.TMS152ZS1 b WHERE b.Zs1Num = ZsbNum) AND a.Zs1Fcd = (SELECT MIN(c.Zs1Fcd) FROM %SCHEMA%.TMS152ZS1 c WHERE c.Zs1Num = ZsbNum AND c.Zs1Lst = a.Zs1Lst) AND a.Zs1Von = (SELECT MIN(d.Zs1Von) FROM %SCHEMA%.TMS152ZS1 d WHERE d.Zs1Num = ZsbNum AND d.Zs1Lst = a.Zs1Lst AND d.Zs1Fcd = a.Zs1Fcd) AND a.Zs1Zpz = (SELECT MIN(e.Zs1Zpz) FROM %SCHEMA%.TMS152ZS1 e WHERE e.Zs1Num = ZsbNum AND e.Zs1Lst = a.Zs1Lst AND e.Zs1Fcd = a.Zs1Fcd AND e.Zs1Von = a.Zs1Von) ;
CREATE VIEW %SCHEMA%.ZEITABZULAGEN_ALL ( ZAZZSBNUM,ZAZZSBTXT,ZAZZSBKTX,ZAZZS1LST,ZAZZS1FCD,ZAZZS1ZPZ,ZAZZS1VON,ZAZZS1BIS,ZAZZS1ZKT,ZAZZS1ZK2,ZAZZS1ZK3,ZAZZS1ZK4,ZAZZS1PRZ,ZAZZS1PR2,ZAZZS1PR3,ZAZZS1PR4,ZAZZS1MKT,ZAZZS1MZK,ZAZZS1MZP,ZAZZS1Z1G,ZAZZS1Z2G,ZAZZS1Z3G,ZAZZS1Z4G,ZAZZS1Z1N,ZAZZS1Z2N,ZAZZS1Z3N,ZAZZS1Z4N,ZAZZSBSTA,ZAZZSBDAT,ZAZZSBERF,ZAZZS1NUM ) AS SELECT ZsbNum, ZsbTxt, ZsbKtx, Zs1Lst, Zs1Fcd, Zs1Zpz, Zs1Von, Zs1Bis, Zs1Zkt, Zs1Zk2, Zs1Zk3, Zs1Zk4, Zs1Prz, Zs1Pr2, Zs1Pr3, Zs1Pr4, Zs1Mkt, Zs1Mzk, Zs1Mzp, Zs1Z1g, Zs1Z2g, Zs1Z3g, Zs1Z4g, Zs1Z1n, Zs1Z2n, Zs1Z3n, Zs1Z4n, ZsbSta, Zs1Dat, Zs1Erf, Zs1Num FROM %SCHEMA%.TMS152ZSB, %SCHEMA%.TMS152ZS1 a WHERE ZsbNum = a.Zs1Num ;
CREATE VIEW %SCHEMA%.URLAUBSMODELL ( SURSURNAM,SURSURTXT,SURSURVMO,SURSURMAX,SURSURMIN,SURSURSTA,SURSURERF,SURSURDAT,SURSURMOD,SURSU2ZKT,SURSU2STD,SURSU2TGE,SURSU2ZK2,SURSU2ZK3,SURSU2ZK4,SURSU1AGR,SURSU1MON,SURSU1TGE,SURSU1MAX,SURSU3AGR,SURSU3STD,SURSU3TGE,SURSU3ZKT,SURSU3FKT,SURSU3ZK1,SURSU3FK1,SURSU3ZK2,SURSU3FK2,SURSU3ZK3,SURSU3FK3,SURSU1STA,SURSU2STA,SURSU3STA ) AS SELECT SURNAM,SURTXT,SURVMO,SURMAX,SURMIN,SURSTA,SURERF,SURDAT,SURMOD, '',0,0,'','','', '','','','', '',0,0,'','','','','','','','', 1,1,1 FROM %SCHEMA%.TMS155SUR ;
CREATE VIEW %SCHEMA%.LOHNFORTZ ( LHNLFZNUM,LHNLFZTXT,LHNLF1ZRV,LHNLF1ZRB,LHNLF1LT1,LHNLF1LT2,LHNLF1FC1,LHNLF1FC2,LHNLF1FZK,LHNLFZSTA,LHNLFZDAT,LHNLFZERF,LHNLF1NUM ) AS SELECT LFZNUM, LFZTXT, LF1ZRV, LF1ZRB, LF1LT1, LF1LT2, LF1FC1, LF1FC2, LF1FZK, LFZSTA, LF1DAT, LF1ERF, LF1NUM FROM %SCHEMA%.TMS154LFZ, %SCHEMA%.TMS154LF1 A WHERE LFZNUM = A.LF1NUM AND A.LF1ZRV = (SELECT MIN(B.LF1ZRV) FROM %SCHEMA%.TMS154LF1 B WHERE B.LF1NUM = LFZNUM) ;
CREATE VIEW %SCHEMA%.LOHNFORTZ_ALL ( LHNLFZNUM,LHNLFZTXT,LHNLF1ZRV,LHNLF1ZRB,LHNLF1LT1,LHNLF1LT2,LHNLF1FC1,LHNLF1FC2,LHNLF1FZK,LHNLFZSTA,LHNLFZDAT,LHNLFZERF,LHNLF1NUM ) AS SELECT LFZNUM, LFZTXT, LF1ZRV, LF1ZRB, LF1LT1, LF1LT2, LF1FC1, LF1FC2, LF1FZK, LFZSTA, LF1DAT, LF1ERF, LF1NUM FROM %SCHEMA%.TMS154LFZ, %SCHEMA%.TMS154LF1 A WHERE LFZNUM = A.LF1NUM ;
CREATE VIEW %SCHEMA%.ARBZEITMODELL ( AZMAZTNUM,AZMAZTTXT,AZMAZVGVO,AZMAZVGBI,AZMAZVATV,AZMAZVATB,AZMAZVMAV,AZMAZVMAB,AZMAZVNOV,AZMAZVNOB,AZMAZVGLV,AZMAZVGLB,AZMAZVKEV,AZMAZVKEB,AZMAZVSOL,AZMAZVSO2,AZMAZVVHS,AZMAZVHSO,AZMAZVHS2,AZMAZVHTV,AZMAZVSBG,AZMAZVAZP,AZMAZVPSM,AZMAZVGLT,AZMAZVZPZ,AZMAZVSKZ,AZMAZVKMA,AZMAZVDAT,AZMAZVERF,AZMAZVSTA,AZMAZVNUM,AZMAZVZTR ) AS SELECT AztNum, AztTxt, AzvGvo, AzvGbi, AzvAtv, AzvAtb, AzvMav, AzvMab, AzvNov, AzvNob, AzvGlv, AzvGlb, AzvKev, AzvKeb, AzvSol, AzvSo2, AzvVhs, AzvHso, AzvHs2, AzvHtv, AzvSbg, AzvAzp, AzvPsm, AzvGlt, AzvZpz, AzvSkz, AzvKma, AzvDat, AzvErf, AzvSta, AzvNum, NULL FROM %SCHEMA%.TMS140AZT, %SCHEMA%.TMS140AZV a WHERE AztNum = a.AzvNum ;
CREATE VIEW %SCHEMA%.MITARBQUAL ( ZUOQZPPNR,ZUOQZPVON,ZUOQZPBIS,ZUOQZPQFK,ZUOQZPTXT,ZUOQZPNAM,ZUOQZPPRI,ZUOQZPSTA,ZUOQZPDAT,ZUOQZPERF ) AS SELECT QZPPNR, MAX(QZPVON), NULLIF(MAX(COALESCE(QZPBIS, TO_DATE('20991231', 'yyyymmdd'))),TO_DATE('20991231', 'yyyymmdd')) , MIN(QZPQFK), MIN(QZPTXT), PNRNAM, MIN(QZPPRI), MIN(QZPSTA), MIN(QZPDAT), MIN(QZPERF) FROM %SCHEMA%.TMS716QZP, %SCHEMA%.TMS300PNR WHERE QZPPNR = PNRNUM GROUP BY QZPPNR, PNRNAM ;
CREATE VIEW %SCHEMA%.SALDENVORTRAG ( SAVSVTPNR,SAVSVTNAM,SAVSVTDTM,SAVSVTVT1,SAVSVTVT2,SAVSVTVT3,SAVSVTVT4,SAVSVTVT5,SAVSVTVT6,SAVSVTVT7,SAVSVTSTA,SAVSVTDAT,SAVSVTERF,SAVSVTNPK,SAVSVTUGL,SAVSVTUMA,SAVSVTUVZ,SAVSVTUJZ,SAVSVTUS5,SAVSVTUS6,SAVSVTUS7,SAVSVTUNP ) AS SELECT SVTPNR, PNRNAM, SVTDTM, SVTVT1, SVTVT2, SVTVT3, SVTVT4, SVTVT5, SVTVT6, SVTVT7, SVTSTA, SVTDAT, SVTERF, SVTNPK, SVTUGL, SVTUMA, SVTUVZ, SVTUJZ, SVTUS5, SVTUS6, SVTUS7, SVTUNP FROM %SCHEMA%.TMS320SVT, %SCHEMA%.TMS300PNR WHERE SVTPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.URLAUBSVORTRAG ( UVTURVPNR,UVTURVNAM,UVTURVJHR,UVTURVVJR,UVTURVVUR,UVTURVUAN,UVTURVJUB,UVTURVSBH,UVTURVGUR,UVTURVSTA,UVTURVDAT,UVTURVERF,UVTURVABG,UVTURVZUS,UVTURVJMO,UVTURVMIN,UVTURVVTG,UVTURVSUL ) AS SELECT URVPNR, PNRNAM, URVJHR, URVVJR, URVVUR, URVUAN, URVJUB, URVSBH, URVGUR, URVSTA, URVDAT, URVERF, URVABG, URVZUS, URVJMO, URVMIN, URVVTG, URVSUL FROM %SCHEMA%.TMS332URV, %SCHEMA%.TMS300PNR WHERE URVPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.SCHICHTURV ( SUVSUVPNR,SUVSUVNAM,SUVSUVJHR,SUVSUVVTG,SUVSUVVTS,SUVSUVMAN,SUVSUVSTA,SUVSUVERF,SUVSUVDAT ) AS SELECT SUVPNR, PNRNAM, SUVJHR, SUVVTG, SUVVTS, SUVMAN, SUVSTA, SUVERF, SUVDAT FROM %SCHEMA%.TMS332SUV, %SCHEMA%.TMS300PNR WHERE SUVPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.SCHICHTFZV ( SFVSFVPNR,SFVSFVNAM,SFVSFVJHR,SFVSFVVTG,SFVSFVVAN,SFVSFVMAN,SFVSFVSTA,SFVSFVERF,SFVSFVDAT,SFVSFVZUS ) AS SELECT SFVPNR, PNRNAM, SFVJHR, SFVVTG, SFVVAN, SFVMAN, SFVSTA, SFVERF, SFVDAT, SFVZUS FROM %SCHEMA%.TMS332SFV, %SCHEMA%.TMS300PNR WHERE SFVPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.IDSZURPNR ( ASWPNR,ASW1,ASW2,ASW3,ASW4,ASW5,BAKDTM ) AS SELECT PNRNUM AS ASWPNR, ( SELECT MIN(ASWNUM) FROM TMSYSTEM.TMS301ASW WHERE ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) ) AS ASW1, ( SELECT MIN(B.ASWNUM) FROM TMSYSTEM.TMS301ASW B WHERE B.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND B.ASWNUM > ( SELECT MIN(A.ASWNUM) FROM TMSYSTEM.TMS301ASW A WHERE A.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) ) ) AS ASW2, ( SELECT MIN(C.ASWNUM) FROM TMSYSTEM.TMS301ASW C WHERE C.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND C.ASWNUM > ( SELECT MIN(B.ASWNUM) FROM TMSYSTEM.TMS301ASW B WHERE B.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND B.ASWNUM > ( SELECT MIN(A.ASWNUM) FROM TMSYSTEM.TMS301ASW A WHERE A.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) ) ) ) AS ASW3, ( SELECT MIN(D.ASWNUM) FROM TMSYSTEM.TMS301ASW D WHERE D.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND D.ASWNUM > ( SELECT MIN(C.ASWNUM) FROM TMSYSTEM.TMS301ASW C WHERE C.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND C.ASWNUM > ( SELECT MIN(B.ASWNUM) FROM TMSYSTEM.TMS301ASW B WHERE B.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND B.ASWNUM > ( SELECT MIN(A.ASWNUM) FROM TMSYSTEM.TMS301ASW A WHERE A.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) ) ) ) ) AS ASW4, ( SELECT MIN(E.ASWNUM) FROM TMSYSTEM.TMS301ASW E WHERE E.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND E.ASWNUM > ( SELECT MIN(D.ASWNUM) FROM TMSYSTEM.TMS301ASW D WHERE D.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND D.ASWNUM > ( SELECT MIN(C.ASWNUM) FROM TMSYSTEM.TMS301ASW C WHERE C.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND C.ASWNUM > ( SELECT MIN(B.ASWNUM) FROM TMSYSTEM.TMS301ASW B WHERE B.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) AND B.ASWNUM > ( SELECT MIN(A.ASWNUM) FROM TMSYSTEM.TMS301ASW A WHERE A.ASWPNR = PNRNUM AND ASWMND = MNDNUM AND ASWLIZ = MNDLIZ AND (ASWGBI >= BAKDTM OR ASWGBI IS NULL) ) ) ) ) ) AS ASW5, BAKDTM FROM %SCHEMA%.TMS300PNR, TMSYSTEM.TMS133BAK, TMSYSTEM.TMS112MND WHERE UPPER(MNDSCH) = UPPER('%SCHEMA%') ;
CREATE VIEW %SCHEMA%.AKTUELLEBSV ( BSVPNR,BSVGVO,BSVGBI,BSVAKR,BSVKAL,BSVSAL,BSVURL,BSVLFZ,BSVSFR,BSVTGS,BSVWCH,BSVMON,BSVJHR,BSVAGM,BSVABR,BSVSTA,BSVDAT,BSVERF,BSVBGR,BSVLUG,BSVJAZ,BSVJUR,BSVAGV,BSVBON,BSVBDE,BSVPEP,BSVDMS,BSVPNM,BSVRE0,BSVRE1,BSVRE2,BSVRE3,BSVRE4,BSVRE5,BSVRE6,BSVRE7,BSVRE8,BSVRE9,BSVDWT,BSVPZM,BSVZUT,BSVMAP,BSVDWH,BSVPSI,BSVMMA,BSVPRZ,BSVJAM,BSVSUL,BSVKSM,BSVKSW,BAKDTM,BAKJHR,BAKMON,BAKTAG,BAKWCH,BAKWTG,BAKJTG,BAKDAT,BAKERF,BAKYOW,BAKCSM,BAKSTA,BAKQUA,BAKHJH,BSVWAM,BSVWPZ,BSVWAR,BSVFST ) AS select BSVPNR, BSVGVO, BSVGBI, BSVAKR, BSVKAL, BSVSAL, BSVURL, BSVLFZ, BSVSFR, BSVTGS, BSVWCH, BSVMON, BSVJHR, BSVAGM, BSVABR, BSVSTA, BSVDAT, BSVERF, BSVBGR, BSVLUG, BSVJAZ, BSVJUR, BSVAGV, BSVBON, BSVBDE, BSVPEP, BSVDMS, BSVPNM, BSVRE0, BSVRE1, BSVRE2, BSVRE3, BSVRE4, BSVRE5, BSVRE6, BSVRE7, BSVRE8, BSVRE9, BSVDWT, BSVPZM, BSVZUT, BSVMAP, BSVDWH, BSVPSI, BSVMMA, BSVPRZ, BSVJAM, BSVSUL, BSVKSM, BSVKSW, BAKDTM, BAKJHR, BAKMON, BAKTAG, BAKWCH, BAKWTG, BAKJTG, BAKDAT, BAKERF, BAKYOW, BAKCSM, BAKSTA, BAKQUA, BAKHJH, BSVWAM, BSVWPZ, BSVWAR, BSVFST from %SCHEMA%.tms310bsv a, tmsystem.tms133bak where ( (a.bsvgvo<=bakdtm and (a.bsvgbi>=bakdtm or a.bsvgbi is null)) or ( (select count(*) from %SCHEMA%.tms310bsv b where b.bsvpnr=a.bsvpnr and b.bsvgvo<=bakdtm and (b.bsvgbi>=bakdtm or b.bsvgbi is null) ) = 0 and a.bsvgvo = (select max(b.bsvgvo) from %SCHEMA%.tms310bsv b where b.bsvgvo<bakdtm and b.bsvpnr = a.bsvpnr) ) or ( (select count(*) from %SCHEMA%.tms310bsv c where c.bsvgvo<=bakdtm and c.bsvpnr=a.bsvpnr and (a.bsvgbi>=bakdtm or a.bsvgbi is null) ) =0 and (select count(*) from %SCHEMA%.tms310bsv where (select count(*) from %SCHEMA%.tms310bsv b where b.bsvpnr=a.bsvpnr and b.bsvgvo<=bakdtm and (b.bsvgbi>=bakdtm or b.bsvgbi is null) ) = 0 and a.bsvgvo = (select max(b.bsvgvo) from %SCHEMA%.tms310bsv b where b.bsvgvo<bakdtm and b.bsvpnr = a.bsvpnr) ) = 0 and a.bsvgvo = (select min(b.bsvgvo) from %SCHEMA%.tms310bsv b where b.bsvgvo>bakdtm and b.bsvpnr = a.bsvpnr) ) ) ;
CREATE VIEW %SCHEMA%.PERSONALSTAMM_STICHTAG ( PSTPNRNUM,PSTPNRANM,PSTPNRAKT,PSTPNREND,PSTPNREML,PSTPNRMRT,PSTPNRFRT,PSTPNRBEA,PSTPNRGEB,PSTPNREIN,PSTPNRNAM,PSTPNRSTR,PSTPNRPLZ,PSTPNRORT,PSTPNRTEL,PSTPNRMOB,PSTPNRTIT,PSTPNRANR,PSTPNRNEB,PSTPNRSTA,PSTPNRDAT,PSTPNRERF,PSTPNRSPR,PSTPNRFRE,PSTPNRGBI,PSTPNRKRD,PSTPNRPRT,PSTPNRLDA,PSTPNRFMO,PSTBSVAKR,PSTBSVKAL,PSTBSVSAL,PSTBSVURL,PSTBSVLFZ,PSTBSVSFR,PSTBSVMON,PSTBSVAGM,PSTBSVABR,PSTBSVBGR,PSTBSVLUG,PSTBSVAGV,PSTBSVPEP,PSTBSVDMS,PSTBSVPNM,PSTBSVRE0,PSTBSVRE1,PSTBSVRE2,PSTBSVRE3,PSTBSVRE4,PSTBSVRE5,PSTBSVDWT,PSTBSVPZM,PSTBSVZUT,PSTBSVMAP,PSTBSVDWH,PSTBSVMMA,PSTBSVPRZ,PSTBSVJAM,PSTBSVSUL,PSTZUOABT,PSTZUOVRA,PSTZUOAGR,PSTZUOSDT,PSTZUOSTG,PSTZUOKST,PSTZUOKTR,PSTASWNU1,PSTASWNU2,PSTASWNU3,PSTASWNU4,PSTBSVKSM,PSTASWNU5,STICHTAG,PSTPNRSAL,PSTZUOKSB,PSTPNRMTA,PSTPNRAGE,PSTBSVWAM,PSTBSVWPZ,PSTBSVWAR,PSTPNR1NM,PSTPNR2NM,PSTPNRNBU,PSTPNRAZU,PSTPNRABF,PSTPNRABE,PSTBSVFST ) AS select PNRNUM, PNRANM, PNRAKT, PNREND, PNREML, PNRMRT, PNRFRT, PNRBEA, PNRGEB, PNREIN, PNRNAM, PNRSTR, PNRPLZ, PNRORT, PNRTEL, PNRMOB, PNRTIT, PNRANR, PNRNEB, PNRSTA, PNRDAT, PNRERF, PNRSPR, PNRFRE, PNRGBI, PNRKRD, PNRPRT, PNRLDA, PNRFMO, BSVAKR, BSVKAL, BSVSAL, BSVURL, BSVLFZ, BSVSFR, BSVMON, BSVAGM, BSVABR, BSVBGR, BSVLUG, BSVAGV, BSVPEP, BSVDMS, BSVPNM, BSVRE0, BSVRE1, BSVRE2, BSVRE3, BSVRE4, BSVRE5, BSVDWT, BSVPZM, BSVZUT, BSVMAP, BSVDWH, BSVMMA, BSVPRZ, BSVJAM, BSVSUL, ZUOABT, ZUOVRA, ZUOAGR, ZUOSDT, ZUOSTG, ZUOKST, ZUOKTR, ASW1, ASW2, ASW3, ASW4, BSVKSM, ASW5, stichtag.bakdtm as STICHTAG, PNRSAL, ZUOKSB, PNRMTA, PNRAGE, BSVWAM, BSVWPZ, BSVWAR, PNR1NM, PNR2NM, PNRNBU, PNRAZU, PNRABF, PNRABE, BSVFST from %SCHEMA%.tms300pnr, %SCHEMA%.AKTUELLEBSV a, %SCHEMA%.aktuellezuo b, %SCHEMA%.IDSZURPNR d, tmsystem.tms133bak stichtag where pnrnum = bsvpnr and pnrnum = zuopnr and pnrnum = aswpnr and a.bakdtm = stichtag.bakdtm and b.bakdtm = stichtag.bakdtm and d.bakdtm = stichtag.bakdtm ;
CREATE VIEW %SCHEMA%.FSTVORTRAG ( FTVPNR,FTVNAM,FTVJHR,FTVVJR,FTVVUR,FTVUAN,FTVGUR,FTVSTA,FTVDAT,FTVERF,FTVABG,FTVZUS,FTVMIN,FTVVTG ) AS SELECT FTVPNR, PNRNAM, FTVJHR, FTVVJR, FTVVUR, FTVUAN, FTVGUR, FTVSTA, FTVDAT, FTVERF, FTVABG, FTVZUS, FTVMIN, FTVVTG FROM %SCHEMA%.TMS332FTV, %SCHEMA%.TMS300PNR WHERE FTVPNR = PNRNUM ;
CREATE VIEW %SCHEMA%.TMS899BMV ( BMVNUM,BMVTXT,BMVGVO,BMVGBI,BMVUVO,BMVUBI,BMVKKZ,BMVLFD,BMVBSA,BMVEIN,BMVWTG,BMVTTP,BMVGEE,BMVZKT,BMVZKTTXT,BMVGEW,BMVZKE,BMVZKETXT,BMVANW ) AS (SELECT Bm1Num, (SELECT BmdTxt FROM %SCHEMA%.TMS157BMD c WHERE c.BmdNum = a.Bm1Num), TO_CHAR(Bm1Gvo, 'yyyymmdd'), TO_CHAR(Bm1Gbi, 'yyyymmdd'), Bm1Uvo, Bm1Ubi, Bm1Kkz, Bm1Lfd, Bm1Bsa, Bm1Ein, Bm1Wtg, Bm1Ttp, Bm1Gee, Bm1Zkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT b WHERE b.ZktNum = a.Bm1Zkt), Bm1Gew, Bm1Zke, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT c WHERE c.ZktNum = a.Bm1Zke), a.Bm1Anw FROM %SCHEMA%.TMS157BM1 a WHERE Bm1Sta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899BSS ( BSSPLG,PLGTXT,BSSESO,ESOTXT,BSSESA,ESATXT,BSSTTP,TT2TXT,BSSBSZ,BSSVON,BSSBIS,BSSTXT,BSSASW,BSSSOL,BSSMIN,BSSMAX,BSSMWT,BSSATG,BSSKGW,BSSKMW ) AS (SELECT BssPlg, PlgTxt, BssEso, EsoTxt, BssEsa, EsaTxt, BssTtp, Tt2Txt, BssBsz, BssVon, BssBis, BssTxt, BssAsw, BssSol, BssMin, BssMax, BssMwt, BssAtg, BssKgw, BssKmw FROM %SCHEMA%.TMS720BSS, %SCHEMA%.TMS701PLG, %SCHEMA%.TMS705ESO, %SCHEMA%.TMS703ESA, %SCHEMA%.TMS132TT2 WHERE PlgPlg = BssPlg AND EsoEso = BssEso AND EsaEsa = BssEsa AND Tt2Num = BssTtp) ;
CREATE VIEW %SCHEMA%.TMS899BSM ( BSMBDT,BSMESO,BSMESA,BSMSZV,BSMSZB,BSMBSZ,BSMJHR,BSMMON,BSMWOC,BSMJWC,BSMTAG,BSMTTP,BSMSOL,BSMMIN,BSMMAX,BSMIST,BSMPLG,BSMHTG,BSMHTV,BSMHTN,BSMASW,BSMKNG,BSMFKT,BSMSTA,BSMDAT,BSMERF,BSMID1,BSMID2,BSMID3,BSMID4,BSMID5,BSMID6,BSMID7,BSMID8,BSMKOR,BSMKOS,BSMKO2 ) AS (SELECT TO_CHAR(BsmBdt, 'YYYYMMDD'), BsmEso, BsmEsa, BsmSzv, BsmSzb, BsmBsz, BsmJhr, BsmMon, BsmWoc, BsmJwc, BsmTag, BsmTtp, BsmSol, BsmMin, BsmMax, BsmIst, BsmPlg, BsmHtg, BsmHtv, BsmHtn, BsmAsw, BsmKng, BsmFkt, BsmSta, BsmDat, BsmErf, BsmId1, BsmId2, BsmId3, BsmId4, BsmId5, BsmId6, BsmId7, BsmId8, BsmKor, BsmKos, BsmKo2 FROM %SCHEMA%.TMS725BSM) ;
CREATE VIEW %SCHEMA%.TMS899QZP ( QZPPNR,QZPNAM,QZPVON,QZPBIS,QZPQFK,QZPQFKTXT,QZPPRI ) AS (SELECT QzpPnr, PnrNam, TO_CHAR(QzpVon, 'yyyymmdd'), TO_CHAR(QzpBis, 'yyyymmdd'), QzpQfk, QfkTxt, QzpPri FROM %SCHEMA%.TMS716QZP, %SCHEMA%.TMS300PNR, %SCHEMA%.TMS707QFK WHERE PnrNum = QzpPnr AND QfkQfk = QzpQfk) ;
CREATE VIEW %SCHEMA%.TMS899PBV ( PBVPNR,PBVNAM,PBVGVO,PBVGBI,PBVESO,PBVESOTXT,PBVESA,PBVESATXT,PBVAWP,PBVZRM,PBVSST ) AS (SELECT PbvPnr, PnrNam, TO_CHAR(PbvGvo, 'yyyymmdd'), TO_CHAR(PbvGbi, 'yyyymmdd'), PbvEso, EsoTxt, PbvEsa, EsaTxt, PbvAwp, PbvZrm, PbvSst FROM %SCHEMA%.TMS710PBV, %SCHEMA%.TMS300PNR, %SCHEMA%.TMS705ESO, %SCHEMA%.TMS703ESA WHERE PnrNum = PbvPnr AND EsoEso = PbvEso AND EsaEsa = PbvEsa) ;
CREATE VIEW %SCHEMA%.TMS899PGZ ( PGZPLG,PGZPLGTXT,PGZESO,PGZESOTXT,PGZESA,PGZESATXT,PGZTXT,PGZKST,PGZKSTTXT,PGZAZK,PGZRZK ) AS (SELECT PgzPlg, PlgTxt, PgzEso, EsoTxt, PgzEsa, EsaTxt, PgzTxt, PgzKst, (SELECT KstTxt FROM %SCHEMA%.O1003KSTSTELLE WHERE KstNum = PgzKst), PgzAzk, PgzRzk FROM %SCHEMA%.TMS712PGZ, %SCHEMA%.TMS701PLG, %SCHEMA%.TMS705ESO, %SCHEMA%.TMS703ESA WHERE PlgPlg = PgzPlg AND EsoEso = PgzEso AND EsaEsa = PgzEsa) ;
CREATE VIEW %SCHEMA%.TMS899QEA ( QEAESA,QEAESATXT,QEAQFK,QEAQFKTXT,QEATXT ) AS (SELECT QeaEsa, EsaTxt, QeaQfk, QfkTxt, QeaTxt FROM %SCHEMA%.TMS717QEA, %SCHEMA%.TMS703ESA, %SCHEMA%.TMS707QFK WHERE EsaEsa = QeaEsa AND QfkQfk = QeaQfk) ;
CREATE VIEW %SCHEMA%.TMS899SA1 ( SA1NUM,SA1TXT,SA1BEG,SA1ZKT,SA1ZKTTXT,SA1LFN,SA1ZTR,SA1MAX,SA1MIN,SA1KMX,SA1KMXTXT,SA1KMN,SA1KMNTXT ) AS (SELECT Sa1Num, (SELECT b.SalTxt FROM %SCHEMA%.TMS153SAL b WHERE a.Sa1Num = b.SalNum), Sa1Beg, Sa1Zkt, (SELECT c.ZktTxt FROM %SCHEMA%.TMS130ZKT c WHERE a.Sa1Zkt = c.ZktNum), Sa1Lfn, Sa1Ztr, Sa1Max, Sa1Min, Sa1Kmx, (SELECT d.ZktTxt FROM %SCHEMA%.TMS130ZKT d WHERE a.Sa1Kmx = d.ZktNum), Sa1Kmn, (SELECT e.ZktTxt FROM %SCHEMA%.TMS130ZKT e WHERE a.Sa1Kmn = e.ZktNum) FROM %SCHEMA%.TMS153SA1 a WHERE a.Sa1Sta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899SUR ( SURNAM,SURTXT,SURVMO,SURMAX,SURMIN,SURMOD,SU1AGR,SU1MON,SU1TGE,SU1MAX,SU2ZKT,SU2ZKTTXT,SU2STD,SU2TGE,SU2ZK2,SU2ZK3,SU2ZK4,SU3AGR,SU3STD,SU3TGE,SU3ZKT,SU3ZKTTXT,SU3FKT,SU3ZK1,SU3FK1,SU3ZK2,SU3FK2,SU3ZK3,SU3FK3 ) AS (SELECT SurNam, SurTxt, SurVmo, SurMax, SurMin, SurMod, Su1Agr, Su1Mon, Su1Tge, Su1Max, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null FROM %SCHEMA%.TMS155SUR, %SCHEMA%.TMS155SU1 WHERE SurNam = Su1Nam AND SurSta <> 9 UNION SELECT SurNam, SurTxt, SurVmo, SurMax, SurMin, SurMod, null, null, null, null, Su2Zkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE Zktnum = Su2Zkt), Su2Std, Su2Tge, Su2Zk2, Su2Zk3, Su2Zk4, null, null, null, null, null, null, null, null, null, null, null, null FROM %SCHEMA%.TMS155SUR, %SCHEMA%.TMS155SU2 WHERE SurNam = Su2Nam AND SurSta <> 9 UNION SELECT SurNam, SurTxt, SurVmo, SurMax, SurMin, SurMod, null, null, null, null, null, null, null, null, null, null, null, Su3Agr, Su3Std, Su3Tge, Su3Zkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE Zktnum = Su3Zkt), Su3Fkt, Su3Zk1, Su3Fk1, Su3Zk2, Su3Fk2, Su3Zk3, Su3Fk3 FROM %SCHEMA%.TMS155SUR, %SCHEMA%.TMS155SU3 WHERE SurNam = Su3Nam AND SurSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899URG ( URGNUM,URGVWT,URGANT,URGRND,URGTXT,URGAL1,URGAN1,URGAL2,URGAN2,URGAL3,URGAN3,URGBZ1,URGAB1,URGJU1,URGBZ2,URGAB2,URGJU2,URGBZ3,URGAB3,URGJU3,URGBG1,URGSU1,URGBG2,URGSU2,URGBG3,URGSU3,URGJMO,URGVMO,URGMAT,URGAST,URGBST,URGVJB,URGBJ1,URGBJ2,URGBJ3,URGAJ1,URGAJ2,URGAJ3,URGJST ) AS (SELECT UrlNUM, UrlVWT, UrlANT, UrlRND, UrlTXT, UrlAL1, UrlAN1, UrlAL2, UrlAN2, UrlAL3, UrlAN3, UrlBZ1, UrlAB1, UrlJU1, UrlBZ2, UrlAB2, UrlJU2, UrlBZ3, UrlAB3, UrlJU3, UrlBG1, UrlSU1, UrlBG2, UrlSU2, UrlBG3, UrlSU3, UrlJMO, UrlVMO, UrlMAT, UrlAST, UrlBST, UrlVJB, UrlBJ1, UrlBJ2, UrlBJ3, UrlAJ1, UrlAJ2, UrlAJ3, UrlJST FROM %SCHEMA%.TMS155URL WHERE UrlSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899VR1 ( VR1NUM,VR1NUMTXT,VR1TTP,VR1TTPTXT,VR1FC1,VR1FC1TXT,VR1FC2,VR1FC2TXT,VR1FHL,VR1FHLTXT,VR1ZGS,VR1ZGSTXT,VR1MZP,VR1MZPTXT ) AS (SELECT Vr1Num, (SELECT VraTxt FROM %SCHEMA%.TMS151VRA WHERE Vr1Num = VraNum), Vr1Ttp, (SELECT Tt2Txt FROM %SCHEMA%.TMS132TT2 WHERE Vr1Ttp = Tt2Num), Vr1Fc1, (SELECT FcdTxt FROM %SCHEMA%.TMS131FCD WHERE Vr1Fc1 = FcdNum), Vr1Fc2, (SELECT FcdTxt FROM %SCHEMA%.TMS131FCD WHERE Vr1Fc2 = FcdNum), Vr1Fhl, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE Vr1Fhl = ZktNum), Vr1Zgs, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE Vr1Zgs = ZktNum), Vr1Mzp, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE Vr1Mzp = ZktNum) FROM %SCHEMA%.TMS151Vr1) ;
CREATE VIEW %SCHEMA%.TMS899ZS1 ( ZS1NUM,ZS1NUMTXT,ZS1VON,ZS1BIS,ZS1FCD,ZS1FCDTXT,ZS1LST,ZS1LSTTXT,ZS1ZPZ,ZS1ZKT,ZS1ZKTTXT,ZS1ZK2,ZS1ZK2TXT,ZS1ZK3,ZS1ZK3TXT,ZS1ZK4,ZS1ZK4TXT,ZS1MKT,ZS1MKTTXT,ZS1MZK,ZS1MZKTXT,ZS1MZP ) AS (SELECT Zs1Num, (SELECT ZsbTxt FROM %SCHEMA%.TMS152ZSB WHERE Zs1Num = ZsbNum), Zs1Von, Zs1Bis, Zs1Fcd, (SELECT FcdTxt FROM %SCHEMA%.TMS131FCD WHERE Zs1Fcd = FcdNum), Zs1Lst, (SELECT LstTxt FROM %SCHEMA%.O1006LEISTUNG WHERE Zs1Lst = LstNum), Zs1Zpz, Zs1Zkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Zkt = ZktNum), Zs1Zk2, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Zk2 = ZktNum), Zs1Zk3, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Zk3 = ZktNum), Zs1Zk4, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Zk4 = ZktNum), Zs1Mkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Mkt = ZktNum), Zs1Mzk, (SELECT ZktTxt FROM %SCHEMA%.TMS130Zkt WHERE Zs1Mzk = ZktNum), Zs1Mzp FROM %SCHEMA%.TMS152ZS1) ;
CREATE VIEW %SCHEMA%.TMS899ZBK ( ZBKNUM,ZBKTXT,ZBKAWS,ZBKNAM,ZBKPNR,ZBKTRM ) AS (SELECT ZbkNum, ZbkTxt, ' ', null, null, ZlsTrm FROM TMSYSTEM.TMS403ZBK, TMSYSTEM.TMS401ZLS, TMSYSTEM.TMS404ZBR WHERE ZbkNum = ZbrZbk AND ZlsZko = ZbrZko AND ZbkArt = 5 AND ZbkSta = 1 UNION SELECT ZbkNum, ZbkTxt, BklAsw, AswNam, BklPnr, ' ' FROM TMSYSTEM.TMS403ZBK, TMSYSTEM.TMS301BKL, TMSYSTEM.TMS301ASW WHERE ZbkNum = BklBkl AND (BklPnr = AswPnr AND AswNam IS NOT NULL) AND BklAsw = AswNum AND ZbkArt = 5 AND zbksta = 1) ;
CREATE VIEW %SCHEMA%.TMS899SZK ( SZKNUM,SZKTXT,SZKTGE,SZKAGT,SZKSNR,SZKPRI,SZKZZ1,SZKZZ1TXT,SZKZZ2,SZKZZ2TXT,SZKZZ3,SZKZZ3TXT,SZKZZ4,SZKZZ4TXT ) AS (SELECT ZKTNUM, ZKTTXT, ZKTTGE, ZKTAGT, ZKTSNR, ZKTPRI, ZKTZZ1, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = ZktZz1), ZKTZZ2, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = ZktZz2), ZKTZZ3, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = ZktZz3), ZKTZZ4, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = ZktZz4) FROM %SCHEMA%.TMS130ZKT) ;
CREATE VIEW %SCHEMA%.WOCHENARBZEIT ( WAZWAZMOD,WAZWAZBEG,WAZWAZEND,WAZWAZTXT,WAZWAZSOL,WAZWAZSTA,WAZWAZDAT,WAZWAZERF,WAZWAZMV1,WAZWAZMB1,WAZWAZGV1,WAZWAZGB1,WAZWAZNV1,WAZWAZNB1,WAZWAZKV1,WAZWAZKB1,WAZWAZPM1,WAZWAZAP1,WAZWAZMV2,WAZWAZMB2,WAZWAZGV2,WAZWAZGB2,WAZWAZNV2,WAZWAZNB2,WAZWAZKV2,WAZWAZKB2,WAZWAZPM2,WAZWAZAP2,WAZWAZMV3,WAZWAZMB3,WAZWAZGV3,WAZWAZGB3,WAZWAZNV3,WAZWAZNB3,WAZWAZKV3,WAZWAZKB3,WAZWAZPM3,WAZWAZAP3 ) AS SELECT WAZMOD, WAZBEG, WAZEND, WAZTXT, WAZSOL, WAZSTA, WAZDAT, WAZERF, WAZMV1, WAZMB1, WAZGV1, WAZGB1, WAZNV1, WAZNB1, WAZKV1, WAZKB1, WAZPM1, WAZAP1, WAZMV2, WAZMB2, WAZGV2, WAZGB2, WAZNV2, WAZNB2, WAZKV2, WAZKB2, WAZPM2, WAZAP2, WAZMV3, WAZMB3, WAZGV3, WAZGB3, WAZNV3, WAZNB3, WAZKV3, WAZKB3, WAZPM3, WAZAP3 FROM %SCHEMA%.TMS156WAZ a WHERE WAZBEG = (select max(WAZBEG) from %SCHEMA%.TMS156WAZ b where a.WAZMOD = b.WAZMOD AND (WAZBEG <= CURRENT_DATE and (wazend >= CURRENT_DATE or wazend is null) OR (select count(WAZMOD) from %SCHEMA%.TMS156WAZ where WAZBEG <= CURRENT_DATE and (WAZEND >= CURRENT_DATE or WAZEND is null) and WAZMOD = b.WAZMOD) = 0) ) ;
CREATE VIEW %SCHEMA%.TMS899AZG ( AZGNUM,AZGTXT,AZGGVO,AZGGBI,AZGTGE,AZGSOL,AZGSTG,AZGTAG,AZGAZT,AZGVRA,AZGAZE,AZGBSD,AZGAZTTXT,AZGVRATXT,AZGBSDTXT ) AS (SELECT Ag1Num, Ag1Txt, TO_CHAR(Ag1Gvo,'yyyymmdd'), TO_CHAR(Ag1Gbi,'yyyymmdd'), Ag1Tge, Ag1Sol, Ag1Stg, WplTag, WplAzt, WplVra, WplAze, WplBsd, (SELECT AztTxt FROM %SCHEMA%.TMS140AZT WHERE WplAzt = AztNum), (SELECT VraTxt FROM %SCHEMA%.TMS151VRA WHERE WplVra = Vranum), (SELECT BsdTxt FROM %SCHEMA%.TMS150BSD WHERE WplBsd = Bsdnum) FROM %SCHEMA%.TMS160AG1, %SCHEMA%.TMS161WPL WHERE AG1NUM = WPLAGR AND AG1GVO = WPLGVO) ;
CREATE VIEW %SCHEMA%.TMS899BSD ( BSDNUM,BSDTXT,BSDZKT,BSDZKTTXT,BSDNRM,BSDNRMTXT,BSDWOA,BSDZFM,BSDSOS,BSDNMV,BSDAMP,BSDAMPTXT,BSDGRV,BSDGRB,BSDGEV,BSDGEB,BSDNPK,BSDNPB,BSDZKN,BSDZKNTXT,BSDTGN,BSDNK1,BSDNK1TXT,BSDNS1,BSDNK2,BSDNK2TXT,BSDNS2,BSDNK3,BSDNK3TXT,BSDNS3 ) AS (SELECT a.BSDNUM, a.BSDTXT, a.BSDZKT, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT b WHERE b.ZktNum = a.BsdZkt) as BSDZKTTXT, a.BSDNRM, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT c WHERE c.ZktNum = a.BsdNrm) as BSDNRMTXT, a.BSDWOA, a.BSDZFM, a.BSDSOS, a.BSDNMV, a.BSDAMP, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT d WHERE d.ZktNum = a.BsdAmp) as BSDAMPTXT, a.BSDGRV, a.BSDGRB, a.BSDGEV, a.BSDGEB, a.BSDNPK, a.BSDNPB, a.BSDZKN, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT e WHERE e.ZktNum = a.BsdZkn) as BSDZKNTXT, a.BSDTGN, a.BSDNK1, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT f WHERE f.ZktNum = a.BsdNk1) as BSDNK1TXT, a.BSDNS1, a.BSDNK2, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT g WHERE g.ZktNum = a.BsdNk2) as BSDNK2TXT, a.BSDNS2, a.BSDNK3, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT h WHERE h.ZktNum = a.BsdNk3) as BSDNK3TXT, a.BSDNS3 FROM %SCHEMA%.TMS150BSD a) ;
CREATE VIEW %SCHEMA%.TMS899BS1 ( BS1NUM,BS1NUMTXT,BS1TTP,BS1TTPTXT,BS1ZSN,BS1ZSNTXT,BS1ZSM,BS1ZSMTXT,BS1ZKM,BS1ZKMTXT,BS1ZKZ,BS1ZKZTXT,BS1PRZ,BS1ZTR,BS1STD,BS1GLT,BS1VHS ) AS (SELECT a.BS1NUM, (SELECT b.BsdTxt FROM %SCHEMA%.TMS150BSD b WHERE a.Bs1Num = b.BsdNum) AS Bs1NumTxt, a.BS1TTP, (SELECT c.Tt2Txt FROM %SCHEMA%.TMS132TT2 c WHERE a.Bs1Ttp = c.Tt2Num) AS Bs1TtpTxt, a.BS1ZSN, (SELECT d.ZsbTxt FROM %SCHEMA%.TMS152ZSB d WHERE a.Bs1Zsn = d.ZsbNum) AS Bs1ZsnTxt, a.BS1ZSM, (SELECT e.ZsbTxt FROM %SCHEMA%.TMS152ZSB e WHERE a.Bs1Zsm = e.ZsbNum) AS Bs1ZsmTxt, a.BS1ZKM, (SELECT f.ZktTxt FROM %SCHEMA%.TMS130ZKT f WHERE a.Bs1Zkm = f.ZktNum) AS Bs1ZkmTxt, a.BS1Zkz, (SELECT g.ZktTxt FROM %SCHEMA%.TMS130ZKT g WHERE a.Bs1Zkz = g.ZktNum) AS Bs1ZkzTxt, a.BS1PRZ, a.BS1ZTR, a.BS1STD, a.BS1GLT, a.BS1VHS FROM %SCHEMA%.TMS150BS1 a) ;
CREATE VIEW %SCHEMA%.TMS899AKR ( AKRNUM,AKRTXT,AKRAZR,AKRTAG,AKRRRM,AKRLUG,AKRLUGTXT,AKRSSL,AKRVWS,AKREBN ) AS (SELECT AkrNum, AkrTxt, AkrAzr, AkrTag, AkrRrm, AkrLug, (SELECT LugTxt FROM %SCHEMA%.TMS171LUG WHERE LugLug = AkrLug) AS AkrLugTxt, AkrSsl, AkrVws, AkrEbn FROM %SCHEMA%.O1001AbrKreis WHERE AkrSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899ABT ( ABTNUM,ABTTXT,ABTLST,ABTLSTTXT ) AS (SELECT a.AbtNum, a.AbtTxt, a.AbtLst, (SELECT b.LstTxt FROM %SCHEMA%.O1006Leistung b WHERE b.LstNum = a.AbtLst) AS AbtLstTxt FROM %SCHEMA%.O1002Abteilung a WHERE AbtSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899AZM ( AZTNUM,AZTTXT,AZVGVO,AZVGBI,AZVMAV,AZVMAB,AZVNOV,AZVNOB,AZVGLV,AZVGLB,AZVKEV,AZVKEB,AZVSOL,AZVHSO,AZVVHS,AZVHTV,AZVSO2,AZVHS2,AZVAZP,AZVPSM,AZVSKZ,AZVGLT,AZVZPZ,AZVSBG,AZVKMA,AZPTXT,PSMTXT ) AS (SELECT AztNum, AztTxt, TO_CHAR(AzvGvo,'yyyymmdd') AS AzvGvo, TO_CHAR(AzvGbi,'yyyymmdd') AS AzvGbi, AzvMav, AzvMab, AzvNov, AzvNob, AzvGlv, AzvGlb, AzvKev, AzvKeb, AzvSol, AzvHso, AzvVhs, AzvHtv, AzvSo2, AzvHs2, AzvAzp, AzvPsm, AzvSkz, AzvGlt, AzvZpz, AzvSbg, AzvKma, (SELECT AzpTxt FROM %SCHEMA%.TMS141AZP WHERE AzpNum = AzvAzp) AS AzpTxt, (SELECT PsmTxt FROM %SCHEMA%.TMS142PSM WHERE PsmNum = AzvPsm) AS PsmTxt FROM %SCHEMA%.TMS140AZT, %SCHEMA%.TMS140AZV WHERE AztNum = AzvNum) ;
CREATE VIEW %SCHEMA%.TMS899JAV ( JAVPNR,JAVPNM,JAVBEG,JAVEND,JAVJAZ,JAVIST,JAVSOL,JAVDIF,JAVDI2 ) AS (SELECT JavPnr, JavPnm, TO_CHAR(JavBeg, 'yyyymmdd') AS JavBeg, TO_CHAR(JavEnd, 'yyyymmdd') AS JavEnd, JavJaz, JavIst, JavSol, JavDif, JavDi2 FROM %SCHEMA%.TMS332JAV WHERE JavSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899AZP ( AZPNUM,AZPAZPTXT,AZPMIN,AZPVOR,AZPNAC,AZPMAX,AZPZGS,AZPTOK,AZPTOG,AZPTKT,AZPTAK,AZPMTK,AZPZMT,AZPZKTTXT,AZPWEG,AZPWKT,AZPVHM,AZPVHT ) AS (SELECT AzpNum, AzpTxt as AzpZktTxt, AzpMin, AzpVor, AzpNac, AzpMax, AzpZgs, AzpTok, AzpTog, AzpTkt, AzpTak, AzpMtk, AzpZmt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = AzpZmt) AS AzpZktTxt, AzpWeg, AzpWkt, AzpVhm, AzpVht FROM %SCHEMA%.TMS141AZP) ;
CREATE VIEW %SCHEMA%.TMS899BAF ( BAFNUM,BAFTXT,BAFTYP,BAFKRT,BAFZWT,BAFSTD,BAFMIN,BAFMAX,BAFZKT,BAFZKTTXT,BAFZPT,BAFPRI,BAFTKT,BAFWEG,BAFFCD,BAFFLG,BAFKSB,BAFMJS,BAFEVM,BAFENM,BAFSTO,BAFABJ,BAFABM,BAFINF,BAFANW,BAFBZA,BAFPSE,BAFSOL,BAFSOA,BAFUNB,BAFTRM,BAFKOR,BAFGPU,BAFAKA,BAFIGN ) AS (SELECT FCDNUM, FCDTXT, FCDTYP, FCDKRT, FCDZWT, FCDSTD, FCDMIN, FCDMAX, FCDZKT, (SELECT DISTINCT ZKTTXT FROM %SCHEMA%.TMS130ZKT b WHERE b.ZKTNUM = a.FCDZKT) AS BAFZKTTXT, FCDZPT, FCDPRI, FCDTKT, FCDWEG, FCDFCD, FCDFLG, FCDKSB, FCDMJS, FCDEVM, FCDENM, FCDSTO, FCDABJ, FCDABM, FCDINF, FCDANW, FCDBZA, FCDPSE, FCDSOL, FCDSOA, FCDUNB, FCDTRM, FCDKOR, FCDGPU, FCDAKA, FCDIGN FROM %SCHEMA%.TMS131FCD a) ;
CREATE VIEW %SCHEMA%.TMS899EAZ ( ERSAGR,AG1TXT,ERSAZT,AZTTXT,ERSTLZ,ERSAZE,AZETXT,ERSVRA,VRATXT,ERSTLA,ERSBSD,BSDTXT ) AS (SELECT a.ERSAGR, (SELECT DISTINCT b.AgrTxt FROM %SCHEMA%.TMS160AGR b WHERE b.AgrNum = a.ERSAGR) AS AGRBezeichnung, a.ERSAZT, (SELECT DISTINCT c.AztTxt FROM %SCHEMA%.TMS140AZT c WHERE c.AztNum = a.ERSAZT) AS AZTBezeichnung, a.ERSTLZ, a.ERSAZE, (SELECT DISTINCT d.AztTxt FROM %SCHEMA%.TMS140AZT d WHERE d.AztNum = a.ERSAZE) AS AZEBezeichnung, a.ERSVRA, (SELECT DISTINCT e.VraTxt FROM %SCHEMA%.TMS151VRA e WHERE e.VraNum = a.ERSVRA) AS VRABezeichnung, a.ERSTLA, a.ERSBSD, (SELECT DISTINCT f.BsdTxt FROM %SCHEMA%.TMS150BSD f WHERE f.BsdNum = a.ERSBSD) AS EAMBezeichnung FROM %SCHEMA%.TMS162ERS a) ;
CREATE VIEW %SCHEMA%.TMS899EZK ( EZKAKR,EZKAKRTXT,EZKZKT,EZKZKTTXT,EZKEZK,EZKEZKTXT,EZKVTP,EZKWRT,EZKAEW,EZKSGR,EZKSKL ) AS (SELECT EzkAkr, (SELECT AkrTxt FROM %SCHEMA%.O1001ABRKREIS b WHERE b.AkrNum = a.EzkAkr) AS EzkAkrTxt, EzkZkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT c WHERE c.ZktNum = a.EzkZkt) AS EzkZktTxt, EzkEzk, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT d WHERE d.ZktNum = a.EzkEzk ) AS EzkEzkTxt, EzkVtp, EzkWrt, EzkAew, EzkSgr, EzkSkl FROM %SCHEMA%.TMS130EZK a) ;
CREATE VIEW %SCHEMA%.TMS899KST ( KSTNUM,KSTTXT,KSTLST,KSTLSTTXT,KSTAPZ ) AS (SELECT KstNum, KstTxt, KstLst, (SELECT LstTxt FROM %SCHEMA%.O1006LEISTUNG b WHERE b.LstNum = a.KstLst) AS KstLstTxt, KstApz FROM %SCHEMA%.O1003KSTSTELLE a WHERE KstSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899KTR ( KTRNUM,KTRTXT,KTRLST,KTRLSTTXT ) AS (SELECT a.KtrNum, a.KtrTxt, a.KtrLst, (SELECT b.LstTxt FROM %SCHEMA%.O1006Leistung b WHERE b.LstNum = a.KtrLst) AS KtrLstTxt FROM %SCHEMA%.O1004KSTTRAEGER a WHERE a.KtrSta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899LUG ( LUGLUG,LUGDTM,LUGUNT,LUGAKR,LUGTXT,LUGANZ,LUGDAT,LUGLGP,LUGMON,LUGFIR,LUGPNR,LUGSTA,LUGERF,LU1ZTK,LU1LGR,LU1GBI,LU1TGW,LU1KTR,LU1TGE,LU1SVK,LU1FKT,LU1MOG,LU1BAU,LU1STZ,LU1LNA,LU1GVO,LU1RRE,LU1KST,LU1KRT,LU1SOL,LU1FLG,LU1STS,LU1ZTR,LU1FKZ,LGPTXT,ZKTTXT ) AS (SELECT LugLug, LugDtm, LugUnt, LugAkr, LugTxt, LugAnz, TO_CHAR(LugDat,'yyyymmddhh24miss'), LugLgp, LugMon, LugFir, LugPnr, LugSta, LugErf, Lu1Ztk, Lu1Lgr, TO_CHAR(Lu1Gbi,'yyyymmdd'), Lu1Tgw, Lu1Ktr, Lu1Tge, Lu1Svk, Lu1Fkt, Lu1Mog, Lu1Bau, Lu1Stz, Lu1Lna, TO_CHAR(Lu1Gvo,'yyyymmdd'), Lu1Rre, Lu1Kst, Lu1Krt, Lu1Sol, Lu1Flg, Lu1Sts, Lu1Ztr, Lu1Fkz, LgpTxt, ZktTxt FROM %SCHEMA%.TMS171LUG, %SCHEMA%.TMS171LU1, %SCHEMA%.TMS130ZKT, %SCHEMA%.TMS170LGP WHERE LugLug = Lu1Num AND Lu1Ztk = ZktNum AND LugLgp = LgpNum) ;
CREATE VIEW %SCHEMA%.TMS899LFZ ( LF1NUM,LF1TXT,LF1ZRV,LF1ZRB,LF1LT1,LF1LT2,LF1FC1,LF1FC2,LF1FZK,LF1FC1TXT,LF1FC2TXT ) AS (SELECT Lf1Num, (SELECT LfzTxt FROM %SCHEMA%.TMS154LFZ WHERE LfzNum = Lf1Num), Lf1Zrv, Lf1Zrb, Lf1Lt1, Lf1Lt2, Lf1Fc1, Lf1Fc2, Lf1Fzk, (SELECT FcdTxt FROM %SCHEMA%.TMS131FCD WHERE FcdNum = Lf1Fc1), (SELECT FcdTxt FROM %SCHEMA%.TMS131FCD WHERE FcdNum = Lf1Fc2) FROM %SCHEMA%.TMS154Lf1 WHERE Lf1Sta <> 9) ;
CREATE VIEW %SCHEMA%.TMS899PSM ( PSMNUM,PSMPSMTXT,PSMANZ,PSEVON,PSEBIS,PSEMIN,PSMZPP,PSMANT,PSMOPS,PSMUNT,PSMANI,PSMZSB,PSMUZI,PSMZUI,PSMGKZ,PSELFD,PSEINT,PSEPSE,PSEZKT,PSMZKTTXT,PSEBPU,PSEAFU ) AS (SELECT PsmNum, PsmTxt, PsmAnz, PseVon, PseBis, PseMin, PsmZpp, PsmAnt, PsmOps, PsmUnt, PsmAni, PsmZsb, PsmUzi, PsmZui, PsmGkz, PseLfd, PseInt, PsePse, PseZkt, (SELECT ZktTxt FROM %SCHEMA%.TMS130ZKT WHERE ZktNum = PseZkt), PseBpu, PseAfu FROM %SCHEMA%.TMS142PSM, %SCHEMA%.TMS143PSE WHERE PsmNum = PseNum) ;
CREATE VIEW %SCHEMA%.TMS899PEO ( PEOPNR,PEONAM,PEOESO,PEOESOTXT,PEOVON,PEOBIS,PEOPRI ) AS (SELECT PeoPnr, PnrNam, PeoEso, EsoTxt, TO_CHAR(PeoVon, 'yyyymmdd'), TO_CHAR(PeoBis, 'yyyymmdd'), PeoPri FROM %SCHEMA%.TMS711PEO, %SCHEMA%.TMS300PNR, %SCHEMA%.TMS705ESO WHERE PnrNum = PeoPnr AND EsoEso = PeoEso) ;
CREATE VIEW %SCHEMA%.TMS899AFG ( AFGART,AFGPNR,AFGVON,AFGBIS,AFGAZV,AFGAZB,AFGKEY,AFGTYP,AFGSTA,AFGERF ) AS (SELECT AfgArt, AfgPnr, TO_CHAR(AfgAdt, 'YYYYMMDD'), TO_CHAR(AfgAdt, 'YYYYMMDD'), AfgAzv, AfgAzb, AfgKey, AfgTyp, AfgSta, AfgErf FROM %SCHEMA%.TMS730AFG a WHERE AFGART = 'A' AND a.AFGADT = (SELECT MAX(AFGADT) FROM %SCHEMA%.TMS730Afg b WHERE b.AfgArt = 'A' AND b.AfgPnr = a.AfgPnr AND b.AfgKey = a.AfgKey AND b.AfgAzv = a.AfgAzv)) ;
