GlobalCube
/
C11


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
							COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\nw\belege_ker_fuer_bestandsauswertung.imr
TITLE,belege_ker_fuer_bestandsauswertung.imr
BEGIN SQL
select T1."No_" as c1,
	   T1."Name" as c2,
	   T1."Account Type" as c3,
	   T1."Income_Balance" as c4,
	   T2."G_L Account No_" as c5,
	   T2."Posting Date" as c6,
	   T2."Document No_" as c7,
	   T2."Description" as c8,
	   T2."Bal_ Account No_" as c9,
	   T2."Department Code" as c10,
	   T2."Make Code" as c11,
	   T2."User ID" as c12,
	   T2."Reason Code" as c13,
	   T2."Document Date" as c14,
	   T2."Branch Code" as c15,
	   T2."Main Area" as c16,
	   T2."VIN" as c17,
	   T2."Book No_" as c18,
	   T3."Code" as c19,
	   T3."Name" as c20,
	   T2."Posting Date" as c21,
	   CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END as c22,
	   T2."G_L Account No_" as c23,
	   ((cast_float(T2."Amount"))) as c24,
	   CASE WHEN ((od_left(T2."G_L Account No_",1)) IN ('8','6')) THEN ((((cast_float(T2."Amount")))) * -1) ELSE (0) END as c25,
	   CASE WHEN (((od_left(T2."G_L Account No_",1)) IN ('7','5')) or (T2."G_L Account No_" = '44020')) THEN ((((cast_float(T2."Amount")))) * -1) ELSE (0) END as c26,
	   (cast_float(T2."Amount")) as c27,
	   CASE WHEN ((extract(DAY FROM (now()) - T2."Posting Date")) <= 60) THEN (T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."User ID") ELSE null END as c28,
	   '1' as c29,
	   (CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END) as c30,
	   (now()) as c31,
	   (od_right(T2."Department Code",2)) as c32,
	   CASE WHEN (((od_right(T2."Department Code",2))) = '20') THEN ('GA') WHEN (((od_right(T2."Department Code",2))) IN ('11','10')) THEN ('NA') ELSE ('NA') END as c33,
	   T2."Client_DB" as c34,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c35,
	   ((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END)) as c36,
	   CASE WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('10')) THEN ('MM') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('30')) THEN ('KRU') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('40')) THEN ('ULM') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('50')) THEN ('LL') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('55')) THEN ('GZ') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('60')) THEN ('AAM') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('70')) THEN ('LEH') WHEN ((((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') WHEN (T2."Branch Code" IN ('','AAM','LEH','WTB')) THEN ('60') ELSE ((od_left(T2."Department Code",2))) END))) IN ('80')) THEN ('WTB') ELSE null END as c37
from "NAVISION"."import"."G_L_Account" T1,
	("NAVISION"."import"."G_L_Entry" T2 left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB"))
where ((T1."No_" = T2."G_L Account No_") and (T1."Client_DB" = T2."Client_DB"))
and ((((((T1."Income_Balance" = 0) and (((T2."G_L Account No_" BETWEEN '81000' AND '82999') or (T2."G_L Account No_" = '88200')) or ((T2."G_L Account No_" BETWEEN '71000' AND '72999')))) and (not T2."Description" IN ('V-ZAHL'))) and (T2."Document No_" <> 'ABSCHLUSS2008_1')) and (not T2."Document No_" IN ('ABSCHLUSS2009_1','ABSCHLUSS2009_2','ABSCHLUSS2009_3','ABSCHLUSS_1','ABSCHLUSS2011','ABSCHLUSS2013','ABSCHLUSS2014','ABSCHLUSS2015','ABSCHLUSS2016','ABSCHLUSS2017','ABSCHLUSS2018'))) and ((od_year(T2."Posting Date")) = (od_year(((now()))))))

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Account Type
COLUMN,3,Income Balance
COLUMN,4,G L Account No
COLUMN,5,Posting Date
COLUMN,6,Document No
COLUMN,7,Description
COLUMN,8,Bal Account No
COLUMN,9,Department Code
COLUMN,10,Make Code
COLUMN,11,User Id
COLUMN,12,Reason Code
COLUMN,13,Document Date
COLUMN,14,Branch Code
COLUMN,15,Main Area
COLUMN,16,Vin
COLUMN,17,Book No
COLUMN,18,Code
COLUMN,19,Department_Name
COLUMN,20,Jahr
COLUMN,21,Betrieb Nr
COLUMN,22,Konto Nr
COLUMN,23,Betrag
COLUMN,24,Umsatzerlöse
COLUMN,25,VAK
COLUMN,26,Amount_1
COLUMN,27,Text
COLUMN,28,Hauptbetrieb
COLUMN,29,Standort
COLUMN,30,Heute
COLUMN,31,Department_rechts_2
COLUMN,32,Fahrzeugart
COLUMN,33,Hauptbetrieb_ID
COLUMN,34,Hauptbetrieb_Name
COLUMN,35,Standort_ID
COLUMN,36,Standort_Name