C11/System/ARI/IQD/Service/Service_Ausgangsrechnung_ab_2011.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",
	   "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" as "Department Code",
	   "Make Code" as "Make Code",
	   "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",
	   "Order No" as "Order No",
	   "On Hold" as "On Hold",
	   "Gen Bus Posting Group" as "Gen Bus Posting Group",
	   "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",
	   "Shipping Agent Code" as "Shipping Agent Code",
	   "No Series" as "No Series",
	   "Order No Series" as "Order No Series",
	   "User Id" as "User Id",
	   "Order Type" as "Order Type",
	   "Service Order No" as "Service Order No",
	   "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_ori" as "Model_ori",
	   "Document No" as "Document No",
	   "Line No" as "Line No",
	   "No" as "No",
	   "Description" as "Description",
	   "Description 2" as "Description 2",
	   "Quantity" as "Quantity",
	   "Unit Price" as "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "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",
	   "Item Group Code" as "Item Group Code",
	   "Customer Group Code" as "Customer 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 Std" as "verk Std",
	   "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") OVER (partition by "No") as "DG2",
	   ("DG1" / COUNT("Service Order Line No") OVER (partition by "No")) as "DG",
	   "Order Number_Rg_Ausg_2" as "Order Number_Rg_Ausg_2",
	   "Order Number_Rg_Ausg_1" as "Order Number_Rg_Ausg_1",
	   "Charging Group No" as "Charging Group No",
	   "ben  Zeit" as "ben  Zeit",
	   "EW Lohn Basis (Std)" as "EW Lohn Basis (Std)",
	   "EW Lohn" as "EW Lohn",
	   "Cust_No_Verkaufskunde" as "Cust_No_Verkaufskunde",
	   "Cust_Name_Verkaufskunde" as "Cust_Name_Verkaufskunde",
	   "Cust_Group_Description_Verkaufskunde" as "Cust_Group_Description_Verkaufskunde",
	   "Kundenart_Verkaufskunde" as "Kundenart_Verkaufskunde",
	   "Kunde_Verkaufskunde" as "Kunde_Verkaufskunde",
	   "Auftragsposition" as "Auftragsposition",
	   "Rabatt Lohn" as "Rabatt Lohn",
	   "NL Teile_ori" as "NL Teile_ori",
	   "Rabatt Teile" as "Rabatt Teile",
	   "Hauptbetrieb_ID" as "Hauptbetrieb_ID",
	   "Standort_ID" as "Standort_ID",
	   "NL Lohn %" as "NL Lohn %",
	   "Nachlass > 90 %" as "Nachlass > 90 %",
	   "NL Teile %" as "NL Teile %",
	   "Zuordnung_Funktion" as "Zuordnung_Funktion",
	   "Cost_Centre_ID" as "Cost_Centre_ID",
	   "Order_Desc_30" as "Order_Desc_30",
	   "Invoice_Desc_30" as "Invoice_Desc_30",
	   "Order_Desc_100" as "Order_Desc_100",
	   "Invoice_Desc_100" as "Invoice_Desc_100",
	   "Model_Desc" as "Model_Desc",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Customer_Group_Owner" as "Customer_Group_Owner",
	   "Fahrzeugalter_Tage" as "Fahrzeugalter_Tage",
	   "Fahrzeugalter" as "Fahrzeugalter",
	   "FZG-Altersstaffel" as "FZG-Altersstaffel",
	   "Repair_Group_Desc" as "Repair_Group_Desc",
	   "DB1_><_EK" as "DB1_><_EK",
	   "Rechnung_Gutschrift" as "Rechnung_Gutschrift",
	   "Parts_Group_Desc" as "Parts_Group_Desc",
	   "Parts_Make_Desc" as "Parts_Make_Desc",
	   "Parts_Focus_Group" as "Parts_Focus_Group",
	   "Post Code" as "Post Code",
	   "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",
	   "Customer_Name_Owner" as "Customer_Name_Owner",
	   "Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   "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 c173 as "No",
	   c304 as "Sell-to Customer No",
	   c303 as "Bill-to Customer No",
	   c302 as "Bill-to Name",
	   c301 as "Bill-to Address",
	   c300 as "Bill-to City",
	   c299 as "Order Date",
	   c216 as "Posting Date",
	   c298 as "Payment Terms Code",
	   c297 as "Location Code",
	   c196 as "Department Code",
	   c296 as "Make Code",
	   c295 as "Customer Posting Group",
	   c294 as "Price Group Code",
	   c200 as "Prices Including Vat",
	   c293 as "Allow Quantity Disc",
	   c292 as "Salesperson Code",
	   c291 as "Order No",
	   c290 as "On Hold",
	   c189 as "Gen Bus Posting Group",
	   c289 as "Transaction Type",
	   c288 as "Sell-to Customer Name",
	   c287 as "Sell-to Address",
	   c286 as "Sell-to City",
	   c285 as "Correction",
	   c284 as "Document Date",
	   c283 as "External Document No",
	   c282 as "Area",
	   c281 as "Shipping Agent Code",
	   c280 as "No Series",
	   c279 as "Order No Series",
	   c278 as "User Id",
	   c277 as "Order Type",
	   c276 as "Service Order No",
	   c275 as "Customer Group Code",
	   c274 as "Service Order Line No",
	   c273 as "Branch Code",
	   c190 as "Vin",
	   c191 as "Model_ori",
	   c272 as "Document No",
	   c271 as "Line No",
	   c270 as "No",
	   c269 as "Description",
	   '' as "Description 2",
	   c268 as "Quantity",
	   c267 as "Unit Price",
	   c266 as "Unit Cost (lcy)",
	   c265 as "Line Discount Amount",
	   c264 as "Amount",
	   c263 as "Amount Including Vat",
	   c262 as "Department Code",
	   c261 as "Make Code",
	   c260 as "Inv Discount Amount",
	   c259 as "Gen Bus Posting Group",
	   c176 as "Gen Prod Posting Group",
	   c258 as "Unit Cost",
	   c257 as "Order No",
	   c256 as "Order Line No",
	   c255 as "Order Type",
	   c254 as "Item Type",
	   c253 as "Vin",
	   c252 as "Vehicle Status",
	   c251 as "Registration Date",
	   c250 as "Mileage",
	   c249 as "Service Order No",
	   c248 as "Service Order Line No",
	   c247 as "Labor No",
	   c246 as "Item Group Code",
	   c245 as "Customer Group Code",
	   c244 as "Service Advisor No_Archiv",
	   c243 as "Service Advisor No_oA",
	   c242 as "No_für_Archiv",
	   c241 as "First Name_für_Archiv",
	   c240 as "Last Name_für_Archiv",
	   c239 as "No",
	   c238 as "First Name",
	   c237 as "Last Name",
	   c236 as "Serviceberater",
	   '1' as "Hauptbetrieb",
	   c201 as "Standort",
	   c235 as "Umsatzart",
	   c234 as "Fabrikat",
	   c191 as "Model",
	   c233 as "Fahrzeug",
	   c232 as "Marke",
	   c231 as "Service Posting Group_für_Archiv",
	   c230 as "Service Posting Group",
	   c229 as "Auftragsart",
	   c228 as "Cust_Gr_Code",
	   c227 as "Cust_Gr_Description",
	   c226 as "Kundenart",
	   c225 as "Cust_No",
	   c177 as "Cust_Name",
	   c224 as "Kunde",
	   '' as "Auftragsart_1",
	   '' as "Function Code",
	   '' as "Monteur",
	   c223 as "Umsatz Lohn",
	   c183 as "Umsatz Teile Service_ori",
	   c175 as "Umsatz Sonstiges_ori",
	   c222 as "Document No_Service_ledger",
	   c221 as "No_Service_ledger",
	   c184 as "Total Cost_Service_ledger",
	   COUNT(c173) OVER (partition by c174) as "Anzahl Datensätze",
	   (c183) / (COUNT(c173) OVER (partition by c174)) as "Umsatz Teile Service",
	   (c175) / (COUNT(c173) OVER (partition by c174)) as "Umsatz Sonstiges",
	   CASE WHEN ((c184) IS NOT NULL) THEN ((c184) / (COUNT(c173) OVER (partition by c174))) ELSE (0) END as "Einsatz Teile Service",
	   c220 as "verk Std",
	   c219 as "Labor No",
	   c218 as "Actual Time",
	   c217 as "benutzte AW",
	   c216 as "Invoice Date",
	   c215 as "Order Number",
	   c214 as "Order Number_Rg_Ausg",
	   1 as "DG1",
	   c213 as "Order Number_Rg_Ausg_2",
	   c212 as "Order Number_Rg_Ausg_1",
	   c211 as "Charging Group No",
	   c210 as "ben  Zeit",
	   c209 as "EW Lohn Basis (Std)",
	   c208 as "EW Lohn",
	   c207 as "Cust_No_Verkaufskunde",
	   c206 as "Cust_Name_Verkaufskunde",
	   c205 as "Cust_Group_Description_Verkaufskunde",
	   c204 as "Kundenart_Verkaufskunde",
	   c203 as "Kunde_Verkaufskunde",
	   c202 as "Auftragsposition",
	   c199 as "Rabatt Lohn",
	   c197 as "NL Teile_ori",
	   (c197) / (COUNT(c173) OVER (partition by c174)) as "Rabatt Teile",
	   '1' as "Hauptbetrieb_ID",
	   c201 as "Standort_ID",
	   c198 as "NL Lohn %",
	   CASE WHEN ((((c198) > 90) and ((c199) <> 0)) and (c200 <> 1)) THEN ('Nachlass > 90 %') WHEN ((((CASE WHEN ((((c183) / (COUNT(c173) OVER (partition by c174))) + ((c197) / (COUNT(c173) OVER (partition by c174)))) <> 0) THEN (((c197) / (COUNT(c173) OVER (partition by c174))) / (((c183) / (COUNT(c173) OVER (partition by c174))) + ((c197) / (COUNT(c173) OVER (partition by c174)))) * 100) ELSE (0) END) > 90) and (((c197) / (COUNT(c173) OVER (partition by c174))) <> 0)) and (c200 <> 1)) THEN ('Nachlass > 90 %') ELSE ('Nachlass < 90 %') END as "Nachlass > 90 %",
	   CASE WHEN ((((c183) / (COUNT(c173) OVER (partition by c174))) + ((c197) / (COUNT(c173) OVER (partition by c174)))) <> 0) THEN (((c197) / (COUNT(c173) OVER (partition by c174))) / (((c183) / (COUNT(c173) OVER (partition by c174))) + ((c197) / (COUNT(c173) OVER (partition by c174)))) * 100) ELSE (0) END as "NL Teile %",
	   'Serviceberater' as "Zuordnung_Funktion",
	   c196 as "Cost_Centre_ID",
	   c195 as "Order_Desc_30",
	   c194 as "Invoice_Desc_30",
	   c193 as "Order_Desc_100",
	   c192 as "Invoice_Desc_100",
	   c191 as "Model_Desc",
	   c190 as "Fahrgestellnummer",
	   c189 as "Customer_Group_Owner",
	   c188 as "Fahrzeugalter_Tage",
	   c187 as "Fahrzeugalter",
	   c186 as "FZG-Altersstaffel",
	   c185 as "Repair_Group_Desc",
	   CASE WHEN (((c183) / (COUNT(c173) OVER (partition by c174))) - (CASE WHEN ((c184) IS NOT NULL) THEN ((c184) / (COUNT(c173) OVER (partition by c174))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as "DB1_><_EK",
	   'Rechnung' as "Rechnung_Gutschrift",
	   '' as "Parts_Group_Desc",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Focus_Group",
	   c178 as "Post Code",
	   c182 as "PLZ_1_Stelle",
	   c181 as "PLZ_2_Stelle",
	   c180 as "PLZ_3_Stelle",
	   c179 as "PLZ_4_Stelle",
	   c178 as "PLZ",
	   c177 as "Customer_Name_Owner",
	   c176 as "Produktbuchungsgruppe",
	   CASE WHEN (c176 IN ('816_SONST','817_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END as "TÜV_Amount",
	   CASE WHEN (c176 IN ('821_SONST','823_SONST','828_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END as "FL_Lack_Amount",
	   CASE WHEN (c176 IN ('881_SONST','886_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END as "Mietw_Amount",
	   ((c175) / (COUNT(c173) OVER (partition by c174))) - (CASE WHEN (c176 IN ('816_SONST','817_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END) - (CASE WHEN (c176 IN ('821_SONST','823_SONST','828_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END) - (CASE WHEN (c176 IN ('881_SONST','886_SONST')) THEN (((c175) / (COUNT(c173) OVER (partition by c174)))) ELSE (0) END) as "Umsatz_Sonst_Rest"
from 
(select T1."No_" as c173,
	   (T3."Document No_" + (cast_numberToString(cast_integer(T3."Line No_")))) as c174,
	   CASE WHEN ((T3."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T3."Gen_ Prod_ Posting Group" LIKE '%SONST%')) THEN (((convert(float, T3."Amount")))) ELSE (0) END as c175,
	   T3."Gen_ Prod_ Posting Group" as c176,
	   T9."Name" as c177,
	   T9."Post Code" as c178,
	   (left(T9."Post Code",4)) as c179,
	   (left(T9."Post Code",3)) as c180,
	   (left(T9."Post Code",2)) as c181,
	   (left(T9."Post Code",1)) as c182,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T3."Amount")))) ELSE (0) END as c183,
	   (convert(float, T10."Total Cost")) as c184,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (T3."Gen_ Prod_ Posting Group") ELSE null END as c185,
	   CASE WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END as c186,
	   (CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 as c187,
	   CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END as c188,
	   T1."Gen_ Bus_ Posting Group" as c189,
	   T2."VIN" as c190,
	   T2."Model" as c191,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN (T1."No_" + ' - ' + T9."Name") ELSE ('Rechnungen älter 100 Tage') END as c192,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) + ' - ' + T9."Name") ELSE ('Aufträge älter 100 Tage') END as c193,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 30) THEN (T1."No_" + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name") ELSE ('Rechnungen älter 30 Tage') END as c194,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 30) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE ('Aufträge älter 30 Tage') END as c195,
	   T1."Shortcut Dimension 1 Code" as c196,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T3."Line Discount Amount")))) ELSE (0) END as c197,
	   CASE WHEN (((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount")))) ELSE (0) END) + (CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount")))) ELSE (0) END)) <> 0) THEN ((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount")))) ELSE (0) END) / ((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount")))) ELSE (0) END) + (CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount")))) ELSE (0) END)) * 100) ELSE (0) END as c198,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount")))) ELSE (0) END as c199,
	   T1."Prices Including VAT" as c200,
	   (CASE WHEN (T1."Location Code" = 'BUR') THEN ('10') WHEN (T1."Location Code" = 'MUE') THEN ('20') ELSE null END) as c201,
	   CASE WHEN ((CASE WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) ELSE ('Rechnungen älter 30 Tage') END) <> 'Rechnungen älter 30 Tage') THEN ((rtrim((cast_numberToString(cast_integer(T3."Line No_"))))) + ' - ' + T3."No_" + ' - ' + (CASE WHEN (T3."Description" LIKE '%;%') THEN ((substring(pack(T3."Description") from 1 for POSITION(' ' IN pack(T3."Description") + ' ') - 1))) ELSE (T3."Description") END)) ELSE ('Rechnungen älter 30 Tage') END as c202,
	   T12."No_" + ' - ' + T12."Name" as c203,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T13."Description") END as c204,
	   T13."Description" as c205,
	   T12."Name" as c206,
	   T12."No_" as c207,
	   ((((convert(float, T11."Actual Time"))) * 12) / 12) * (CASE WHEN (T11."Charging Group No_" = 'MECH') THEN (40) WHEN (T11."Charging Group No_" = 'ELEK') THEN (40) WHEN (T11."Charging Group No_" = 'KARO') THEN (42) ELSE (0) END) as c208,
	   CASE WHEN (T11."Charging Group No_" = 'MECH') THEN (40) WHEN (T11."Charging Group No_" = 'ELEK') THEN (40) WHEN (T11."Charging Group No_" = 'KARO') THEN (42) ELSE (0) END as c209,
	   (((convert(float, T11."Actual Time"))) * 12) / 12 as c210,
	   T11."Charging Group No_" as c211,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE null END as c212,
	   CASE WHEN (((day((now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name") WHEN (((day((now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) + ' - ' + T9."Name") ELSE null END as c213,
	   CASE WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) ELSE ('Rechnungen älter 30 Tage') END as c214,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 180) THEN (T1."No_" + ' - ' + (CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) + ' - ' + T9."Name") ELSE ('Aufträge älter 180 Tage') END as c215,
	   T1."Posting Date" as c216,
	   ((convert(float, T11."Actual Time"))) * 12 as c217,
	   (convert(float, T11."Actual Time")) as c218,
	   T11."Labor No_" as c219,
	   CASE WHEN ((T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') or (T11."Labor No_" IS NOT NULL)) THEN (((convert(float, T3."Quantity")))) ELSE (0) END as c220,
	   T10."No_" as c221,
	   T10."Document No_" as c222,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount")))) ELSE (0) END as c223,
	   T9."No_" + ' - ' + T9."Name" as c224,
	   T9."No_" as c225,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T8."Description") END as c226,
	   T8."Description" as c227,
	   T8."Code" as c228,
	   CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END as c229,
	   T5."Service Posting Group" as c230,
	   T4."Service Posting Group" as c231,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT','BMWVW')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c232,
	   T2."VIN" + ' - ' + T2."Model" as c233,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c234,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') WHEN (T1."No_ Series" LIKE 'I%') THEN ('Intern') ELSE ('Extern') END as c235,
	   CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END as c236,
	   T7."Last Name" as c237,
	   T7."First Name" as c238,
	   T7."No_" as c239,
	   T6."Last Name" as c240,
	   T6."First Name" as c241,
	   T6."No_" as c242,
	   T5."Service Advisor No_" as c243,
	   T4."Service Advisor No_" as c244,
	   T3."Customer Group Code" as c245,
	   T3."Item Group Code" as c246,
	   T3."Labor No_" as c247,
	   T3."Service Order Line No_" as c248,
	   T3."Service Order No_" as c249,
	   T3."Mileage" as c250,
	   T3."Registration Date" as c251,
	   T3."Vehicle Status" as c252,
	   T3."VIN" as c253,
	   T3."Item Type" as c254,
	   T3."Order Type" as c255,
	   T3."Order Line No_" as c256,
	   T3."Order No_" as c257,
	   T3."Unit Cost" as c258,
	   T3."Gen_ Bus_ Posting Group" as c259,
	   T3."Inv_ Discount Amount" as c260,
	   T3."Shortcut Dimension 2 Code" as c261,
	   T3."Shortcut Dimension 1 Code" as c262,
	   T3."Amount Including VAT" as c263,
	   (convert(float, T3."Amount")) as c264,
	   (convert(float, T3."Line Discount Amount")) as c265,
	   T3."Unit Cost (LCY)" as c266,
	   (convert(float, T3."Unit Price")) as c267,
	   (convert(float, T3."Quantity")) as c268,
	   CASE WHEN (T3."Description" LIKE '%;%') THEN ((substring(pack(T3."Description") from 1 for POSITION(' ' IN pack(T3."Description") + ' ') - 1))) ELSE (T3."Description") END as c269,
	   T3."No_" as c270,
	   T3."Line No_" as c271,
	   T3."Document No_" as c272,
	   T1."Branch Code" as c273,
	   T1."Service Order Line No_" as c274,
	   T1."Customer Group Code" as c275,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END as c276,
	   T1."Order Type" as c277,
	   T1."User ID" as c278,
	   T1."Order No_ Series" as c279,
	   T1."No_ Series" as c280,
	   T1."Shipping Agent Code" as c281,
	   T1."Area" as c282,
	   T1."External Document No_" as c283,
	   T1."Document Date" as c284,
	   T1."Correction" as c285,
	   T1."Sell-to City" as c286,
	   T1."Sell-to Address" as c287,
	   T1."Sell-to Customer Name" as c288,
	   T1."Transaction Type" as c289,
	   T1."On Hold" as c290,
	   T1."Order No_" as c291,
	   T1."Salesperson Code" as c292,
	   T1."Allow Quantity Disc_" as c293,
	   T1."Price Group Code" as c294,
	   T1."Customer Posting Group" as c295,
	   T1."Shortcut Dimension 2 Code" as c296,
	   T1."Location Code" as c297,
	   T1."Payment Terms Code" as c298,
	   T1."Order Date" as c299,
	   T1."Bill-to City" as c300,
	   T1."Bill-to Address" as c301,
	   T1."Bill-to Name" as c302,
	   T1."Bill-to Customer No_" as c303,
	   T1."Sell-to Customer No_" as c304
from ((((("Vogl7x"."dbo"."BMW AH Vogl$Sales Invoice Header" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Vehicle" T2 on T1."Supply VIN" = T2."VIN") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T9 on T9."No_" = T1."Bill-to Customer No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer Group" T8 on T9."Customer Group Code" = T8."Code") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T12 on T12."No_" = T1."Sell-to Customer No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer Group" T13 on T12."Customer Group Code" = T13."Code"),
	(((((("Vogl7x"."dbo"."BMW AH Vogl$Sales Invoice Line" T3 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Archived Service Header" T4 on T3."Service Order No_" = T4."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Service Header" T5 on T3."Service Order No_" = T5."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T6 on T4."Service Advisor No_" = T6."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T7 on T5."Service Advisor No_" = T7."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Service Ledger Entry" T10 on ((T3."Document No_" = T10."Document No_") and (T3."Type" = 2)) and (T3."No_" = T10."No_")) left outer join "Vogl7x"."dbo"."BMW AH Vogl$Labor Ledger Entry" T11 on (((T3."Document No_" = T11."Document No_") and (T3."Labor No_" = T11."Labor No_")) and (T3."Service Order No_" = T11."Service Order No_")) and (T3."Service Order Line No_" = T11."Service Order Line No_"))
where (T1."No_" = T3."Document No_")
and (((((left(T1."No_",1)) IN ('I','W')) and (not T3."Type" IN (0,11,12))) and (T1."Posting Date" >= convert(datetime, '2020-01-01 00:00:00.000'))) and (not (CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) IN ('NASISPA')))
) D1
) D4
-- order by "No" asc,"Line No" asc