create temp table tmp_mtknr_ldsg(mtknr integer);
-- alle mtknr aus der Tabelle sos in die Tabelle mtknr_ldsg einfügen
--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;

DROP TABLE IF EXISTS tmp_xdummy;
create table tmp_xdummy
(
c char(10)
);

create table tmp_xdummy
(
c char(10)
);

DROP TABLE IF EXISTS superx_sos;
create table superx_sos (mtknr integer, mtknr_ldsg integer);

create table superx_sos (mtknr integer, mtknr_ldsg integer);

DROP TABLE IF EXISTS superx_sos;
create table superx_sos (id integer,
mtknr_ldsg integer,
person_id integer,
erhssembrd integer,
erhskfz varchar(10), 
discontinuationsemester integer,
study_before_university_lid integer,
study_before_country_lid integer,
study_before_place_of_study char(10),
study_before_country_of_study char(10),
k_study_interruption_type_id integer,
k_study_interruption_type_astat char(10),
personinfo_country_id integer,
second_nationality_country_lid integer,
second_nationality_astat char(10),
orgrole_valid_from date,
orgrole_valid_to date,
person_k_gender_id integer,
person_birthdate date,
person_birthcity varchar(255)
);

insert into tmp_xdummy values('1');

create table superx_sos (mtknr integer, mtknr_ldsg integer);

insert into tmp_xdummy values('1');

create table tmp_hskonst (pnr integer);

DROP TABLE IF EXISTS tmp_hskonst;
create table tmp_hskonst (pnr integer);

DROP TABLE IF EXISTS tmp_course_of_study;
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,
    k_field_of_study_subj_id integer,
    k_field_of_study_cos_id integer,
    examination_office_no character varying,
    is_historized smallint,
    subject_defaulttext character varying
);

--DDP.studysemester fuer Teilzeit-Studienverlauf ungewichtet entladen: Z-242699
--COS.regular_number_of_semesters fuer Teilzeit-Studiengaenge gewichtet entladen: Z-242699
DROP TABLE IF EXISTS tmp_studysem;
CREATE TABLE tmp_studysem AS
(
  SELECT CAST(cv.parameter_value AS INTEGER) AS studysem_integer 
  FROM confparameter cp
    JOIN confvalue cv ON cp.id=cv.confparameter_id
    JOIN confsrc cs ON cv.confsrc_id=cs.id
  WHERE cp.confkey='cm.stu.degreeprogramprogress.studysemester_integer' AND
    cp.active=1 AND cs.active=1
  ORDER BY cs.priority DESC
  LIMIT 1
); 
INSERT INTO tmp_studysem select 1 WHERE 0=(select count(*) from tmp_studysem);

create table tmp_hskonst (pnr 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_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,
       k_field_of_study_cos_id,
       examination_office_no,
       is_historized)
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, --Z-242699
       CASE WHEN (0=(select studysem_integer from tmp_studysem) AND (k_enrollment_id=3 AND (part_time_percentage IS NOT NULL) AND part_time_percentage!=0) ) THEN trunc(regular_number_of_semesters*100/part_time_percentage) ELSE regular_number_of_semesters END,
       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,
       k_field_of_study_id,
       examination_office_no,
       0 --is_historized
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)
)
);
--wenn degree nicht ermittelt werden kann, dann ohne Historisierung
update tmp_course_of_study set degree_id=(select min(D.id) from degree D
where D.lid=tmp_course_of_study.degree_lid
)
where degree_id 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)
)
);
--wenn subject nicht ermittelt werden kann, dann ohne Historisierung
update tmp_course_of_study set subject_id=(select min(D.id) from subject D
where D.lid=tmp_course_of_study.subject_lid
)
where subject_id is null
;
UPDATE tmp_course_of_study SET subject_defaulttext=(SELECT defaulttext FROM subject D
WHERE D.id=tmp_course_of_study.subject_id AND tmp_course_of_study.subject_id IS NOT 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)
)
);
--wenn Studiengang heute gültig ist und orgunit historisiert, nimmt er den aktuellen FB
update tmp_course_of_study set orgunit_id=(select D.id from orgunit D
where D.lid=tmp_course_of_study.orgunit_lid
and current_date between D.valid_from and D.valid_to)
where current_date between valid_from and valid_to
and orgunit_id 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;

--wenn Studiengang heute gültig ist und orgunit historisiert, nimmt er aktuelle LE
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 current_date between D.valid_from and D.valid_to)
where current_date between valid_from and valid_to
and teachingunit_orgunit_id is null;


--k_field_of_study_subj_id
update tmp_course_of_study set k_field_of_study_subj_id=(select S.k_field_of_study_id
from subject S
where S.id=tmp_course_of_study.subject_id)
;

--k_field_of_study_cos_id
update tmp_course_of_study set k_field_of_study_cos_id=(select S.k_field_of_study_id
from subject S
where S.id=tmp_course_of_study.subject_id)
where k_field_of_study_cos_id is null
;
--nicht mehr noetig fuer faecher-Zuordn.: hochprojizieren auf Fächergruppe
--update tmp_course_of_study set k_field_of_study_id=(select K.parent_id
--from k_field_of_study K
--where K.id=tmp_course_of_study.k_field_of_study_id);

--is_historized=1 wenn es mehrere Studiengänge pro LID gibt
update tmp_course_of_study set is_historized=1
where 1 < (select count(*) from course_of_study C
where C.lid=tmp_course_of_study.lid);

create index i_tc1 on tmp_course_of_study(lid);

insert into tmp_hskonst
select 1 from k_stg where 1=0;

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 (fehlerkz not in ("F", "V")
	or fehlerkz is null)
$STUDENT_SOSPOS_FILTER
 and   (
 --Entweder
 "$SOS_UNL_COMPLETE"='true'
 --oder DATLAE
 or ((datlae >= date("$DATUM")
	 OR ruebeudat >= date("$DATUM")
	 OR exmdat >= date("$DATUM"))
	)
	--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 (fehlerkz not in ('F', 'V')
	or fehlerkz is null)
$STUDENT_SOSPOS_FILTER
 and   (
 --Entweder
 '"$SOS_UNL_COMPLETE"'='"true"'
 --oder DATLAE
 or ((datlae >= date('"$DATUM"')
	 OR ruebeudat >= date('"$DATUM"')
	 OR exmdat >= date('"$DATUM"'))
	)
	--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(
  id,
  mtknr_ldsg,
  person_id,
  person_birthdate,
  person_birthcity,
  person_k_gender_id
)
SELECT DISTINCT
  S.id,
  S.registrationnumber,
  S.person_id,
  P.birthdate,
  substring(P.birthcity from 1 for 255),
  P.k_gender_id
FROM
  student S,
  person P
WHERE
  P.id = S.person_id
  AND S.registrationnumber IS NOT NULL
  $STUDENT_FILTER
  AND '$SOS_UNL_ANON' != 'true'
  AND ((S.updated_at > '$stu_updated_at') OR (S.updated_at IS NULL AND '$stu_updated_at' = '1900-01-01'))
  --keine vorläufigen Studierenden #118453
  --nur Rollen "Student" und "Gasthörer" (hiskey_id 5 und 8)
  AND 0 < (
    SELECT
      count(*)
    FROM
      orgrole O,
      role R
    WHERE
      R.id = O.role_id
      AND S.person_id = O.person_id
      AND R.hiskey_id IN (5, 8))
  --nicht Rolle "Gelöscht" (hiskey_id 3000)
  AND 0 = (
    SELECT
      count(*)
    FROM
      orgrole O,
      role R
    WHERE
      R.id = O.role_id
      AND S.person_id = O.person_id
      AND R.hiskey_id IN (3000)
    );


--Merkmale fuer Ersteinschreibung ermitteln - Ersteinschreibung: Frueheres/Externes Studium
--Erstes Studium (erhssembrd,study_before_place_of_study,study_before_country_of_study,erhskfz)
UPDATE
  superx_sos
SET
  erhssembrd = to_number(('' || STUBE.first_term_year || TETY.termnumber),'99999'),
  discontinuationsemester = STUBE.discontinuationsemester,
  study_before_university_lid = STUBE.university_lid,
  study_before_country_lid = STUBE.country_lid,
  k_study_interruption_type_id = STUBE.k_study_interruption_type_id
FROM
  study_before STUBE LEFT OUTER JOIN term_type TETY on STUBE.first_term_type_id = TETY.id
WHERE
  STUBE.person_id = superx_sos.person_id;
  
--Setzen: superx_sos.study_before_place_of_study (beim Inlandsstudium): 
UPDATE superx_sos SET study_before_place_of_study=(SELECT U.astat_university_of_enrollment FROM university U WHERE superx_sos.study_before_university_lid=U.lid)
  WHERE study_before_university_lid IS NOT NULL;

--Ersteinschreibung an eigener HS: .Z-244931
DROP TABLE IF EXISTS tmp_erhssembrd;
CREATE TABLE tmp_erhssembrd AS
(
WITH tm1dpp(person_id,dpp_startdate_min) as (
  SELECT DP.person_id,min(DPP.startdate) min_start
   FROM   
     degree_program DP,
     degree_program_progress DPP,
     course_of_study COS
   WHERE 
     DP.id=DPP.degree_program_id 
     AND COS.id=DPP.course_of_study_id 
     AND (
       (DPP.k_studystatus_id IN (select id from k_studystatus where btrim(astat)='1') 
         AND (DPP.k_studentstatus_id IN (select id from k_studentstatus where btrim(astat) in ('1','2')) ) ) 
       OR  --beurlaubte im fsem=0
       (DPP.k_studystatus_id IN (select id from k_studystatus where btrim(astat)='4') AND DPP.studysemester=0 
         AND (DPP.k_studentstatus_id IN (select id from k_studentstatus where btrim(astat) in ('1','2')) ) )
     )
     AND DPP.studynumber=1 AND DPP.subjectnumber=1
     AND DP.belongs_to='STUDENT'
  GROUP BY  DP.person_id
  ) 
   SELECT DP1.person_id dpperson_id, 
           COS.k_place_of_studies_id cosk_plc_of_st_id, null::char(10) distr_astat, null::char(10) plc_of_st_astat,
           DPP1.period_id dppperiod_id, null::int sem, null::int term_type_id, null::int term_year   
   FROM   
     degree_program_progress DPP1,degree_program DP1,course_of_study COS, tm1dpp
   WHERE
     DP1.id=DPP1.degree_program_id 
     AND COS.id=DPP1.course_of_study_id 
     AND DP1.person_id=tm1dpp.person_id AND DPP1.startdate=tm1dpp.dpp_startdate_min 
     AND (DPP1.k_studystatus_id IN (select id from k_studystatus where btrim(astat)='1') OR  (DPP1.k_studystatus_id IN (select id from k_studystatus where btrim(astat)='4') AND DPP1.studysemester=0) )
     AND DPP1.studynumber=1 AND DPP1.subjectnumber=1
     AND DP1.belongs_to='STUDENT'
);
CREATE index i_te1_pid ON tmp_erhssembrd(dpperson_id);

UPDATE tmp_erhssembrd SET distr_astat=
  (SELECT D.astat FROM hisinone.district D,hisinone.k_place_of_studies KP WHERE KP.district_id=D.id AND tmp_erhssembrd.cosk_plc_of_st_id=KP.id)
WHERE cosk_plc_of_st_id IS NOT NULL;

UPDATE tmp_erhssembrd SET plc_of_st_astat=
  (SELECT KP.astat FROM k_place_of_studies KP WHERE tmp_erhssembrd.cosk_plc_of_st_id=KP.id)
WHERE cosk_plc_of_st_id IS NOT NULL;

--wenn plc_of_st_astat=k_place_of_studies.uniquename==DEFAULT(astat='0'), dann der Orgunit astat:
UPDATE tmp_erhssembrd SET plc_of_st_astat=
  (select max(astat) from orgunit where (parent_lid=(select max(lid) from hisinone.orgunit ORG where parent_lid IS NULL) OR parent_lid IS NULL))
WHERE cosk_plc_of_st_id IS NOT NULL AND btrim(plc_of_st_astat)='0';

--term_year, term_type_id --to-- sem setzen:
UPDATE tmp_erhssembrd SET (term_type_id, term_year) = (select term_type_id, term_year from period P where dppperiod_id=P.id);
UPDATE tmp_erhssembrd SET sem = to_number(('' || term_year || TETY.termnumber),'99999') 
  FROM term_type TETY
WHERE
  term_type_id=TETY.id;
CREATE index i_te1_sem ON tmp_erhssembrd(sem); 
--
UPDATE
  superx_sos
SET
  erhssembrd = to_number(('' || P.term_year || TETY.termnumber),'99999')
FROM
  tmp_erhssembrd ERST,
  period P,
  term_type TETY
WHERE
  superx_sos.person_id=ERST.dpperson_id 
  AND ERST.dppperiod_id=P.id 
  AND P.term_type_id=TETY.id
  AND erhssembrd IS NULL;
--  
--  
UPDATE superx_sos SET study_before_place_of_study='9990'
  WHERE study_before_place_of_study IS NULL AND study_before_university_lid IS NULL AND 
  (study_before_country_lid IS NOT NULL AND study_before_country_lid!=((select lid FROM hisinone.country WHERE astat='000' AND current_date BETWEEN valid_from AND valid_to LIMIT 1)));
--
DROP TABLE IF EXISTS tmp_person_minsem;
CREATE TABLE tmp_person_minsem AS
  SELECT dpperson_id,min(sem) minsem FROM tmp_erhssembrd GROUP BY dpperson_id; 
CREATE index i_tpm1_pid ON tmp_person_minsem(dpperson_id);

UPDATE superx_sos SET study_before_place_of_study = 
  (SELECT distinct plc_of_st_astat FROM tmp_erhssembrd ERST WHERE ERST.dpperson_id=superx_sos.person_id 
    AND ERST.sem=(select minsem from tmp_person_minsem tmp1 where tmp1.dpperson_id=superx_sos.person_id) )
  WHERE study_before_place_of_study IS NULL AND study_before_university_lid IS NULL
  AND (study_before_country_lid IS NULL OR study_before_country_lid=(SELECT lid FROM country WHERE astat='000' AND current_date BETWEEN valid_from AND valid_to LIMIT 1) );
--
DROP TABLE tmp_erhssembrd;	 
DROP TABLE tmp_person_minsem;
	 
----------------------------------------------------
-- Setzen: superx_sos.study_before_country_of_study:
UPDATE superx_sos SET study_before_country_of_study = (
       SELECT CNT.astat FROM country CNT WHERE CNT.lid = superx_sos.study_before_country_lid
       AND CNT.valid_to = (SELECT max(CNT2.valid_to) FROM country CNT2 WHERE CNT.lid = CNT2.lid LIMIT 1)) 
WHERE study_before_country_lid IS NOT NULL;
--Inland (DE)--
UPDATE   superx_sos SET study_before_country_of_study='000'
  WHERE (study_before_country_lid IS NULL AND study_before_university_lid IS NULL 
    AND study_before_country_of_study IS NULL)
    AND btrim(study_before_place_of_study) IN 
   (SELECT distinct D.astat FROM hisinone.district D JOIN hisinone.k_place_of_studies P ON D.id=P.district_id);
--Setzen: superx_sos.erhskfz
UPDATE superx_sos SET erhskfz=study_before_place_of_study
WHERE study_before_university_lid IS NOT NULL AND erhskfz is null;
--

UPDATE
  superx_sos
SET
  k_study_interruption_type_astat = (
    SELECT
      STINTY.astat
    FROM
      k_study_interruption_type STINTY
    WHERE
      STINTY.id = superx_sos.k_study_interruption_type_id
  )
WHERE
  k_study_interruption_type_id IS NOT NULL
;

UPDATE
  superx_sos
SET
  second_nationality_country_lid = prsinf.second_nationality_country_lid,
  personinfo_country_id = cnt.id
FROM
  personinfo prsinf
  LEFT OUTER JOIN country cnt ON cnt.lid = prsinf.country_lid AND cnt.valid_to = (SELECT max(c1.valid_to) FROM country c1 WHERE c1.lid = cnt.lid)
WHERE
  prsinf.person_id = superx_sos.person_id
;

UPDATE
  superx_sos
SET
  second_nationality_astat = (
    SELECT
      cnt.astat
    FROM
      country cnt
    WHERE
      cnt.lid = superx_sos.second_nationality_country_lid
      AND cnt.valid_to = (SELECT max(c2.valid_to) FROM country c2 WHERE c2.lid = cnt.lid)
  )
WHERE
  second_nationality_country_lid IS NOT NULL
;

UPDATE
  superx_sos
SET
  orgrole_valid_from = OrgR.valid_from,
  orgrole_valid_to = OrgR.valid_to
FROM
  orgrole OrgR
WHERE
  OrgR.person_id = superx_sos.person_id
  AND OrgR.role_id = 5
;

--CREATE index i_ts1 ON superx_sos(mtknr_ldsg);
CREATE index i_ts2 ON superx_sos(person_id);
CREATE INDEX tmp_i_ts99 ON superx_sos(id);

-- fuer unload-job id="sos_faecher"
DROP INDEX IF EXISTS tcos_i1sid;
CREATE INDEX tcos_i1sid ON tmp_course_of_study(subject_id);


DROP TABLE IF EXISTS tmp_dp_dis_cnt;
CREATE TABLE tmp_dp_dis_cnt AS
(
  select DegP.id,DegP.person_id,DegP.k_type_of_doctorate_id, DIS.astat as dastat, CNT.astat as castat 
  from degree_program DegP
    left outer join district DIS on DIS.id = DegP.final_exam_district_id
    left outer join country CNT on CNT.lid = DegP.final_exam_country_lid
  where CNT.valid_to = (SELECT max(c.valid_to) FROM country c WHERE c.lid = CNT.lid) and DegP.belongs_to='STUDENT'    
); 
CREATE INDEX tmp_ddc_i1id ON tmp_dp_dis_cnt(id);
CREATE INDEX tmp_ddc_i1pid ON tmp_dp_dis_cnt(person_id);

insert into superx_sos(mtknr, mtknr_ldsg)
SELECT  DISTINCT S.mtknr,S.mtknr
from sos S
where S.semester >= $start_stud_sem

 ;

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
union
select 34,"0","SOS_Trimester" 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
union
select 34,'0','SOS_Trimester' 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'
and $stu_prot_maxid=0
union
select 32,'0','sos_unload_complete' from tmp_xdummy
where '$SOS_UNL_COMPLETE'!='true'
or $stu_prot_maxid>0
union
select 33,'6','SOS_Quellsystem' from tmp_xdummy
union
select 34,'0','SOS_Trimester' from tmp_xdummy
where 0=(SELECT count(*)
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=3 --Trimester
)
union
select 34,'1','SOS_Trimester' from tmp_xdummy
where 0< (SELECT count(*)
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=3 --Trimester
)
;

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,'15','SOS_Quellsystem' from tmp_xdummy
union
select 34,'0','SOS_Trimester' from tmp_xdummy
;

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,
null::char(1) as hzbnote ,
hssemgewicht,
null::char(1) as urlsemgewicht ,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez,
ersthzbkfz,
ersthzbkfzkz,
ersthzbart,
NULL::char(1) AS second_nationality,
NULL::char(1) AS practicalsemester,
NULL::char(1) AS kollegsemester,
NULL::char(1) AS job,
NULL::char(1) AS studyrelatedjob,
NULL::char(1) AS studyrelatedinternship,
NULL::char(1) AS interruptiontype,
NULL::char(1) AS study_before_place_of_study,
NULL::char(1) AS study_before_country_of_study,
NULL::date AS org_role_valid_from,
NULL::date AS org_role_valid_to,
NULL::char(1) AS sema,
NULL::char(1) AS gebasoll,
NULL::char(1) AS geba,
NULL::char(1) AS geba_sws,
NULL::integer as ersthzbjahr,
NULL::integer as ersthzbnote
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,
null::char(1) as hzbnote ,
hssemgewicht,
urlsemgewicht ,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez,
ersthzbkfz,
ersthzbkfzkz,
ersthzbart,
NULL::char(1) AS second_nationality,
praxsem AS practicalsemester,
kolsem AS kollegsemester,
NULL::char(1) AS discontinuationsemester ,
NULL::char(1) AS studyrelatedjob ,
NULL::char(1) AS studyrelatedinternship,
NULL::char(1) AS interruptiontype,
NULL::char(1) AS study_before_place_of_study,
NULL::char(1) AS study_before_country_of_study,
NULL::date AS org_role_valid_from,
NULL::date AS org_role_valid_to,
sema,
gebasoll,
geba,
geba_sws,
ersthzbjahr,
ersthzbnote
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,
null::char(1) 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),
null::char(1) as hzbnote ,
hssemgewicht,
null::char(1) as urlsemgewicht,
erhskfz,
erhsart,
erhssembrd,
erstsemhs,
sperrart1,
sperrart2,
staatkez,
ersthzbkfz,
ersthzbkfzkz ,
ersthzbart,
NULL::char(1) AS second_nationality,
praxsem AS practicalsemester,
kolsem AS kollegsemester,
NULL::char(1) AS discontinuationsemester ,
NULL::char(1) AS studyrelatedjob ,
NULL::char(1) AS studyrelatedinternship,
NULL::char(1) AS interruptiontype,
NULL::char(1) AS study_before_place_of_study,
NULL::char(1) AS study_before_country_of_study,
NULL::date AS org_role_valid_from,
NULL::date AS org_role_valid_to,
sema,
gebasoll,
geba,
geba_sws,
ersthzbjahr,
ersthzbnote
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 DISTINCT
  S.mtknr_ldsg,
  S.person_birthdate,
  S.person_birthcity,                                                                                         --gebort,
  G.astat AS geschl,
  CASE WHEN RRT.k_statement_of_fact_id IS NOT NULL THEN RD.disenrollment_date END AS exmdat,                  --exmdat
  U.k_studystatus_id,                                                                                         --status
  '' || U.term_year || TT.termnumber,                                                                         --semester, TODO muss auf SuperX-Seite auf max(degree_program_progress) gesetzt werden
  null::char(1),                                                                                              --poplz,
  null::char(1),                                                                                              --poort,
  U.enrollmentdate,                                                                                           --immdat 10
  null::char(1),                                                                                              -- beugrund as gdbu,
  (SELECT STMT.uniquename FROM k_statement_of_fact STMT WHERE RRT.k_statement_of_fact_id = STMT.id) AS gdex,  -- #118735
  S.personinfo_country_id AS staat,
  null::char(1) AS bland,
  round(U.universitysemester,0)::int,                                                                         --hssem, TODO muss ermittelt werden
  U.leavesemester::int,                                                                                       --urlsem,
  U.home_country_lid,                                                                                         --hmkfzkz, Heimatwohnsitz 17,
  U.home_district_lid,                                                                                        --hmkfz,
  U.semester_country_lid,                                                                                     --semkfzkz,
  U.semester_district_lid,                                                                                             --semkfz
  --wird aus sos_hzb ermittelt:
  null::char(1),                                                                                              --hzbart,
  null::char(1),                                                                                              --hzbjahr,
  null::char(1),                                                                                              --hzbkfzkz,
  null::char(1),                                                                                              --hzbkfz,
  null::char(1),                                                                                              --extract(day from hzbdatum)::varchar(2) || '.' || extract(month from hzbdatum)::varchar(2) || '.' || extract(year from hzbdatum)::varchar(4),
  null::char(1),                                                                                              --hzbnote ,
  --TODO:
  null::char(1),                                                                                              --hssemgewicht,
  null::char(1) as urlsemgewicht,
  S.erhskfz,                                                                                                  --erhskfz, --28.01.2020
  null::char(1),                                                                                              --erhsart,
  S.erhssembrd,
  null::char(1),                                                                                              --erstsemhs
  null::char(1),                                                                                              --sperrart1,
  null::char(1),                                                                                              --sperrart2,
  null::char(1),                                                                                              --staatkez,
  null::char(1),                                                                                              --ersthzbkfz,
  null::char(1),                                                                                              --ersthzbkfzkz
  null::char(1),                                                                                              --ersthzbart
  S.second_nationality_astat,
  U.practicalsemester::int,
  U.kollegsemester::int,
  S.discontinuationsemester,
  U.study_related_job,
  U.study_related_internship,
  S.k_study_interruption_type_astat,
  S.study_before_place_of_study,
  S.study_before_country_of_study,
  S.orgrole_valid_from,
  S.orgrole_valid_to,
  NULL::char(1) AS sema,
  NULL::char(1) AS gebasoll,
  NULL::char(1) AS geba,
  NULL::char(1) AS geba_sws,
  NULL::integer as ersthzbjahr,
  NULL::integer as ersthzbnote
FROM
  k_gender G,
  (student U
  LEFT OUTER JOIN request_for_disenrollment RD ON (RD.id = U.request_for_disenrollment_id)
  LEFT OUTER JOIN request_base RB ON (RB.id = RD.id AND 0 < (
    SELECT
      count(*)
    FROM
      k_status
    WHERE
      k_status.id = RB.approval_k_status_id
      AND k_status.hiskey_id = 4001
      AND k_status.uniquename='G'
  ))
  LEFT OUTER JOIN reasonable_request_type RRT ON RB.reasonable_request_type_id = RRT.id
  LEFT OUTER JOIN term_type TT ON TT.id = U.term_type_id
  ),
  superx_sos S
WHERE
  S.person_k_gender_id = G.id
  AND U.id = S.id
  AND (U.term_year * 10 + TT.termnumber >= $start_stud_sem)
;

select superx_sos.mtknr_ldsg,
gebdat,
'',--gebort,
 (CASE WHEN geschl='M'  THEN '1'
 when geschl='W' then '2'
 WHEN geschl='X'  THEN '3'
 when geschl='U' then '4'
            else ''
	    end)  as geschl,
exmdat,
status,
semester,
'' ,--poplz,
'' ,--poort,
immdat,
'' ,--beugrund as gdbu,
exmgrund as gdex,
(select min(K.astat) from k_akfz K where K.akfz=sos.staat) as staat,
'' as bland,
'' ,--hssem,
'' ,--urlsem,
hmkfzkz,
hmkfz,
semkfzkz,
semkfz,
hzbart,
hzbjahr,
hzbkfzkz,
hzbkfz,
hzbdatum,
hzbnote ,
'' ,--hssemgewicht,
'' as urlsemgewicht,
erhskfz,
'' ,--erhsart,
erhssembrd,
erstsemhs,
'' ,--sperrart1,
'' ,--sperrart2,
'' ,--staatkez,
'' ,--ersthzbkfz,
'' ,--ersthzbkfzkz ,
'',--ersthzbart
'',-- second_nationality,
'',-- practicalsemester,
'',-- kollegsemester,
'',-- discontinuation_sem_zahl,
'',-- studyrelatedjob,
'',-- studyrelatedinternship,
'',-- interruptiontype,
erhskfz,-- study_before_place_of_study,
'', -- study_before_country_of_study
'', --date AS org_role_valid_from,
'', -- date AS org_role_valid_to
'' AS sema,
'' AS gebasoll,
'' AS geba,
'' AS geba_sws,
'' AS ersthzbjahr,
'' AS ersthzbnote
FROM     superx_sos,sos
where
sos.mtknr = superx_sos.mtknr
and sos.semester >= $start_stud_sem
;

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,
null:: char(255), --ersthzbart,
null:: smallint, --ersthzbjahr,
null:: varchar(255), --ersthzbkfzkz,
null:: varchar(255),--ersthzbkzf,
null:: date, --ersthzbdate,
null:: char(10) --ersthzbnote
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,
null:: char(255), --ersthzbart,
null:: smallint, --ersthzbjahr,
null:: varchar(255), --ersthzbkfzkz,
null:: varchar(255),--ersthzbkzf,
null:: date, --ersthzbdate,
null:: char(10) --ersthzbnote
FROM     superx_sos,sos
where
sos.mtknr = superx_sos.mtknr
and (fehlerkz not in ('F', 'V') or fehlerkz is null)

;

select distinct O.mtknr_ldsg,
H.entrance_qualification_type_id,--.hzbart,
extract(year from E.date_of_work)::smallint,--hzbjahr,
(select C.astat from country C
where C.lid=I.country_lid
and C.valid_to = (SELECT max(c2.valid_to) FROM country c2 WHERE c2.lid = C.lid)) as hzbkfzkz,
(select D.astat from district D
where D.lid=I.district_lid
and D.valid_to = (SELECT max(d2.valid_to) FROM district d2 WHERE d2.lid = D.lid)) as hzbkfz,
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),
I.foreign_grade, --hzbnote,
null:: char(255), --ersthzbart,
null:: smallint, --ersthzbjahr,
null:: varchar(255), --ersthzbkfzkz,
null:: varchar(255),--ersthzbkzf,
null:: date, --ersthzbdate,
null:: char(10) --ersthzbnote
FROM superx_sos O, student S, person P, examplan E, unit U, k_elementtype Y,
entrance_qualification H,
examimport I
where
S.id=O.id
and P.id=S.person_id
and P.id=E.person_id
and H.examplan_id=E.id
and I.examplan_id=E.id
and U.id=E.unit_id
and U.k_elementtype_id=Y.id
and Y.hiskey_id=7 --nur HZB
;

select superx_sos.mtknr_ldsg,
hzbart,
hzbjahr,
(case when hzbkfzkz ='A' then (select K.astat from k_akfz K where K.akfz=sos.hzbkfz)
when hzbkfzkz ='I' then '000'
else null end) as hzbkfzkz,
(case when hzbkfzkz ='I' then (select K.astat from k_ikfz K where K.ikfz=sos.hzbkfz)
else null end) as hzbkfz,
hzbdatum,
hzbnote,
null as hzbart,
null as hzbjahr,
null as hzbkfzkz,
null as hzbkzf,
null as hzbdate,
null as hzbnote
FROM     superx_sos,sos
where
sos.mtknr = superx_sos.mtknr
and sos.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,
null::char(1) as hssemgewicht,
null::char(1) as urlsemgewicht,
kohsem,
lfdnr,
endedat ,
anfdat ,
lepsem,
NULL::char(1), -- district.astat nur h1
NULL::char(1),  -- country.astat nur h1
null::int, --Art der Promotion
null::char(1), --field_of_study
null::char(1) as field_of_study,
null::Char(10),--primaerfach,
null::Char(10), --unikey
null::numeric(9,6), -- individual_number_of_semesters
null::int, --Studiengang-ID(HIS1)
null::int as hssem_valid,
null::Char(10) as kz_rueck_beur_ein_tsg,
null::Char(10) as endedat_tsg,
null::Char(10) as abmeldegrund,
null::Char(10) as abmeldegrund_tsg,
null::Char(10) as degree_program_uniquename
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,
NULL::char(1), -- district.astat nur h1
NULL::char(1),  -- country.astat nur h1
null::int, --Art der Promotion
null::char(1) as field_of_study,
null::Char(10),--primaerfach,
null::Char(10), --unikey
null::numeric(9,6), -- individual_number_of_semesters
null::int, --Studiengang-ID(HIS1)
null::int as hssem_valid,
null::Char(10) as kz_rueck_beur_ein_tsg,
null::Char(10) as endedat_tsg,
null::Char(10) as abmeldegrund,
null::Char(10) as abmeldegrund_tsg,
null::Char(10) as degree_program_uniquename
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,
null::char(1) as hssemgewicht,
null::char(1) 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,
NULL::char(1), -- district.astat nur h1
NULL::char(1),  -- country.astat nur h1
null::int, --Art der Promotion
null::char(1) as field_of_study,
null::Char(10),--primaerfach,
null::Char(10), --unikey
null::numeric(9,6), -- individual_number_of_semesters
null::int, --Studiengang-ID(HIS1)
null::int as hssem_valid,
null::Char(10) AS field_of_study_subj,
null::Char(10) as kz_rueck_beur_ein_tsg,
null::Char(10) as endedat_tsg,
null::Char(10) as abmeldegrund,
null::Char(10) as abmeldegrund_tsg,
null::Char(10) as degree_program_uniquename
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 DISTINCT
  S.mtknr_ldsg,
  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,
  null::char(1),--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,
  DP.k_reason_of_leave_id, --Beurlaubungsgrund
DP.k_reason_of_finishing_id, --Exmatrikulationsgrund
CASE WHEN (0=(select studysem_integer from tmp_studysem) AND  (C.k_enrollment_id=3 AND (DP.part_time_percentage IS NOT NULL) AND DP.part_time_percentage!=0) ) THEN trunc(DP.studysemester*100/DP.part_time_percentage) ELSE ceil(DP.studysemester) END, --F.stgsem,
DP.k_studentstatus_id, --F.hrst,
ceil(DP.clinical_semester),--F.klinsem,
ceil(U.universitysemester),--F.hssem,
C.k_type_of_study_id ,--stuart,
C.k_enrollment_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,
null::char(1),--stggewicht,
DP.studysemester,--stgsemgewicht,
null::char(1) as urlsemgewicht,
null::char(1) as hssemgewicht,
null::char(1),--kohsem,
0, --lfdnr,
extract(day from DP.finished)::varchar(2) || '.' || extract(month from DP.finished)::varchar(2) || '.' || extract(year from DP.finished)::varchar(4) ,--endedat
DP.reregistration_date, --anfdat #230268
ceil(DP.curriculum_semester),--lepsem
D.dastat, --district.astat
D.castat, --country.astat
-- DIS.astat, --district.astat
-- CNT.astat,  --country.astat
D.k_type_of_doctorate_id, --Art der Promotion
C.k_field_of_study_cos_id, --k_field_of_study_id/uniquename from course_of_study #222047 Studienbereich
--K1.uniquename, --k_field_of_study_id/uniquename from course_of_study #222047 Studienbereich
null::Char(10),--primaerfach,
null::Char(10), --unikey
DP.individual_number_of_semesters, --individual RSZ
DP.degree_program_id, --Studiengang-ID
(SELECT NOT (SELECT hiskey_id || '' FROM k_studentstatus where k_studentstatus.id = DP.k_studentstatus_id) = 
ANY (string_to_array(replace(cv.parameter_value, ' ', ''), ',')) 
  FROM confparameter cp
    JOIN confvalue cv ON cp.id=cv.confparameter_id
    JOIN confsrc cs ON cv.confsrc_id=cs.id
  WHERE cp.confkey='cm.stu.semester_calculation.studentstatus_no_semester' AND
    cp.active=1 AND cs.active=1
  ORDER BY cs.priority DESC
  LIMIT 1) AS hssem_valid,
C.k_field_of_study_subj_id AS field_of_study_subj,  --k_field_of_study_id from subject #222047
null::Char(10) as kz_rueck_beur_ein_tsg,
null::Char(10) as endedat_tsg,
null::Char(10) as abmeldegrund,
null::Char(10) as abmeldegrund_tsg,
null::Char(10) as degree_program_uniquename

FROM  period P,
term_type T,
degree_program_progress DP,
tmp_dp_dis_cnt D,
superx_sos S,
student U,
tmp_course_of_study C
--  LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=1) K1 ON
--  (K1.id=C.k_field_of_study_cos_id)

WHERE T.id=P.term_type_id
and D.id=DP.degree_program_id
and DP.course_of_study_id=C.id
and DP.period_id=P.id
and S.person_id = U.person_id
and S.person_id = D.person_id
and U.id=S.id
AND to_number('' || P.term_year || T.termnumber,'99999')  >= $start_stud_sem
;

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,
F.ruebeudat,
'' ,--semgewicht,
'' ,--stggewicht,
'' ,--stgsemgewicht,
'' as hssemgewicht,
'' as urlsemgewicht,
'' ,--kohsem,
'' ,--lfdnr,
F.endedat,
F.anfdat,
'', --lepsem
'',-- -- district.astat nur h1
'',--  -- country.astat nur h1
'' , --Art der Promotion
'' as field_of_study,
primaerfach,
unikey,
null as individual_number_of_semesters, --individual RSZ
null as degree_program_id, --Studiengang-ID
null as hssem_valid,
null as field_of_study_subj,
null as kz_rueck_beur_ein_tsg,
null as endedat_tsg,
null as abmeldegrund,
null as abmeldegrund_tsg,
null as degree_program_uniquename

FROM stg F, superx_sos S
WHERE F.mtknr = S.mtknr
and F.status is not null
AND F.semester >= $start_stud_sem
;

DROP TABLE IF EXISTS  tmp_dp_dis_cnt;

DROP TABLE IF EXISTS tmp_lab;

CREATE TABLE tmp_lab(
  student_id INTEGER,
  mtknr_ldsg INTEGER,
  examplan_id INTEGER,
  date_of_work DATE,
  term_year INTEGER,
  termnumber INTEGER,
  term_type_id INTEGER,
  term_segment INTEGER,
  default_examrelation_id INTEGER,
  unit_id INTEGER,
  k_elementtype_id INTEGER,
  k_unit_purpose_id INTEGER,
  examrelation_id INTEGER,
  k_remark_on_exam_id INTEGER,
  k_examform_id INTEGER,
  cancelation SMALLINT,
  person_id INTEGER,
  official_statistics INTEGER,
  grade DECIMAL(9,5),
  ppunkte DECIMAL(9,5),
  degree_program_id INTEGER,
  type_of_doctorate_id INTEGER,
  type_of_doctorate_astat INTEGER,
  degree_program_progress_id INTEGER,
  degree_program_progress_studynumber INTEGER,
  degree_program_progress_subjectnumber INTEGER,
  degree_program_progress_studysemester INTEGER,
  studysemester_acknowledgement_id INTEGER,
  studysemester_acknowledgement_semester_overall DECIMAL(19,6) ,
  studysemester_acknowledgement_former_degree_program DECIMAL(19,6) ,
  studysemester_acknowledgement_practice_semester DECIMAL(19,6) ,
  studysemester_acknowledgement_semester_abroad DECIMAL(19,6) ,
  course_of_study_id INTEGER,
  examrelation_k_workstatus_id INTEGER,
  examrelation_malus DECIMAL(9,5),
  examrelation_bonus DECIMAL(9,5),
  examrelation_parent_examplan_id INTEGER,
  panerk CHAR(10),
  type_of_grading SMALLINT,
  grading_type_id INTEGER,
  attempt INTEGER,
  ut_top_unit_id INTEGER, 
  ut_top_k_elementtype_id INTEGER,
  child_ut_top_unit_id INTEGER,
  child_ut_top_unit_stat INTEGER
  );

INSERT INTO tmp_lab(
  student_id,
  mtknr_ldsg,
  examplan_id,
  date_of_work,
  term_year,
  termnumber,
  term_type_id,
  term_segment,
  default_examrelation_id ,
  unit_id,
  k_elementtype_id,
  k_unit_purpose_id,
  k_remark_on_exam_id,
  k_examform_id,
  cancelation,
  person_id
)
SELECT
  S.id,
  O.mtknr_ldsg,
  E.id as examplan_id,
  E.date_of_work,
  E.term_year,
  T.termnumber,
  T.id,
  E.term_segment,
  E.default_examrelation_id ,
  E.unit_id,
  U.k_elementtype_id,
  U.k_unit_purpose_id,
  E.k_remark_on_exam_id,
  E.k_examform_id,
  E.cancelation,
  S.person_id
FROM
  superx_sos O,
  student S,
  examplan E,
  term_type T,
  unit U
WHERE
  S.person_id = E.person_id
  AND S.id = O.id
  AND T.id = E.term_type_id
  AND U.id = E.unit_id
  AND (
    U.uniquename != 'AP-EXT'
    OR U.uniquename IS NULL
  ) --ext.Pruefungnen ausschliessen
  AND 0 = (
    SELECT
      count(*)
    FROM
      k_elementtype K
    WHERE
      K.id = U.k_elementtype_id
      AND K.hiskey_id = 7
  ) --keine HZB-Leistungen
  AND (
    E.updated_at > '${exa_updated_at}'
    OR (
      E.updated_at IS NULL
      AND '${exa_updated_at}' = '1900-01-01')
    )
    AND (to_number('' || E.term_year || T.termnumber,'99999') ) >= $start_pruef_sem
;

UPDATE
  tmp_lab
SET
  examrelation_id = default_examrelation_id
WHERE
  examrelation_id IS NULL
;

UPDATE
  tmp_lab
SET
  examrelation_k_workstatus_id = R.k_workstatus_id,
  examrelation_malus = R.malus,
  examrelation_bonus = R.bonus,
  examrelation_parent_examplan_id = R.parent_examplan_id
FROM
  examrelation R
WHERE
  R.id = tmp_lab.examrelation_id
;

UPDATE
  tmp_lab
SET
  type_of_grading = gt.type_of_grading,
  grading_type_id = unr.grading_type_id
FROM
  examplan exp,
  unitrelation unr,
  unit u,
  grading_type gt
WHERE
  exp.id = tmp_lab.examplan_id
  AND exp.unit_id = u.id
  AND u.default_unitrelation_id = unr.id
  AND unr.grading_type_id = gt.id
; 

UPDATE
  tmp_lab
SET
  official_statistics = (
    SELECT
      official_statistics
    FROM
      unit U
    WHERE
      U.id = tmp_lab.unit_id
    )
;

UPDATE
  tmp_lab
SET
  official_statistics = 3
WHERE EXISTS (
  SELECT
    unit_id
  FROM
    examination X
  WHERE 
    X.unit_id = tmp_lab.unit_id
    AND k_examination_type_id IN (
      SELECT
        id
      FROM
        k_examination_type
      WHERE
        hiskey_id = 99
      )
    )
;

--VACUUM ANALYZE tmp_lab;

--benotet: grading_type wird z.Z. nicht ausgewertet (Z.244194)
UPDATE
  tmp_lab
SET
  (grade, attempt) = (
    SELECT
      R.grade, R.attempt
    FROM
      examresult R
    WHERE
      R.examrelation_id = tmp_lab.examrelation_id
  )
;

--Studienverlauf ermitteln
UPDATE
  tmp_lab
SET
  degree_program_progress_id = DP.id,
  degree_program_progress_studynumber = DP.studynumber,
  degree_program_progress_subjectnumber = DP.subjectnumber,
--  degree_program_progress_studysemester = DP.studysemester,
  degree_program_progress_studysemester = CASE WHEN (0=(select studysem_integer from tmp_studysem) AND  (C.k_enrollment_id=3 AND (DP.part_time_percentage IS NOT NULL) AND DP.part_time_percentage!=0) ) THEN trunc(DP.studysemester*100/DP.part_time_percentage) ELSE ceil(DP.studysemester) END, --F.stgsem,
  degree_program_id = D.id,
  course_of_study_id = C.id,
  studysemester_acknowledgement_id = DP.studysemester_acknowledgement_id,
  type_of_doctorate_id = D.k_type_of_doctorate_id,
  ut_top_unit_id = ut.top_unit_id
FROM
  degree_program D,
  degree_program_progress DP,
  period P,
  unit_top ut,
  unit_studies us,
  tmp_course_of_study C
WHERE
  D.id = DP.degree_program_id
  AND P.id = DP.period_id
  AND ut.top_unit_id = us.unit_id
  AND C.lid = us.course_of_study_lid
  AND DP.course_of_study_id = C.id
  AND (
    tmp_lab.date_of_work BETWEEN C.valid_from AND C.valid_to
    OR tmp_lab.date_of_work IS NULL
    OR C.is_historized = 0
    OR (
      C.valid_from IS NULL
      AND tmp_lab.date_of_work <= C.valid_to
    )
    OR (
      C.valid_to IS NULL
      AND tmp_lab.date_of_work >= C.valid_from
    )
  )
  AND tmp_lab.unit_id = ut.unit_id
  AND tmp_lab.person_id = D.person_id
  AND tmp_lab.term_type_id = P.term_type_id
  AND tmp_lab.term_year = P.term_year
;

--evt. früheren Studienverlauf ermitteln
UPDATE
  tmp_lab
SET
  degree_program_progress_id = DP.id,
  degree_program_progress_studynumber = DP.studynumber,
  degree_program_progress_subjectnumber = DP.subjectnumber,
--  degree_program_progress_studysemester = DP.studysemester,
  degree_program_progress_studysemester = CASE WHEN (0=(select studysem_integer from tmp_studysem) AND  (C.k_enrollment_id=3 AND (DP.part_time_percentage IS NOT NULL) AND DP.part_time_percentage!=0) ) THEN trunc(DP.studysemester*100/DP.part_time_percentage) ELSE ceil(DP.studysemester) END, --F.stgsem,
  degree_program_id = D.id,
  course_of_study_id = C.id,
  studysemester_acknowledgement_id = DP.studysemester_acknowledgement_id,
  type_of_doctorate_id = D.k_type_of_doctorate_id
FROM
  degree_program D,
  degree_program_progress DP ,
  tmp_course_of_study C
WHERE
  tmp_lab.course_of_study_id IS NULL
  AND D.id = DP.degree_program_id
  AND DP.id = (
    SELECT
      DP.id
    FROM
      degree_program D,
      degree_program_progress DP
    WHERE
      tmp_lab.person_id = D.person_id
      AND DP.degree_program_id = D.id
      AND DP.course_of_study_id IN (
        SELECT
          cos.id
        FROM
          unit_top ut,
          unit_studies us,
          course_of_study cos
        WHERE
          ut.unit_id = tmp_lab.unit_id
          AND ut.top_unit_id = us.unit_id
          AND us.course_of_study_lid = cos.lid
          AND (
            tmp_lab.date_of_work BETWEEN cos.valid_from AND cos.valid_to
            OR (
              tmp_lab.date_of_work IS NULL
                AND (
                  SELECT
                    startdate
                  FROM
                    period P,
                    period_usage PU,
                    k_period_usage KPU
                  WHERE
                    KPU.hiskey_id = 7
                    AND PU.k_period_usage_id = KPU.id
                    AND PU.period_id = P.id
                    AND P.term_year = tmp_lab.term_year
                    AND P.term_type_id = tmp_lab.term_type_id
                  ) BETWEEN cos.valid_from AND cos.valid_to
                )
              )
        )
    ORDER BY
      enddate desc
    LIMIT
      1
    )
    AND C.id = DP.course_of_study_id
    AND tmp_lab.course_of_study_id IS NULL
;

-- zusätzlich schauen nach Sätzen mit abgelaufener Gültigkeit, dabei den aktuellsten Gültigkeitszeitraum wählen
UPDATE
  tmp_lab
SET
  degree_program_progress_id = DP.id,
  degree_program_progress_studynumber = DP.studynumber,
  degree_program_progress_subjectnumber = DP.subjectnumber,
 -- degree_program_progress_studysemester = DP.studysemester,
  degree_program_progress_studysemester = CASE WHEN (0=(select studysem_integer from tmp_studysem) AND  (C.k_enrollment_id=3 AND (DP.part_time_percentage IS NOT NULL) AND DP.part_time_percentage!=0) ) THEN trunc(DP.studysemester*100/DP.part_time_percentage) ELSE ceil(DP.studysemester) END, --F.stgsem,
  degree_program_id = D.id,
  course_of_study_id = C.id,
  studysemester_acknowledgement_id = DP.studysemester_acknowledgement_id,
  type_of_doctorate_id = D.k_type_of_doctorate_id
FROM
  degree_program D,
  degree_program_progress DP ,
  tmp_course_of_study C
WHERE
  D.id = DP.degree_program_id
  AND DP.id = (
    SELECT
      DP.id
    FROM
      degree_program D,
      degree_program_progress DP
    WHERE
      tmp_lab.person_id = D.person_id
      AND DP.degree_program_id = D.id
      AND DP.course_of_study_id IN (
        SELECT
          cos.id from
          unit_top ut,
          unit_studies us,
          course_of_study cos
        WHERE
          ut.unit_id = tmp_lab.unit_id
          AND ut.top_unit_id = us.unit_id
          AND us.course_of_study_lid = cos.lid
        )
    ORDER BY 
      enddate DESC
    LIMIT
      1
    )
    AND C.id = DP.course_of_study_id
    AND tmp_lab.course_of_study_id IS NULL
;

-- kein Studienverlauf vorhanden, sehen ob ein Studiengang passt
UPDATE
  tmp_lab
SET
  course_of_study_id = (
    SELECT
      cos.id
    FROM
      unit_top ut,
      unit_studies us,
      course_of_study cos
    WHERE
      ut.unit_id = tmp_lab.unit_id
      AND ut.top_unit_id = us.unit_id
      AND us.course_of_study_lid = cos.lid
      AND (
        tmp_lab.date_of_work BETWEEN cos.valid_from AND cos.valid_to
        OR tmp_lab.date_of_work IS NULL)
    limit 1
)
WHERE
  course_of_study_id IS NULL
  AND 0 = (
    SELECT
      count(*)
    FROM
      unit u,
      k_elementtype k
    WHERE
      u.id = tmp_lab.unit_id
      AND u.k_elementtype_id = k.id
      AND k.hiskey_id NOT IN (2,3)
    )
;

UPDATE
  tmp_lab
SET 
  child_ut_top_unit_id=UR.child_unit_id
FROM 
  unitrelation UR
WHERE
  UR.parent_unit_id = tmp_lab.ut_top_unit_id
;

UPDATE
  tmp_lab
SET 
  ut_top_k_elementtype_id=U.k_elementtype_id
FROM 
  unit U
WHERE
  U.id = tmp_lab.ut_top_unit_id
;

UPDATE
  tmp_lab
SET
  child_ut_top_unit_stat = (
    SELECT official_statistics
    FROM unit U
    WHERE U.id = tmp_lab.child_ut_top_unit_id
    )
;

-- Sonderbehandlung Umrechnung Punkte bei Staatsexamen Jura in Noten
UPDATE
  tmp_lab
SET
 grade =
  CASE
    WHEN grade BETWEEN 14   AND 18    THEN 1
    WHEN grade BETWEEN 11.5 AND 13.99 THEN 2
    WHEN grade BETWEEN  9   AND 11.49 THEN 7
    WHEN grade BETWEEN  6.5 AND  8.99 THEN 3
    WHEN grade BETWEEN  4   AND  6.49 THEN 4
    WHEN grade BETWEEN  0   AND  3.99 THEN 5
  ELSE
    grade
  END
WHERE
  course_of_study_id IN (
    SELECT
      cos.id
   FROM
     course_of_study cos,
     subject s,
     degree d
   WHERE
     cos.subject_lid = s.lid
     AND cos.degree_lid = d.lid
     AND coalesce(d.astat_bund, d.astat_land) IN ('08','09') -- Staatsexamen
     AND coalesce(s.astat_bund, s.astat_land) = '0135' -- Jura (Rechtswissenschaft)
  )
  AND (type_of_grading IS NULL OR type_of_grading = 1) -- unbekannt oder Punkte
;

UPDATE
  tmp_lab
SET
  type_of_doctorate_astat = (
    SELECT
      to_number(D.astat,'99999')
    FROM
      k_type_of_doctorate D
    WHERE
      D.id = tmp_lab.type_of_doctorate_id
    )
WHERE
  type_of_doctorate_id IS NOT NULL
;

--Anerkennung
UPDATE
  tmp_lab
SET
  studysemester_acknowledgement_semester_overall = S.semester_overall,
  studysemester_acknowledgement_former_degree_program = S.former_degree_program,
  studysemester_acknowledgement_practice_semester = S.practice_semester,
  studysemester_acknowledgement_semester_abroad = S.semester_abroad
FROM
  studysemester_acknowledgement S
WHERE
  S.id = tmp_lab.studysemester_acknowledgement_id
;

UPDATE
  tmp_lab
SET
  panerk = 'J'
WHERE
  examplan_id IN (
    SELECT
      examplan_id
    FROM
      accreditation
    )
;

CREATE index i_tl1 ON tmp_lab(student_id);

SELECT distinct
 pord.stg,
 pord.vert,
 pord.abschl,
 pord.kzfa,
 null::char(1),--tid_stg,
 null::char(1),--sto,
 pord.pversion,
 pord.schwp,
 pord.pnr ,
 pord.pordnr,
 pord.pdtxt,
 pord.pnr,
 pord.modulart,
 null::char(1) --stutyp
FROM pord
;

 SELECT distinct
 C.subject_id,--stg,
 C.major_field_of_study_id, --vert,
 C.degree_id,--abschl,
 C.k_subject_indicator_id,--kzfa,
 null::char(1),--tid_stg,
 C.k_place_of_studies_id,--stort,
 C.k_examinationversion_id,--pversion,
 C.course_specialization_id,--schwp,
 U.official_statistics ,
 U.id,
 substring(U.defaulttext from 1 for 255),
 U.elementnr,
 U.k_elementtype_id,
 C.k_enrollment_id --stutyp
 FROM 
    unit_top ut,
    unit_studies us,
    tmp_course_of_study C,
   unit U
    WHERE U.id=ut.unit_id
    AND ut.top_unit_id = us.unit_id
    AND C.lid=us.course_of_study_lid

;

select * from k_stg where 1=0;

-- Externe Abschlussprüfungen
SELECT S.mtknr_ldsg AS matrikel_nr,
null::integer AS studentexternal_id,
CASE WHEN E.stgnr ~ '^[0-9]*$' THEN to_number(substring(E.stgnr from 1 for 1),'99')
ELSE NULL::int END AS studiengang_nr,
CASE WHEN E.stgnr ~ '^[0-9]*$' THEN to_number(substring(E.stgnr from 2 for 1),'9')
ELSE NULL::int END AS fach_nr,
E.abschl AS ang_abschl,
E.stgsem AS fachsem_zahl,
E.semester AS p_sem,
extract(day from E.lzabdat)::varchar(2) || '.' || extract(month from E.lzabdat)::varchar(2) || '.' || extract(year from E.lzabdat)::varchar(4) AS d_abg_pruefung,
extract(month from E.lzabdat)::integer AS p_monat,
extract(year from E.lzabdat)::integer AS p_jahr,
E.stg AS fach,
CASE WHEN E.pnote ~ '^[0-9]*$' THEN
substring(replace(replace(E.pnote,',',''),'.','') from 1 for 1) || '.' || substring(replace(replace(E.pnote,',',''),'.','') from 2 for 2)
ELSE NULL END,
E.pstatus,
E.stufrm as p_art,
E.hskfzkz,
E.hskfz,
K.astat AS hs,
E.hsart,
null::char(1), -- country astat wird in trans_sos_pruefungenext.sql ermittelt
null::int as examplan_id
from stgext E, superx_sos S, sos O left outer join  k_ikfzha K
on (K.ikfzha=trim(O.erhskfz) || trim(O.erhsart))
WHERE E.mtknr = S.mtknr
and E.mtknr=O.mtknr
and E.extart in ('AP')
;

SELECT S.mtknr_ldsg AS matrikel_nr,
null::integer AS studentexternal_id,
substring(E.stgnr from 1 for 1) AS studiengang_nr,
substring(E.stgnr from 2 for 1) AS fach_nr,
E.abschl AS ang_abschl,
E.stgsem AS fachsem_zahl,
E.semester AS p_sem,
E.lzabdat AS d_abg_pruefung,
month(E.lzabdat) AS p_monat,
year(E.lzabdat) AS p_jahr,
E.stg AS fach,
CASE WHEN E.pnote MATCHES '[0-9][0-9][0-9]' THEN
substring(replace(replace(E.pnote,',',''),'.','') from 1 for 1) || '.' || substring(replace(replace(E.pnote,',',''),'.','') from 2 for 2)
ELSE NULL::char(1) END,
E.pstatus,
E.stufrm as p_art,
E.hskfzkz,
E.hskfz,
K.astat AS hs,
E.hsart,
null::char(1), -- country astat wird in trans_sos_pruefungenext.sql ermittelt
null::int as examplan_id
from stgext E, superx_sos S, sos O left outer join  k_ikfzha K
on (K.ikfzha=(trim(O.erhskfz) || trim(O.erhsart)))
WHERE E.mtknr = S.mtknr
and E.mtknr=O.mtknr
and E.extart in ('AP') -- Externe Abschlussprüfungen
;

-- Externe Abschlussprüfungen
SELECT SS.mtknr_ldsg AS matrikel_nr
,SE.id AS studentexternal_id
--, 'AP' AS extart -- Abschlussprüfungen
,DP.studynumber AS studiengang_nr
,DP.subjectnumber AS fach_nr
,DE.astat_land AS ang_abschl
,DP.number_of_studysemester::INT AS fachsem_zahl
, '' || EP.term_year || TT.termnumber AS p_sem
,EP.date_of_work AS d_abg_pruefung
,EXTRACT(MONTH FROM EP.date_of_work)::int AS p_monat
,EXTRACT(YEAR FROM EP.date_of_work)::int AS p_jahr
,SU.astat_land AS fach
,EI.foreign_grade AS p_note
,KWS.id AS p_status
--,KEF.uniquename AS p_art
, D.k_form_of_studies_id as p_art
, CASE WHEN C.uniquename = 'D' THEN 'I' ELSE 'A' END AS hskfzkz
, CASE WHEN C.uniquename = 'D' THEN UNI.vehicle_registration_plate ELSE C.uniquename END AS hskfz
,UNI.astat AS hs
,UNI.universitytype AS hsart
,C.astat as country_astat
,EP.id as examplan_id
FROM person P
JOIN student S ON S.person_id = P.id AND S.registrationnumber IS NOT NULL
JOIN superx_sos SS on SS.id=S.id
JOIN examplan EP ON EP.person_id = P.id
JOIN unit U ON U.id = EP.unit_id
JOIN examimport EI ON EP.id = EI.examplan_id
JOIN degree_program_before D ON D.examimport_id = EI.id
JOIN studentexternal SE ON SE.id = D.studentexternal_id
JOIN degree_program_progress_before DP ON DP.degree_program_before_id = D.id
JOIN degree DE ON DE.lid = D.degree_lid AND DE.valid_to = (SELECT max(d.valid_to) FROM degree d WHERE d.lid=DE.lid)
JOIN examrelation ER ON EP.default_examrelation_id = ER.id
JOIN subject SU ON SU.lid = DP.subject_lid AND SU.valid_to = (SELECT max(s.valid_to) FROM subject s WHERE s.lid=SU.lid)
LEFT JOIN term_type TT ON EP.term_type_id = TT.id
--left join university UNI on EI.university_lid = UNI.lid and now() between UNI.valid_from and UNI.valid_to
LEFT JOIN university UNI ON SE.university_lid = UNI.lid AND UNI.valid_to = (SELECT max(u.valid_to) FROM university u WHERE u.lid = UNI.lid)
LEFT JOIN country C ON C.lid = SE.country_lid
LEFT JOIN k_workstatus KWS ON KWS.id = ER.k_workstatus_id
LEFT JOIN k_examform KEF ON KEF.id = EP.k_examform_id
WHERE U.uniquename = 'AP-EXT'
ORDER BY matrikel_nr,studiengang_nr,fach_nr
;

select * from k_stg where 1=0;

SELECT
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}
null::char(7), --pruefungsamt_nr
lab.pnr,   {pnr}
pversion, {pversion}
pstatus,{pstatus ist char(2)}
stgsem                  {fach_sem_zahl} ,
part,
labnr,
pversuch,
ppruef1,
ppruef2,
malus,
bonus,
pordnr,
psws,
null::char(1), --relation_id nur bei HIS1
pvermerk,
prueck,
ptermin,
pform,
null::char(1), --anger. fs insg.
null::char(1), --berufspraktische taetigkeit
null::char(1), --aus auslandsstudium
null::char(1), --aus anderem stg an einer deutschen hs
null::char(1), -- amtl. Schl. Art der Promotion
panerk,
pstatkennz,
ppunkte,
null::CHAR(1), --stuart
null::CHAR(1),  --stufrm
null::CHAR(1),  --stutyp
null::int, --degree_program_id (HIS1 only)
null::int, --k_elementtype_id (HIS1 only)
null::int, --top_unit-Element (HIS1 only)
null::int, --top_unit-Elementtype=4(PO) (HIS1 only)
null::int, --direkt Untergeordnetes Konto(HIS1 only)
null::int, --unit.official_statistics bei child_ut_top_unit_id (HIS1 only)
null::int, --Unit-Eigenschaft (HIS1 only)
psem::int, --Sem. der HP (HIS1 only)
pdatum , --Datum der HP (HIS1 only)
null::char(10) as pstatus_tsg, --CO only
null::decimal(8,2) as note_tsg, --CO only
null::char(1) as fach_sem_zahl_tsg --CO only
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
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,
null::char(7), --pruefungsamt_nr
lab.pnr,
pversion,
pstatus,
stgsem ,
part,
labnr,
pversuch,
ppruef1,
ppruef2,
malus,
bonus,
pordnr,
psws,
null::char(1), --relation_id nur bei HIS1
pvermerk,
prueck,
ptermin, --term_segment
pform,
null::char(1), --anger. fs insg.
null::char(1), --berufspraktische taetigkeit
null::char(1), --aus auslandsstudium
null::char(1), --aus anderem stg an einer deutschen hs
null::char(1), -- amtl. Schl. Art der Promotion
panerk,
pstatkennz,
ppunkte,
null::CHAR(1), --stuart
null::CHAR(1),  --stufrm
null::CHAR(1),  --stutyp
null::int, --degree_program_id (HIS1 only)
null::int, --k_elementtype_id (HIS1 only)
null::int, --top_unit-Element (HIS1 only)
null::int, --top_unit-Elementtype=4(PO) (HIS1 only)
null::int, --direkt Untergeordnetes Konto(HIS1 only)
null::int, --unit.official_statistics bei child_ut_top_unit_id (HIS1 only)
null::int, --Unit-Eigenschaft (HIS1 only)
psem::int, --Sem. der HP (HIS1 only)
extract(day from pdatum)::varchar(2) || '.' || extract(month from pdatum)::varchar(2) || '.' || extract(year from pdatum)::varchar(4), --Datum der HP (HIS1 only)
null::char(10) as pstatus_tsg, --CO only
null::decimal(8,2) as note_tsg, --CO only
null::char(1) as fach_sem_zahl_tsg --CO only
FROM lab , superx_sos S
WHERE lab.mtknr = S.mtknr
$LAB_FILTER
AND (lab.pstatus is null or lab.pstatus != 'SO')
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 
L.mtknr_ldsg,
degree_program_progress_studynumber,--substr(stgnr,1,1),
degree_program_progress_subjectnumber,--substr(stgnr,length(stgnr),1),
'' || C.degree_id,--abschl,
'' || L.term_year || L.termnumber,--psem,
extract(day from L.date_of_work)::varchar(2) || '.' || extract(month from L.date_of_work)::varchar(2) || '.' || extract(year from L.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('' || L.grade from 1 for 1) || coalesce(substring('' || L.grade from 3 for 2),'') ,--pnote,  TODO: examvaluation.grade
null::char(1),--vken1,
c.examination_office_no, --pruefungsamt
L.official_statistics, --lab.pnr,
'' || C.k_examinationversion_id,--pversion,
L.examrelation_k_workstatus_id,--pstatus,
L.degree_program_progress_studysemester,--stgsem , provisorisch s. Ticket https://hiszilla.his.de/hiszilla/show_bug.cgi?id=88788#c4
(select X.k_examination_type_id from examination X where X.unit_id=L.unit_id),--part,
L.examplan_id,--labnr,
L.attempt,--pversuch,
null::char(1),--ppruef1,
null::char(1),--ppruef2,
L.examrelation_malus,--malus
L.examrelation_bonus,--bonus
L.unit_id,--pordnr,
null::char(1),--psws
L.examrelation_parent_examplan_id,--relation_id(z.Z. immer null?)
L.k_remark_on_exam_id,--pvermerk
L.cancelation ,--prueck
L.term_segment,--zeitabschnitt
L.k_examform_id,--pform
ceil(L.studysemester_acknowledgement_semester_overall)::integer, --anger. fs insg.
ceil(L.studysemester_acknowledgement_practice_semester)::integer, -- berufspraktische taetigkeit
ceil(L.studysemester_acknowledgement_semester_abroad)::integer, -- aus Auslandsstudium
ceil(L.studysemester_acknowledgement_former_degree_program)::integer, --aus anderem stg an einer deutschen hs
L.type_of_doctorate_astat, -- amtl. Schl. Art der Promotion
L.panerk,--panerk
null::integer, --pstatkennz
L.ppunkte, --ppunkte
C.k_type_of_study_id, --stuart
C.k_form_of_studies_id,  --stufrm 
C.k_enrollment_id, --stutyp
L.degree_program_id,      --Studiengang-ID (HIS1)
L.k_elementtype_id,       --Elementtype-ID(Konto,Pruefung,PO - HIS1)
L.ut_top_unit_id,         --top_unit-Element
L.ut_top_k_elementtype_id,--top_unit-Elementtype=4(PO)
L.child_ut_top_unit_id,   --direkt Untergeordnetes Konto (Abschlusspruefung.Fall: 1.Ebene unter PO)
L.child_ut_top_unit_stat, --unit.official_statistics bei child_ut_top_unit_id
L.k_unit_purpose_id,      --Unit-Eigenschaft(Test-FreiburgFall)
'' || L.term_year || L.termnumber,--psem,
extract(day from L.date_of_work)::varchar(2) || '.' || extract(month from L.date_of_work)::varchar(2) || '.' || extract(year from L.date_of_work)::varchar(4),
null::char(10) as pstatus_tsg, --CO only
null::decimal(8,2) as note_tsg, --CO only
null::char(1) as fach_sem_zahl_tsg --CO only
FROM tmp_lab L left outer join tmp_course_of_study C
on (C.id=L.course_of_study_id)
;

SELECT distinct
mtknr,
substr(stgnr,1,1),
substr(stgnr,length(stgnr),1),
abschl,
psem,
pdatum,
stg,
'' ,--vert,
'' ,--schwp,
'' ,--kzfa,
pnote,
'' ,--vken1,
'',--pruefungsamt_nr
lab.pnr,
spov_abschluss as pversion,
pstatus,
stgsem ,
'' ,--part,
labnr,
pversuch,
ppruef1,
ppruef2,
malus,
bonus,
'',--pordnr bei CO ein Bigint, der Probleme macht
'' ,--psws,
'', --relation_id nur bei HIS1
'' ,--pvermerk,
case when prueck ='N' then '0' 
when prueck='J' then '1'
else '' end as prueck,
'' ,--ptermin, --term_segment
'', --pform,
'', --anger. fs insg.
'', --aus anderem stg an einer deutschen hs
'', --berufspraktische taetigkeit
'', --aus auslandsstudium
'' ,-- amtl. Schl. Art der Promotion
'', --panerk
'', --pstatkennz
'', --ppunkte
'', --stuart
'',  --stufrm 
'', --stutyp
null as degree_program_id, --Studiengang-ID (HIS1)
null as k_elementtype_id, --Elementtype-ID(Konto,Pruefung,PO - HIS1)
null as ut_top_unit_id,--top_unit-Element
null as ut_top_k_elementtype_id,--top_unit-Elementtype=4(PO)
null as child_ut_top_unit_id, --direkt Untergeordnetes Konto (Abschlusspruefung.Fall: 1.Ebene unter PO)
null as child_ut_top_unit_stat, --unit.official_statistics bei child_ut_top_unit_id
null as k_unit_purpose_id ,
null as psem, --Sem. der HP (HIS1 only)
null as pdatum , --Datum der HP (HIS1 only)
null as pstatus_tsg, --CO only
null as note_tsg, --CO only
null as fach_sem_zahl_tsg --CO only
FROM lab 
WHERE lab.psem >= $start_pruef_sem
;

select
null::integer AS labnr, -- Pruefungsnummer: labnr
null::integer AS st_abr_id, --Auslandsaufenthalte: stay_abroad.id
null::integer AS sourcesystem, -- Quellsystem
null::integer AS st_abr_country_astat, -- Staat des Auslandsaufenthaltes: country.astat
null::smallint AS st_abr_month, -- Dauer Auslandsaufenthalt in Monaten: stay_abroad.number_of_month
null::integer AS st_abr_type, -- Typ des Auslandsaufenthaltes: stay_abroad.stayabroad_type
null::integer AS st_abr_program, -- Mobilitätsprogramm: k_mobility_program.mobility_program
null::integer AS matrikel_nr,
null::integer AS sem_der_pruefung,
null::integer as d_abg_pruefung --Prüfungsdatum
from hskonst where 1=0

   SELECT distinct
L.examplan_id,
STAB.id,
CNT.astat, -- astat Land 1
STAB.number_of_month, -- Monate 1
SATY.astat, -- astat Art 1
MOPRO.astat, -- astat Mobilitätsprogramm 1
L.mtknr_ldsg,
'' || L.term_year || L.termnumber,--psem,
extract(day from L.date_of_work)::varchar(2) || '.' || extract(month from L.date_of_work)::varchar(2) || '.' || extract(year from L.date_of_work)::varchar(4)
 FROM 
    (tmp_lab as L
    join stayabroad_examplan as STABEX on L.examplan_id = STABEX.examplan_id
    join stay_abroad as STAB on STAB.id = STABEX.stay_abroad_id
    left outer join country as CNT on CNT.lid = STAB.country_lid
    left outer join k_stayabroad_type as SATY on SATY.id = STAB.k_stayabroad_type_id
    left outer join k_mobility_program as MOPRO on MOPRO.id = STAB.k_mobility_program_id)
 WHERE L.official_statistics in (1,2)
    AND (to_number('' || L.term_year || L.termnumber,'99999') ) >= $start_pruef_sem
;

select * from k_stg where 1=0;

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 student WHERE 1=0;

select * from k_stg where 1=0;

select
S.mtknr_ldsg, E.extart, E.semester, E.hskfzkz, E.hskfz,
K.astat, E.hsart, substring(E.stgnr from 1 for 1),
substring(E.stgnr from 2 for 1), E.abschl,
E.stg, E.stgsem
,null::char(1), -- country astat wird in trans_sos_faecher_ext.sql ermittelt
E.stufrm
from stgext E, superx_sos S, sos O left outer join  k_ikfzha K
on (K.ikfzha=trim(O.erhskfz) || trim(O.erhsart))
WHERE E.mtknr = O.mtknr
and E.mtknr=S.mtknr
and E.extart in ('ZW','VS');

    
-- AP (Abschlussprüfungen extern) fehlen hier. Werden gesondert behandelt!
SELECT SS.mtknr_ldsg AS matrikel_nr
, CASE WHEN 0 < (
   SELECT COUNT(1)
   FROM student S2, person PS
   JOIN degree_program D2 ON D2.person_id = PS.id
   JOIN degree_program_progress DP2 ON DP2.degree_program_id = D2.id
   JOIN period P ON P.id = DP2.period_id AND P.term_year=DP.term_year
   JOIN term_type TT2 ON P.term_type_id = TT2.id AND TT2.termnumber=TT.termnumber
   WHERE S2.registrationnumber = S.registrationnumber AND S2.person_id = PS.id
) THEN 'ZW' ELSE 'VS' END AS extart -- VS = Vorsemester an anderer Hochschule ZW = Zweithochschule in diesem Semester
,  case when DP.term_year not between 1900 and 2999 then null else '' || DP.term_year || TT.termnumber end AS sem_rueck_beur_ein
, CASE WHEN C.uniquename = 'D' THEN 'I' ELSE 'A' END AS hskfzkz
, CASE WHEN C.uniquename = 'D' THEN U.vehicle_registration_plate ELSE C.uniquename END AS hskfz
, U.astat AS hs
, U.universitytype AS hsart
,case when DP.studynumber >9 then 1 else DP.studynumber end AS studiengang_nr
,DP.subjectnumber AS fach_nr
,DE.astat_land AS ang_abschl
,SU.astat_land AS fach
,DP.number_of_studysemester::INT AS fachsem_zahl
,C.astat AS country_astat
,D.k_form_of_studies_id
FROM studentexternal SE
JOIN student S ON SE.person_id = S.person_id AND S.registrationnumber IS NOT NULL
JOIN superx_sos SS on SS.id=S.id
JOIN degree_program_before D ON D.studentexternal_id = SE.id
JOIN degree_program_progress_before DP ON DP.degree_program_before_id = D.id
JOIN subject SU ON SU.lid = DP.subject_lid AND SU.valid_to = (SELECT max(s.valid_to) FROM subject s WHERE s.lid = SU.lid)
JOIN degree DE ON DE.lid = D.degree_lid AND DE.valid_to = (SELECT max(d.valid_to) FROM degree d WHERE d.lid=DE.lid)
LEFT JOIN term_type TT ON DP.term_type_id = TT.id
LEFT JOIN university U ON SE.university_lid = U.lid AND U.valid_to = (SELECT max(uni.valid_to) FROM university uni WHERE uni.lid = U.lid)
LEFT JOIN country C ON C.lid = SE.country_lid

;

select * from k_stg 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.period_id=P.id
and DP.studynumber=1
and DP.subjectnumber=1
and D.belongs_to='STUDENT'
AND 0 < (
    SELECT
      count(*)
    FROM
      orgrole O,
      role R
    WHERE
      R.id = O.role_id
      AND D.person_id = O.person_id
      AND R.hiskey_id IN (5, 8))
  --nicht Rolle "Gelöscht" (hiskey_id 3000)
  AND 0 = (
    SELECT
      count(*)
    FROM
      orgrole O,
      role R
    WHERE
      R.id = O.role_id
      AND D.person_id = O.person_id
      AND R.hiskey_id IN (3000)
    )
group by 1
order by 1
;

select * from k_stg where 1=0;

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 (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 (select distinct pnr from tmp_hskonst)
group by lab.psem
;

SELECT 'lab'::char(10),'' || L.term_year || L.termnumber,--psem,
    count(*)
FROM tmp_lab L,
   unit U
  where L.unit_id=U.id
--and U.official_statistics in (1,2)
AND (to_number('' || L.term_year || L.termnumber,'99999') ) >= $start_pruef_sem
group by 1,2
;

select * from k_stg where 1=0;

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 * from k_stg 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 * from k_stg where 1=0;

   SELECT distinctrow hsnr, bland, hsbez,hsort,hsstrasse,hsplz,hspostfach FROM hskonst;

SELECT distinct hsnr, bland, hsbez,hsort,hsstrasse,hsplz,hspostfach FROM hskonst;
   

SELECT distinct hsnr, bland, hsbez,hsort,hsstrasse,hsplz,hspostfach FROM hskonst;
   

SELECT DISTINCT ou.astat,
                (case when $SOS_BLAND is NULL or trim($SOS_BLAND)=''
                      then SUBSTRING(kp.astat FROM 1 FOR 2) 
                      else $SOS_BLAND
                      end) as land, 
                 ou.defaulttext, a.city, a.street, a.postcode, a.postofficebox
FROM ( -- Höchste aktive Konfigurationaquelle mit aktivem Schlüssel
       -- core.psv.self.own_university
       SELECT cv.parameter_value AS orgunit_obj_guid,
              cs.uniquename AS confsrc_uniquename -- Zur Information
         FROM confparameter cp
              JOIN confvalue cv ON cp.id=cv.confparameter_id
              JOIN confsrc cs ON cv.confsrc_id=cs.id
        WHERE cp.confkey='core.psv.self.own_university' AND
              cp.active=1 AND
              cs.active=1
        ORDER BY cs.priority DESC
        LIMIT 1
     ) i
     JOIN orgunitobj ouo ON i.orgunit_obj_guid=ouo.obj_guid
     JOIN orgunit ou ON ouo.id=ou.lid
     JOIN address a ON ou.lid=a.orgunit_lid
     JOIN k_postcode kp USING(postcode)
WHERE CURRENT_DATE BETWEEN ou.valid_from AND ou.valid_to AND
     a.addresstype='Postaddress' ;

select * from k_stg where 1=0;

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

SELECT distinct aktsem,aktsem, sembg,semende,stistat
FROM sossys where aktsem is not null;
   

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 where aktsem is not null;
   

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
-- Semester nur entladen wenn es keine Trimester gibt
and T.termcategory=2 --Semester
and 0=(select count(*) 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=3)
union
--Trimester werden entladen wenn es mind. eines gibt
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=3 --Trimester
and 0<(select count(*) 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=3)
;
   

SELECT distinct aktsem,
aktsem, 
'01.10.' || substr('' || aktsem,1,4) as sem_beginn,
'31.03.' || substr('' || (aktsem+9),1,4) as semende,
stistat
FROM sosys where aktsem >= $start_stud_sem
and '' || aktsem like '%2'
union
SELECT distinct aktsem,
aktsem, 
'01.04.' || substr('' || aktsem,1,4) as sem_beginn,
'30.09.' || substr('' || aktsem,1,4) as semende,
stistat
FROM sosys where aktsem >= $start_stud_sem
and '' || aktsem like '%1';

SELECT
    12,
0,
   '' || astat,
   ktxt,
   dtxt,
   ltxt,
null::char(1),
null::char(1),
'' || astat as uniquename,
null::char(1),
null::char(1)
   FROM k_akfz
   WHERE akfz is not null
   and astat is not null
   and aikz="A"
   and (sprache ="D" or sprache is null)
union
  SELECT
9010,
-1,
   '' || vpnr,"VD","Vorprüfung","Vorprüfung",
'' || vpnr,
null::char(1),
'' || vpnr  as uniquename,
null::char(1),
null::char(1)
FROM hskonst
union
SELECT
   9010,
-1,
   '' || hpnr,"HD","Hauptprüfung","Hauptprüfung",
'' || hpnr,
null::char(1),
'' || hpnr  as uniquename,
null::char(1),
null::char(1)
FROM hskonst

union
SELECT
   9003,
0,
   astat,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
'' || astat as uniquename,
null::char(1),
null::char(1)
   FROM k_geschl
   WHERE astat is not null
 union
SELECT
   617,
-1,
   '' || semgewicht,
   ktxt,
   dtxt,
   ltxt,
'' || semgewicht,
null::char(1),
'' || semgewicht  as uniquename,
null::char(1),
null::char(1)
   FROM k_semgewicht

 union
SELECT
   631,
-1,
   '' || pnr,
   ktxt,
   dtxt,
   ltxt,
'' ||  pnr,
null::char(1),
'' || pnr  as uniquename,
null::char(1),
null::char(1)
   FROM k_pnr
   where pnr is not null
   union
SELECT
   632,
-1,
   '' || refpvers,
   ktxt,
   dtxt,
   ltxt,
'' ||  pvers,
'' || refpvers  as uniquename,
null::char(1) ,
null::char(1),
sprache
   FROM k_pvers
   where refpvers is not null;

SELECT
    12,
0,
   '' || astat,
   ktxt,
   dtxt,
   ltxt,
null::char(1),
null::char(1),
'' || astat as uniquename,
null::char(1),
null::char(1)
   FROM k_akfz
   WHERE akfz is not null
   and astat is not null
   and aikz='A'
   and (sprache ='D' or sprache is null)
union
SELECT
   9010,
-1,
   '' || vpnr,'VD','Vorprüfung','Vorprüfung',
'' || vpnr,
null::char(1),
'' || vpnr  as uniquename,
null::char(1),
null::char(1)
FROM hskonst
union
SELECT
   9010,
   -1,
   '' || hpnr,'HD','Hauptprüfung','Hauptprüfung',
   '' || hpnr,
   null::char(1),
   '' || hpnr  as uniquename,
   null::char(1),
   null::char(1)
FROM hskonst

union
SELECT
   9003,
   0,
   astat,
   ktxt,
   dtxt,
   ltxt,
   astat,
   null::char(1),
   '' || astat as uniquename,
   null::char(1),
   null::char(1)
   FROM k_geschl
   WHERE astat is not null
 union
SELECT
   617,
   -1,
   '' || semgewicht,
   ktxt,
   dtxt,
   ltxt,
   '' || semgewicht,
   null::char(1),
   '' || semgewicht  as uniquename,
   null::char(1),
   null::char(1)
   FROM k_semgewicht

 union
SELECT
   631,
   -1,
   '' || pnr,
   ktxt,
   dtxt,
   ltxt,
   '' ||  pnr,
   null::char(1),
   '' || pnr  as uniquename,
   null::char(1),
   null::char(1)
   FROM k_pnr
   where pnr is not null
  union
SELECT
   632,
-1,
   '' || refpvers,
   ktxt,
   dtxt,
   ltxt,
'' ||  pvers,
'' || refpvers  as uniquename,
null::char(1) ,
null::char(1),
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
null::char(1),--parent
substring(uniquename from 1 for 255),
null::char(1),--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,
   '2','VD','Vorprüfung',
   'Vorprüfung',
 null::char(1) , --astat
 null::char(1), --parent
 '2' as uniquename,
 null::char(1), --bund_apnr
 null::char(1) --sprache
FROM tmp_xdummy
union
SELECT
   9010,
-1,
   '1','HD','Hauptprüfung',
   'Hauptprüfung',
null::char(1) , --astat
null::char(1), --parent
'1' as uniquename, 
null::char(1), --bund_apnr
null::char(1) --sprache
FROM tmp_xdummy
union
SELECT
   9010,
-1,
   '3','AA','Abschlussarbeit',
   'Abschlussarbeit',
null::char(1) , --astat
null::char(1), --parent
'3' as uniquename,
null::char(1), --bund_apnr
null::char(1) --sprache
FROM tmp_xdummy
union
SELECT
   9010,
-1,
   '11','HG','Hauptprüfung',
   'Hauptprüfung',
null::char(1) , --astat
null::char(1), --parent
'11'  as uniquename, 
null::char(1), --bund_apnr
null::char(1) --sprache
FROM tmp_xdummy
union
SELECT
   9010,
-1,
   '12','HF','Hauptprüfung',
   'Hauptprüfung',
null::char(1) , --astat
null::char(1), --parent
'12'  as uniquename, 
null::char(1), --bund_apnr
null::char(1) --sprache
FROM tmp_xdummy
union
SELECT
   9010,
-1,
   '14','HS','Hauptprüfung',
   'Hauptprüfung',
null::char(1) , --astat
null::char(1), --parent
'14'  as uniquename, 
null::char(1), --bund_apnr
null::char(1) --sprache
FROM tmp_xdummy
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,
    null::char(1),
   substring('' || astat from 1 for 255)  as uniquename,
   null::char(1),
   null::char(1)
   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),
   null::char(1) , --astat
   null::char(1),
   substring('' || uniquename from 1 for 255),
   null::char(1),
   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),
'' || hiskey_id , --astat
null::char(1), --parent
  substring('' || uniquename from 1 for 255),
 null::char(1),--bund_apnr
substring('' || k_language_id from 1 for 3)--sprache
   FROM  k_examinationversion;

SELECT
   631,
   -1,
   '' || nr,
   substr(kurzbezeichnung, 1 , 10),
   substr(name, 1 , 100),
   substr(name, 1 , 150),
   null , --astat
   null,
   '' || nr , --uniquename
   null,
   '' --sprache
   FROM pv_dp_typen
   

    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,
null::char(1),--parent
refabint,--sortc1 wird später uniquename
null::char(1),
sprache,
null::char(1), --gültig von
null::char(1), --gültig bis
refabint, --sourcesystem_id
null::char(1), --hiskey_id
aikz,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),--parent
refstg, --sortc1  wird später uniquename
null::char(1),--bund_apnr
sprache,
null::char(1), --gültig von
null::char(1), --gültig bis
refstg , --sourcesystem_id
null::char(1), --hiskey_id
fb,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),
null::char(1),
vert,
null::char(1),
sprache,
null::char(1), --gültig von
null::char(1), --gültig bis
refvert , --sourcesystem_id
null::char(1), --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),
null::char(1),
schwp,
null::char(1),
sprache ,
null::char(1), --gültig von
null::char(1), --gültig bis
schwp, --sourcesystem_id
null::char(1), --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder

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

   union

   SELECT
   40,
-1,
   stutyp,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
stutyp ,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
stutyp , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_stutyp
union
SELECT
   11,
-1,
   ikfz,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
ikfz,
bland,
null::char(1) ,
null::char(1), --gültig von
null::char(1), --gültig bis
ikfz , --sourcesystem_id
null::char(1) , --hiskey_id
aikz,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_ikfz
   WHERE astat is not null
   --and aikz ='A'
 union
SELECT
   12,
0,
   akfz,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
akfz,--uniqename
erdteil,
null::char(1) ,
null::char(1), --gültig von
null::char(1), --gültig bis
akfz , --sourcesystem_id
null::char(1) , --hiskey_id
egmitgl,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),
akfz,--bei staaten ist astat=uniqename
erdteil,
null::char(1) ,
null::char(1), --gültig von
'01.01.2009', --gültig bis
akfz , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),
bland,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
bland , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_bland
   WHERE bland is not null
   union
   SELECT
   9011,
0,
   ppruefer,
   kname,
   nachname  ,
  nachname || ', '|| vorname ,
null::char(1),
null::char(1),
ppruefer,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
ppruefer , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   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,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
sperre , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_sperre
   WHERE sperre is not null
   and aikz='A'
   union
SELECT
   9001,
0,
   status,
   ktxt,
   dtxt,
   ltxt ,
astat,
null::char(1),
status,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
status , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_status
   WHERE status is not null
   and aikz='A'
    union
SELECT
   9013,
0,
   modulart,
   ktxt,
   dtxt  ,
  ltxt ,
null::char(1),
null::char(1),
modulart,
null::char(1),
sprache,
null::char(1), --gültig von
null::char(1), --gültig bis
modulart , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_modulart
   WHERE modulart is not null
   and aikz='A'
   union

   SELECT
   90,
-1,
   reffb,
   ktxt,
   dtxt,
   ltxt,
null::char(1),
null::char(1),
fb ,
null::char(1),
sprache,
null::char(1), --gültig von
null::char(1), --gültig bis
reffb , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_fb
   where aikz='A'
   and (sprache ='D' or sprache is null)
 union
   SELECT
   	9012,
	-1,
   	stort,
   	ktxt,
   	dtxt,
   	ltxt,
	astat,
	null::char(1),
	stort ,
	null::char(1),
	null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
stort , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_stort
    where aikz='A'

   union
   SELECT
   619,
-1,
   lehreinh,
   ktxt,
   dtxt,
   ltxt,
null::char(1),
null::char(1),
lehreinh ,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
lehreinh , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_le
   where lehreinh is not null
   and trim(lehreinh)!=''
   union
   SELECT
   27,
-1,
   gdbu,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
gdbu ,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
gdbu , --sourcesystem_id
his_gdbu , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_gdbu
   where gdbu is not null
   --and aikz='A'
   and trim(gdbu)!=''
   union
   SELECT
   62,
-1,
   gdex,
   ktxt,
   dtxt,
   ltxt,
astat,
null::char(1),
gdex ,
null::char(1),
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
gdex , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_gdex
   where gdex is not null
   --and aikz='A'
   and trim(gdex)!=''
   union
       SELECT
   624,
-1,
   pvermerk,
   ktxt,
   dtxt  ,
  ltxt ,
astat,
null::char(1) ,
pvermerk,
null::char(1) ,
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
pvermerk , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_pverme
   WHERE pvermerk is not null
   and aikz='A'
   and trim(pvermerk) != ''
union
       SELECT
   9002,
-1,
   part,
   ktxt,
   dtxt  ,
  ltxt ,
astat,
null::char(1) ,
part,
null::char(1) ,
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
part , --sourcesystem_id
prfgkz , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_part
   WHERE part is not null
union
       SELECT
   618,
-1,
   abext,
   ktxt,
   dtxt  ,
  ltxt ,
astat,
null::char(1) ,
abext,
null::char(1) ,
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
abext , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_abext
   WHERE abext is not null
union
       SELECT
   623,
-1,
   art,
   substring(dtxt from 1 for 10),
   dtxt  ,
  artmind ,
astat,
null::char(1) ,
art,
null::char(1) ,
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
art , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1) ,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_minder
   WHERE art is not null
   and aikz='A'
union
       SELECT
   9014,
-1,
   refpform,
   substring(dtxt from 1 for 10),
   dtxt  ,
  null::char(1) ,
null::char(1),
null::char(1) ,
null::char(1),
null::char(1) ,
null::char(1),
null::char(1), --gültig von
null::char(1), --gültig bis
refpform , --sourcesystem_id
null::char(1) , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
null::integer as sortorder
   FROM k_pform
   WHERE refpform is not null
   and aikz='A'
   ;

   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),
substring(astat_land from 1 for 10),--astat,
null::char(1),--parent
uniquename,
substring(astat_bund from 1 for 10), --bund_apnr
substring('' || k_language_id from 1 for 3), --sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   FROM degree
   where (hiskey_id !=2 or hiskey_id is null) --keine externen Abschlüsse
    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),
substring(S.astat_land from 1 for 10) as astat,
min('' || F1.uniquename) as parent, --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
--min('' || S.k_field_of_study_id) as hiskey_id, --hiskey_id
min('' || F2.uniquename) as hiskey_id, --hiskey_id
--min('' || F2.uniquename) as intern_c, --intern_c fuer FächerGruppe
min('' || C.orgunit_id) as struktur_c, --struktur_c ist FB, keine Duplikate erlaubt
S.lid::char(255) as lid,
null::char(255) as parent_lid,
S.sortorder
  FROM subject S 
    LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=1) F1 ON
  (F1.id=S.k_field_of_study_id)
    LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=4) F2 ON
  (F1.parent_id=F2.id)
, tmp_course_of_study C
where S.id=C.subject_id 
and S.hiskey_id=1 --nur interne Fächer
group by 1,2,3,4,5,6,7,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),
null::char(1) as astat,
null::char(1),--parent
uniquename,
null::char(1) ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
null::char(1), --hiskey_id
null::char(1),  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   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,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
null::char(1), --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   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,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    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
null::char(1),--parent
uniquename,
null::char(1), --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,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM k_subject_indicator
   union
   --Studierendenstatus
   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,
null::char(1), --parent
uniquename,
 null::char(1) ,--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,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
 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,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
null::char(1)|| hiskey_id,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_type_of_study
    union
       -- k_astgrp:
 SELECT
     621,
 -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
 uniquename,
 '', --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
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
     FROM k_field_of_study
     where hiskey_id=4
     
     
    union
--k_field_of_study - Fachrichtungen (Gasthoerer)
     SELECT
    620,
 0,
   '' || id, -- ,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
astat_guest_auditor as astat,
 '',--parent
uniquename,
astat_guest_auditor ,--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 , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_field_of_study WHERE hiskey_id=1


     
    union
       -- pvermerk:
    SELECT
    624,
-1,
   '' || id,
   substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),--substring('' || astat from 1 for 10),--astat,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_remark_on_exam
    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,
null::char(1),--parent
uniquename,
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' || hiskey_id,
null::char(1) ,  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_enrollment
 union
 --district:
 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,
null::char(1), --parent
uniquename,
substring(astat from 1 for 2), -- (bund_apnr) hier StBa-Key für Bundesland aus astat,
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
''|| lid , --hiskey_id
'I'  ,  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM district
where valid_to < current_date
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,
null::char(1), --parent
uniquename,
substring(astat from 1 for 2), -- (bund_apnr) hier StBa-Key für Bundesland aus astat,
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
''|| lid , --hiskey_id
'A' ,  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM district
where (valid_to >= current_date or valid_to is null)
 union
 --country:
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,
null::char(1),--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
'' || lid, --hiskey_id
'I',  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   FROM country
   where valid_to < current_date
   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,
null::char(1),--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
'' || lid, --hiskey_id
'A' ,  --struktur_c
lid::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   FROM country
   where (valid_to >= current_date or valid_to is null)
  union
  --k_federal_state:
    SELECT
    8,
 -1,
--     uniquename,
(CASE WHEN uniquename='HA'  THEN 'HH' WHEN uniquename='BR'  THEN 'HB' 
      WHEN uniquename='BA'  THEN 'BY' WHEN uniquename='SA'  THEN 'SL'
      ELSE uniquename 
 END),
   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,
null::char(1),--parent
--uniquename,
(CASE WHEN uniquename='HA'  THEN 'HH' WHEN uniquename='BR'  THEN 'HB' 
      WHEN uniquename='BA'  THEN 'BY' WHEN uniquename='SA'  THEN 'SL'
      ELSE uniquename
 END),
null::char(1), --bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
null::date, --gueltig von
null::date, --gueltig bis
id , --sourcesystem_id
'' ,--hiskey_id,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_federal_state
    WHERE uniquename  not in ('99','BO') --ohne Ausland und Berlin Ost
   union
--k_studystatus:
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,
null::char(1), --parent
uniquename,
 null::char(1) ,--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,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_studystatus
     union
--k_elementtype:
SELECT
   9013,
 0,
   '' || id, -- modulart,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1) , -- astat,
null::char(1), --parent
substring(uniquename from 1 for 10),--uniquename
 null::char(1) ,--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 , --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM k_elementtype
union
--Fachbereich:
   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
O.uniquename,
null::char(1), --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,
null::char(1),  --struktur_c
O.lid::char(255) as lid,
O.parent_lid::char(255) as parent_lid,
O.sortorder
   FROM orgunit O,k_orgunittype T
   where O.k_orgunittype_id=T.id
   and T.hiskey_id=4 --Fachbereich
union
--Lehreinheit:
   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
null::char(1), --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,
null::char(1),  --struktur_c
O.lid::char(255) as lid,
O.parent_lid::char(255) as parent_lid,
O.sortorder
   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,
null::char(1),--parent
uniquename,
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
null::char(1), --hiskey_id
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   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),
	null::char(1),
null::char(1),--parent
uniquename,
null::char(1), --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,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
   FROM k_workstatus

 union
  --Beurlaubungsgründe
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,
null::char(1), --parent
uniquename,
 null::char(1) ,--bund_apnr
substring('' || k_language_id from 1 for 3),--sprache,
valid_from, --gueltig von
valid_to, --gueltig bis
id , --sourcesystem_id
null::char(1) , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_reason_of_leave

union

SELECT -- Exmatrikulationsgründe
  62,
  0,
  '' || S.id, -- gdbu,
  substring(S.shorttext from 1 for 10),
  substring(S.defaulttext from 1 for 100),
  substring(S.longtext from 1 for 150),
  substring(S.astat from 1 for 10), -- astat,
  '', --parent
  S.uniquename,
  substring(S.astat from 1 for 10), --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
  '' || S.hiskey_id, -- hiskey,
  '',  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM k_reason_of_finishing S

union
--k_examination_type
SELECT
  9002,
  0,
  '' || id, -- ,
  substring(shorttext from 1 for 10),
  substring(defaulttext from 1 for 100),
  substring(longtext from 1 for 150),
  null::char(1),-- astat,
  null::char(1), --parent
  uniquename,
  null::char(1) ,--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 , -- hiskey,
  null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
FROM k_examination_type

union
--k_examform
SELECT
    9014,
 0,
   '' || id, -- ,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),-- astat,
null::char(1), --parent
uniquename,
 null::char(1) ,--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 , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_examform

    union
--course_specialization
SELECT
    41,
 0,
   '' || id, -- ,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),-- astat,
null::char(1), --parent
uniquename,
 null::char(1) ,--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 , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM course_specialization

    union
--k_accreditationtype
    SELECT
    9005,
 0,
   '' || id, -- ,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
null::char(1),-- astat,
null::char(1), --parent
uniquename,
 null::char(1) ,--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 , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_accreditationtype

    union
--k_field_of_study - Studienbereiche
     SELECT
    9006,
 0,
   '' || id, -- ,
substring(shorttext from 1 for 10),
   substring(defaulttext from 1 for 100),
   substring(longtext from 1 for 150),
 astat,
parent_id::char(10) as parent,
uniquename,
astat_guest_auditor ,--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 , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
sortorder
    FROM k_field_of_study

    union
--Pruefungsamtnummer tmp_course_of_study.examination_office_no
    SELECT DISTINCT
    9008,
 0,
   '' || examination_office_no, -- ,
substring(examination_office_no::varchar from 1 for 10),
   substring(examination_office_no::varchar from 1 for 100),
   substring(examination_office_no::varchar from 1 for 150),
 '',
null::char(1), --parent
examination_office_no, --uniquename
'' ,--bund_apnr
'',--sprache,
date('01.01.1900'), --gueltig von
date('01.01.2900'), --gueltig bis
examination_office_no::int  , --sourcesystem_id
'' , -- hiskey,
null::char(1),  --struktur_c
null::char(255) as lid,
null::char(255) as parent_lid,
0
    FROM tmp_course_of_study
        ;

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

   35,
-1,
   abint,
   abint,--ktxt,
   dtxt,
   ltxt,
astat,
null as parent,
abint,--sortc1 wird später uniquename
' ',
null as sprache,
null as gueltig_von,
null as gueltig_bis,
abint, --sourcesystem_id
' ', --hiskey_id
aikz, --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_abint
   WHERE abint is not null

    union
SELECT
   30,
-1,
   stg,
   stg as ktxt,
   dtxt,
   ltxt,
astat,
null as parent,
stg, --sortc1  wird später uniquename
null as bund_apnr,
null as sprache,
null as gueltig_von,
null as gueltig_bis,
stg , --sourcesystem_id
' ', --hiskey_id
fb , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_stg
   WHERE stg is not null

 union
    SELECT
    601,
 -1,
    cast(hzbart as Char(10)) as hzbart,
    cast(hzbart as Char(10)) as ktxt,
    dtxt,
    ltxt,
 astat,
 '',
 cast(hzbart as Char(10)) as uniquename,
 '',
 '',
null as gueltig_von,
null as gueltig_bis,
 cast(hzbart as Char(10)) as sourcesystem_id,
 '', --hiskey_id
 '' , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
     FROM k_hzbart
    union
    SELECT
    612,
 -1,
    stufrm,
    stufrm as ktxt,
    dtxt,
    ltxt,
 astat,
 '',
 stufrm ,
 '',
 '',
null as gueltig_von,
null as gueltig_bis,
 stufrm , --sourcesystem_id
 '', --hiskey_id
 '' , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
    FROM k_stufrm
   union
   SELECT
   613,
-1,
   hrst,
   hrst as ktxt,
   dtxt,
   ltxt,
astat,
null as his_hrst,
hrst ,
' ',
' ',
null as gueltig_von,
null as gueltig_bis,
hrst, --sourcesystem_id
' ' , --hiskey_id
' ' , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_hrst
-- union
   -- SELECT
   	-- 622,
	-- -1,
   	-- pstatus,
   	-- pstatus as ktxt,
   	-- dtxt,
   	-- ltxt,
	-- '',
	-- '',
	-- pstatus ,
	-- '',
	-- '',
-- '', --gültig von
-- '', --gültig bis
-- pstatus , --sourcesystem_id
-- '' , --hiskey_id
-- '' , --struktur_c
--null as lid,
--null as parent_lid,
--null as sortorder
   -- FROM k_pstatus
 union
 SELECT
    11,
 -1,
    ikfz,
    ktxt,
    dtxt,
    ltxt,
 astat,
 '',
 ikfz,
 bland,
 '' ,
null as gueltig_von,
null as gueltig_bis,
 ikfz , --sourcesystem_id
 '' , --hiskey_id
 aikz , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
    FROM k_ikfz
 union
 SELECT
    12,
 0,
    akfz,
    akfz as ktxt,
    dtxt,
    ltxt,
 astat,
 '',
 akfz,--uniqename
 erdteil,
 '' ,
null as gueltig_von,
null as gueltig_bis,
 akfz , --sourcesystem_id
 '' , --hiskey_id
 '' ,--egmitgl struktur_c
null as lid,
null as parent_lid,
null as sortorder
    FROM k_akfz
    WHERE akfz is not null
    --and aikz='A'
   -- union
-- SELECT
   -- 12,
-- 0,
   -- akfz,
   -- akfz as ktxt,
   -- dtxt,
   -- ltxt,
-- astat,
-- '',
-- akfz,--bei staaten ist astat=uniqename
-- erdteil,
-- '' ,
--null as gueltig_von,
-- '01.01.2009', --gültig bis
-- akfz , --sourcesystem_id
-- '' , --hiskey_id
-- '' --struktur_c
   -- FROM k_akfz
   -- WHERE akfz is not null
   -- and (aikz='I' or aikz is null)
    -- union
-- SELECT
   -- 8,
-- 0,
   -- bland,
   -- bland as ktxt,
   -- dtxt,
   -- ltxt,
-- astat,
-- '',
-- bland,
-- '',
-- '',
--null as gueltig_von,
--null as gueltig_bis,
-- bland , --sourcesystem_id
-- '' , --hiskey_id
-- '' --struktur_c
   -- FROM k_bland
   -- WHERE bland is not null
   -- union
   -- SELECT
   -- 9011,
-- 0,
   -- ppruef, --Achtung: in CO Typ char(18)
   -- kname,
   -- nachname  ,
  -- nachname || ', '|| vorname ,
-- '',
-- '',
-- ppruef,
-- '',
-- '',
--null as gueltig_von,
--null as gueltig_bis,
-- ppruef , --sourcesystem_id
-- '' , --hiskey_id
-- '' --struktur_c
   -- FROM k_ppruef
   -- WHERE ppruef is not null
    union
SELECT
   9001,
0,
   stathissos,--status,
   stathissos as ktxt,
   dtxt,
   ltxt ,
astat,
' ',
stathissos,--status,
' ',
' ',
null as gueltig_von,
null as gueltig_bis,
stathissos, --sourcesystem_id
' ' , --hiskey_id
' ' , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_status
   WHERE status is not null
   union
 SELECT
   90,
-1,
'' || O.fb, --   reffb,
'',--   ktxt,
 O.dtxt,
O.ltxt,
'',
'',
''  ,
'',
'',--sprache,
null as gueltig_von,
null as gueltig_bis,
'' || O.fb , --sourcesystem_id
'', --hiskey_id
''  , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_fb O
   where O.fb in (select distinct fb from k_abstgv)
--   where aikz='A'
--   and (sprache ='D' or sprache is null)

--   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,
   -- gdbu as ktxt,
   -- dtxt,
   -- ltxt,
-- astat,
-- '',
-- gdbu ,
-- '',
-- '',
--null as gueltig_von,
--null as gueltig_bis,
-- gdbu , --sourcesystem_id
-- '', --his_gdbu , --hiskey_id
-- '' --struktur_c
   -- FROM k_gdbu
   -- where gdbu is not null
   --and aikz='A'
   union
   SELECT
   62,
-1,
   gdex,
   gdex as ktxt,
   dtxt,
   ltxt,
astat,
' ',
gdex ,
' ',
' ',
null as gueltig_von,
null as gueltig_bis,
gdex , --sourcesystem_id
' ' , --hiskey_id
' ' , --struktur_c
null as lid,
null as parent_lid,
null as sortorder
   FROM k_gdex
   where gdex is not null
    ;

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

    SELECT * from tmp_xdummy
where 1=0;

SELECT  * --wird oben bei cifx entladen, und in trans_k_stg.sql gefüllt
FROM subject S
where 1=0;

select * from k_stg 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  * --wird oben bei cifx entladen, und in trans_k_stg.sql gefüllt
FROM subject S
where 1=0;

SELECT stg,
       aikz,
       stg,
       dtxt,
       ltxt,
       astat,
       fb,
null as astfr,
null as astgrp,
null as zulplaugr,
null as kzfaarray,
null as sortkz1,
null as sprache,
stg --refstg
FROM k_stg
where stg is not null
    ;

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

    SELECT * from tmp_xdummy
where 1=0;

SELECT stgext,
       aikz,
       stgext as ktxt,
       dtxt,
       ltxt,
       astat,
       null as astfr,
       ' '  --astgrp
       FROM k_stgext;

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,
       null::char(1) as efh,
       null::char(1) as regelstz,
       null::char(1) as email_part,
       null::char(1) as login_part,
       null::char(1) as sem_gueltigbis,
       guthaben_ects,
       null::char(255) as k_stg_dtxt   -- subject_defaulttext
       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,
       guthaben_ects,
       null::char(255) as k_stg_dtxt   -- subject_defaulttext
       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,
   null::char(1),--    frist1,
   null::char(1),--    frist2,
   null::char(1),--    frist3,
   null::char(1),--    frist3_alt,
   null::char(1),--    frist4,
   F3.uniquename,--    astat1 (#222047, Studienbereich aus Fach)
   F1.uniquename,--     astat2 (#222047, Studienbereich aus Studiengang),
   F2.uniquename,--    astgrp (#222047),
   null::char(1),--    prfgamt,
   null::char(1),--    vglgrp,
    C.orgunit_id,--       fb,
    C.k_enrollment_id,--   stutyp,
    C.k_subject_indicator_id,--   kzfa,
    null::char(1),--    immsemkz,
    null::char(1),--    kzfaarray,
    C.k_place_of_studies_id,--   stort,
    C.teachingunit_orgunit_id,--  lehreinh,
    null::char(1),--    stkkurs1,
    null::char(1),--    stkkurs2,
    null::char(1),--    stkkurs3,
    null::char(1),--    prax1fsem,
    null::char(1),--    prax2fsem,
    C.degree_id,--   abschl,
     C.subject_id,--  stg,
     C.major_field_of_study_id,--  vert,
     C.course_specialization_id,--  schwp,
    null::char(1),--    stuart,
     C.k_form_of_studies_id,--  stufrm,
     null::char(1),--   abstitel,
     C.id,--  abstgvnr,
     null::char(1),--   efh,
     C.regular_number_of_semesters,--  regelstz,
     null::char(1),--    email_part,
     null::char(1),--   login_part,
     '' || C.to_term_year || T.termnumber, --  sem_gueltigbis
     part_of_studies,
     C.subject_defaulttext
  FROM tmp_course_of_study C
    left outer join term_type        T on (C.to_term_type_id=T.id)
    LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=1) F1 ON
  (F1.id=C.k_field_of_study_cos_id)
    LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=4) F2 ON
  (F1.parent_id=F2.id)
    LEFT OUTER JOIN (select * from k_field_of_study where hiskey_id=1) F3 ON
  (F3.id=C.k_field_of_study_subj_id)
;

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,
       '' , -- ects
       ''   -- subject_defaulttext
       FROM k_abstgv;

SELECT abschl,
       stg,
       vert,
       schwp,
       kzfa,
       pversion,
       pnr,
       pabschn,
       pteil,
       pdum,
       pausw1,
       pausw2,
       pausw3,
       pktxt,
       pdtxt,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt1,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt2,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt3,
       null::char(1), --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,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert :beleg,
       null::char(1), --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,
       null::char(1) --schriftlich
FROM pord;

   SELECT
null::char(1),--C.degree_id,--abschl,
null::char(1),--C.subject_id,--stg,
null::char(1),--C.major_field_of_study_id, --vert,
null::char(1),--C.course_specialization_id,--schwp,
null::char(1),--C.k_subject_indicator_id,--kzfa,
null::char(1),--C.k_examinationversion_id,--pversion,
 U.official_statistics,  --pnr
 null::char(1),--pabschn,
 null::char(1),--pteil,
 null::char(1),--pdum,
 null::char(1),--pausw1,
 null::char(1),--pausw2,
 null::char(1),--pausw3,
 U.elementnr,--pktxt,
 substring(U.defaulttext from 1 for 255),--pdtxt,
 null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt1,
 null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt2,
 null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt3,
 null::char(1), --wg. Umbrüchen erstmal auskommentiert : pltxt4,
 null::char(1),--geldat,
 substring(KC.uniquename from 1 for 10),--ppflicht,
 E.k_examform_id,--pform,
 E.k_examination_type_id,--part,
 null::char(1),--pdauer,
 null::char(1),--partngb,
R.recommended_semester::integer,--pfsem,
 null::char(1),--psws,
 null::char(1),--pminsem,
 null::char(1),--phoesem,
 null::char(1),--pmaxvbe,
 null::char(1),--pmaxver,
 null::char(1),--pzuver,
 null::char(1),--pmaxrueck,
 null::char(1),--pwfrist,
 null::char(1),--pfrist1,
 null::char(1),--pfrist2,
 null::char(1),--pfristr,
 null::char(1),--pnhstat3,
 null::char(1),--paenddat,
 null::char(1),--pnrvl1,
 null::char(1),--pnrvl2,
 null::char(1),--pnrvl3,
 null::char(1),--pnrvl4,
 null::char(1),--pnrvl5,
 null::char(1),--pvken1,
 null::char(1),--pvken2,
 null::char(1),--pvken3,
 null::char(1),--pvken4,
 null::char(1),--pvken5,
 null::char(1),--psort1,
 null::char(1),--psort2,
 null::char(1),--psort3,
 null::char(1),--pnra,
 null::char(1),--petgp,
 null::char(1),--pmerg,
 null::char(1),--pbedv,
 null::char(1),--pbedg,
 null::char(1),--pbedw,
 null::char(1),--vordipl,
 null::char(1),--pfnrex,
 null::char(1),--pbetrag,
 null::char(1),--panmkenn,
 null::char(1),--freivers,
 null::char(1),--pbetragz1,
 null::char(1),--pbetragz2,
 U.id,--pordnr,
R.bonus,--bonus,
 null::char(1),--malus,
 null::char(1),--bogverb,
 null::char(1),--bogempf,
 null::char(1),--mogverb,
 null::char(1),--mogempf,
 null::char(1),--pnotpkt,
 null::char(1),--fb,
 null::char(1),--nacharb,
  substring('' || U.official_statistics from 1 for 1),--meldekz,
 null::char(1),--sivabschl,
 null::char(1),--sivstg,
 null::char(1),--sivvert,
 null::char(1),--sivschwp,
 null::char(1),--sivkzfa,
 null::char(1),--sivpversion,
 null::char(1),--zmadauer,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert :beleg,
       null::char(1), --wg. Umbrüchen erstmal auskommentiert :diplkz,
 null::char(1),--bonus_bei_nb,
 null::char(1),--steuer_wvl,
 null::char(1),--bendauer,
 null::char(1),--disdauer,
 null::char(1),--diszmadauer,
 null::char(1),--instnr,
 null::char(1),--beschkat,
 null::char(1),--labgewicht,
 null::char(1),--standbearb,
 null::char(1),--modulturnus,
 null::char(1),--praesenzzeit,
 null::char(1),--selbstzeit,
 null::char(1),--workload,
 U.k_elementtype_id,--modulart,
 null::char(1),--modulcode,
 null::char(1),--moduldauer,
 null::char(1),--zmamoduldauer,
 null::char(1),--verart,
 null::char(1), --max_teilnehmer
 E.exam_written
 FROM
   unitrelation R left outer join k_compulsory KC on (R.k_compulsory_id=KC.id),
   unit U left outer join examination E
   on (E.unit_id=U.id)
   where R.id=U.default_unitrelation_id
    ;

select * from k_stg 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,
       case when '$DIPL_THEMA'='true' then substring(thema from 1 for 255) else null::char(10) end as thema,
       case when '$PRUEFER_NAME'='true' then betreu1 else null::char(10) end as betreu1,
       case when '$PRUEFER_NAME'='true' then betreu2 else null::char(10) end as betreu2
FROM dipl, superx_sos
WHERE dipl.mtknr = superx_sos.mtknr
AND dipl.psem >= $start_pruef_sem
;

    SELECT L.mtknr_ldsg,
null::char(1),--abschl,
null::char(1),--stg,
null::char(1),--vert,
null::char(1),--schwp,
null::char(1),--kzfa,
null::char(1),--pnr,
'' || L.term_year || L.termnumber,--psem,
null::char(1),--pversuch,
null::char(1),--prueck,
null::char(1),--pversion,
W.date_of_application,--antrdat,
null::char(1),--meldat,
null::char(1),--beabeg,
null::char(1),--dauer,
null::char(1),--verlkz,
null::char(1),--abdat,
null::char(1),--tabdat,
null::char(1),--pnoteges,
null::char(1),--zeudat,
L.examplan_id, --labnr
case when '$DIPL_THEMA'='true' then substring(W.title from 1 for 255) else null::char(10) end as thema,
null::char(1), --betreu1
null::char(1) --betreu2
FROM
    tmp_lab L,
    exam_written W
    where W.examplan_id=L.examplan_id
   AND (to_number('' || L.term_year || L.termnumber,'99999') ) >= $start_pruef_sem
;

select * from k_stg 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 * from k_stg where 1=0;

select
 distinct S.mtknr_ldsg,
 R.identnr,
 I.name,
 I.vorname,
 T.tel,
 T.artkz,
 T.anschrkz,
 O.res13,
 null::integer -- person_id
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';

    
-- detaillierte Personendaten
select
S.mtknr_ldsg,
U.id,--identnr (student id)
P.surname, --name
COALESCE(P.firstname,P.surname), --vorname,
'',--tel,
'',--artkz,
'',--anschrkz,
'', -- O.res13
S.person_id --(person_id
FROM student U, superx_sos S, person P, term_type TT
where
U.term_year * 10 + TT.termnumber >= $start_stud_sem
and P.id=U.person_id
and U.id=S.id
and S.person_id=P.id
and U.term_type_id = TT.id
and '$STUD_IDENT'='true'
UNION

-- einfache Personendaten
-- Beispiel 11200321^20^^Wern^^^^^
 select
S.mtknr_ldsg,
U.id, --identnr (student id)
'', --name
SUBSTRING(COALESCE(P.firstname,P.surname) FROM 1 FOR 4), --vorname,
'',--tel,
'',--artkz,
'',--anschrkz,
'', -- O.res13
S.person_id --(person_id
FROM student U, superx_sos S, person P, term_type TT
where
U.term_year * 10 + TT.termnumber >= $start_stud_sem
and P.id=U.person_id
and U.id=S.id
and S.person_id=P.id
and U.term_type_id = TT.id
and '$STUD_IDENT'='false'
;

select
 distinct S.mtknr_ldsg,
 S.mtknr_ldsg,
 O.nachname,
 O.vorname,
 '',--T.tel,
 '',--T.artkz,
 '',--T.anschrkz,
 '',--O.res13,
 S.mtknr_ldsg -- person_id
FROM sos O, superx_sos S
where
O.mtknr=S.mtknr
and '$STUD_IDENT'='true';

SELECT pordnr,
       pktxt,
       pdtxt,
       pltxt1,
       pordsnr,
       sprache
FROM pords

    SELECT * from tmp_xdummy
where 1=0;

select * from k_stg 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;

select * from k_stg where 1=0;

select
superx_sos.mtknr_ldsg,
labzuord.labnr,
labzuord.artzuordnung,
labzuord.pordnrzu,
labzuord.labnrzu,
labzuord.bonus,
labzuord.malus
FROM labzuord,lab,
 superx_sos
WHERE lab.mtknr = superx_sos.mtknr
and labzuord.mtknr = superx_sos.mtknr
and labzuord.labnr=lab.labnr
AND (lab.pstatus is null or lab.pstatus != 'SO')
$LAB_FILTER
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
superx_sos.mtknr_ldsg,
labzuord.labnr,
labzuord.artzuordnung,
labzuord.pordnrzu,
labzuord.labnrzu,
labzuord.bonus,
labzuord.malus
FROM labzuord,lab,
 superx_sos
WHERE lab.mtknr = superx_sos.mtknr
and labzuord.mtknr = superx_sos.mtknr
and labzuord.labnr=lab.labnr
AND (lab.pstatus is null or lab.pstatus != 'SO')
$LAB_FILTER
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
L.mtknr_ldsg,
L.examplan_id as labnr,
K.uniquename as artzuordnung,
null::integer as  pordnrzu,
R.parent_examplan_id as labnrzu,
R.bonus,
R.malus
 FROM
tmp_lab L, examrelation R, k_unitrelationtype K
WHERE
L.examplan_id = R.child_examplan_id
and K.id=R.k_unitrelationtype_id
AND (to_number('' || L.term_year || L.termnumber,'99999') ) >= $start_pruef_sem

select * from k_stg where 1=0;

select
       lfdnr,
       artzuord,
       pordnr,
       pordnrzu,
       bonus,
       malus,
       bez,
       bonus_bei_nb,
       ppflicht,
       freivsem,
       semester
FROM pnrzuord

    select
null::smallint as lfdnr,--R.sortorder
K.uniquename as artzuord,
R.child_unit_id as pordnr,
R.parent_unit_id as pordnrzu,
R.bonus,
R.malus,
null::char(1) as bez,
null::char(1) as bonus_bei_nb,
null::char(1) as ppflicht,
R.firsttrial_semester as freivsem,
R.recommended_semester::integer as semester
FROM unitrelation R, k_unitrelationtype K
where K.id=R.k_unitrelationtype_id

select * from k_stg where 1=0;

select 'nix' FROM pnrzuord where 1=0

   SELECT id,
       defaulttext
FROM student_weighting_variant;

select * from k_stg where 1=0;

select 'nix' FROM pnrzuord where 1=0

  SELECT k_student_weighting_filter_type_id,
       filter_value,
       student_weighting_rule_id FROM student_weighting_filter;

select * from k_stg where 1=0;

select 'nix' FROM pnrzuord where 1=0

 -- die _list Felder bleiben leer, werden später im ETL gefüllt
  SELECT id,
       shorttext,
       student_weighting_variant_id,
       sortorder,
       weight,
       k_student_weighting_weight_usage_type_id,
       k_student_weighting_action_type_id,
       weighting_ident_operator_id,
       ''::char(1) as weighting_ident_list,
       new_weighting_ident,
       degree_counter_operator_id,
         ''::char(1) as degree_counter_list,
       new_degree_counter,
       subject_counter_operator_id,
         ''::char(1) as  subject_counter_list,
       new_subject_counter,
       subject_count_operator_id,
         ''::char(1) as subject_count_list,
       degree_count_operator_id,
         ''::char(1) as degree_count_list,
       ects_operator_id,
         ''::char(1) as ects_list,
       newects,
       teaching_unit_operator_id,
         ''::char(1) as teachning_unit_list,
       new_teaching_unit,
       department_operator_id,
         ''::char(1) as department_list,
       new_department,
       subject_operator_id,
         ''::char(1) as subject_list,
       new_subject,
       degree_operator_id,
         ''::char(1) as  degree_list,
       new_degree,
       subject_indicator_operator_id,
         ''::char(1) as subject_indicator_operator_list,
       new_subject_indicator,
       type_of_study_operator_id,
         ''::char(1) as type_of_study_operator_list,
       new_type_of_study,
       form_of_study_operator_id,
         ''::char(1) as form_of_study_list,
       new_form_of_study
FROM student_weighting_rule

select * from k_stg where 1=0;

drop table superx_sos;

drop table superx_sos;
drop table tmp_xdummy;

drop table superx_sos;
drop table tmp_xdummy;

drop table tmp_hskonst;

drop table tmp_course_of_study;

drop table tmp_hskonst;

select
P.id,
P.person_id,
S.registrationnumber,
7 as systeminfo_id,
P.personattributetype_id,
coalesce(P.attributevalue, (select attributevalue from personattribute_value_list pav where pav.id=P.personattribute_value_list_id) ) attributevalue,
P.valid_from,
P.valid_to,
null::integer ,--semester_von
null::integer --semester_bis
from personattribute P, student S where S.person_id= P.person_id and P.personattributetype_id in ($PERSONATTRIBUTE_IDS) order by person_id;

select * from k_stg where 1=0;

select * from k_stg where 1=0;

select
id,
fieldname
from personattributetype where '$PERSONATTRIBUTE_IDS' != '0';

select * from k_stg where 1=0;

select * from k_stg where 1=0;

select
personattributetype_id,
attributevalue,
shorttext,
defaulttext,
longtext
from personattribute_value_list where '$PERSONATTRIBUTE_IDS' != '0';

select * from k_stg where 1=0;

select * from k_stg where 1=0;

SELECT 7::integer,'stu'::char(40), cast(extract(epoch from (select max(updated_at)))as integer), max(updated_at) from student;

select * from k_stg where 1=0;

select * from k_stg where 1=0;

SELECT 7::integer,'exa'::char(40), cast(extract(epoch from (select max(updated_at)))as integer), max(updated_at) from examplan;

select * from k_stg where 1=0;

select * from k_stg where 1=0;

select
TL.mtknr_ldsg,
AE.examplan_id,
AE.abroad_ects,
AE.professional_experience_ects,
AE.total_accredited_ects,
'' || TL.term_year || TL.termnumber, --psem
extract(day from TL.date_of_work)::varchar(2) || '.' || extract(month from TL.date_of_work)::varchar(2) || '.' || extract(year from TL.date_of_work)::varchar(4) --Prüfungsdatum
from accredited_ects AE, tmp_lab TL where AE.examplan_id = TL.examplan_id;

select
*,
null::integer AS sem_der_pruefung, --psem
null::integer as d_abg_pruefung --Prüfungsdatum
from k_stg where 1=0;

select * from k_stg where 1=0;

DROP TABLE IF EXISTS  tmp_lab;
DROP TABLE IF EXISTS  tmp_studysem;

DROP INDEX IF EXISTS tcos_i1sid;

SELECT U.unit_id as pordnr,
       6 as sourcesystem,
       O.id as institution,
       U.valid_from,
       U.valid_to,
       R.uniquename as art,
       O.sortorder
FROM hisinone.orgunit O, hisinone.unit_orgunit U left outer join hisinone.k_unit_orgunit_relation R
on (R.id=U.k_unit_orgunit_relation_id)
where O.lid=U.orgunit_lid
 ;

select * from k_stg where 1=0;

select * from k_stg where 1=0;

SELECT id as pordnr,
       6 as sourcesystem,
       uniquename,
       elementnr,
       k_elementtype_id,
       defaulttext,
       official_statistics as pnr,
       valid_from,
       valid_to,
       default_unitrelation_id,
       lid,
       shortcomment,
       k_unit_purpose_id
FROM hisinone.unit 
WHERE official_statistics IS NOT NULL;
 

select * from k_stg where 1=0;

select * from k_stg where 1=0;

SELECT 
  registrationnumber,
  surname,
  firstname,
  birthdate,
  archive_number,
  archiving_date,
  person_type,
  obj_guid,
  lock_version
FROM hisinone.student_archive 
;

select * from k_stg where 1=0;

select * from k_stg where 1=0;

SELECT 
  person_id,
  registrationnumber,
  entity_class,
  deletion_time,
  obj_guid,
  lock_version,
  job
FROM hisinone.bi_deleted_person 
WHERE 
  entity_class = 'cm.stu.Student'
  AND registrationnumber IS NOT NULL 
  AND '${STUDENT_ARCHIVE_UNLOAD}' = 'true'
;
 

select * from k_stg where 1=0;

select * from k_stg where 1=0;