C11/System/NAVISION/IQD/Belege/Teile_Fremdwerkstatt_FIBU_EW_Wertposten_Umsatz.sql

select "No_4" as "No_4",
	   "Sell-to Customer No_2" as "Sell-to Customer No_2",
	   "Bill-to Customer No" as "Bill-to Customer No",
	   "Bill-to Name" as "Bill-to Name",
	   "Bill-to Name 2" as "Bill-to Name 2",
	   "Order Date" as "Order Date",
	   "Posting Date_2" as "Posting Date_2",
	   "Posting Description" as "Posting Description",
	   "Due Date" as "Due Date",
	   "Payment Discount %" as "Payment Discount %",
	   "Pmt Discount Date" as "Pmt Discount Date",
	   "Location Code_2" as "Location Code_2",
	   "Department Code_3" as "Department Code_3",
	   "Make Code_2" as "Make Code_2",
	   "Customer Posting Group_2" as "Customer Posting Group_2",
	   "Invoice Disc Code" as "Invoice Disc Code",
	   "Cust Item Disc Gr" as "Cust Item Disc Gr",
	   "Salesperson Code" as "Salesperson Code",
	   "Order No_2" as "Order No_2",
	   "On Hold" as "On Hold",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "Sell-to Customer Name" as "Sell-to Customer Name",
	   "Sell-to Customer Name 2" as "Sell-to Customer Name 2",
	   "Document Date" as "Document Date",
	   "Area_2" as "Area_2",
	   "Payment Method Code" as "Payment Method Code",
	   "No Series" as "No Series",
	   "Order No Series" as "Order No Series",
	   "User Id" as "User Id",
	   "Order Type_2" as "Order Type_2",
	   "Service Order No_2" as "Service Order No_2",
	   "Item Sales Price Group" as "Item Sales Price Group",
	   "Show Discount" as "Show Discount",
	   "Customer Group Code_2" as "Customer Group Code_2",
	   "Service Order No 2" as "Service Order No 2",
	   "Service Order Line No_2" as "Service Order Line No_2",
	   "Branch Code" as "Branch Code",
	   "Vin" as "Vin",
	   "Document No" as "Document No",
	   "Line No" as "Line No",
	   "Sell-to Customer No" as "Sell-to Customer No",
	   "Type" as "Type",
	   "No_3" as "No_3",
	   "Location Code" as "Location Code",
	   "Posting Group" as "Posting Group",
	   "Quantity Disc Code" as "Quantity Disc Code",
	   "Description_2" as "Description_2",
	   "Description 2" as "Description 2",
	   "Unit Of Measure" as "Unit Of Measure",
	   "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",
	   "Allow Invoice Disc" as "Allow Invoice Disc",
	   "Department Code_2" as "Department Code_2",
	   "Make Code" as "Make Code",
	   "Price Group Code" as "Price Group Code",
	   "Allow Quantity Disc" as "Allow Quantity Disc",
	   "Area" as "Area",
	   "Unit Cost" as "Unit Cost",
	   "Book No" as "Book No",
	   "Variant Code" as "Variant Code",
	   "Qty Per Unit Of Measure" as "Qty Per Unit Of Measure",
	   "Unit Of Measure Code" as "Unit Of Measure Code",
	   "Quantity (base)" as "Quantity (base)",
	   "Order No" as "Order No",
	   "Order Line No" as "Order Line No",
	   "Posting Date" as "Posting Date",
	   "Branch Book No" as "Branch Book No",
	   "Order Type" as "Order Type",
	   "Item Type" as "Item Type",
	   "Service Order No" as "Service Order No",
	   "Service Order Line No" as "Service Order Line No",
	   "Item Group Code" as "Item Group Code",
	   "Menge" as "Menge",
	   "Unit Preis" as "Unit Preis",
	   "Unit Kosten (LCY)" as "Unit Kosten (LCY)",
	   "Line Rabatt Betrag" as "Line Rabatt Betrag",
	   "Betrag" as "Betrag",
	   "Kosten" as "Kosten",
	   "Umsatz Teile Service" as "Umsatz Teile Service",
	   "Einsatz Teile Service" as "Einsatz Teile Service",
	   "Invoice Date" as "Invoice Date",
	   "Hauptbetrieb" as "Hauptbetrieb",
	   "Standort" as "Standort",
	   "First Name" as "First Name",
	   "Last Name" as "Last Name",
	   "Serviceberater" as "Serviceberater",
	   "Department Code" as "Department Code",
	   "Order Number" as "Order Number",
	   "Fabrikat" as "Fabrikat",
	   "Model" as "Model",
	   "Fahrzeug" as "Fahrzeug",
	   "Marke" as "Marke",
	   "Umsatzart" as "Umsatzart",
	   "Auftragsart" as "Auftragsart",
	   "Kundenart" as "Kundenart",
	   "Kunde" as "Kunde",
	   "Function Code" as "Function Code",
	   "Monteur" as "Monteur",
	   "Auftragsart_1" as "Auftragsart_1",
	   "Order Number_Rg_Ausg" as "Order Number_Rg_Ausg",
	   "DG_1" as "DG_1",
	   "DG_2" as "DG_2",
	   "Durchgänge" as "Durchgänge",
	   "Rg_Ausgang_Arb_Pos" as "Rg_Ausgang_Arb_Pos",
	   "No_2" as "No_2",
	   "Name" as "Name",
	   "Customer Posting Group" as "Customer Posting Group",
	   "Customer Type" as "Customer Type",
	   "Customer Group Code" as "Customer Group Code",
	   "No" as "No",
	   "Description" as "Description",
	   "Class" as "Class",
	   "Inventory Posting Group" as "Inventory Posting Group",
	   "Commission Group" as "Commission Group",
	   "Bmw Parts Type" as "Bmw Parts Type",
	   "Item Group Code_Teilestamm" as "Item Group Code_Teilestamm",
	   "Teileart" as "Teileart",
	   "Artikelgruppe" as "Artikelgruppe",
	   "Vstufe 1" as "Vstufe 1",
	   "Zeile mit Bez" as "Zeile mit Bez",
	   "Betrieb Nr" as "Betrieb Nr",
	   "Konto" as "Konto",
	   "Konto Nr" as "Konto Nr",
	   "Text" as "Text",
	   "Jahr" as "Jahr",
	   "Bereich" as "Bereich",
	   ("Betrag" / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END)) as "Umsatzerlöse",
	   ("Einsatz Teile Service" / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END)) as "VAK",
	   "Entry No_Value_ledger_entry" as "Entry No_Value_ledger_entry",
	   "Item No_Value_ledger_entry" as "Item No_Value_ledger_entry",
	   "Posting Date_Value_ledger_entry" as "Posting Date_Value_ledger_entry",
	   "Item Ledger Entry No_Value_ledger_entry" as "Item Ledger Entry No_Value_ledger_entry",
	   "Valued Quantity_Value_ledger_entry" as "Valued Quantity_Value_ledger_entry",
	   "Invoiced Quantity_Value_ledger_entry" as "Invoiced Quantity_Value_ledger_entry",
	   "Cost Per Unit_Value_ledger_entry" as "Cost Per Unit_Value_ledger_entry",
	   "Adjusted Cost_Value_ledger_entry" as "Adjusted Cost_Value_ledger_entry",
	   "Cost Posted To G L_Value_ledger_entry" as "Cost Posted To G L_Value_ledger_entry",
	   "No_Item_No_Value_ledger_entry" as "No_Item_No_Value_ledger_entry",
	   CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END as "Anzahl_Sätze_Value_ledger_entry",
	   CASE WHEN ((("No_4" IN ('VRGT164013178','VRGT164013176')) and ("No_3" = 'X4509122825')) and (((CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (c282 / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END) < -1000000) or ((CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (c282 / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END) > 1000000))) THEN (-291.28) ELSE ((c283) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END as "VAK_Wertposten_ori",
	   CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END as "VAK_Wertposten_neu",
	   CASE WHEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END < 0) THEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END * -1) ELSE CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END END as "VAK_Wertposten_neu_1",
	   CASE WHEN (("Betrag" / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END)) < 0) THEN (CASE WHEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END < 0) THEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END * -1) ELSE CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END END * -1) ELSE CASE WHEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END < 0) THEN (CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END * -1) ELSE CASE WHEN ("Cost Per Unit_Value_ledger_entry" = -2277740.08) THEN (291.28) ELSE (((c282)) / CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE "DG_1" END) END END END as "VAK_Wertposten",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Standort_ID" as "Standort_ID"
from 
(select "No_4",
	   "Sell-to Customer No_2",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Bill-to Name 2",
	   "Order Date",
	   "Posting Date_2",
	   "Posting Description",
	   "Due Date",
	   "Payment Discount %",
	   "Pmt Discount Date",
	   "Location Code_2",
	   "Department Code_3",
	   "Make Code_2",
	   "Customer Posting Group_2",
	   "Invoice Disc Code",
	   "Cust Item Disc Gr",
	   "Salesperson Code",
	   "Order No_2",
	   "On Hold",
	   "Gen Bus Posting Group",
	   "Sell-to Customer Name",
	   "Sell-to Customer Name 2",
	   "Document Date",
	   "Area_2",
	   "Payment Method Code",
	   "No Series",
	   "Order No Series",
	   "User Id",
	   "Order Type_2",
	   "Service Order No_2",
	   "Item Sales Price Group",
	   "Show Discount",
	   "Customer Group Code_2",
	   "Service Order No 2",
	   "Service Order Line No_2",
	   "Branch Code",
	   "Vin",
	   "Document No",
	   "Line No",
	   "Sell-to Customer No",
	   "Type",
	   "No_3",
	   "Location Code",
	   "Posting Group",
	   "Quantity Disc Code",
	   "Description_2",
	   "Description 2",
	   "Unit Of Measure",
	   "Quantity",
	   "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Vat %",
	   "Quantity Disc %",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Allow Invoice Disc",
	   "Department Code_2",
	   "Make Code",
	   "Price Group Code",
	   "Allow Quantity Disc",
	   "Area",
	   "Unit Cost",
	   "Book No",
	   "Variant Code",
	   "Qty Per Unit Of Measure",
	   "Unit Of Measure Code",
	   "Quantity (base)" as "Quantity (base)",
	   "Order No",
	   "Order Line No",
	   "Posting Date",
	   "Branch Book No",
	   "Order Type",
	   "Item Type",
	   "Service Order No",
	   "Service Order Line No",
	   "Item Group Code",
	   "Menge",
	   "Unit Preis",
	   "Unit Kosten (LCY)" as "Unit Kosten (LCY)",
	   "Line Rabatt Betrag",
	   "Betrag",
	   "Kosten",
	   "Umsatz Teile Service",
	   "Einsatz Teile Service",
	   "Posting Date_2" as "Invoice Date",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "Department Code",
	   "Order Number",
	   '' as "Fabrikat",
	   '' as "Model",
	   '' as "Fahrzeug",
	   "Make Code" as "Marke",
	   'Teile' as "Umsatzart",
	   'Teile' as "Auftragsart",
	   "Gen Bus Posting Group" as "Kundenart",
	   "Kunde",
	   '' as "Function Code",
	   '' as "Monteur",
	   'Teile' as "Auftragsart_1",
	   "Order Number_Rg_Ausg",
	   1 as "DG_1",
	   COUNT("Service Order No_2") OVER (partition by c159) as "DG_2",
	   1 / (COUNT("Service Order No_2") OVER (partition by c159)) as "Durchgänge",
	   '' as "Rg_Ausgang_Arb_Pos",
	   "No_2",
	   "Name",
	   "Customer Posting Group",
	   "Customer Type",
	   "Customer Group Code",
	   "No",
	   "Description",
	   "Class",
	   "Inventory Posting Group",
	   "Commission Group",
	   "Bmw Parts Type",
	   "Item Group Code_Teilestamm",
	   "Teileart",
	   "Artikelgruppe",
	   ('Umsatz FW gesamt') as "Vstufe 1",
	   "Zeile mit Bez",
	   "Standort" as "Betrieb Nr",
	   '' as "Konto",
	   "Zeile mit Bez" as "Konto Nr",
	   '' as "Text",
	   "Posting Date_2" as "Jahr",
	   (('Umsatz FW gesamt')) as "Bereich",
	   "Entry No_Value_ledger_entry",
	   "Item No_Value_ledger_entry",
	   "Posting Date_Value_ledger_entry",
	   "Item Ledger Entry No_Value_ledger_entry",
	   "Valued Quantity_Value_ledger_entry",
	   "Invoiced Quantity_Value_ledger_entry",
	   "Cost Per Unit_Value_ledger_entry",
	   "Adjusted Cost_Value_ledger_entry",
	   "Cost Posted To G L_Value_ledger_entry",
	   "No_Item_No_Value_ledger_entry",
	   "Hauptbetrieb_ID",
	   "Standort" as "Standort_ID",
	   c162 as c282,
	   c166 as c283
from 
(select (T1."No_" + '-' + (left((((T2."Line No_"))),7)) + '-' + T6."Item No_") as "No_Item_No_Value_ledger_entry",
	   ((T1."Service Order No_" + ' - ' + T1."Bill-to Name")) as c159,
	   ((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') ELSE null END)) as "Standort",
	   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') ELSE null END)) IN ('60','70')) THEN ('2') ELSE ('1') END as "Hauptbetrieb_ID",
	   ((convert(float, T6."Valued Quantity"))) * ((convert(float, T6."Cost per Unit"))) as c162,
	   T6."Item No_" as "Item No_Value_ledger_entry",
	   (convert(float, T6."Cost per Unit")) as "Cost Per Unit_Value_ledger_entry",
	   (convert(float, T2."Amount")) as "Betrag",
	   (((convert(float, T6."Valued Quantity"))) * ((convert(float, T6."Cost per Unit")))) * -1 as c166,
	   T2."No_" as "No_3",
	   T1."No_" as "No_4",
	   (convert(float, T6."Cost Posted to G_L")) as "Cost Posted To G L_Value_ledger_entry",
	   (convert(float, T6."Adjusted Cost")) as "Adjusted Cost_Value_ledger_entry",
	   T6."Invoiced Quantity" as "Invoiced Quantity_Value_ledger_entry",
	   (convert(float, T6."Valued Quantity")) as "Valued Quantity_Value_ledger_entry",
	   T6."Item Ledger Entry No_" as "Item Ledger Entry No_Value_ledger_entry",
	   T6."Posting Date" as "Posting Date_Value_ledger_entry",
	   T6."Entry No_" as "Entry No_Value_ledger_entry",
	   ((convert(float, T2."Quantity"))) * ((convert(float, T2."Unit Cost"))) as "Einsatz Teile Service",
	   T1."Posting Date" as "Posting Date_2",
	   (CASE WHEN (((CASE WHEN (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."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 (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."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 "Zeile mit Bez",
	   CASE WHEN (T5."Item Group Code" IN ('A','C','B')) THEN ('Artikelgruppe A - C') WHEN (T5."Item Group Code" IN ('E','G','H','F','D','I','K','J')) THEN ('Artikelgruppe D - K') ELSE ('Teileart fehlt/Rest') END as "Artikelgruppe",
	   CASE WHEN (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END as "Teileart",
	   T5."Item Group Code" as "Item Group Code_Teilestamm",
	   T5."BMW Parts Type" as "Bmw Parts Type",
	   T5."Commission Group" as "Commission Group",
	   T5."Inventory Posting Group" as "Inventory Posting Group",
	   T5."Class" as "Class",
	   T5."Description" as "Description",
	   T5."No_" as "No",
	   T4."Customer Group Code" as "Customer Group Code",
	   T4."Customer Type" as "Customer Type",
	   T4."Customer Posting Group" as "Customer Posting Group",
	   T4."Name" as "Name",
	   T4."No_" as "No_2",
	   CASE WHEN ((T3."First Name" + ' ' + T3."Last Name") IS NOT NULL) THEN (T1."Service Order No_" + ' - ' + (T3."First Name" + ' ' + T3."Last Name") + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T1."Bill-to Name") WHEN ((T3."First Name" + ' ' + T3."Last Name") IS NULL) THEN (T1."Service Order No_" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date"))) + ' - ' + T1."Bill-to Name") ELSE null END as "Order Number_Rg_Ausg",
	   T1."Sell-to Customer No_" + ' - ' + T1."Bill-to Name" as "Kunde",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Make Code" as "Make Code",
	   T1."Service Order No_" + ' - ' + T1."Bill-to Name" as "Order Number",
	   T3."Department Code" as "Department Code",
	   T3."First Name" + ' ' + T3."Last Name" as "Serviceberater",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   ((convert(float, T2."Amount"))) as "Umsatz Teile Service",
	   (convert(float, T2."Unit Cost")) as "Kosten",
	   (convert(float, T2."Line Discount Amount")) as "Line Rabatt Betrag",
	   (convert(float, T2."Unit Cost (LCY)")) as "Unit Kosten (LCY)",
	   (convert(float, T2."Unit Price")) as "Unit Preis",
	   (convert(float, T2."Quantity")) as "Menge",
	   T2."Item Group Code" as "Item Group Code",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No",
	   T2."Item Type" as "Item Type",
	   T2."Order Type" as "Order Type",
	   T2."Branch Book No_" as "Branch Book No",
	   T2."Posting Date" as "Posting Date",
	   T2."Order Line No_" as "Order Line No",
	   T2."Order No_" as "Order No",
	   T2."Quantity (Base)" as "Quantity (base)",
	   T2."Unit of Measure Code" as "Unit Of Measure Code",
	   T2."Qty_ per Unit of Measure" as "Qty Per Unit Of Measure",
	   T2."Variant Code" as "Variant Code",
	   T2."Book No_" as "Book No",
	   T2."Unit Cost" as "Unit Cost",
	   T2."Area" as "Area",
	   T2."Allow Quantity Disc_" as "Allow Quantity Disc",
	   T2."Price Group Code" as "Price Group Code",
	   T2."Department Code" as "Department Code_2",
	   T2."Allow Invoice Disc_" as "Allow Invoice Disc",
	   T2."Amount Including VAT" as "Amount Including Vat",
	   T2."Amount" as "Amount",
	   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",
	   T2."Quantity" as "Quantity",
	   T2."Unit of Measure" as "Unit Of Measure",
	   T2."Description 2" as "Description 2",
	   T2."Description" as "Description_2",
	   T2."Quantity Disc_ Code" as "Quantity Disc Code",
	   T2."Posting Group" as "Posting Group",
	   T2."Location Code" as "Location Code",
	   T2."Type" as "Type",
	   T2."Sell-to Customer No_" as "Sell-to Customer No",
	   T2."Line No_" as "Line No",
	   T2."Document No_" as "Document No",
	   T1."VIN" as "Vin",
	   T1."Branch Code" as "Branch Code",
	   T1."Service Order Line No_" as "Service Order Line No_2",
	   T1."Service Order No_ 2" as "Service Order No 2",
	   T1."Customer Group Code" as "Customer Group Code_2",
	   T1."Show Discount" as "Show Discount",
	   T1."Item Sales Price Group" as "Item Sales Price Group",
	   T1."Service Order No_" as "Service Order No_2",
	   T1."Order Type" as "Order Type_2",
	   T1."User ID" as "User Id",
	   T1."Order No_ Series" as "Order No Series",
	   T1."No_ Series" as "No Series",
	   T1."Payment Method Code" as "Payment Method Code",
	   T1."Area" as "Area_2",
	   T1."Document Date" as "Document Date",
	   T1."Sell-to Customer Name 2" as "Sell-to Customer Name 2",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."On Hold" as "On Hold",
	   T1."Order No_" as "Order No_2",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Cust__Item Disc_ Gr_" as "Cust Item Disc Gr",
	   T1."Invoice Disc_ Code" as "Invoice Disc Code",
	   T1."Customer Posting Group" as "Customer Posting Group_2",
	   T1."Make Code" as "Make Code_2",
	   T1."Department Code" as "Department Code_3",
	   T1."Location Code" as "Location Code_2",
	   T1."Pmt_ Discount Date" as "Pmt Discount Date",
	   T1."Payment Discount %" as "Payment Discount %",
	   T1."Due Date" as "Due Date",
	   T1."Posting Description" as "Posting Description",
	   T1."Order Date" as "Order Date",
	   T1."Bill-to Name 2" as "Bill-to Name 2",
	   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_2"
from (("NAVISION"."import"."Sales_Invoice_Header" T1 left outer join "NAVISION"."import"."Employee" T3 on (T1."Salesperson Code" = T3."No_") and (T1."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Customer" T4 on (T1."Bill-to Customer No_" = T4."No_") and (T1."Client_DB" = T4."Client_DB")),
	(("NAVISION"."import"."Sales_Invoice_Line" T2 left outer join "NAVISION"."import"."Item" T5 on (T2."No_" = T5."No_") and (T2."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Value_Ledger_Entry" T6 on ((T6."Document No_" = T2."Document No_") and (T6."Item No_" = T2."No_")) and (T6."Client_DB" = T2."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((T1."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000')) and ((T1."No_" LIKE 'VRT%') or (T1."No_" LIKE 'VRGT%'))) and (T1."Item Sales Price Group" IN ('44','51','60','61','65','66','67','68','50','52','53','70','LACK','AL-KO','GROßKUNDE','KUNDE10','SOLOPLAN')))
) D1
) D4
-- order by "No_4" asc,"No_Item_No_Value_ledger_entry" asc,"Salesperson Code" asc,"Service Order No_2" asc,"Serviceberater" asc