ETL_DWH_LOCOSOFT/schema/GC/views/transform.Umsatz_Belege_SKR.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Umsatz_Belege_SKR] 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",
       case
           when "Kontenrahmen_GC_Struct_SKR"."Ebene1" = 'Umsatzerlöse' then "LOC_Belege"."Betrag" * -1
           else 0 end                                                                                              AS "Umsatz_FIBU_gesamt"
from ((locosoft."LOC_Belege" "LOC_Belege" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
       on "LOC_Belege"."Rechtseinheit" = "GC_Department"."Hauptbetrieb" and
          "LOC_Belege"."Betrieb" = "GC_Department"."Standort") LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken"
      on "LOC_Belege"."Marke" = "GC_Marken"."Marke_FIBU" and "LOC_Belege"."Rechtseinheit" = "GC_Marken"."Client_DB")
         LEFT OUTER JOIN locosoft."Kontenrahmen_GC_Struct_SKR" "Kontenrahmen_GC_Struct_SKR"
                         on "LOC_Belege"."Acct Nr" = "Kontenrahmen_GC_Struct_SKR"."Konto Nr"
where case when ("Kontenrahmen_GC_Struct_SKR"."Ebene1" = 'Umsatzerlöse') then "LOC_Belege"."Betrag" * -1 else 0 end <> 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO