Beschreibung Entladescript

Beschreibung Entladescript COB-Modul

SuperX-Version	3.5
SuperX-Modul	cob Version 1.1

Kurzitel

Beschreibung

Tabelle/Script

Datei

konstanten

wichtige Konstanten aus COB entladen, z.B. HISCOB-Version.

DBMS ACCESS

select 17,left(version,1),'COB-Version' from db_version 
 where his_system='HISCOB-GX' 
 union 
 select 18,'' & $START_COB,'COB-Startjahr' from db_version
where his_system='HISCOB-GX' ;
union 
 select 19,'1','COB_SOSKEY' from sys
where msnr='SOSKEY'
and trim(txt)='H' 
union 
 select 20,'0','COB_SOSKEY' from sys
where msnr='SOSKEY'
and trim(txt)='S';

DBMS INFORMIX

select 17,"" || $VERSION,"COB-Version" from db_version 
 where his_system="HISCOB-GX" 
 union 
 select 18,"" || $START_COB,"COB-Startjahr" from db_version
where his_system="HISCOB-GX" 
union 
 select 19,"1","COB_SOSKEY" from sys
where msnr="SOSKEY"
and trim(txt)="H"
union 
 select 20,"0","COB_SOSKEY" from sys
where msnr="SOSKEY"
and trim(txt)="S";

DBMS POSTGRES

select 17,'' || $VERSION::varchar(200),'COB-Version' from db_version 
 where his_system='HISCOB-GX' 
 union 
 select 18,'' || $START_COB::varchar(200),'COB-Startjahr' from db_version
where his_system='HISCOB-GX' 
union 
 select 19,'1','COB_SOSKEY' from sys
where msnr='SOSKEY'
and trim(txt)='H' 
union 
 select 20,'0','COB_SOSKEY' from sys
where msnr='SOSKEY'
and trim(txt)='S'
;

unl/konstanten.unl

busa

Buchungssätze aus COB entladen, ohne Storno-Buchungen.

DBMS ACCESS Quellsystem-Version 5

SELECT busa.busa_id, busa.busa_jahr, busa.busa_monat, busa.busa_instnr, busa.busa_projnr, busa.busa_fikrkey, busa.busa_bukz, busa.busa_vbunr, busa.busa_kapitel, busa.busa_titel, round([busa_betrag],5) AS Ausdr1,null as busa_verwzweck1, null as busa_verwzweck2, busa.busa_quellsys, busa.busa_quelldatnr, busa.busa_quellidnr, format([busa_buchdat],'dd.mm.yyyy'), busa.busa_vbutab,''
	FROM busa
	WHERE (((busa.busa_jahr)>=$START_COB)) And [busa_bukz]<>'IES' And [busa_bukz]<>'PES' and [busa_bukz]<>'PE';

DBMS ACCESS Quellsystem-Version 6,7,8,9,10,11,12

SELECT busa.busa_id, busa.busa_jahr, busa.busa_monat, busa.busa_instnr, busa.busa_projnr, busa.busa_fikrkey, busa.busa_bukz, busa.busa_vbunr, busa.busa_kapitel, busa.busa_titel, round([busa_betrag],5) AS Ausdr1,null as busa_verwzweck1, null as busa_verwzweck2, busa.busa_quellsys, busa.busa_quelldatnr, busa.busa_quellidnr, format([busa_buchdat],'dd.mm.yyyy'), busa.busa_vbutab,''
FROM busa
WHERE (((busa.busa_jahr)>=$START_COB)) And [busa_bukz]<>'IES' And [busa_bukz]<>'PES' and [busa_bukz]<>'PE';

DBMS INFORMIX Quellsystem-Version 5

select
		busa_id ,
		busa_jahr,
		busa_monat,
		busa_instnr,
		busa_projnr,
		busa_fikrkey ,
		busa_bukz ,
		busa_vbunr,
		busa_kapitel,
		busa_titel,
		round(busa_betrag,5),
		'', -- busa_verwzweck1,
		'', -- busa_verwzweck2,
		busa_quellsys,
		busa_quelldatnr,
		busa_quellidnr,
		day(busa_buchdat) || '.' || month(busa_buchdat) || '.' || year(busa_buchdat) ,
		busa_vbutab,
		' '
		from busa
		where busa_jahr >= $START_COB
		and busa_bukz != "IES" And busa_bukz != "PES" and busa_bukz!="PE";

DBMS INFORMIX Quellsystem-Version 6,7,8,9,10,11,12

select
    busa_id ,
    busa_jahr,
    busa_monat,
    busa_instnr,
    busa_projnr,
    busa_fikrkey ,
    busa_bukz ,
    busa_vbunr,
    busa_kapitel,
    busa_titel,
    round(busa_betrag,5),
      '', --  busa_verwzweck1,
      '', --  busa_verwzweck2,
    busa_quellsys,
    busa_quelldatnr,
    busa_quellidnr,
    day(busa_buchdat) || '.' || month(busa_buchdat) || '.' || year(busa_buchdat),
    busa_vbutab,
    busa_ggnr
    from busa
where busa_jahr >= $START_COB
--and busa_monat=1
and busa_bukz != "IES" And busa_bukz != "PES" and busa_bukz!="PE";

DBMS POSTGRES Quellsystem-Version 6,7,8,9,10,11,12

select
    busa_id ,
    busa_jahr,
    busa_monat,
    busa_instnr,
    busa_projnr,
    busa_fikrkey ,
    busa_bukz ,
    busa_vbunr,
    busa_kapitel,
    busa_titel,
    round(busa_betrag::numeric,5),
        '', -- busa_verwzweck1,
        '', -- busa_verwzweck2,
    busa_quellsys,
    busa_quelldatnr,
    busa_quellidnr,
    extract(day from busa_buchdat)::varchar(2) || '.' || extract(month from busa_buchdat)::varchar(2) || '.' || extract(year from busa_buchdat)::varchar(4)  ,
    busa_vbutab,
    busa_ggnr
    from busa
where busa_jahr >= $START_COB
--and busa_monat=1
and busa_bukz != 'IES' And busa_bukz != 'PES' and busa_bukz!='PE';

unl/cob_busa.unl

vtbu

Verteilbuchungen aus HISCOB

Quellsystem-Version 5

select
    vtbu_id,
    vtbu_jahr,
    vtbu_monat,
    vtbu_varnr,
    vtbu_quell_instnr,
    vtbu_quell_projnr,
    vtbu_quell_stug,
    vtbu_ziel_instnr,
    vtbu_ziel_projnr,
    vtbu_ziel_stug,
    vtbu_ziel_fikrkey,
    vtbu_betrag,
    vtbu_umksid,
    vtbu_zfikr,
    ''
from vtbu
where vtbu_jahr >=
$START_COB;

Quellsystem-Version 6,7,8,9,10,11,12

select
    vtbu_id,
    vtbu_jahr,
    vtbu_monat,
    vtbu_varnr,
    vtbu_quell_instnr,
    vtbu_quell_projnr,
    vtbu_quell_stug,
    vtbu_ziel_instnr,
    vtbu_ziel_projnr,
    vtbu_ziel_stug,
    vtbu_ziel_fikrkey,
    vtbu_betrag,
    vtbu_umksid,
    vtbu_zfikr,
    klr_geldgeber
from vtbu
where vtbu_jahr >= 
$START_COB
--and vtbu_monat=1
;

unl/cob_vtbu.unl

umks

Verzeichnis der Verrechnungssätze/Festpreise.

select
    umks_id ,
    umks_kpumkey ,
    umks_jahr,
    umks_monat,
    umks_primko ,
    umks_verrsatz,
    umks_festpr ,
    umks_art,
    umks_kpumart,
    umks_varnr,
    umks_fikrkey,
    umks_instnr,
    umks_projnr,
    umks_stugkey,
    umks_kostenanteil,
       '', -- umks_vert_typ, --unklare Nutzung in cob 8 entfernt
    umks_vert_org,
    umks_vert_instnr,
    umks_kpumgruppe,
    umks_zfikr
from umks 
where umks_jahr >= $START_COB
--and umks_monat =1
;

unl/cob_umks.unl

kpum

Verzeichnis der Leistungsarten

Quellsystem-Version 5
select kpum_id, kpum_key, kpum_kbez, kpum_dbez, kpum_lbez, kpum_einhkbez, kpum_art, kpum_gruppe, kpum_type from kpum;

Quellsystem-Version 6,7,8,9,10,11,12
select kpum_id, kpum_key, kpum_kbez, kpum_dbez, kpum_lbez, kpum_einhkbez, '', '', kpum_type from kpum;

unl/cob_kpum.unl

inst

Verzeichnis der Kostenstellen aus COB

select
inst_nr,
uebinst_nr,
dname,
lname1,
key_von,
key_bis,
orgstruktur
from inst
where  (cobrel='1'
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'=''
union
select
inst_nr,
uebinst_nr,
dname,
lname1,
   date('01.01.1900'),
     date('01.01.3000'),
orgstruktur
from inst
where  (cobrel='1'
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'='0'
;

unl/cob_inst.unl

proj_to_inst

Zuordnung Kostenträger zu Kostenstellen


select projnr, inst_nr, haupt_kst_kz from proj_to_inst;

unl/cob_proj_to_inst.unl

fikr

Der Kostenartenplan (Nur Kosten und Erlöse)

Quellsystem-Version 6

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 7

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 8

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 9

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 10

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    '', --syf
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 11

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    syf,
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    syf,
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

Quellsystem-Version 12

select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    syf,
    key_von,
    key_bis
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'=''
union
select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    syf,
    date('01.01.1900'),
     date('01.01.3000')
from fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;

unl/cob_fikr.unl

aggregierung

wichtige Dimensionen aus COB entladen, z.B. Zeiträume, Aggregierungsebenen.

DBMS ACCESS

select zeit_nr,zeit_dbez,'ZeitraumCob',
'monat>=' & zeit_monat_von & ' and monat<=' & zeit_monat_bis
from zeit where zeit_jahre_diff=0 and zeit_dbez not like '*semester*' 
union 
select distinct 80,'Ebene 1 (Kostengruppe)','Kostenaggregierung','1'  from fikr
where len(key)>=2 
union select distinct 81,'Ebene 2','Kostenaggregierung','2' from fikr
where len(key)>=3 
union select distinct 82,'Ebene 3','Kostenaggregierung','3' from fikr
where len(key)>=4 
union select distinct 83,'Ebene 4','Kostenaggregierung','4' from fikr
where len(key)>=5 
UNION select distinct 84,'Ebene 5','Kostenaggregierung','5' from fikr
where len(key)>=6
union select distinctrow busa_jahr,busa_jahr,'COB-Jahr','jahr' from busa
union select zeit_nr,zeit_dbez,'CobSemester',
'monat>=' & zeit_monat_von & ' and monat<=' & zeit_monat_bis
from zeit where zeit_dbez like '*semester*' 
ORDER BY 1;

DBMS INFORMIX

select zeit_nr,zeit_dbez,"ZeitraumCob",
"monat>=" || zeit_monat_von || " and monat<=" || zeit_monat_bis
from zeit where zeit_jahre_diff=0 and zeit_dbez not like "%semester%" 
union select unique 80,"Ebene 1 (Kostengruppe)","Kostenaggregierung","1" from fikr
where length(key)>=2 
union select unique 81,"Ebene 2","Kostenaggregierung","2" from fikr
where length(key)>=3 
union select unique 82,"Ebene 3","Kostenaggregierung","3" from fikr
where length(key)>=4 
union select unique 83,"Ebene 4","Kostenaggregierung","4" from fikr
where length(key)>=5 
union select unique 84,"Ebene 5","Kostenaggregierung","5" from fikr
where length(key)>=6
union select unique busa_jahr,"" || busa_jahr,"COB-Jahr","jahr" from busa
where busa_jahr >= $START_COB
union select zeit_nr,zeit_dbez,"CobSemester",
"monat>=" || zeit_monat_von || " and monat<=" || zeit_monat_bis
from zeit where zeit_dbez like "%semester%"
 order by 1 ;

DBMS POSTGRES

select zeit_nr,zeit_dbez,'ZeitraumCob',
'monat>=' || zeit_monat_von::varchar(200) || ' and monat<=' || zeit_monat_bis::varchar(200)
from zeit where zeit_jahre_diff=0 and zeit_dbez not like '%semester%' 
union select distinct 80,'Ebene 1 (Kostengruppe)','Kostenaggregierung','1' from fikr
where length(key)>=2 
union select distinct 81,'Ebene 2','Kostenaggregierung','2' from fikr
where length(key)>=3 
union select distinct 82,'Ebene 3','Kostenaggregierung','3' from fikr
where length(key)>=4 
union select distinct 83,'Ebene 4','Kostenaggregierung','4' from fikr
where length(key)>=5 
union select distinct 84,'Ebene 5','Kostenaggregierung','5' from fikr
where length(key)>=6
union select distinct busa_jahr,'' || busa_jahr::varchar(200),'COB-Jahr','jahr' from busa
where busa_jahr >= $START_COB
union select zeit_nr,zeit_dbez,'CobSemester',
'monat>=' || zeit_monat_von::varchar(200) || ' and monat<=' || zeit_monat_bis::varchar(200)
from zeit where zeit_dbez like '%semester%'
 order by 1 ;

unl/aggregierung.unl

proj

Die Schlüssel für die Projekte bzw. Kostenträger

DBMS INFORMIX
select projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,bewdat1,bewabl from proj where (cobrel='1' or '$COB_REL_PROJ'='0');

DBMS POSTGRES
select projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,bewdat1,bewabl from proj where (cobrel='1' or '$COB_REL_PROJ'='0');

DBMS ACCESS
select projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,format([bewdat1],'dd.mm.yyyy'),format([bewabl],'dd.mm.yyyy') from proj where cobrel='1';

unl/cob_proj.unl

drittmittelherk

Kapitel und Titel der Drittmittel


select id, dbez, lbez , jahr , kapitel , titel_gruppe from drittmittelherk;

unl/cob_drittmittelherk.unl

stug

Zuordnung Studiengänge zu Lehreinheiten

select 
stug_key,
stug_von,
   '', -- stug_fach, --nicht wichtig bzw. nicht benutzt
'', --stug_abschl, --nicht wichtig bzw. nicht benutzt
stug_dbez ,
stug_rstuz,
stug_cnw,
stug_bis ,
key_lehreinh ,
stug_plaetze,
key_extkotr
from stug;

unl/cob_stug.unl

gege

Schlüsseltabelle Geldgeber

select 
ggnr ,
ueberkey,
ggkey  ,
fikey,
ggkurz   ,
ggdruck  ,
ggname1  ,
ggname2,
ggname3  ,
key_von     ,
key_bis       ,
bund_geldgeber ,
klr_geldgeber
from gege
where (cobrel='1'
or '$COB_REL_GEGE'='0') 
and '$COB_VONBIS_GEGE'=''
union
select 
ggnr ,
ueberkey,
ggkey  ,
fikey,
ggkurz   ,
ggdruck  ,
ggname1  ,
ggname2,
ggname3  ,
   date('01.01.1900'),
     date('01.01.3000'),
bund_geldgeber ,
klr_geldgeber
from gege
where (cobrel='1'
or '$COB_REL_GEGE'='0') 
 and '$COB_VONBIS_GEGE'='0'
 ;

unl/cob_geldgeber.unl

su_imp_stud_view

Studierenden-Rohdaten aus der COB-Importtabelle.

	SELECT id,
       stuartdbez,
       stuart,
       mtknr,
       sem,
       stgnr,
       fsemnr,
       fachkey,
       fachkeydbez,
       abslkey,
       abslkeydbez,
       kzfach,
       stufrm,
       stufrmdbez,
       kzfachdbez,
       ffstgnr,
       gruppierung,
       ffgewicht,
       stugkey,
       vza,
       ffges,
       ffidr,
       ff1_4,
       ffidrgfs,
       ff1_4gfs,
       ffidrgf2,
       ff1_4gf2,
       kpfges,
       kpfidr,
       kpf1_4
FROM su_imp_stud_view
where sem is not null

unl/cob_su_imp_stud_view.unl

cob_imp_sva_pbv

Personaldaten aus COB

DBMS ACCESS Quellsystem-Version 5

select    id  ,
	format([durchfuehrung],'dd.mm.yyyy'),
	pbv_dienststelle ,
    poz_institut ,
    poz_institut ,
    poz_anteil_proz ,
    poz_projekt ,
    jahr ,
    monat ,
    pbl_bvl_gruppe ,
    pbl_laufbahn ,
    pbl_adt_bez ,
    pbv_dienstart ,
    vollzeit_aequiv ,
    pbv_befr_bis ,
    pbv_art ,
    pbv_rechtsstell,
    pbv_geldgeber ,
    pbv_kapitel ,
    pbv_titel ,
    pbv_kostenart ,
    pbv_zaehler ,
    kalk_kostenart ,
    pgd_zaehler ,
    id
    from imp_sva_pbv 
    where jahr >= $START_COB
    ;

DBMS ACCESS Quellsystem-Version 6,7,8,9,10,11,12

select    id  ,
	format([durchfuehrung],'dd.mm.yyyy'),
    pbv_dienststelle ,
    poz_institut ,
    poz_institut ,
    poz_anteil_proz ,
    poz_projekt ,
    jahr ,
    monat ,
    pbl_bvl_gruppe ,
    pbl_laufbahn ,
    pbl_adt_bez ,
    pbv_dienstart ,
    vollzeit_aequiv ,
    pbv_befr_bis ,
    pbv_art ,
    pbv_rechtsstell,
    pbv_geldgeber ,
    pbv_kapitel ,
    pbv_titel ,
    pbv_kostenart ,
    pbv_zaehler ,
    kalk_kostenart ,
    pgd_zaehler ,
    imp_verz_id 
    from imp_sva_pbv 
    where jahr >= $START_COB
    ;

DBMS INFORMIX Quellsystem-Version 5

select    id  ,
	day(durchfuehrung) || '.' || month(durchfuehrung) || '.' || year(durchfuehrung) ,
    pbv_dienststelle ,
    poz_institut ,
    poz_institut ,
    poz_anteil_proz ,
    poz_projekt ,
    jahr ,
    monat ,
    pbl_bvl_gruppe ,
    pbl_laufbahn ,
    pbl_adt_bez ,
    pbv_dienstart ,
    vollzeit_aequiv ,
    pbv_befr_bis ,
    pbv_art ,
    pbv_rechtsstell,
    pbv_geldgeber ,
    pbv_kapitel ,
    pbv_titel ,
    pbv_kostenart ,
    pbv_zaehler ,
    kalk_kostenart ,
    pgd_zaehler ,
    id 
    from imp_sva_pbv 
    where jahr >= $START_COB
    --and monat=1
    ;

DBMS INFORMIX Quellsystem-Version 6,7,8,9,10,11,12

select    id  ,
	day(durchfuehrung) || '.' || month(durchfuehrung) || '.' || year(durchfuehrung) ,
    pbv_dienststelle ,
    poz_institut ,
    poz_institut ,
    poz_anteil_proz ,
    poz_projekt ,
    jahr ,
    monat ,
    pbl_bvl_gruppe ,
    pbl_laufbahn ,
    pbl_adt_bez ,
    pbv_dienstart ,
    vollzeit_aequiv ,
    pbv_befr_bis ,
    pbv_art ,
    pbv_rechtsstell,
    pbv_geldgeber ,
    pbv_kapitel ,
    pbv_titel ,
    pbv_kostenart ,
    pbv_zaehler ,
    kalk_kostenart ,
    pgd_zaehler ,
    imp_verz_id 
    from imp_sva_pbv 
    where jahr >= $START_COB
    --and monat=1
    ;

DBMS POSTGRES Quellsystem-Version 6,7,8,9,10,11,12

select    id  ,
	durchfuehrung ,
    pbv_dienststelle ,
    poz_institut ,
    poz_institut ,
    poz_anteil_proz ,
    poz_projekt ,
    jahr ,
    monat ,
    pbl_bvl_gruppe ,
    pbl_laufbahn ,
    pbl_adt_bez ,
    pbv_dienstart ,
    vollzeit_aequiv ,
    pbv_befr_bis ,
    pbv_art ,
    pbv_rechtsstell,
    pbv_geldgeber ,
    pbv_kapitel ,
    pbv_titel ,
    pbv_kostenart ,
    pbv_zaehler ,
    kalk_kostenart ,
    pgd_zaehler ,
    imp_verz_id 
    from imp_sva_pbv 
    where jahr >= $START_COB
    --and monat=1
    ;

unl/cob_imp_sva_pbv.unl

cob_alt_hier

Alternative Hierarchien aus COB


select id,name,type from alt_hier;

unl/cob_alt_hier.unl

cob_alt_keys

Schlüssel für alternative Hierarchien aus COB

DBMS ACCESS Quellsystem-Version 5,6,7
select 'fikr',hierarchie_id,key,parent_key,null from alt_fikr union select 'inst',hierarchie_id,key,parent_key,null from alt_inst union select 'proj',hierarchie_id,key,parent_key,null from alt_proj;

DBMS INFORMIX Quellsystem-Version 5,6,7
select 'fikr',hierarchie_id,key,parent_key,'' from alt_fikr union select 'inst',hierarchie_id,key,parent_key,'' from alt_inst union select 'proj',hierarchie_id,key,parent_key,'' from alt_proj;

DBMS POSTGRES Quellsystem-Version 5,6,7
select 'fikr',hierarchie_id,key,parent_key,null from alt_fikr union select 'inst',hierarchie_id,key,parent_key,null from alt_inst union select 'proj',hierarchie_id,key,parent_key,null from alt_proj;

Quellsystem-Version 8,9,10,11,12

select 'fikr',hierarchie_id,key,parent_key,flags from alt_fikr
	union 
	select 'inst',hierarchie_id,key,parent_key,flags from alt_inst
	union
	select 'proj',hierarchie_id,key,parent_key,flags from alt_proj;

unl/cob_alt_keys.unl

cob_trees

Trees aus COB

Quellsystem-Version 5,6,7,8,9,10,11,12
SELECT tree_id, name, start_id, tree_typ, alt_hier_key FROM trees where name is not null;

unl/cob_trees.unl

cob_tree_cfg

Hierarchiespezifikation tree-cfg aus COB

Quellsystem-Version 5,6,7,8,9,10,11,12
select tree_id,node_id from tree_cfg;

unl/cob_tree_cfg.unl

cifx

Hochschulnummern aus COB

SELECT 0, 0 , 36 , bund_hochschule, key_von, key_bis, kurz_hochschule, druck_hochschule, substring(text_hochschule from 1 for 100), land_hochschule, '' 
FROM k_hochschule
where bund_hochschule not like '%A' and bund_hochschule not like '%B'
;

unl/cifx.unl

cob_cifx

Schlüssel aus COB

DBMS ACCESS

select 0,0,109,key_bvlgruppe,ueberkey,sort_key,key_von, key_bis, kurz_bvlgruppe, druck_bvlgruppe, text_bvlgruppe,land_bvlgruppe,'' from k_bvlgruppe
union select 0,0,30, key_fach,'',1,
       key_von,
       key_bis,
       left(kurz_fach,10),
       druck_fach,
       text_fach,
       '',''
FROM k_fach
union select 0,0,700, nadin_key,'',1,'1.1.1900','31.12.3000',nadin_key,
       nadin_dbez,
       nadin_lbez,nadin_lbez,''
FROM k_nadin
union select 0,0,701, kfa_key,'',1,'1.1.1900','31.12.3000',
       kfa_key,
       kfa_dbez,
       kfa_lbez,
 kfa_lbez,'' 
FROM k_kfa
union select 0,0,710, kz_fa, '',1,'1.1.1900','31.12.3000',
       ktxt,
       dtxt,
       ltxt,
       hfkz,''
FROM k_kzfa
where aikz='A' or aikz='a'
union select 0,0,12, key_staat,
       ueberkey, key_staat,
       key_von,
       key_bis,
       kurz_land,
       druck_land,
       text_land,
    text_land,
       bund_staat       
FROM k_staat
union select 0,0,711, key_stuart,
       '', 1,
       '1.1.1900','31.12.3000',
       key_stuart,
       druck_stuart,
       druck_stuart,
druck_stuart,
       ''       
FROM k_stuart
union select 0,0,712, key_stufrm,
       '', 1,
       '1.1.1900','31.12.3000',
       key_stufrm,
       druck_stufrm,
       druck_stufrm,
druck_stufrm,
       ''       
FROM k_stufrm;

DBMS INFORMIX

select 0,0,109,key_bvlgruppe,ueberkey,sort_key,key_von, key_bis, kurz_bvlgruppe, druck_bvlgruppe, text_bvlgruppe,land_bvlgruppe,'' from k_bvlgruppe
union select 0,0,30, key_fach,'',1,
       key_von,
       key_bis,
       kurz_fach[1,10],
       druck_fach,
       text_fach,
       '',''
FROM k_fach
union select 0,0,700, nadin_key,'',1,date('1.1.1900'),date('31.12.3000'),nadin_key,
       nadin_dbez,
       nadin_lbez,nadin_lbez,''
FROM k_nadin
union select 0,0,701, kfa_key,'',1,date('1.1.1900'),date('31.12.3000'),
       kfa_key,
       kfa_dbez,
       kfa_lbez,
 kfa_lbez,'' 
FROM k_kfa
union select 0,0,710, kz_fa, '',1,date('1.1.1900'),date('31.12.3000'),
       ktxt,
       dtxt,
       ltxt,
       hfkz,''
FROM k_kzfa
where aikz='A' or aikz='a'
union select 0,0,12, key_staat,
       ueberkey, 1,
       key_von,
       key_bis,
       kurz_land,
       druck_land,
       text_land,
    text_land,
       bund_staat       
FROM k_staat
union select 0,0,711, key_stuart,
       '', 1,
       date('1.1.1900'),date('31.12.3000'),
       key_stuart,
       druck_stuart,
       druck_stuart,
druck_stuart,
       ''       
FROM k_stuart
union select 0,0,712, key_stufrm,
       '', 1,
       date('1.1.1900'),date('31.12.3000'),
       key_stufrm,
       druck_stufrm,
       druck_stufrm,
druck_stufrm,
       ''       
FROM k_stufrm;

DBMS POSTGRES

select 0,0,109,key_bvlgruppe,ueberkey,sort_key,key_von, key_bis, kurz_bvlgruppe, druck_bvlgruppe, text_bvlgruppe,land_bvlgruppe,'' from k_bvlgruppe
union select 0,0,30, key_fach,'',1,
       key_von,
       key_bis,
       substring(kurz_fach,0,11),
       druck_fach,
       text_fach,
       '',''
FROM k_fach
union select 0,0,700, nadin_key,'',1,null::date,null::date,nadin_key,
       nadin_dbez,
       nadin_lbez,nadin_lbez,''
FROM k_nadin
union select 0,0,701, kfa_key,'',1,null::date,null::date,
       kfa_key,
       kfa_dbez,
       kfa_lbez,
 kfa_lbez,'' 
FROM k_kfa
union select 0,0,710, kz_fa, '',1,null::date,null::date,
       ktxt,
       dtxt,
       ltxt,
       hfkz,''
FROM k_kzfa
where aikz='A' or aikz='a'
union select 0,0,12, key_staat,
       ueberkey, 1,
       key_von,
       key_bis,
       kurz_land,
       druck_land,
       text_land,
    text_land,
       bund_staat       
FROM k_staat
union select 0,0,711, key_stuart,
       '', 1,
       null::date,null::date,
       key_stuart,
       druck_stuart,
       druck_stuart,
druck_stuart,
       ''       
FROM k_stuart
union select 0,0,712, key_stufrm,
       '', 1,
       null::date,null::date,
       key_stufrm,
       druck_stufrm,
       druck_stufrm,
druck_stufrm,
       ''       
FROM k_stufrm;

unl/cob_cifx.unl

cob_cif

Verteilschritte Schlüssel aus COB


select 0,0,714,vari_nr,vari_ueberid,vari_nr,'1.1.1900','31.12.3000', '', vari_dbez, vari_dbez,'','' from vari ;

unl/cob_cif.unl

bzgr

Schlüssel Bezugsgroessen aus COB


SELECT bzgr_id, bzgr_jahr, bzgr_kpumkey, -- web ab COB 7.0.2 bzgr_gruppe, bzgr_varnr -- web ab COB 7.0.2 bzgr_type FROM bzgr

unl/cob_bzgr.unl

bzgr_empf

Faktentabelle Bezugsgroessen aus COB

SELECT
       bzgr_empf_id,
       bzgr_bzgr_id,
       bzgr_instnr,
       bzgr_projnr,
       bzgr_stugkey,
       bzgr_menge_jan,
       bzgr_menge_feb,
       bzgr_menge_mar,
       bzgr_menge_apr,
       bzgr_menge_mai,
       bzgr_menge_jun,
       bzgr_menge_jul,
       bzgr_menge_aug,
       bzgr_menge_sep,
       bzgr_menge_okt,
       bzgr_menge_nov,
       bzgr_menge_dez,
       bzgr_menge_kor,
       bzgr_basiswert
FROM bzgr_empf

unl/cob_bzgr_empf.unl

inst_to_ext

externe Schlüssel für Kostenstellen aus COB


SELECT instnr, email, key_ext, dbez_ext, gebnr FROM inst_to_ext

unl/cob_inst_to_ext.unl

cob_extkotr

externe Schlüssel für Kostenträger aus COB


SELECT key_extkotr, '' ,-- parent text_extkotr, kategorie, fins_stugfach FROM k_extkotr

unl/cob_extkotr.unl

cob_imp_flaeche

Flächendaten aus COB

DBMS INFORMIX

SELECT 
       dstnr,
       quelldatnr,
       day(periodendatum) || '.' || month(periodendatum) || '.' || year(periodendatum) ,
       gebaeudeid,
       geschossid,
       raumid,
       flaeche,
       kap_min,
       nutz_min,
       rnanr,
       rninr,
       instnr,
       instnr,
       qualkz,
       nanr,
       kfanr,
       id,
       projnr,
       imp_verz_id
FROM imp_flaeche;

DBMS POSTGRES
SELECT dstnr, quelldatnr, periodendatum , gebaeudeid, geschossid, raumid, flaeche, kap_min, nutz_min, rnanr, rninr, instnr, instnr, qualkz, nanr, kfanr, id, projnr, imp_verz_id FROM imp_flaeche;

DBMS ACCESS

SELECT 
       dstnr,
       quelldatnr,
       format([periodendatum],'dd.mm.yyyy'),
       gebaeudeid,
       geschossid,
       raumid,
       flaeche,
       kap_min,
       nutz_min,
       rnanr,
       rninr,
       instnr,
       instnr,
       qualkz,
       nanr,
       kfanr,
       id,
       projnr,
       imp_verz_id
FROM imp_flaeche;

unl/cob_imp_flaeche.unl

cob_rna2nakfa

Zuordnung Raumnutzungart zur Kostenflächenart in COB


SELECT rna_key, kfa_nr, na_nr, rna_dbez FROM rna_to_nakfa;

unl/cob_rna2nakfa.unl

cob_kontrolle

Kontrollsumme in COB

DBMS ACCESS

	select 'busa_summe' as art,busa_jahr,
    sum(round(busa_betrag,5)) 
    from busa
where busa_jahr >= '$START_COB'
and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
and busa_fikrkey in (select distinct   key 
from fikr
where (kokl='K' or kokl='E')
and cobrel='1')
and busa_instnr in (select distinct inst_nr from inst where cobrel='1')
and (busa_projnr is null or busa_projnr in (select distinct projnr from proj where cobrel='1'))
group by busa_jahr
union select 'fikr_nicht_cobrel' as art,busa_jahr,
    sum(round(busa_betrag,5)) 
    from busa
where busa_jahr >= '$START_COB'
and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
and busa_fikrkey in (select distinct   key 
from fikr
where (kokl='K' or kokl='E')
and cobrel='0')
group by busa_jahr
union select 'inst_nicht_cobrel' as art,busa_jahr,
    sum(round(busa_betrag,5)) 
    from busa
where busa_jahr >= '$START_COB'
and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
and busa_instnr in (select distinct inst_nr from inst where cobrel='0')
group by busa_jahr
union select 'proj_nicht_cobrel' as art,busa_jahr,
    sum(round(busa_betrag,5)) 
    from busa
where busa_jahr >= '$START_COB'
and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
and busa_fikrkey in (select distinct   key 
from fikr
where (kokl='K' or kokl='E'))
and (busa_projnr is not null and busa_projnr in (select distinct projnr from proj where cobrel='0'))
group by busa_jahr;

DBMS INFORMIX

		select 'busa_summe' as art,busa_jahr,
			sum(round(busa_betrag,5)) 
			from busa
			where busa_jahr >= $START_COB
			and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
			and busa_fikrkey in (select distinct   key 
			from fikr
			where (kokl='K' or kokl='E')
			and  (cobrel='1'
or '$COB_REL_FIKR'='0') )
			and busa_instnr in (select distinct inst_nr from inst where  (cobrel='1'
or '$COB_REL_INST'='0') )
			and (busa_projnr is null or busa_projnr in (select distinct projnr from proj where  (cobrel='1'
or '$COB_REL_PROJ'='0') ))
			group by busa_jahr
			union select 'fikr_nicht_cobrel' as art,busa_jahr,
			sum(round(busa_betrag,5)) 
			from busa
			where busa_jahr >= $START_COB
			and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
			and busa_fikrkey in (select distinct   key 
			from fikr
			where (kokl='K' or kokl='E')
			and cobrel='0')
			group by busa_jahr
			union select 'inst_nicht_cobrel' as art,busa_jahr,
			sum(round(busa_betrag,5)) 
			from busa
			where busa_jahr >= $START_COB
			and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
			and busa_instnr in (select distinct inst_nr from inst where cobrel='0')
			group by busa_jahr
			union select 'proj_nicht_cobrel' as art,busa_jahr,
			sum(round(busa_betrag,5)) 
			from busa
			where busa_jahr >= $START_COB
			and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
			and busa_fikrkey in (select distinct   key 
			from fikr
			where (kokl='K' or kokl='E'))
			and (busa_projnr is not null and busa_projnr in (select distinct projnr from proj where cobrel='0'))
			group by busa_jahr;

DBMS POSTGRES

		select 'busa_summe' as art,busa_jahr,
		sum(round(busa_betrag::numeric,5)) 
		from busa
		where busa_jahr >= $START_COB
		and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
		and busa_fikrkey in (select distinct   key 
		from fikr
		where (kokl='K' or kokl='E')
		and  (cobrel='1'
or '$COB_REL_FIKR'='0') )
		and busa_instnr in (select distinct inst_nr from inst where  (cobrel='1'
or '$COB_REL_INST'='0') )
		and (busa_projnr is null or busa_projnr in (select distinct projnr from proj where  (cobrel='1'
or '$COB_REL_PROJ'='0') ))
		group by busa_jahr
		
		union select 'fikr_nicht_cobrel' as art,busa_jahr,
		sum(round(busa_betrag::numeric,5)) 
		from busa
		where busa_jahr >= $START_COB
		and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
		and busa_fikrkey in (select distinct   key 
		from fikr
		where (kokl='K' or kokl='E')
		and cobrel='0')
		group by busa_jahr
		
		union select 'inst_nicht_cobrel' as art,busa_jahr,
		sum(round(busa_betrag::numeric,5)) 
		from busa
		where busa_jahr >= $START_COB
		and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
		and busa_instnr in (select distinct inst_nr from inst where cobrel='0')
		group by busa_jahr
		
		union select 'proj_nicht_cobrel' as art,busa_jahr,
		sum(round(busa_betrag::numeric,5)) 
		from busa
		where busa_jahr >= $START_COB
		and busa_bukz <> 'IES' And busa_bukz <> 'PES' and busa_bukz<>'PE'
		and busa_fikrkey in (select distinct   key 
		from fikr
		where (kokl='K' or kokl='E'))
		and (busa_projnr is not null and busa_projnr in (select distinct projnr from proj where cobrel='0'))
		group by busa_jahr;

unl/cob_kontrolle.unl

stupl

Studienplaetze je Studiengang

Quellsystem-Version 9,10,11,12
select stug_key,jahr,zeit_id,stug_plaetze from stupl;

Quellsystem-Version 5,6,7,8
select 0 from inst where 1=0;

unl/cob_stupl.unl

lvm

Lehrverflechtungsmatrix

Quellsystem-Version 9,10,11,12
SELECT lvm_lekey, lvm_stugkey, lvm_ca, lvm_id, stupl_id FROM lvm ;

Quellsystem-Version 5,6,7,8
SELECT lvm_lekey, lvm_stugkey, lvm_ca, lvm_id, stupl_id FROM lvm;

Quellsystem-Version 12
SELECT lvm_lekey, 'obsolet', lvm_ca, lvm_id, stupl_id FROM lvm ;

unl/cob_lvm.unl

cob_p_select


select id, dbez, alias, typ, flag, script_id FROM p_select ;

unl/cob_p_select.unl

cob_felder2p_sel


select id, p_select_id, spalten_id, feldwert_expr, neuer_wert FROM felder_to_p_select ;

unl/cob_felder2p_sel.unl

cob_ff_to_stug


SELECT stugkey, ff_fachkey, ff_gruppe, gf2 FROM ff_to_stug ;

unl/cob_ff_to_stug.unl

cob_stud

		 SELECT 
       stud_sem,
       stud_stugkey,
       stud_ffges,
       stud_ffidr,
       stud_ff1_4,
       stud_ffgesgfs,
       stud_ffidrgfs,
       stud_ff1_4gfs,
       stud_ffgesgf2,
       stud_ffidrgf2,
       stud_ff1_4gf2,
       stud_kpfges,
       stud_kpfidr,
       stud_kpf1_4
FROM stud;

unl/cob_stud.unl

cob_ff_gruppe


SELECT key, dbez, lbez FROM ff_gruppe;

unl/cob_ff_gruppe.unl