C11/System/NAVISION/IQD/Belege/belege_bank.sql

select T1."No_" as "No",
	   T1."Name" as "Name_2",
	   T1."Search Description" as "Search Description",
	   T1."Account Type" as "Account Type",
	   T1."Department Code" as "Department Code_2",
	   T1."Make Code" as "Make Code_2",
	   T1."Income_Balance" as "Income Balance",
	   T1."Last Date Modified" as "Last Date Modified",
	   T2."Entry No_" as "Entry No",
	   T2."G_L Account No_" as "G L Account No",
	   T2."Posting Date" as "Posting Date",
	   T2."Document Type" as "Document Type",
	   T2."Document No_" as "Document No",
	   T2."Description" as "Description",
	   T2."Bal_ Account No_" as "Bal Account No",
	   T2."Department Code" as "Department Code",
	   T2."Make Code" as "Make Code",
	   T2."User ID" as "User Id",
	   T2."Source Code" as "Source Code",
	   T2."Quantity" as "Quantity",
	   T2."Reason Code" as "Reason Code",
	   T2."Gen_ Posting Type" as "Gen Posting Type",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Bal_ Account Type" as "Bal Account Type",
	   T2."Transaction No_" as "Transaction No",
	   T2."Document Date" as "Document Date",
	   T2."External Document No_" as "External Document No",
	   T2."Source Type" as "Source Type",
	   T2."Source No_" as "Source No",
	   T2."No_ Series" as "No Series",
	   T2."Branch Code" as "Branch Code",
	   T2."Main Area" as "Main Area",
	   T2."VIN" as "Vin",
	   T2."Book No_" as "Book No",
	   T2."Veh_ Source Code" as "Veh Source Code",
	   T2."Reposted" as "Reposted",
	   T2."Corrected" as "Corrected",
	   T2."Correction to curr_ No_" as "Correction To Curr No",
	   T2."Reposting to curr_ No_" as "Reposting To Curr No",
	   T3."Code" as "Code",
	   T3."Name" as "Name",
	   T2."Posting Date" as "Jahr",
	   'AHR' as "Betrieb Nr",
	   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 "Konto Nr",
	   ((convert(float, T2."Amount"))) as "Betrag",
	   (convert(float, T2."Amount")) as "Amount_1",
	   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 "Bemerkung",
	   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 "Besicherung",
	   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 "Laufzeit",
	   CASE WHEN (T1."No_" IN ('07150','07600','07180','07640')) THEN ((((convert(float, T2."Amount")))) * -1) ELSE (0) END as "Kreditlinie aus Ist",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('10') WHEN (T2."Client_DB" = '2') THEN ('60') ELSE null END as "Standort_ID",
	   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 "Standort_Name"
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'))