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

SELECT [T1]."invoice_type" AS "Invoice Type"
     , [T1]."invoice_number" AS "Invoice Number"
     , '1' as Hauptbetrieb_imr
     , convert(varchar, [T1]."subsidiary") AS "Standort_imr"
     , convert(varchar, [T1]."subsidiary") AS "subsidiary"
     , [T1]."invoice_date" AS "Invoice Date"
     , [T1]."service_date" AS "Service Date"
     , [T1]."is_canceled" AS "Is Canceled"
     , [T1]."vehicle_number" AS "Vehicle Number"
     , (left(((([T1]."invoice_type"))), 1)) + '_' + (left(((([T1]."invoice_number"))), 9)) AS "Invoice_Type_Invoice_Number"
     , [T10].[Hauptbetrieb_ID]
     , [T10].[Hauptbetrieb_Name]
     , [T10].[Standort_ID]
     , [T10].[Standort_Name]
     , [T2]."Serviceberater" AS "Serviceberater"
     , CASE
           WHEN ([T1]."invoice_type" = 6)
               THEN ('GWL')
           WHEN (
               ([T1]."invoice_type" = 4)
                   OR ([T3]."customer_number" = 100001)
               )
               THEN ('intern')
           ELSE ('extern')
       END AS "Umsatzart"
     , [T4]."description" AS "Fabrikat_imr"
     , [T4]."description" AS "Fabrikat"
     , [T4]."description" AS "Description_Makes"
     , [T5]."description" AS "Description_Models"
     , (left((upper([T5]."description")), 3)) AS "Model"
     , (left((left(((([T1]."vehicle_number"))), 5)) + ' - ' + [T5]."description", 100)) AS "Fahrzeug"
     , CASE
           WHEN ((left(((([T6]."type"))), 1)) IN ('1', '6'))
               THEN ('41 - After Sales Kundendienst eigene Werkstatt')
           WHEN ([T6]."type" IN (40, 41, 44, 45, 46, 47, 48, 49, 88, 89, 50, 90))
               THEN ('41 - After Sales Kundendienst eigene Werkstatt')
           WHEN ((left(((([T6]."type"))), 1)) IN ('2'))
               THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
           WHEN ([T6]."type" IN (42, 92))
               THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
           WHEN ((left(((([T6]."type"))), 1)) IN ('3'))
               THEN ('45 - After Sales Kundendienst eigene Lackiererei')
           WHEN ([T6]."type" IN (43, 93))
               THEN ('45 - After Sales Kundendienst eigene Lackiererei')
           ELSE NULL
       END AS "Kostenstelle"
     , CASE
           WHEN ([T4]."description" = 'Ford')
               THEN ('1')
           WHEN ([T4]."description" = 'Nissan')
               THEN ('3')
           WHEN ([T4]."description" = 'Suzuki')
               THEN ('2')
           ELSE ('9')
       END AS "Marke"
     , 'Service' AS "Auftragsart"
     , [T3]."zip_code" AS "Geschäftsart"
     , CASE
           WHEN ([T3]."family_name" IS NOT NULL)
               THEN ((left(((([T3]."customer_number"))), 7)) + ' - ' + [T3]."family_name")
           ELSE ((left(((([T3]."customer_number"))), 7)))
       END AS "Kunde"
     , [T7]."time_units" AS "Time Units"
     , (convert(float, [T7]."time_units")) AS "Time Units_Zahl"
     , 10 AS "AW/Std."
     , ((convert(float, [T7]."time_units"))) / 10 AS "verk. Std. Focus"
     , CASE
           WHEN ([T2]."Serviceberater" IS NOT NULL)
               THEN (
               (left(((([T1]."invoice_type"))), 1)) + (left(((([T1]."invoice_number"))), 8)) + ' - ' + [T2]."Serviceberater" + ' - ' + (
                   CASE
                       WHEN ([T3]."family_name" IS NOT NULL)
                           THEN ((left(((([T3]."customer_number"))), 7)) + ' - ' + [T3]."family_name")
                       ELSE ((left(((([T3]."customer_number"))), 7)))
                   END
                   )
               )
           ELSE (
               (left(((([T1]."invoice_type"))), 1)) + (left(((([T1]."invoice_number"))), 8)) + ' - ' + (
                   CASE
                       WHEN ([T3]."family_name" IS NOT NULL)
                           THEN ((left(((([T3]."customer_number"))), 7)) + ' - ' + [T3]."family_name")
                       ELSE ((left(((([T3]."customer_number"))), 7)))
                   END
                   )
               )
       END AS "Order Number"
     , CASE
           WHEN ((- 1 * datediff(DAY, (getdate()), (convert(datetime, [T1]."invoice_date")))) <= 4)
               THEN (
               (
                   CASE
                       WHEN ([T2]."Serviceberater" IS NOT NULL)
                           THEN (
                           (left(((([T1]."invoice_type"))), 1)) + (left(((([T1]."invoice_number"))), 8)) + ' - ' + [T2]."Serviceberater" + ' - ' + (
                               CASE
                                   WHEN ([T3]."family_name" IS NOT NULL)
                                       THEN ((left(((([T3]."customer_number"))), 7)) + ' - ' + [T3]."family_name")
                                   ELSE ((left(((([T3]."customer_number"))), 7)))
                               END
                               )
                           )
                       ELSE (
                           (left(((([T1]."invoice_type"))), 1)) + (left(((([T1]."invoice_number"))), 8)) + ' - ' + (
                               CASE
                                   WHEN ([T3]."family_name" IS NOT NULL)
                                       THEN ((left(((([T3]."customer_number"))), 7)) + ' - ' + [T3]."family_name")
                                   ELSE ((left(((([T3]."customer_number"))), 7)))
                               END
                               )
                           )
                   END
                   )
               )
           ELSE NULL
       END AS "Order Number_Rg_Ausg"
     --T2."name" AS "Kundenart",
     , '' AS "Kundenart"
     , [T7]."labour_operation_id" AS "Labour Operation Id"
     , [T7]."text_line" AS "Text Line"
     , [T7]."net_price_in_order" AS "Umsatz Focus"
     , [T7]."rebate_percent" AS "Rebate Percent"
     , [T8]."Focus_Group" AS "Focus Group"
     , [T8]."Parts_Number" AS "Parts Number"
     , 1 AS "Menge Focus"
     , ((left(((([T1]."invoice_type"))), 1)) + '_' + (left(((([T1]."invoice_number"))), 9))) + '_' + [T8]."Parts_Number" AS "Inv_Type_Number_Parts_Number"
     , [T8]."GMPD_Group" AS "Gmpd Group"
     , (convert(float, [T8]."Marke_ID")) AS "Provision Betrag"
     , 1 * ((convert(float, [T8]."Marke_ID"))) AS "Provision SB"
     , 'Serviceberater' AS [Employee_Function]
     , 'Serviceberater' AS [Zuordnung_Funktion]
FROM [LOCOSOFT]."dbo"."invoices" [T1]
         LEFT JOIN [LOCOSOFT]."dbo"."customers_suppliers" [T3] ON [T3]."customer_number" = [T1]."paying_customer"
         LEFT JOIN [LOCOSOFT]."dbo"."vehicles" [T9] ON [T1]."vehicle_number" = [T9]."internal_number"
         LEFT JOIN [LOCOSOFT]."dbo"."makes" [T4] ON [T9]."make_number" = [T4]."make_number"
         LEFT JOIN [LOCOSOFT]."dbo"."models" [T5] ON ([T9]."make_number" = [T5]."make_number")
    AND ([T9]."model_code" = [T5]."model_code")
         LEFT JOIN [LOCOSOFT]."dbo"."labours" [T7] ON ([T7]."invoice_number" = [T1]."invoice_number")
    AND ([T7]."invoice_type" = [T1]."invoice_type")
         LEFT JOIN "data"."GC_Fokus_Produktgruppen" [T8] ON ([T7]."labour_operation_id" = [T8]."Parts_Number")
         LEFT JOIN [LOCOSOFT]."dbo"."charge_type_descriptions" [T6] ON ([T6]."type" = [T7]."charge_type")
    AND ([T6]."client_db" = [T7]."client_db")
         LEFT JOIN "transform_ims"."Serviceberater_Rechnung" [T2] ON [T1]."invtype_invnr" = [T2].[Invoice_Type_Invoice_Number]
         LEFT JOIN [data].[GC_Department] [T10] ON convert(varchar, [T1].[subsidiary]) = [T10].[Standort] AND [T10].[Hauptbetrieb] = '1'

WHERE ([T1]."invoice_type" BETWEEN 2 AND 6)
  AND ([T1]."is_canceled" <> 1)
  AND ([T1]."invoice_date" >= convert(datetime, '2017-01-01T00:00:00'))
  AND ([T7]."labour_operation_id" IS NOT NULL)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
