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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\IQD\belege\belege_eds.imr
TITLE,belege_eds.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 (T2."Markencode" = '') THEN ('fehlt') ELSE (T2."Markencode") END as c49,
	   (database()) as c50,
	   (cast_float(T2."Menge")) as c51,
	   T2."Buchungsdatum" as c52,
	   CASE WHEN (((not T1."Nr_" LIKE '4%') and ((extract(DAY FROM (now()) - T2."Buchungsdatum")) <= 60)) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID") WHEN (((T1."Nr_" LIKE '4%') and ((extract(DAY FROM (now()) - T2."Buchungsdatum")) <= 365)) and (not T1."Nr_" IN ('49930','49950','49960','49970','49980'))) THEN (T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID") ELSE null END 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) || ' - ' || T3."Name" as c57,
	   T2."Sachkontonr_" as c58,
	   (((cast_float(T2."Betrag")))) 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 ((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 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 c68,
	   (od_left(T2."Kostenstellencode",2)) as c69,
	   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 c70,
	   (substring(T1."Nr_" from 1 for 1)) as c71,
	   T1."GuV_Bilanz" as c72,
	   T2."Filialcode" as c73,
	   CASE WHEN (T1."Nr_" IN ('82015','82035','82115','82135','82215','82235','82415','82435','82565','82085','82185','82285','82485','82625','82635')) 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 c74
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 (T2."Herkunftscode" <> 'JAHRABSCH')) and (T2."Buchungsdatum" >= TIMESTAMP '2017-01-01 00:00:00.000'))
order by c52 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_ori
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_mit_ETÜ
COLUMN,60,Betrieb_ori
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,Marke
COLUMN,70,Susa
COLUMN,71,GuV_Bilanz
COLUMN,72,Betrieb
COLUMN,73,Menge