-- alle mtknr aus der Tabelle sos in die Tabelle mtknr_ldsg einfügen

create temp table tmp_mtknr_ldsg(mtknr integer);
--Welche Matrikelnummern sind neu?
insert into tmp_mtknr_ldsg
select S.mtknr from sos S left outer join mtknr_ldsg L
on (L.mtknr=S.mtknr)
where L.mtknr is null
and S.mtknr is not null
	  ;

--Nur die neuen Matrikelnummern hinzufügen
	   insert into mtknr_ldsg(mtknr)
	   select  distinct mtknr from tmp_mtknr_ldsg
	   where '$SOS_UNL_ANON' = 'true';

	   insert into mtknr_ldsg(mtknr,mtknr_ldsg)
	   select  distinct mtknr,mtknr from tmp_mtknr_ldsg
	   where '$SOS_UNL_ANON' != 'true';



drop table tmp_mtknr_ldsg;

create table tmp_xdummy
(
c char(10)
);

create table superx_sos (mtknr integer, mtknr_ldsg integer); 

insert into  tmp_xdummy values ('1');

create table tmp_hskonst (pnr integer);

CREATE TABLE tmp_course_of_study (
    id integer NOT NULL,
    lid integer NOT NULL,
    degree_lid integer,
    degree_id integer,
    subject_lid integer,
    subject_id integer,
    major_field_of_study_lid integer,
    major_field_of_study_id integer,
    course_specialization_lid integer,
    course_specialization_id integer,
    k_subject_indicator_id integer,
    k_examinationversion_id integer,
    k_place_of_studies_id integer,
    k_enrollment_id integer,
    k_type_of_study_id integer,
    k_form_of_studies_id integer,
    part_of_studies integer NOT NULL,
    valid_from date,
    valid_to date,
    k_language_id integer NOT NULL,
    shorttext character varying,
    defaulttext character varying NOT NULL,
    longtext character varying,
    orgunit_lid integer,
    orgunit_id integer,
    regular_number_of_semesters integer,
    uniquename character varying,
    from_term_year integer,
    from_term_type_id integer,
    to_term_year integer,
    to_term_type_id integer,
    admission_to_study_id integer,
    is_admission_to_study integer,
    is_course_of_study_start integer,
    k_course_of_study_type_id integer NOT NULL,
    termcategory integer NOT NULL,
    teachingunit_orgunit_lid integer,
    teachingunit_orgunit_id integer
);

insert into tmp_hskonst
select vpnr from hskonst
union
select hpnr from hskonst
union
select sonstpnr1 from hskonst
union
select sonstpnr2 from hskonst
union
select sonstpnr3 from hskonst;

insert into tmp_hskonst
select vpnr from hskonst;
insert into tmp_hskonst
select hpnr from hskonst;
insert into tmp_hskonst
select sonstpnr1 from hskonst;
insert into tmp_hskonst
select sonstpnr2 from hskonst;
insert into tmp_hskonst
select sonstpnr3 from hskonst;

insert into tmp_hskonst
select vpnr from hskonst;
insert into tmp_hskonst
select hpnr from hskonst;
insert into tmp_hskonst
select sonstpnr1 from hskonst;
insert into tmp_hskonst
select sonstpnr2 from hskonst;
insert into tmp_hskonst
select sonstpnr3 from hskonst;

insert into tmp_course_of_study( id,
       lid,
       degree_lid,
       subject_lid,
       major_field_of_study_lid,
       course_specialization_lid,
       k_subject_indicator_id,
       k_examinationversion_id,
       k_place_of_studies_id,
       k_enrollment_id,
       k_type_of_study_id,
       k_form_of_studies_id,
       part_of_studies,
       valid_from,
       valid_to,
       k_language_id,
       shorttext,
       defaulttext,
       longtext,
       orgunit_lid,
       regular_number_of_semesters,
       uniquename,
       from_term_year,
       from_term_type_id,
       to_term_year,
       to_term_type_id,
       admission_to_study_id,
       is_admission_to_study,
       is_course_of_study_start,
       k_course_of_study_type_id,
       termcategory,
       teachingunit_orgunit_lid)
SELECT id,
       lid,
       degree_lid,
       subject_lid,
       major_field_of_study_lid,
       course_specialization_lid,
       k_subject_indicator_id,
       k_examinationversion_id,
       k_place_of_studies_id,
       k_enrollment_id,
       k_type_of_study_id,
       k_form_of_studies_id,
       part_of_studies,
       valid_from,
       valid_to,
       k_language_id,
       shorttext,
       defaulttext,
       longtext,
       orgunit_lid,
       regular_number_of_semesters,
       uniquename,
       from_term_year,
       from_term_type_id,
       to_term_year,
       to_term_type_id,
       admission_to_study_id,
       is_admission_to_study,
       is_course_of_study_start,
       k_course_of_study_type_id,
       termcategory,
       teachingunit_orgunit_lid
FROM course_of_study;
--ermitteln der historisierten IDs
--degree
update tmp_course_of_study set degree_id=(select D.id from degree D
where D.lid=tmp_course_of_study.degree_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
);
--subject
update tmp_course_of_study set subject_id=(select D.id from subject D
where D.lid=tmp_course_of_study.subject_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
);
--major_field_of_study
update tmp_course_of_study set major_field_of_study_id=(select D.id from major_field_of_study D
where D.lid=tmp_course_of_study.major_field_of_study_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
);
--course_specialization_lid
update tmp_course_of_study set course_specialization_id=(select D.id from course_specialization D
where D.lid=tmp_course_of_study.course_specialization_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
);
--orgunit
update tmp_course_of_study set orgunit_id=(select D.id from orgunit D
where D.lid=tmp_course_of_study.orgunit_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
);
--teachingunit_orgunit_lid

update tmp_course_of_study set teachingunit_orgunit_id=(select D.id from orgunit D
where D.lid=tmp_course_of_study.teachingunit_orgunit_lid
and (D.valid_from <= tmp_course_of_study.valid_from
or D.valid_from is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
and (D.valid_to >= tmp_course_of_study.valid_to
or D.valid_to is null
or ( D.valid_from is null and tmp_course_of_study.valid_from is null)
)
)
where teachingunit_orgunit_lid is not null;

insert into superx_sos(mtknr, mtknr_ldsg)
SELECT  DISTINCT S.mtknr,M.mtknr_ldsg 
from sos S,mtknr_ldsg M
 where S.mtknr=M.mtknr
 and   (
 --Entweder 
 "$SOS_UNL_COMPLETE"='true'
 --oder DATLAE
 or ((datlae >= date("$DATUM")
	 OR ruebeudat >= date("$DATUM")
	 OR exmdat >= date("$DATUM"))
	AND (fehlerkz not in ("F", "V")
	or fehlerkz is null)
	)
	--oder pro
or (S.mtknr in (select mtknr
FROM    pro
WHERE   datum >= date("$DATUM")
-- keine Berücksichtigung von archivierten Daten
and fktkz not in ("ARC", "REA", "NER", "AAE"))
)
--oder pprot
or (S.mtknr in (select 
 DISTINCT mtknr
FROM    pprot
WHERE   datlae >= date("$DATUM")) )

)     
	

 ;

insert into superx_sos(mtknr, mtknr_ldsg)
SELECT  DISTINCT S.mtknr,M.mtknr_ldsg 
from sos S,mtknr_ldsg M
 where S.mtknr=M.mtknr
 and   (
 --Entweder 
 '"$SOS_UNL_COMPLETE"'='"true"'
 --oder DATLAE
 or ((datlae >= date('"$DATUM"')
	 OR ruebeudat >= date('"$DATUM"')
	 OR exmdat >= date('"$DATUM"'))
	AND (fehlerkz not in ('F', 'V')
	or fehlerkz is null)
	)
	--oder pro
or (S.mtknr in (select mtknr
FROM    pro
WHERE   datum >= date('"$DATUM"')
-- keine Berücksichtigung von archivierten Daten
and fktkz not in ('ARC', 'REA', 'NER', 'AAE'))
)
--oder pprot
or (S.mtknr in (select 
 DISTINCT mtknr
FROM    pprot
WHERE   datlae >= date('"$DATUM"')) )

)     
	

 ;

 
insert into superx_sos(mtknr, mtknr_ldsg)
SELECT  mtknr,mtknr
FROM    sos;

select 21,left(dbversnr,1),'SOS-Version' from hskonst 
 union 
 select 22,'' & $start_stud_sem,'Start_SOS_Semester' from hskonst
union 
  select 23,'' & 19901,'Start_POS_Semester' from hskonst
 union
select 24,'' & vpnr,'sos_pruef_vdpnr' from hskonst 
 union 
select 25,'' & hpnr,'sos_pruef_hdpnr' from hskonst 
 union 
select 26,'' & sonstpnr1,'sos_einz_pruef_pnr1' from hskonst 
 union 
select 27,'' & sonstpnr2,'sos_einz_pruef_pnr2' from hskonst 
 union 
select 28,'' & sonstpnr3,'sos_einz_pruef_pnr3' from hskonst 
 union
select 30,'1',beschr from k_s_var where beschr='Semester 5-stellig' and wert1='J'
union
select 31,'0',beschr from k_s_var where beschr='Semester 5-stellig' and wert1 <>'J'
union
select 32,'1','sos_unload_complete' from hskonst
union
select 33,'5','SOS_Quellsystem' from hskonst

;

select 21,"" || trunc(substring(dbversnr from 1 for 2)),"SOS-Version" from hskonst
 union 
 select 22,"" || $start_stud_sem,"Start_SOS_Semester" from hskonst
union 
  select 23,"" || $start_pruef_sem,"Start_POS_Semester" from hskonst
 union
select 24,"" || vpnr,"sos_pruef_vdpnr" from hskonst 
 union 
select 25,"" || hpnr,"sos_pruef_hdpnr" from hskonst 
 union 
select 26,"" || sonstpnr1,"sos_einz_pruef_pnr1" from hskonst 
 union 
select 27,"" || sonstpnr2,"sos_einz_pruef_pnr2" from hskonst 
 union 
select 28,"" || sonstpnr3,"sos_einz_pruef_pnr3" from hskonst 
 union
select 30,"1",beschr from k_s_var where beschr="Semester 5-stellig" and wert1='J'
union
select 31,"0",beschr from k_s_var where beschr="Semester 5-stellig" and wert1 !='J'
union
select 32,"1","sos_unload_complete" from hskonst
where "$SOS_UNL_COMPLETE"="true"
union
select 32,"0","sos_unload_complete" from hskonst
where "$SOS_UNL_COMPLETE"!="true"
union
select 33,"5","SOS_Quellsystem" from hskonst
;

select 21,'' || trunc(to_number(dbversnr,'99')),'SOS-Version' from hskonst
 union 
 select 22,'' || $start_stud_sem,'Start_SOS_Semester' from hskonst
union 
  select 23,'' || $start_pruef_sem,'Start_POS_Semester' from hskonst
 union
select 24,'' || vpnr,'sos_pruef_vdpnr' from hskonst 
 union 
select 25,'' || hpnr,'sos_pruef_hdpnr' from hskonst 
 union 
select 26,'' || sonstpnr1,'sos_einz_pruef_pnr^1' from hskonst 
 union 
select 27,'' || sonstpnr2,'sos_einz_pruef_pnr2' from hskonst 
 union 
select 28,'' || sonstpnr3,'sos_einz_pruef_pnr3' from hskonst 
 union
select 30,'1',beschr from k_s_var where beschr='Semester 5-stellig' and wert1='J'
union
select 31,'0',beschr from k_s_var where beschr='Semester 5-stellig' and wert1 <>'J'
union
select 32,'1','sos_unload_complete' from hskonst
where '$SOS_UNL_COMPLETE'='true'
union
select 32,'0','sos_unload_complete' from hskonst
where '$SOS_UNL_COMPLETE'!='true'
union
select 33,'5','SOS_Quellsystem' from hskonst
;

select 21,'' || 2,'SOS-Version' as version from tmp_xdummy
 union 
 select 22,'' || $start_stud_sem,'Start_SOS_Semester' from tmp_xdummy
union 
  select 23,'' || $start_pruef_sem,'Start_POS_Semester' from tmp_xdummy
 union
select 32,'1','sos_unload_complete' from tmp_xdummy
where '$SOS_UNL_COMPLETE'='true'
union
select 32,'0','sos_unload_complete' from tmp_xdummy
where '$SOS_UNL_COMPLETE'!='true'
union
select 33,'6','SOS_Quellsystem' from tmp_xdummy
;

select superx_sos.mtknr_ldsg,   
format([gebdat],'dd.mm.yyyy'), 
gebort, 
k_geschl.astat as geschl, 
format([exmdat],'dd.mm.yyyy'), 
status, 
semester, 
poplz, 
poort, 
format([immdat],'dd.mm.yyyy'),
 beugrund as gdbu, 
exmgrund as gdex,  
staat,
'' as bland,
hssem, 
urlsem,
hmkfzkz,               
hmkfz,              
semkfzkz,            
semkfz,               
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
format([hzbdatum],'dd.mm.yyyy'),
hzbnote ,
hssemgewicht,
'' as urlsemgewicht,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez,

FROM    superx_sos,sos, k_geschl where
sos.mtknr = superx_sos.mtknr
and  sos.geschl = k_geschl.geschl
 and  (fehlerkz not in ('F', 'V') or fehlerkz is null)
-- and sos.semester >= $start_stud_sem
;

select superx_sos.mtknr_ldsg,   
format([gebdat],'dd.mm.yyyy'),  
gebort, 
k_geschl.astat as geschl, 
format([exmdat],'dd.mm.yyyy'), 
status, 
semester, 
poplz, 
poort, 
format([immdat],'dd.mm.yyyy'),  
 beugrund as gdbu, 
exmgrund as gdex,  
staat,
'' as bland,
hssem, 
urlsem,
hmkfzkz,               
hmkfz,              
semkfzkz,            
semkfz,               
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
format([hzbdatum],'dd.mm.yyyy'),
hzbnote ,
hssemgewicht,
urlsemgewicht,
erhskfz,
erhsart,
erhssembrd,
erstsemhs ,
sperrart1,
sperrart2,
staatkez              
FROM    superx_sos,sos, k_geschl where
sos.mtknr = superx_sos.mtknr
and  sos.geschl = k_geschl.geschl
 and  (fehlerkz not in ('F', 'V') or fehlerkz is null)
-- and sos.semester >= $start_stud_sem
;

SELECT
superx_sos.mtknr_ldsg mtknr,        {matrikel_nr}
gebdat, {d_geburt}
gebort, {geburtsort}
k_geschl.astat geschl,  {geschlecht}
exmdat, {d_exmatr}
status, {kz_rueck_beur_ein}
semester, {sem_rueck_beur_ein}
poplz, {plz}
poort, {ort}
immdat, {d_immatr}
beugrund as gdbu, 
exmgrund as gdex,  
staat,
k_ikfz.bland bland,     {bundesland}
hssem, {hs_sem_zahl}
urlsem,{url_sem_zahl}
hmkfzkz,                {Kennz. Heimatkfz}
hmkfz,                  {Heimatkfz}
semkfzkz,               {Kennz. Semesterkfz}
semkfz,                 {Semesterkfz}
hzbart,                  {Art der Hochschulzugangsberechtigung}
hzbjahr,
hzbkfzkz,
hzbkfz,
hzbdatum,
hzbnote ,
hssemgewicht,
"" as urlsemgewicht ,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez
FROM sos
, superx_sos
, outer k_geschl
, outer k_ikfz

WHERE sos.mtknr = superx_sos.mtknr
AND sos.geschl = k_geschl.geschl
AND sos.hmkfz = k_ikfz.ikfz
--and sos.semester >= $start_stud_sem;

SELECT
superx_sos.mtknr_ldsg mtknr,        {matrikel_nr}
gebdat, {d_geburt}
gebort, {geburtsort}
k_geschl.astat geschl,  {geschlecht}
exmdat, {d_exmatr}
status, {kz_rueck_beur_ein}
semester, {sem_rueck_beur_ein}
poplz, {plz}
poort, {ort}
immdat, {d_immatr}
beugrund as gdbu, 
exmgrund as gdex,  
staat,
k_ikfz.bland bland,     {bundesland}
hssem, {hs_sem_zahl}
urlsem,{url_sem_zahl}
hmkfzkz,                {Kennz. Heimatkfz}
hmkfz,                  {Heimatkfz}
semkfzkz,               {Kennz. Semesterkfz}
semkfz,                 {Semesterkfz}
hzbart,                  {Art der Hochschulzugangsberechtigung}
hzbjahr,
hzbkfzkz,
hzbkfz,
hzbdatum,
hzbnote ,
hssemgewicht,
urlsemgewicht ,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez
FROM sos
, superx_sos
, outer k_geschl
, outer k_ikfz

WHERE sos.mtknr = superx_sos.mtknr
AND sos.geschl = k_geschl.geschl
AND sos.hmkfz = k_ikfz.ikfz 
--and sos.semester >= $start_stud_sem;

select superx_sos.mtknr_ldsg,   
extract(day from gebdat)::varchar(2) || '.' || extract(month from gebdat)::varchar(2) || '.' || extract(year from gebdat)::varchar(4),
gebort, 
k_geschl.astat as geschl, 
extract(day from exmdat)::varchar(2) || '.' || extract(month from exmdat)::varchar(2) || '.' || extract(year from exmdat)::varchar(4),
status, 
semester, 
poplz, 
poort, 
extract(day from immdat)::varchar(2) || '.' || extract(month from immdat)::varchar(2) || '.' || extract(year from immdat)::varchar(4),
 beugrund as gdbu, 
exmgrund as gdex,  
staat,
'' as bland,
hssem, 
urlsem,
hmkfzkz,               
hmkfz,              
semkfzkz,            
semkfz,               
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
extract(day from hzbdatum)::varchar(2) || '.' || extract(month from hzbdatum)::varchar(2) || '.' || extract(year from hzbdatum)::varchar(4),
hzbnote ,
hssemgewicht,
'' as urlsemgewicht,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez               
FROM     superx_sos,sos
left outer join k_geschl on  (sos.geschl = k_geschl.geschl)
where
sos.mtknr = superx_sos.mtknr 
and (fehlerkz not in ('F', 'V') or fehlerkz is null)
--and sos.semester >= $start_stud_sem
;

select U.id,   
P.birthdate,
'', --gebort, 
G.astat as geschl, 
U.disenrollment_date, --exmdat
'', --status
'',--semester, TODO muss auf SuperX-Seite auf max(degree_program_progress) gesetzt werden
'', --poplz, 
'', --poort, 
U.enrollmentdate, --immdat
'', -- beugrund as gdbu, 
U.k_reason_of_finishing_id, --exmgrund as gdex,  
C.id as staat, --staat
'' as bland,
'', --hssem, TODO muss ermittelt werden
'',--urlsem,
U.home_country_lid , --hmkfzkz,      TODO muss auf SuperX-Seite umgesetzt werden         
U.home_district_lid ,--hmkfz,                TODO muss auf SuperX-Seite umgesetzt werden         
U.semester_country_lid,--semkfzkz,              TODO muss auf SuperX-Seite umgesetzt werden         
U.semester_district_lid, --semkfz,   TODO muss auf SuperX-Seite umgesetzt werden         
--wird aus sos_hzb ermittelt:
'', --hzbart,
'', --hzbjahr,
'', --hzbkfzkz,
'', --hzbkfz,
'', --extract(day from hzbdatum)::varchar(2) || '.' || extract(month from hzbdatum)::varchar(2) || '.' || extract(year from hzbdatum)::varchar(4),
'', --hzbnote ,
--TODO:
'', --hssemgewicht,
'' as urlsemgewicht,
'', --erhskfz,
'', --erhsart,
'', --erhssembrd,
'', --erstsemhs
'', --sperrart1,
'', --sperrart2,
'' --staatkez     
FROM   k_gender G,  student U, person P left outer join
country C on (C.lid=P.country_lid
and (
C.valid_from <=P.birthdate
or P.birthdate is null
)
and (
C.valid_to >=P.birthdate
or P.birthdate is null
))
where
P.id=U.person_id
and P.k_gender_id=G.id
;

select superx_sos.mtknr_ldsg,   
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
extract(day from hzbdatum)::varchar(2) || '.' || extract(month from hzbdatum)::varchar(2) || '.' || extract(year from hzbdatum)::varchar(4),
hzbnote          
FROM     superx_sos,sos
where
sos.mtknr = superx_sos.mtknr 
and (fehlerkz not in ('F', 'V') or fehlerkz is null)

;

select superx_sos.mtknr_ldsg,   
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
day(hzbdatum)::varchar(2) || '.' || month(hzbdatum)::varchar(2) || '.' || year(hzbdatum)::varchar(4),
hzbnote          
FROM     superx_sos,sos
where
sos.mtknr = superx_sos.mtknr 
and (fehlerkz not in ('F', 'V') or fehlerkz is null)

;

select S.id,   
H.entrance_qualification_type_id,--.hzbart,
extract(year from E.date_of_work)::smallint,--hzbjahr, ,
I.country_lid,--hzbkfzkz,
I.district_lid,--hzbkzf,
extract(day from E.date_of_work)::varchar(2) || '.' || extract(month from E.date_of_work)::varchar(2) || '.' || extract(year from E.date_of_work)::varchar(4),
round(L.grade,0)::smallint --hzbnote,          
FROM     student S, person P, examplan E,
entrance_qualification H,
examresult L,
examrelation X,
examimport I,
unit U,
k_elementtype Y
where
P.id=S.person_id
and P.id=E.person_id
and H.examplan_id=E.id
and L.examrelation_id=X.id
and I.examplan_id=E.id
and E.unit_id=U.id
and U.k_elementtype_id=Y.id
and Y.hiskey_id=7
and E.default_examrelation_id =X.id
;

SELECT
S.mtknr_ldsg,
abschl,
stg, 
vert,
schwp,                  
kzfa,
fb,
pversion,              
F.semester,
left(F.stgnr,1),
right(F.stgnr,1),
F.status,
F.beugrund as gdbu, 
F.endegrd as gdex, 
F.stgsem,
F.hrst,               
F.klinsem,
F.hssem,
stuart,
stutyp,
stufrm,
stort,
format([F.ruebeudat],'dd.mm.yyyy'),
semgewicht,
stggewicht,
stgsemgewicht,
'' as hssemgewicht,
'' as urlsemgewicht,
kohsem,
lfdnr,
format([F.endedat],'dd.mm.yyyy'), 
format([F.anfdat],'dd.mm.yyyy') ,
lepsem

FROM stg F, superx_sos S
WHERE F.mtknr = S.mtknr
and (lfdnr=0 or lfdnr is null)
AND F.semester >= $start_stud_sem       
;

 
SELECT
S.mtknr_ldsg,
abschl,
stg, 
vert,
schwp,                  
kzfa,
fb,
pversion,              
F.semester,
left(F.stgnr,1),
right(F.stgnr,1),
F.status,
F.beugrund as gdbu, 
F.endegrd as gdex, 
F.stgsem,
F.hrst,               
F.klinsem,
F.hssem,
stuart,
stutyp,
stufrm,
stort,
format([F.ruebeudat],'dd.mm.yyyy'), 
semgewicht,
stggewicht,
stgsemgewicht,
F.hssemgewicht,
F.urlsemgewicht,
kohsem,
lfdnr,
format([F.endedat],'dd.mm.yyyy'), 
format([F.anfdat],'dd.mm.yyyy'),
lepsem 

FROM stg F,  superx_sos S
WHERE F.mtknr = S.mtknr
AND F.semester >= $start_stud_sem  
;

SELECT
superx_sos.mtknr_ldsg, {matrikel_nr}
abschl,{ch35_ang_abschluss}
stg, {ch30_fach}  
vert, {ch39_vertief} 
schwp,                  {schwerpunkt}
kzfa,{kz_fach}
fb,
pversion,               {pversion}
stg.semester, {sem_rueck_beur_ein}
stg.stgnr[1,1], {studiengang_nr}
stg.stgnr[2,2], {fach_nr}
status, {kz_rueck_beur_ein}
beugrund as gdbu, 
endegrd as gdex,  
stg.stgsem,{fach_sem_zahl}
stg.hrst,                {Hörerstatus}
stg.klinsem,{klinische Semester}
stg.hssem,
stuart,
stutyp,
stufrm,
stort,
ruebeudat,
semgewicht,
stggewicht,
stgsemgewicht,
"" as hssemgewicht,
"" as urlsemgewicht,
kohsem,
lfdnr,
endedat ,
anfdat ,
lepsem

FROM stg
, superx_sos
WHERE stg.mtknr = superx_sos.mtknr
and (lfdnr=0 or lfdnr is null)
AND stg.semester >= $start_stud_sem          {ab hier werden Daten ausgewertet}
;

SELECT
superx_sos.mtknr_ldsg, {matrikel_nr}
abschl,{ch35_ang_abschluss}
stg, {ch30_fach}  
vert, {ch39_vertief} 
schwp,                  {schwerpunkt}
kzfa,{kz_fach}
fb,
pversion,               {pversion}
stg.semester, {sem_rueck_beur_ein}
stg.stgnr[1,1], {studiengang_nr}
stg.stgnr[2,2], {fach_nr}
status, {kz_rueck_beur_ein}
beugrund as gdbu, 
endegrd as gdex,  
stg.stgsem,{fach_sem_zahl}
stg.hrst,                {Hörerstatus}
stg.klinsem,{klinische Semester}
stg.hssem,
stuart,
stutyp,
stufrm,
stort,
ruebeudat,
semgewicht,
stggewicht,
stgsemgewicht,
hssemgewicht,
urlsemgewicht,
kohsem,
lfdnr,
endedat ,
anfdat ,
lepsem

FROM stg
, superx_sos
WHERE stg.mtknr = superx_sos.mtknr
and (lfdnr=0 or lfdnr is null)
AND stg.semester >= $start_stud_sem          {ab hier werden Daten ausgewertet}
;

SELECT
S.mtknr_ldsg,
abschl,
stg, 
vert,
schwp,                  
kzfa,
fb,
pversion,              
F.semester,
substr(F.stgnr,1,1),
substr(F.stgnr,length(F.stgnr),1),
F.status,
F.beugrund as gdbu, 
F.endegrd as gdex, 
F.stgsem,
F.hrst,               
F.klinsem,
F.hssem,
stuart,
stutyp,
stufrm,
stort,
extract(day from F.ruebeudat)::varchar(2) || '.' || extract(month from F.ruebeudat)::varchar(2) || '.' || extract(year from F.ruebeudat)::varchar(4),
semgewicht,
stggewicht,
stgsemgewicht,
'' as hssemgewicht,
'' as urlsemgewicht,
kohsem,
lfdnr,
extract(day from F.endedat)::varchar(2) || '.' || extract(month from F.endedat)::varchar(2) || '.' || extract(year from F.endedat)::varchar(4),
extract(day from F.anfdat)::varchar(2) || '.' || extract(month from F.anfdat)::varchar(2) || '.' || extract(year from F.anfdat)::varchar(4),
lepsem

FROM stg F, superx_sos S
WHERE F.mtknr = S.mtknr
and (lfdnr=0 or lfdnr is null)
AND F.semester >= $start_stud_sem       
;

SELECT
D.student_id,
C.degree_id,--abschl
C.subject_id,--stg, 
C.major_field_of_study_id, --vert,
C.course_specialization_id, --schwp,               
C.k_subject_indicator_id,--kzfa,
'',--fb,
C.k_examinationversion_id,--pversion,              
'' || P.term_year || T.termnumber,--F.semester,
DP.studynumber, --substr(F.stgnr,1,1),
DP.subjectnumber,--substr(F.stgnr,length(F.stgnr),1),
DP.k_studystatus_id, --F.status,
'',--F.beugrund as gdbu, 
DP.k_reason_of_leave_id, --F.endegrd as gdex, 
trunc(studysemester),--F.stgsem,
DP.k_studentstatus_id, --F.hrst,               
'',--F.klinsem,
'',--F.hssem,
DP.k_part_time_reason_id,--stuart,
C.k_type_of_study_id,--stutyp,
C.k_form_of_studies_id,--stufrm,
C.k_place_of_studies_id,--stort,
extract(day from DP.reregistration_date)::varchar(2) || '.' || extract(month from DP.reregistration_date)::varchar(2) || '.' || extract(year from DP.reregistration_date)::varchar(4), --ruebeudat
DP.part_time_percentage, --semgewicht,
'',--stggewicht,
'',--stgsemgewicht,
'' as urlsemgewicht,
'' as hssemgewicht,
'',--kohsem,
DP.id, --lfdnr,
extract(day from D.finished)::varchar(2) || '.' || extract(month from D.finished)::varchar(2) || '.' || extract(year from D.finished)::varchar(4) ,--endedat
extract(day from DP.startdate)::varchar(2) || '.' || extract(month from DP.startdate)::varchar(2) || '.' || extract(year from DP.startdate)::varchar(4), --anfdat
''--lepsem

FROM  period P, 
term_type  T,
degree_program_progress DP,
degree_program D,
tmp_course_of_study C
WHERE T.id=P.term_type_id
and D.id=DP.degree_program_id
and DP.course_of_study_lid=C.lid 
and (C.valid_from <= DP.startdate
or C.valid_from <= DP.enddate
or (DP.enddate is null and DP.startdate is null))
and DP.startdate=date(P.startdate)
and DP.enddate=date(P.enddate)
AND to_number('' || P.term_year || T.termnumber,'99999')  >= 19001 --$start_stud_sem
;

SELECT unique
superx_sos.mtknr_ldsg, {matrikel_nr}
stgnr[1,1], {studiengang_nr}
stgnr[2,2], {fach_nr}
abschl, {ch35_ang_abschluss}  
psem, {sem_der_pruefung}
pdatum, {d_abg_pruefung}
stg, {ch30_fach} 
vert, {ch39_vertief}
schwp,                  {schwerpunkt}
kzfa, {kzfa}
pnote, {haupt_pruef_note}
vken1, {lief_amtl_statist}
lab.pnr,   {pnr}                     
pversion, {pversion}
pstatus,{pstatus ist char(2)} 
stgsem                  {fach_sem_zahl} ,
part,
labnr,
pversuch,
ppruef1,
ppruef2,
malus,
bonus,
pordnr,
psws,
'' --relation_id nur bei HIS1
FROM lab
, superx_sos
WHERE lab.mtknr = superx_sos.mtknr
AND (lab.pstatus is null or lab.pstatus != "SO")
$LAB_FILTER
AND lab.prueck = 0         { ab 8.99 }
AND lab.psem >= $start_pruef_sem        {ab hier werden Daten ausgewertet}
and 
((lab.pnr in ($POS_PNR) or "0"="$POS_PNR") 
or lab.pnr in 
 (
select distinct pnr from tmp_hskonst 
) 
)
;

SELECT distinct
S.mtknr_ldsg,
substr(stgnr,1,1),
substr(stgnr,length(stgnr),1),
abschl, 
psem, 
extract(day from pdatum)::varchar(2) || '.' || extract(month from pdatum)::varchar(2) || '.' || extract(year from pdatum)::varchar(4),
stg, 
vert, 
schwp,                 
kzfa, 
pnote, 
vken1, 
lab.pnr,                        
pversion, 
pstatus,
stgsem ,
part,
labnr,
pversuch,
ppruef1,
ppruef2,
malus,
bonus,
pordnr,
psws,
'' --relation_id nur bei HIS1
                 
FROM lab , superx_sos S
WHERE lab.mtknr = S.mtknr
$LAB_FILTER  
AND (lab.pstatus is null or lab.pstatus != 'SO')    
AND lab.prueck = 0  
AND lab.psem >= $start_pruef_sem        
and 
((lab.pnr in ($POS_PNR) or '"0"'='"$POS_PNR"') 
or lab.pnr in 
 (
select distinct pnr from tmp_hskonst 
) 
)
;

    SELECT distinct
S.id,
'',--substr(stgnr,1,1),
'',--substr(stgnr,length(stgnr),1),
C.degree_id,--abschl, 
'' || E.term_year || T.termnumber,--psem, 
extract(day from E.date_of_work)::varchar(2) || '.' || extract(month from E.date_of_work)::varchar(2) || '.' || extract(year from E.date_of_work)::varchar(4),
C.subject_id,--stg, 
C.major_field_of_study_id, --vert, 
C.course_specialization_id,--schwp,                 
C.k_subject_indicator_id,--kzfa, 
substring('' || N.grade from 1 for 1) || coalesce(substring('' || N.grade from 3 for 2),'') ,--pnote,  TODO: examvaluation.grade
'',--vken1, 
X.k_examination_type_id, --lab.pnr,                        
C.k_examinationversion_id,--pversion, 
R.k_workstatus_id,--pstatus,
'',--stgsem ,
'',--part,
'',--labnr,
'',--pversuch,
'',--ppruef1,
'',--ppruef2,
R.malus,
R.bonus,
'',--pordnr,
'',--psws
R.id
                 
FROM student S, 
unit_studies US,
tmp_course_of_study C,
term_type T,
examrelation R,
examresult N,
examplan E,
unit U 
left outer join examination X
on (X.unit_id=U.id)
WHERE S.person_id=E.person_id
 and U.id=US.unit_id
 and C.lid=US.course_of_study_lid
 and E.unit_id=U.id
 and E.cancelation=0 --0=reguläre leistung, 1=zurückgetreten
 and E.id=R.parent_examplan_id
 and N.examrelation_id=R.id
 AND to_number('' || E.term_year || T.termnumber,'99999') >= $start_pruef_sem        
;

SELECT  distinctrow mtknr
FROM    pro
WHERE  fktkz = 'LOE' ;

SELECT  distinct mtknr_ldsg
FROM    mtknr_ldsg, pro left outer join sos on (pro.mtknr=sos.mtknr) 
WHERE  pro.mtknr=mtknr_ldsg.mtknr
and pro.datum >= date('$DATUM')
and fktkz = 'LOE' 
and sos.mtknr is null;

    SELECT * from tmp_xdummy
where 1=0; 

select
superx_sos.mtknr_ldsg, extart, semester, hskfzkz, hskfz, hsart, left(stgnr,1), 
mid(stgnr,2,2), abschl, 
stg, stgsem
from stgext, superx_sos
WHERE stgext.mtknr = superx_sos.mtknr
and extart in ('AP','ZW','VS');

select
superx_sos.mtknr_ldsg, extart, semester, hskfzkz, hskfz, hsart, stgnr[1,1], 
stgnr[2,2], abschl, 
stg, stgsem
from stgext, superx_sos
WHERE stgext.mtknr = superx_sos.mtknr
and extart in ("AP","ZW","VS");

select
superx_sos.mtknr_ldsg, extart, semester, hskfzkz, hskfz, hsart, substr(stgnr,1,1), 
substr(stgnr,2,1), abschl, 
stg, stgsem
from stgext, superx_sos
WHERE stgext.mtknr = superx_sos.mtknr
and extart in ('AP','ZW','VS');

    SELECT * from tmp_xdummy
where 1=0; 

select stg.semester,"" || round(count(*),0) from sos, stg
where sos.mtknr = stg.mtknr
and stg.stgnr = '11'
AND stg.semester >= $start_stud_sem
group by stg.semester
order by stg.semester;

select stg.semester,count(*) from sos, stg
where sos.mtknr = stg.mtknr
and stg.stgnr = '11'
group by stg.semester
order by stg.semester;

select stg.semester,'' || round(count(*),0) from sos, stg
where sos.mtknr = stg.mtknr
and stg.stgnr = '11'
AND stg.semester >= $start_stud_sem
group by stg.semester
order by stg.semester;

select '' || P.term_year || T.termnumber,'' || round(count(*),0) 
    FROM  period P, 
term_type  T,
degree_program_progress DP,
degree_program D
WHERE T.id=P.term_type_id
and D.id=DP.degree_program_id
AND to_number('' || P.term_year || T.termnumber,'99999')  >= $start_stud_sem
and DP.startdate=date(P.startdate)
and DP.enddate=date(P.enddate)
group by 1
;

select 'lab',lab.psem,count(*) from lab, sos S
WHERE lab.mtknr = S.mtknr
AND (lab.panerk is null or lab.panerk <> 'J')       
AND lab.prueck = 0         
AND (S.fehlerkz not in ('F', 'V') or S.fehlerkz is null)
group by lab.psem
;

select 'lab',lab.psem,"" || round(count(*),0) from lab, sos S
WHERE lab.mtknr = S.mtknr
AND (lab.pstatus is null or lab.pstatus != "SO")
$LAB_FILTER     
AND lab.prueck = 0         
AND (S.fehlerkz not in ("F", "V") or S.fehlerkz is null)
AND lab.psem >= $start_pruef_sem 
and (lab.pnr in ($POS_PNR) or "0"="$POS_PNR"
or lab.pnr in 
 (select distinct pnr from tmp_hskonst) 
)

group by 1,2
;

select 'lab',lab.psem,count(*) from lab, sos S
WHERE lab.mtknr = S.mtknr
$LAB_FILTER      
AND lab.prueck = 0         
AND (S.fehlerkz not in ('F', 'V') or S.fehlerkz is null)
AND lab.psem >= $start_pruef_sem 
and (lab.pnr in ($POS_PNR) or '"0"'='"$POS_PNR"'
or lab.pnr in 
 (select distinct pnr from tmp_hskonst) 
 )

group by lab.psem
;

    SELECT 'lab','' || E.term_year || T.termnumber,--psem,
    count(*)
FROM student S, 
unit U, 
term_type T,
examplan E 
left outer join examimport I
on (I.examplan_id=E.id)
WHERE S.person_id=E.person_id
  and E.unit_id=U.id
 and E.cancelation=0 --0=reguläre leistung, 1=zurückgetreten
 and I.id is null --keine Anerkannten Prüfungen
AND to_number('' || E.term_year || T.termnumber,'99999') >= $start_pruef_sem --lab.psem
group by 1,2
;

SELECT identnr,
       superx_sos.mtknr_ldsg,
       bewnr,
       anschrkz,
       kfz,
       plz,
       ort,
       anzuord,
       anwt,
      zusaort
FROM anschri, superx_sos
where superx_sos.mtknr=anschri.mtknr;

SELECT identnr,
       superx_sos.mtknr_ldsg,
       bewnr,
       anschrkz,
       kfz,
       plz,
       ort,
       anzuord,
       anwt,
      zusaort
FROM anschri, superx_sos
WHERE anschri.mtknr = superx_sos.mtknr;

SELECT identnr,
       superx_sos.mtknr_ldsg,
       bewnr,
       anschrkz,
       kfz,
       plz,
       ort,
       anzuord,
       anwt,
      zusaort
FROM anschri, superx_sos
WHERE anschri.mtknr = superx_sos.mtknr;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       psem,
       ptermin,
       folgesem,
       pmaxvg,
       pmaxvh,
       vpfri,
       hpfri,
       exmmeld,
       beumeld,
       pmaxfrnbs,
       pmaxfrnbvd,
       pmaxfrbevd,
       pmaxfrnbhd,
       pmaxfrbehd,
       pnotgrenzw,
       kap,
       tit,
       huel,
       freivers,
       psemfr,
       pdatkenn,
       abtitelhm,
       abtitelhw,
       abtitelvm,
       abtitelvw,
       mspgen,
       potext,
       melostg,
       semsetza,
       semsetzn,
       termintext,
       panfang,
       pende,
       rmfrist,
       sivabschl,
       sivstg,
       sivvert,
       sivschwp,
       sivkzfa,
       sivpversion,
       sivverw,
       pspanfang,
       pspende
FROM parstg

SELECT abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       psem,
       ptermin,
       folgesem,
       pmaxvg,
       pmaxvh,
       vpfri,
       hpfri,
       exmmeld,
       beumeld,
       pmaxfrnbs,
       pmaxfrnbvd,
       pmaxfrbevd,
       pmaxfrnbhd,
       pmaxfrbehd,
       pnotgrenzw,
       kap,
       tit,
       huel,
       freivers,
       psemfr,
       pdatkenn,
       abtitelhm,
       abtitelhw,
       abtitelvm,
       abtitelvw,
       mspgen,
       potext,
       melostg,
       semsetza,
       semsetzn,
       termintext,
       format([panfang],'dd.mm.yyyy'),
       format([pende],'dd.mm.yyyy'),
       format([rmfrist],'dd.mm.yyyy'),
       sivabschl,
       sivstg,
       sivvert,
       sivschwp,
       sivkzfa,
       sivpversion,
       sivverw,
       format([pspanfang],'dd.mm.yyyy'),
       format([pspende],'dd.mm.yyyy')
FROM parstg

SELECT abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       psem,
       ptermin,
       folgesem,
       pmaxvg,
       pmaxvh,
       vpfri,
       hpfri,
       exmmeld,
       beumeld,
       pmaxfrnbs,
       pmaxfrnbvd,
       pmaxfrbevd,
       pmaxfrnbhd,
       pmaxfrbehd,
       pnotgrenzw,
       kap,
       tit,
       huel,
       freivers,
       psemfr,
       pdatkenn,
       abtitelhm,
       abtitelhw,
       abtitelvm,
       abtitelvw,
       mspgen,
       potext,
       melostg,
       semsetza,
       semsetzn,
       termintext,
       panfang,
       pende,
       rmfrist,
       sivabschl,
       sivstg,
       sivvert,
       sivschwp,
       sivkzfa,
       sivpversion,
       sivverw,
       pspanfang,
       pspende
FROM parstg;

    SELECT * from tmp_xdummy
where 1=0; 

   SELECT distinctrow hsnr, bland, hsbez FROM hskonst;

SELECT distinct hsnr, bland, hsbez FROM hskonst;
   

SELECT distinct hsnr, bland, hsbez FROM hskonst;
   

    SELECT * from tmp_xdummy
where 1=0; 

   SELECT distinctrow aktsem,aktsem, format([sembg],'dd.mm.yyyy'),format([semende],'dd.mm.yyyy'),format([stistat],'dd.mm.yyyy')
FROM sossys;

SELECT distinct aktsem,aktsem, sembg,semende,stistat
FROM sossys;
   

SELECT distinct aktsem,aktsem, 
   extract(day from sembg)::varchar(2) || '.' || extract(month from sembg)::varchar(2) || '.' || extract(year from sembg)::varchar(4),
   extract(day from semende)::varchar(2) || '.' || extract(month from semende)::varchar(2) || '.' || extract(year from semende)::varchar(4),
   extract(day from stistat)::varchar(2) || '.' || extract(month from stistat)::varchar(2) || '.' || extract(year from stistat)::varchar(4)
FROM sossys;
   

SELECT distinct '' || P.term_year || T.termnumber,'' || P.term_year || T.termnumber, date(P.startdate),date(P.enddate),date(P.startdate)+85 --Mitte des semesters
FROM k_period_usage K,period_usage U,period P,term_type T
where K.id=U.k_period_usage_id
and K.hiskey_id=7 --Semesterzeitraum
and U.period_id=P.id
and T.id=P.term_type_id 
and T.termcategory=2 --Semester
;
   

SELECT 
    12, 
0, 
   '' & astat,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
'',
'',
'' 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz='A'
   --and (sprache ='D' or sprache is null) --gibt es erst ab SOS 8
union
SELECT 
   9010, 
-1, 
   '' & vpnr,'VD','Vorprüfung','Vorprüfung',
'' & vpnr,
'',
'',
'',
''
FROM hskonst
union
SELECT 
   9010, 
-1, 
   '' & hpnr,'HD','Hauptprüfung','Hauptprüfung',
'' & hpnr,
'',
'',
'',
''
FROM hskonst
   
union   
SELECT 
   9003, 
0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
'',
'',
'' 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
-1, 
   '' & semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
'' & semgewicht,
'',
'' ,
'',
''
   FROM k_semgewicht
 
 union
SELECT 
   631, 
-1, 
   '' & pnr,
   ktxt, 
   dtxt, 
   ltxt,
'' &  pnr,
'',
'' ,
'',
''
   FROM k_pnr
   where pnr is not null
   union
SELECT 
   632, 
-1, 
   '' & refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' &  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers
   where refpvers is not null
 ;
   

SELECT 
    12, 
0, 
   '' & astat,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
'',
'',
'' 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz='A'
   and (sprache ='D' or sprache is null)
union
SELECT 
   9010, 
-1, 
   '' & vpnr,'VD','Vorprüfung','Vorprüfung',
'' & vpnr,
'',
'',
'',
''
FROM hskonst
union
SELECT 
   9010, 
-1, 
   '' & hpnr,'HD','Hauptprüfung','Hauptprüfung',
'' & hpnr,
'',
'',
'',
''
FROM hskonst
   
union   
SELECT 
   9003, 
0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
'',
'',
'' 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
-1, 
   '' & semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
'' & semgewicht,
'',
'' ,
'',
''
   FROM k_semgewicht
 
 union
SELECT 
   631, 
-1, 
   '' & pnr,
   ktxt, 
   dtxt, 
   ltxt,
'' &  pnr,
'',
'' ,
'',
''
   FROM k_pnr
   where pnr is not null
   union
SELECT 
   632, 
-1, 
   '' & refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' &  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers
   where refpvers is not null
 ;
   

SELECT 
    12, 
0, 
   "" || astat,
   ktxt, 
   dtxt, 
   ltxt,
"",
"",
"",
"",
"" 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz="A"
   
union
  SELECT 
9010, 
-1, 
   "" || vpnr,"VD","Vorprüfung","Vorprüfung",
"" || vpnr,
"",
"",
"",
""
FROM hskonst
union
SELECT 
   9010, 
-1, 
   "" || hpnr,"HD","Hauptprüfung","Hauptprüfung",
"" || hpnr,
"",
"",
"",
""
FROM hskonst
   
union   
SELECT 
   9003, 
0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
astat,
"",
"",
"",
"" 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
-1, 
   "" || semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
"" || semgewicht,
"",
"" ,
"",
""
   FROM k_semgewicht
 
 union
SELECT 
   631, 
-1, 
   "" || pnr,
   ktxt, 
   dtxt, 
   ltxt,
"" ||  pnr,
"",
"" ,
"",
""
   FROM k_pnr
   where pnr is not null
  union
SELECT 
   632, 
-1, 
   '' || refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' ||  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers 
   where refpvers is not null;
   

SELECT 
    12, 
0, 
   "" || astat,
   ktxt, 
   dtxt, 
   ltxt,
"",
"",
"",
"",
"" 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz="A"
   and (sprache ="D" or sprache is null)
union
  SELECT 
9010, 
-1, 
   "" || vpnr,"VD","Vorprüfung","Vorprüfung",
"" || vpnr,
"",
"",
"",
""
FROM hskonst
union
SELECT 
   9010, 
-1, 
   "" || hpnr,"HD","Hauptprüfung","Hauptprüfung",
"" || hpnr,
"",
"",
"",
""
FROM hskonst
   
union   
SELECT 
   9003, 
0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
astat,
"",
"",
"",
"" 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
-1, 
   "" || semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
"" || semgewicht,
"",
"" ,
"",
""
   FROM k_semgewicht
 
 union
SELECT 
   631, 
-1, 
   "" || pnr,
   ktxt, 
   dtxt, 
   ltxt,
"" ||  pnr,
"",
"" ,
"",
""
   FROM k_pnr
   where pnr is not null
   union
SELECT 
   632, 
-1, 
   '' || refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' ||  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers
   where refpvers is not null;
   

SELECT 
    12, 
0, 
   '' || astat,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
'',
'',
'' 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz='A'
   
union
SELECT 
   9010, 
-1, 
   '' || vpnr,'VD','Vorprüfung','Vorprüfung',
'' || vpnr,
'',
'',
'',
''
FROM hskonst
union
SELECT 
   9010, 
   -1, 
   '' || hpnr,'HD','Hauptprüfung','Hauptprüfung',
   '' || hpnr,
   '',
   '',
   '',
   ''
FROM hskonst
   
union   
SELECT 
   9003, 
   0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
   astat,
   '',
   '',
   '',
   '' 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
   -1, 
   '' || semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
   '' || semgewicht,
   '',
   '' ,
   '',
   ''
   FROM k_semgewicht
 
 union
SELECT 
   631, 
   -1, 
   '' || pnr,
   ktxt, 
   dtxt, 
   ltxt,
   '' ||  pnr,
   '',
   '' ,
   '',
   ''
   FROM k_pnr
   where pnr is not null
  union
SELECT 
   632, 
-1, 
   '' || refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' ||  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers 
   where refpvers is not null;
   

SELECT 
    12, 
0, 
   '' || astat,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
'',
'',
'' 
   FROM k_akfz 
   WHERE akfz is not null
   and aikz='A'
   and (sprache ='D' or sprache is null)
union
SELECT 
   9010, 
-1, 
   '' || vpnr,'VD','Vorprüfung','Vorprüfung',
'' || vpnr,
'',
'',
'',
''
FROM hskonst
union
SELECT 
   9010, 
   -1, 
   '' || hpnr,'HD','Hauptprüfung','Hauptprüfung',
   '' || hpnr,
   '',
   '',
   '',
   ''
FROM hskonst
   
union   
SELECT 
   9003, 
   0, 
   astat,
   ktxt, 
   dtxt, 
   ltxt,
   astat,
   '',
   '',
   '',
   '' 
   FROM k_geschl 
   WHERE astat is not null
 union
SELECT 
   617, 
   -1, 
   '' || semgewicht,
   ktxt, 
   dtxt, 
   ltxt,
   '' || semgewicht,
   '',
   '' ,
   '',
   ''
   FROM k_semgewicht
 
 union
SELECT 
   631, 
   -1, 
   '' || pnr,
   ktxt, 
   dtxt, 
   ltxt,
   '' ||  pnr,
   '',
   '' ,
   '',
   ''
   FROM k_pnr
   where pnr is not null
  union
SELECT 
   632, 
-1, 
   '' || refpvers,
   ktxt, 
   dtxt, 
   ltxt,
'' ||  pvers,
'',
'' ,
'',
sprache
   FROM k_pvers 
   where refpvers is not null;
   

SELECT 
   12, 
0, 
   substring('' || astat from 1 for 10),--astat,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring('' || astat from 1 for 10), --astat
'',--parent
'',--sortc1
'',--bund_apnr
substring('' || k_language_id from 1 for 3) --sprache,
   FROM country --k_akfz
   --cif-Schlüssel derzeit noch nicht historisiert:
   where (current_date >= valid_from
   or valid_from is null)
   and (current_date <= valid_to
   or valid_to is null)
   
union
SELECT 
   9010, 
-1, 
   '' || id,'VD','Vorprüfung',
   substring(longtext from 1 for 150),
'' , --astat
'', --parent
'', --sortc1
'', --bund_apnr
'' --sprache
FROM k_examination_type
where defaulttext like '%Vordipl%' --gibt leider noch keien hiskey_id
union
SELECT 
   9010, 
   -1, 
   '' || id,'HD','Hauptprüfung',
      substring(longtext from 1 for 150),
 '' , --astat
'', --parent
'', --sortc1
'', --bund_apnr
   '' --sprache
FROM k_examination_type
where hiskey_id=2
   
union   
SELECT 
   9003, 
   0, 
   substring('' || astat from 1 for 10),--astat,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
  substring('' || astat from 1 for 10),--astat,
    '',
   '',
   '',
   '' 
   FROM k_gender 
   WHERE astat is not null
  union
SELECT 
   631, 
   -1, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
   '' , --astat
   '',
   '' || sortorder ,--sortc1
   '',
   substring('' || k_language_id from 1 for 3)--sprache
   FROM k_examination_type
  union
SELECT 
   632, 
-1, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
'' , --astat
'',
'' ,
'',
substring('' || k_language_id from 1 for 3)--sprache
   FROM  k_examinationversion;
   

 
    SELECT 
    --zuerst gemeinsame Schluessel von SOS, POS und ZUL
 --die gleichen selects muessen auch in der zul_unload.xml sein!!!

   35, 
-1, 
   refabint,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',--parent
refabint,--sortc1 wird später uniquename
'',
sprache,
'', --gültig von
'', --gültig bis
refabint, --sourcesystem_id
'', --hiskey_id
aikz --struktur_c
   FROM k_abint 
   WHERE refabint is not null and refabint != '' and refabint != ''
   and (sprache='D' or sprache is null)
    union
SELECT 
   30, 
-1, 
   refstg,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',--parent
refstg, --sortc1  wird später uniquename
'',--bund_apnr
sprache,
'', --gültig von
'', --gültig bis
refstg , --sourcesystem_id
'', --hiskey_id
fb --struktur_c
   FROM k_stg 
   WHERE refstg is not null
      and (sprache='D' or sprache is null)
       and trim (refstg) !=''
union
SELECT 
   39, 
-1, 
   refvert,
   ktxt, 
   dtxt, 
   ltxt, 
'',
'',
vert,
'',
sprache,
'', --gültig von
'', --gültig bis
refvert , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM k_vert 
        WHERE refvert is not null
      and (sprache='D' or sprache is null)
       and trim (refvert) !=''

   union
SELECT 
   41, 
0, 
   schwp,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
schwp,
'',
sprache ,
'', --gültig von
'', --gültig bis
schwp, --sourcesystem_id
'', --hiskey_id
'' --struktur_c

   FROM k_schwp 
   WHERE schwp is not null
   and  trim(schwp) != ''
   union
   SELECT 
   601, 
-1, 
   hzbart,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
hzbart ,
'',
'',
'', --gültig von
'', --gültig bis
hzbart , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM k_hzbart
   union
   SELECT 
   612, 
-1, 
   stufrm,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
stufrm ,
'',
'',
'', --gültig von
'', --gültig bis
stufrm , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM k_stufrm
   union
   SELECT 
   614, 
-1, 
   kzfa,
   ktxt, 
   dtxt, 
   ltxt,
   his_kzfa,
refkzfa,
kzfa ,
'',
sprache,
'', --gültig von
'', --gültig bis
kzfa , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_kzfa
   where (sprache='D' or sprache is null)
   union
   SELECT 
   613, 
-1, 
   hrst,
   ktxt, 
   dtxt, 
   ltxt,
astat,
his_hrst,
hrst ,
'',
'',
'', --gültig von
'', --gültig bis
hrst, --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_hrst
   union
   SELECT 
   616, 
-1, 
   stuart,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
stuart ,
'',
'',
'', --gültig von
'', --gültig bis
stuart , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_stuart
   union
   SELECT 
   620, 
-1, 
   astfr,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
astfr ,
'',
'',
'', --gültig von
'', --gültig bis
astfr , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_astfr
   where astfr is not null
   and trim(astfr)!=''
   union
   SELECT 
   621, 
-1, 
   astgrp,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
astgrp ,
'',
'',
'', --gültig von
'', --gültig bis
astgrp , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_astgrp
   where astgrp is not null
   and trim(astgrp)!=''
   union
   SELECT 
   618, 
-1, 
   abext,
   ktxt, 
   dtxt, 
   ltxt,
astat,
astat,
abext ,
'',
'',
'', --gültig von
'', --gültig bis
abext , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_abext
   union
   SELECT 
   	622, 
	-1, 
   	refpstatus,
   	ktxt, 
   	dtxt, 
   	ltxt,
	'',
	'',
	refpstatus ,
	'',
	'',
'', --gültig von
'', --gültig bis
refpstatus , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_pstatus
   where aikz='A'
   and (sprache='D' or sprache is null)

   union
   
   SELECT 
   40, 
-1, 
   stutyp,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
stutyp ,
'',
'',
'', --gültig von
'', --gültig bis
stutyp , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_stutyp
union
SELECT 
   11, 
-1, 
   ikfz,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
ikfz,
bland,
'' ,
'', --gültig von
'', --gültig bis
ikfz , --sourcesystem_id
'' , --hiskey_id
aikz --struktur_c
   FROM k_ikfz 
   WHERE astat is not null
   --and aikz ='A'
 union
SELECT 
   12, 
0, 
   akfz,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
akfz,--uniqename
erdteil,
'' ,
'', --gültig von
'', --gültig bis
akfz , --sourcesystem_id
'' , --hiskey_id
egmitgl --struktur_c
   FROM k_akfz 
   WHERE akfz is not null
   and (sprache ='D' or sprache is null)
   and aikz='A'
   union
SELECT 
   12, 
0, 
   akfz,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
akfz,--bei staaten ist astat=uniqename
erdteil,
'' ,
'', --gültig von
'01.01.2009', --gültig bis
akfz , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_akfz 
   WHERE akfz is not null
   and (sprache ='D' or sprache is null)
   and aikz='I'
    union
SELECT 
   8, 
0, 
   bland,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
bland,
'',
'',
'', --gültig von
'', --gültig bis
bland , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_bland 
   WHERE bland is not null
   union
   SELECT 
   9011, 
0, 
   ppruefer,
   kname, 
   nachname  , 
  nachname || ', '|| vorname ,
'',
'',
ppruefer,
'',
'',
'', --gültig von
'', --gültig bis
ppruefer , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_ppruef 
   WHERE ppruefer is not null 
   and '$PRUEFER_NAME'='true'
   union
   SELECT 
   305, 
0, 
   sperre,
   ktxt, 
   dtxt  , 
  ltxt ,
sos_sperre1::char(10),
sos_sperre2::char(10),
sperre,
'',
'',
'', --gültig von
'', --gültig bis
sperre , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_sperre
   WHERE sperre is not null 
   and aikz='A'
   union
SELECT 
   9001, 
0, 
   status,
   ktxt, 
   dtxt, 
   ltxt ,
astat,
'',
status,
'',
'',
'', --gültig von
'', --gültig bis
status , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_status 
   WHERE status is not null 
   and aikz='A'
    union
SELECT 
   9013, 
0, 
   modulart,
   ktxt, 
   dtxt  , 
  ltxt ,
'',
'',
modulart,
'',
sprache,
'', --gültig von
'', --gültig bis
modulart , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_modulart
   WHERE modulart is not null 
   and aikz='A'
   union

   SELECT 
   90, 
-1, 
   reffb,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
fb ,
'',
sprache,
'', --gültig von
'', --gültig bis
reffb , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_fb
   where aikz='A'
   and (sprache ='D' or sprache is null)
 union
   SELECT 
   	9012, 
	-1, 
   	stort,
   	ktxt, 
   	dtxt, 
   	ltxt,
	astat,
	'',
	stort ,
	'',
	'',
'', --gültig von
'', --gültig bis
stort , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_stort
    where aikz='A'
    
   union
   SELECT 
   619, 
-1, 
   lehreinh,
   ktxt, 
   dtxt, 
   ltxt,
'',
'',
lehreinh ,
'',
'',
'', --gültig von
'', --gültig bis
lehreinh , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_le
   where lehreinh is not null
   and trim(lehreinh)!=''
   union
   SELECT 
   27, 
-1, 
   gdbu,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
gdbu ,
'',
'',
'', --gültig von
'', --gültig bis
gdbu , --sourcesystem_id
his_gdbu , --hiskey_id
'' --struktur_c
   FROM k_gdbu
   where gdbu is not null
   --and aikz='A'
   and trim(gdbu)!='' 
   union
   SELECT 
   62, 
-1, 
   gdex,
   ktxt, 
   dtxt, 
   ltxt,
astat,
'',
gdex ,
'',
'',
'', --gültig von
'', --gültig bis
gdex , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
   FROM k_gdex
   where gdex is not null
   --and aikz='A'
   and trim(gdex)!=''
   ;
   

   
   SELECT 
   35, 
-1, 
   ('' || id)::varchar(255) as id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
'',--astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM degree  
    union
SELECT 30, 
-1, 
   '' || S.id, 
   substring(S.shorttext from 1 for 10), 
   substring(S.defaulttext from 1 for 100), 
   substring(S.longtext from 1 for 150), 
S.astat_land as astat,
'',--parent 
substring(S.uniquename from 1 for 10), --sortc1
S.astat_bund ,--bund_apnr
substring('' || S.k_language_id from 1 for 3),--sprache,
S.valid_from, --gueltig von
S.valid_to, --gueltig bis
S.id , --sourcesystem_id
'', --hiskey_id
min('' || C.orgunit_id) --struktur_c ist FB, keine Duplikate erlaubt 
   FROM subject  S, tmp_course_of_study C 
   
where S.id=C.subject_id
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
   
union
SELECT 
--Vertiefung
   39, 
-1, 
   '' || id, 
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
'' as astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM major_field_of_study 
   
union
--HZB-Art
   SELECT 
   601, 
-1, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring('' || astat from 1 for 10),--astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM entrance_qualification_type
    union
    SELECT 
    612, 
 -1, 
   '' || id, --stufrm
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring('' || astat from 1 for 10),--astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
'' --struktur_c
    FROM k_form_of_studies
   union
   --kzfa:
   SELECT 
   614, 
-1, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
   '' || hiskey_id, --astat
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
'' --struktur_c
FROM k_subject_indicator
   union
   SELECT 
    613, 
 -1, 
   '' || id, -- hrst,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), -- astat,
'', --parent
substring(uniquename from 1 for 10), --sortc1
 '' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id, -- his_hrst,
'' --struktur_c
 FROM k_studentstatus
    union
       -- stuart:
    SELECT 
    616, 
-1, 
   '' || id, 
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
'',--substring('' || astat from 1 for 10),--astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' ,--|| hiskey_id,
'' --struktur_c
    FROM k_part_time_reason
    union
   -- stutyp,
    SELECT 
    40, 
 -1, 
     '' || id, 
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring('' || astat from 1 for 10),--astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
'' --struktur_c
    FROM k_type_of_study
 union
 SELECT 
    11, 
 -1, 
   '' || id, -- ikfz,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), -- astat,
'', --parent
substring(uniquename from 1 for 10), --sortc1
'',-- bund_apnr, hier bland,
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'' , --hiskey_id
'' || lid --struktur_c
FROM district 
 union
SELECT 
   12, 
0, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring('' || astat from 1 for 10),--astat,
'',--parent
substring('' || uniquename from 1 for 10), --sortc1
substring('' || continent from 1 for 10), -- erdteil
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'', --hiskey_id
'' || lid --struktur_c
   FROM country 
    -- union
-- SELECT 
   -- 8, 
-- 0, 
   -- bland,
   -- ktxt, 
   -- dtxt, 
   -- ltxt,
-- astat,
-- '',
-- '',
-- '',
-- '',
-- '', --gueltig von
-- '', --gueltig bis
-- '' , --sourcesystem_id
--'' --hiskey_id
   -- FROM k_bland 
   -- WHERE bland is not null
   union
SELECT 
    9001, 
 0, 
   '' || id, -- status,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), -- astat,
'', --parent
substring(uniquename from 1 for 10), --sortc1
 '' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id , --sourcesystem_id
'' || hiskey_id, -- his_hrst,
'' --struktur_c
    FROM k_studystatus 
     union
SELECT 
   9013, 
 0, 
   '' || id, -- modulart,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
'' , -- astat,
'', --parent
'', --substring(uniquename from 1 for 10), --sortc1
 '' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id , --sourcesystem_id
'' , --hiskey_id
'' --struktur_c
FROM unit
union
   SELECT 
   90, 
-1, 
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   substring(O.defaulttext from 1 for 100), 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),--astat,
substring('' || O.parent_lid from 1 for 3),--parent
substring(O.uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || O.k_language_id from 1 for 3),--sprache,
O.valid_from, --gueltig von
O.valid_to, --gueltig bis
O.id, --sourcesystem_id
'' || T.hiskey_id,
'' --struktur_c
   FROM orgunit O,k_orgunittype T
   where O.k_orgunittype_id=T.id
   and T.hiskey_id=3 --Fachbereich
union
   SELECT 
   619, 
-1, 
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   substring(O.defaulttext from 1 for 100), 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),--astat,
substring('' || O.parent_lid from 1 for 3),--parent
substring(O.uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || O.k_language_id from 1 for 3),--sprache,
O.valid_from, --gueltig von
O.valid_to, --gueltig bis
O.id, --sourcesystem_id
'' || T.hiskey_id,
'' --struktur_c
   FROM orgunit O,k_orgunittype T
   where O.k_orgunittype_id=T.id
   and T.hiskey_id=7 --Lehreinheit
 union
 --Standorte
   SELECT 
   	9012, 
	-1, 
    '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), --astat,
'',--parent
substring(uniquename from 1 for 10), --sortc1
substring('' || hiskey_id from 1 for 10), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id, --sourcesystem_id
'', --hiskey_id
'' --struktur_c
   FROM k_place_of_studies
   union
   --pstatus
   SELECT 
   	622, 
	-1, 
   '' || id,
   substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
	'',
'',--parent
substring(uniquename from 1 for 10), --sortc1
'', --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
id, --sourcesystem_id
'' || hiskey_id, 
'' --struktur_c
   FROM k_workstatus
 union
 --gdbu
SELECT 
    27, 
 0, 
   '' || id, -- gdbu,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), -- astat,
'', --parent
substring(uniquename from 1 for 10), --sortc1
 '' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'' , -- hiskey,
'' --struktur_c
    FROM k_reason_of_leave 
   union
 --gdex
SELECT 
    62, 
 0, 
   '' || id, -- gdbu,
substring(shorttext from 1 for 10), 
   substring(defaulttext from 1 for 100), 
   substring(longtext from 1 for 150), 
substring(astat from 1 for 10), -- astat,
'', --parent
substring(uniquename from 1 for 10), --sortc1
 '' ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'' , -- hiskey,
'' --struktur_c
    FROM k_reason_of_finishing 
   ;

    SELECT 
   9002, 
-1, 
   part,
   ktxt, 
   dtxt, 
   ltxt, 
astat,
astat,
'',
'',
sprache
FROM k_part 
   WHERE part is not null
   
union
SELECT 
   618, 
-1, 
   abext,
   ktxt, 
   dtxt, 
   ltxt,
astat,
astat,
'' ,
'',
''
   FROM k_abext
   ;
   
   

    SELECT 
   9002, 
-1, 
   part,
   ktxt, 
   dtxt, 
   ltxt, 
astat,
astat,
'',
'',
sprache
FROM k_part 
   WHERE part is not null
    union
   SELECT 
   618, 
-1, 
   abext,
   ktxt, 
   dtxt, 
   ltxt,
astat,
astat,
'' ,
'',
''
   FROM k_abext
union
SELECT 
   9011, 
0, 
   ppruefer,
   kname, 
   nachname  , 
  nachname || ', '|| vorname ,
'',
'',
'',
'',
''
   FROM k_ppruef 
   WHERE ppruefer is not null 
   and '$PRUEFER_NAME'='true'
    union
SELECT 
   305, 
0, 
   sperre,
   ktxt, 
   dtxt  , 
  ltxt ,
sos_sperre1::char(10),
sos_sperre2::char(10),
'',
'',
''
   FROM k_sperre
   WHERE sperre is not null 
   and aikz='A'
   union
SELECT 
   9013, 
0, 
   modulart,
   ktxt, 
   dtxt  , 
  ltxt ,
'',
'',
'',
'',
sprache
   FROM k_modulart
   WHERE modulart is not null 
   and aikz='A'
   union
SELECT 
   623, 
0, 
   art,
   gebart, 
   dtxt  , 
  artmind ,
astat,
'' || maxbonus,
'' ,
gebart, --bund_apnr
''
   FROM k_minder
   WHERE art is not null 
   and aikz='A'
   ;

    SELECT 
   9002, 
-1, 
   part,
   ktxt, 
   dtxt, 
   ltxt, 
astat,
astat,
'',
'',
sprache
FROM k_part 
   WHERE part is not null
    union
   
SELECT 
   618, 
-1, 
   abext,
   ktxt, 
   dtxt, 
   ltxt,
astat,
astat,
'' ,
'',
''
   FROM k_abext
union
SELECT 
   9011, 
0, 
   ppruefer,
   kname, 
   nachname  , 
  nachname || ', '|| vorname ,
'',
'',
'',
'',
''
   FROM k_ppruef 
   WHERE ppruefer is not null 
   and '$PRUEFER_NAME'='true'
    union
SELECT 
   305, 
0, 
   sperre,
   ktxt, 
   dtxt  , 
  ltxt ,
sos_sperre1::char(10),
sos_sperre2::char(10),
'',
'',
''
   FROM k_sperre
   WHERE sperre is not null 
   and aikz='A'
   union
SELECT 
   9013, 
0, 
   modulart,
   ktxt, 
   dtxt  , 
  ltxt ,
'',
'',
'',
'',
sprache
   FROM k_modulart
   WHERE modulart is not null 
   and aikz='A'
    union
SELECT 
   623, 
0, 
   art,
   substring(dtxt from 1 for 10), 
   dtxt  , 
  artmind ,
astat,
'',--parent
'' || maxbonus, --sortc1
gebart, --bund_apnr
''
   FROM k_minder
   WHERE art is not null 
   and aikz='A'
   ;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT pvers,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       sprache,
       refpvers FROM k_pvers
       where refpvers is not null;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT refstg,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       astat,
       fb,
       astfr,
       astgrp,
       zulplaugr,
       kzfaarray,
       sortkz1,
       sprache,
       refstg
FROM k_stg
where refstg is not null
      and (sprache='D' or sprache is null)
       and trim (refstg) !=''; 

SELECT    '' || S.id, 
'A' as aikz,
   substring(S.shorttext from 1 for 10), 
   substring(S.defaulttext from 1 for 100), 
   substring(S.longtext from 1 for 100), 
S.astat_land as astat,
'' as fb, --wird später aus cifx geholt
'' as astfr,
'' as astgrp,
'' as zulplaugr,
'' as kzfaarray,
'' as sortkz1,
substring('' || S.k_language_id from 1 for 3), --sprache,
S.id
FROM subject S;

SELECT stgext,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       astat,
       astfr,
       astgrp FROM k_stgext; 

    SELECT * from tmp_xdummy
where 1=0; 

SELECT abext,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       astat,
       mag_laa
FROM k_abext;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT  abstgv,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       pversion,
       frist1,
       frist2,
       frist3,
       frist3_alt,
       frist4,
       astat1,
       astat2,
       astgrp,
       prfgamt,
       vglgrp,
       fb,
       stutyp,
       kzfa,
       immsemkz,
       kzfaarray,
       stort,
       lehreinh,
       stkkurs1,
       stkkurs2,
       stkkurs3,
       prax1fsem,
       prax2fsem,
       abschl,
       stg,
       vert,
       schwp,
       stuart,
       stufrm,
       abstitel,
       abstgvnr,
       "" as efh,
       "" as regelstz,
       "" as email_part,
       "" as login_part,
       "" as sem_gueltigbis
       FROM k_abstgv; 

SELECT  abstgv,
       aikz,
       ktxt,
       dtxt,
       ltxt,
       pversion,
       frist1,
       frist2,
       frist3,
       frist3_alt,
       frist4,
       astat1,
       astat2,
       astgrp,
       prfgamt,
       vglgrp,
       fb,
       stutyp,
       kzfa,
       immsemkz,
       kzfaarray,
       stort,
       lehreinh,
       stkkurs1,
       stkkurs2,
       stkkurs3,
       prax1fsem,
       prax2fsem,
       abschl,
       stg,
       vert,
       schwp,
       stuart,
       stufrm,
       abstitel,
       abstgvnr,
       efh,
       regelstz,
        email_part,
       login_part,
       sem_gueltigbis
       FROM k_abstgv; 

SELECT substring(C.uniquename from 1 for 10) ,-- abstgv,
   'A',--    aikz,
   substring(C.shorttext from 1 for 10), 
   substring(C.defaulttext from 1 for 25), 
   substring(C.longtext from 1 for 150), 
   C.k_examinationversion_id,--    pversion,
   '',--    frist1,
   '',--    frist2,
   '',--    frist3,
   '',--    frist3_alt,
   '',--    frist4,
   '',--        astat1,
   '',--        astat2,
   '',--        astgrp,
    '',--       prfgamt,
   '',--        vglgrp,
    C.orgunit_id,--       fb,
    C.k_type_of_study_id,--   stutyp,
    C.k_subject_indicator_id,--   kzfa,
    '',--    immsemkz,
    '',--    kzfaarray,
    C.k_place_of_studies_id,--   stort,
    C.teachingunit_orgunit_id,--  lehreinh,
    '',--    stkkurs1,
    '',--    stkkurs2,
    '',--    stkkurs3,
    '',--    prax1fsem,
    '',--    prax2fsem,
    C.degree_id,--   abschl,
     C.subject_id,--  stg,
     C.major_field_of_study_id,--  vert,
     C.course_specialization_id,--  schwp,
    '',--    stuart,
     C.k_form_of_studies_id,--  stufrm,
     '',--   abstitel,
     C.id,--  abstgvnr,
     '',--   efh,
     C.regular_number_of_semesters,--  regelstz,
     '',--    email_part,
     '',--   login_part,
     '' || C.to_term_year || T.termnumber --  sem_gueltigbis
       FROM tmp_course_of_study C
       left outer join term_type  T on (C.to_term_type_id=T.id); 

SELECT abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       pnr,
       pabschn,
       pteil,
       pdum,
       pausw1,
       pausw2,
       pausw3,
       pktxt,
       pdtxt,
       '', --wg. Umbrüchen erstmal auskommentiert : pltxt1,
       '', --wg. Umbrüchen erstmal auskommentiert : pltxt2,
       '', --wg. Umbrüchen erstmal auskommentiert : pltxt3,
       '', --wg. Umbrüchen erstmal auskommentiert : pltxt4,
       geldat,
       ppflicht,
       pform,
       part,
       pdauer,
       partngb,
       pfsem,
       psws,
       pminsem,
       phoesem,
       pmaxvbe,
       pmaxver,
       pzuver,
       pmaxrueck,
       pwfrist,
       pfrist1,
       pfrist2,
       pfristr,
       pnhstat3,
       paenddat,
       pnrvl1,
       pnrvl2,
       pnrvl3,
       pnrvl4,
       pnrvl5,
       pvken1,
       pvken2,
       pvken3,
       pvken4,
       pvken5,
       psort1,
       psort2,
       psort3,
       pnra,
       petgp,
       pmerg,
       pbedv,
       pbedg,
       pbedw,
       vordipl,
       pfnrex,
       pbetrag,
       panmkenn,
       freivers,
       pbetragz1,
       pbetragz2,
       pordnr,
       bonus,
       malus,
       bogverb,
       bogempf,
       mogverb,
       mogempf,
       pnotpkt,
       fb,
       nacharb,
       meldekz,
       sivabschl,
       sivstg,
       sivvert,
       sivschwp,
       sivkzfa,
       sivpversion,
       zmadauer,
       '', --wg. Umbrüchen erstmal auskommentiert :beleg,
       '', --wg. Umbrüchen erstmal auskommentiert :diplkz,
       bonus_bei_nb,
       steuer_wvl,
       bendauer,
       disdauer,
       diszmadauer,
       instnr,
       beschkat,
       labgewicht,
       standbearb,
       modulturnus,
       praesenzzeit,
       selbstzeit,
       workload,
       modulart,
       modulcode,
       moduldauer,
       zmamoduldauer,
       verart,
       max_teilnehmer
FROM pord;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT superx_sos.mtknr_ldsg,
       abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pnr,
       psem,
       pversuch,
       prueck,
       pversion,
       antrdat,
       meldat,
       beabeg,
       dauer,
       verlkz,
       abdat,
       tabdat,
       pnoteges,
       zeudat,
       labnr
FROM dipl, superx_sos
WHERE dipl.mtknr = superx_sos.mtknr
AND dipl.psem >= $start_pruef_sem        
;

    SELECT * from tmp_xdummy
where 1=0; 

SELECT superx_sos.mtknr_ldsg,
       art,
       grund,
       vonsem,
       bissem,
       bonus,
       betrag,
 --      bemerkung,
       gewicht,
       hskfzkz,
       hskfz,
       hsart,
       lfdnr,
       bewnr,
--       herkunft,
       antr_genehmigt,
       antr_datum
FROM minder, superx_sos
WHERE minder.mtknr = superx_sos.mtknr
AND (minder.vonsem >= $start_stud_sem  or minder.vonsem is null)      
;

    SELECT * from tmp_xdummy
where 1=0; 

select distinct S.mtknr_ldsg,
 R.identnr,
 I.name,
 I.vorname,
 T.tel,
 T.artkz,
 T.anschrkz,
 O.res13
FROM identroll R, sos O, superx_sos S, ident I left outer join telefon T
on (T.identnr=I.identnr)
WHERE R.verbindung_integer = S.mtknr
and O.mtknr=S.mtknr
and I.identnr=R.identnr
and R.rolle='S'
and '$STUD_IDENT'='true';

    SELECT * from tmp_xdummy
where 1=0; 

SELECT pordnr,
       pktxt,
       pdtxt,
       pltxt1,
       pordsnr
FROM pords
where sprache='D'

    SELECT * from tmp_xdummy
where 1=0; 

SELECT porgnr,
pordnr,
abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       angeboten,
pdauer, 
ppruefer,
zweitpruefer,
psem,
pnr
from  porg

    SELECT * from tmp_xdummy
where 1=0; 

drop table superx_sos;

drop table tmp_xdummy;

drop table tmp_hskonst;

drop table tmp_course_of_study;