SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[Aftersales_Rechnungen_neu] AS
SELECT "Invoice Type"
     , "Type_Invoice_Types"
     , "Description_Invoice_Types"
     , "Invoice Number_2"
     , "Subsidiary"
     , "Paying Customer"
     , "Order Customer"
     , "Invoice Date"
     , "Service Date"
     , "Is Canceled"
     , "Cancelation Number"
     , "Cancelation Date"
     , "Cancelation Employee"
     , "Is Own Vehicle"
     , "Is Credit"
     , "Credit Invoice Type"
     , "Credit Invoice Number"
     , "Odometer Reading"
     , "Creating Employee"
     , "Internal Cost Account"
     , "Vehicle Number"
     , "Full Vat Basevalue"
     , "Full Vat Percentage"
     , "Full Vat Value"
     , "Reduced Vat Basevalue"
     , "Reduced Vat Percentage"
     , "Reduced Vat Value"
     , "Used Part Vat Value"
     , "Job Amount Net"
     , "Job Amount Gross"
     , "Job Rebate"
     , "Part Amount Net"
     , "Part Amount Gross"
     , "Part Rebate"
     , "Part Disposal"
     , "Total Gross"
     , "Total Net"
     , "Inv_Type_Invoice_Number"
     , "Accounting Date"
     , "Document Type"
     , "Document Number"
     , "Position In Document"
     , "Customer Number"
     , "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_falsch"
     , "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"
     , "Hauptbetrieb"
     , "Standort"
     , "Accounting Date" AS "Invoice Date_accounting"
     , "Employee Number_Employees"
     , "Name_Employees"
     , "Mechanic Number_Employees"
     , "Salesman Number_Employees"
     , "Is Business Executive_Employees"
     , "Is Master Craftsman_Employees"
     , "Serviceberater_Rg_Steller"
     , "Customer Number_Customers_Suppliers"
     , "First Name_Customers_Suppliers"
     , "Family Name_Customers_Suppliers"
     , "Kunde"
     , "Betrag"
     , "Kostenstelle"
     , "Skr51 Make" AS "Marke"
     , "Kundenart"
     , "KST_1_Stelle"
     , "Umsatzart"
     , "Auftragsart"
     , '' AS "Gesch�ftsart"
     , "Make Number"
     , "Free Form Make Text"
     , "Model Code"
     , "Free Form Model Text"
     , "Description_Makes"
     , "Description_Models"
     , "Description_Makes" AS "Fabrikat"
     , "Description_Models" AS "Model_Detail"
     , "Model"
     , (0) AS "Umsatz Lohn"
     , (0) AS "Umsatz FL"
     , (0) AS "Einsatz FL"
     , (0) AS "Umsatz Teile"
     , (0) AS "Einsatz Teile"
     , (0) AS "Umsatz MW"
     , (0) AS "Einsatz MW"
     , (0) AS "Umsatz Sonst."
     , (0) AS "Einsatz Sonst."
     , 1.0 AS "DG_1"
     , count("Invoice Type") OVER (PARTITION BY "Order Number") AS "DG_2"
     , 1.0 / (count("Invoice Type") OVER (PARTITION BY "Order Number")) AS "DG"
     , "Fahrzeug"
     , "Order Number"
     , "Invoice_Date_Uhrzeit"
     , "Order Number_Rg_Ausg"
     , '' AS "Konto Nr"
     , '' AS "Konto Bezeichnung"
     , '' AS "Ebene1"
     , '' AS "Ebene2"
     , '' AS "Ebene3"
     , '' AS "Ebene4"
     , "Serviceberater_order_pos"
     , "Serviceberater"
FROM (SELECT (
    CASE
        WHEN (
            (
                CASE
                    WHEN ([invoices]."invoice_type" = 5)
                        THEN (((left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name"))
                    ELSE ([Serviceberater_Rechnung]."Serviceberater")
                END
                ) IS NOT NULL
            )
            THEN (
            ((left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9))) + ' - ' + (
                CASE
                    WHEN ([invoices]."invoice_type" = 5)
                        THEN (((left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name"))
                    ELSE ([Serviceberater_Rechnung]."Serviceberater")
                END
                ) + ' - ' + (
                CASE
                    WHEN ([customers_suppliers]."first_name" IS NULL)
                        THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                    ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                END
                )
            )
        ELSE (
            ((left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9))) + ' - ' + (
                CASE
                    WHEN ([customers_suppliers]."first_name" IS NULL)
                        THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                    ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                END
                )
            )
    END
    ) AS "Order Number"
           , CASE
                 WHEN ([invoices]."invoice_type" = 5)
                     THEN (((left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name"))
                 ELSE ([Serviceberater_Rechnung]."Serviceberater")
             END AS "Serviceberater"
           , [Serviceberater_Rechnung]."Serviceberater" AS "Serviceberater_order_pos"
           , CASE
                 WHEN ((- 1 * datediff(DAY, (getdate()), ((convert(datetime, [invoices]."invoice_date"))))) <= 4)
                     THEN (
                     (
                         CASE
                             WHEN (
                                 (
                                     CASE
                                         WHEN ([invoices]."invoice_type" = 5)
                                             THEN (((left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name"))
                                         ELSE ([Serviceberater_Rechnung]."Serviceberater")
                                     END
                                     ) IS NOT NULL
                                 )
                                 THEN (
                                 ((left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9))) + ' - ' + (
                                     CASE
                                         WHEN ([invoices]."invoice_type" = 5)
                                             THEN (((left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name"))
                                         ELSE ([Serviceberater_Rechnung]."Serviceberater")
                                     END
                                     ) + ' - ' + (
                                     CASE
                                         WHEN ([customers_suppliers]."first_name" IS NULL)
                                             THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                                         ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                                     END
                                     )
                                 )
                             ELSE (
                                 ((left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9))) + ' - ' + (
                                     CASE
                                         WHEN ([customers_suppliers]."first_name" IS NULL)
                                             THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                                         ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
                                     END
                                     )
                                 )
                         END
                         )
                     )
                 ELSE NULL
             END AS "Order Number_Rg_Ausg"
           , (convert(datetime, [invoices]."invoice_date")) AS "Invoice_Date_Uhrzeit"
           , (left((left(((([invoices]."vehicle_number"))), 5)) + ' - ' + [models]."description", 100)) AS "Fahrzeug"
           , (left((upper([models]."description")), 3)) AS "Model"
           , [models]."description" AS "Description_Models"
           , [makes]."description" AS "Description_Makes"
           , [vehicles]."free_form_model_text" AS "Free Form Model Text"
           , [vehicles]."model_code" AS "Model Code"
           , [vehicles]."free_form_make_text" AS "Free Form Make Text"
           , [vehicles]."make_number" AS "Make Number"
           , CASE
                 WHEN ([invoices]."invoice_type" = 5)
                     THEN ('Teile')
                 ELSE ('Service')
             END AS "Auftragsart"
           , CASE
                 WHEN ([invoices]."invoice_type" = 6)
                     THEN ('GWL')
                 WHEN (
                     ([invoices]."invoice_type" = 4)
                         OR ([customers_suppliers]."customer_number" IN (1000000, 1000001, 1000002, 1000003, 1000010))
                     )
                     THEN ('intern')
                 ELSE ('extern')
             END AS "Umsatzart"
           , (left(((convert(varchar,((([journal_accountings]."skr51_cost_center"))))) + ' - ' + [accounts_characteristics]."skr51_cost_center_name"), 1)) AS "KST_1_Stelle"
           , (convert(varchar, ((([journal_accountings]."skr51_sales_channel"))))) + ' - ' + [accounts_characteristics]."skr51_sales_channel_name" AS "Kundenart"
           , [journal_accountings]."skr51_make" AS "Skr51 Make"
           , (convert(varchar, ((([journal_accountings]."skr51_cost_center"))))) + ' - ' + [accounts_characteristics]."skr51_cost_center_name" AS "Kostenstelle"
           , CASE
                 WHEN ([journal_accountings]."debit_or_credit" = 'H')
                     THEN (((convert(float, [journal_accountings]."posted_value"))) / 100 * - 1)
                 ELSE (((convert(float, [journal_accountings]."posted_value"))) / 100)
             END AS "Betrag"
           , CASE
                 WHEN ([customers_suppliers]."first_name" IS NULL)
                     THEN ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."family_name")
                 ELSE ((left(((([customers_suppliers]."customer_number"))), 7)) + ' - ' + [customers_suppliers]."first_name" + ' ' + [customers_suppliers]."family_name")
             END AS "Kunde"
           , [customers_suppliers]."family_name" AS "Family Name_Customers_Suppliers"
           , [customers_suppliers]."first_name" AS "First Name_Customers_Suppliers"
           , [customers_suppliers]."customer_number" AS "Customer Number_Customers_Suppliers"
           , (left(((([employees]."employee_number"))), 4)) + ' - ' + [employees]."name" AS "Serviceberater_Rg_Steller"
           , [employees]."is_master_craftsman" AS "Is Master Craftsman_Employees"
           , [employees]."is_business_executive" AS "Is Business Executive_Employees"
           , [employees]."salesman_number" AS "Salesman Number_Employees"
           , [employees]."mechanic_number" AS "Mechanic Number_Employees"
           , [employees]."name" AS "Name_Employees"
           , [employees]."employee_number" AS "Employee Number_Employees"
           , [journal_accountings]."accounting_date" AS "Accounting Date"
           , convert(varchar, (([journal_accountings]."branch_number"))) AS "Standort"
           , [journal_accountings]."subsidiary_to_company_ref" AS "Hauptbetrieb"
           , 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')
                 ELSE NULL
             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_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]."invoice_date" AS "Invoice Date_falsch"
           , [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"
           , CASE
                 WHEN ([journal_accountings]."nominal_account_number" = 5701)
                     THEN ((convert(varchar,((([journal_accountings]."nominal_account_number"))))) + '_' + (rtrim(((([journal_accountings]."skr51_cost_center"))))))
                 ELSE ((convert(varchar,((([journal_accountings]."nominal_account_number"))))))
             END 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"
           , (convert(float, [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]."nominal_account_number" AS "Nominal Account Number"
           , [journal_accountings]."customer_number" AS "Customer Number"
           , [journal_accountings]."position_in_document" AS "Position In Document"
           , [journal_accountings]."document_number" AS "Document Number"
           , [journal_accountings]."document_type" AS "Document Type"
           , (left(((([invoices]."invoice_type"))), 1)) + (left(((([invoices]."invoice_number"))), 9)) AS "Inv_Type_Invoice_Number"
           , [invoices]."total_net" AS "Total Net"
           , [invoices]."total_gross" AS "Total Gross"
           , [invoices]."part_disposal" AS "Part Disposal"
           , [invoices]."part_rebate" AS "Part Rebate"
           , [invoices]."part_amount_gross" AS "Part Amount Gross"
           , [invoices]."part_amount_net" AS "Part Amount Net"
           , [invoices]."job_rebate" AS "Job Rebate"
           , [invoices]."job_amount_gross" AS "Job Amount Gross"
           , [invoices]."job_amount_net" AS "Job Amount Net"
           , [invoices]."used_part_vat_value" AS "Used Part Vat Value"
           , [invoices]."reduced_vat_value" AS "Reduced Vat Value"
           , [invoices]."reduced_vat_percentage" AS "Reduced Vat Percentage"
           , [invoices]."reduced_vat_basevalue" AS "Reduced Vat Basevalue"
           , [invoices]."full_vat_value" AS "Full Vat Value"
           , [invoices]."full_vat_percentage" AS "Full Vat Percentage"
           , [invoices]."full_vat_basevalue" AS "Full Vat Basevalue"
           , [invoices]."vehicle_number" AS "Vehicle Number"
           , [invoices]."internal_cost_account" AS "Internal Cost Account"
           , [invoices]."creating_employee" AS "Creating Employee"
           , [invoices]."odometer_reading" AS "Odometer Reading"
           , [invoices]."credit_invoice_number" AS "Credit Invoice Number"
           , [invoices]."credit_invoice_type" AS "Credit Invoice Type"
           , [invoices]."is_credit" AS "Is Credit"
           , [invoices]."is_own_vehicle" AS "Is Own Vehicle"
           , [invoices]."cancelation_employee" AS "Cancelation Employee"
           , [invoices]."cancelation_date" AS "Cancelation Date"
           , [invoices]."cancelation_number" AS "Cancelation Number"
           , [invoices]."is_canceled" AS "Is Canceled"
           , [invoices]."service_date" AS "Service Date"
           , [invoices]."invoice_date" AS "Invoice Date"
           , [invoices]."order_customer" AS "Order Customer"
           , [invoices]."paying_customer" AS "Paying Customer"
           , [invoices]."subsidiary" AS "Subsidiary"
           , [invoices]."invoice_number" AS "Invoice Number_2"
           , [invoice_types]."description" AS "Description_Invoice_Types"
           , [invoice_types]."type" AS "Type_Invoice_Types"
           , [invoices]."invoice_type" AS "Invoice Type"
      FROM [LOCOSOFT]."dbo"."invoices" [invoices]
               LEFT JOIN [LOCOSOFT]."dbo"."invoice_types" [invoice_types] ON [invoice_types]."type" = [invoices]."invoice_type"
               LEFT JOIN [LOCOSOFT]."dbo"."journal_accountings" [journal_accountings] ON [journal_accountings]."invoice_number" = [invoices]."invtype_invnr"
               LEFT JOIN [LOCOSOFT]."dbo"."customers_suppliers" [customers_suppliers] ON [customers_suppliers]."customer_number" = [invoices]."paying_customer"
               LEFT JOIN [LOCOSOFT]."dbo"."employees" [employees] ON [invoices]."creating_employee" = [employees]."employee_number"
               LEFT JOIN [LOCOSOFT]."dbo"."accounts_characteristics" [accounts_characteristics] ON
          ([accounts_characteristics]."skr51_branch" = [journal_accountings]."skr51_branch")
              AND ([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]."subsidiary_to_company_ref" = [journal_accountings]."subsidiary_to_company_ref")
               LEFT JOIN [LOCOSOFT]."dbo"."vehicles" [vehicles] ON [invoices]."vehicle_number" = [vehicles]."internal_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 "transform_ims"."Serviceberater_Rechnung" [Serviceberater_Rechnung] ON [invoices]."invtype_invnr" = [Serviceberater_Rechnung].[Invoice_Type_Invoice_Number]
      WHERE ([invoices]."invoice_type" BETWEEN 2 AND 6)
        AND (left(convert(varchar, [journal_accountings]."nominal_account_number"), 1) IN ('4', '5', '7', '8'))
        AND ([invoices]."invoice_date" >= convert(datetime, '2019-01-01T00:00:00'))
        AND ([invoices]."is_canceled" <> 1)
        AND ([journal_accountings]."nominal_account_number" BETWEEN 0 AND 9999)

         -- order by "Order Number" asc,"Document Number" asc,"Position In Document" asc,"Inv_Type_Invoice_Number" asc
     ) [D1]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
