DROP TABLE IF EXISTS tmp_orgunit;
	CREATE TABLE tmp_orgunit (
    id integer NOT NULL,
    lid integer NOT NULL,
    k_orgunittype_id integer,
    valid_from date,
    valid_to date,
    astat character varying,
    companynumber character varying,
    description text,
    shorttext character varying,
    defaulttext character varying NOT NULL,
    longtext character varying,
    sortorder integer,
    k_language_id integer NOT NULL,
    parent_lid integer,
    uniquename character varying,
    obj_guid character varying,
    lock_version integer,
    parent_id integer,
    cost_center_id integer,
    subject_area_k_subject_area_id integer,
    k_astat_orgunit_category_id integer
);
insert into tmp_orgunit(id,
       lid,
       k_orgunittype_id,
       valid_from,
       valid_to,
       astat,
       companynumber,
       description,
       shorttext,
       defaulttext,
       longtext,
       sortorder,
       k_language_id,
       parent_lid,
       uniquename,
       subject_area_k_subject_area_id ,
    k_astat_orgunit_category_id ,
       obj_guid,
       lock_version
       )
       select
        o.id,
       o.lid,
       o.k_orgunittype_id,
       o.valid_from,
       o.valid_to,
       o.astat,
       o.companynumber,
       o.description,
       o.shorttext,
       o.defaulttext,
       o.longtext,
       o.sortorder,
       o.k_language_id,
       o.parent_lid,
       o.uniquename,
       osa.k_subject_area_id ,
       o.k_astat_orgunit_category_id ,
       o.obj_guid,
       o.lock_version
       from orgunit o left join orgunit_subject_area osa
       on o.lid = osa.orgunit_lid;
      
update tmp_orgunit set parent_id=(select min(O.id) from orgunit O
where tmp_orgunit.parent_lid=O.lid
   and (tmp_orgunit.valid_from >=O.valid_from
   or (tmp_orgunit.valid_from is null and O.valid_from is null))
   and (tmp_orgunit.valid_to <=O.valid_to
   or (tmp_orgunit.valid_to is null and O.valid_to is null))
   );
update tmp_orgunit set cost_center_id=(select min(C.id) from cost_center C
where tmp_orgunit.lid=C.orgunit_lid);
  
   
   

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_id integer,
    teachingunit_orgunit_lid integer
);

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

DROP TABLE IF EXISTS tmp_alternative_tree_node;
	CREATE TABLE tmp_alternative_tree_node
(
   id                   integer ,
   tree_node_type       varchar(255)  ,
   is_expanded          integer        ,
   parent_id            integer        ,
   alternative_tree_id  integer        ,
   cost_center_id       integer        ,
   financial_account_id integer,
   cost_unit_id integer,
       budget_source_id integer
);
insert into tmp_alternative_tree_node( id,
       tree_node_type,
       is_expanded,
       parent_id,
       alternative_tree_id,
       cost_center_id,
       financial_account_id,
       cost_unit_id,
       budget_source_id
       )
       select 
        id,
       tree_node_type,
       is_expanded,
       parent_id,
       alternative_tree_id,
       cost_center_id,
       financial_account_id,
       cost_unit_id,
       budget_source_id
       from alternative_tree_node;
       

   
   

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

	

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

	 
 select distinct 18,'' || $START_COB::varchar(200),'COB-Startjahr' from orgunit
 union
 select distinct 21,'6','COB_Quellsystem' from orgunit
     
	

	 
 select distinct 18,'' || $START_COB::varchar(200),'COB-Startjahr' from gxstage_inst
 union
 select distinct 21,'4','COB_Quellsystem' from gxstage_sap_sc01
     
	

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

	

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

	

select
    A.id,--busa_id ,
    accounting_year ,--busa_jahr,
    M.hiskey_id,--busa_monat,
    A.cost_center_id, --busa_instnr,
    A.cost_unit_id,--busa_projnr,
    A.financial_account_id,--busa_fikrkey ,
    'IE', --busa_bukz ,
    null::char(1), --busa_vbunr,
    null::char(1), --busa_kapitel,
    null::char(1), --busa_titel,
    round(amount::numeric,5), --busa_betrag
    $COB_UNLOAD_VERWZWECK, -- reason_for_payment, busa_verwzweck1, aus Datenschutzgründen nicht übernommen
    null::char(1), -- busa_verwzweck2,
    'HIS1', -- busa_quellsys,
    null::char(1), -- busa_quelldatnr,
    source_id,--busa_quellidnr,
    extract(day from accounting_date )::varchar(2) || '.' || extract(month from accounting_date)::varchar(2) || '.' || extract(year from accounting_date)::varchar(4)  ,--busa_buchdat
    T.hiskey_id,--busa_vbutab,
    budget_source_id, --busa_ggnr
    A.cost_center_id
    from k_month M, accounting_record A left outer join k_accounting_record_source_type T
    on (T.id=A.k_accounting_record_source_type_id)
    
where M.id=A.k_month_id
and A.accounting_year >= $START_COB
;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select
    vtbu_id,
    vtbu_jahr,
    vtbu_monat,
    vtbu_varnr,
    vtbu_quell_instnr,
    vtbu_quell_projnr,
    vtbu_quell_stug,
    vtbu_ziel_instnr,
    vtbu_ziel_projnr,
    vtbu_ziel_stug,
    vtbu_ziel_fikrkey,
    vtbu_betrag,
    vtbu_umksid,
    vtbu_zfikr,
    klr_geldgeber,
    null::char(1), --source_cost_center_id
	null::char(1), --source_cost_unit_id
	null::char(1), --source_financial_account_id
	null::char(1), --source_budget_source_id
	null::char(1), --target_cost_center_id
	null::char(1), --target_cost_unit_id
	null::char(1), --target_financial_account_id
	null::char(1), --target_budget_source_id
	null::char(1), --allocation_rule_id 
	null::char(1), --quell_fikrkey
	null::char(1), --quell_geldgeber
	null::char(1), --ziel_geldgeber
	null::char(1), --allocation_step_id
	'K' --kokl
from vtbu
where vtbu_jahr >= 
$START_COB
--and vtbu_monat=1
;
	

select
    vtbu_id,
    vtbu_jahr,
    vtbu_monat,
    vtbu_varnr,
    vtbu_quell_instnr,
    vtbu_quell_projnr,
    vtbu_quell_stug,
    vtbu_ziel_instnr,
    vtbu_ziel_projnr,
    vtbu_ziel_stug,
    vtbu_ziel_fikrkey,
    vtbu_betrag,
    vtbu_umksid,
    vtbu_zfikr,
    klr_geldgeber,
    null::char(1), --source_cost_center_id
	null::char(1), --source_cost_unit_id
	null::char(1), --source_financial_account_id
	null::char(1), --source_budget_source_id
	null::char(1), --target_cost_center_id
	null::char(1), --target_cost_unit_id
	null::char(1), --target_financial_account_id
	null::char(1), --target_budget_source_id
	null::char(1), --allocation_rule_id 
	null::char(1), --quell_fikrkey
	null::char(1), --quell_geldgeber
	null::char(1), --ziel_geldgeber
	null::char(1), --allocation_step_id
	'K' --kokl
from vtbu
where vtbu_jahr >= 
$START_COB
--and vtbu_monat=1
union
select
    id,
    jahr,
    monat,
    varnr,
    quell_instnr,
    quell_projnr,
    quell_stug,
    ziel_instnr,
    ziel_projnr,
    ziel_stug,
    ziel_fikrkey,
    betrag,
    null::integer,--umksid,
    zfikr,
    klr_geldgeber,
    null::char(1), --source_cost_center_id
	null::char(1), --source_cost_unit_id
	null::char(1), --source_financial_account_id
	null::char(1), --source_budget_source_id
	null::char(1), --target_cost_center_id
	null::char(1), --target_cost_unit_id
	null::char(1), --target_financial_account_id
	null::char(1), --target_budget_source_id
	null::char(1), --allocation_rule_id 
	null::char(1), --quell_fikrkey
	null::char(1), --quell_geldgeber
	null::char(1), --ziel_geldgeber
	null::char(1), --allocation_step_id
	'E' --kokl
from vtbu_e
where jahr >= 
$START_COB
;
	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select
    kpum_id,
    kpum_key,
    kpum_kbez,
    kpum_dbez,
    kpum_lbez,
    kpum_einhkbez,
    null::char(1),
    null::char(1),
    kpum_type
  from kpum;

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select
inst_nr,
uebinst_nr,
dname,
lname1,
key_von,
key_bis,
orgstruktur,
fins,
lehr,
bund_kategorie,
bund_lehr_forsch,
bund_fachgebiet,
land_fachgebiet,
'' as sis_fachbereich,
land_institut,
bund_rahmenplan,
ggnr

from inst
where  (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'='1'
union
select
inst_nr,
uebinst_nr,
dname,
lname1,
   date('01.01.1900'),
     date('01.01.3000'),
orgstruktur,
fins,
lehr,
bund_kategorie,
bund_lehr_forsch,
bund_fachgebiet,
land_fachgebiet,
'' as sis_fachbereich,
land_institut,
bund_rahmenplan,
ggnr
from inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'='0'
;

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select
projnr, inst_nr, haupt_kst_kz,null::char(1),null::char(1),null::char(1) from proj_to_inst;

	

SELECT U.cost_unit_id, U.cost_center_id, substring('' || U.is_primary_cost_center
from 1 for 1) as primary,
U.cost_unit_id,U.cost_center_id,U.allocation_factor
from cost_unit_cost_center U
;

     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

	

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

	

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

	

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

	

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

	

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

	

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

	

SELECT * from orgunit where 1=0;
     
	

 
	select
    key ,
    ueberg ,
    kbez,
    lbez,
    kokl,
    habpos,
    syf,
    key_von,
    key_bis
from gxstage_fikr
	

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

	

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

	

select hiskey_id,substring(defaulttext from 1 for 50),'ZeitraumCob',
'monat>=' || hiskey_id::varchar(200) || ' and monat<=' || hiskey_id::varchar(200)
from k_month 
union
select 200+termnumber,substring(shorttext from 1 for 50),'ZeitraumCob',
'monat>=' || termnumber *3-2 || ' and monat<=' || termnumber *3
from term_type
where termcategory=4 --Quartal
union select distinct accounting_year,'' || accounting_year::varchar(200),'COB-Jahr','jahr' from accounting_record
where accounting_year >= $START_COB
union
select 100+termnumber,substring(defaulttext from 1 for 50),'CobSemester',
null::char(1)
from term_type
where termcategory=2 --Semester
 order by 1 ;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

	select
projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,laufzeitbeginn,bewabl 
from proj
where (cobrel='1'
or '$COB_REL_PROJ'='0')
and '$COB_VONBIS_PROJ'='1'
union
--ohne Zeitbezug:
select
projnr,ueberkey,ktext,ltxt1,fins,lehr,key_extkotr,null::date as laufzeitbeginn,
null::date as bewabl 
from proj
where (cobrel='1'
or '$COB_REL_PROJ'='0')
and '$COB_VONBIS_PROJ'='0'


	

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

     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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



	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select 
stug_key,
stug_von,
   null::char(1), -- stug_fach, --nicht wichtig bzw. nicht benutzt
null::char(1), --stug_abschl, --nicht wichtig bzw. nicht benutzt
stug_dbez ,
stug_rstuz,
stug_cnw,
stug_bis ,
key_lehreinh ,
stug_plaetze,
key_extkotr,
null::char(1), --projnr
null::integer as course_of_study_id
from stug;

	

SELECT U.id,--stug_key,
C.valid_from, --stug_von,
C.subject_id::varchar(255), -- stug_fach, 
C.degree_id::varchar(255), --stug_abschl, 
C.defaulttext,--stug_dbez ,
C.regular_number_of_semesters, --stug_rstuz,
null::decimal(10,2),--stug_cnw,
C.valid_to, --stug_bis ,
T.id,--key_lehreinh ,
null::smallint,--stug_plaetze,
null::char(1),--key_extkotr
U.id , --projnr
C.id as course_of_study_id

from 
tmp_course_of_study C,
cost_unit U left outer join cost_unit_cost_center O on
(O.cost_unit_id=U.id
and O.is_primary_cost_center =1)
left outer join tmp_orgunit T on
(T.cost_center_id=O.cost_center_id)
where U.course_of_study_lid=C.lid
--KLR Studiengänge
union
SELECT distinct U.id,--stug_key,
null::date, --stug_von,
null::varchar(255), -- stug_fach, 
null::varchar(255), --stug_abschl, 
C.defaulttext,--stug_dbez ,
C.regular_number_of_semesters, --stug_rstuz,
null::decimal(10,2),--stug_cnw,
null::date, --stug_bis ,
T.id,--C.orgunit_id,--key_lehreinh ,
null::smallint,--stug_plaetze,
null::char(1),--key_extkotr
U.id , --projnr
null::integer as course_of_study_id
from 
cost_unit_study C, cost_unit U 
left outer join cost_unit_cost_center O on
(O.cost_unit_id=U.id
and O.is_primary_cost_center =1)
left outer join tmp_orgunit T on
(T.cost_center_id=O.cost_center_id)
where U.cost_unit_study_id=C.id
;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

	

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

	

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

	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select  id,name,type
	from alt_hier;
	
     
	

select 
	T.id,--id,
	T.tree_name,--name, 
	T.tree_type as type
	from alternative_tree T
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select 'fikr',hierarchie_id,key,parent_key,null::char(1)
	from alt_fikr
	union
	select 'inst',hierarchie_id,key,parent_key,null::char(1)
	from alt_inst
	union
	select 'proj',hierarchie_id,key,parent_key,null::char(1)
	from alt_proj;
	

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

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

	select 'fikr',
	T.id,--hierarchie_id,
	N.financial_account_id,--key,
	A.financial_account_id ,--N.parent_id,--parent_key,
	N.is_expanded --flags
	from alternative_tree T,alternative_tree_node N left outer join
	tmp_alternative_tree_node A on (A.id=N.parent_id)
	where 
	N.alternative_tree_id=T.id
	and T.tree_type=2 --KoA
	and N.financial_account_id is not null

	union 
	select 'inst',
	T.id,--hierarchie_id,
	N.cost_center_id,--key,
	A.cost_center_id,--parent_key,
	N.is_expanded --flags
	from alternative_tree T,alternative_tree_node N left outer join
	tmp_alternative_tree_node A on (A.id=N.parent_id)
	where 
	N.alternative_tree_id=T.id
	and T.tree_type=1 --KoSt
	and N.cost_center_id is not null
union 
	select 'proj',
	T.id,--hierarchie_id,
	N.cost_unit_id,--key,
	A.cost_unit_id,--parent_key,
	N.is_expanded --flags
	from alternative_tree T,alternative_tree_node N left outer join
	tmp_alternative_tree_node A on (A.id=N.parent_id)
	where 
	N.alternative_tree_id=T.id
	and T.tree_type=3 --Kotr
	and N.cost_unit_id is not null
union 
	select 'gege',
	T.id,--hierarchie_id,
	N.budget_source_id,--key,
	A.budget_source_id,--parent_key,
	N.is_expanded --flags
	from alternative_tree T,alternative_tree_node N left outer join
	tmp_alternative_tree_node A on (A.id=N.parent_id)
	where 
	N.alternative_tree_id=T.id
	and T.tree_type=4 --Geldgeber
	and N.budget_source_id is not null
	
	
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT tree_id, name, start_id, tree_typ, alt_hier_key
FROM trees where name is not null;
     
	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select tree_id,node_id from tree_cfg; 
	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT 
36 ,--key
0, --hs
bund_hochschule, 
kurz_hochschule, 
druck_hochschule, 
substring(text_hochschule from 1 for 100), 
land_hochschule, --astat 
null::char(1) ,--parent
bund_hochschule,--uniquename
bund_hochschule,--bund_apnr
null::char(1), --sprache
key_von, 
key_bis,
bund_hochschule ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM k_hochschule
where bund_hochschule not like '%A' and bund_hochschule not like '%B'
union
--Institut ohne Zeitbezug:
select 110,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
null::date, --key_von, 
null::date, --key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0')
and '$COB_VONBIS_INST'='0'
union
--Institut mit Zeitbezug
select 110,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
key_von, 
key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0')
and '$COB_VONBIS_INST'='1'
union
--FB ohne Zeitbezug:
select 90,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
null::date, --key_von, 
null::date, --key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where 
(
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0')
and '$COB_VONBIS_INST'='0'
and orgstruktur=20
union
--FB mit Zeitbezug
select 90,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
key_von, 
key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0')
and '$COB_VONBIS_INST'='1'
and orgstruktur=20
union
--Geldgeber mit Zeitbezug
select 212,--key
0, --hs
ggnr, 
substring(ggkurz from 1 for 10), 
ggdruck, 
substring(ggname1 from 1 for 100), 
klr_geldgeber, --astat 
ueberkey ,--parent
ggnr,--uniquename
bund_geldgeber,--bund_apnr
null::char(1), --sprache
key_von, 
key_bis,
ggnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM gege
where (cobrel='1'
or '$COB_REL_GEGE'='0') 
and '$COB_VONBIS_GEGE'='1'
union
--Geldgeber ohne Zeitbezug
select 212,--key
0, --hs
ggnr, 
substring(ggkurz from 1 for 10), 
ggdruck, 
substring(ggname1 from 1 for 100), 
klr_geldgeber, --astat 
ueberkey ,--parent
ggnr,--uniquename
bund_geldgeber,--bund_apnr
null::char(1), --sprache
null::date, --key_von, 
null::date, --key_bis,
ggnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM gege
where (cobrel='1'
or '$COB_REL_GEGE'='0') 
and '$COB_VONBIS_GEGE'='1'
union
--Kostenstelle ohne Zeitbezug: -- ACHTUNG ----wenn es hier Änderungen gibt auch in kern HLR kern_unload.xml etc ändern!
select 122,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
null::date, --key_von, 
null::date, --key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'='0'
union
--Kostenstelle mit Zeitbezug -- ACHTUNG ----wenn es hier Änderungen gibt auch in kern HLR kern_unload.xml etc ändern!
select 122,--key
0, --hs
inst_nr, 
kname, 
dname, 
substring(lname1 from 1 for 100), 
land_institut, --astat 
uebinst_nr ,--parent
inst_nr,--uniquename
bund_fachgebiet,--bund_apnr
null::char(1), --sprache
key_von, 
key_bis,
inst_nr ,--sourcesystem_id
null::char(1),--hiskey_id
orgstruktur::char(10), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM inst
where (
('$COB_REL_INST'='1' and cobrel='1')
or ('$COB_REL_INST'='2' and migrel='1')
or '$COB_REL_INST'='0') 
and '$COB_VONBIS_INST'='1'
union
--Kostenträger mit Zeitbezug
select 123,--key
0, --hs
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 100), 
substring(ltxt2 from 1 for 100), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
substring(key_ext from 1 for 10),--bund_apnr
null::char(1), --sprache
laufzeitbeginn,
bewabl,
projnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM proj
where (cobrel='1'
or '$COB_REL_PROJ'='0') 
and '$COB_VONBIS_PROJ'='1'
union
--Kostenträger ohne Zeitbezug
select 123,--key
0, --hs
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 100), 
substring(ltxt2 from 1 for 100), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
substring(key_ext from 1 for 10),--bund_apnr
null::char(1), --sprache
null::date, --laufzeitbeginn,
null::date, --bewabl,
projnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM proj
where (cobrel='1'
or '$COB_REL_PROJ'='0') 
and '$COB_VONBIS_PROJ'='0'
union
--Projekte mit Zeitbezug
select 91,--key
0, --hs
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 100), 
substring(ltxt2 from 1 for 100), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
substring(key_ext from 1 for 10),--bund_apnr
null::char(1), --sprache
laufzeitbeginn,
bewabl,
projnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM proj
where (cobrel='1'
or '$COB_REL_PROJ'='0')
and '$COB_VONBIS_PROJ'='1'
union
--Projekte ohne Zeitbezug
select 91,--key
0, --hs
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 100), 
substring(ltxt2 from 1 for 100), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
substring(key_ext from 1 for 10),--bund_apnr
null::char(1), --sprache
null::date, --laufzeitbeginn,
null::date, --bewabl,
projnr ,--sourcesystem_id
null::char(1),--hiskey_id
null::char(1), --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM proj
where (cobrel='1'
or '$COB_REL_PROJ'='0') 
and '$COB_VONBIS_PROJ'='0'
union
--Kostenarten mit Zeitbezug
select 114,--key
0, --hs
key, 
substring(kbez from 1 for 10), 
lbez, 
lbez, 
syf, --astat 
ueberg ,--parent
key,--uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
key_von,
key_bis,
key ,--sourcesystem_id
null::char(1),--hiskey_id
kokl, --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='1'
union
--Kostenarten ohne Zeitbezug
select 114,--key
0, --hs
key, 
substring(kbez from 1 for 10),  
lbez, 
lbez, 
syf, --astat 
ueberg ,--parent
key,--uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date,--key_von,
null::date,--key_bis,
key ,--sourcesystem_id
null::char(1),--hiskey_id
kokl, --struktur_c
null::char(1), --lid,
null::char(1) , --parent_lid
null::integer as sortorder
FROM fikr
where (kokl='K' or kokl='E')
and (cobrel='1'
or '$COB_REL_FIKR'='0')
and '$COB_VONBIS_FIKR'='0'
;
     
	

	
SELECT 
 90 ,--Merkmal FB
 0,--hs
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   substring(O.defaulttext from 1 for 100), 
   substring(O.longtext from 1 for 150), 
null::char(1), --astat
O.parent_id,
replace(O.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
valid_from,
valid_to,
O.id::varchar(255), --sourcesystem_id
null::char(1),--hiskey_id
20::char(5), --orgstruktur , struktur_c
lid::char(255),
parent_lid::char(255),
null::integer as sortorder
   FROM k_orgunittype T,tmp_orgunit O
   where 
   O.k_orgunittype_id=T.id
   and T.hiskey_id=4 --Fachbereich
union
SELECT 
 212 ,--Merkmal Geldgeber
 0,--hs
   '' || B.id,
   substring(B.shorttext from 1 for 10), 
   substring(B.defaulttext from 1 for 100), 
   substring(B.longtext from 1 for 150), 
null::char(1),--astat, klr_geldgeber
B.parent_budget_source_id as parent,
replace(B.uniquename,',',''), --uniquename
substring(T.uniquename from 1 for 10) as bund_apnr,
null::char(1), --sprache
B.valid_from,
B.valid_to,
B.id::varchar(255), --sourcesystem_id
null::char(1), --hiskey_id
k_budget_source_type_id::char(50) ,-- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
   FROM budget_source B left outer join k_budget_source_type T
   on (B.k_budget_source_type_id=T.id)
   
union
--Merkmal Institution: zuerst FBs, dann LE, dann Institut,dann alle anderen
--Fachbereiche
SELECT 
 110 ,
 0,--hs
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   (case when '$COB_VONBIS_INST'='0'  and O.valid_to <= current_date then substring(O.defaulttext from 1 for 84) || coalesce(' (bis ' || extract(day from valid_to) || '.' || extract(month from valid_to) || '.' || extract(year from valid_to) || ')') else substring(O.defaulttext from 1 for 100) end) as defaulttext , 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),
O.parent_id,
replace(O.uniquename,',',''), --uniquename
'' || O.subject_area_k_subject_area_id,--bund_apnr
null::char(1), --sprache
(case when '$COB_VONBIS_INST'='0'  then null::date else valid_from end) as valid_from,
(case when '$COB_VONBIS_INST'='0' then null::date else O.valid_to end) as valid_to,
O.id::varchar(255), --sourcesystem_id
'' || O.k_astat_orgunit_category_id,--hiskey_id
20::char(5), --orgstruktur , struktur_c
lid::char(255),
parent_lid::char(255),
null::integer as sortorder
FROM k_orgunittype T,tmp_orgunit O
   where 
   O.k_orgunittype_id=T.id
   and T.hiskey_id=4 --Fachbereich
   union
   --Lehreinheiten
SELECT 
 110 ,
 0,--hs
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   (case when '$COB_VONBIS_INST'='0'  and O.valid_to <= current_date then substring(O.defaulttext from 1 for 84) || coalesce(' (bis ' || extract(day from valid_to) || '.' || extract(month from valid_to) || '.' || extract(year from valid_to) || ')') else substring(O.defaulttext from 1 for 100) end) as defaulttext , 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),
O.parent_id,
replace(O.uniquename,',',''), --uniquename
'' || O.subject_area_k_subject_area_id,--bund_apnr
null::char(1), --sprache
(case when '$COB_VONBIS_INST'='0' then null::date else valid_from end) as valid_from,
(case when '$COB_VONBIS_INST'='0' then null::date else O.valid_to end) as valid_to,
O.id::varchar(255), --sourcesystem_id
'' || O.k_astat_orgunit_category_id,--hiskey_id
30::char(5), --orgstruktur , struktur_c
lid::char(255),
parent_lid::char(255),
null::integer as sortorder
   FROM k_orgunittype T,tmp_orgunit O
   where 
   O.k_orgunittype_id=T.id

   and T.hiskey_id=7 --Lehreinheit
   union
   --Institut
SELECT 
 110 ,
 0,--hs
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   (case when '$COB_VONBIS_INST'='0'  and O.valid_to <= current_date then substring(O.defaulttext from 1 for 84) || coalesce(' (bis ' || extract(day from valid_to) || '.' || extract(month from valid_to) || '.' || extract(year from valid_to) || ')') else substring(O.defaulttext from 1 for 100) end) as defaulttext , 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),
O.parent_id,
replace(O.uniquename,',',''), --uniquename
'' || O.subject_area_k_subject_area_id,--bund_apnr
null::char(1), --sprache
(case when '$COB_VONBIS_INST'='0' then null::date else valid_from end) as valid_from,
(case when '$COB_VONBIS_INST'='0' then null::date else O.valid_to end) as valid_to,
O.id::varchar(255), --sourcesystem_id
'' || O.k_astat_orgunit_category_id,--hiskey_id
40::char(5), --orgstruktur , struktur_c
lid::char(255),
parent_lid::char(255),
null::integer as sortorder
   FROM k_orgunittype T,tmp_orgunit O
   where 
   O.k_orgunittype_id=T.id
   and T.hiskey_id=5 --Institut
   union
   --alle anderen Inst.-Typen
SELECT 

 110 ,
 0,--hs
   '' || O.id,
   substring(O.shorttext from 1 for 10), 
   (case when '$COB_VONBIS_INST'='0'  and O.valid_to <= current_date then substring(O.defaulttext from 1 for 84) || coalesce(' (bis ' || extract(day from valid_to) || '.' || extract(month from valid_to) || '.' || extract(year from valid_to) || ')') else substring(O.defaulttext from 1 for 100) end) as defaulttext , 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),
O.parent_id,
replace(O.uniquename,',','') , --uniquename
'' || O.subject_area_k_subject_area_id,--bund_apnr
null::char(1), --sprache
(case when '$COB_VONBIS_INST'='0' then null::date else valid_from end) as valid_from,
(case when '$COB_VONBIS_INST'='0' then null::date else O.valid_to end) as valid_to,
O.id::varchar(255), --sourcesystem_id
'' || O.k_astat_orgunit_category_id,--hiskey_id 
null::char(50), --orgstruktur , struktur_c
lid::char(255),
parent_lid::char(255),
null::integer as sortorder
    FROM tmp_orgunit O left outer join k_orgunittype T
   on(O.k_orgunittype_id=T.id)
   where (T.hiskey_id is null or T.hiskey_id not in (4,7,5)) --Fachbereich, LE, Inst.
union
--Merkmal Kostenstelle: -- ACHTUNG ----wenn es hier Änderungen gibt auch in kern HLR kern_unload.xml etc ändern!
--zuerst orgunits
SELECT 
 122 ,
0,
'' || C.id,
 substring(O.shorttext from 1 for 10), 
   (case when '$COB_VONBIS_INST'='0'  and O.valid_to <= current_date then substring(O.defaulttext from 1 for 84) || coalesce(' (bis ' || extract(day from valid_to) || '.' || extract(month from valid_to) || '.' || extract(year from valid_to) || ')') else substring(O.defaulttext from 1 for 100) end) as defaulttext , 
   substring(O.longtext from 1 for 150), 
substring(O.astat from 1 for 10),
parent_cost_center_id, --parent_id,
replace(O.uniquename,',','') , --uniquename
'' || subject_area_k_subject_area_id,--bund_apnr
null::char(1), --sprache
 (case when '$COB_VONBIS_INST'='0' then null::date else O.valid_from end) as valid_from,
 (case when '$COB_VONBIS_INST'='0' then null::date else O.valid_to end) as valid_to,
'o_' || O.id, --sourcesystem_id
C.id::varchar(255), --hiskey_id
100::char(50), --orgstruktur , struktur_c
null::char(255), --lid::char(255),
null::char(255), --parent_lid::char(255),
null::integer as sortorder
    FROM tmp_orgunit O ,cost_center C
   
   where C.orgunit_lid=O.lid
   union
   --dann Gebäude 
SELECT 
 122 ,
0,
'' || C.id,
 substring(B.shorttext from 1 for 10), 
   substring(B.defaulttext from 1 for 100), 
   substring(B.longtext from 1 for 150), 
null::char(1), --astat
parent_cost_center_id, --parent_id,
replace(B.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
 null::date, --valid_from,
 null::date, --valid_to,
C.id::varchar(255), --sourcesystem_id
C.id::varchar(255), --hiskey_id
110::char(10), --orgstruktur , struktur_c für Gebäude
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM building B ,cost_center C
   
   where C.building_id=B.id
   union
      --dann Verrechnungskostenstellen
SELECT 
 122 ,
 0,
   '' || C.id,
 substring(B.shorttext from 1 for 10), 
   substring(B.defaulttext from 1 for 100), 
   substring(B.longtext from 1 for 150), 
null::char(1),--astat
parent_cost_center_id, --parent_id,
replace(B.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
 null::date, --valid_from,
 null::date, --valid_to,
C.id::varchar(255), --sourcesystem_id 
C.id::varchar(255),--hiskey_id
120::char(10) , --orgstruktur , struktur_c für Gebäude
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_center_clearing B ,cost_center C
   
   where C.cost_center_clearing_id=B.id
   union
   --Kostenträger mit Zeitbezug
   --zuerst Projekte :
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
'' || P.is_economical,--astat
C.parent_cost_unit_id, --parent_id,
replace(P.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
P.startdate, --valid_from,
P.enddate, --valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10), -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,project P
   
   where C.project_id=P.id
   and '$COB_VONBIS_PROJ'='1'
   union
      --dann  Verrechnungsprojekte :
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
'' || P.is_economical,--astat
C.parent_cost_unit_id, --parent_id,
replace(P.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
P.valid_from, --valid_from,
P.valid_to, --valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,cost_unit_clearing P
   
   where C.cost_unit_clearing_id=P.id
   and '$COB_VONBIS_PROJ'='1'
   union
   --Kostenträger
   --dann Studiengänge
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(L.shorttext from 1 for 10), 
   substring(L.defaulttext from 1 for 100), 
   substring(L.longtext from 1 for 150), 
null::char(1),--astat
C.parent_cost_unit_id, --parent_id,
replace(L.uniquename,'|','!'), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
L.valid_from,
L.valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
110::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,course_of_study L
   
   where C.course_of_study_lid=L.lid
   and '$COB_VONBIS_PROJ'='1'
   union
   --dann KLR-Studiengänge
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(L.shorttext from 1 for 10), 
   substring(L.defaulttext from 1 for 100), 
   substring(L.longtext from 1 for 150), 
null::char(1),--astat
C.parent_cost_unit_id, --parent_id,
replace(L.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date, --L.valid_from,
null::date, --L.valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
110::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,cost_unit_study L
   
   where C.cost_unit_study_id=L.id
   and '$COB_VONBIS_PROJ'='1'
   union
   --Kostenträger ohne Zeitbezug
   --zuerst Projekte :
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
'' || P.is_economical,--astat
C.parent_cost_unit_id, --parent_id,
replace(P.uniquename,',','') , --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date,--P.startdate, --valid_from,
null::date,--P.enddate, --valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,project P
    where C.project_id=P.id
    and '$COB_VONBIS_PROJ'='0'
   union
      --dann Verrechnungs-Projekte :
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
'' || P.is_economical,--astat
C.parent_cost_unit_id, --parent_id,
replace(P.uniquename,',','') , --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date,--P.startdate, --valid_from,
null::date,--P.enddate, --valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,cost_unit_clearing P
    where C.cost_unit_clearing_id=P.id
    and '$COB_VONBIS_PROJ'='0'
   union
   --Kostenträger
   --dann Studiengänge ohne Zeitbezug
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(L.shorttext from 1 for 10), 
   substring(L.defaulttext from 1 for 100), 
   substring(L.longtext from 1 for 150), 
null::char(1),--astat
C.parent_cost_unit_id, --parent_id,
replace(L.uniquename,'|','!') , --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date,--L.valid_from,
null::date,--L.valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
110::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,course_of_study L
   
   where C.course_of_study_lid=L.lid
and '$COB_VONBIS_PROJ'='0'
union
   --dann KLR-Studiengänge ohne Zeitbezug
   SELECT 
 123 ,
 0,
   '' || C.id,
 substring(L.shorttext from 1 for 10), 
   substring(L.defaulttext from 1 for 100), 
   substring(L.longtext from 1 for 150), 
null::char(1),--astat
C.parent_cost_unit_id, --parent_id,
replace(L.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date, --L.valid_from,
null::date, --L.valid_to,
C.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
110::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM cost_unit C ,cost_unit_study L
   
   where C.cost_unit_study_id=L.id
   and '$COB_VONBIS_PROJ'='0'
   union
   --Projekte mit Zeitbezug
   SELECT 
 91 ,
 0,
   '' || P.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
null::char(1),--astat
P.parent_project_id, --parent_id,
replace(P.uniquename,',','') , --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
P.startdate, --valid_from,
P.enddate, --valid_to,
P.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM project P
    where '$COB_VONBIS_PROJ'='1'
   union
   --Projekte ohne Zeitbezug
   SELECT 
 91 ,
 0,
   '' || P.id,
 substring(P.shorttext from 1 for 10), 
   substring(P.defaulttext from 1 for 100), 
   substring(P.longtext from 1 for 150), 
null::char(1),--astat
P.parent_project_id, --parent_id,
replace(P.uniquename,',','') , --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date, --P.startdate, --valid_from,
null::date, --P.enddate, --valid_to,
P.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
100::char(10) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM project P
     where '$COB_VONBIS_PROJ'='0'
    union
   --Kostenarten
   SELECT 
 114 ,
 0,
   '' || F.id,
 substring(F.shorttext from 1 for 10), 
   substring(F.defaulttext from 1 for 100), 
   substring(F.longtext from 1 for 150), 
null::char(1),--astat
F.parent_financial_account_id as parent,
replace(F.uniquename,',',''), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date, --valid_from,
null::date, --valid_to,
F.id::varchar(255), --sourcesystem_id 
'' || FT.hiskey_id,
FT.uniquename , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM financial_account F, k_financial_account_type FT
    where F.k_financial_account_type_id = FT.id
--    and FT.uniquename in ('R','E','M')
    --where F.account_type in ('R','E','M') --Erlöse/Kosten entspricht Revenue/Expense, M= memo gekennzeichnet, was neutral bedeutet
     union
   --KLR-Geldgeber
   SELECT 
 112 ,
 0,
   '' || K.id,
 substring(K.shorttext from 1 for 10), 
   substring(K.defaulttext from 1 for 100), 
   substring(K.longtext from 1 for 150), 
null::char(1),--astat
null::integer, --parent_id,
substring(replace(K.uniquename,',','') from 1 for 10), --uniquename
null::char(1),--bund_apnr
null::char(1), --sprache
null::date, --valid_from,
null::date, --valid_to,
K.id::varchar(255), --sourcesystem_id 
null::char(1),--hiskey_id
null::char(1) , -- struktur_c
null::char(1) as lid,
null::char(1) as parent_lid,
null::integer as sortorder
    FROM K_budget_source_type K
   ;
     
	

 
	select 91,--key
0,
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 200),
substring(ltxt1 from 1 for 255), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
null::char(1),
laufzeitbeginn,
null::date,
projnr,--sourcessystem_id
null::char(1),
null::integer, --struktur_c
null::char(1), --lid,
null::char(1),--parent_lid,
null::integer as sortorder
FROM gxstage_proj
union
	select 123,--key
0,
projnr, 
substring(ktext from 1 for 10), 
substring(ltxt1 from 1 for 200),
substring(ltxt1 from 1 for 255), 
substring(key_extkotr from 1 for 20), --astat 
ueberkey ,--parent
projnr,--uniquename
null::char(1),
laufzeitbeginn,
null::date,
projnr,--sourcessystem_id
null::char(1),
null::integer, --struktur_c
null::char(1), --lid,
null::char(1),--parent_lid,
null::integer as sortorder
FROM gxstage_proj
	union
	
	SELECT 
 90 ,0,
 inst_nr,
 substring(kname from 1 for 10),
 substring(dname from 1 for 200),
substring(lname1 from 1 for 255), 
null::char(1),
uebinst_nr,
inst_nr, --uniquename
bund_fachgebiet,
 key_von,
 key_bis,
inst_nr, --sourcesystem_id 
null::char(1),
orgstruktur, --struktur_c
null::char(1), --lid,
null::char(1),--parent_lid,
null::integer as sortorder
FROM gxstage_inst
where --mbsrel='1'
orgstruktur=20
union
select 110 ,0,
 inst_nr,
 substring(kname from 1 for 10),
 substring(dname from 1 for 200),
substring(lname1 from 1 for 255), 
null::char(1),
uebinst_nr,
inst_nr, --uniquename
bund_fachgebiet,
 key_von,
 key_bis,
inst_nr, --sourcesystem_id 
null::char(1),
orgstruktur, --struktur_c
null::char(1), --lid,
null::char(1),--parent_lid,
null::integer as sortorder
FROM gxstage_inst
--where mbsrel='1'
--and orgstruktur=20
union
select 122 ,0,
 inst_nr,
 substring(kname from 1 for 10),
 substring(dname from 1 for 200),
substring(lname1 from 1 for 255), 
null::char(1),
uebinst_nr,
inst_nr, --uniquename
bund_fachgebiet,
 key_von,
 key_bis,
inst_nr, --sourcesystem_id 
null::char(1),
orgstruktur, --struktur_c
null::char(1), --lid,
null::char(1),--parent_lid,
null::integer as sortorder

FROM gxstage_inst
union
select 110 ,0,
 inst_nr,
 substring(kname from 1 for 10),
 substring(dname from 1 for 200),
substring(lname1 from 1 for 255), 
null::char(1),
uebinst_nr,
inst_nr, --uniquename
bund_fachgebiet,
 key_von,
 key_bis,
inst_nr, --sourcesystem_id 
null::char(1),
orgstruktur , --struktur_c
null::char(1), --lid,
null::char(1) ,--parent_lid,
null::integer as sortorder
FROM gxstage_inst

union
--geldgeber		
SELECT 212 ,0,
 ggnr,
 substring(ggkurz from 1 for 10),
 substring(ggdruck from 1 for 200),
substring(ggname1 from 1 for 250), 
null::char(1),
ueberkey,
ggnr, --uniquename
null::char(1) as bund_fachgebiet,
null::date as valid_from,
 null::date as valid_till,
ggnr, --sourcesystem_id 
null::char(1),
null::integer as orgstruktur, --struktur_c
null::char(1), --lid,
null::char(1) ,--parent_lid,
null::integer as sortorder
FROM gxstage_gege
union
select
114,0,
key,
 substring(kbez from 1 for 10),
 substring(lbez from 1 for 200),
substring(lbez from 1 for 250), 
null::char(1),
ueberg,
key, --uniquename
null::char(1) as bund_fachgebiet,
key_von,
key_bis,
key, --sourcesystem_id
null::char(1),
null::integer as orgstruktur, --struktur_c
null::char(1), --lid,
null::char(1) ,--parent_lid,
null::integer as sortorder
from gxstage_fikr
;
	
	

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

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

select 0,0,701, 
substring(uniquename from 1 for 10),--kfa_key,
null::char(1),
1,
valid_from,
valid_to,
substring(shorttext from 1 for 10),--kfa_key,
substring(defaulttext from 1 for 100),--kfa_dbez,
substring(longtext from 1 for 100), --kfa_lbez,
null::char(1), --lbez2
null::char(1) --astat
FROM area_cost_type
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select 0,0,714,vari_nr,vari_ueberid,vari_nr,'1.1.1900','31.12.3000', null::char(1), vari_dbez, vari_dbez,null::char(1),null::char(1) from vari
;
     
	

select  0,0,714,
S.id,
-M.id as parent, --vari_ueberid
S.allocation_order, --sort_key
'01.01.1900',
'31.12.3000',
substring(S.step_name from 1 for 10), 
trim(S.step_name),
null::char(1),
null::char(1),
substring(S.step_name from 1 for 50) 
from allocation_step S,allocation_accounting_model M
where M.id=S.allocation_accounting_model_id
union
select distinct 0,0,714,
-M.id,
null::integer as parent, --vari_ueberid
null::integer as sort_key,
'01.01.1900',
'31.12.3000',
substring(model_name from 1 for 10), 
model_name,
null::char(1),
null::char(1),
substring(model_name from 1 for 50) 
from allocation_step S,allocation_accounting_model M
where M.id=S.allocation_accounting_model_id

;

     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT 
       instnr,
       email,
       key_ext,
       dbez_ext,
       gebnr,
       null::char(1)
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 'nix' from gxstage_sap_sc01 where 1=0;

SELECT 
       key_extkotr,
       null::char(1) ,-- parent
       text_extkotr,
       kategorie,
       fins_stugfach
FROM k_extkotr
     
	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT 
       dstnr,
       quelldatnr,
       day(periodendatum) || '.' || month(periodendatum) || '.' || year(periodendatum) ,
       gebaeudeid,
       geschossid,
       raumid,
       flaeche,
       kap_min,
       nutz_min,
       rnanr,
       rninr,
       instnr,
       instnr,
       qualkz,
       nanr,
       kfanr,
       id,
       projnr,
       imp_verz_id,
       null::char(1) --faktor nur bei HIS1
FROM imp_flaeche;
     
	

SELECT 
       dstnr,
       quelldatnr,
       to_char(periodendatum,'DD.MM.YYYY') ,
       gebaeudeid,
       geschossid,
       raumid,
       flaeche,
       kap_min,
       nutz_min,
       rnanr,
       rninr,
       instnr,
       instnr,
       qualkz,
       nanr,
       kfanr,
       id,
       projnr,
       imp_verz_id,
       null::char(1) --faktor nur bei HIS1
FROM imp_flaeche;
     
	

SELECT 
       null::char(1),--dstnr,
       rent_area_transfer_id,-- quelldatnr,
       current_date,--to_char(periodendatum,'DD.MM.YYYY') ,
       coalesce(substring(B.shorttext from 1 for 10),substring(replace(B.uniquename,',','') from 1 for 10)),--building_id,--gebaeudeid char(10)
       null::char(1),--geschossid,
       room_id,--raumid,
       area_size,--flaeche,
       null::char(1),--kap_min,
       null::char(1),--nutz_min,
       k_din277_roomuse.dinnumber,--rnanr,
       null::char(1),--rninr,
       cost_center_id,--instnr,
       cost_center_id,--instnr,
       quality_sign,--qualkz,
       null::char(1),--nanr,
       substring(A.uniquename from 1 for 2),--kfanr,
       R.id,
       R.cost_unit_id,--projnr,
       null::char(1), --imp_verz_id
       A.imputed_rent_area_factor
FROM  building B, imputed_rent_area R left outer join area_cost_type A
on (A.lid=R.area_cost_typeobj_id)
left outer join k_din277_roomuse on (k_din277_roomuse_id = k_din277_roomuse.id)
where B.id=R.building_id
;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT 
       rna_key,
       kfa_nr,
       na_nr,
       rna_dbez
FROM rna_to_nakfa;
     
	

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

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

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

select stug_key,jahr,zeit_id,stug_plaetze,stupl_id from stupl; 
		

select 0 from inst where 1=0; 
		

select S.cost_unit_id as stug_key,
P.term_year as jahr,
'' || P.term_year || T.termnumber as zeit_id,
S.capacity as stug_plaetze,
S.id as stupl_id 
from curricular_standard S, period P, term_type T
where P.id=S.period_id
and T.id=P.term_type_id;

     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

SELECT
       lvm_lekey,
       'obsolet',
       lvm_ca,
        lvm_id,
       stupl_id,
       null::integer as cnw
FROM lvm
; 
		

SELECT
       lvm_lekey,
       lvm_stugkey,
       lvm_ca,
       lvm_id,
       stupl_id,
       null::integer as cnw
FROM lvm; 
		

select A.cost_center_id as lvm_lekey,
S.cost_unit_id as lvm_stugkey,
A.curricular_share_value as lvm_ca,
A.id as lvm_id,
S.id as stupl_id,
S.curricular_standard_value 	 as cnw
       from curricular_share A, curricular_standard S
       where S.id=A.curricular_standard_id;
       
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

		 SELECT 
       stugkey,
       ff_fachkey,
       ff_gruppe,
       gf2
FROM ff_to_stug
;

		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

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

		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

		 SELECT 
       key,
       dbez,
       lbez
FROM ff_gruppe;

		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

		 SELECT 
        projnr,
       ggnr,
       foerd_kz,
       fo_kz,
       join_nr,
       info1,
       info2,
       info3
FROM prge;


		

SELECT * from orgunit where 1=0;
     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

		 SELECT * from inst where 1=0;


		

SELECT id,
       source_financial_account_id,
       target_financial_account_id,
       valid_from,
       valid_to,
       is_imputed_personnel_cost,
       is_accounting_record
FROM cost_type_bridge

     
	

 select 'nix' from gxstage_sap_sc01 where 1=0;

drop table tmp_orgunit;

drop table tmp_course_of_study;

drop table tmp_alternative_tree_node;