C11/System/AUTOLINE/IQD/Belege/belege_eds_stk_ohne_service_aw.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\IQD\belege\belege_eds_stk_ohne_service_aw.imr
TITLE,belege_eds_stk_ohne_service_aw.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,
	   T2."Sollbetrag" as c27,
	   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 ('fehlt') ELSE (T2."Markencode") END) = 'SKODA') THEN ('SKODA') WHEN (((CASE WHEN (T2."Markencode" = '') THEN ('fehlt') ELSE (T2."Markencode") END) = 'VOLVO') or (T2."Kostenstellencode" = '70')) THEN ('VOLVO') ELSE ('OPEL') END as c49,
	   (database()) as c50,
	   (cast_float(T2."Menge")) as c51,
	   T2."Buchungsdatum" as c52,
	   '' as c53,
	   CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END as c54,
	   CASE WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) = '40') THEN ('3') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) = '41') THEN ('4') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) = '42') THEN ('5') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '6%') THEN ('7') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '8%') THEN ('8') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '7%') THEN ('9') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) LIKE '9%') THEN ('0') ELSE null END as c55,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) as c56,
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode" from 1 for 2))) END) || ' - ' || T1."Name" as c57,
	   T2."Sachkontonr_" || '_STK' as c58,
	   (CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','88','89')) 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','88','89'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END) as c59,
	   CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','88','89')) 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','88','89'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END as c60,
	   T2."Filialcode" 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 ((od_left(T1."Nr_",1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as c66,
	   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 1 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 1 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 1 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 1 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 1 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 1 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 1 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 1 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 1 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 1 for 2))) END) from 1 for 1)) <> '5')) THEN ('nein') ELSE ('ja') END as c67,
	   (od_left(T2."Kostenstellencode",2)) as c68,
	   CASE WHEN (T2."Markencode" = '') THEN ('fehlt') ELSE (T2."Markencode") END as c69,
	   'STK' as c70,
	   '3' as c71,
	   CASE WHEN (T1."Nr_" IN ('82015','82035','82115','82135','82215','82235','82415','82435','82565','82085','82185','82285','82485','82625','82635','82701')) THEN (0) ELSE (((CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','88','89')) 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','88','89'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END))) END as c72
from "CARLO"."import"."Sachkonto" T1,
	((("CARLO"."import"."Sachposten" T2 left outer join "CARLO"."import"."Kostenstelle" T3 on T3."Code" = T2."Kostenstellencode") left outer join "CARLO"."import"."Filialbezeichnung" T4 on T2."Filialcode" = T4."Code") left outer join "CARLO"."import"."Fahrzeug" T5 on T2."Fahrgestellnummer" = T5."Fahrgestellnummer")
where (T1."Nr_" = T2."Sachkontonr_")
and (((((((((T1."GuV_Bilanz" = 0) and (((CASE WHEN (((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','88','89')) 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','88','89'))) THEN (((cast_float(T2."Menge"))) * -1) ELSE (0) END)) <> 0)) and (not (od_left(T2."Sachkontonr_",2)) IN ('83','84','85','86','87'))) and (not (od_left(T2."Sachkontonr_",1)) IN ('4','7','9'))) and (not T2."Sachkontonr_" IN ('89500','89400','88810','88870','88900'))) and (not T2."Sachkontonr_" BETWEEN '88100' AND '88795')) and (not T2."Sachkontonr_" BETWEEN '89200' AND '89500')) and (not T2."Beschreibung" LIKE '%Nullstellung%')) and (T2."Buchungsdatum" >= TIMESTAMP '2016-01-01 00:00:00.000'))
order by c35 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,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_alt
COLUMN,59,Menge_3
COLUMN,60,Betrieb
COLUMN,61,Benutzer
COLUMN,62,Fahrgestellnummer
COLUMN,63,Modell_neu
COLUMN,64,Markencode
COLUMN,65,Konto KZ
COLUMN,66,Plausibilität
COLUMN,67,Marke aus KST
COLUMN,68,Marke_ori
COLUMN,69,Susa
COLUMN,70,GuV_Bilanz
COLUMN,71,Betrag