COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\belege\belege_bank.imr
TITLE,belege_bank.imr
BEGIN SQL
select T1."No_" as c1,
	   T1."Name" as c2,
	   T1."Search Description" as c3,
	   T1."Account Type" as c4,
	   T1."Department Code" as c5,
	   T1."Make Code" as c6,
	   T1."Income_Balance" as c7,
	   T1."Last Date Modified" as c8,
	   T2."Entry No_" as c9,
	   T2."G_L Account No_" as c10,
	   T2."Posting Date" as c11,
	   T2."Document Type" as c12,
	   T2."Document No_" as c13,
	   T2."Description" as c14,
	   T2."Bal_ Account No_" as c15,
	   T2."Department Code" as c16,
	   T2."Make Code" as c17,
	   T2."User ID" as c18,
	   T2."Source Code" as c19,
	   T2."Quantity" as c20,
	   T2."Reason Code" as c21,
	   T2."Gen_ Posting Type" as c22,
	   T2."Gen_ Bus_ Posting Group" as c23,
	   T2."Gen_ Prod_ Posting Group" as c24,
	   T2."Bal_ Account Type" as c25,
	   T2."Transaction No_" as c26,
	   T2."Document Date" as c27,
	   T2."External Document No_" as c28,
	   T2."Source Type" as c29,
	   T2."Source No_" as c30,
	   T2."No_ Series" as c31,
	   T2."Branch Code" as c32,
	   T2."Main Area" as c33,
	   T2."VIN" as c34,
	   T2."Book No_" as c35,
	   T2."Veh_ Source Code" as c36,
	   T2."Reposted" as c37,
	   T2."Corrected" as c38,
	   T2."Correction to curr_ No_" as c39,
	   T2."Reposting to curr_ No_" as c40,
	   T3."Code" as c41,
	   T3."Name" as c42,
	   T2."Posting Date" as c43,
	   'AHR' as c44,
	   CASE WHEN ((T2."G_L Account No_" IN ('12410','12380','12390')) and (T2."Branch Code" = 'MM')) THEN ('MM 12370 - 12450') WHEN ((T2."G_L Account No_" IN ('12410','12380','12390')) and (T2."Branch Code" = 'GZ')) THEN ('GZ 12370 - 12450') WHEN ((T2."G_L Account No_" IN ('12410','12380','12390')) and (T2."Branch Code" = 'KRU')) THEN ('KRU 12370 - 12450') WHEN ((T2."G_L Account No_" IN ('12410','12380','12390')) and (T2."Branch Code" = 'ULM')) THEN ('ULM 12370 - 12450') WHEN ((T2."G_L Account No_" IN ('12410','12380','12390')) and (T2."Branch Code" = 'LL')) THEN ('LL 12370 - 12450') ELSE (T2."G_L Account No_") END as c45,
	   ((cast_float(T2."Amount"))) as c46,
	   (cast_float(T2."Amount")) as c47,
	   CASE WHEN (T1."No_" = '12600') THEN ('Rahmenlinie ausnutzbar mit KK oder EuroKredite') WHEN (T1."No_" = '00725') THEN ('CHF 366.700,00') WHEN (T1."No_" = '07160') THEN ('CHF 640.000,00') WHEN (T1."No_" = '12500') THEN ('Rahmenlinie für Gebrauchtwagen inkl. T€ 300 ÜZ-Linie ausnutzbar mit KK oder Eurokredite (GMK)') WHEN (T1."No_" = '07240') THEN ('CHF 313.000,00') WHEN (T1."No_" = '07640') THEN ('CHF 1.328.240,00') ELSE null END as c48,
	   CASE WHEN (T1."No_" = '12600') THEN ('TEUR 1.815 Grundschulden auf Europastrasse 1, MM / (TDM 250, 2.300, 500 und 500) und TEUR 256 sBü PR') WHEN (T1."No_" = '07260') THEN ('Grundschulden und sBü PR w.o.') WHEN (T1."No_" = '07180') THEN ('Grundschulden und sBü PR w.o.') WHEN (T1."No_" IN ('12500','07500','07550','07590')) THEN ('SÜ GA-Bestand a, Standort Europastrasse 1, MM und sämtliche sonstigen der LBBW gewährten Sicherheiten') WHEN (T1."No_" IN ('12410','12380','12390')) THEN ('NA + GA; selbstschuldnerische Höchstbürgschaft von PR TEUR 500') WHEN (T1."No_" IN ('07240','07300','07600')) THEN ('Grundschulden und sBü PR w.o.') WHEN (T1."No_" IN ('07510','07190')) THEN ('TEUR 2.045 Grundschulden auf Europastrasse 1, MM (TDM 1.000, 1.000, 750, 1.000 und 250) Sbü PR') WHEN (T1."No_" = '07020') THEN ('LV-Tilg. zum 30.08.2011') WHEN (T1."No_" = '07140') THEN ('TEUR 16,87Annuität p.a., Laufzeit bis 31.03.2005') WHEN (T1."No_" = '07150') THEN ('Raum-Sich.übereig. Inv. Ulm gem. Vertrag v. 27.07.11 Selbstschuldn. Bürgsch. ü. €700.000,00 von PR') WHEN (T1."No_" IN ('12180','07170')) THEN ('Sicherungübertragung NA + VFZ. Selbsstschuldnerische Höchstbürgschaft PR TEUR 500') ELSE null END as c49,
	   CASE WHEN (T1."No_" = '07250') THEN ('LZ 11/2005') WHEN (T1."No_" = '07160') THEN ('LZ 12/2007') WHEN (T1."No_" = '07260') THEN ('LZ 11/2007') WHEN (T1."No_" = '07500') THEN ('LZ 01/2008') WHEN (T1."No_" = '07550') THEN ('LZ 12/2006') WHEN (T1."No_" = '07590') THEN ('LZ 11/2007') WHEN (T1."No_" = '07580') THEN ('LZ 11/2007') WHEN (T1."No_" = '07240') THEN ('LZ 01/2008') WHEN (T1."No_" = '07300') THEN ('LZ 01/2013') WHEN (T1."No_" = '07600') THEN ('LZ 12/2019') WHEN (T1."No_" = '07570') THEN ('LZ 12/2005') WHEN (T1."No_" = '07510') THEN ('LZ 03/2019') WHEN (T1."No_" = '07190') THEN ('LZ 03/2019') WHEN (T1."No_" = '07640') THEN ('LZ 01/2008') WHEN (T1."No_" = '07180') THEN ('LZ 12/2019') ELSE null END as c50,
	   CASE WHEN (T1."No_" IN ('07150','07600','07180','07640')) THEN ((((cast_float(T2."Amount")))) * -1) ELSE (0) END as c51,
	   T2."Client_DB" as c52,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c53,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END as c54,
	   CASE WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('10')) THEN ('MM') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('30')) THEN ('KRU') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('40')) THEN ('ULM') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('50')) THEN ('LL') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('55')) THEN ('GZ') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('60')) THEN ('AAM') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('70')) THEN ('LEH') WHEN ((CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END) IN ('80')) THEN ('WTB') ELSE null END as c55
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."No_" IN ('12600','12620','07260','07160','12500','07500','07590','12100','12130','12150','12200','12300','12320','12340','12360','12400','12410','12380','12390','07000','07240','07300','07600','07580','07640','07510','12510','12170','07150','07140','12180','07170','07180','07190','12175','12220','07185'))

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Search Description
COLUMN,3,Account Type
COLUMN,4,Department Code
COLUMN,5,Make Code
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
COLUMN,43,Betrieb Nr
COLUMN,44,Konto Nr
COLUMN,45,Betrag
COLUMN,46,Amount_1
COLUMN,47,Bemerkung
COLUMN,48,Besicherung
COLUMN,49,Laufzeit
COLUMN,50,Kreditlinie aus Ist
COLUMN,51,Hauptbetrieb_ID
COLUMN,52,Hauptbetrieb_Name
COLUMN,53,Standort_ID
COLUMN,54,Standort_Name