C11/System/NAVISION/IQD/Serv_Teile/reifen_service_ausgangsrechnung.sql

select "No_3" as "No_3",
	   "Sell-to Customer No" as "Sell-to Customer No",
	   "Bill-to Customer No" as "Bill-to Customer No",
	   "Bill-to Name" as "Bill-to Name",
	   "Bill-to Address" as "Bill-to Address",
	   "Bill-to City" as "Bill-to City",
	   "Order Date" as "Order Date",
	   "Posting Date" as "Posting Date",
	   "Payment Terms Code" as "Payment Terms Code",
	   "Location Code" as "Location Code",
	   "Department Code_2" as "Department Code_2",
	   "Make Code" as "Make Code",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Salesperson Code" as "Salesperson Code",
	   "Gen Bus Posting Group_2" as "Gen Bus Posting Group_2",
	   "Sell-to Customer Name" as "Sell-to Customer Name",
	   "Sell-to Address" as "Sell-to Address",
	   "Sell-to City" as "Sell-to City",
	   "Correction" as "Correction",
	   "Document Date" as "Document Date",
	   "Order Type_2" as "Order Type_2",
	   "Service Order No_2" as "Service Order No_2",
	   "Inv Discount %" as "Inv Discount %",
	   "Customer Group Code_2" as "Customer Group Code_2",
	   "Service Order Line No_2" as "Service Order Line No_2",
	   "Branch Code" as "Branch Code",
	   "Vin_2" as "Vin_2",
	   "Mileage" as "Mileage",
	   "Model Code" as "Model Code",
	   "Model No" as "Model No",
	   "Prod Year" as "Prod Year",
	   "Model_ori" as "Model_ori",
	   "Type_Header" as "Type_Header",
	   "Initial Registration" as "Initial Registration",
	   "Customer Registration Date" as "Customer Registration Date",
	   "Document No" as "Document No",
	   "Line No" as "Line No",
	   "Type" as "Type",
	   "No_2" as "No_2",
	   "Description_2" as "Description_2",
	   "Description 2" as "Description 2",
	   "Quantity" as "Quantity",
	   "Unit Price" as "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Quantity Disc %" as "Quantity Disc %",
	   "Line Discount %" as "Line Discount %",
	   "Line Discount Amount" as "Line Discount Amount",
	   "Amount" as "Amount",
	   "Amount Including Vat" as "Amount Including Vat",
	   "Department Code" as "Department Code",
	   "Inv Discount Amount" as "Inv Discount Amount",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Unit Cost" as "Unit Cost",
	   "Order No" as "Order No",
	   "Order Line No" as "Order Line No",
	   "Order Type" as "Order Type",
	   "Item Type" as "Item Type",
	   "Vin" as "Vin",
	   "Vehicle Status" as "Vehicle Status",
	   "Service Order No" as "Service Order No",
	   "Service Order Line No" as "Service Order Line No",
	   "Labor No_2" as "Labor No_2",
	   "Item Group Code" as "Item Group Code",
	   "Customer Group Code" as "Customer Group Code",
	   "No" as "No",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Serviceberater" as "Serviceberater",
	   "Umsatzart" as "Umsatzart",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "Marke" as "Marke",
	   "Auftragsart_2" as "Auftragsart_2",
	   "Cust_Gr_Code" as "Cust_Gr_Code",
	   "Cust_Gr_Description" as "Cust_Gr_Description",
	   "Kundenart" as "Kundenart",
	   "Cust_No" as "Cust_No",
	   "Cust_Name" as "Cust_Name",
	   "Kunde" as "Kunde",
	   "Umsatz Lohn" as "Umsatz Lohn",
	   "Umsatz Teile Service_ori" as "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori" as "Umsatz Sonstiges_ori",
	   "Document No_Service_ledger" as "Document No_Service_ledger",
	   "No_Service_ledger" as "No_Service_ledger",
	   "Total Cost_Service_ledger" as "Total Cost_Service_ledger",
	   "Anzahl Datensätze" as "Anzahl Datensätze",
	   "Umsatz Teile Service" as "Umsatz Teile Service",
	   "Umsatz Sonstiges" as "Umsatz Sonstiges",
	   "Einsatz Teile Service" as "Einsatz Teile Service",
	   "verk. AW" as "verk. AW",
	   "Labor No" as "Labor No",
	   "Actual Time" as "Actual Time",
	   "benutzte AW" as "benutzte AW",
	   "Invoice Date" as "Invoice Date",
	   "Order Number" as "Order Number",
	   "Order Number_Rg_Ausg" as "Order Number_Rg_Ausg",
	   "DG1" as "DG1",
	   COUNT("Service Order Line No_2") OVER (partition by "No_3") as "DG2",
	   ("DG1" / COUNT("Service Order Line No_2") OVER (partition by "No_3")) as "Durchgänge",
	   COUNT("Service Order Line No_2") OVER (partition by "Service Order No_2") as "DG_3",
	   "Position" as "Position",
	   "Umsatz FL" as "Umsatz FL",
	   "Vehicle Category Code" as "Vehicle Category Code",
	   "Heute" as "Heute",
	   "FZG-Alter_Tage" as "FZG-Alter_Tage",
	   "No_labor" as "No_labor",
	   "Description_labor" as "Description_labor",
	   "Description 2_labor" as "Description 2_labor",
	   "Charging Group_labor" as "Charging Group_labor",
	   "Main Group Code_labor" as "Main Group Code_labor",
	   "Subgroup Code_labor" as "Subgroup Code_labor",
	   "Line Group Code_labor" as "Line Group Code_labor",
	   "No_Item" as "No_Item",
	   "Description_Item" as "Description_Item",
	   "Item Group Code_Item" as "Item Group Code_Item",
	   "Item Type_Item" as "Item Type_Item",
	   "Main Group Code_Item" as "Main Group Code_Item",
	   "Subgroup Code_Item" as "Subgroup Code_Item",
	   "Item Category_Item" as "Item Category_Item",
	   "Bmw Parts Type_Item" as "Bmw Parts Type_Item",
	   "Parts Family_Item_ori" as "Parts Family_Item_ori",
	   "Lohn/Teile" as "Lohn/Teile",
	   "Auftragsart" as "Auftragsart",
	   "Fremdwerkstatt/Endkunde" as "Fremdwerkstatt/Endkunde",
	   "Teileart" as "Teileart",
	   "Teilegruppe_Definition" as "Teilegruppe_Definition",
	   "Teilegruppe FW" as "Teilegruppe FW",
	   "Inventory Posting Group_Item" as "Inventory Posting Group_Item",
	   "Code" as "Code",
	   "Attached To Main Group" as "Attached To Main Group",
	   "Description" as "Description",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Menge" as "Menge",
	   "Parts Family_Item_alt" as "Parts Family_Item_alt",
	   "Kosten_sales_invoice_line" as "Kosten_sales_invoice_line",
	   "Parts Family_Item" as "Parts Family_Item",
	   "Menge_final" as "Menge_final",
	   "Kundennummern_Ausschluss" as "Kundennummern_Ausschluss",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name"
from 
(select "No_3",
	   "Sell-to Customer No",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Bill-to Address",
	   "Bill-to City",
	   "Order Date",
	   "Posting Date",
	   "Payment Terms Code",
	   "Location Code",
	   "Department Code_2",
	   "Make Code",
	   "Customer Posting Group",
	   "Salesperson Code",
	   "Gen Bus Posting Group_2",
	   "Sell-to Customer Name",
	   "Sell-to Address",
	   "Sell-to City",
	   "Correction",
	   "Document Date",
	   "Order Type_2",
	   "Service Order No_2",
	   "Inv Discount %",
	   "Customer Group Code_2",
	   "Service Order Line No_2",
	   "Branch Code",
	   "Vin_2",
	   "Mileage",
	   "Model Code",
	   "Model No",
	   "Prod Year",
	   "Model_ori",
	   "Type_Header",
	   "Initial Registration",
	   "Customer Registration Date",
	   "Document No",
	   "Line No",
	   "Type",
	   "No_2",
	   "Description_2",
	   "Description 2",
	   "Quantity",
	   "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Quantity Disc %",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Department Code",
	   "Inv Discount Amount",
	   "Gen Bus Posting Group",
	   "Gen Prod Posting Group",
	   "Unit Cost",
	   "Order No",
	   "Order Line No",
	   "Order Type",
	   "Item Type",
	   "Vin",
	   "Vehicle Status",
	   "Service Order No",
	   "Service Order Line No",
	   "Labor No_2",
	   "Item Group Code",
	   "Customer Group Code",
	   "No",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "Umsatzart",
	   "Make Code" as "Fabrikat",
	   "Model_ori" as "Model",
	   "Fahrzeug",
	   "Make Code" as "Marke",
	   "Auftragsart_2",
	   "Cust_Gr_Code",
	   "Cust_Gr_Description",
	   "Kundenart",
	   "Cust_No",
	   "Cust_Name",
	   "Kunde",
	   "Umsatz Lohn",
	   "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori",
	   "Document No_Service_ledger",
	   "No_Service_ledger",
	   "Total Cost_Service_ledger",
	   COUNT("No_3") OVER (partition by c155) as "Anzahl Datensätze",
	   ("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (partition by c155)) as "Umsatz Teile Service",
	   ("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (partition by c155)) as "Umsatz Sonstiges",
	   CASE WHEN (("Total Cost_Service_ledger") IS NOT NULL) THEN (("Total Cost_Service_ledger") / (COUNT("No_3") OVER (partition by c155))) ELSE (("Kosten_sales_invoice_line") / (COUNT("No_3") OVER (partition by c155))) END as "Einsatz Teile Service",
	   "verk. AW",
	   "Labor No",
	   "Actual Time",
	   "benutzte AW",
	   "Posting Date" as "Invoice Date",
	   "Order Number",
	   "Order Number_Rg_Ausg",
	   1 as "DG1",
	   "Position",
	   CASE WHEN ("Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST')) THEN (("Amount") / (COUNT("No_3") OVER (partition by c155))) ELSE (0) END as "Umsatz FL",
	   "Vehicle Category Code",
	   "Heute",
	   "FZG-Alter_Tage",
	   "No_labor",
	   "Description_labor",
	   "Description 2_labor",
	   "Charging Group_labor",
	   "Main Group Code_labor",
	   "Subgroup Code_labor",
	   "Line Group Code_labor",
	   "No_Item",
	   "Description_Item",
	   "Item Group Code_Item",
	   "Item Type_Item",
	   "Main Group Code_Item",
	   "Subgroup Code_Item",
	   "Item Category_Item",
	   "Bmw Parts Type_Item",
	   "Parts Family_Item_ori",
	   "Lohn/Teile",
	   "Auftragsart",
	   "Fremdwerkstatt/Endkunde",
	   "Teileart",
	   "Teilegruppe_Definition",
	   "Teilegruppe FW",
	   "Inventory Posting Group_Item",
	   "Code",
	   "Attached To Main Group",
	   "Description",
	   "Hauptbetrieb",
	   "Standort",
	   "Menge",
	   "Parts Family_Item_alt",
	   "Kosten_sales_invoice_line",
	   "Parts Family_Item",
	   "Menge_final",
	   "Kundennummern_Ausschluss",
	   "Hauptbetrieb" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name"
from 
(select T1."Service Order No_" as "Service Order No_2",
	   T1."No_" as "No_3",
	   (T2."Document No_" + (((T2."Line No_")))) as c155,
	   CASE WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name",
	   (CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') ELSE null END) as "Standort",
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T1."Client_DB" as "Hauptbetrieb",
	   CASE WHEN (T8."No_" IN ('022350','036287','205744')) THEN ('raus') ELSE ('rein') END as "Kundennummern_Ausschluss",
	   CASE WHEN ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('369011','369012','369022')) THEN ((CASE WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) BETWEEN 0.5 AND 1)) THEN (4) WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = -1)) THEN (-4) ELSE (((convert(float, T2."Quantity")))) END) / 4) ELSE (((convert(float, T2."Quantity")))) END as "Menge_final",
	   CASE WHEN (not (CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('36907111','36907112','36907113','36907114','36907115','Reifen fremd')) THEN ((substring((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END), 1, 6))) ELSE ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END)) END as "Parts Family_Item",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as "Kosten_sales_invoice_line",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END as "Parts Family_Item_alt",
	   CASE WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) BETWEEN 0.5 AND 1)) THEN (4) WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = -1)) THEN (-4) ELSE (((convert(float, T2."Quantity")))) END as "Menge",
	   T14."Description" as "Description",
	   T14."Attached to Main Group" as "Attached To Main Group",
	   T14."Code" as "Code",
	   T13."Inventory Posting Group" as "Inventory Posting Group_Item",
	   CASE WHEN ((CASE WHEN (T1."Item Sales Price Group" IN ('44','51','60','61','65','66','67','68','50','52','70','LACK','AL-KO','GROßKUNDE','KUNDE10','SOLOPLAN')) THEN ('Fremdwerkstatt') ELSE ('Endkunde') END) = 'Fremdwerkstatt') THEN ((CASE WHEN (((CASE WHEN (T13."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T13."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('D','E','23','26','27','30','31','32','33','24','25','28','29'))) THEN ('Umsatz FW TA1-2 D/E') WHEN (((CASE WHEN (T13."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T13."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('F','G','H','I','K','34','35','36','37','38','39','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','65','66','67','69','71','73','74','75','98','99'))) THEN ('Umsatz FW TA1-2 F-K') WHEN (T1."Item Sales Price Group" = 'LACK') THEN ('Umsatz Lack') ELSE ('Umsatz FW Rest') END)) ELSE null END as "Teilegruppe FW",
	   CASE WHEN (((CASE WHEN (T13."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T13."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('D','E','23','26','27','30','31','32','33','24','25','28','29'))) THEN ('Umsatz FW TA1-2 D/E') WHEN (((CASE WHEN (T13."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T13."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('F','G','H','I','K','34','35','36','37','38','39','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','65','66','67','69','71','73','74','75','98','99'))) THEN ('Umsatz FW TA1-2 F-K') WHEN (T1."Item Sales Price Group" = 'LACK') THEN ('Umsatz Lack') ELSE ('Umsatz FW Rest') END as "Teilegruppe_Definition",
	   CASE WHEN (T13."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T13."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END as "Teileart",
	   CASE WHEN (T1."Item Sales Price Group" IN ('44','51','60','61','65','66','67','68','50','52','70','LACK','AL-KO','GROßKUNDE','KUNDE10','SOLOPLAN')) THEN ('Fremdwerkstatt') ELSE ('Endkunde') END as "Fremdwerkstatt/Endkunde",
	   CASE WHEN (T1."No_" LIKE 'W%') THEN ('Service') WHEN (T1."No_" LIKE 'V%') THEN ('Teile') ELSE null END as "Auftragsart",
	   CASE WHEN (T2."Type" = 1) THEN ('Lohn') WHEN (T2."Type" = 2) THEN ('Teile') ELSE null END as "Lohn/Teile",
	   T13."Parts Family" as "Parts Family_Item_ori",
	   T13."BMW Parts Type" as "Bmw Parts Type_Item",
	   T13."Item Category" as "Item Category_Item",
	   T13."Subgroup Code" as "Subgroup Code_Item",
	   T13."Main Group Code" as "Main Group Code_Item",
	   T13."Item Type" as "Item Type_Item",
	   T13."Item Group Code" as "Item Group Code_Item",
	   T13."Description" as "Description_Item",
	   T13."No_" as "No_Item",
	   T12."Line Group Code" as "Line Group Code_labor",
	   T12."Subgroup Code" as "Subgroup Code_labor",
	   T12."Main Group Code" as "Main Group Code_labor",
	   T12."Charging Group" as "Charging Group_labor",
	   T12."Description 2" as "Description 2_labor",
	   T12."Description" as "Description_labor",
	   T12."No_" as "No_labor",
	   CASE WHEN (T1."VIN" IS NOT NULL) THEN ((-1 * datediff(day, ((getdate())), T1."Initial Registration"))) ELSE (0) END as "FZG-Alter_Tage",
	   (getdate()) as "Heute",
	   T11."Vehicle Category Code" as "Vehicle Category Code",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   (convert(float, T2."Amount")) as "Amount",
	   ((left((((T2."Line No_"))),6)) + ' - ' + T2."No_" + ' - ' + T2."Description") as "Position",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + (CASE WHEN (T6."Service Posting Group" IS NULL) THEN (T7."Service Posting Group") ELSE (T6."Service Posting Group") END) + ' - ' + (CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' - ' + T8."Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + (CASE WHEN (T6."Service Posting Group" IS NULL) THEN (T7."Service Posting Group") ELSE (T6."Service Posting Group") END) + ' - ' + T8."Name") ELSE null END as "Order Number_Rg_Ausg",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 180) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + T8."Name") ELSE ('Aufträge älter 180 Tage') END as "Order Number",
	   T1."Posting Date" as "Posting Date",
	   ((convert(float, T10."Actual Time"))) * 12 as "benutzte AW",
	   (convert(float, T10."Actual Time")) as "Actual Time",
	   T10."Labor No_" as "Labor No",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or (T10."Labor No_" IS NOT NULL)) THEN (((convert(float, T2."Quantity")))) ELSE (0) END as "verk. AW",
	   (convert(float, T9."Total Cost")) as "Total Cost_Service_ledger",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T2."Gen_ Prod_ Posting Group" LIKE '%SONST%')) THEN (((convert(float, T2."Amount")))) ELSE (0) END as "Umsatz Sonstiges_ori",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T2."Amount")))) ELSE (0) END as "Umsatz Teile Service_ori",
	   T9."No_" as "No_Service_ledger",
	   T9."Document No_" as "Document No_Service_ledger",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T2."Amount")))) ELSE (0) END as "Umsatz Lohn",
	   T8."No_" + ' - ' + T8."Name" as "Kunde",
	   T8."Name" as "Cust_Name",
	   T8."No_" as "Cust_No",
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T5."Description") END as "Kundenart",
	   T5."Description" as "Cust_Gr_Description",
	   T5."Code" as "Cust_Gr_Code",
	   CASE WHEN (T6."Service Posting Group" IS NULL) THEN (T7."Service Posting Group") ELSE (T6."Service Posting Group") END as "Auftragsart_2",
	   T1."Make Code" as "Make Code",
	   T1."VIN" + ' - ' + T1."Model" as "Fahrzeug",
	   T1."Model" as "Model_ori",
	   CASE WHEN (((CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T5."Description") END) = 'Intern') or (T1."Gen_ Bus_ Posting Group" = 'INT_NSTBAR')) THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END as "Serviceberater",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   T3."No_" as "No",
	   T2."Customer Group Code" as "Customer Group Code",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Labor No_" as "Labor No_2",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No",
	   T2."Vehicle Status" as "Vehicle Status",
	   T2."VIN" as "Vin",
	   T2."Item Type" as "Item Type",
	   T2."Order Type" as "Order Type",
	   T2."Order Line No_" as "Order Line No",
	   T2."Order No_" as "Order No",
	   (convert(float, T2."Unit Cost")) as "Unit Cost",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Inv_ Discount Amount" as "Inv Discount Amount",
	   T2."Department Code" as "Department Code",
	   T2."Amount Including VAT" as "Amount Including Vat",
	   T2."Line Discount Amount" as "Line Discount Amount",
	   T2."Line Discount %" as "Line Discount %",
	   T2."Quantity Disc_ %" as "Quantity Disc %",
	   T2."Unit Cost (LCY)" as "Unit Cost (lcy)",
	   (convert(float, T2."Unit Price")) as "Unit Price",
	   (convert(float, T2."Quantity")) as "Quantity",
	   T2."Description 2" as "Description 2",
	   T2."Description" as "Description_2",
	   T2."No_" as "No_2",
	   T2."Type" as "Type",
	   T2."Line No_" as "Line No",
	   T2."Document No_" as "Document No",
	   T1."Customer Registration Date" as "Customer Registration Date",
	   T1."Initial Registration" as "Initial Registration",
	   T1."Type" as "Type_Header",
	   T1."Prod_ Year" as "Prod Year",
	   T1."Model No_" as "Model No",
	   T1."Model Code" as "Model Code",
	   T1."Mileage" as "Mileage",
	   T1."VIN" as "Vin_2",
	   T1."Branch Code" as "Branch Code",
	   T1."Service Order Line No_" as "Service Order Line No_2",
	   T1."Customer Group Code" as "Customer Group Code_2",
	   T1."Inv_ Discount %" as "Inv Discount %",
	   T1."Order Type" as "Order Type_2",
	   T1."Document Date" as "Document Date",
	   T1."Correction" as "Correction",
	   T1."Sell-to City" as "Sell-to City",
	   T1."Sell-to Address" as "Sell-to Address",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group_2",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Department Code" as "Department Code_2",
	   T1."Location Code" as "Location Code",
	   T1."Payment Terms Code" as "Payment Terms Code",
	   T1."Order Date" as "Order Date",
	   T1."Bill-to City" as "Bill-to City",
	   T1."Bill-to Address" as "Bill-to Address",
	   T1."Bill-to Name" as "Bill-to Name",
	   T1."Bill-to Customer No_" as "Bill-to Customer No",
	   T1."Sell-to Customer No_" as "Sell-to Customer No"
from ((("NAVISION"."import"."Sales_Invoice_Header" T1 left outer join "NAVISION"."import"."Customer" T8 on (T8."No_" = T1."Bill-to Customer No_") and (T8."Client_DB" = T1."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T5 on (T8."Customer Group Code" = T5."Code") and (T8."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Vehicle" T11 on (T11."VIN" = T1."VIN") and (T11."Client_DB" = T1."Client_DB")),
	((((((((("NAVISION"."import"."Sales_Invoice_Line" T2 left outer join "NAVISION"."import"."Service_Header" T7 on (T2."Service Order No_" = T7."No_") and (T2."Client_DB" = T7."Client_DB")) left outer join "NAVISION"."import"."Employee" T3 on (T7."Service Advisor No_" = T3."No_") and (T7."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Archived_Service_Header" T6 on (T2."Service Order No_" = T6."No_") and (T2."Client_DB" = T6."Client_DB")) left outer join "NAVISION"."import"."Employee" T4 on (T6."Service Advisor No_" = T4."No_") and (T6."Client_DB" = T4."Client_DB")) left outer join "NAVISION"."import"."Service_Ledger_Entry" T9 on (((T2."Document No_" = T9."Document No_") and (T2."Type" = 2)) and (T2."No_" = T9."No_")) and (T9."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Labor_Ledger_Entry" T10 on ((((T2."Document No_" = T10."Document No_") and (T2."Labor No_" = T10."Labor No_")) and (T2."Service Order No_" = T10."Service Order No_")) and (T2."Service Order Line No_" = T10."Service Order Line No_")) and (T2."Client_DB" = T10."Client_DB")) left outer join "NAVISION"."import"."Labor" T12 on ((T12."No_" = T2."Labor No_") and (T12."Make Code" = T2."Labor Make Code")) and (T12."CLIENT_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Item" T13 on (T2."No_" = T13."No_") and (T2."Client_DB" = T13."Client_DB")) left outer join "NAVISION"."import"."Vehicle_Subgroup" T14 on ((T12."Main Group Code" = T14."Code") and (T12."Subgroup Code" = T14."Attached to Main Group")) and (T12."CLIENT_DB" = T14."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((T1."No_" LIKE 'W%') and (not T2."Type" IN (0,11,12))) or ((T1."No_" LIKE 'VRT%') or (T1."No_" LIKE 'VRGT%'))) and (T1."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and (T2."Type" <> 0)) and (((((((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) LIKE '3690%') or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) LIKE '8545%')) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) LIKE '8550%')) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T13."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T13."Parts Family") END) LIKE '8555%')) or ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T13."Inventory Posting Group" = 'T_SONST'))) or ((T13."Inventory Posting Group" = 'R_FR_PKW') and (not T13."No_" IN ('00004'))))) and ((CASE WHEN (T8."No_" IN ('022350','036287','205744')) THEN ('raus') ELSE ('rein') END) = 'rein'))
) D1
) D5
-- order by "No_3" asc,"Line No" asc