Zuletzt bearbeitet vor 8 Jahren
von Meikel Bisping

Hohenheim-Fipos: Unterschied zwischen den Versionen

 
Zeile 58: Zeile 58:
<pre>delete from gxstage_fipos where variante=3;
<pre>delete from gxstage_fipos where variante=3;
insert into gxstage_fipos (variante,key,kbez,lbez) values (3,'root','alle','alle');
insert into gxstage_fipos (variante,key,kbez,lbez) values (3,'root','alle','alle');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP1','root','Einnahmen (zahlungswirksam)','Einnahmen (zahlungswirksam)');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP1','root','Einnahmen','Einnahmen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP2','root','Hülsen','Hülsen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP2','root','Hülsen','Hülsen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP3','root','Personalausgaben (zahlungswirksam)','Personalausgaben (zahlungswirksam)');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP3','root','Personalausgaben','Personalausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP4','root','Sachausgaben (zahlungswirksam)','Sachausgaben (zahlungswirksam)');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP4','root','Sachausgaben','Sachausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP5','root','Investitionen (zahlungswirksam)','Investitionen (zahlungswirksam)');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP5','root','Investitionen','Investitionen');


select * into temp table tmp_fipos from gxstage_fipos where variante=1;
select * into temp table tmp_fipos from gxstage_fipos where variante=1;

Version vom 18. Oktober 2016, 10:04 Uhr

Ergänzen Sie die Datei gxstage_system_.sql um den SQL unten.


Suchen Sie eimalig eine freie tid heraus mit select max(tid)+1 from sichten und ergänzen eine Sicht: 

INSERT INTO sichten
(  tid,
  systeminfoid,
  art,
  TYPE,
  name_intern,
  name,
  sortnr,
  quelle,
  label,
  user_rechte,
  standbutton,
  gueltig_seit,
  gueltig_bis,
  aktiv
)
VALUES
(
  xxx,
  170,
  'SAP-Finanzpositions-Sicht               ',
  10,
  'mit_zwischensummen',
  'zusätzl. Zwischensummen',
  2,
  '<> select key||''-''||kbez,key,ueberg from gxstage_fipos where variante=18001 and ( key_von is null or key_von<= <> ) and (key_bis is null or key_bis >= <> ) order by key;                                                                       ',
  0,
  1,
  1,
  '1900-01-01',
  '3000-01-01',
  1
);

Im Finanzpostionsbericht muss die Zeile
<#assign linkbuch="SuperXmlTabelle?tid=33140&cachingcontrol=clearmask&Jahr=<>"/>
erweitert werden zu:
<#assign linkbuch="SuperXmlTabelle?tid=33140&cachingcontrol=clearmask&Jahr=<>&&Finanzposition-Sicht="+Finanzposition.sichtname_intern/>


Das nächtliche Script prüft, ob es bebuchte Fipos gibt, die nicht in der Sicht enthalten sind und schreibt diese in gxstage_pruefrout-Tabelle.

Sie können unter $GXSTAGE_PFAD ein Script anlegen

check_fipos.sql

mit dem Inhalt 

select distinct 'Fehler:Fipos fehlt in Sicht:'||key from gxstage_pruefrout where problem='Fipos bebucht, aber nicht in Sicht enthalten';

und dann in Ihren ETL einfügen, dass Sie eine Fehlermail erhalten, wenn FIpos fehlen.

runAndCheck.x $GXSTAGE_PFAD "DOSQL check_fipos.sql" EXIT_ON_ERROR SEND_ERRORMAIL NO_LOGMAIL fipos_pruefung 


delete from gxstage_fipos where variante=3;
insert into gxstage_fipos (variante,key,kbez,lbez) values (3,'root','alle','alle');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP1','root','Einnahmen','Einnahmen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP2','root','Hülsen','Hülsen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP3','root','Personalausgaben','Personalausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP4','root','Sachausgaben','Sachausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP5','root','Investitionen','Investitionen');

select * into temp table tmp_fipos from gxstage_fipos where variante=1;

--Einnahmen
insert into gxstage_fipos(key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       variante)
SELECT key,
       'FP1',
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       3
FROM tmp_fipos where key in ('22400000',
'22900000',
'25300000',
'25700000',
'25900000',
'40000100',
'40000101',
'40000900',
'40000901',
'48000100',
'48000110',
'48000120',
'48000130',
'48000140',
'48000150',
'48000199',
'48000200',
'48000210',
'48000220',
'48000299',
'48000300',
'48000310',
'48000320',
'48000399',
'48200000',
'48200100',
'48900000',
'48900200',
'48900300',
'48920010',
'48990900',
'50000000',
'50000100',
'50000200',
'50000210',
'50000220',
'50000230',
'50000240',
'50000250',
'50000255',
'50000260',
'50000265',
'50000270',
'50000300',
'50000400',
'50000450',
'50000500',
'50000600',
'50000700',
'50000800',
'50000900',
'50010000',
'50010005',
'50010006',
'50010010',
'50010100',
'50010105',
'50010110',
'50020000',
'50020005',
'50020010',
'50030000',
'50030005',
'50030010',
'50030100',
'50030110',
'50030200',
'50030210',
'50040000',
'50040005',
'50040010',
'50050900',
'50050905',
'50050910',
'50050920',
'50050925',
'50050930',
'50060100',
'50060105',
'50060110',
'50060900',
'50060910',
'50090000',
'50100000',
'50200000',
'50500000',
'51000000',
'51000002',
'51010000',
'51030000',
'51060000',
'51070000',
'51090000',
'51400000',
'51700000',
'51700010',
'51700100',
'51900000',
'53100200',
'53300000',
'53300100',
'53300200',
'53300250',
'53300300',
'53300400',
'53300900',
'53301000',
'53301100',
'53301200',
'53301300',
'53301400',
'53301500',
'53400000',
'53500000',
'53600000',
'53600002',
'53600100',
'53610002',
'53900000',
'53900010',
'53900090',
'54001100',
'54001200',
'54001300',
'54001310',
'54800000',
'54900001',
'54900002',
'54900003',
'54900004',
'54900009',
'54910000',
'54910100',
'54910500',
'54920000',
'54920100',
'56700000',
'56700100',
'56700200',
'57100000',
'57110000',
'57900000',
'58500100',
'59000010',
'59000020',
'59000030',
'59000040',
'59010010',
'59010020',
'59200000',
'59960000');


--Hülsen
insert into gxstage_fipos(key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       variante)
SELECT key,
       'FP2',
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       3
FROM tmp_fipos where key in (
'62001005',
'62002005',
'62003005',
'62010015',
'62010105',
'62011005',
'62012005',
'62013005',
'62100105',
'62101005',
'62102005',
'62103005',
'62110015',
'62110105',
'62111005',
'62112005',
'62113005',
'63401105',
'63401135',
'63402125',
'63402135',
'64000105',
'64001005',
'64002005',
'64003005',
'64010015',
'64010105',
'64011005',
'64012005',
'64013005',
'64700105',
'64701005',
'64702005',
'64703005',
'64710015',
'64710105',
'64711005',
'64712005',
'64713005');

--Personalausgaben
insert into gxstage_fipos(key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       variante)
SELECT key,
       'FP3',
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       3
FROM tmp_fipos where key in (
'61300210',
'61300220',
'61300230',
'62000000',
'62000100',
'62000105',
'62000200',
'62000300',
'62001000',
'62002000',
'62003000',
'62004000',
'62004100',
'62005100',
'62005110',
'62005200',
'62005300',
'62005400',
'62005500',
'62005600',
'62010000',
'62010010',
'62010100',
'62010200',
'62010300',
'62011000',
'62012000',
'62013000',
'62014000',
'62014100',
'62015100',
'62015110',
'62015200',
'62015300',
'62015400',
'62015500',
'62015600',
'62100000',
'62100100',
'62100200',
'62100300',
'62101000',
'62102000',
'62103000',
'62104000',
'62104100',
'62110000',
'62110010',
'62110100',
'62110200',
'62110300',
'62111000',
'62112000',
'62113000',
'62114000',
'62114100',
'62120000',
'62121000',
'62124000',
'62126000',
'62127000',
'62300000',
'62310000',
'62311000',
'62314000',
'62316000',
'62317000',
'62500000',
'62500010',
'62500020',
'63401100',
'63401110',
'63401120',
'63401130',
'63401200',
'63401300',
'63401400',
'63401500',
'63401600',
'63401700',
'63401800',
'63402100',
'63402110',
'63402120',
'63402130',
'63402200',
'63402300',
'63402400',
'63402500',
'63402600',
'63402800',
'63710000',
'63800000',
'64000000',
'64000100',
'64000200',
'64000300',
'64001000',
'64002000',
'64003000',
'64004000',
'64004100',
'64010000',
'64010010',
'64010100',
'64010200',
'64010300',
'64011000',
'64012000',
'64013000',
'64014000',
'64014100',
'64020000',
'64021000',
'64022000',
'64023000',
'64024000',
'64025000',
'64026000',
'64027000',
'64700000',
'64700100',
'64700200',
'64700300',
'64701000',
'64702000',
'64703000',
'64704000',
'64704100',
'64710000',
'64710010',
'64710100',
'64710200',
'64710300',
'64711000',
'64712000',
'64713000',
'64714000',
'64714100',
'64720000',
'64721000',
'64726000',
'64727000',
'64910000',
'64910100',
'64910200',
'64910300',
'65000000',
'65100100',
'65100300',
'65500000',
'65500010',
'65700001',
'65700011',
'65700100',
'65700150',
'65700200',
'65700300',
'65700900',
'65900000',
'65900100',
'65900200',
'65900210',
'65900300',
'65900500',
'65900510',
'65900550',
'65900560',
'65900570',
'65900580',
'65900800',
'65900900');

--Sachausgaben
insert into gxstage_fipos(key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       variante)
SELECT key,
       'FP4',
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       3
FROM tmp_fipos where key in ('20700000',
'20700100',
'20700900',
'20800010',
'20800011',
'20800012',
'20800013',
'20800020',
'20800021',
'20800030',
'20800031',
'20800032',
'20800040',
'20800050',
'20800060',
'20800080',
'21000000',
'21100000',
'21200000',
'21300000',
'48400100',
'48400110',
'48400120',
'48400130',
'48400140',
'48400200',
'48400300',
'26000000',
'26000099',
'26100000',
'26100099',
'26100100',
'26100199',
'26200000',
'26200010',
'26200011',
'26200020',
'26200029',
'26200100',
'26500010',
'26500020',
'26500025',
'26600000',
'26700000',
'26710000',
'26790000',
'26790300',
'26790600',
'26790900',
'26900000',
'36000101',
'36000201',
'36000601',
'36010101',
'36030101',
'36120901',
'36200101',
'36200901',
'36210901',
'36700001',
'60000100',
'60000110',
'60000200',
'60000300',
'60000400',
'60100100',
'60100110',
'60100111',
'60100112',
'60100113',
'60100130',
'60100131',
'60100132',
'60100140',
'60100200',
'60100300',
'60100310',
'60100400',
'60100500',
'60100700',
'60100900',
'60200100',
'60200200',
'60300100',
'60400000',
'60500000',
'60500100',
'60500110',
'60500200',
'60500300',
'60500400',
'60600000',
'60700010',
'60700090',
'60700100',
'60700200',
'60700600',
'60700900',
'60800000',
'60800100',
'60900000',
'60999999',
'61200200',
'61200300',
'61200400',
'61300100',
'61300150',
'61300200',
'61300290',
'61300300',
'61300600',
'61300700',
'61300900',
'61400000',
'61500000',
'61600010',
'61600020',
'61620010',
'61620020',
'61620110',
'61620120',
'61620910',
'61620920',
'61700000',
'61700100',
'61700200',
'61700500',
'61700900',
'61800000',
'65000100',
'65100000',
'65100200',
'65200000',
'65200010',
'65400000',
'66100000',
'66200000',
'66310000',
'66390000',
'66400000',
'66410000',
'66490000',
'66500010',
'66500020',
'66500030',
'66500040',
'66800000',
'67000000',
'67000010',
'67000100',
'67000150',
'67000200',
'67000300',
'67000310',
'67100100',
'67100200',
'67100300',
'67100310',
'67200000',
'67200100',
'67300000',
'67300010',
'67300020',
'67300090',
'67500000',
'67600000',
'67700000',
'67700100',
'67700200',
'67800000',
'67900000',
'67900010',
'67900020',
'67900030',
'68000000',
'68000200',
'68000300',
'68100000',
'68100100',
'68100200',
'68100300',
'68100400',
'68100500',
'68200000',
'68300000',
'68300900',
'68500000',
'68500100',
'68500200',
'68500300',
'68500400',
'68600000',
'68600200',
'68600800',
'69000000',
'69010000',
'69100000',
'69110000',
'69200000',
'69301000',
'69302000',
'69305000',
'69305100',
'69305200',
'69305300',
'69305400',
'69305500',
'69309000',
'69500000',
'69600000',
'69900000',
'69900500',
'70200000',
'70300000',
'70700000',
'70900100',
'70900900',
'71100000',
'71700000',
'71700090',
'73000100',
'73000110',
'73000200',
'73000210',
'73000400',
'73100010',
'73100100',
'73100900',
'74000000',
'74500000',
'75110000',
'75900000',
'77000000',
'77100000',
'77200000',
'77300000',
'77990000',
'79100000',
'79670000',
'79690000',
'79700000',
'90/1',
'91000100',
'91000150',
'91000200',
'91000210',
'91000220',
'91000230',
'91000240',
'91000250',
'91000300',
'91000310',
'91000320',
'91000330',
'91000340',
'91000350',
'91000360',
'91000370',
'91000380');


--Investionen
insert into gxstage_fipos(key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       variante)
SELECT key,
       'FP5',
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       3
FROM tmp_fipos where key in ('13000000',
'13010000',
'16000000',
'19000011',
'19000099',
'02200000',
'02200001',
'02400000',
'02400001',
'02500000',
'02500001',
'03100000',
'03800000',
'04000010',
'04000011',
'05300000',
'05300001',
'05500000',
'05500001',
'05700000',
'05700001',
'06200000',
'06200001',
'06220000',
'06220001',
'06500000',
'06500001',
'06500010',
'06500011',
'07000000',
'07000001',
'07200000',
'07200001',
'07300000',
'07300001',
'07300010',
'07300011',
'07300020',
'07300021',
'07300030',
'07300031',
'07900000',
'07900001',
'08000000',
'08000001',
'08000010',
'08000011',
'08100000',
'08100001',
'08200000',
'08200001',
'08400000',
'08400001',
'08400010',
'08400011',
'08400090',
'08400091',
'08510000',
'08510001',
'08590000',
'08590001',
'08600000',
'08600001',
'08700000',
'08700001',
'08800000',
'08800001',
'08800010',
'08800011',
'08800020',
'08900000',
'08900001',
'09000000',
'09000010',
'09000011',
'09500000',
'09500001',
'09500400',
'09500401');

delete from gxstage_fipos where variante=3 and stats='X';
drop table tmp_fipos;
select * into temp table tmp_fipos from gxstage_fipos where variante=3;

delete from gxstage_fipos where variante=18001;
insert into gxstage_fipos select key,
       ueberg,
       fipup,
       kbez,
       lbez,
       key_von,
       key_bis,
       stats,
       potyp,
       kokl,
       18001 from tmp_fipos;
       
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP1','root','Einnahmen','Einnahmen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP2','root','Hülsen','Hülsen');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP3','root','Personalausgaben','Personalausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP4','root','Sachausgaben','Sachausgaben');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP5','root','Investitionen','Investitionen');



insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)
select distinct ueberg||'.'||substring(key from 1 for 1),ueberg,'Einnahmen '||substring(key from 1 for 1)||'*','Einnahmen '||substring(key from 1 for 1)||'*',18001 from tmp_fipos where ueberg='FP1';
--bei Hülsen und Personalausgaben gibts derzeit nur eine Hauptgruppe
--insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)
--select distinct ueberg||'.'||substring(key from 1 for 1),ueberg,'Hülsen '||substring(key from 1 for 1)||'*','Hülsen '||substring(key from 1 for 1)||'*',18001 from tmp_fipos where ueberg='FP2';
--insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)
--select distinct ueberg||'.'||substring(key from 1 for 1),ueberg,'Personalausgaben '||substring(key from 1 for 1)||'*','Personalausgaben '||substring(key from 1 for 1)||'*',18001 from tmp_fipos where ueberg='FP3';
insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)
select distinct ueberg||'.'||substring(key from 1 for 1),ueberg,'Sachausgaben '||substring(key from 1 for 1)||'*','Sachausgaben '||substring(key from 1 for 1)||'*',18001 from tmp_fipos where ueberg='FP4';
insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)
select distinct ueberg||'.'||substring(key from 1 for 1),ueberg,'Investitionen '||substring(key from 1 for 1)||'*','Investitionen '||substring(key from 1 for 1)||'*',18001 from tmp_fipos where ueberg='FP5';

drop table tmp_fipos;
update gxstage_fipos set ueberg=ueberg||'.'||substring(key from 1 for 1) where variante=18001  
and ueberg not in ('FP3','FP3')
and potyp is not null --Einzelfipos
;
select distinct sachkonto into temp table tmp_fehlende_fipos from gxstage_konto where sachkonto not in (select key from gxstage_fipos where variante=3);
insert into gxstage_pruefrout (datum,problem,key,aktion)
select today(),'Fipos bebucht, aber nicht in Sicht enthalten',sachkonto,'ERROR' from tmp_fehlende_fipos;
drop table tmp_fehlende_fipos;
Abgerufen von „https://superxhosting.de/wiki/index.php?title=Hohenheim-Fipos&oldid=1946“