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

select "No" as "No",
	   "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",
	   "Order Date" as "Order Date",
	   "Posting Date" as "Posting Date",
	   "Location Code" as "Location Code",
	   "Department Code" as "Department Code",
	   "Make Code" as "Make Code",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Salesperson Code" as "Salesperson Code",
	   "Sell-to Customer Name" as "Sell-to Customer Name",
	   "Document Date" as "Document Date",
	   "Service Order No_ori" as "Service Order No_ori",
	   "Customer Group Code" as "Customer Group Code",
	   "Service Order Line No" as "Service Order Line No",
	   "Branch Code" as "Branch Code",
	   "Vin" as "Vin",
	   "Model Code" as "Model Code",
	   "Model No" as "Model No",
	   "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_1" as "No_1",
	   "Description" as "Description",
	   "Description 2" as "Description 2",
	   "Quantity" as "Quantity",
	   "Line Discount %" as "Line Discount %",
	   "Line Discount Amount" as "Line Discount Amount",
	   "Amount" as "Amount",
	   "Department Code_1" as "Department Code_1",
	   "Make Code_1" as "Make Code_1",
	   "Gen Bus Posting Group_1" as "Gen Bus Posting Group_1",
	   "Gen Prod Posting Group" as "Gen Prod Posting Group",
	   "Unit Cost" as "Unit Cost",
	   "Order No_1" as "Order No_1",
	   "Order Line No" as "Order Line No",
	   "Order Type_1" as "Order Type_1",
	   "Item Type" as "Item Type",
	   "Vin_1" as "Vin_1",
	   "Service Order No_1" as "Service Order No_1",
	   "Service Order Line No_1" as "Service Order Line No_1",
	   "Labor No" as "Labor No",
	   "Item Group Code" as "Item Group Code",
	   "No_2" as "No_2",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Serviceberater" as "Serviceberater",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "Umsatzart" as "Umsatzart",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "Marke" as "Marke",
	   "Service Posting Group" as "Service Posting Group",
	   "Auftragsart" as "Auftragsart",
	   "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",
	   "Auftragsart_1" as "Auftragsart_1",
	   "Function Code" as "Function Code",
	   "Monteur" as "Monteur",
	   "Umsatz Lohn" as "Umsatz Lohn",
	   "Umsatz Teile Service_ori" as "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori" as "Umsatz Sonstiges_ori",
	   "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_1" as "Labor No_1",
	   "Actual Time" as "Actual Time",
	   "benutzte AW" as "benutzte AW",
	   "Invoice Date" as "Invoice Date",
	   "Order Number_2" as "Order Number_2",
	   "Order Number_Rg_Ausg" as "Order Number_Rg_Ausg",
	   "DG1" as "DG1",
	   COUNT("Service Order Line No") OVER (partition by "No") as "DG2",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No")) as "Durchgänge",
	   "Order Number_Rg_Ausg_2" as "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1" as "Order Number_Rg_Ausg_1",
	   "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" as "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   (("Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" / (COUNT("Service Order Line No") OVER (partition by "Service Order No_ori"))) * 12) as "benutze AW_Stempelzeiten",
	   COUNT("Service Order Line No") OVER (partition by "Service Order No_ori") as "DG_3",
	   SUM("benutzte AW") OVER (partition by "Service Order No_ori") as "Summe benutze AW Auftrag",
	   CASE WHEN (SUM("benutzte AW") OVER (partition by "Service Order No_ori") <> 0) THEN "benutzte AW" ELSE (("Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" / (COUNT("Service Order Line No") OVER (partition by "Service Order No_ori"))) * 12) END as "benutze AW wenn_dann",
	   "Position" as "Position",
	   "Einsatz Sonstiges" as "Einsatz Sonstiges",
	   "Umsatz FL" as "Umsatz FL",
	   "Einsatz FL" as "Einsatz FL",
	   "Fahrzeugalter_Tage" as "Fahrzeugalter_Tage",
	   "Fahrzeugalter" as "Fahrzeugalter",
	   "FZG_Altersstaffel" as "FZG_Altersstaffel",
	   "Rechnung_Gutschrift" as "Rechnung_Gutschrift",
	   "PLZ_1_Stelle" as "PLZ_1_Stelle",
	   "PLZ_2_Stelle" as "PLZ_2_Stelle",
	   "PLZ_3_Stelle" as "PLZ_3_Stelle",
	   "PLZ_4_Stelle" as "PLZ_4_Stelle",
	   "PLZ" as "PLZ",
	   "Einsatz Teile" as "Einsatz Teile",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No")) as "DG",
	   "Repair_Group_Desc" as "Repair_Group_Desc",
	   "Umsatz Teile" as "Umsatz Teile",
	   "DB1_><_EK" as "DB1_><_EK",
	   "Order_Desc_100" as "Order_Desc_100",
	   "Invoice_Desc_100" as "Invoice_Desc_100",
	   "Order_Desc_30" as "Order_Desc_30",
	   "Invoice_Desc_30" as "Invoice_Desc_30",
	   "Cost_Centre_ID" as "Cost_Centre_ID",
	   "Model_Desc" as "Model_Desc",
	   "Customer_Group_Owner" as "Customer_Group_Owner",
	   "Customer_Name_Owner" as "Customer_Name_Owner",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "verk. Std." as "verk. Std.",
	   CASE WHEN (SUM("benutzte AW") OVER (partition by "Service Order No_ori") <> 0) THEN "benutzte AW" ELSE (("Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export" / (COUNT("Service Order Line No") OVER (partition by "Service Order No_ori"))) * 12) END as "ben. Zeit",
	   "Parts_Focus_Group" as "Parts_Focus_Group",
	   "Parts_Make_Desc" as "Parts_Make_Desc",
	   "Parts_Group_Desc" as "Parts_Group_Desc",
	   "Rabatt Teile" as "Rabatt Teile",
	   "Rabatt Lohn" as "Rabatt Lohn",
	   "Rabatt Sonst." as "Rabatt Sonst.",
	   "Remaining Amount" as "Remaining Amount",
	   "Betrag offen_ori" as "Betrag offen_ori",
	   ("Betrag offen_ori" / COUNT("Service Order Line No") OVER (partition by "No")) as "Betrag offen",
	   "Tage bis Rechnung_ori" as "Tage bis Rechnung_ori",
	   ("Tage bis Rechnung_ori" / COUNT("Service Order Line No") OVER (partition by "No")) as "Tage bis Rechnung",
	   "No_7+" as "No_7+",
	   "Description_7+" as "Description_7+",
	   "No_GWL" as "No_GWL",
	   "Service Order No 1" as "Service Order No 1",
	   "Service Order No" as "Service Order No",
	   "gepl. AW-Satz" as "gepl. AW-Satz",
	   "Umsatz Lohn Plan" as "Umsatz Lohn Plan",
	   "Monteur_fuer_Anzeige" as "Monteur_fuer_Anzeige",
	   "Serviceberater/Monteur" as "Serviceberater/Monteur",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name" as "Hauptbetrieb_Name",
	   "Standort_ID" as "Standort_ID",
	   "Standort_Name" as "Standort_Name",
	   "TÜV_Amount" as "TÜV_Amount",
	   "FL_Lack_Amount" as "FL_Lack_Amount",
	   "Mietw_Amount" as "Mietw_Amount",
	   "Umsatz_Sonst_Rest" as "Umsatz_Sonst_Rest"
from 
(select "No",
	   "Sell-to Customer No",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Order Date",
	   "Posting Date",
	   "Location Code",
	   "Department Code",
	   "Make Code",
	   "Customer Posting Group",
	   "Salesperson Code",
	   "Sell-to Customer Name",
	   "Document Date",
	   "Service Order No_ori",
	   "Customer Group Code",
	   "Service Order Line No",
	   "Branch Code",
	   "Vin",
	   "Model Code",
	   "Model No",
	   '' as "Model_ori",
	   "Type_Header",
	   "Initial Registration",
	   "Customer Registration Date",
	   "Document No",
	   "Line No",
	   "Type",
	   "No_1",
	   "Description",
	   '' as "Description 2",
	   "Quantity",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Department Code_1",
	   "Make Code_1",
	   "Gen Bus Posting Group_1",
	   "Gen Prod Posting Group",
	   "Unit Cost",
	   "Order No_1",
	   "Order Line No",
	   "Order Type_1",
	   "Item Type",
	   "Vin_1",
	   "Service Order No_1",
	   "Service Order Line No_1",
	   "Labor No",
	   "Item Group Code",
	   "No_2",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "Umsatzart",
	   "Fabrikat",
	   '' as "Model",
	   "Fahrzeug",
	   "Make Code" as "Marke",
	   "Service Posting Group",
	   "Auftragsart",
	   "Cust_Gr_Code",
	   "Cust_Gr_Description",
	   "Kundenart",
	   "Cust_No",
	   "Cust_Name",
	   "Kunde",
	   '' as "Auftragsart_1",
	   '' as "Function Code",
	   '' as "Monteur",
	   "Umsatz Lohn",
	   "Umsatz Teile Service_ori",
	   "Umsatz Sonstiges_ori",
	   "Total Cost_Service_ledger",
	   COUNT("No") OVER (partition by c164) as "Anzahl Datensätze",
	   ("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c164)) as "Umsatz Teile Service",
	   CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END as "Umsatz Sonstiges",
	   CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN (("Total Cost_Service_ledger") / (COUNT("No") OVER (partition by c164))) ELSE (0) END as "Einsatz Teile Service",
	   "verk. AW",
	   "Labor No_1",
	   "Actual Time",
	   "benutzte AW",
	   "Posting Date" as "Invoice Date",
	   "Order Number_2",
	   "Order Number_Rg_Ausg",
	   1 as "DG1",
	   "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1",
	   "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   "Position",
	   CASE WHEN (("Umsatz Sonstiges_ori") <> 0) THEN (c204 / (COUNT("No") OVER (partition by c164))) ELSE (0) END as "Einsatz Sonstiges",
	   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") OVER (partition by c164))) ELSE (0) END as "Umsatz FL",
	   CASE WHEN ((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") OVER (partition by c164))) ELSE (0) END) <> 0) THEN (c204 / (COUNT("No") OVER (partition by c164))) ELSE (0) END as "Einsatz FL",
	   "Fahrzeugalter_Tage",
	   "Fahrzeugalter",
	   "FZG_Altersstaffel",
	   'Rechnung' as "Rechnung_Gutschrift",
	   "PLZ_1_Stelle",
	   "PLZ_2_Stelle",
	   "PLZ_3_Stelle",
	   "PLZ_4_Stelle",
	   "PLZ",
	   (CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN (("Total Cost_Service_ledger") / (COUNT("No") OVER (partition by c164))) ELSE (0) END) as "Einsatz Teile",
	   "Repair_Group_Desc",
	   (("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c164))) as "Umsatz Teile",
	   CASE WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c164)))) - (CASE WHEN (((("Total Cost_Service_ledger") IS NOT NULL) and (not "Gen Prod Posting Group" LIKE '%FZG%')) and (not "Gen Prod Posting Group" LIKE '%SONST%')) THEN (("Total Cost_Service_ledger") / (COUNT("No") OVER (partition by c164))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as "DB1_><_EK",
	   "Order_Desc_100",
	   "Invoice_Desc_100",
	   "Order_Desc_30",
	   "Invoice_Desc_30",
	   "Department Code_1" as "Cost_Centre_ID",
	   '' as "Model_Desc",
	   "Customer_Group_Owner",
	   "Cust_Name" as "Customer_Name_Owner",
	   "Vin" as "Fahrgestellnummer",
	   "Gen Prod Posting Group" as "Produktbuchungsgruppe",
	   "verk. AW" as "verk. Std.",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc",
	   CASE WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (partition by c164)))) <> 0) THEN ((c179)) ELSE (0) END as "Rabatt Teile",
	   "Rabatt Lohn",
	   CASE WHEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END) <> 0) THEN ((c179)) ELSE (0) END as "Rabatt Sonst.",
	   "Remaining Amount",
	   "Betrag offen_ori",
	   "Tage bis Rechnung_ori",
	   '' as "No_7+",
	   '' as "Description_7+",
	   '' as "No_GWL",
	   "Service Order No_ori" as "Service Order No 1",
	   "Service Order No",
	   "gepl. AW-Satz",
	   "Umsatz Lohn Plan",
	   "Monteur_fuer_Anzeige",
	   "Serviceberater/Monteur",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name",
	   CASE WHEN ("Gen Prod Posting Group" IN ('816_SONST','817_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END as "TÜV_Amount",
	   CASE WHEN ("Gen Prod Posting Group" IN ('821_SONST','822_SONST','823_SONST','824_SONST','825_SONST','826_SONST','827_SONST','828_SONST','829_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END as "FL_Lack_Amount",
	   CASE WHEN ("Gen Prod Posting Group" IN ('879_SONST','881_SONST','882_SONST','883_SONST','884_SONST','889_SONST','894_SONST','897_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END as "Mietw_Amount",
	   (CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('816_SONST','817_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('821_SONST','822_SONST','823_SONST','824_SONST','825_SONST','826_SONST','827_SONST','828_SONST','829_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END) - (CASE WHEN ("Gen Prod Posting Group" IN ('879_SONST','881_SONST','882_SONST','883_SONST','884_SONST','889_SONST','894_SONST','897_SONST')) THEN ((CASE WHEN ((not "Gen Prod Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN (("Umsatz Sonstiges_ori") / (COUNT("No") OVER (partition by c164))) ELSE (0) END)) ELSE (0) END) as "Umsatz_Sonst_Rest"
from 
(select T1."Service Order No_" as "Service Order No_ori",
	   T1."No_" as "No",
	   (T2."Document No_" + (((T2."Line No_")))) as c164,
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   CASE WHEN (((T2."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T2."Gen_ Prod_ Posting Group" LIKE '%VKH%')) or (T2."Gen_ Prod_ Posting Group" LIKE '%SONST%')) THEN (((convert(float, T2."Amount")))) ELSE (0) END as "Umsatz Sonstiges_ori",
	   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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') 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_ID",
	   CASE WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NOT NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' / ' + T11."Last Name") WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END)) WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL) and (T11."Last Name" IS NOT NULL)) THEN ('SB fehlt / ' + T11."Last Name") ELSE null END as "Serviceberater/Monteur",
	   T11."Last Name" as "Monteur_fuer_Anzeige",
	   (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T10."Labor No_" IS NOT NULL) and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity")))) ELSE (0) END) * (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') and (((convert(float, T2."Quantity"))) <> 0)) THEN (((convert(float, T2."Unit Price")))) ELSE (0) END) as "Umsatz Lohn Plan",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') and (((convert(float, T2."Quantity"))) <> 0)) THEN (((convert(float, T2."Unit Price")))) ELSE (0) END as "gepl. AW-Satz",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 15) THEN (((T1."Service Order No_"))) ELSE null END as "Service Order No",
	   (datediff(day, T1."Order Date")) as "Tage bis Rechnung_ori",
	   CASE WHEN (((convert(float, T13."Remaining Amount"))) IS NOT NULL) THEN (((convert(float, T13."Remaining Amount")))) ELSE (0) END as "Betrag offen_ori",
	   (convert(float, T13."Remaining Amount")) as "Remaining Amount",
	   convert(float, T2."Line Discount Amount") as c179,
	   CASE WHEN (((CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T2."Amount")), T1."Posting Date")) ELSE (0) END) <> 0) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T10."Labor No_" IS NOT NULL) and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity")))) ELSE (0) END) <> 0)) THEN ((convert(float, T2."Line Discount Amount"))) ELSE (0) END as "Rabatt Lohn",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T2."Amount")))) ELSE (0) END as "Umsatz Teile Service_ori",
	   T12."Summe produktiv für Auftrag" as "Summe Produktiv Für Auftrag_aus_Stempelzeiten_Export",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" <> ('821_SONST')) THEN (((convert(float, T10."Actual Time"))) * 12) ELSE (0) END as "benutzte AW",
	   (CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or ((T10."Labor No_" IS NOT NULL) and (T2."Gen_ Prod_ Posting Group" <> '821_SONST'))) THEN (((convert(float, T2."Quantity")))) ELSE (0) END) as "verk. AW",
	   T1."VIN" as "Vin",
	   T8."Name" as "Cust_Name",
	   T1."Gen_ Bus_ Posting Group" as "Customer_Group_Owner",
	   T2."Department Code" as "Department Code_1",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 30) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' - ' + T8."Name") ELSE ('Rechnungen älter 30 Tage') END as "Invoice_Desc_30",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 30) THEN (T1."Service Order No_") ELSE ('Aufträge älter 30 Tage') END as "Order_Desc_30",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 100) THEN (T1."No_" + ' - ' + T8."Name") ELSE ('Rechnungen älter 100 Tage') END as "Invoice_Desc_100",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 100) THEN (T1."Service Order No_" + ' - ' + T8."Name") ELSE ('Aufträge älter 100 Tage') END as "Order_Desc_100",
	   (convert(float, T9."Total Cost")) as "Total Cost_Service_ledger",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (T2."Gen_ Prod_ Posting Group") ELSE null END as "Repair_Group_Desc",
	   T8."Post Code" as "PLZ",
	   (left(T8."Post Code",4)) as "PLZ_4_Stelle",
	   (left(T8."Post Code",3)) as "PLZ_3_Stelle",
	   (left(T8."Post Code",2)) as "PLZ_2_Stelle",
	   (left(T8."Post Code",1)) as "PLZ_1_Stelle",
	   CASE WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END as "FZG_Altersstaffel",
	   (CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"), T1."Posting Date")) ELSE (0) END) / 365 as "Fahrzeugalter",
	   CASE WHEN (T2."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((datediff(day, T2."Registration Date"))) ELSE (0) END as "Fahrzeugalter_Tage",
	   (convert(float, T2."Amount")) as "Amount",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as c204,
	   CASE WHEN ((day((getdate(, T1."Posting Date")) - T1."Posting Date")) <= 5) THEN ((left((((T2."Line No_"))),6)) + ' - ' + T2."No_" + ' - ' + (CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 30) THEN ((left(T2."Description",25))) ELSE null END)) ELSE null END as "Position",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 5) THEN (T1."Service Order No_") ELSE null END as "Order Number_Rg_Ausg_1",
	   CASE WHEN (((datediff(day, T1."Posting Date", (getdate()))) <= 7) and ((CASE WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NOT NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' / ' + T11."Last Name") WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END)) WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL) and (T11."Last Name" IS NOT NULL)) THEN ('SB fehlt / ' + T11."Last Name") ELSE null END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NOT NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' / ' + T11."Last Name") WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END)) WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL) and (T11."Last Name" IS NOT NULL)) THEN ('SB fehlt / ' + T11."Last Name") ELSE null END) + ' - ' + T8."Name" + ' - ' + (convert(varchar(50), year(T1."Document Date")) + '-' + convert(varchar(50), month(T1."Document Date")) + '-' + convert(varchar(50), datediff(day, T1."Posting Date", T1."Document Date")))) WHEN (((day((getdate()))) <= 7) and ((CASE WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NOT NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) + ' / ' + T11."Last Name") WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NOT NULL) and (T11."Last Name" IS NULL)) THEN ((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END)) WHEN (((CASE WHEN (T4."No_" IS NULL) THEN (T3."First Name" + ' ' + T3."Last Name") ELSE (T4."First Name" + ' ' + T4."Last Name") END) IS NULL) and (T11."Last Name" IS NOT NULL)) THEN ('SB fehlt / ' + T11."Last Name") ELSE null END) IS NULL)) THEN (T1."No_" + ' - ' + T8."Name" + ' - ' + (convert(varchar(50), year(T1."Document Date")) + '-' + convert(varchar(50), month(T1."Document Date")) + '-' + convert(varchar(50), datediff(day, T1."Posting Date", T1."Document Date")))) ELSE null END as "Order Number_Rg_Ausg_2",
	   CASE WHEN (((day((getdate()))) <= 15) 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 (T5."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 (((datediff(day, T1."Posting Date", (getdate()))) <= 15) 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 (T5."Service Posting Group") ELSE (T6."Service Posting Group") END) + ' - ' + T8."Name") ELSE null END as "Order Number_Rg_Ausg",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 180) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + T8."Name") ELSE ('Aufträge älter 180 Tage') END as "Order Number_2",
	   T1."Posting Date" as "Posting Date",
	   (convert(float, T10."Actual Time")) as "Actual Time",
	   T10."Labor No_" as "Labor No_1",
	   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."No_" as "Cust_No",
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T7."Description") END as "Kundenart",
	   T7."Description" as "Cust_Gr_Description",
	   T7."Code" as "Cust_Gr_Code",
	   CASE WHEN (T6."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T6."Service Posting Group") END as "Auftragsart",
	   T5."Service Posting Group" as "Service Posting Group",
	   T1."Make Code" as "Make Code",
	   T1."VIN" + ' - ' + '' as "Fahrzeug",
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI','BMWI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as "Fabrikat",
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') 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_2",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Labor No_" as "Labor No",
	   T2."Service Order Line No_" as "Service Order Line No_1",
	   T2."Service Order No_" as "Service Order No_1",
	   T2."VIN" as "Vin_1",
	   T2."Item Type" as "Item Type",
	   T2."Order Type" as "Order Type_1",
	   T2."Order Line No_" as "Order Line No",
	   T2."Order No_" as "Order No_1",
	   (convert(float, T2."Unit Cost")) as "Unit Cost",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group_1",
	   T2."Make Code" as "Make Code_1",
	   T2."Line Discount Amount" as "Line Discount Amount",
	   T2."Line Discount %" as "Line Discount %",
	   (convert(float, T2."Quantity")) as "Quantity",
	   CASE WHEN ((datediff(day, T1."Posting Date", (getdate()))) <= 30) THEN ((left(T2."Description",25))) ELSE null END as "Description",
	   T2."No_" as "No_1",
	   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."Model No_" as "Model No",
	   T1."Model Code" as "Model Code",
	   T1."Branch Code" as "Branch Code",
	   T1."Service Order Line No_" as "Service Order Line No",
	   T1."Customer Group Code" as "Customer Group Code",
	   T1."Document Date" as "Document Date",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Department Code" as "Department Code",
	   T1."Location Code" as "Location Code",
	   T1."Order Date" as "Order Date",
	   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" T7 on (T8."Customer Group Code" = T7."Code") and (T8."Client_DB" = T7."Client_DB")) left outer join "ims"."Monteure_fuer_Anzeige" T11 on T11."Order No_" = T1."Service Order No_") left outer join "ims"."Stempelzeiten_Monteur_Auftrag_Export_SC_Rg_Ausgang" T12 on T1."Service Order No_" = T12."Order No_"),
	((((((("NAVISION"."import"."Sales_Invoice_Line" T2 left outer join "NAVISION"."import"."Service_Header" T5 on (T2."Service Order No_" = T5."No_") and (T2."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Employee" T3 on (T5."Service Advisor No_" = T3."No_") and (T5."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 (T2."Client_DB" = T9."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"."Customer_Ledger_Entry" T13 on (T2."Document No_" = T13."Document No_") and (T2."Client_DB" = T13."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((year(T1."Posting Date")) >= (year((getdate()))) - 1) and (T1."No_" LIKE 'W%')) and (not T2."Type" IN (0,11,12))) and (not T2."No_" IN ('PFAND')))
) D1
) D5
-- order by "No" asc,"Line No" asc