C11/System/ARI/IQD/Service/Service_Gutschriften_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",
	   "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",
	   "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",
	   "No Series" as "No Series",
	   "User Id" as "User Id",
	   "Order Type" as "Order Type",
	   "Service Order No_ohne_Einschränkung" as "Service Order No_ohne_Einschränkung",
	   "Branch Code" as "Branch Code",
	   "Vin" as "Vin",
	   "Model Code" as "Model Code",
	   "Model No" as "Model No",
	   "Model_ori" as "Model_ori",
	   "Document No" as "Document No",
	   "Line No" as "Line No",
	   "Type" as "Type",
	   "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",
	   "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 Std" as "verk Std",
	   "ben Zeit" as "ben Zeit",
	   "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",
	   "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",
	   "Model_Desc" as "Model_Desc",
	   "Fahrgestellnummer" as "Fahrgestellnummer",
	   "Fahrzeugalter_Tage" as "Fahrzeugalter_Tage",
	   "Customer_Group_Owner" as "Customer_Group_Owner",
	   "Customer_Name_Owner" as "Customer_Name_Owner",
	   "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_Focus_Group" as "Parts_Focus_Group",
	   "Parts_Make_Desc" as "Parts_Make_Desc",
	   "Parts_Group_Desc" as "Parts_Group_Desc",
	   "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",
	   "Order_Desc_100" as "Order_Desc_100",
	   "Invoice_Desc_100" as "Invoice_Desc_100",
	   "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",
	   "Service Order No" as "Service Order No"
from 
(select c165 as "No",
	   c285 as "Sell-to Customer No",
	   c284 as "Bill-to Customer No",
	   c283 as "Bill-to Name",
	   c282 as "Bill-to Address",
	   c281 as "Bill-to City",
	   c205 as "Posting Date",
	   c280 as "Payment Terms Code",
	   c279 as "Location Code",
	   c189 as "Department Code",
	   c278 as "Make Code",
	   c277 as "Customer Posting Group",
	   c276 as "Price Group Code",
	   c193 as "Prices Including Vat",
	   c275 as "Allow Quantity Disc",
	   c274 as "Salesperson Code",
	   c273 as "On Hold",
	   c183 as "Gen Bus Posting Group",
	   c272 as "Transaction Type",
	   c271 as "Sell-to Customer Name",
	   c270 as "Sell-to Address",
	   c269 as "Sell-to City",
	   c268 as "Correction",
	   c267 as "Document Date",
	   c266 as "External Document No",
	   c265 as "Area",
	   c264 as "No Series",
	   c263 as "User Id",
	   c262 as "Order Type",
	   c261 as "Service Order No_ohne_Einschränkung",
	   c260 as "Branch Code",
	   c185 as "Vin",
	   '' as "Model Code",
	   '' as "Model No",
	   c186 as "Model_ori",
	   c259 as "Document No",
	   c258 as "Line No",
	   c257 as "Type",
	   c256 as "No",
	   c255 as "Description",
	   '' as "Description 2",
	   c254 as "Quantity",
	   c253 as "Unit Price",
	   c252 as "Unit Cost (lcy)",
	   c251 as "Line Discount Amount",
	   c250 as "Amount",
	   c249 as "Amount Including Vat",
	   c248 as "Department Code",
	   c247 as "Make Code",
	   c246 as "Inv Discount Amount",
	   c245 as "Gen Bus Posting Group",
	   c169 as "Gen Prod Posting Group",
	   c244 as "Unit Cost",
	   c243 as "Order No",
	   c242 as "Order Line No",
	   c241 as "Order Type",
	   c240 as "Item Type",
	   c239 as "Vin",
	   c238 as "Vehicle Status",
	   c237 as "Registration Date",
	   c236 as "Mileage",
	   c235 as "Service Order No_",
	   c234 as "Service Order Line No",
	   c233 as "Labor No",
	   c232 as "Customer Group Code",
	   c231 as "Item Group Code",
	   c230 as "Service Advisor No_Archiv",
	   c229 as "Service Advisor No_oA",
	   c228 as "No_für_Archiv",
	   c227 as "First Name_für_Archiv",
	   c226 as "Last Name_für_Archiv",
	   c225 as "No",
	   c224 as "First Name",
	   c223 as "Last Name",
	   c222 as "Serviceberater",
	   '1' as "Hauptbetrieb",
	   c194 as "Standort",
	   c221 as "Umsatzart",
	   c220 as "Fabrikat",
	   c186 as "Model",
	   c219 as "Fahrzeug",
	   c218 as "Marke",
	   c217 as "Service Posting Group_für_Archiv",
	   c216 as "Service Posting Group",
	   c215 as "Auftragsart",
	   c214 as "Cust_Gr_Code",
	   c213 as "Cust_Gr_Description",
	   c212 as "Kundenart",
	   c211 as "Cust_No",
	   c182 as "Cust_Name",
	   c210 as "Kunde",
	   '' as "Auftragsart_1",
	   '' as "Function Code",
	   '' as "Monteur",
	   c209 as "Umsatz Lohn",
	   c177 as "Umsatz Teile Service_ori",
	   c168 as "Umsatz Sonstiges_ori",
	   c208 as "Document No_Service_ledger",
	   c207 as "No_Service_ledger",
	   c178 as "Total Cost_Service_ledger",
	   COUNT(c165) OVER (partition by c166) as "Anzahl Datensätze",
	   (c177) / (COUNT(c165) OVER (partition by c166)) as "Umsatz Teile Service",
	   (c168) / (COUNT(c165) OVER (partition by c166)) as "Umsatz Sonstiges",
	   CASE WHEN ((c178) IS NOT NULL) THEN (((c178)) / (COUNT(c165) OVER (partition by c166))) ELSE (0) END as "Einsatz Teile Service",
	   c206 as "verk Std",
	   0 as "ben Zeit",
	   c205 as "Invoice Date",
	   c204 as "Order Number",
	   c203 as "Order Number_Rg_Ausg",
	   -1 as "DG1",
	   c202 as "Order Number_Rg_Ausg_2",
	   c201 as "Order Number_Rg_Ausg_1",
	   c200 as "Cust_No_Verkaufskunde",
	   c199 as "Cust_Name_Verkaufskunde",
	   c198 as "Cust_Group_Description_Verkaufskunde",
	   c197 as "Kundenart_Verkaufskunde",
	   c196 as "Kunde_Verkaufskunde",
	   c195 as "Auftragsposition",
	   c192 as "Rabatt Lohn",
	   c190 as "NL Teile_ori",
	   (c190) / (COUNT(c165) OVER (partition by c166)) as "Rabatt Teile",
	   '1' as "Hauptbetrieb_ID",
	   c194 as "Standort_ID",
	   c191 as "NL Lohn %",
	   CASE WHEN ((((c191) > 90) and ((c192) <> 0)) and (c193 <> 1)) THEN ('Nachlass > 90 %') WHEN ((((CASE WHEN ((((c177) / (COUNT(c165) OVER (partition by c166))) + ((c190) / (COUNT(c165) OVER (partition by c166)))) <> 0) THEN (((c190) / (COUNT(c165) OVER (partition by c166))) / (((c177) / (COUNT(c165) OVER (partition by c166))) + ((c190) / (COUNT(c165) OVER (partition by c166)))) * 100) ELSE (0) END) > 90) and (((c190) / (COUNT(c165) OVER (partition by c166))) <> 0)) and (c193 <> 1)) THEN ('Nachlass > 90 %') ELSE ('Nachlass < 90 %') END as "Nachlass > 90 %",
	   CASE WHEN ((((c177) / (COUNT(c165) OVER (partition by c166))) + ((c190) / (COUNT(c165) OVER (partition by c166)))) <> 0) THEN (((c190) / (COUNT(c165) OVER (partition by c166))) / (((c177) / (COUNT(c165) OVER (partition by c166))) + ((c190) / (COUNT(c165) OVER (partition by c166)))) * 100) ELSE (0) END as "NL Teile %",
	   'Serviceberater' as "Zuordnung_Funktion",
	   c189 as "Cost_Centre_ID",
	   c188 as "Order_Desc_30",
	   c187 as "Invoice_Desc_30",
	   c186 as "Model_Desc",
	   c185 as "Fahrgestellnummer",
	   c184 as "Fahrzeugalter_Tage",
	   c183 as "Customer_Group_Owner",
	   c182 as "Customer_Name_Owner",
	   c181 as "Fahrzeugalter",
	   c180 as "FZG-Altersstaffel",
	   c179 as "Repair_Group_Desc",
	   CASE WHEN (((c177) / (COUNT(c165) OVER (partition by c166))) - (CASE WHEN ((c178) IS NOT NULL) THEN (((c178)) / (COUNT(c165) OVER (partition by c166))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as "DB1_><_EK",
	   'Gutschrift' as "Rechnung_Gutschrift",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc",
	   c172 as "Post Code",
	   c176 as "PLZ_1_Stelle",
	   c175 as "PLZ_2_Stelle",
	   c174 as "PLZ_3_Stelle",
	   c173 as "PLZ_4_Stelle",
	   c172 as "PLZ",
	   c171 as "Order_Desc_100",
	   c170 as "Invoice_Desc_100",
	   c169 as "Produktbuchungsgruppe",
	   CASE WHEN (c169 IN ('816_SONST','817_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END as "TÜV_Amount",
	   CASE WHEN (c169 IN ('821_SONST','823_SONST','828_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END as "FL_Lack_Amount",
	   CASE WHEN (c169 IN ('881_SONST','886_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END as "Mietw_Amount",
	   ((c168) / (COUNT(c165) OVER (partition by c166))) - (CASE WHEN (c169 IN ('816_SONST','817_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END) - (CASE WHEN (c169 IN ('821_SONST','823_SONST','828_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END) - (CASE WHEN (c169 IN ('881_SONST','886_SONST')) THEN (((c168) / (COUNT(c165) OVER (partition by c166)))) ELSE (0) END) as "Umsatz_Sonst_Rest",
	   c167 as "Service Order No"
from 
(select T1."No_" as c165,
	   (T3."Document No_" + (cast_numberToString(cast_integer(T3."Line No_")))) as c166,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END)) ELSE ('Aufträge älter 60 Tage') END as c167,
	   CASE WHEN ((T3."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T3."Gen_ Prod_ Posting Group" LIKE '%SONST%')) THEN (((convert(float, T3."Amount"))) * -1) ELSE (0) END as c168,
	   T3."Gen_ Prod_ Posting Group" as c169,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN (T1."No_" + ' - ' + T9."Name") ELSE ('Rechnungen älter 100 Tage') END as c170,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) + ' - ' + T9."Name") ELSE ('Aufträge älter 100 Tage') END as c171,
	   T9."Post Code" as c172,
	   (left(T9."Post Code",4)) as c173,
	   (left(T9."Post Code",3)) as c174,
	   (left(T9."Post Code",2)) as c175,
	   (left(T9."Post Code",1)) as c176,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T3."Amount"))) * -1) ELSE (0) END as c177,
	   (convert(float, T10."Total Cost")) as c178,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (T3."Gen_ Prod_ Posting Group") ELSE null END as c179,
	   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 c180,
	   (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 c181,
	   T9."Name" as c182,
	   T1."Gen_ Bus_ Posting Group" as c183,
	   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 c184,
	   T2."VIN" as c185,
	   T2."Model" as c186,
	   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 c187,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 30) THEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END)) ELSE ('Aufträge älter 30 Tage') END as c188,
	   T1."Shortcut Dimension 1 Code" as c189,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%') THEN (((convert(float, T3."Line Discount Amount"))) * -1) ELSE (0) END as c190,
	   CASE WHEN (((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount"))) * -1) ELSE (0) END) + (CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount"))) * -1) ELSE (0) END)) <> 0) THEN ((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount"))) * -1) ELSE (0) END) / ((CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount"))) * -1) ELSE (0) END) + (CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount"))) * -1) ELSE (0) END)) * 100) ELSE (0) END as c191,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Line Discount Amount"))) * -1) ELSE (0) END as c192,
	   T1."Prices Including VAT" as c193,
	   (CASE WHEN (T1."Location Code" = 'BUR') THEN ('10') WHEN (T1."Location Code" = 'MUE') THEN ('20') ELSE null END) as c194,
	   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_"))))) + ' - ' + T1."No_" + ' - ' + T3."Description") ELSE ('Rechnungen älter 30 Tage') END as c195,
	   T11."No_" + ' - ' + T11."Name" as c196,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T12."Description") END as c197,
	   T12."Description" as c198,
	   T11."Name" as c199,
	   T11."No_" as c200,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN (T3."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T3."Service Order No_") END)) ELSE null END as c201,
	   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 (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_" + ' - ' + T9."Name") ELSE null END as c202,
	   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 c203,
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 180) THEN (T1."No_" + ' - ' + (CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) + ' - ' + T9."Name") ELSE ('Aufträge älter 180 Tage') END as c204,
	   T1."Posting Date" as c205,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Quantity"))) * -1) ELSE (0) END as c206,
	   T10."No_" as c207,
	   T10."Document No_" as c208,
	   CASE WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%') THEN (((convert(float, T3."Amount"))) * -1) ELSE (0) END as c209,
	   T9."No_" + ' - ' + T9."Name" as c210,
	   T9."No_" as c211,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T8."Description") END as c212,
	   T8."Description" as c213,
	   T8."Code" as c214,
	   CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END as c215,
	   T5."Service Posting Group" as c216,
	   T4."Service Posting Group" as c217,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW I','BMWI','BMW','BMW-MINI')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as c218,
	   T2."VIN" + ' - ' + T2."Model" as c219,
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW I','BMWI','BMW','BMW-MINI')) THEN (T1."Shortcut Dimension 2 Code") WHEN ((CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END) = 'Gutschrift ohne Auftrag') THEN ('GS ohne Auftrag') ELSE ('Fremdfabrikat') END as c220,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') WHEN (T1."No_ Series" LIKE 'I%') THEN ('Intern') ELSE ('Extern') END as c221,
	   CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END as c222,
	   T7."Last Name" as c223,
	   T7."First Name" as c224,
	   T7."No_" as c225,
	   T6."Last Name" as c226,
	   T6."First Name" as c227,
	   T6."No_" as c228,
	   T5."Service Advisor No_" as c229,
	   T4."Service Advisor No_" as c230,
	   T3."Item Group Code" as c231,
	   T3."Customer Group Code" as c232,
	   T3."Labor No_" as c233,
	   T3."Service Order Line No_" as c234,
	   CASE WHEN (T3."Service Order No_" IS NULL) THEN ('Gutschrift ohne Auftrag') ELSE (T3."Service Order No_") END as c235,
	   T3."Mileage" as c236,
	   T3."Registration Date" as c237,
	   T3."Vehicle Status" as c238,
	   T3."VIN" as c239,
	   T3."Item Type" as c240,
	   T3."Order Type" as c241,
	   T3."Order Line No_" as c242,
	   T3."Order No_" as c243,
	   T3."Unit Cost" as c244,
	   T3."Gen_ Bus_ Posting Group" as c245,
	   T3."Inv_ Discount Amount" as c246,
	   T3."Shortcut Dimension 2 Code" as c247,
	   T3."Shortcut Dimension 1 Code" as c248,
	   T3."Amount Including VAT" as c249,
	   (convert(float, T3."Amount")) as c250,
	   (convert(float, T3."Line Discount Amount")) as c251,
	   T3."Unit Cost (LCY)" as c252,
	   T3."Unit Price" as c253,
	   (convert(float, T3."Quantity")) as c254,
	   T3."Description" as c255,
	   T3."No_" as c256,
	   T3."Type" as c257,
	   T3."Line No_" as c258,
	   T3."Document No_" as c259,
	   T1."Branch Code" as c260,
	   CASE WHEN (T1."Service Order No_" = ' ') THEN ('Gutschrift ohne Auftrag') ELSE (T1."Service Order No_") END as c261,
	   T1."Order Type" as c262,
	   T1."User ID" as c263,
	   T1."No_ Series" as c264,
	   T1."Area" as c265,
	   T1."External Document No_" as c266,
	   T1."Document Date" as c267,
	   T1."Correction" as c268,
	   T1."Sell-to City" as c269,
	   T1."Sell-to Address" as c270,
	   T1."Sell-to Customer Name" as c271,
	   T1."Transaction Type" as c272,
	   T1."On Hold" as c273,
	   T1."Salesperson Code" as c274,
	   T1."Allow Quantity Disc_" as c275,
	   T1."Price Group Code" as c276,
	   T1."Customer Posting Group" as c277,
	   T1."Shortcut Dimension 2 Code" as c278,
	   T1."Location Code" as c279,
	   T1."Payment Terms Code" as c280,
	   T1."Bill-to City" as c281,
	   T1."Bill-to Address" as c282,
	   T1."Bill-to Name" as c283,
	   T1."Bill-to Customer No_" as c284,
	   T1."Sell-to Customer No_" as c285
from ((((("Vogl7x"."dbo"."BMW AH Vogl$Sales Credit Memo 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 T1."Bill-to Customer No_" = T9."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" T11 on T11."No_" = T1."Sell-to Customer No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer Group" T12 on T11."Customer Group Code" = T12."Code"),
	((((("Vogl7x"."dbo"."BMW AH Vogl$Sales Credit Memo Line" T3 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Archived Service Header" T4 on T4."No_" = T3."Service Order No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Service Header" T5 on T5."No_" = T3."Service Order 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_"))
where (T1."No_" = T3."Document No_")
and ((((T1."No_" LIKE 'W%') or (T1."No_" LIKE '%I%')) and (not T3."Type" IN (0,11,12))) and (T1."Posting Date" >= convert(datetime, '2020-01-01 00:00:00.000')))
) D1
) D4
-- order by "No" asc