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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\belege\belege_eds_skr_stk.imr
TITLE,belege_eds_skr_stk.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,
	   0 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,
	   (T2."Sollbetrag") as c29,
	   (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,
	   (0) as c47,
	   T2."Client_DB" as c48,
	   CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as c49,
	   T6."Bezeichnung" as c50,
	   CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END as c51,
	   (database()) || ' - ' || 'Kandel' as c52,
	   (cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0)) 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,
	   T2."Sachkontonr_" as c60,
	   ((CASE WHEN ((((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END)) as c61,
	   CASE WHEN ((((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END as c62,
	   CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END 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,
	   (T2."MB_Makecode") as c72,
	   (substring(T1."Nr_" from 1 for 1)) as c73,
	   T1."GuV_Bilanz" as c74,
	   T2."MB_Distributionchannelcode" as c75,
	   CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END as c76,
	   T2."MB_Costcentercode" as c77,
	   T2."MB_Makecode" as c78,
	   T2."MB_Locationcode" as c79,
	   T2."MB_Costcentercode" as c80,
	   T2."MB_Distributionchannelcode" as c81,
	   T2."MB_Costunitcode" as c82,
	   T2."MB_Taxationcode" as c83,
	   T2."MB_Costunitcode" as c84,
	   T2."Sachkontonr_" || '_' || T2."Kostenstellencode" || '_STK' as c85,
	   T8."MB Makecode" as c86,
	   T8."Code" as c87,
	   T8."Description" as c88,
	   CASE WHEN (T2."MB_Costunitcode" BETWEEN '01' AND '49') THEN (T2."MB_Makecode" || '-' || T2."MB_Costunitcode") ELSE (T2."MB_Costunitcode") END as c89
from "CARLO"."import"."Sachkonto" T1,
	(((((("CARLO"."import"."Sachposten" T2 left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T3 on (T2."Geschäftsbuchungsgruppe" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Produktbuchungsgrp" T4 on (T2."Produktbuchungsgruppe" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T5."Code" = T2."Kostenstellencode") and (T5."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T6 on (T2."Filialcode" = T6."Code") and (T2."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T2."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T2."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."MB_Costunit" T8 on ((T2."MB_Costunitcode" = T8."Code") and (T2."Client_DB" = T8."Client_DB")) and (T2."MB_Makecode" = T8."MB Makecode"))
where ((T1."Nr_" = T2."Sachkontonr_") and (T1."Client_DB" = T2."Client_DB"))
and (((((not T2."Beschreibung" LIKE '%Nullstellung%') and (T2."Buchungsdatum" >= TIMESTAMP '2018-01-01 00:00:00.000')) and (T1."GuV_Bilanz" = 0)) and ((T1."Nr_" BETWEEN '800000' AND '829900') or (T1."Nr_" BETWEEN '851000' AND '851007'))) and ((((CASE WHEN ((((od_left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END))) <> 0))
order by c37 asc,c7 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_ori
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
COLUMN,77,Mb Makecode
COLUMN,78,Mb Locationcode
COLUMN,79,Mb Costcentercode
COLUMN,80,Mb Distributionchannelcode
COLUMN,81,Mb Costunitcode
COLUMN,82,Mb Taxationcode
COLUMN,83,Kostenträger
COLUMN,84,Acct Nr
COLUMN,85,Mb Makecode
COLUMN,86,Code_MB_Cost_Unit
COLUMN,87,Description_MB_Cost_Unit
COLUMN,88,Kostenträger_FIBU