COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\op\Belege_KER_Umsatz_OP_Berechnung_gruppiert.imr
TITLE,Belege_KER_Umsatz_OP_Berechnung_gruppiert.imr
BEGIN SQL
select CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END as c1,
	   'Forderungen' as c2,
	   'Kundenforderungen' as c3,
	   CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END as c4,
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) <> 'NA Flotte') THEN ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) ELSE null END as c5,
	   SUM(((CASE WHEN ((od_left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T1."Amount"))))) ELSE (0) END))) as c6,
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA') THEN ((SUM(((CASE WHEN ((od_left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T1."Amount"))))) ELSE (0) END)))) / 365 * 14) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') THEN ((SUM(((CASE WHEN ((od_left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T1."Amount"))))) ELSE (0) END)))) / 365 * 21) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('GA','T&Z')) THEN ((SUM(((CASE WHEN ((od_left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T1."Amount"))))) ELSE (0) END)))) / 365 * 7) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN ((SUM(((CASE WHEN ((od_left(T1."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T1."Amount"))))) ELSE (0) END)))) / 365 * 14) ELSE (0) END as c7,
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA') THEN (14) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') THEN (21) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('GA','T&Z')) THEN (7) WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN (14) ELSE (0) END as c8,
	   CASE WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (not T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('KD-Forderungen') ELSE null END as c9,
	   CASE WHEN ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'NA Flotte') THEN ('NA') ELSE ((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END)) END as c10,
	   '1' as c11,
	   (CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) as c12
from "DMS1"."dbo"."AH Reisacher$G_L Account" T3,
	"DMS1"."dbo"."AH Reisacher$G_L Entry" T1,
	QSS."C:\GlobalCube\System\NAVISION\IQD\Belege\Konten Soll Ist Nav_2013_Umsatz_für_OP.ims" T2
where (T3."No_" = T1."G_L Account No_") and (T3."No_" = T2."Konto Nr")
and (((((((((T3."Income_Balance" = 0) and (((od_left(T1."G_L Account No_",1)) IN ('8','7','6','5')) or (T3."No_" = '44020'))) and (not T1."Description" IN ('V-ZAHL'))) and (T1."Document No_" <> 'ABSCHLUSS2008_1')) and (not T1."Document No_" IN ('ABSCHLUSS2009_1','ABSCHLUSS2009_2','ABSCHLUSS2009_3','ABSCHLUSS_1','ABSCHLUSS2011','ABSCHLUSS2012','ABSCHLUSS2013','ABSCHLUSS2014','ABSCHLUSS2015','ABSCHLUSS2016','ABSCHLUSS2017','ABSCHLUSS2018','ABSCHLUSS2019'))) and (T1."Source Code" <> 'JAHRABSCH')) and ((extract(DAY FROM (now()) - T1."Posting Date")) <= 400)) and (CASE WHEN (((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))),(cdatetime((now()) - cinterval(extract(DAY FROM (now())) - 1))) - INTERVAL '001 00:00:00.000' - (cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))))))),5)) = 0) and ((extract(YEAR FROM (cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))))) <> (extract(YEAR FROM (cdatetime((now()) - cinterval(extract(DAY FROM (now())) - 1))) - INTERVAL '001 00:00:00.000')))) THEN (99) WHEN (((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))),(cdatetime((now()) - cinterval(extract(DAY FROM (now())) - 1))) - INTERVAL '001 00:00:00.000' - (cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))))))),5)) = 0) and ((cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))) > (cdatetime((now()) - cinterval(extract(DAY FROM (now())) - 1))) - INTERVAL '001 00:00:00.000')) THEN (99) ELSE ((ymdint_extract(datetime_to_ymdint((ymdint_to_datetime(daysint_to_ymdint((cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))),(cdatetime((now()) - cinterval(extract(DAY FROM (now())) - 1))) - INTERVAL '001 00:00:00.000' - (cdatetime(T1."Posting Date" - cinterval(extract(DAY FROM T1."Posting Date") - 1))))))),5))) END BETWEEN 0 AND 11)) and (not (CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) IN ('NA Flotte')))
group by CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END,CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END,CASE WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('GWL-Forderungen') WHEN (((CASE WHEN ((T2."Bereich" = 'NA Flotte') and ((CASE WHEN (T1."Branch Code" = 'MM') THEN ('10') WHEN (T1."Branch Code" = 'VÖH') THEN ('20') WHEN (T1."Branch Code" = 'KRU') THEN ('30') WHEN (T1."Branch Code" = 'ULM') THEN ('40') WHEN (T1."Branch Code" = 'LL') THEN ('50') WHEN (T1."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) IN ('30','40','50'))) THEN ('NA') ELSE (T2."Bereich") END) = 'SC') and (not T2."Zeile" IN ('6030','6038','6070','6078','6110','6150','6190'))) THEN ('KD-Forderungen') ELSE null END
order by c1 asc,c4 asc,c9 asc

END SQL
COLUMN,0,Betrieb Nr
COLUMN,1,Stufe1
COLUMN,2,Stufe2
COLUMN,3,Stufe3_ori
COLUMN,4,Stufe4
COLUMN,5,Summe Umsatz 365
COLUMN,6,Max. Wert aus Ist-Umsatz
COLUMN,7,Soll-Fälligkeit in Tagen
COLUMN,8,Stufe5
COLUMN,9,Stufe3
COLUMN,10,Hauptbetrieb_ID
COLUMN,11,Standort_ID