C11/System/LOCOSOFT/SQL/schema/GC/views/F_Belege_SKR_direkt.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
create view [dbo].[F_Belege_SKR_direkt] as

select "GC_Department"."Hauptbetrieb_ID" AS "Rechtseinheit_ID",
    "GC_Department"."Hauptbetrieb_Name" AS "Rechtseinheit_Name",
    "GC_Department"."Standort_ID" AS "Betrieb_ID",
    "GC_Department"."Standort_Name" AS "Betrieb_Name",
    "Kontenrahmen_GC_Struct_SKR"."Ebene1" AS "Ebene1",
    "Kontenrahmen_GC_Struct_SKR"."Ebene2" AS "Ebene2",
    "Kontenrahmen_GC_Struct_SKR"."Ebene3" AS "Ebene3",
    "Kontenrahmen_GC_Struct_SKR"."Ebene4" AS "Ebene4",
    "Kontenrahmen_GC_Struct_SKR"."Ebene5" AS "Ebene5",
    "Kontenrahmen_GC_Struct_SKR"."Ebene6" AS "Ebene6",
    left("Kontenrahmen_GC_Struct_SKR"."Konto Nr", 4) + ' - ' + "Kontenrahmen_GC_Struct_SKR"."Konto Bezeichnung" AS "Konto",
    "LOC_Belege"."Acct Nr" AS "Acct_Nr",
    "LOC_Belege"."Text" AS "Text",
    case
        when left(left("LOC_Belege"."KST", 2), 1) = '1' then 'Neuwagen'
        when left(left("LOC_Belege"."KST", 2), 1) = '2' then 'Gebrauchtwagen'
        when left(left("LOC_Belege"."KST", 2), 1) = '3' then 'Teile & Zubehör'
        when left(left("LOC_Belege"."KST", 2), 1) = '4' then 'Service'
        when left(left("LOC_Belege"."KST", 2), 1) = '5' then 'Weitere Bereiche'
        when left(left("LOC_Belege"."KST", 2), 1) = '0' then 'ohne Herkunft'
        else 'Verwaltung'
    end AS "Kostenstelle",
    "LOC_Belege"."KST" AS "KST_mit_Bez",
    left("LOC_Belege"."KST", 2) AS "KST",
    left("LOC_Belege"."Marke", 1) AS "Markencode",
    "LOC_Belege"."Vehicle Reference" AS "Fahrgestellnummer",
    left("Kontenrahmen_GC_Struct_SKR"."Konto Nr", 4) + ' - ' + "Kontenrahmen_GC_Struct_SKR"."Konto Bezeichnung" AS "Konto_Buchung",
    "LOC_Belege"."Marke" AS "Marke_mit_Bez",
    left("LOC_Belege"."Marke", 1) AS "Marke",
    case
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '1' then 'Privater Endkunde'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '2' then 'Gewerbekunde'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '3' then 'Großkunde'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '4' then 'Sonderabnehmer'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '5' then 'Wiederverkäufer'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '6' then 'Wirtschaftsraum'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '9' then 'Sonstige'
        when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '0' then 'ohne Absatzkanal'
        else NULL
    end AS "Ebene21",
    "LOC_Belege"."Absatzkanal" AS "Absatzkanal_mit_Bez",
    left("LOC_Belege"."Absatzkanal", 2) AS "Absatzkanal",
    "LOC_Belege"."Free Form Document Text" AS "Ebene31",
    "LOC_Belege"."Kostenträger" AS "Kostenträger_mit_Bez",
    "LOC_Belege"."Skr51 Cost Unit" AS "Kostenträger_FIBU",
    "Kontenrahmen_GC_Struct_SKR"."Ebene71" AS "Ebene71",
    "Kontenrahmen_GC_Struct_SKR"."Ebene72" AS "Ebene72",
    "Kontenrahmen_GC_Struct_SKR"."Ebene73" AS "Ebene73",
    "Kontenrahmen_GC_Struct_SKR"."Ebene74" AS "Ebene74",
    "Kontenrahmen_GC_Struct_SKR"."Ebene75" AS "Ebene75",
    "Kontenrahmen_GC_Struct_SKR"."Ebene76" AS "Ebene76",
    "Kontenrahmen_GC_Struct_SKR"."Ebene77" AS "Ebene77",
    "Kontenrahmen_GC_Struct_SKR"."Ebene81" AS "Ebene81",
    "Kontenrahmen_GC_Struct_SKR"."Ebene82" AS "Ebene82",
    "Kontenrahmen_GC_Struct_SKR"."Ebene83" AS "Ebene83",
    "Kontenrahmen_GC_Struct_SKR"."Ebene84" AS "Ebene84",
    "Kontenrahmen_GC_Struct_SKR"."Ebene85" AS "Ebene85",
    "Kontenrahmen_GC_Struct_SKR"."Ebene86" AS "Ebene86",
    "LOC_Belege"."GuV_Bilanz" AS "GuV_Bilanz",
    "LOC_Belege"."Susa" AS "Susa",
    "LOC_Belege"."Mandant" AS "Mandant",
    "LOC_Belege"."Betrag" AS "Ist",
    "LOC_Belege"."Menge" AS "Menge",
    "GC_Department"."Gruppe_ID" AS "Gruppe_ID",
    "GC_Department"."Gruppe_Name" AS "Gruppe_Name",
    "LOC_Belege"."Bookkeep Date" AS "Invoice_Date",
    case
        when "GC_Marken"."Fabrikat" is null then 'Fremd'
        else "GC_Marken"."Fabrikat"
    end AS "Fabrikat",
    case
        when case
            when "GC_Marken"."Fabrikat" is null then 'Fremd'
            else "GC_Marken"."Fabrikat"
        end = 'Fremd' then 9
        else "GC_Marken"."Order_By"
    end AS "Fabrikat_Order_By"
from "LOCOSOFT"."locosoft"."LOC_Belege" "LOC_Belege"
    LEFT OUTER JOIN "GC"."data"."GC_Department" "GC_Department" on "LOC_Belege"."Rechtseinheit" = "GC_Department"."Hauptbetrieb"
    and "LOC_Belege"."Betrieb" = "GC_Department"."Standort"
    LEFT OUTER JOIN "GC"."data"."GC_Marken" "GC_Marken" on "LOC_Belege"."Marke" = "GC_Marken"."Marke_FIBU"
    LEFT OUTER JOIN "GC"."locosoft"."Kontenrahmen_GC_Struct_SKR" "Kontenrahmen_GC_Struct_SKR" on "LOC_Belege"."Acct Nr" = "Kontenrahmen_GC_Struct_SKR"."Konto Nr"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO