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

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

select "GC_Department"."Hauptbetrieb_ID"                                                     AS "Hauptbetrieb_ID",
       "GC_Department"."Hauptbetrieb_Name"                                                   AS "Hauptbetrieb_Name",
       "GC_Department"."Standort_ID"                                                         AS "Standort_ID",
       "GC_Department"."Standort_Name"                                                       AS "Standort_Name",
       case when "GC_Marken"."Fabrikat" is null then 'Fremd' else "GC_Marken"."Fabrikat" end AS "Fabrikat",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Model_Detail"                                       AS "Modell_Beschreibung",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Model"                                              AS "Model",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."FZG"                                                AS "FZG",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Fahrzeugart"                                        AS "Fahrzeugart",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Fahrzeugtyp"                                        AS "Fahrzeugtyp",
       ''                                                                                    AS "Fahrzeugtyp_1",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Standtagestaffel"                                   AS "Standtagestaffel",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Farbe"                                              AS "Farbe",
       ''                                                                                    AS "Standort_1",
       ''                                                                                    AS "Lieferant_Einkäufer",
       ''                                                                                    AS "Name_Lieferant",
       ''                                                                                    AS "Name_Einkäufer",
       ''                                                                                    AS "FZG_Einkäufer",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."First Registration Date"                            AS "EZ",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Mileage Km"                                         AS "Km_stand",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Menge"                                              AS "Menge",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Einsatz"                                            AS "Einsatz",
       0                                                                                     AS "int__Aufw_",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Standtage"                                          AS "Standtage",
       0                                                                                     AS "Plan_VK_Netz_brutto",
       0                                                                                     AS "Plan_VK_Netz_netto",
       0                                                                                     AS "Plan_Vk_Platz_brutto",
       0                                                                                     AS "Plan_VK_Platz_netto",
       0                                                                                     AS "Einsatz_ori__Betrag_FIBU_",
       0                                                                                     AS "Einsatz_ber",
       convert(datetime, convert(char(8), current_timestamp, 112), 112)                      AS "Invoice_Date",
       "GC_Marken"."Order_By"                                                                AS "Fabrikat_Order_By",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Standtage_Berechnung"                               AS "Standtage_ER",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Accounting Date"                                    AS "Auslief__Datum_gepl_",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Document Type"                                      AS "Kunde",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Is Balanced"                                        AS "Standort_Nr_AB",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Debit Or Credit"                                    AS "Verkäufer",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Invoice Number"                                     AS "Auslief_Datum_Zeichen",
       "GC_Department_Auftragsbestand"."Standort_Name"                                       AS "Standort_Name_Auftragsbestand",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Contra Account Text"                                AS "AB_ja_nein",
       case
           when "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Contra Account Text" = 'Auftragsbestand' then
               "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Vehicle_Reference_berechnet" + ' - ' +
               "GC_Department_Auftragsbestand"."Standort_Name" + ' - ' +
               "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Fahrzeugtyp" + ' - ' +
               "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Debit Or Credit" + ' - ' +
               "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Document Type" + ' - ' +
               "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Invoice Number"
           else NULL end                                                                     AS "FZG_AB",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Vehicle_Reference_berechnet"                        AS "Vehicle_Reference_berechnet",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Posted Value"                                       AS "Posted_Value",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Posted Count"                                       AS "Erlös_FZG",
       "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Document Number"                                    AS "Tage_bis_Auslief_"
from ((locosoft."NW_GW_BE_auf_NW_GW_Bestand_Cat" "NW_GW_BE_auf_NW_GW_Bestand_Cat" LEFT OUTER JOIN "data"."GC_Department" "GC_Department"
       on "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Hauptbetrieb" = "GC_Department"."Hauptbetrieb" and
          "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Standort" =
          "GC_Department"."Standort") LEFT OUTER JOIN "data"."GC_Marken" "GC_Marken"
      on "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Fabrikat" = "GC_Marken"."Description")
         LEFT OUTER JOIN "GC"."data"."GC_Department" "GC_Department_Auftragsbestand"
                         on "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Hauptbetrieb" =
                            "GC_Department_Auftragsbestand"."Hauptbetrieb" and
                            "NW_GW_BE_auf_NW_GW_Bestand_Cat"."Is Balanced" = "GC_Department_Auftragsbestand"."Standort"
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO