COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\belege\Belege_Bilanz_Grosskunden_STK_BWA.imr
TITLE,Belege_Bilanz_Grosskunden_STK_BWA.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   XSUM(c46 for c34) as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   XCOUNT(c13 for c34) as c53,
	   (c52 / (XCOUNT(c13 for c34))) as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   XMAX(c57 for c34) as c58,
	   CASE WHEN ((XMAX(c57 for c34)) = 'BMW') THEN ('88200_Stk') WHEN ((XMAX(c57 for c34)) = 'BMW-MINI') THEN ('88203_Stk') WHEN ((XMAX(c57 for c34)) = 'BMWI') THEN ('88270_Stk') ELSE null END as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63
from 
(select c119 as c1,
	   c118 as c2,
	   c117 as c3,
	   c116 as c4,
	   c115 as c5,
	   c114 as c6,
	   c113 as c7,
	   c112 as c8,
	   c111 as c9,
	   c79 as c10,
	   c80 as c11,
	   c110 as c12,
	   c109 as c13,
	   c108 as c14,
	   c107 as c15,
	   c106 as c16,
	   c105 as c17,
	   c104 as c18,
	   c103 as c19,
	   c102 as c20,
	   c101 as c21,
	   c100 as c22,
	   c99 as c23,
	   c98 as c24,
	   c97 as c25,
	   c96 as c26,
	   c95 as c27,
	   c94 as c28,
	   c93 as c29,
	   c92 as c30,
	   c91 as c31,
	   c90 as c32,
	   c89 as c33,
	   c69 as c34,
	   c88 as c35,
	   c87 as c36,
	   c86 as c37,
	   c85 as c38,
	   c84 as c39,
	   c83 as c40,
	   c82 as c41,
	   c81 as c42,
	   c80 as c43,
	   c71 as c44,
	   c79 as c45,
	   c78 as c46,
	   c77 as c47,
	   c71 as c48,
	   c76 as c50,
	   XMIN(c76 for c69) as c51,
	   1 as c52,
	   c75 as c55,
	   CASE WHEN (((c69 LIKE '%FK68973%') or (c69 LIKE '%FK70609%')) or (c69 LIKE '%FK72533%')) THEN (TIMESTAMP '2021-01-31 00:00:00.000') ELSE ((XMIN(c76 for c69))) END as c56,
	   c74 as c57,
	   c73 as c60,
	   c72 as c61,
	   c71 as c62,
	   c70 as c63,
	   XSUM(c78 for c69) as c64
from 
(select (T2."VIN") as c69,
	   CASE WHEN (T2."Branch Code" IN ('GÖG','AAM')) THEN ('AAM') ELSE (T2."Branch Code") END as c70,
	   (CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('10') 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') ELSE ('10') END) as c71,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c72,
	   T2."Client_DB" as c73,
	   CASE WHEN (T2."Document No_" LIKE 'VRGF%') THEN (T2."Make Code") ELSE null END as c74,
	   CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') ELSE ((od_left(T1."Department Code",2))) END as c75,
	   CASE WHEN (T2."Document No_" LIKE 'VRGF%') THEN (T2."Posting Date") ELSE null END as c76,
	   (cast_float(T2."Amount")) as c77,
	   ((cast_float(T2."Amount"))) as c78,
	   T2."G_L Account No_" as c79,
	   T2."Posting Date" as c80,
	   T3."Name" as c81,
	   T3."Code" as c82,
	   T2."Reposting to curr_ No_" as c83,
	   T2."Correction to curr_ No_" as c84,
	   T2."Corrected" as c85,
	   T2."Reposted" as c86,
	   T2."Veh_ Source Code" as c87,
	   T2."Book No_" as c88,
	   T2."Main Area" as c89,
	   T2."Branch Code" as c90,
	   T2."No_ Series" as c91,
	   T2."Source No_" as c92,
	   T2."Source Type" as c93,
	   T2."External Document No_" as c94,
	   T2."Document Date" as c95,
	   T2."Transaction No_" as c96,
	   T2."Bal_ Account Type" as c97,
	   T2."Gen_ Prod_ Posting Group" as c98,
	   T2."Gen_ Bus_ Posting Group" as c99,
	   T2."Gen_ Posting Type" as c100,
	   T2."Reason Code" as c101,
	   T2."Quantity" as c102,
	   T2."Source Code" as c103,
	   T2."User ID" as c104,
	   T2."Make Code" as c105,
	   T2."Department Code" as c106,
	   T2."Bal_ Account No_" as c107,
	   T2."Description" as c108,
	   T2."Document No_" as c109,
	   T2."Document Type" as c110,
	   T2."Entry No_" as c111,
	   T1."Last Date Modified" as c112,
	   T1."Income_Balance" as c113,
	   T1."Make Code" as c114,
	   T1."Department Code" as c115,
	   T1."Account Type" as c116,
	   T1."Search Description" as c117,
	   T1."Name" as c118,
	   T1."No_" as c119
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" = 1) and (T1."No_" = '15210'))
) D2
) D1
where ((((c64 <= c52) and (c64 >= -9999)) and (c51 IS NOT NULL)) and (c51 >= TIMESTAMP '2019-01-01 00:00:00.000'))
order by c34 asc

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Search Description
COLUMN,3,Account Type
COLUMN,4,Department Code
COLUMN,5,Make Code_Konto
COLUMN,6,Income Balance
COLUMN,7,Last Date Modified
COLUMN,8,Entry No
COLUMN,9,G L Account No
COLUMN,10,Posting Date
COLUMN,11,Document Type
COLUMN,12,Document No
COLUMN,13,Description
COLUMN,14,Bal Account No
COLUMN,15,Department Code
COLUMN,16,Make Code
COLUMN,17,User Id
COLUMN,18,Source Code
COLUMN,19,Quantity
COLUMN,20,Reason Code
COLUMN,21,Gen Posting Type
COLUMN,22,Gen Bus Posting Group
COLUMN,23,Gen Prod Posting Group
COLUMN,24,Bal Account Type
COLUMN,25,Transaction No
COLUMN,26,Document Date
COLUMN,27,External Document No
COLUMN,28,Source Type
COLUMN,29,Source No
COLUMN,30,No Series
COLUMN,31,Branch Code
COLUMN,32,Main Area
COLUMN,33,Vin
COLUMN,34,Book No
COLUMN,35,Veh Source Code
COLUMN,36,Reposted
COLUMN,37,Corrected
COLUMN,38,Correction To Curr No
COLUMN,39,Reposting To Curr No
COLUMN,40,Code
COLUMN,41,Name
COLUMN,42,Jahr_ori
COLUMN,43,Betrieb Nr
COLUMN,44,Konto Nr_ori
COLUMN,45,Betrag_ori
COLUMN,46,Amount_1
COLUMN,47,Betrieb Nr_neu_1
COLUMN,48,Summe_Betrag_VIN
COLUMN,49,Rechnungsdatum
COLUMN,50,Minmum_Rechnungsdatum
COLUMN,51,Menge_1
COLUMN,52,Menge_2
COLUMN,53,Betrag
COLUMN,54,Betrieb Nr
COLUMN,55,Jahr
COLUMN,56,Marke_GK
COLUMN,57,Maximum_Marke
COLUMN,58,Konto Nr
COLUMN,59,Hauptbetrieb_ID
COLUMN,60,Hauptbetrieb_Name
COLUMN,61,Standort_ID
COLUMN,62,Standort_Name