ETL_DWH_LOCOSOFT/schema/GC_ETL_LOCOSOFT/views/transform.Belege_SKR.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform].[Belege_SKR] AS

select [LOC_Belege].[Rechtseinheit] as Hauptbetrieb_imr,
        [LOC_Belege].[Betrieb] as Standort_imr,
       "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 Konto_Nr_imr,
       "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 N'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"."Skr51 Make" as Marke_FIBU_imr,
       "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 N'Großkunde'
           when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '4' then 'Sonderabnehmer'
           when left(left("LOC_Belege"."Absatzkanal", 2), 1) = '5' then N'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",
		   DATEPART(DAY, [Invoice Date]) AS Tag_Dashboard,
		   DATEPART(MONTH, [Invoice Date]) AS Monat_Dashboard,
		   DATEPART(YEAR, [Invoice Date]) AS Jahr_Dashboard
from transform_imr."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"."Skr51 Make" = "GC_Marken"."Marke_FIBU" and "LOC_Belege"."Rechtseinheit" = "GC_Marken"."Client_DB"
    LEFT OUTER JOIN data."Kontenrahmen_GC_Struct_SKR" "Kontenrahmen_GC_Struct_SKR" on "LOC_Belege"."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