GAPS/Portal/System/IQD/Belege/Belege_EDS_Bilanz_Germersheim.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\Belege_EDS_Bilanz_Germersheim.imr
TITLE,Belege_EDS_Bilanz_Germersheim.imr
BEGIN SQL
select T1."Nr_" as c1,
	   T1."Name" as c2,
	   T1."Kontoart" as c3,
	   T1."GuV_Bilanz" as c4,
	   T1."Soll_Haben" as c5,
	   T1."Kostenstellen Buchung" as c6,
	   T2."Lfd_ Nr_" as c7,
	   T2."Sachkontonr_" as c8,
	   T2."Buchungsdatum" as c9,
	   T2."Belegart" as c10,
	   T2."Belegnr_" as c11,
	   T2."Beschreibung" as c12,
	   T2."Gegenkontonr_" as c13,
	   T2."Betrag" as c14,
	   T2."Kostenstellencode" as c15,
	   T2."Markencode" as c16,
	   T2."Benutzer ID" as c17,
	   T2."Herkunftscode" as c18,
	   T2."Nachbuchung" as c19,
	   T2."Menge" as c20,
	   T2."MWSt Betrag" as c21,
	   T2."Buchungsart" as c22,
	   T2."Geschäftsbuchungsgruppe" as c23,
	   T2."Produktbuchungsgruppe" as c24,
	   T2."Gegenkontoart" as c25,
	   T2."Transaktionsnr_" as c26,
	   (cast_float(T2."Sollbetrag")) as c27,
	   (cast_float(T2."Habenbetrag")) as c28,
	   T2."Belegdatum" as c29,
	   T2."Externe Belegnummer" as c30,
	   T2."Herkunftsart" as c31,
	   T2."Herkunftsnr_" as c32,
	   T2."Filialcode" as c33,
	   T2."Hauptbereich" as c34,
	   T2."Fahrgestellnummer" as c35,
	   T2."Buchnummer" as c36,
	   T2."Fahrzeug-Kz" as c37,
	   T2."Umgebucht" as c38,
	   T2."Storniert" as c39,
	   T2."Fahrzeugklassecode" as c40,
	   T3."Code" as c41,
	   T3."Name" as c42,
	   T4."Code" as c43,
	   T4."Bezeichnung" as c44,
	   (cast_float(T2."Betrag")) as c45,
	   '1' as c46,
	   T2."Filialcode" as c47,
	   T4."Bezeichnung" as c48,
	   CASE WHEN ((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) IN ('VOLKSWAGEN','VW')) THEN ('01 - VW') WHEN ((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) = 'SKODA') THEN ('04 - Skoda') WHEN ((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) = 'OPEL') THEN ('52 - Opel') WHEN ((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) = 'RENAULT') THEN ('55 - Renault') WHEN (((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) = '00 - ohne Marke') or ((CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END) IS NULL)) THEN ('00 - ohne Marke') ELSE ('99 - Fremdmarke') END as c49,
	   (database()) || ' - ' || 'Germersheim' as c50,
	   (cast_float(T2."Menge")) as c51,
	   T2."Buchungsdatum" as c52,
	   CASE WHEN (((extract(DAY FROM (now()) - T2."Buchungsdatum")) <= 30) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID") ELSE null END as c53,
	   CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END as c54,
	   CASE WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '41') THEN ('3') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '44') THEN ('4') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) = '45') THEN ('5') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '5%') THEN ('7') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) LIKE '9%') THEN ('0') ELSE null END as c55,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) as c56,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) || ' - ' || T3."Name" as c57,
	   T2."Sachkontonr_" as c58,
	   CASE WHEN (((od_left(T2."Sachkontonr_",1)) = '8') or (T2."Sachkontonr_" IN ('20000','90000','90001','90008','90009','92000','95000','95009','95194','96004','97000','97001','97009','99000','99004','99008','99009','99104','99204','99304','99510','99514','99900','99904','93000','93050','94100','94200','94204','94304','94450','94454','94550','94554','94600','94604','94650','94654','94704','94804','94904'))) THEN (((cast_float(T2."Betrag"))) * -1) ELSE (((cast_float(T2."Betrag")))) END as c59,
	   CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82')) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cast_float(T2."Menge"))) * -1) WHEN (((od_left(T2."Sachkontonr_",1)) = '8') and (not (od_left(T2."Sachkontonr_",2)) IN ('80','81','82'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END as c60,
	   '04' as c61,
	   T2."Benutzer ID" as c62,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T5."Fahrgestellnummer") ELSE null END as c63,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T5."Modell") ELSE null END as c64,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T5."Markencode") ELSE null END as c65,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T5."Verkäufer Nr_ Verkauf") ELSE null END as c66,
	   CASE WHEN ((od_left(T1."Nr_",1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as c67,
	   CASE WHEN (((od_left(T1."Nr_",2)) IN ('70','71','80','81')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '1')) THEN ('nein') WHEN (((od_left(T1."Nr_",3)) IN ('880','881','890','891')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '1')) THEN ('nein') WHEN (((od_left(T1."Nr_",2)) IN ('72','82')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '2')) THEN ('nein') WHEN (((od_left(T1."Nr_",3)) IN ('882','892')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '2')) THEN ('nein') WHEN (((od_left(T1."Nr_",2)) IN ('73','83')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '3')) THEN ('nein') WHEN (((od_left(T1."Nr_",3)) IN ('883','893')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '3')) THEN ('nein') WHEN (((od_left(T1."Nr_",2)) IN ('74','75','84','85')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '4')) THEN ('nein') WHEN (((od_left(T1."Nr_",3)) IN ('884','885','894','899')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '4')) THEN ('nein') WHEN (((od_left(T1."Nr_",2)) IN ('76','86')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '5')) THEN ('nein') WHEN (((od_left(T1."Nr_",3)) IN ('896')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) from 1 for 1)) <> '5')) THEN ('nein') ELSE ('ja') END as c68,
	   (od_left(T2."Kostenstellencode",2)) as c69,
	   ((substring(T1."Nr_" from 1 for 1))) as c70,
	   T1."GuV_Bilanz" as c71,
	   CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END as c72
from "DE0682"."dbo"."Tretter Germersheim$Sachkonto" T1,
	((("DE0682"."dbo"."Tretter Germersheim$Sachposten" T2 left outer join "DE0682"."dbo"."Tretter Germersheim$Kostenstelle" T3 on T3."Code" = T2."Kostenstellencode") left outer join "DE0682"."dbo"."Tretter Germersheim$Filialbezeichnung" T4 on T2."Filialcode" = T4."Code") left outer join "DE0682"."dbo"."Tretter Germersheim$Fahrzeug" T5 on T2."Fahrgestellnummer" = T5."Fahrgestellnummer")
where (T1."Nr_" = T2."Sachkontonr_")
and (((T1."GuV_Bilanz" = 1) and (not T1."Nr_" LIKE '5%')) and (T1."Nr_" <> '08380'))

END SQL
COLUMN,0,Nr
COLUMN,1,Name
COLUMN,2,Kontoart
COLUMN,3,Guv Bilanz
COLUMN,4,Soll Haben
COLUMN,5,Kostenstellen Buchung
COLUMN,6,Lfd Nr
COLUMN,7,Sachkontonr
COLUMN,8,Buchungsdatum
COLUMN,9,Belegart
COLUMN,10,Belegnr
COLUMN,11,Beschreibung
COLUMN,12,Gegenkontonr
COLUMN,13,Betrag_ori
COLUMN,14,Kostenstellencode
COLUMN,15,Markencode
COLUMN,16,Benutzer Id
COLUMN,17,Herkunftscode
COLUMN,18,Nachbuchung
COLUMN,19,Menge_ori
COLUMN,20,Mwst Betrag
COLUMN,21,Buchungsart
COLUMN,22,Geschäftsbuchungsgruppe
COLUMN,23,Produktbuchungsgruppe
COLUMN,24,Gegenkontoart
COLUMN,25,Transaktionsnr
COLUMN,26,Sollbetrag
COLUMN,27,Habenbetrag
COLUMN,28,Belegdatum
COLUMN,29,Externe Belegnummer
COLUMN,30,Herkunftsart
COLUMN,31,Herkunftsnr
COLUMN,32,Filialcode
COLUMN,33,Hauptbereich
COLUMN,34,Fahrgestellnummer
COLUMN,35,Buchnummer
COLUMN,36,Fahrzeug-kz
COLUMN,37,Umgebucht
COLUMN,38,Storniert
COLUMN,39,Fahrzeugklassecode
COLUMN,40,Code
COLUMN,41,Name
COLUMN,42,Code
COLUMN,43,Bezeichnung
COLUMN,44,Betrag_1
COLUMN,45,Rechtseinheit
COLUMN,46,Betrieb_Nr
COLUMN,47,Betrieb_1
COLUMN,48,Marke
COLUMN,49,Mandant
COLUMN,50,Menge_1
COLUMN,51,Bookkeep Date
COLUMN,52,Text
COLUMN,53,KST_aus_Code
COLUMN,54,Kostenstelle_ori
COLUMN,55,KST_1
COLUMN,56,KST_2
COLUMN,57,Acct Nr
COLUMN,58,Betrag
COLUMN,59,Menge
COLUMN,60,Betrieb
COLUMN,61,Benutzer
COLUMN,62,Fahrgestellnummer
COLUMN,63,Modell_neu
COLUMN,64,Markencode
COLUMN,65,Verkäufer Nr Verkauf
COLUMN,66,Konto KZ
COLUMN,67,Plausibilität
COLUMN,68,Marke aus KST
COLUMN,69,Susa
COLUMN,70,GuV_Bilanz
COLUMN,71,Marke_ori