create table superx_sva (pgd_join_id integer, pgd_join_id_ldsg integer,pbv_nr integer,sgd_join_id integer,pbe_serial integer); 

create table superx_sva (pgd_join_id integer, pgd_join_id_ldsg integer,pbv_nr integer,sgd_join_id integer,pbe_serial integer); 

create table tmp_pgd_join_id_ldsg(pgd_join_id integer);

create table tmp_pgd_join_id_ldsg(pgd_join_id integer);

insert into tmp_pgd_join_id_ldsg
select S.pgd_join_id from pgd S left outer join pgd_join_id_ldsg L
on (L.pgd_join_id=S.pgd_join_id)
where L.pgd_join_id is null
;

insert into tmp_pgd_join_id_ldsg
select S.pgd_join_id from pgd S left outer join pgd_join_id_ldsg L
on (L.pgd_join_id=S.pgd_join_id)
where L.pgd_join_id is null
;

	   insert into pgd_join_id_ldsg(pgd_join_id)
	   select  distinct pgd_join_id from tmp_pgd_join_id_ldsg
	   where '$ANONYM'='true';

	   insert into pgd_join_id_ldsg(pgd_join_id)
	   select  distinct pgd_join_id from tmp_pgd_join_id_ldsg
	   where '$ANONYM'='true';

	   insert into pgd_join_id_ldsg(pgd_join_id,pgd_join_id_ldsg)
	   select  distinct pgd_join_id,pgd_join_id from tmp_pgd_join_id_ldsg
	   where '$ANONYM'!='true';

	   insert into pgd_join_id_ldsg(pgd_join_id,pgd_join_id_ldsg)
	   select  distinct pgd_join_id,pgd_join_id from tmp_pgd_join_id_ldsg
	   where '$ANONYM'!='true';

	create table tmp_xdummy(i integer);
	
	insert into tmp_xdummy SELECT setval('pgd_join_id_ldsg_pgd_join_id_ldsg_seq', (SELECT MAX(pgd_join_id_ldsg) FROM pgd_join_id_ldsg));
	
	drop table tmp_xdummy;	

drop table tmp_pgd_join_id_ldsg;

drop table tmp_pgd_join_id_ldsg;

insert into superx_sva(pgd_join_id,pgd_join_id_ldsg,pbv_nr)
SELECT  distinct P.pgd_join_id,P.pgd_join_id_ldsg,V.pbv_nr
FROM    pgd_join_id_ldsg P,pbv V
where V.pbv_pgd_join_id=P.pgd_join_id
and (date('$SVA_AKTIV')  <= V.pbv_bis 
or V.pbv_bis is null
 or ('$SVA_AKTIV')=date('01.01.1900') );
 
--Stellen ohne personelle Besetzung:
insert into superx_sva(sgd_join_id,pbe_serial)
SELECT  distinct  S.sgd_join_id,B.pbe_serial
FROM    sgd S left outer join pbe B on (B.pbe_sgd_join_id=S.sgd_join_id)
where S.sgd_status=0
and B.pbe_pgd_join_id is null 
and (date('$SVA_AKTIV') <= S.sgd_bis 
or S.sgd_bis is null
or date('$SVA_AKTIV')=date('01.01.1900') );

--Stellen mit Besetzung, ggf. mit Pseudonym:
insert into superx_sva(pgd_join_id_ldsg,sgd_join_id,pbe_serial)
SELECT  distinct  P.pgd_join_id_ldsg,S.sgd_join_id,B.pbe_serial
FROM    sgd S ,pbe B,pgd_join_id_ldsg P 
where B.pbe_sgd_join_id=S.sgd_join_id
and B.pbe_pgd_join_id=P.pgd_join_id 
and S.sgd_status=0
and (date('$SVA_AKTIV') <= S.sgd_bis 
or S.sgd_bis is null
or date('$SVA_AKTIV')=date('01.01.1900') );

insert into superx_sva(pgd_join_id,pgd_join_id_ldsg,pbv_nr)
SELECT  distinct P.pgd_join_id,P.pgd_join_id_ldsg,V.pbv_nr
FROM    pgd_join_id_ldsg P,pbv V
where V.pbv_pgd_join_id=P.pgd_join_id
and (date('$SVA_AKTIV')  <= V.pbv_bis 
or V.pbv_bis is null
 or ('$SVA_AKTIV')=date('01.01.1900') );
 
--Stellen ohne personelle Besetzung:
insert into superx_sva(sgd_join_id,pbe_serial)
SELECT  distinct  S.sgd_join_id,B.pbe_serial
FROM    sgd S left outer join pbe B on (B.pbe_sgd_join_id=S.sgd_join_id)
where S.sgd_status=0
and B.pbe_pgd_join_id is null 
and (date('$SVA_AKTIV') <= S.sgd_bis 
or S.sgd_bis is null
or date('$SVA_AKTIV')=date('01.01.1900') );

--Stellen mit Besetzung, ggf. mit Pseudonym:
insert into superx_sva(pgd_join_id_ldsg,sgd_join_id,pbe_serial)
SELECT  distinct  P.pgd_join_id_ldsg,S.sgd_join_id,B.pbe_serial
FROM    sgd S ,pbe B,pgd_join_id_ldsg P 
where B.pbe_sgd_join_id=S.sgd_join_id
and B.pbe_pgd_join_id=P.pgd_join_id 
and S.sgd_status=0
and (date('$SVA_AKTIV') <= S.sgd_bis 
or S.sgd_bis is null
or date('$SVA_AKTIV')=date('01.01.1900') );

select 1,$VERSION,'SVA-Version' from db_version where his_system like '%SVA%';

select 1,$VERSION,'SVA-Version' from db_version where his_system like '%SVA%';

select 1,$VERSION,'SVA-Version' from db_version where his_system like '%SVA%';

select 1,$VERSION,'SVA-Version' from db_version where his_system like '%SVA%';

select distinct  
  S.pgd_join_id_ldsg,
  V.pbv_nr ,
'R' ,
  pbv_art ,
pbv_von,
pbv_bis,
    pbv_befr_bis,
  pbv_befr_grund,
  pbv_dienstart,
pbv_hoechstdauer,
pbv_serial,
pbv_rechtsstell,
pbv_funktionsart,
$pbv_inhaber,
pbv_ausricht_prof,
pbv_wahlberecht,
pbv_qualifizierungsposition,
pbv_qualifizierungsverfahren,
pbv_besold_nr,
$pbv_ausscheidgrund
FROM pbv V, superx_sva S
WHERE
 V.pbv_pgd_join_id=S.pgd_join_id
 and V.pbv_nr=S.pbv_nr
 and V.pbv_status=0
;

select distinct  
  S.pgd_join_id_ldsg,
  V.pbv_nr ,
'R' ,
  pbv_art ,
pbv_von,
pbv_bis,
    pbv_befr_bis,
null::char(1),--  pbv_befr_grund,
  pbv_dienstart,
null::char(1),--  pbv_hoechstdauer,
pbv_serial,
null::char(1), --  pbv_rechtsstell
null::char(1), --pbv_funktionsart
null::char(1), --pbv_inhaber
null::char(1), --pbv_ausricht_prof
null::char(1),  --pbv_wahlberecht
null::char(1),  --pbv_qualifizierungsposition,
null::char(1),  --pbv_qualifizierungsverfahren,
null::char(1),  --pbv_besold_nr,
null::char(1)  --$pbv_ausscheidgrund
FROM pbv V, superx_sva S
WHERE
 V.pbv_pgd_join_id=S.pgd_join_id
 and V.pbv_nr=S.pbv_nr
 and V.pbv_status=0
;

select 
pfa_pgd_join_id,
pfa_serial,
pfa_status,
pfa_von,
pfa_bis,
pfa_art,
pfa_gebiet,
pfa_einrichtungen,
pfa_kz_voraus,
pfa_pruefung_datum,
pfa_staat_ausland,
pfa_studienbereich,
pfa_berufung_zeit,
pfa_berufung_lebenszeit,
pfa_vorquali_berufung_lebenszeit
from pfa;

select  
  pfi_pbv_nr ,
  S.pgd_join_id_ldsg,   
    pfi_serial,
'R',
  pfi_kapitel,
  pfi_titel,
    pfi_utitel,
    pfi_fb_projekt,
    pfi_inst_ausg_1,
    pfi_inst_ausg_2,
    pfi_inst_ausg_3,
  pfi_geldgeber,
  pfi_prozent,
  null::char(1), --pre_calculated vzae (nur aus SAP-HR)
  pfi_kostenziffer,
  pfi_von,
  pfi_bis,
null::char(1),--pfi_poz_serial,
pfi_kostenstelle,
pfi_projekt,
pfi_kostenart,
 pfi_art,
poz_raumnr,
poz_postversandstelle
FROM pfi P,superx_sva S
WHERE
P.pfi_pgd_join_id=S.pgd_join_id
and P.pfi_pbv_nr=S.pbv_nr
and pfi_status=0
;

select  
  pfi_pbv_nr ,
  S.pgd_join_id_ldsg,   
    pfi_serial,
'R',
  pfi_kapitel,
null::char(1),--  pfi_titel,
null::char(1),--      pfi_utitel,
null::char(1),--      pfi_fb_projekt,
null::char(1),--      pfi_inst_ausg_1,
null::char(1),--      pfi_inst_ausg_2,
null::char(1),--      pfi_inst_ausg_3,
  pfi_geldgeber,
  pfi_prozent,
null::char(1),--    pfi_kostenziffer,
  pfi_von,
  pfi_bis,
null::char(1),--pfi_poz_serial,
pfi_kostenstelle,
null::char(1),--  pfi_projekt,
null::char(1), --  pfi_kostenart
null::char(1), --pfi_art
null::char(1), -- poz_raumnr
null::char(1) as poz_postversandstelle
FROM pfi P,superx_sva S
WHERE
P.pfi_pgd_join_id=S.pgd_join_id
and P.pfi_pbv_nr=S.pbv_nr
and pfi_status=0
;

select  
pfi_pbv_nr, 
S.pgd_join_id_ldsg, 
null::char(1),
pfi_serial, 
'R' as poz_status,
  poz_institut,
pfi_von , 
pfi_bis, 
100,
pfi_serial,
poz_dienststelle,
null::char(1) as geldgeber
FROM pfi P,superx_sva S
WHERE
P.pfi_pgd_join_id=S.pgd_join_id
and P.pfi_pbv_nr=S.pbv_nr
and
pfi_status = 0
;

select  
pfi_pbv_nr, 
S.pgd_join_id_ldsg, 
null::char(1),
pfi_serial, 
'R' as poz_status,
poz_institut,
pfi_von , 
pfi_bis, 
100,
pfi_serial,
null::char(1), --  poz_dienststelle
null::char(1) as geldgeber
FROM pfi P,superx_sva S
WHERE
P.pfi_pgd_join_id=S.pgd_join_id
and P.pfi_pbv_nr=S.pbv_nr
and
pfi_status = 0
;

select  
  pbl_pbv_nr ,
S.pgd_join_id_ldsg,   
pbl_serial,
  'R' as pbl_status,
  pbl_bvl_gruppe,
  pbl_laufbahn,
  pbl_bewaehr_ab,
  pbl_adt_bez,
  pbl_adt_von,
pbl_betrag,
null::char(1),--pbl_betragsart, entfallen zu Version 16
  pbl_von,
  pbl_bis,
null::char(1), --pbl_kostenart
pbl_pbv_serial,
pbl_fallgruppe,
pbl_teil,
pbl_abschnitt,
pbl_unterabsch,
pbl_tvl_stufe,
pbl_aendergrd,
pbl_tvl_steig_ab
FROM pbl P,superx_sva S
WHERE
P.pbl_pgd_join_id=S.pgd_join_id
and P.pbl_pbv_nr=S.pbv_nr
and pbl_status=0
;

select  
  pbl_pbv_nr ,
S.pgd_join_id_ldsg,   
pbl_serial,
  'R' as pbl_status,
 '-1',--   pbl_bvl_gruppe,
null::char(1),--    pbl_laufbahn,
null::char(1),--    pbl_bewaehr_ab,
  pbl_adt_bez,
null::char(1),--    pbl_adt_von,
null::char(1),--  pbl_betrag,
null::char(1),--  pbl_betragsart,
  pbl_von,
  pbl_bis,
null::char(1),--  pbl_kostenart,
pbl_pbv_serial,
null::char(1),--  pbl_fallgruppe,
null::char(1),--  pbl_teil,
null::char(1),--  pbl_abschnitt,
null::char(1),--  pbl_unterabsch,
null::char(1), --  pbl_tvl_stufe
null::char(1), --pbl_aendergrd,
null::char(1) --pbl_tvl_steig_ab
FROM pbl P,superx_sva S
WHERE
P.pbl_pgd_join_id=S.pgd_join_id
and P.pbl_pbv_nr=S.pbv_nr
and pbl_status=0
;

select distinct
 S.pgd_join_id_ldsg,   
    'R' as status ,
    null::char(1) ,
    null::char(1),
    null::char(1) ,
$pgd_titel ,
$pgd_geburtsdatum,
$pgd_geschlecht,
$pgd_fam_stand,
$pgd_staatsangehoer ,
$pgd_plz ,
$pgd_wohnort ,
$pgd_land ,
$pgd_kategorie ,
$pgd_matrikelnummer ,
$pgd_austrittsdatum ,
$pgd_beschaeft_von ,
$pgd_besold_dienst ,
$pgd_dienstzeit ,
$pgd_eintrittsdatum ,
$pgd_eintritt_land ,
$pgd_jubi_25_datum ,
$pgd_jubi_40_datum ,
$pgd_jubi_50_datum ,
$pgd_jubi_berech ,
$pgd_leb_alterstufe ,
$pgd_verleihung,
$pgd_ablauf_probe ,
$pgd_rentendatum,
$pgd_widerrufdatum,
$pgd_inhaber,
$pgd_habil_jahr ,
$pgd_habil_hochsch ,
$pgd_habil_fach,
$pgd_berufungsjahr,
$pgd_lehrfach_einw,
$pgd_akad_grad,
$pgd_mobiltelefon,
$pgd_steuernummer,
$pgd_fax,
$pgd_account,
$pgd_email,
$pgd_staatsangeh_2
FROM  pgd P,superx_sva S
WHERE
P.pgd_join_id=S.pgd_join_id
and
 pgd_status=0
 and '$ANONYM'='true'
 union
 select distinct
 S.pgd_join_id_ldsg,   
    'R' as status ,
    $pgd_name ,
null::char(1),--     bis Version 17.1 $pgd_suchname,
    $pgd_vornamen ,
$pgd_titel ,
$pgd_geburtsdatum,
$pgd_geschlecht,
$pgd_fam_stand,
$pgd_staatsangehoer ,
$pgd_plz ,
$pgd_wohnort ,
$pgd_land ,
$pgd_kategorie ,
$pgd_matrikelnummer ,
$pgd_austrittsdatum ,
$pgd_beschaeft_von ,
$pgd_besold_dienst ,
$pgd_dienstzeit ,
$pgd_eintrittsdatum ,
$pgd_eintritt_land ,
$pgd_jubi_25_datum ,
$pgd_jubi_40_datum ,
$pgd_jubi_50_datum ,
$pgd_jubi_berech ,
$pgd_leb_alterstufe ,
$pgd_verleihung,
$pgd_ablauf_probe ,
$pgd_rentendatum,
$pgd_widerrufdatum,
$pgd_inhaber,
$pgd_habil_jahr ,
$pgd_habil_hochsch ,
$pgd_habil_fach,
$pgd_berufungsjahr,
$pgd_lehrfach_einw,
$pgd_akad_grad, --pgd_akad_grad
$pgd_mobiltelefon, --pgd_mobiltelefon
$pgd_steuernummer,
$pgd_fax,
$pgd_account,
$pgd_email,
$pgd_staatsangeh_2
FROM  pgd P,superx_sva S
WHERE
P.pgd_join_id=S.pgd_join_id
and
 pgd_status=0
 and '$ANONYM'!='true'
  ;

select distinct
 S.pgd_join_id_ldsg,   
    'R' as status ,
null::char(1),--      $pgd_name ,
null::char(1),--     bis Version 17.1 $pgd_suchname,
null::char(1),--      $pgd_vornamen ,
null::char(1),--  $pgd_titel ,
null::char(1),--  $pgd_geburtsdatum,
pgd_geschlecht,
null::char(1),--  $pgd_fam_stand,
'-1',--  $pgd_staatsangehoer ,
null::char(1),--  $pgd_plz ,
null::char(1),--  $pgd_wohnort ,
null::char(1),--  $pgd_land ,
null::char(1),--  $pgd_kategorie ,
null::char(1),--  $pgd_matrikelnummer ,
null::char(1),--  $pgd_austrittsdatum ,
null::char(1),--  $pgd_beschaeft_von ,
null::char(1),--  $pgd_besold_dienst ,
null::char(1),--  $pgd_dienstzeit ,
null::char(1),--  $pgd_eintrittsdatum ,
null::char(1),--  $pgd_eintritt_land ,
null::char(1),--  $pgd_jubi_25_datum ,
null::char(1),--  $pgd_jubi_40_datum ,
null::char(1),--  $pgd_jubi_50_datum ,
null::char(1),--  $pgd_jubi_berech ,
null::char(1),--  $pgd_leb_alterstufe ,
null::char(1),--  $pgd_verleihung,
null::char(1),--  $pgd_ablauf_probe ,
null::char(1),--  $pgd_rentendatum,
null::char(1),--  $pgd_widerrufdatum,
null::char(1), --$pgd_inhaber
null::char(1),--  $pgd_habil_jahr ,
null::char(1),--  $pgd_habil_hochsch ,
null::char(1),--  $pgd_habil_fach,
null::char(1),--  $pgd_berufungsjahr,
null::char(1),--  $pgd_lehrfach_einw,
null::char(1), --  $pgd_akad_grad
null::char(1),--$pgd_mobiltelefon
null::char(1),--$pgd_steuernummer
null::char(1), --$pgd_fax
null::char(1), --$pgd_account
null::char(1), --$pgd_email
null::char(1) --$pgd_staatsangeh_2
FROM  pgd P,superx_sva S
WHERE
P.pgd_join_id=S.pgd_join_id
and
 pgd_status=0
  ;

SELECT 
S.pgd_join_id_ldsg,
S.pbv_nr,
$pbz_serial,
'R' as status ,
$pbz_von,
$pbz_bis,
$pbz_info_1,
$pbz_info_2,
$pbz_berech_datum,
$pbz_grundverguet,
$pbz_ortszuschlag,
$pbz_zulage1,
$pbz_zulage2,
null::char(1), --$pbz_zulage3  Feld wurde entfernt ,
null::char(1), --$pbz_zulage4  Feld wurde entfernt,
$pbz_vermoegenlst,
$pbz_kv_zuschuss,
$pbz_pv_zuschuss,
$pbz_sonstige1,
$pbz_sonstige2,
$pbz_sonstige3,
$pbz_sonstige4,
$pbz_av_zuschuss,
$pbz_rv_zuschuss,
$pbz_kindergeld,
$pbz_sonstige5,
$pbz_sonstige6,
$pbz_gesamtbetrag,
$pbz_kuerzungen,
$pbz_vbl_steuer,
$pbz_aus_mon_soll,
$pbz_zuschuss_405,
$pbz_sv_gesamt,
$pbz_imp_serial,
$pbz_pbv_serial,
$pbz_pfi_serial,
null::char(1), --$pbz_pfi_uebernahme  Feld wurde entfernt
$pbz_vr_betrag,
$pbz_vr_zulagen
FROM  pbz P,superx_sva S
WHERE
P.pbz_pgd_join_id=S.pgd_join_id
and P.pbz_pbv_nr=S.pbv_nr
and '$pbz_pgd_join_id' !='null::char(1)'
and pbz_status=0
  ;

select * from superx_sva where 1=0;

SELECT 
       pbv_to_pbz_serial,
       S.pgd_join_id_ldsg,
       P.pbv_nr,
       pbv_to_pbz_von,
       pbv_to_pbz_bis,
       prozent,
       festbetrag,
       aufteil_art,
       null::char(1) as pbv_serial,
       'R' as pbv_to_pbz_status
FROM pbv_to_pbz P,superx_sva S
WHERE
P.pgd_join_id=S.pgd_join_id
and P.pbv_nr=S.pbv_nr
and '$pbv_to_pbz_unload' ='true'
and pbv_to_pbz_status=0
;

select * from superx_sva where 1=0;

select distinct
  S.sgd_join_id,
  'R'::char(1),
  sgd_ansatz_proz,
  sgd_von,
  sgd_bis,
  sgd_restkont_proz,
  sgd_genehm_jahr,
  null::char(1)::char(1),
  sgd_kategorie,
sgd_besetzung,
sgd_geteilt,
sgd_sis_vermerk1,
sgd_sis_vermerk2,
sgd_sis_vermerk3,
sgd_widmung,
sgd_haushalt_nr

FROM  sgd S,superx_sva P
WHERE
S.sgd_join_id=P.sgd_join_id
and sgd_status=0
;

select distinct
  S.sgd_join_id,
  'R'::char(1),
  sgd_ansatz_proz,
  sgd_von,
  sgd_bis,
null::char(1),--  sgd_restkont_proz,
null::char(1),--   sgd_genehm_jahr,
  null::char(1)::char(1),
null::char(1),--   sgd_kategorie,
null::char(1),-- sgd_besetzung,
null::char(1),-- sgd_geteilt,
null::char(1),-- sgd_sis_vermerk1,
null::char(1),-- sgd_sis_vermerk2,
null::char(1), -- sgd_sis_vermerk3
null::char(1), -- sgd_widmung 
null::char(1) -- sgd_haushalt_nr

FROM  sgd S,superx_sva P
WHERE
S.sgd_join_id=P.sgd_join_id
and sgd_status=0
;

select distinct
  swe_sgd_join_id,
swe_serial,
  'R',
  swe_dienstart,
  swe_stellengruppe,
  swe_bezeichnung,
  swe_von,
  swe_bis,
swe_z_stellengrp,
swe_z_dienstart,
swe_z_bezeichnung,
swe_sis_wert,
swe_fallgruppe,
swe_kapitel,
swe_titel,
swe_fb_projekt,
swe_laufbahn,
swe_geldgeber

FROM  swe,superx_sva P
WHERE
swe_sgd_join_id=P.sgd_join_id
and swe_status=0
;

select distinct
  swe_sgd_join_id,
swe_serial,
  'R',
  swe_dienstart,
null::char(1),--   swe_stellengruppe,
  swe_bezeichnung,
  swe_von,
  swe_bis,
null::char(1),-- swe_z_stellengrp,
null::char(1),-- swe_z_dienstart,
null::char(1),-- swe_z_bezeichnung,
null::char(1),-- swe_sis_wert,
null::char(1),-- swe_fallgruppe,
null::char(1),-- swe_kapitel,
null::char(1),-- swe_titel,
null::char(1),-- swe_fb_projekt,
null::char(1) -- swe_laufbahn,
null::char(1) -- swe_geldgeber

FROM  swe,superx_sva P
WHERE
swe_sgd_join_id=P.sgd_join_id
and swe_status=0
;

select distinct
  soe_sgd_join_id,
soe_serial,
  'R',
  soe_institut,
    soe_lehreinheit, 
  soe_kapitel,
  soe_titel,
  soe_kostenstelle,
  soe_von,
  soe_bis,
  soe_u_institut,
soe_zuord_pgd,
soe_zuord_sgd,
soe_dienststelle,
    soe_anteil_proz

FROM  soe,superx_sva P
WHERE
soe_sgd_join_id=P.sgd_join_id
and soe_status=0
;

select distinct
  soe_sgd_join_id,
soe_serial,
  'R',
  soe_institut,
null::char(1),--     soe_lehreinheit, 
null::char(1),--   soe_kapitel,
null::char(1),--   soe_titel,
null::char(1),--   soe_kostenstelle,
  soe_von,
  soe_bis,
null::char(1),--   soe_u_institut,
null::char(1),-- soe_zuord_pgd,
null::char(1),-- soe_zuord_sgd,
null::char(1),-- soe_dienststelle,
    soe_anteil_proz

FROM  soe,superx_sva P
WHERE
soe_sgd_join_id=P.sgd_join_id
and soe_status=0
;

select distinct
  shv_sgd_join_id,
shv_serial,
  'R',
  shv_hh_vermerk,
  shv_von,
  shv_bis,
 shv_kategorie_ziel,
 shv_bvlgruppe_ziel,
 shv_laufbahn_ziel,
 shv_dienstbez_ziel,
 shv_kw_faell_hh

FROM  shv,superx_sva P
WHERE
shv_sgd_join_id=P.sgd_join_id
and shv_status=0
;

select * from superx_sva where 1=0;

select
pbe_nr,
pbe_pbv_nr,
P.pgd_join_id_ldsg,
pbe_sgd_join_id,
pbe.pbe_serial,
'R' as pbe_status,
pbe_art,
pbe_tarif_std,
pbe_anteil_stdpro,
pbe_anteil_prozent,
pbe_anteil_std,
pbe_kennzeichen,
pbe_grund,
pbe_von,
pbe_bis,
pbe_endegrund,
null::char(1),--pbe_genehmigung,
pbe_genehmigung_v,
pbe_ms_schluessel,
pbe_ms_ziel,
pbe_ms_bis,
null::char(1),
pbe_sis_ia,
pbe_sis_wert,
pbe_ms_prog_bis,
pbe_serial_storno,
pbe_soe_serial,
null::char(1) ,
pbe_pfi_serial

from  pbe ,superx_sva P
WHERE
pbe_sgd_join_id=P.sgd_join_id
and pbe.pbe_serial=P.pbe_serial
and
 pbe_status=0
;

select
pbe_nr,
pbe_pbv_nr,
P.pgd_join_id_ldsg,
pbe_sgd_join_id,
pbe.pbe_serial,
'R' as pbe_status,
pbe_art,
null::char(1),-- pbe_tarif_std,
null::char(1),-- pbe_anteil_stdpro,
pbe_anteil_prozent,
null::char(1),-- pbe_anteil_std,
null::char(1),-- pbe_kennzeichen,
null::char(1),-- pbe_grund,
pbe_von,
pbe_bis,
null::char(1),-- pbe_endegrund,
null::char(1),-- pbe_genehmigung,
null::char(1),-- pbe_genehmigung_v,
null::char(1),-- pbe_ms_schluessel,
null::char(1),-- pbe_ms_ziel,
null::char(1),-- pbe_ms_bis,
null::char(1),
null::char(1),-- pbe_sis_ia,
null::char(1),-- pbe_sis_wert,
null::char(1),-- pbe_ms_prog_bis,
null::char(1),-- pbe_serial_storno,
pbe_soe_serial,
null::char(1) ,
pbe_pfi_serial

from  pbe ,superx_sva P
WHERE
pbe_sgd_join_id=P.sgd_join_id
and pbe.pbe_serial=P.pbe_serial
and
 pbe_status=0
;

select
pbu_pbv_nr, 
P.pgd_join_id_ldsg,  
pbu_serial,
pbu_art,
pbu_von,
pbu_bis,
pbu_ununter_seit,
pbu_pbv_serial
from pbu,superx_sva P 
WHERE
pbu_pgd_join_id=P.pgd_join_id
and pbu_pbv_nr=P.pbv_nr
and
 pbu_status =0
;

select * from superx_sva where 1=0;

select 
paz_pbv_nr,
P.pgd_join_id_ldsg, 
paz_serial,
'R' as paz_status,
paz_tarif_std,
paz_arbzeit_stdpro,
paz_tz_std, 
paz_tz_proz, 
paz_tz_art, 
paz_von, 
paz_bis, 
paz_art_zeit,
paz_atz_block,
paz_atz_teilzeit
from paz,superx_sva P
WHERE
paz_pgd_join_id=P.pgd_join_id
and paz_pbv_nr=P.pbv_nr
and
 paz_status=0
;

select 
paz_pbv_nr,
P.pgd_join_id_ldsg, 
paz_serial,
'R' as paz_status,
null::char(1),-- paz_tarif_std,
null::char(1),-- paz_arbzeit_stdpro,
paz_tz_std, 
paz_tz_proz, 
null::char(1),-- paz_tz_art, 
paz_von, 
paz_bis, 
paz_art_zeit,
null::char(1),-- paz_atz_block,
null::char(1)-- paz_atz_teilzeit
from paz,superx_sva P
WHERE
paz_pgd_join_id=P.pgd_join_id
and paz_pbv_nr=P.pbv_nr
and
 paz_status=0
;

select distinct
    sbu_serial,
    sbu_pbe_serial,
    'R' as sbu_status,
    sbu_stdpro,
    sbu_prozent,
    sbu_stunden,
    sbu_von,
    sbu_bis,
    sbu_grund
from sbu,pbe,superx_sva P
WHERE
sbu_pbe_serial=pbe.pbe_serial
and P.pbe_serial=pbe.pbe_serial
and sbu_status = 0
;

select distinct
    sbu_serial,
    sbu_pbe_serial,
    'R' as sbu_status,
null::char(1),--     sbu_stdpro,
    sbu_prozent,
 null::char(1),--    sbu_stunden,
    sbu_von,
    sbu_bis,
'-1' --     sbu_grund
from sbu,pbe,superx_sva P
WHERE
sbu_pbe_serial=pbe.pbe_serial
and P.pbe_serial=pbe.pbe_serial
and sbu_status = 0
;

select
pdp_pbv_nr,
P.pgd_join_id_ldsg,
pdp_serial,
'R' as pdp_status,
pdp_lehreinheit,
pdp_stunden,
pdp_vermerk,
pdp_von,
pdp_bis,
pdp_praesenztage,
pdp_pbv_serial,
pdp_iststunden,
pdp_institut
from pdp,superx_sva P
WHERE
pdp_pgd_join_id=P.pgd_join_id
and pdp_pbv_nr=P.pbv_nr
and
 pdp_status= 0
;

select * from superx_sva where 1=0;

select
pmi_pdp_serial,
pmi_serial,
'R' as pmi_status,
pmi_minderungsgrd,
pmi_minderungsstd,
pmi_von,
pmi_bis
from pmi M, pdp P,superx_sva S
where pmi_status = 0
and P.pdp_serial=M.pmi_pdp_serial
and P.pdp_pgd_join_id=S.pgd_join_id
and P.pdp_pbv_nr=S.pbv_nr
;

select
pmi_pdp_serial,
pmi_serial,
'R' as pmi_status,
pmi_minderungsgrd,
pmi_minderungsstd,
pmi_von,
pmi_bis
from pmi M, pdp P,superx_sva S
where pmi_status = 0
and P.pdp_serial=M.pmi_pdp_serial
and P.pdp_pgd_join_id=S.pgd_join_id
and P.pdp_pbv_nr=S.pbv_nr
;

SELECT distinct
       pbz_betrag_serial,
       B.pbz_serial,
       pbz_betrag,
       pbz_betragsfeld,
       pbz_zkey,
       pbz_vr_relevant,
       pbz_vr_rgf,
       pbz_fibu_relevanz,
       pbz_nur_informativ,
       pbz_text
FROM pbz_betraege B,pbz P,superx_sva S
where 
 B.pbz_serial=P.pbz_serial
and P.pbz_pgd_join_id=S.pgd_join_id
and '$pbz_pgd_join_id' !='null::char(1)'
;

select * from superx_sva where 1=0;

select 0::integer as hs,
635::integer as key,
 key_befristetgrd as apnr,
ueberkey as parent,
key_von as d_akt_von,
 key_bis as d_akt_bis,
kurz_befristetgrd as kurz,
druck_befristetgrd as druck,
text_befristetgrd as lang_1,
null::char(1) as bund_apnr,
null::char(1) as astat,
0::integer as sort1,
null::char(3) as sprache,
null::char(1) as struktur_c,
his_system_key,
key_befristetgrd as sourcesystem_id,
key_befristetgrd as uniquename
 from k_befristetgrund K 
union
select 0::integer as hs,
109::integer as key,
key_bvlgruppe as apnr,
 ueberkey as parent,
key_von as d_akt_von,
key_bis as d_akt_bis,
kurz_bvlgruppe as kurz,
druck_bvlgruppe as druck,
text_bvlgruppe as lang_1,
bund_bvlgruppe as bund_apnr,
land_bvlgruppe as astat,
sort_key_report as sort1,
null::char(3) as sprache,
 bund_dienstverh as struktur_c,
 null::char(10) as his_system_key,
key_bvlgruppe as sourcesystem_id,
key_bvlgruppe as uniquename
 from k_bvlgruppe K 
union
select 0,108,
 key_amtsdienstbez,
 ueberkey,
key_von,
key_bis,
kurz_adbz_m,
druck_adbz_m,
text_adbz_m,
bund_adbz,
land_adbz,
sort_key,
null::char(3) as sprache,
gremienwahl,
null::char(10) as his_system_key,
key_amtsdienstbez as sourcesystem_id,
key_amtsdienstbez as uniquename
from k_amtsdienstbez K
union select
0,
106,
key_beurlaubart ,
ueberkey,
key_von ,
key_bis ,
kurz_beurlaubart,
druck_beurlaubart ,
text_beurlaubart,
frauenfoerderplan::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
bezuege_kz as struktur_c,
his_system_key,
key_beurlaubart as sourcesystem_id,
key_beurlaubart as uniquename
from k_beurlaubart K
union select
0,
602,
key_pbeart ,
ueberkey,
key_von ,
key_bis ,
kurz_pbeart,
druck_pbeart ,
text_pbeart,
berechnung::char(10)  as bund_apnr,
schoepfung::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
karte::char(10) as his_system_key,
key_pbeart as sourcesystem_id,
key_pbeart as uniquename
from k_pbeart K
union
select 0,
603,
key_teilzeitart ,
ueberkey,
key_von ,
key_bis ,
kurz_teilzeitart,
druck_teilzeitart ,
text_teilzeitart,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_teilzeitart as sourcesystem_id,
key_teilzeitart as uniquename
from k_teilzeitart 
union
select 0,
604,
key_vertragsart,
ueberkey,
key_von,
key_bis,
kurz_vertragsart_m,
druck_vertragsartm,
text_vertragsart_m,
bundstat_relevant,
schwstat_relevant,
null::integer as sort_key,
null::char(3) as sprache,
bund_taetigkeit as struktur_c,
his_system_key ,
key_vertragsart as sourcesystem_id,
key_vertragsart as uniquename
FROM k_vertragsart K
union
select 0,
115,
key_hhvermerk,
ueberkey,
key_von,
key_bis,
kurz_hhvermerk,
druck_hhvermerk,
text_hhvermerk,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
hh_hhvermerk as struktur_c,
his_system_key,
key_hhvermerk as sourcesystem_id,
key_hhvermerk as uniquename
FROM k_haushaltsverm 
union
select 0,
107,
key_dienstart,
ueberkey,
key_von,
key_bis,
kurz_dienstart,
druck_dienstart,
text_dienstart,
bund_dienstart,
ministerium as astat,
null::integer as sort_key,
null::char(3) as sprache,
frauenfoerderplan::char(10) as struktur_c,
null::char(10) as his_system_key,
key_dienstart as sourcesystem_id,
key_dienstart as uniquename
FROM k_dienstart 
union
select 0,
300,
key_mind_grund,
ueberkey,
key_von as d_akt_von,
key_bis as d_akt_bis,
kurz_mind_grund,
druck_mind_grund,
text_mind_grund,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_mind_grund as sourcesystem_id,
key_mind_grund as uniquename
from k_minderungsgrd 
union
select 0,
606,
key_lehreinh,
ueberkey,
key_von,
key_bis,
kurz_lehreinh,
druck_lehreinh,
text_lehreinh,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_lehreinh as sourcesystem_id,
key_lehreinh as uniquename
FROM k_lehreinh
union
select 0,
259,
key_kategorie,
ueberkey,
key_von,
key_bis,
kurz_kategorie,
druck_kategorie,
text_kategorie,
bundstat_relevant,
hh_kategorie as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_kategorie as sourcesystem_id,
key_kategorie as uniquename
from k_sgd_kategorie 
union
select 0,
501,
key_staat,
ueberkey,
key_von,
key_bis,
kurz_land, 
druck_land, 
text_land, 
bund_staat,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
eg_mitglied as struktur_c ,
null::char(10) as his_system_key,
key_staat as sourcesystem_id,
key_staat as uniquename
from k_staat S
union
select 0,
605,
wert,
null::char(10) as parent,
 null::date as d_akt_von,
 null::date as d_akt_bis,
substring(besold from 1 for 10),
kztxt,
latxt,
null::char(10) as bund_apnr,
deputat as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
wert as sourcesystem_id,
wert as uniquename
from k_sis_wert 
union
select  0,
608,
key_rechtsstell,
ueberkey,
 key_von,
key_bis,
kurz_rechtsstell_m,
druck_rechtsstellm,
text_rechtsstell_m,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
gremienwahl::char(10)  as struktur_c,
null::char(10) as his_system_key,
key_rechtsstell as sourcesystem_id,
key_rechtsstell as uniquename
from k_rechtsstell
union
select 0,
607,
key_laufbahn,
ueberkey,
 key_von,
key_bis,
kurz_laufbahn,
druck_laufbahn,
text_laufbahn,
bund_laufbahn,
land_laufbahn,
sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_laufbahn as sourcesystem_id,
key_laufbahn as uniquename
from k_laufbahn 
union
select 0,
609,
key_pbekennz,
       ueberkey,
       key_von,
       key_bis,
       kurz_pbekennz,
       druck_pbekennz,
       text_pbekennz,
       bund_besetzung,
       land_pbekennz,
 null::integer as sort_key,
 null::char(3) as sprache,
null::char(50) as struktur_c,
      his_system_key,
key_pbekennz as sourcesystem_id,
key_pbekennz as uniquename
       from k_pbekennzeichen 
union
       select 0,
       632,
       key_art_zeit,
null::char(10) as parent,       
key_von,
key_bis,
kurz_art_zeit,
druck_art_zeit,
text_art_zeit,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_art_zeit as sourcesystem_id,
key_art_zeit as uniquename
 from k_art_zeit
 where key_art_zeit is not null
union
       select 0,
       633,
       key_pfi_art,
ueberkey as parent,       
null::date as key_von,
null::date as key_bis,
kurz_pfi_art,
druck_pfi_art,
text_pfi_art,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_pfi_art as sourcesystem_id,
key_pfi_art as uniquename
 from k_pfi_art
 where key_pfi_art is not null
 union
       select 0,
       291,
       key_kategorie,
ueberkey as parent,       
key_von,
key_bis,
kurz_kategorie_m,
druck_kategorie_m,
text_kategorie_m,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_kategorie as sourcesystem_id,
key_kategorie as uniquename
 from k_pgd_kategorie
 where key_kategorie is not null
 union
       select 0,
       634,
       key_funktionsart,
ueberkey as parent,       
key_von,
key_bis,
kurz_funktionsartm,
druck_funktion_m,
text_funktionsartm,
frauenfoerderplan::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_funktionsart as sourcesystem_id,
key_funktionsart as uniquename
 from k_funktionsart
 where key_funktionsart is not null
 union
 select 0::integer as hs,
610::integer as key,
 key_anredetitel as apnr,
null::char(1) as parent,
null::date as d_akt_von,
 null::date as d_akt_bis,
null::char(1) as kurz,
druck_anredetitelm as druck,
text_anredetitel_m as lang_1,
null::char(1) as bund_apnr,
null::char(1) as astat,
0::integer as sort1,
null::char(3) as sprache,
null::char(1) as struktur_c,
key_anredetitel as his_system_key,
key_anredetitel as sourcesystem_id,
key_anredetitel as uniquename
 from k_anredetitel
 union 
 --hochschulen kommt auch nach cifx
select 0,
810,
key_hochschule ,
ueberkey,
key_von ,
key_bis ,
kurz_hochschule,
druck_hochschule,
text_hochschule,
bund_hochschule as bund_apnr,
null::char(3) as astat,
null::integer as sort_key,
null::char(3) as sprache,
key_bundesland as struktur_c,
null::char(1) his_system_key,
key_hochschule as sourcesystem_id,
key_hochschule as uniquename
from k_hochschule
union
--fachgebiete
select 0,
701,
key_fachgebiet ,
ueberkey,
key_von ,
key_bis ,
kurz_fachgebiet,
druck_fachgebiet,
text_fachgebiet,
bund_fachgebiet as bund_apnr,
bund_lehr_forsch as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(1) as struktur_c,
null::char(1) his_system_key,
key_fachgebiet as sourcesystem_id,
key_fachgebiet as uniquename
from k_fachgebiet
union
--studienbereiche
select 0,
702,
key_studienbereich ,
ueberkey,
key_von ,
key_bis ,
kurz_studienbereich,
druck_studienbereich,
text_studienbereich,
bund_studienbereich as bund_apnr,
bund_faechergruppe as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(1) as struktur_c,
null::char(1) his_system_key,
key_studienbereich as sourcesystem_id,
key_studienbereich as uniquename
from k_studienbereich
union
--k vorquali berufung
select 0,
703,
key_vorquali_berufung ,
ueberkey,
key_von ,
key_bis ,
kurz_svorquali_berufung ,
druck_vorquali_berufung ,
text_vorquali_berufung ,
bund_vorquali_berufung  as bund_apnr,
null::char(3) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(1) as struktur_c,
null::char(1) his_system_key,
key_vorquali_berufung  as sourcesystem_id,
key_vorquali_berufung as uniquename
from k_vorquali_berufung
union
-- Änderungsgrund
select 0,
704,
key_aendergrd ,
ueberkey,
key_von ,
key_bis ,
kurz_aendergrd,
druck_aendergrd,
text_aendergrd ,
null::char(3)  as bund_apnr,
null::char(3) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(1) as struktur_c,
null::char(1) his_system_key,
key_aendergrd  as sourcesystem_id,
key_aendergrd as uniquename
from k_aenderungsgrd
union
--k_pfaart
select 0,
705,
key_pfaart ,
ueberkey,
key_von ,
key_bis ,
kurz_pfaart,
druck_pfaart,
text_pfaart,
bund_pfaart as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
isced_level as struktur_c,
his_system_key,
key_pfaart as sourcesystem_id,
key_pfaart as uniquename
from k_pfaart
union
--k_persattrib
select 0,
706,
key_persattribut ,
ueberkey,
key_von ,
key_bis ,
kurz_persattributm as kurz, --maennlich,
druck_persattrib_m as druck, --maennlich
druck_persattrib_w as lang_1, --weiblich
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
zusatzinfo as struktur_c,
his_system_key,
key_persattribut as sourcesystem_id,
key_persattribut as uniquename
from k_persattribut
--707 und 708 reserviert s. sva_cifx_fuellen.sql
union
--k_ausscheidegrund
select 0,
709,
key_ausscheidegrd ,
ueberkey,
key_von ,
key_bis ,
kurz_ausscheidegrd as kurz, 
druck_ausscheide as druck, 
text_ausscheidegrd as lang_1, 
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(3) as struktur_c,
his_system_key,
key_ausscheidegrd as sourcesystem_id,
key_ausscheidegrd as uniquename
from k_ausscheidegrund

;

select 0::integer as hs,
635::integer as key,
 key_befristetgrd as apnr,
ueberkey as parent,
key_von as d_akt_von,
 key_bis as d_akt_bis,
kurz_befristetgrd as kurz,
druck_befristetgrd as druck,
text_befristetgrd as lang_1,
null::char(1) as bund_apnr,
null::char(1) as astat,
0::integer as sort1,
null::char(3) as sprache,
null::char(1) as struktur_c,
null::char(10) as his_system_key,
key_befristetgrd as sourcesystem_id,
key_befristetgrd as uniquename
 from k_befristetgrund K 
union
select distinct 0::integer as hs,
109::integer as key,
'-1'::char(10) as apnr,
 null::char(10) as parent,
 null::date as d_akt_von,
 null::date as d_akt_bis,
'Unbekannt'::char(10) as kurz,
'Unbekannt'::char(10) as druck,
'Unbekannt'::char(10) as lang_1,
null::char(10) as bund_apnr,
null::char(10) as astat,
1 as sort1,
null::char(3) as sprache,
 null::char(50) as struktur_c,
 null::char(10) as his_system_key,
'-1'::char(10) as sourcesystem_id,
'-1'::char(10) as uniquename
 from k_bvlgruppe K 
union
select 0,108,
 key_amtsdienstbez,
 ueberkey,
key_von,
key_bis,
kurz_adbz_m,
druck_adbz_m,
text_adbz_m,
bund_adbz,
land_adbz,
sort_key,
null::char(3) as sprache,
gremienwahl,
null::char(10) as his_system_key,
key_amtsdienstbez as sourcesystem_id,
key_amtsdienstbez as uniquename
from k_amtsdienstbez K
union select
0,
106,
key_beurlaubart ,
ueberkey,
key_von ,
key_bis ,
kurz_beurlaubart,
druck_beurlaubart ,
text_beurlaubart,
frauenfoerderplan::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
bezuege_kz as struktur_c,
his_system_key,
key_beurlaubart as sourcesystem_id,
key_beurlaubart as uniquename
from k_beurlaubart K
union select
0,
602,
key_pbeart ,
ueberkey,
key_von ,
key_bis ,
kurz_pbeart,
druck_pbeart ,
text_pbeart,
berechnung::char(10)  as bund_apnr,
schoepfung::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
karte::char(10) as his_system_key,
key_pbeart as sourcesystem_id,
key_pbeart as uniquename
from k_pbeart K
union
select 0,
603,
key_teilzeitart ,
ueberkey,
key_von ,
key_bis ,
kurz_teilzeitart,
druck_teilzeitart ,
text_teilzeitart,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_teilzeitart as sourcesystem_id,
key_teilzeitart as uniquename
from k_teilzeitart 
union
select 0,
604,
key_vertragsart,
ueberkey,
key_von,
key_bis,
kurz_vertragsart_m,
druck_vertragsartm,
text_vertragsart_m,
bundstat_relevant,
schwstat_relevant,
null::integer as sort_key,
null::char(3) as sprache,
bund_taetigkeit as struktur_c,
his_system_key ,
key_vertragsart as sourcesystem_id,
key_vertragsart as uniquename
FROM k_vertragsart K
union
select 0,
115,
key_hhvermerk,
ueberkey,
key_von,
key_bis,
kurz_hhvermerk,
druck_hhvermerk,
text_hhvermerk,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
hh_hhvermerk as struktur_c,
his_system_key,
key_hhvermerk as sourcesystem_id,
key_hhvermerk as uniquename
FROM k_haushaltsverm 
union
select 0,
107,
key_dienstart,
ueberkey,
key_von,
key_bis,
kurz_dienstart,
druck_dienstart,
text_dienstart,
bund_dienstart,
ministerium as astat,
null::integer as sort_key,
null::char(3) as sprache,
frauenfoerderplan::char(10) as struktur_c,
null::char(10) as his_system_key,
key_dienstart as sourcesystem_id,
key_dienstart as uniquename
FROM k_dienstart 
union
select 0,
300,
key_mind_grund,
ueberkey,
 null::date as d_akt_von,
 null::date as d_akt_bis,
kurz_mind_grund,
druck_mind_grund,
text_mind_grund,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_mind_grund as sourcesystem_id,
key_mind_grund as uniquename
from k_minderungsgrd 
union
select 0,
606,
key_lehreinh,
ueberkey,
key_von,
key_bis,
kurz_lehreinh,
druck_lehreinh,
text_lehreinh,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_lehreinh as sourcesystem_id,
key_lehreinh as uniquename
FROM k_lehreinh
union
select 0,
259,
key_kategorie,
ueberkey,
key_von,
key_bis,
kurz_kategorie,
druck_kategorie,
text_kategorie,
bundstat_relevant,
hh_kategorie as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_kategorie as sourcesystem_id,
key_kategorie as uniquename
from k_sgd_kategorie 
union
select distinct 0,
501,
'-1'::char(10),--key_staat,
null::char(10),--ueberg
 null::date as d_akt_von,
 null::date as d_akt_bis,
'Unbekannt'::char(10) as kurz,
'Unbekannt'::char(10) as druck,
'Unbekannt'::char(10) as lang_1,
null::char(10),--bund_staat,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50)  as struktur_c ,
null::char(10) as his_system_key,
'-1'::char(10) as sourcesystem_id,
'-1'::char(10) as uniquename
from k_staat S
union
select 0,
605,
wert,
null::char(10) as parent,
 null::date as d_akt_von,
 null::date as d_akt_bis,
substring(besold from 1 for 10),
kztxt,
latxt,
null::char(10) as bund_apnr,
deputat as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
wert as sourcesystem_id,
wert as uniquename
from k_sis_wert 
union
select  0,
608,
key_rechtsstell,
ueberkey,
 key_von,
key_bis,
kurz_rechtsstell_m,
druck_rechtsstellm,
text_rechtsstell_m,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
gremienwahl::char(10)  as struktur_c,
null::char(10) as his_system_key,
key_rechtsstell as sourcesystem_id,
key_rechtsstell as uniquename
from k_rechtsstell
union
select 0,
607,
key_laufbahn,
ueberkey,
 key_von,
key_bis,
kurz_laufbahn,
druck_laufbahn,
text_laufbahn,
bund_laufbahn,
land_laufbahn,
sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_laufbahn as sourcesystem_id,
key_laufbahn as uniquename
from k_laufbahn 
union
select 0,
609,
key_pbekennz,
       ueberkey,
       key_von,
       key_bis,
       kurz_pbekennz,
       druck_pbekennz,
       text_pbekennz,
       bund_besetzung,
       land_pbekennz,
 null::integer as sort_key,
 null::char(3) as sprache,
null::char(50) as struktur_c,
      his_system_key,
key_pbekennz as sourcesystem_id,
key_pbekennz as uniquename
       from k_pbekennzeichen 
union
       select 0,
       632,
       key_art_zeit,
null::char(10) as parent,       
key_von,
key_bis,
kurz_art_zeit,
druck_art_zeit,
text_art_zeit,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_art_zeit as sourcesystem_id,
key_art_zeit as uniquename
 from k_art_zeit
 where key_art_zeit is not null
union
       select 0,
       633,
       key_pfi_art,
ueberkey as parent,       
null::date as key_von,
null::date as key_bis,
kurz_pfi_art,
druck_pfi_art,
text_pfi_art,
null::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
his_system_key,
key_pfi_art as sourcesystem_id,
key_pfi_art as uniquename
 from k_pfi_art
 where key_pfi_art is not null
 union
--besch.-Stelle=orgunit
       select 0,
       110,
       inst_nr,
uebinst_nr as parent,       
key_von,
key_bis,
inst_nr,
substring(dname from 1 for 100),
substring(lname1  from 1 for 100),
bund_fachgebiet as bund_apnr,
land_institut as astat,
null::integer as sort_key,
null::char(3) as sprache,
orgstruktur::char(10) as struktur_c,
art as hiskey_id,
inst_nr as sourcesystem_id,
inst_nr as uniquename
 from inst
 where svarel='1'
 union
--Kostenstelle
       select 0,
       122,
       inst_nr,
uebinst_nr as parent,       
key_von,
key_bis,
inst_nr,
substring(dname from 1 for 100),
substring(lname1  from 1 for 100),
bund_fachgebiet as bund_apnr,
land_institut as astat,
null::integer as sort_key,
null::char(3) as sprache,
orgstruktur::char(10) as struktur_c,
art as hiskey_id,
inst_nr as sourcesystem_id,
inst_nr as uniquename
 from inst
 where svarel='1'
 union
       select 0,
       634,
       key_funktionsart,
ueberkey as parent,       
key_von,
key_bis,
kurz_funktionsartm,
druck_funktion_m,
text_funktionsartm,
frauenfoerderplan::char(10) as bund_apnr,
null::char(10) as astat,
null::integer as sort_key,
null::char(3) as sprache,
null::char(50) as struktur_c,
null::char(10) as his_system_key,
key_funktionsart as sourcesystem_id,
key_funktionsart as uniquename
 from k_funktionsart
 where key_funktionsart is not null
 --707 und 708 reserviert s. sva_cifx_fuellen.sql
 ;
  

select  
inst_nr,
uebinst_nr,
dname,
lname1,
key_von,
key_bis,
orgstruktur,
fins,
lehr,
bund_kategorie,
 bund_lehr_forsch,
 bund_fachgebiet,
 land_fachgebiet,
null::char(1),-- sis_fachbereich,
 land_institut,
 bund_rahmenplan,
 art,
 inst_nr, --uniquename
 ggnr

from inst 
where inst.svarel='1' or inst.inst_nr in (select distinct pfi.pfi_kostenstelle 
from pfi where pfi.pfi_status=0);

select  
inst_nr,
uebinst_nr,
'Unbekannt',--dname,
'Unbekannt',--lname1,
key_von,
key_bis,
null::char(1),--orgstruktur,
null::char(1),--fins,
null::char(1),--lehr,
null::char(1),--bund_kategorie,
null::char(1),-- bund_lehr_forsch,
 bund_fachgebiet,
null::char(1),-- land_fachgebiet,
null::char(1),-- sis_fachbereich,
null::char(1),-- land_institut,
null::char(1),-- bund_rahmenplan
null::char(1), -- art
inst_nr, --uniquename
 ggnr
from inst 
where inst.svarel='1' or inst.inst_nr in (select distinct pfi.pfi_kostenstelle 
from pfi where pfi.pfi_status=0);

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

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

SELECT * from orgunit where 1=0;
     
	

select 
ggnr ,
ueberkey,
ggkey  ,
fikey,
ggkurz   ,
ggdruck  ,
ggname1  ,
ggname2,
ggname3  ,
key_von     ,
key_bis ,
bund_geldgeber ,
klr_geldgeber
from gege
where svarel='1' 
and ggnr is not null

select 
ggnr ,
null::char(1),--ueberkey,
null::char(1),--ggkey  ,
null::char(1),--fikey,
'Unbekannt',--ggkurz   ,
'Unbekannt',--ggdruck  ,
'Unbekannt',--ggname1  ,
'Unbekannt',--ggname2,
'Unbekannt',--ggname3  ,
null::char(1),--key_von     ,
null::char(1),--key_bis ,
null::char(1),--bund_geldgeber ,
klr_geldgeber
from gege
where svarel='1' 
and ggnr is not null

SELECT
key_lehreinh,ueberkey,
kurz_lehreinh,
druck_lehreinh,
     text_lehreinh,
key_von,
key_bis
 FROM k_lehreinh;

select * from superx_sva where 1=0;

SELECT '' || round(count(*),0) from pbv WHERE
    pbv_status=0
    and (today >= pbv_von or pbv_von is null)
    and
     (
    today <= pbv_bis
    or (pbv_bis is null and pbv_befr_bis >= today)
    or (pbv_bis is null and pbv_befr_bis is null)
    );

SELECT count(*) from pbv WHERE
    pbv_status=0
    and (current_date >= pbv_von or pbv_von is null)
    and
     (
    current_date <= pbv_bis
    or (pbv_bis is null and pbv_befr_bis >= current_date)
    or (pbv_bis is null and pbv_befr_bis is null)
    );

SELECT count(*) from pbv WHERE
    pbv_status=0
    and (current_date >= pbv_von or pbv_von is null)
    and
     (
    current_date <= pbv_bis
    or (pbv_bis is null and pbv_befr_bis >= current_date)
    or (pbv_bis is null and pbv_befr_bis is null)
    );

SELECT "" || round(count(*),0) from sgd WHERE
sgd_status=0
and (TODAY between sgd_von and sgd_bis
 or (TODAY >= sgd_von and sgd_bis is null)
 );

SELECT count(*) from sgd WHERE
sgd_status=0
and (CURRENT_DATE between sgd_von and sgd_bis
 or (CURRENT_DATE >= sgd_von and sgd_bis is null)

 );

SELECT "" || round(count(*),0) from sgd WHERE
sgd_status=0
and (TODAY between sgd_von and sgd_bis
 or (TODAY >= sgd_von and sgd_bis is null)
 );

SELECT count(*) from sgd WHERE
sgd_status=0
and (CURRENT_DATE between sgd_von and sgd_bis
 or (CURRENT_DATE >= sgd_von and sgd_bis is null)

 );

SELECT jahr,
       kap,
       tit,
       ut,
       fb,
       fbbez,
       dr,
       dr2,
       hhans,
       akts,
       sperr,
       reste,
       zvert,
       fest,
       ausz,
       soll,
       ein,
       verfkor,
       vojs,
       kz_ueber,
       sperrkz,
       soll_kontr,
       o_nr,
       finz_stat,
       art,
       mwpf,
       join_nr,
       dtgr,
       vwg
FROM fb
where 0 < (select count(*) from pbz where '$pbz_pgd_join_id' !='null::char(1)');

select * from superx_sva where 1=0;

SELECT jahr,
       kap,
       tit,
       ut,
       fb,
       ins,
       insbez,
       dr,
       dr2,
       hhans,
       akts,
       sperr,
       reste,
       zvert,
       fest,
       ausz,
       soll,
       ein,
       verfkor,
       vojs,
       kz_ueber,
       sperrkz,
       soll_kontr,
       o_nr,
       finz_stat,
       art,
       mwpf,
       join_nr,
       dtgr,
       vwg
FROM ins
where 0 < (select count(*) from pbz where '$pbz_pgd_join_id' !='null::char(1)');

select * from superx_sva where 1=0;

select
durchfuehrung,
       poz_dienststelle,
       pfi_kostenstelle,
       poz_anteil_proz,
       pfi_projekt,
       jahr,
       monat,
       pbl_bvl_gruppe,
       pbl_laufbahn,
       pbl_adt_bez,
       pbv_dienstart,
       vollzeit_aequiv,
       pbv_befristet,
       pbv_art,
       pbv_rechtsstell,
       pfi_geldgeber,
       pfi_kapitel,
       pfi_titel,
       pfi_kostenart,
       bv_zaehler,
       null::char(1),--Spalte f_e_h_l_e_rmeldung wird nicht benötigt,
       out_cob_pbv_serial,
       pgd_join_id,
       pbv_nr,
       pfi_utitel,
       pfi_fb_projekt,
       pfi_inst_ausg_1,
       pfi_inst_ausg_2,
       pfi_inst_ausg_3,
       pfi_kostenziffer,
       bund_kategorie,
       bund_lehr_forsch,
       bund_fachgebiet,
       bund_bvlgruppe,
       bund_dienstverh,
       bund_laufbahn,
       bund_adbz,
       bund_dienstart,
       bund_geldgeber,
       pgd_zaehler,
       pbv_inhaber,
       personenmonate,
       poz_institut
       from out_cob_pbv
       where fehlermeldung is null or trim(fehlermeldung)='';

select * from superx_sva where 1=0;

select B.key_bvlgruppe,
K.key_kalkulation,
K.ueberkey,
K.zuschlag,
K.key_von,
K.key_bis,
K.land_zuschlag,
K.kalkulationsbetrag,
K.land_betrag
from k_bvlgruppe B, k_kalkwert K
where K.key_kalkulation=B.key_kalkulation
;

select * from superx_sva where 1=0;

select distinct S.pgd_join_id_ldsg,
pat_serial,
pat_status,
pat_persattribut,
pat_von,
pat_bis,
pat_institut,
null::char(1) as pat_zusatz
from pat,superx_sva S
WHERE
pat_pgd_join_id=S.pgd_join_id
and '$pat_unload' ='true' and '$ANONYM'='true'
-- nicht anonym: pat_zusatz mit entladen
union
select distinct S.pgd_join_id_ldsg,
pat_serial,
pat_status,
pat_persattribut,
pat_von,
pat_bis,
pat_institut,
pat_zusatz
from pat,superx_sva S
WHERE
pat_pgd_join_id=S.pgd_join_id
and '$pat_unload' ='true' and '$ANONYM'!='true'

select * from superx_sva where 1=0;

	select
projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,laufzeitbeginn,bewabl 
from proj
where svarel='1'


	

	select
projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,laufzeitbeginn,bewabl 
from proj
where svarel='1'


	

SELECT * from orgunit 
--der Unload findet statt in cifx.key=123/91
where 1=0;

     
	

drop table superx_sva ; 

drop table superx_sva ;