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

select distinct "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",
	   "Posting Date" as "Posting Date",
	   "Payment Terms Code" as "Payment Terms Code",
	   "Payment Discount %" as "Payment Discount %",
	   "Location Code" as "Location Code",
	   "Department Code_2" as "Department Code_2",
	   "Make Code_2" as "Make Code_2",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Price Group Code" as "Price Group Code",
	   "Prices Including Vat" as "Prices Including Vat",
	   "Allow Quantity Disc" as "Allow Quantity Disc",
	   "Salesperson Code" as "Salesperson Code",
	   "On Hold" as "On Hold",
	   "Job No" as "Job No",
	   "Gen Bus Posting Group_2" as "Gen Bus Posting Group_2",
	   "Transaction Type" as "Transaction Type",
	   "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",
	   "External Document No" as "External Document No",
	   "Area" as "Area",
	   "No Series" as "No Series",
	   "User Id" as "User Id",
	   "Order Type_2" as "Order Type_2",
	   "Service Order No_2" as "Service Order No_2",
	   "Inv Discount %" as "Inv Discount %",
	   "Branch Code" as "Branch Code",
	   "Vin_2" as "Vin_2",
	   "Mileage_2" as "Mileage_2",
	   "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_2" as "Initial Registration_2",
	   "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" as "Description",
	   "Description 2" as "Description 2",
	   "Quantity" as "Quantity",
	   "Unit Price" as "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Vat %" as "Vat %",
	   "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",
	   "Make Code" as "Make 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",
	   "Registration Date" as "Registration Date",
	   "Mileage" as "Mileage",
	   "Service Order No" as "Service Order No",
	   "Service Order Line No" as "Service Order Line No",
	   "Labor No" as "Labor No",
	   "Customer Group Code" as "Customer Group Code",
	   "Item Group Code" as "Item Group Code",
	   "Service Advisor No_Archiv" as "Service Advisor No_Archiv",
	   "Service Advisor No_oA" as "Service Advisor No_oA",
	   "No_für_Archiv" as "No_für_Archiv",
	   "First Name_für_Archiv" as "First Name_für_Archiv",
	   "Last Name_für_Archiv" as "Last Name_für_Archiv",
	   "No" as "No",
	   "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_für_Archiv" as "Service Posting Group_für_Archiv",
	   "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",
	   "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",
	   "benutzte AW" as "benutzte AW",
	   "Order Number" as "Order Number",
	   "Order Number_Rg_Ausg" as "Order Number_Rg_Ausg",
	   "DG1" as "DG1",
	   COUNT("Service Order Line No") OVER (partition by "No_3") as "DG2",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No_3")) 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",
	   "Umsatz FL" as "Umsatz FL",
	   "Einsatz Sonstiges" as "Einsatz Sonstiges",
	   "Einsatz FL" as "Einsatz FL",
	   "Parts Family_ori" as "Parts Family_ori",
	   "Menge" as "Menge",
	   "Invoice Date" as "Invoice Date",
	   "Inventory Posting Group_Item" as "Inventory Posting Group_Item",
	   "Parts Family_alt" as "Parts Family_alt",
	   "Kosten_sales_invoice_line" as "Kosten_sales_invoice_line",
	   "Parts Family" as "Parts Family",
	   "rein/raus" as "rein/raus",
	   "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",
	   "Posting Date",
	   "Payment Terms Code",
	   "Payment Discount %",
	   "Location Code",
	   "Department Code_2",
	   "Make Code_2",
	   "Customer Posting Group",
	   "Price Group Code",
	   "Prices Including Vat",
	   "Allow Quantity Disc",
	   "Salesperson Code",
	   "On Hold",
	   "Job No",
	   "Gen Bus Posting Group_2",
	   "Transaction Type",
	   "Sell-to Customer Name",
	   "Sell-to Address",
	   "Sell-to City",
	   "Correction",
	   "Document Date",
	   "External Document No",
	   "Area",
	   "No Series",
	   "User Id",
	   "Order Type_2",
	   "Service Order No_2",
	   "Inv Discount %",
	   "Branch Code",
	   "Vin_2",
	   "Mileage_2",
	   "Model Code",
	   "Model No",
	   "Prod Year",
	   "Model_ori",
	   "Type_Header",
	   "Initial Registration_2",
	   "Initial Registration_2" as "Initial Registration",
	   "Customer Registration Date",
	   "Document No",
	   "Line No",
	   "Type",
	   "No_2",
	   "Description",
	   "Description 2",
	   "Quantity",
	   "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Vat %",
	   "Quantity Disc %",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Department Code",
	   "Make 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",
	   "Registration Date",
	   "Mileage",
	   "Service Order No",
	   "Service Order Line No",
	   "Labor No",
	   "Customer Group Code",
	   "Item Group Code",
	   "Service Advisor No_Archiv",
	   "Service Advisor No_oA",
	   "No_für_Archiv",
	   "First Name_für_Archiv",
	   "Last Name_für_Archiv",
	   "No",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "Hauptbetrieb",
	   "Standort",
	   "Umsatzart",
	   "Make Code_2" as "Fabrikat",
	   "Model_ori" as "Model",
	   "Fahrzeug",
	   "Make Code_2" as "Marke",
	   "Service Posting Group_für_Archiv",
	   "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",
	   "Document No_Service_ledger",
	   "No_Service_ledger",
	   "Total Cost_Service_ledger",
	   COUNT("No_3") OVER (partition by c149) as "Anzahl Datensätze",
	   ("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (partition by c149)) as "Umsatz Teile Service",
	   ("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (partition by c149)) as "Umsatz Sonstiges",
	   CASE WHEN (("Total Cost_Service_ledger") IS NOT NULL) THEN ((("Total Cost_Service_ledger")) / (COUNT("No_3") OVER (partition by c149))) ELSE (("Kosten_sales_invoice_line") / (COUNT("No_3") OVER (partition by c149))) END as "Einsatz Teile Service",
	   "verk. AW",
	   0 as "benutzte AW",
	   "Order Number",
	   "Order Number_Rg_Ausg",
	   -1 as "DG1",
	   "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1",
	   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 c149))) * -1) ELSE (0) END as "Umsatz FL",
	   CASE WHEN (("Umsatz Sonstiges_ori") <> 0) THEN ((c166 / (COUNT("No_3") OVER (partition by c149))) * -1) ELSE (0) END as "Einsatz Sonstiges",
	   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_3") OVER (partition by c149))) * -1) ELSE (0) END) <> 0) THEN ((c166 / (COUNT("No_3") OVER (partition by c149))) * -1) ELSE (0) END as "Einsatz FL",
	   "Parts Family_ori",
	   "Menge",
	   "Posting Date" as "Invoice Date",
	   "Inventory Posting Group_Item",
	   "Parts Family_alt",
	   "Kosten_sales_invoice_line",
	   "Parts Family",
	   "rein/raus",
	   "Menge_final",
	   "Kundennummern_Ausschluss",
	   "Hauptbetrieb" as "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Standort" as "Standort_ID",
	   "Standort_Name"
from 
(select T1."No_" as "No_3",
	   (T2."Document No_" + (((T2."Line No_")))) as c149,
	   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 (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) IN ('369011','369012','369022')) THEN ((CASE WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = 1)) THEN (-4) WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = -1)) THEN (4) ELSE (((convert(float, T2."Quantity"))) * -1) END) / 4) ELSE (((convert(float, T2."Quantity"))) * -1) END as "Menge_final",
	   CASE WHEN (((CASE WHEN (not CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END IN ('36907111','36907112','36907113','36907114','36907115','Reifen fremd')) THEN ((substring(CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END, 1, 6))) ELSE (CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END) END) = 'GBWR') and (T1."No_" LIKE 'VGGT%')) THEN ('raus') ELSE ('rein') END as "rein/raus",
	   CASE WHEN (not CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END IN ('36907111','36907112','36907113','36907114','36907115','Reifen fremd')) THEN ((substring(CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END, 1, 6))) ELSE (CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END) END as "Parts Family",
	   (((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost")))) * -1 as "Kosten_sales_invoice_line",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END as "Parts Family_alt",
	   T10."Inventory Posting Group" as "Inventory Posting Group_Item",
	   T1."Posting Date" as "Posting Date",
	   CASE WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = 1)) THEN (-4) WHEN (((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) IN ('369011','GBWR','369012','369022')) and (((convert(float, T2."Quantity"))) = -1)) THEN (4) ELSE (((convert(float, T2."Quantity"))) * -1) END as "Menge",
	   T10."Parts Family" as "Parts Family_ori",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   (convert(float, T2."Amount")) as "Amount",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as c166,
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T2."Gen_ Prod_ Posting Group" IN ('816_SONST','828_SONST','829_SONST','830_SONST','835_SONST','836_SONST','832_SONST','833_SONST','834_SONST'))) THEN (((convert(float, T2."Amount"))) * -1) ELSE (0) END as "Umsatz Sonstiges_ori",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 5) THEN (T2."Service Order No_") ELSE null END as "Order Number_Rg_Ausg_1",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 5) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) + ' - ' + T8."Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 5) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + T8."Name") ELSE null END as "Order Number_Rg_Ausg_2",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + (CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) + ' - ' + T8."Name") WHEN (((-1 * datediff(day, (getdate()), T1."Posting Date")) <= 4) and ((CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + T1."Service Order No_" + ' - ' + 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",
	   CASE WHEN ((T2."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or (T2."Labor No_" <> '')) THEN (((convert(float, T2."Quantity"))) * -1) ELSE (0) END as "verk. AW",
	   (convert(float, T9."Total Cost")) as "Total Cost_Service_ledger",
	   CASE WHEN (T2."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T2."Amount"))) * -1) 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"))) * -1) 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 (T7."Description") END as "Kundenart",
	   T7."Description" as "Cust_Gr_Description",
	   T7."Code" as "Cust_Gr_Code",
	   CASE WHEN (T3."Service Posting Group" IS NULL) THEN (T4."Service Posting Group") ELSE (T3."Service Posting Group") END as "Auftragsart",
	   T4."Service Posting Group" as "Service Posting Group",
	   T3."Service Posting Group" as "Service Posting Group_für_Archiv",
	   T1."Make Code" as "Make Code_2",
	   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 (T7."Description") END) = 'Intern') or (T1."Gen_ Bus_ Posting Group" = 'INT_NSTBAR')) THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   CASE WHEN (T5."No_" IS NULL) THEN (T6."First Name" + ' ' + T6."Last Name") ELSE (T5."First Name" + ' ' + T5."Last Name") END as "Serviceberater",
	   T6."Last Name" as "Last Name",
	   T6."First Name" as "First Name",
	   T6."No_" as "No",
	   T5."Last Name" as "Last Name_für_Archiv",
	   T5."First Name" as "First Name_für_Archiv",
	   T5."No_" as "No_für_Archiv",
	   T4."Service Advisor No_" as "Service Advisor No_oA",
	   T3."Service Advisor No_" as "Service Advisor No_Archiv",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Customer Group Code" as "Customer Group Code",
	   T2."Labor No_" as "Labor No",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No",
	   T2."Mileage" as "Mileage",
	   T2."Registration Date" as "Registration Date",
	   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."Make Code" as "Make Code",
	   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."VAT %" as "Vat %",
	   T2."Unit Cost (LCY)" as "Unit Cost (lcy)",
	   T2."Unit Price" as "Unit Price",
	   (convert(float, T2."Quantity")) as "Quantity",
	   T2."Description 2" as "Description 2",
	   T2."Description" as "Description",
	   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_2",
	   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_2",
	   T1."VIN" as "Vin_2",
	   T1."Branch Code" as "Branch Code",
	   T1."Inv_ Discount %" as "Inv Discount %",
	   T1."Service Order No_" as "Service Order No_2",
	   T1."Order Type" as "Order Type_2",
	   T1."User ID" as "User Id",
	   T1."No_ Series" as "No Series",
	   T1."Area" as "Area",
	   T1."External Document No_" as "External Document No",
	   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."Transaction Type" as "Transaction Type",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group_2",
	   T1."Job No_" as "Job No",
	   T1."On Hold" as "On Hold",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Allow Quantity Disc_" as "Allow Quantity Disc",
	   T1."Prices Including VAT" as "Prices Including Vat",
	   T1."Price Group Code" as "Price Group Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Department Code" as "Department Code_2",
	   T1."Location Code" as "Location Code",
	   T1."Payment Discount %" as "Payment Discount %",
	   T1."Payment Terms Code" as "Payment Terms Code",
	   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_Credit_Memo_Header" T1 left outer join "NAVISION"."import"."Customer" T8 on (T1."Bill-to Customer No_" = T8."No_") and (T1."Client_DB" = T8."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T7 on (T8."Customer Group Code" = T7."Code") and (T8."Client_DB" = T7."Client_DB")),
	(((((("NAVISION"."import"."Sales_Credit_Memo_Line" T2 left outer join "NAVISION"."import"."Archived_Service_Header" T3 on (T3."No_" = T2."Service Order No_") and (T3."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Service_Header" T4 on (T4."No_" = T2."Service Order No_") and (T4."Client_DB" = T2."Client_DB")) left outer join "NAVISION"."import"."Employee" T5 on (T3."Service Advisor No_" = T5."No_") and (T3."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Employee" T6 on (T4."Service Advisor No_" = T6."No_") and (T4."Client_DB" = T6."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"."Item" T10 on (T2."No_" = T10."No_") and (T2."Client_DB" = T10."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((T1."No_" LIKE 'WGG%') or (T1."No_" LIKE 'VGGT%')) and (not T2."Type" IN (0,11,12))) and (T1."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and ((((((T10."Parts Family" LIKE '3690%') or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) LIKE 'GBWR%')) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) LIKE '8545%')) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) LIKE '8550%')) or ((CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') WHEN (T10."Inventory Posting Group" = 'R_FR_PKW') THEN ('Reifen fremd') ELSE (T10."Parts Family") END) LIKE '8555%')) or ((T10."Inventory Posting Group" = 'R_FR_PKW') and (not T10."No_" IN ('00004'))))) and ((CASE WHEN (((CASE WHEN (not CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END IN ('36907111','36907112','36907113','36907114','36907115','Reifen fremd')) THEN ((substring(CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END, 1, 6))) ELSE (CASE WHEN ((T2."Gen_ Prod_ Posting Group" = '678_ART') and (T10."Inventory Posting Group" = 'T_SONST')) THEN ('GBWR') ELSE (T10."Parts Family") END) END) = 'GBWR') and (T1."No_" LIKE 'VGGT%')) THEN ('raus') ELSE ('rein') END) = 'rein')) and ((CASE WHEN (T8."No_" IN ('022350','036287','205744')) THEN ('raus') ELSE ('rein') END) = 'rein'))
) D1
) D4
-- order by "No_3" asc