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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\Belege_EDS_Germersheim.imr
TITLE,Belege_EDS_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,
	   T3."Beschreibung" as c24,
	   T2."Produktbuchungsgruppe" as c25,
	   T4."Beschreibung" as c26,
	   T2."Gegenkontoart" as c27,
	   T2."Transaktionsnr_" as c28,
	   (cast_float(T2."Sollbetrag")) as c29,
	   (cast_float(T2."Habenbetrag")) as c30,
	   T2."Belegdatum" as c31,
	   T2."Externe Belegnummer" as c32,
	   T2."Herkunftsart" as c33,
	   T2."Herkunftsnr_" as c34,
	   T2."Filialcode" as c35,
	   T2."Hauptbereich" as c36,
	   T2."Fahrgestellnummer" as c37,
	   T2."Buchnummer" as c38,
	   T2."Fahrzeug-Kz" as c39,
	   T2."Umgebucht" as c40,
	   T2."Storniert" as c41,
	   T2."Fahrzeugklassecode" as c42,
	   T5."Code" as c43,
	   T5."Name" as c44,
	   T6."Code" as c45,
	   T6."Bezeichnung" as c46,
	   (cast_float(T2."Betrag")) as c47,
	   '1' as c48,
	   T2."Filialcode" as c49,
	   T6."Bezeichnung" as c50,
	   CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END as c51,
	   (database()) || ' - ' || 'Germersheim' as c52,
	   (cast_float(T2."Menge")) as c53,
	   T2."Buchungsdatum" as c54,
	   CASE WHEN (((extract(DAY FROM (now()) - T2."Buchungsdatum")) <= 90) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID") ELSE null END as c55,
	   CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END as c56,
	   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 c57,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) as c58,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 3 for 2))) END) || ' - ' || T5."Name" as c59,
	   (od_left(T2."Sachkontonr_",5)) as c60,
	   (((cast_float(T2."Betrag")))) as c61,
	   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 c62,
	   '04' as c63,
	   T2."Benutzer ID" as c64,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Fahrgestellnummer") ELSE null END as c65,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Modell") ELSE null END as c66,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Markencode") ELSE null END as c67,
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Verkäufer Nr_ Verkauf") ELSE null END as c68,
	   CASE WHEN ((od_left(T1."Nr_",1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as c69,
	   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 c70,
	   (od_left(T2."Kostenstellencode",2)) as c71,
	   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 c72,
	   (substring(T1."Nr_" from 1 for 1)) as c73,
	   T1."GuV_Bilanz" as c74,
	   CASE WHEN ((od_left(T3."Beschreibung",2)) IS NULL) THEN ('00') ELSE ((od_left(T3."Beschreibung",2))) END as c75,
	   CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END as c76,
	   CASE WHEN ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END) = '41') THEN ('43') WHEN ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END) = '42') THEN ('44') WHEN ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END) = '50') THEN ('51') WHEN ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END) = '60') THEN ('61') WHEN ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END) = '31') THEN ('30') ELSE ((CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END)) END as c77
from "DE0682"."dbo"."Tretter Germersheim$Sachkonto" T1,
	((((("DE0682"."dbo"."Tretter Germersheim$Sachposten" T2 left outer join "DE0682"."dbo"."Tretter Germersheim$Geschäftsbuchungsgrp_" T3 on T2."Geschäftsbuchungsgruppe" = T3."Code") left outer join "DE0682"."dbo"."Tretter Germersheim$Produktbuchungsgrp_" T4 on T2."Produktbuchungsgruppe" = T4."Code") left outer join "DE0682"."dbo"."Tretter Germersheim$Kostenstelle" T5 on T5."Code" = T2."Kostenstellencode") left outer join "DE0682"."dbo"."Tretter Germersheim$Filialbezeichnung" T6 on T2."Filialcode" = T6."Code") left outer join "DE0682"."dbo"."Tretter Germersheim$Fahrzeug" T7 on T2."Fahrgestellnummer" = T7."Fahrgestellnummer")
where (T1."Nr_" = T2."Sachkontonr_")
and (((((T1."GuV_Bilanz" = 0) and (not T2."Beschreibung" LIKE '%Nullstellung%')) and (T2."Buchungsdatum" = TIMESTAMP '2015-12-31 00:00:00.000')) and (not T1."Nr_" LIKE '5%')) and (T1."Nr_" = '74300'))
order by c9 asc

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,Geschäftsbuchungsgruppe_Beschreibung
COLUMN,24,Produktbuchungsgruppe
COLUMN,25,Produktbuchungsgruppe_Beschreibung
COLUMN,26,Gegenkontoart
COLUMN,27,Transaktionsnr
COLUMN,28,Sollbetrag
COLUMN,29,Habenbetrag
COLUMN,30,Belegdatum
COLUMN,31,Externe Belegnummer
COLUMN,32,Herkunftsart
COLUMN,33,Herkunftsnr
COLUMN,34,Filialcode
COLUMN,35,Hauptbereich
COLUMN,36,Fahrgestellnummer
COLUMN,37,Buchnummer
COLUMN,38,Fahrzeug-kz
COLUMN,39,Umgebucht
COLUMN,40,Storniert
COLUMN,41,Fahrzeugklassecode
COLUMN,42,Code
COLUMN,43,Name
COLUMN,44,Code
COLUMN,45,Bezeichnung
COLUMN,46,Betrag_1
COLUMN,47,Rechtseinheit
COLUMN,48,Betrieb_Nr
COLUMN,49,Betrieb_1
COLUMN,50,Marke_ori
COLUMN,51,Mandant
COLUMN,52,Menge_1
COLUMN,53,Bookkeep Date
COLUMN,54,Text
COLUMN,55,KST_aus_Code
COLUMN,56,Kostenstelle_ori
COLUMN,57,KST_1
COLUMN,58,KST_2
COLUMN,59,Acct Nr
COLUMN,60,Betrag
COLUMN,61,Menge
COLUMN,62,Betrieb
COLUMN,63,Benutzer
COLUMN,64,Fahrgestellnummer
COLUMN,65,Modell_neu
COLUMN,66,Markencode
COLUMN,67,Verkäufer Nr Verkauf
COLUMN,68,Konto KZ
COLUMN,69,Plausibilität
COLUMN,70,Marke aus KST
COLUMN,71,Marke
COLUMN,72,Susa
COLUMN,73,GuV_Bilanz
COLUMN,74,Absatzkanal_Code_
COLUMN,75,KST_ori
COLUMN,76,KST