C11/System/CARLO/IQD/Belege/belege_eds.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\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,
	   (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,
	   (db_name()) as c50,
	   (T2."Menge") as c51,
	   T2."Buchungsdatum" as c52,
	   CASE WHEN (((not T1."Nr_" LIKE '4%') and ((extract(DAY FROM (getdate()) - T2."Buchungsdatum")) <= 60)) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" || ' - ' || T2."Beschreibung" || ' - ' || T2."Benutzer ID") WHEN (((T1."Nr_" LIKE '4%') and ((extract(DAY FROM (getdate()) - 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 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,
	   (((T2."Betrag"))) as c59,
	   CASE WHEN (((substring(T2."Sachkontonr_" from 1 for 2)) IN ('80','81','82','88','89')) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((T2."Menge")) * -1) WHEN (((substring(T2."Sachkontonr_" from 1 for 1)) = '8') and (not (substring(T2."Sachkontonr_" from 1 for 2)) IN ('80','81','82','88','89'))) THEN (((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 ((substring(T1."Nr_" from 1 for 1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as c67,
	   CASE WHEN (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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 (((substring(T1."Nr_" from 1 for 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,
	   (substring(T2."Kostenstellencode" from 1 for 2)) as c69,
	   CASE WHEN (T2."Kostenstellencode" BETWEEN '110' AND '190') THEN ('Citroen') WHEN (T2."Kostenstellencode" BETWEEN '210' AND '290') THEN ('GM') WHEN (T2."Kostenstellencode" IN ('31','20','70','41','30','40','10','72','71','42')) THEN ('Opel') WHEN (T2."Kostenstellencode" BETWEEN '50' AND '57') THEN ('Chevrolet') WHEN (T2."Kostenstellencode" BETWEEN '60' AND '67') THEN ('Jeep') WHEN (T2."Kostenstellencode" BETWEEN '80' AND '87') THEN ('Lotus') WHEN (T2."Kostenstellencode" BETWEEN '410' AND '470') THEN ('Peugeot') ELSE ('Allgemein') END as c70,
	   CASE WHEN (not (substring(T1."Nr_" from 1 for 1)) IN ('P','C','9')) THEN ((substring(T1."Nr_" from 1 for 1))) ELSE ((substring(T1."Nr_" from 2 for 1))) END as c71,
	   T1."GuV_Bilanz" as c72,
	   CASE WHEN ((T2."Filialcode" = '00') and ('1' = '1')) THEN ('02') ELSE (T2."Filialcode") END 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 (((substring(T2."Sachkontonr_" from 1 for 2)) IN ('80','81','82','88','89')) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((T2."Menge")) * -1) WHEN (((substring(T2."Sachkontonr_" from 1 for 1)) = '8') and (not (substring(T2."Sachkontonr_" from 1 for 2)) IN ('80','81','82','88','89'))) THEN (((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") and (T3."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T4 on (T2."Filialcode" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T5 on (T2."Fahrgestellnummer" = T5."Fahrgestellnummer") and (T2."Client_DB" = T5."Client_DB"))
where ((T1."Nr_" = T2."Sachkontonr_") and (T1."Client_DB" = T2."Client_DB"))
and (((T1."GuV_Bilanz" = 0) and (T2."Buchungsdatum" >= TIMESTAMP '2018-01-01 00:00:00.000')) and (not T2."Beschreibung" LIKE '%GuV Konten Nullstellung%'))
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