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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Belege_SKR_STK] 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",
       "SKR_Konten_Stk_manuell"."Ebene1"                                                                  AS "Ebene1",
       "SKR_Konten_Stk_manuell"."Ebene2"                                                                  AS "Ebene2",
       "SKR_Konten_Stk_manuell"."Ebene3"                                                                  AS "Ebene3",
       "SKR_Konten_Stk_manuell"."Ebene4"                                                                  AS "Ebene4",
       "SKR_Konten_Stk_manuell"."Ebene5"                                                                  AS "Ebene5",
       "SKR_Konten_Stk_manuell"."Ebene6"                                                                  AS "Ebene6",
       left("SKR_Konten_Stk_manuell"."Acct Nr", 4) + ' - ' + "SKR_Konten_Stk_manuell"."Konto Bezeichnung" AS "Konto_ori",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Acct Nr"                                                           AS "Acct_Nr",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Text"                                                              AS "Text",
       case
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '1' then 'Neuwagen'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '2' then 'Gebrauchtwagen'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '3' then 'Teile & Zubehör'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '4' then 'Service'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '5' then 'Weitere Bereiche'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2), 1) = '0' then 'ohne Herkunft'
           else 'Verwaltung' end                                                                          AS "Kostenstelle",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."KST"                                                               AS "KST_mit_Bez",
       left("LOC_Belege_NW_GW_VK_Stk_FIBU"."KST", 2)                                                      AS "KST",
       left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Marke", 1)                                                    AS "Markencode",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Vehicle Reference"                                                 AS "Fahrgestellnummer",
       left("SKR_Konten_Stk_manuell"."Acct Nr", 4) + ' - ' +
       "SKR_Konten_Stk_manuell"."Konto Bezeichnung"                                                       AS "Konto_Buchung",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Marke"                                                             AS "Marke_mit_Bez",
       left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Marke", 1)                                                    AS "Marke",
       case
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '1' then 'Privater Endkunde'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '2' then 'Gewerbekunde'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '3' then 'Großkunde'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '4' then 'Sonderabnehmer'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '5' then 'Wiederverkäufer'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '6' then 'Wirtschaftsraum'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '9' then 'Sonstige'
           when left(left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2), 1) = '0' then 'ohne Absatzkanal'
           else NULL end                                                                                  AS "Ebene21",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal"                                                       AS "Absatzkanal_mit_Bez",
       left("LOC_Belege_NW_GW_VK_Stk_FIBU"."Absatzkanal", 2)                                              AS "Absatzkanal",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Free Form Document Text"                                           AS "Ebene31",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Kostenträger"                                                      AS "Kostenträger_mit_Bez",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Skr51 Cost Unit"                                                   AS "Kostenträger_FIBU",
       ''                                                                                                 AS "Ebene71",
       ''                                                                                                 AS "Ebene72",
       ''                                                                                                 AS "Ebene73",
       ''                                                                                                 AS "Ebene74",
       ''                                                                                                 AS "Ebene75",
       ''                                                                                                 AS "Ebene76",
       ''                                                                                                 AS "Ebene77",
       ''                                                                                                 AS "Ebene81",
       ''                                                                                                 AS "Ebene82",
       ''                                                                                                 AS "Ebene83",
       ''                                                                                                 AS "Ebene84",
       ''                                                                                                 AS "Ebene85",
       ''                                                                                                 AS "Ebene86",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."GuV_Bilanz"                                                        AS "GuV_Bilanz_ori",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Susa"                                                              AS "Susa",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Mandant"                                                           AS "Mandant",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Betrag"                                                            AS "Ist",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."Betrag"                                                            AS "Menge",
       "GC_Department"."Gruppe_ID"                                                                        AS "Gruppe_ID",
       "GC_Department"."Gruppe_Name"                                                                      AS "Gruppe_Name",
       "LOC_Belege_NW_GW_VK_Stk_FIBU"."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",
		   '' AS Konto,
		   '3' AS GuV_Bilanz,
		   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_NW_GW_VK_Stk_FIBU" "LOC_Belege_NW_GW_VK_Stk_FIBU" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
       on "LOC_Belege_NW_GW_VK_Stk_FIBU"."Rechtseinheit" = "GC_Department"."Hauptbetrieb" and
          "LOC_Belege_NW_GW_VK_Stk_FIBU"."Betrieb" =
          "GC_Department"."Standort") LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken"
      on "LOC_Belege_NW_GW_VK_Stk_FIBU"."Marke" = "GC_Marken"."Marke_FIBU" and
         "LOC_Belege_NW_GW_VK_Stk_FIBU"."Rechtseinheit" = "GC_Marken"."Client_DB")
         LEFT OUTER JOIN [locosoft]."SKR_Konten_Stk_manuell" "SKR_Konten_Stk_manuell"
                         on "LOC_Belege_NW_GW_VK_Stk_FIBU"."Acct Nr" = "SKR_Konten_Stk_manuell"."Acct Nr"
where "GC_Department"."Hauptbetrieb_ID" = '1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO