SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[LOC_Belege_NW_GW_VK] AS
SELECT "Accounting Date" AS "Accounting Date"
     , "Document Type" AS "Document Type"
     , "Document Number" AS "Document Number"
     , "Position In Document" AS "Position In Document"
     , (
    datediff(day,(
            convert(datetime, (
                CASE
                    WHEN ((max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")) IS NULL)
                        THEN ("Invoice Date_journal_accountings")
                    ELSE ((max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")))
                END
                ))
            ), [in_buy_invoice_no_date])
    ) AS "Customer Number"
     , "Nominal Account Number" AS "Nominal Account Number"
     , "Is Balanced" AS "Is Balanced"
     , "Clearing Number" AS "Clearing Number"
     , "Document Date" AS "Document Date"
     , "Posted Value" AS "Posted Value"
     , "Debit Or Credit" AS "Debit Or Credit"
     , "Posted Count" AS "Posted Count"
     , "Branch Number" AS "Branch Number"
     , "Customer Contra Account" AS "Customer Contra Account"
     , "Nominal Contra Account" AS "Nominal Contra Account"
     , "Contra Account Text" AS "Contra Account Text"
     , "Account Form Page Number" AS "Account Form Page Number"
     , "Account Form Page Line" AS "Account Form Page Line"
     , "Serial Number Each Month" AS "Serial Number Each Month"
     , "Employee Number" AS "Employee Number"
     , "Invoice Date_journal_accountings" AS "Invoice Date_journal_accountings"
     , "Invoice Number" AS "Invoice Number"
     , "Dunning Level" AS "Dunning Level"
     , "Last Dunning Date" AS "Last Dunning Date"
     , "Journal Page" AS "Journal Page"
     , "Journal Line" AS "Journal Line"
     , "Cash Discount" AS "Cash Discount"
     , "Term Of Payment" AS "Term Of Payment"
     , "Posting Text" AS "Posting Text"
     , "Vehicle Reference" AS "Vehicle Reference"
     , "Vat Id Number" AS "Vat Id Number"
     , "Account Statement Number" AS "Account Statement Number"
     , "Account Statement Page" AS "Account Statement Page"
     , "Vat Key" AS "Vat Key"
     , "Days For Cash Discount" AS "Days For Cash Discount"
     , "Day Of Actual Accounting" AS "Day Of Actual Accounting"
     , "Skr51 Branch" AS "Skr51 Branch"
     , "Skr51 Make" AS "Skr51 Make"
     , "Skr51 Cost Center" AS "Skr51 Cost Center"
     , "Skr51 Sales Channel" AS "Skr51 Sales Channel"
     , "Skr51 Cost Unit" AS "Skr51 Cost Unit"
     , "Previously Used Account No" AS "Previously Used Account No"
     , "Free Form Accounting Text" AS "Free Form Accounting Text"
     , "Free Form Document Text" AS "Free Form Document Text"
     , "Nom_Account_Is Profit Loss Account" AS "Nom_Account_Is Profit Loss Account"
     , "Rechtseinheit" AS "Rechtseinheit"
     , "Betrieb" AS "Betrieb"
     , "Bookkeep Date" AS "Bookkeep Date"
     , "Marke" AS "Marke"
     , "Text" AS "Text"
     , "Mandant" AS "Mandant"
     , "Betrag" AS "Betrag"
     , "Konto_mit_Bezeichnung" AS "Konto_mit_Bezeichnung"
     , "Menge_2" AS "Menge_2"
     , "Stellen Cost Center" AS "Stellen Cost Center"
     , "KST" AS "KST"
     , "Stellen Sales Channel" AS "Stellen Sales Channel"
     , "Absatzkanal" AS "Absatzkanal"
     , "Stellen Cost Unit" AS "Stellen Cost Unit"
     , "Kostenträger_mit_Null" AS "Kostenträger_mit_Null"
     , "Kostenträger" AS "Kostenträger"
     , "GuV_Bilanz" AS "GuV_Bilanz"
     , "Susa" AS "Susa"
     , "Acct Nr" AS "Acct Nr"
     , "Document Type In Journal" AS "Document Type In Journal"
     , "Document Type Description" AS "Document Type Description"
     , "FZG_ja_nein" AS "FZG_ja_nein"
     , sum("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Summe_FZG_ja_nein"
     , CASE
           WHEN (sum("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet") > 0)
               THEN (N'FZG-Geschäft')
           ELSE (N'Buchungen ohne FZG-Geschäft')
       END AS "FZG_Geschäft ja/nein"
     , "Invoice Type_Invoices" AS "Invoice Type_Invoices"
     , "Invoice Number_Invoices" AS "Invoice Number_Invoices"
     , "Invoice Date_Invoices" AS "Invoice Date_Invoices"
     , "Is Canceled_Invoices" AS "Is Canceled_Invoices"
     , "Vehicle Number_Invoices" AS "Vehicle Number_Invoices"
     , max("Vehicle Number_Invoices") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Vehicle_Number_Max"
     , "Invoice_Date_1" AS "Invoice_Date_1"
     , max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Invoice Date_FZG"
     , CASE
           WHEN (max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet") IS NULL)
               THEN ("Invoice Date_journal_accountings")
           ELSE max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")
       END AS "Invoice Date"
     , sum("Betrag") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Summe Betrag"
     , "Internal Number_Vehicles" AS "Internal Number_Vehicles"
     , "Vin_Vehicles" AS "Vin_Vehicles"
     , "Dealer Vehicle Type" AS "Dealer Vehicle Type"
     , "Fahrzeugtyp" AS "Fahrzeugtyp"
     , "Fahrzeugart" AS "Fahrzeugart"
     , "Make Number_Vehicles" AS "Make Number_Vehicles"
     , "Description_Make" AS "Description_Make"
     , "Fabrikat" AS "Fabrikat"
     , "Model Code_Models" AS "Model Code_Models"
     , "Model_Detail" AS "Model_Detail"
     , "Model" AS "Model"
     , "Owner Number_Vehicles" AS "Owner Number_Vehicles"
     , "Holder Number_Vehicles" AS "Holder Number_Vehicles"
     , "First Name_Owner_Customer" AS "First Name_Owner_Customer"
     , "Family Name_Owner_Customer" AS "Family Name_Owner_Customer"
     , "First Name_Holder_Customer" AS "First Name_Holder_Customer"
     , "Family Name_Holder_Customer" AS "Family Name_Holder_Customer"
     , "Kunde" AS "Kunde"
     , "Sales_Channel_FZG" AS "Sales_Channel_FZG"
     , max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Kundenart_Zahl"
     , CASE
           WHEN (max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 10 AND 19)
               THEN ('Privater Endkunde')
           WHEN (max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 20 AND 29)
               THEN ('Gewerbekunde')
           WHEN (max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 30 AND 39)
               THEN (N'Großkunde')
           WHEN (max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 40 AND 49)
               THEN ('Sonderabnehmer')
           WHEN (max("Sales_Channel_FZG") OVER (PARTITION BY "Vehicle Reference_berechnet") BETWEEN 50 AND 59)
               THEN (N'Wiederverkäufer')

       END AS "Kundenart"
     , "Hauptbetrieb" AS "Hauptbetrieb"
     , "Standort_alt" AS "Standort_alt"
     , "Salesman Number_Vehicles" AS "Salesman Number_Vehicles"
     , "Employee Number_Employees_Salesman" AS "Employee Number_Employees_Salesman"
     , "Name_Employees_Salesman" AS "Name_Employees_Salesman"
     , "Verkäufer" AS "Verkäufer"
     , "Standort_FZG_Verkauf" AS "Standort_FZG_Verkauf"
     , max("Standort_FZG_Verkauf") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Standort_Zahl"
     , "Standort" AS "Standort"
     , "FZG" AS "FZG"
     , "Konto" AS "Konto"
     , "Body Paint Code" AS "Body Paint Code"
     , "Body Paint Description" AS "Body Paint Description"
     , "Farbe" AS "Farbe"
     , "Erlös FZG" AS "Erlös FZG"
     , "Nachlass" AS "Nachlass"
     , "VAK FZG" AS "VAK FZG"
     , "Erlös Zulass." AS "Erlös Zulass."
     , "VAK Zulass." AS "VAK Zulass."
     , "Erlös Gar." AS "Erlös Gar."
     , "VAK Gar." AS "VAK Gar."
     , "Erlös Nachr./Aufb." AS "Erlös Nachr./Aufb."
     , "VAK Nachr./Aufb." AS "VAK Nachr./Aufb."
     , "VAK Fertigm." AS "VAK Fertigm."
     , "Erlös überf." AS "Erlös überf."
     , "VAK überf." AS "VAK überf."
     , "Erlös Sonst._8900" AS "Erlös Sonst._8900"
     , "Erlös Sonst." AS "Erlös Sonst."
     , "VAK Sonst." AS "VAK Sonst."
     , "VAK intern" AS "VAK intern"
     , "Erlös Prov." AS "Erlös Prov."
     , "Boni/VK-Hilfen" AS "Boni/VK-Hilfen"
     , "Erlös ges." AS "Erlös ges."
     , "VAK ges." AS "VAK ges."
     , "Menge_1" AS "Menge_1"
     , count("Accounting Date") OVER (PARTITION BY "Vehicle Reference_berechnet") AS "Menge_count"
     , ("Menge_1" / count("Accounting Date") OVER (PARTITION BY "Vehicle Reference_berechnet")) AS "Menge"
     , "Vehicle Reference_berechnet" AS "Vehicle Reference_berechnet"
     , "var. Kosten" AS "var. Kosten"
     , "Dealer Vehicle Type_Vehicles" AS "Dealer Vehicle Type_Vehicles"
     , "Dealer Vehicle Number_Vehicles" AS "Dealer Vehicle Number_Vehicles"
FROM (SELECT "Accounting Date"
           , "Document Type"
           , "Document Number"
           , "Position In Document"
           , "Nominal Account Number"
           , "Is Balanced"
           , "Clearing Number"
           , "Document Date"
           , "Posted Value"
           , "Debit Or Credit"
           , "Posted Count"
           , "Branch Number"
           , "Customer Contra Account"
           , "Nominal Contra Account"
           , "Contra Account Text"
           , "Account Form Page Number"
           , "Account Form Page Line"
           , "Serial Number Each Month"
           , "Employee Number"
           , "Invoice Date_journal_accountings"
           , "Invoice Number"
           , "Dunning Level"
           , "Last Dunning Date"
           , "Journal Page"
           , "Journal Line"
           , "Cash Discount"
           , "Term Of Payment"
           , "Posting Text"
           , "Vehicle Reference"
           , "Vat Id Number"
           , "Account Statement Number"
           , "Account Statement Page"
           , "Vat Key"
           , "Days For Cash Discount"
           , "Day Of Actual Accounting"
           , "Skr51 Branch"
           , "Skr51 Make"
           , "Skr51 Cost Center"
           , "Skr51 Sales Channel"
           , "Skr51 Cost Unit"
           , "Previously Used Account No"
           , "Free Form Accounting Text"
           , "Free Form Document Text"
           , "Nom_Account_Is Profit Loss Account"
           , "Rechtseinheit"
           , "Betrieb"
           , "Accounting Date" AS "Bookkeep Date"
           , "Marke"
           , "Text"
           , '1' AS "Mandant"
           , "Betrag"
           , "Konto_mit_Bezeichnung"
           , "Menge_2"
           , "Stellen Cost Center"
           , "KST"
           , "Stellen Sales Channel"
           , "Absatzkanal"
           , "Stellen Cost Unit"
           , "Kostenträger_mit_Null"
           , "Kostenträger"
           , "GuV_Bilanz"
           , "Susa"
           , "Nominal Account Number" AS "Acct Nr"
           , "Document Type In Journal"
           , "Document Type Description"
           , "FZG_ja_nein"
           , "Invoice Type_Invoices"
           , "Invoice Number_Invoices"
           , "Invoice Date_Invoices"
           , "Is Canceled_Invoices"
           , "Vehicle Number_Invoices"
           , "Invoice_Date_1"
           , "Internal Number_Vehicles"
           , "Vin_Vehicles"
           , "Dealer Vehicle Type"
           , "Fahrzeugtyp"
           , "Fahrzeugart"
           , "Make Number_Vehicles"
           , "Description_Make"
           , "Fabrikat"
           , "Model Code_Models"
           , "Model_Detail"
           , "Model"
           , "Owner Number_Vehicles"
           , "Holder Number_Vehicles"
           , "First Name_Owner_Customer"
           , "Family Name_Owner_Customer"
           , "First Name_Holder_Customer"
           , "Family Name_Holder_Customer"
           , "Kunde"
           , "Sales_Channel_FZG"
           , "Rechtseinheit" AS "Hauptbetrieb"
           , "Betrieb" AS "Standort_alt"
           , "Salesman Number_Vehicles"
           , "Employee Number_Employees_Salesman"
           , "Name_Employees_Salesman"
           , "Verkäufer"
           , "Standort_FZG_Verkauf"
           , "Standort"
           , "FZG"
           , "Konto_mit_Bezeichnung" AS "Konto"
           , "Body Paint Code"
           , "Body Paint Description"
           , "Body Paint Description" AS "Farbe"
           , "Erlös FZG"
           , "Nachlass"
           , "VAK FZG"
           , "Erlös Zulass."
           , "VAK Zulass."
           , "Erlös Gar."
           , "VAK Gar."
           , "Erlös Nachr./Aufb."
           , "VAK Nachr./Aufb."
           , "VAK Fertigm."
           , "Erlös überf."
           , "VAK überf."
           , "Erlös Sonst._8900"
           , "Erlös Sonst."
           , "VAK Sonst."
           , "VAK intern"
           , "Erlös Prov."
           , "Boni/VK-Hilfen"
           , "Erlös ges."
           , "VAK ges."
           , 1.0 AS "Menge_1"
           , "Vehicle Reference_berechnet"
           , "var. Kosten"
           , "Dealer Vehicle Type_Vehicles"
           , "Dealer Vehicle Number_Vehicles"
           , [in_buy_invoice_no_date] AS [in_buy_invoice_no_date]
           , max("Vehicle Number_Invoices") OVER (PARTITION BY "Vehicle Reference_berechnet") AS [c143]
           , (
        CASE
            WHEN ((max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")) IS NULL)
                THEN (("Invoice Date_journal_accountings"))
            ELSE ((max("Invoice_Date_1") OVER (PARTITION BY "Vehicle Reference_berechnet")))
        END
        ) AS [c144]
           , (
        CASE
            WHEN ((sum("FZG_ja_nein") OVER (PARTITION BY "Vehicle Reference_berechnet")) > 0)
                THEN (N'FZG-Geschäft')
            ELSE (N'Buchungen ohne FZG-Geschäft')
        END
        ) AS [c145]
      FROM (SELECT (left([journal_accountings]."vehicle_reference", 7)) + '_' + (right([journal_accountings]."vehicle_reference", 17)) AS "Vehicle Reference_berechnet"
                 , try_convert(datetime, [journal_accountings]."invoice_date") AS "Invoice Date_journal_accountings"
                 , [dealer_vehicles]."in_buy_invoice_no_date" AS [in_buy_invoice_no_date]
                 , [vehicles]."dealer_vehicle_number" AS "Dealer Vehicle Number_Vehicles"
                 , [vehicles]."dealer_vehicle_type" AS "Dealer Vehicle Type_Vehicles"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (4300, 4560, 4580, 4530, 4540, 4590, 4591, 4690, 4621, 4622, 4623, 4500, 4501, 4502, 4503, 4504, 5501))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "var. Kosten"
                 , (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7000, 7010, 7100, 7101, 7110, 7111, 7120, 7510))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (7200))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       )
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (7210))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       )
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (7220))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       )
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (7240))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       )
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (7030))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       )
                                   )
                               ELSE (0)
                           END
                           )
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (5001, 5005, 5006, 5007, 5008, 5003, 5004))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7800, 7850))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) AS "VAK ges."
                 , (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8000, 8010, 8100, 8110, 8111, 8112, 8510, 8928, 8934))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (8200))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       ) * - 1
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (8210))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       ) * - 1
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (8220))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       ) * - 1
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (8030))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       ) * - 1
                                   )
                               ELSE (0)
                           END
                           ) + (
                           CASE
                               WHEN ([journal_accountings]."nominal_account_number" IN (8900))
                                   THEN (
                                   (
                                       CASE
                                           WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                               THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                           ELSE ([journal_accountings]."posted_value" / 100)
                                       END
                                       ) * - 1
                                   )
                               ELSE (0)
                           END
                           )
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8800, 8801, 8870, 8820, 8830))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8001, 8011, 5002))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) AS "Erlös ges."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7800, 7850))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "Boni/VK-Hilfen"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8800, 8801, 8870, 8820, 8830))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös Prov."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (5001, 5005, 5006, 5007, 5008, 5003, 5004))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK intern"
                 , (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7200))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7210))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7220))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7240))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (7030))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   )
                               )
                           ELSE (0)
                       END
                       ) AS "VAK Sonst."
                 , (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8200))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8210))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8220))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8030))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) + (
                       CASE
                           WHEN ([journal_accountings]."nominal_account_number" IN (8900))
                               THEN (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                       ELSE ([journal_accountings]."posted_value" / 100)
                                   END
                                   ) * - 1
                               )
                           ELSE (0)
                       END
                       ) AS "Erlös Sonst."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8900))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös Sonst._8900"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7030))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK überf."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8030))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös überf."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7240))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK Fertigm."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7220))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK Nachr./Aufb."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8220))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös Nachr./Aufb."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7210))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK Gar."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8210))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös Gar."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7200))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK Zulass."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8200))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös Zulass."
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (7000, 7010, 7100, 7101, 7110, 7111, 7120, 7510))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               )
                           )
                       ELSE (0)
                   END AS "VAK FZG"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8001, 8011, 5002))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Nachlass"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8000, 8010, 8100, 8110, 8111, 8112, 8510, 8928, 8934))
                           THEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."debit_or_credit" = 'H')
                                       THEN ([journal_accountings]."posted_value" / 100 * - 1)
                                   ELSE ([journal_accountings]."posted_value" / 100)
                               END
                               ) * - 1
                           )
                       ELSE (0)
                   END AS "Erlös FZG"
                 , [vehicles]."body_paint_description" AS "Body Paint Description"
                 , [vehicles]."body_paint_code" AS "Body Paint Code"
                 , ((convert(varchar,[journal_accountings]."nominal_account_number")) + ' - ' + [nominal_accounts]."account_description") AS "Konto_mit_Bezeichnung"
                 , (left(((left([journal_accountings]."vehicle_reference", 7)) + '_' + (right([journal_accountings]."vehicle_reference", 17))), 7)) + ' / ' + ((left(((([vehicles]."internal_number"))), 5))) + ' - ' + [models]."description" + ' - ' + (
              (
                  (convert(varchar,((([vehicles]."holder_number"))))) + ' - ' + (
                      CASE
                          WHEN ([customers_suppliers]."first_name" IS NULL)
                              THEN ('')
                          ELSE ([customers_suppliers]."first_name")
                      END
                      ) + ' ' + [customers_suppliers]."family_name"
                  )
              ) AS "FZG"
                 , CASE
                       WHEN ([dealer_vehicles]."out_subsidiary" IS NULL)
                           THEN convert(varchar,((((([journal_accountings]."skr51_branch"))))))
                       ELSE convert(varchar,((([dealer_vehicles]."out_subsidiary"))))
                   END AS "Standort"
                 , [dealer_vehicles]."out_subsidiary" AS "Standort_FZG_Verkauf"
                 , (convert(varchar,((([vehicles]."salesman_number"))))) + ' - ' + [employees]."name" AS "Verkäufer"
                 , [employees]."name" AS "Name_Employees_Salesman"
                 , [employees]."employee_number" AS "Employee Number_Employees_Salesman"
                 , [vehicles]."salesman_number" AS "Salesman Number_Vehicles"
                 , convert(varchar,((([journal_accountings]."skr51_branch")))) AS "Betrieb"
                 , convert(varchar,[journal_accountings]."subsidiary_to_company_ref") AS "Rechtseinheit"
                 , CASE
                       WHEN (
                           (
                               (
                                   CASE
                                       WHEN ([journal_accountings]."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
                                           THEN (1)
                                       ELSE (0)
                                   END
                                   ) = 1
                               )
                               AND ([invoices]."is_canceled" <> '1')
                           )
                           THEN ([journal_accountings]."skr51_sales_channel")

                   END AS "Sales_Channel_FZG"
                 , (
              (convert(varchar,((([vehicles]."holder_number"))))) + ' - ' + (
                  CASE
                      WHEN ([customers_suppliers]."first_name" IS NULL)
                          THEN ('')
                      ELSE ([customers_suppliers]."first_name")
                  END
                  ) + ' ' + [customers_suppliers]."family_name"
              ) AS "Kunde"
                 , [customers_suppliers]."family_name" AS "Family Name_Holder_Customer"
                 , CASE
                       WHEN ([customers_suppliers]."first_name" IS NULL)
                           THEN ('')
                       ELSE ([customers_suppliers]."first_name")
                   END AS "First Name_Holder_Customer"
                 , [customers_suppliers2]."family_name" AS "Family Name_Owner_Customer"
                 , [customers_suppliers2]."first_name" AS "First Name_Owner_Customer"
                 , [vehicles]."holder_number" AS "Holder Number_Vehicles"
                 , [vehicles]."owner_number" AS "Owner Number_Vehicles"
                 , (left((upper([models]."description")), 3)) AS "Model"
                 , [models]."description" AS "Model_Detail"
                 , [models]."model_code" AS "Model Code_Models"
                 , CASE
                       WHEN ([makes]."description" IN ('BAIC', 'Cupra', 'Forthing', 'Honda', 'JAC', 'Kia', 'MC Honda', 'SEAT', 'Suzuki'))
                           THEN ([makes]."description")
                       ELSE ('Fremd')
                   END AS "Fabrikat"
                 , [makes]."description" AS "Description_Make"
                 , [vehicles]."make_number" AS "Make Number_Vehicles"
                 , CASE
                       WHEN (
                           (
                               CASE
                                   WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('N'))
                                       THEN ('NW')
                                   WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('V'))
                                       THEN ('VFW')
                                   WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('G', 'D'))
                                       THEN ('GW')
                                   WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('T'))
                                       THEN ('TZ')
                                   ELSE NULL
                               END
                               ) IN ('VFW', 'TZ', 'NW')
                           )
                           THEN ('Neuwagen')
                       ELSE ('Gebrauchtwagen')
                   END AS "Fahrzeugart"
                 , CASE
                       WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('N'))
                           THEN ('NW')
                       WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('V'))
                           THEN ('VFW')
                       WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('G', 'D'))
                           THEN ('GW')
                       WHEN (((left([journal_accountings]."vehicle_reference", 1))) IN ('T'))
                           THEN ('TZ')

                   END AS "Fahrzeugtyp"
                 , (left([journal_accountings]."vehicle_reference", 1)) AS "Dealer Vehicle Type"
                 , [vehicles]."vin" AS "Vin_Vehicles"
                 , [vehicles]."internal_number" AS "Internal Number_Vehicles"
                 , CASE
                       WHEN (
                           (
                               CASE
                                   WHEN ([journal_accountings]."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
                                       THEN (1)
                                   ELSE (0)
                               END
                               ) = 1
                           )
                           THEN try_convert(datetime, [journal_accountings]."invoice_date")

                   END AS "Invoice_Date_1"
                 , [invoices]."vehicle_number" AS "Vehicle Number_Invoices"
                 , [invoices]."is_canceled" AS "Is Canceled_Invoices"
                 , [invoices]."invoice_date" AS "Invoice Date_Invoices"
                 , [invoices]."invoice_number" AS "Invoice Number_Invoices"
                 , [invoices]."invoice_type" AS "Invoice Type_Invoices"
                 , CASE
                       WHEN ([journal_accountings]."nominal_account_number" IN (8000, 8010, 8100, 8110, 8510))
                           THEN (1)
                       ELSE (0)
                   END AS "FZG_ja_nein"
                 , [document_types]."document_type_description" AS "Document Type Description"
                 , [document_types]."document_type_in_journal" AS "Document Type In Journal"
                 , convert(varchar,[journal_accountings]."nominal_account_number") AS "Nominal Account Number"
                 , (left(convert(varchar,(([journal_accountings]."nominal_account_number"))), 1)) AS "Susa"
                 , CASE
                       WHEN ([nominal_accounts]."is_profit_loss_account" = 'J')
                           THEN ('2')
                       ELSE ('1')
                   END AS "GuV_Bilanz"
                 , CASE
                       WHEN ([journal_accountings]."skr51_cost_unit" BETWEEN 1 AND 49)
                           THEN (((convert(varchar,((([journal_accountings]."skr51_make"))))) + ' - ' + (convert(varchar,((([journal_accountings]."skr51_cost_unit"))))) + ' - ' + ([accounts_characteristics]."skr51_cost_unit_name")))
                       ELSE ((convert(varchar,((([journal_accountings]."skr51_cost_unit"))))) + ' - ' + ([accounts_characteristics]."skr51_cost_unit_name"))
                   END AS "Kostenträger"
                 , (convert(varchar,((([journal_accountings]."skr51_make"))))) + ' - ' + (convert(varchar,((([journal_accountings]."skr51_cost_unit"))))) + ' - ' + ([accounts_characteristics]."skr51_cost_unit_name") AS "Kostenträger_mit_Null"
                 , (len(convert(varchar,(([journal_accountings]."skr51_cost_unit"))) + 'Z') - 1) AS "Stellen Cost Unit"
                 , (convert(varchar,((([journal_accountings]."skr51_sales_channel"))))) + ' - ' + [accounts_characteristics]."skr51_sales_channel_name" AS "Absatzkanal"
                 , (len(convert(varchar,(([journal_accountings]."skr51_sales_channel"))) + 'Z') - 1) AS "Stellen Sales Channel"
                 , (convert(varchar,((([journal_accountings]."skr51_cost_center"))))) + ' - ' + [accounts_characteristics]."skr51_cost_center_name" AS "KST"
                 , (len(convert(varchar,(([journal_accountings]."skr51_cost_center"))) + 'Z') - 1) AS "Stellen Cost Center"
                 , CASE
                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                           THEN ([journal_accountings]."posted_count" / 100 * - 1)
                       ELSE ([journal_accountings]."posted_count" / 100)
                   END AS "Menge_2"
                 , CASE
                       WHEN ([journal_accountings]."debit_or_credit" = 'H')
                           THEN ([journal_accountings]."posted_value" / 100 * - 1)
                       ELSE ([journal_accountings]."posted_value" / 100)
                   END AS "Betrag"
                 , CASE
                       WHEN ((- 1 * datediff(DAY, (getdate()), (convert(datetime, [journal_accountings]."accounting_date")))) <= 360)
                           THEN ([journal_accountings]."invoice_number" + ' - ' + [journal_accountings]."posting_text" + '/' + [journal_accountings]."vehicle_reference" + ' - ' + (convert(varchar,((([journal_accountings]."employee_number"))))))
                       ELSE (N'Buchungen älter 360 Tage')
                   END AS "Text"
                 , (convert(varchar,((([journal_accountings]."skr51_make"))))) + ' - ' + [accounts_characteristics]."skr51_make_description" AS "Marke"
                 , [journal_accountings]."accounting_date" AS "Accounting Date"
                 , [nominal_accounts]."is_profit_loss_account" AS "Nom_Account_Is Profit Loss Account"
                 , CASE
                       WHEN ([journal_accountings]."skr51_cost_unit" BETWEEN 1 AND 49)
                           THEN ('Neuwagen')
                       WHEN ([journal_accountings]."skr51_cost_unit" BETWEEN 50 AND 59)
                           THEN ('Gebrauchtwagen')
                       WHEN ([journal_accountings]."skr51_cost_unit" BETWEEN 60 AND 69)
                           THEN (N'Teile & Zubehör')
                       WHEN ([journal_accountings]."skr51_cost_unit" BETWEEN 70 AND 79)
                           THEN ('Service')
                       WHEN ([journal_accountings]."skr51_cost_unit" = 0)
                           THEN (N'Ohne Kostenträger')

                   END AS "Free Form Document Text"
                 , [customers_suppliers]."zip_code" AS "Free Form Accounting Text"
                 , [journal_accountings]."previously_used_account_no" AS "Previously Used Account No"
                 , [journal_accountings]."skr51_cost_unit" AS "Skr51 Cost Unit"
                 , [journal_accountings]."skr51_sales_channel" AS "Skr51 Sales Channel"
                 , [journal_accountings]."skr51_cost_center" AS "Skr51 Cost Center"
                 , [journal_accountings]."skr51_make" AS "Skr51 Make"
                 , [journal_accountings]."skr51_branch" AS "Skr51 Branch"
                 , [journal_accountings]."day_of_actual_accounting" AS "Day Of Actual Accounting"
                 , [journal_accountings]."days_for_cash_discount" AS "Days For Cash Discount"
                 , [journal_accountings]."vat_key" AS "Vat Key"
                 , [journal_accountings]."account_statement_page" AS "Account Statement Page"
                 , [journal_accountings]."account_statement_number" AS "Account Statement Number"
                 , [journal_accountings]."vat_id_number" AS "Vat Id Number"
                 , [journal_accountings]."vehicle_reference" AS "Vehicle Reference"
                 , [journal_accountings]."posting_text" AS "Posting Text"
                 , [journal_accountings]."term_of_payment" AS "Term Of Payment"
                 , [journal_accountings]."cash_discount" AS "Cash Discount"
                 , [journal_accountings]."journal_line" AS "Journal Line"
                 , [journal_accountings]."journal_page" AS "Journal Page"
                 , [journal_accountings]."last_dunning_date" AS "Last Dunning Date"
                 , [journal_accountings]."dunning_level" AS "Dunning Level"
                 , [journal_accountings]."invoice_number" AS "Invoice Number"
                 , [journal_accountings]."employee_number" AS "Employee Number"
                 , [journal_accountings]."serial_number_each_month" AS "Serial Number Each Month"
                 , [journal_accountings]."account_form_page_line" AS "Account Form Page Line"
                 , [journal_accountings]."account_form_page_number" AS "Account Form Page Number"
                 , [journal_accountings]."contra_account_text" AS "Contra Account Text"
                 , [journal_accountings]."nominal_contra_account" AS "Nominal Contra Account"
                 , [journal_accountings]."customer_contra_account" AS "Customer Contra Account"
                 , [journal_accountings]."branch_number" AS "Branch Number"
                 , [journal_accountings]."posted_count" AS "Posted Count"
                 , [journal_accountings]."debit_or_credit" AS "Debit Or Credit"
                 , [journal_accountings]."posted_value" AS "Posted Value"
                 , [journal_accountings]."document_date" AS "Document Date"
                 , [journal_accountings]."clearing_number" AS "Clearing Number"
                 , [journal_accountings]."is_balanced" AS "Is Balanced"
                 , [journal_accountings]."position_in_document" AS "Position In Document"
                 , [journal_accountings]."document_number" AS "Document Number"
                 , [journal_accountings]."document_type" AS "Document Type"
            FROM [LOCOSOFT].[dbo]."nominal_accounts" [nominal_accounts]
                     INNER JOIN [LOCOSOFT].[dbo]."journal_accountings" [journal_accountings] ON ([nominal_accounts]."nominal_account_number" = [journal_accountings]."nominal_account_number")
                AND ([nominal_accounts]."subsidiary_to_company_ref" = [journal_accountings]."subsidiary_to_company_ref")
                     LEFT JOIN [LOCOSOFT].[dbo]."vehicles" [vehicles] ON (right([journal_accountings]."vehicle_reference", 17)) = [vehicles]."vin"
                     LEFT JOIN "LOCOSOFT"."dbo"."dealer_vehicles" [dealer_vehicles] ON ([vehicles]."dealer_vehicle_type" = [dealer_vehicles]."dealer_vehicle_type")
                AND ([vehicles]."dealer_vehicle_number" = [dealer_vehicles]."dealer_vehicle_number")
                     LEFT JOIN [LOCOSOFT].[dbo]."customers_suppliers" [customers_suppliers] ON [customers_suppliers]."customer_number" = [vehicles]."holder_number"
                     LEFT JOIN [LOCOSOFT].[dbo]."accounts_characteristics" [accounts_characteristics] ON ([accounts_characteristics]."skr51_make" = [journal_accountings]."skr51_make")
                AND ([accounts_characteristics]."skr51_cost_center" = [journal_accountings]."skr51_cost_center")
                AND ([accounts_characteristics]."skr51_sales_channel" = [journal_accountings]."skr51_sales_channel")
                AND ([accounts_characteristics]."skr51_cost_unit" = [journal_accountings]."skr51_cost_unit")
                AND ([accounts_characteristics]."skr51_branch" = [journal_accountings]."skr51_branch")
                AND ([accounts_characteristics]."subsidiary_to_company_ref" = [journal_accountings]."subsidiary_to_company_ref")
                     LEFT JOIN [LOCOSOFT].[dbo]."document_types" [document_types] ON [document_types]."document_type_in_journal" = [journal_accountings]."document_type"
                     LEFT JOIN [LOCOSOFT].[dbo]."invoices" [invoices] ON [invoices]."invtype_invnr" = [journal_accountings]."invoice_number"
                     LEFT JOIN [LOCOSOFT].[dbo]."makes" [makes] ON [vehicles]."make_number" = [makes]."make_number"
                     LEFT JOIN [LOCOSOFT].[dbo]."models" [models] ON ([vehicles]."make_number" = [models]."make_number")
                AND ([vehicles]."model_code" = [models]."model_code")
                     LEFT JOIN [LOCOSOFT].[dbo]."customers_suppliers" [customers_suppliers2] ON [customers_suppliers2]."customer_number" = [vehicles]."owner_number"
                     LEFT JOIN [LOCOSOFT].[dbo]."employees" [employees] ON ([vehicles]."salesman_number" <> 0)
                AND ([vehicles]."salesman_number" = [employees]."salesman_number")

            WHERE ([nominal_accounts]."is_profit_loss_account" = 'J')
              AND (trim([journal_accountings]."vehicle_reference") <> '')

              AND ([journal_accountings]."nominal_account_number" IN
                   (8000, 8001, 8010, 8011, 5002, 8100, 8110, 8111, 8200, 8210, 8510, 8800, 8820, 8830, 8870, 8928, 8934, 7000, 7010, 7100, 7101, 7110, 7111, 7120, 7200, 7210, 7240, 7510, 5001, 5005, 5006, 5007, 5008, 5003, 5004, 4300, 4560, 4580, 4530, 4540, 4590, 4591, 4690, 4621, 4622,
                    4623, 4500, 4501, 4502, 4503, 4504, 5501, 7220, 7800, 7850, 7801, 7802, 7803, 7804, 7805, 7806, 7807, 7820, 7825, 7827, 7808, 8030, 7030, 8870, 8871, 8872, 8220, 7220))) [D2]) [D1]
WHERE (
          (
              (
                  (
                      ([c143] = "Internal Number_Vehicles")
                          OR ("Internal Number_Vehicles" IS NULL)
                      )
                      OR ([c143] IS NULL)
                  )
                  AND ([c144] >= convert(datetime, '2017-01-01T00:00:00'))
              )
              AND ([c145] = N'FZG-Geschäft')
          )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
