Hohenheim-Fipos - Versionsgeschichte

Bisping: neue Tabellen

2017-01-25T18:45:23Z

neue Tabellen

@@ Zeile 4: / Zeile 4: @@
 Nötige Schritte für eventuellen Neuaufbau:
+Legen Sie zwei neue Tabellen an:
 Suchen Sie eimalig eine freie tid heraus mit select max(tid)+1 from sichten und ergänzen eine Sicht:
@@ Zeile 203: / Zeile 224: @@
 drop table tmp_fipos;
 </pre>
 '''Obsolet:'''

Bisping: Umstellung auf Webservice

2017-01-25T18:32:42Z

Umstellung auf Webservice

Änderungen zeigen

Bisping am 18. Oktober 2016 um 13:26 Uhr

2016-10-18T13:26:05Z

← Nächstältere Version		Version vom 18. Oktober 2016, 13:26 Uhr
Zeile 925:		Zeile 925:
	and potyp is not null --Einzelfipos		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);		select distinct sachkonto into temp table tmp_fehlende_fipos from gxstage_konto where sachkonto not in (select key from gxstage_fipos where variante=3);
			delete from tmp_fehlende_fipos where sachkonto in (select key from gxstage_fipos where variante=1 and stats='X');

	insert into gxstage_pruefrout (datum,problem,key,aktion)		insert into gxstage_pruefrout (datum,problem,key,aktion)
	select today(),'Fipos bebucht, aber nicht in Sicht enthalten',sachkonto,'ERROR' from tmp_fehlende_fipos;		select today(),'Fipos bebucht, aber nicht in Sicht enthalten',sachkonto,'ERROR' from tmp_fehlende_fipos;
	drop table tmp_fehlende_fipos;		drop table tmp_fehlende_fipos;
	</pre>		</pre>

Bisping: /* Keine Zusammenfassung */

2016-10-18T10:09:28Z

Keine Zusammenfassung

← Nächstältere Version		Version vom 18. Oktober 2016, 10:09 Uhr
Zeile 907:		Zeile 907:
	18001 from tmp_fipos;		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)		insert into gxstage_fipos (key,ueberg,kbez,lbez,variante)

Bisping: /* Keine Zusammenfassung */

2016-10-18T10:04:49Z

Keine Zusammenfassung

← Nächstältere Version		Version vom 18. Oktober 2016, 10:04 Uhr
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;

Bisping: /* Keine Zusammenfassung */

2016-10-18T10:01:27Z

Keine Zusammenfassung

Neue Seite

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:

<pre>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
);
</pre>
Im Finanzpostionsbericht muss die Zeile<br /><#assign linkbuch="SuperXmlTabelle?tid=33140&cachingcontrol=clearmask&Jahr=<>"/><br />erweitert werden zu:<br /><#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

<pre>select distinct 'Fehler:Fipos fehlt in Sicht:'||key from gxstage_pruefrout where problem='Fipos bebucht, aber nicht in Sicht enthalten';</pre>
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

<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,ueberg,kbez,lbez) values (3,'FP1','root','Einnahmen (zahlungswirksam)','Einnahmen (zahlungswirksam)');
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,'FP4','root','Sachausgaben (zahlungswirksam)','Sachausgaben (zahlungswirksam)');
insert into gxstage_fipos (variante,key,ueberg,kbez,lbez) values (3,'FP5','root','Investitionen (zahlungswirksam)','Investitionen (zahlungswirksam)');

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;
</pre>