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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Belege_Bilanz_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_Bilanz"."Acct Nr"                                                                               AS "Acct_Nr",
       "LOC_Belege_Bilanz"."Text"                                                                                  AS "Text",
       case
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '1' then 'Neuwagen'
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '2' then 'Gebrauchtwagen'
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '3' then 'Teile & Zubehör'
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '4' then 'Service'
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '5' then 'Weitere Bereiche'
           when left(left("LOC_Belege_Bilanz"."KST", 2), 1) = '0' then 'ohne Herkunft'
           else 'Verwaltung' end                                                                                   AS "Kostenstelle",
       "LOC_Belege_Bilanz"."KST"                                                                                   AS "KST_mit_Bez",
       left("LOC_Belege_Bilanz"."KST", 2)                                                                          AS "KST",
       left("LOC_Belege_Bilanz"."Marke", 1)                                                                        AS "Markencode",
       "LOC_Belege_Bilanz"."Vehicle Reference"                                                                     AS "Fahrgestellnummer",
       left("Kontenrahmen_GC_Struct_SKR"."Konto Nr", 4) + ' - ' +
       "Kontenrahmen_GC_Struct_SKR"."Konto Bezeichnung"                                                            AS "Konto_Buchung",
       "LOC_Belege_Bilanz"."Marke"                                                                                 AS "Marke_mit_Bez",
       left("LOC_Belege_Bilanz"."Marke", 1)                                                                        AS "Marke",
       case
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '1' then 'Privater Endkunde'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '2' then 'Gewerbekunde'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '3' then 'Großkunde'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '4' then 'Sonderabnehmer'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '5' then 'Wiederverkäufer'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '6' then 'Wirtschaftsraum'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '9' then 'Sonstige'
           when left(left("LOC_Belege_Bilanz"."Absatzkanal", 2), 1) = '0' then 'ohne Absatzkanal'
           else NULL end                                                                                           AS "Ebene21",
       "LOC_Belege_Bilanz"."Absatzkanal"                                                                           AS "Absatzkanal_mit_Bez",
       left("LOC_Belege_Bilanz"."Absatzkanal", 2)                                                                  AS "Absatzkanal",
       "LOC_Belege_Bilanz"."Free Form Document Text"                                                               AS "Ebene31",
       "LOC_Belege_Bilanz"."Kostenträger"                                                                          AS "Kostenträger_mit_Bez",
       left("LOC_Belege_Bilanz"."Kostenträger", 2)                                                                 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_Bilanz"."GuV_Bilanz"                                                                            AS "GuV_Bilanz",
       "LOC_Belege_Bilanz"."Susa"                                                                                  AS "Susa",
       "LOC_Belege_Bilanz"."Mandant"                                                                               AS "Mandant",
       "LOC_Belege_Bilanz"."Betrag"                                                                                AS "Ist",
       "LOC_Belege_Bilanz"."Menge"                                                                                 AS "Menge",
       "GC_Department"."Gruppe_ID"                                                                                 AS "Gruppe_ID",
       "GC_Department"."Gruppe_Name"                                                                               AS "Gruppe_Name",
       "LOC_Belege_Bilanz"."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",
		   DATEPART(DAY, [Invoice Date]) AS Tag_Dashboard,
		   DATEPART(MONTH, [Invoice Date]) AS Monat_Dashboard,
		   DATEPART(YEAR, [Invoice Date]) AS Jahr_Dashboard
from ((locosoft."LOC_Belege_Bilanz" "LOC_Belege_Bilanz" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
       on "LOC_Belege_Bilanz"."Rechtseinheit" = "GC_Department"."Hauptbetrieb" and "LOC_Belege_Bilanz"."Betrieb" =
                                                                                   "GC_Department"."Standort") LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken"
      on "LOC_Belege_Bilanz"."Marke" = "GC_Marken"."Marke_FIBU" and
         "LOC_Belege_Bilanz"."Rechtseinheit" = "GC_Marken"."Client_DB")
         LEFT OUTER JOIN locosoft."Kontenrahmen_GC_Struct_SKR" "Kontenrahmen_GC_Struct_SKR"
                         on "LOC_Belege_Bilanz"."Acct Nr" = "Kontenrahmen_GC_Struct_SKR"."Konto Nr"
where "GC_Department"."Hauptbetrieb_ID" = '1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO