select T1."Client_DB" + '_' + (rtrim((((T1."WIPNumber"))))) as "Order_Number_eindeutig", T1."WIPNumber" as "Wipnumber_2", T1."DateCreated" as "Datecreated", T1."Registration" as "Registration", T1."AccountNo" as "Accountno", T1."SaleType" as "Saletype", T1."CustomerName" as "Customername", T1."MakeModel" as "Makemodel", T1."DiscountCategory" as "Discountcategory", T1."ChassisNumber" as "Chassisnumber", T1."Department" as "Department", T1."DateFirstReg" as "Datefirstreg", T1."FranchiseCode" as "Franchisecode_3", T1."DateDueIn" as "Dateduein", T1."TimeIn" as "Timein", T1."DateDueOut" as "Datedueout", T1."TimeOut" as "Timeout", T1."DeleteFlag" as "Deleteflag", T1."CustomerDiscType" as "Customerdisctype", T1."InvoiceNumber" as "Invoicenumber", T1."CompanyNumber" as "Companynumber", T1."DateUpdated" as "Dateupdated", T1."MagicMKVehicle" as "Magicmkvehicle", T1."JobNumber" as "Jobnumber", T1."MagicNumber001" as "Magicnumber001", T1."MagicNumber002" as "Magicnumber002", T1."MagicNumber003" as "Magicnumber003", T1."SellingDealer" as "Sellingdealer", T1."RetentionType" as "Retentiontype", T1."WIPLabour" as "Wiplabour", T1."WIPParts" as "Wipparts", T1."WLDateIn" as "Wldatein", T1."WLDateOut" as "Wldateout", T1."CustomerContact" as "Customercontact", T1."MagicMKTarget" as "Magicmktarget", T1."MagicMKCompany" as "Magicmkcompany", T1."DateDeleteFlagSet" as "Datedeleteflagset", T1."InvoicableValue" as "Invoicablevalue", T1."BookingStatus" as "Bookingstatus", T1."CreatingOperator" as "Creatingoperator_2", T1."BookedInOperator" as "Bookedinoperator", T1."InvoicingOperator" as "Invoicingoperator", T1."BookedOutOperator" as "Bookedoutoperator", T1."OwningOperator" as "Owningoperator", T1."Client_DB" as "Client Db_2", T2."RTSCode" as "Rtscode", T2."AllowedTime" as "Allowedtime", T2."TimeTaken" as "Timetaken", T2."RatePerHour" as "Rateperhour", T2."MechanicNumber" as "Mechanicnumber", T2."LabourTypeCode" as "Labourtypecode", T2."LabourAnalysisCode" as "Labouranalysiscode", T2."DateInserted" as "Dateinserted", T2."InvoiceStatus" as "Invoicestatus_Labourlines", T2."OriginalInvStat" as "Originalinvstat", T2."VATCode" as "Vatcode", T2."DiscountPercent" as "Discountpercent", T2."MenuPrice" as "Menuprice", T2."Unit" as "Unit", T2."MinsPerUnit" as "Minsperunit", T2."WIPNumber" as "Wipnumber", T2."LineNumber" as "Linenumber", T2."AccountCode" as "Accountcode", T2."DepartmentCode" as "Departmentcode", T2."InvoiceNumber" as "Invoicenumber_Labourlines", T2."DateEditted" as "Dateeditted", T2."FranchiseCode" as "Franchisecode_2", T2."SaleType" as "Saletype_Labourline", T2."PostedToWarranty" as "Postedtowarranty", T2."WarrantyCode" as "Warrantycode", T2."CostPerHour" as "Costperhour", T2."LoadType" as "Loadtype", T2."LoadStatus" as "Loadstatus", T2."EmployeeNumber" as "Employeenumber", T2."ContributionVal" as "Contributionval", T2."VehicleMagic" as "Vehiclemagic", T3."LabourRateCode" as "Labourratecode", T3."RateDescription002" as "Ratedescription002", T2."Description" as "Description_2", T2."WLTAKENS001" as "Wltakens001", T2."WLTAKENS002" as "Wltakens002", T2."WLTAKENS003" as "Wltakens003", T2."WLTAKENS004" as "Wltakens004", T2."WLTAKENS005" as "Wltakens005", T2."WLTAKENS006" as "Wltakens006", T2."DateCosted" as "Datecosted", T2."SOLoghdMagic001" as "Sologhdmagic001", T2."SOLoghdMagic002" as "Sologhdmagic002", T2."PreviousLoadStatus" as "Previousloadstatus", T2."BonusTime" as "Bonustime", T2."SpecialPrice" as "Specialprice", T2."JobCardNumber" as "Jobcardnumber", T2."CreatingOperator" as "Creatingoperator", T2."ValidLabourRates" as "Validlabourrates", T2."StandardTime" as "Standardtime", T2."ExtendedPrice" as "Extendedprice", T2."FirstDayBooked" as "Firstdaybooked", T2."LastDayBooked" as "Lastdaybooked", T2."ResourceClocked" as "Resourceclocked", T2."ActualLineNoSort" as "Actuallinenosort", T2."CompanyMagic" as "Companymagic", T2."TargetMagic" as "Targetmagic", T2."OriginalLine" as "Originalline", T2."Client_DB" as "Client Db", T4."UserID" as "Userid", T4."UserName" as "Username", T4."JobTitle" as "Jobtitle", T5."FranchiseCode" as "Franchisecode", T5."FullName001" as "Fullname001", T5."FullName002" as "Fullname002", T5."FullName003" as "Fullname003", T6."LocationCode" as "Locationcode", T6."Description" as "Description", T6."LocationName" as "Locationname", CASE WHEN (T1."Client_DB" BETWEEN '10' AND '19') THEN ('10') WHEN (T1."Client_DB" BETWEEN '20' AND '29') THEN ('20') WHEN (T1."Client_DB" BETWEEN '30' AND '39') THEN ('30') WHEN (T1."Client_DB" BETWEEN '40' AND '49') THEN ('40') WHEN (T1."Client_DB" BETWEEN '50' AND '59') THEN ('50') ELSE null END as "Hauptbetrieb_ID", '' as "Hauptbetrieb_Name", T6."LocationCode" as "Standort_ID", T6."LocationCode" + ' - ' + T6."Description" as "Standort_Name", 'Serviceberater' as "Zuordnung_Funktion", CASE WHEN (T4."UserName" IS NULL) THEN ('n.N.') ELSE (T4."UserName") END as "Serviceberater", CASE WHEN (T7."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T7."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T7."Franchise" = 'Z') THEN ('Fremd') WHEN (T7."Franchise" = 'S') THEN ('Smart') WHEN (T7."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END as "Fabrikat", (left((ucase(T7."Description")),4)) as "Model", T1."ChassisNumber" as "Fahrzeug", (CASE WHEN (T7."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T7."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T7."Franchise" = 'Z') THEN ('Fremd') WHEN (T7."Franchise" = 'S') THEN ('Smart') WHEN (T7."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END) as "Marke", 'Service' as "Auftragsart", (substring(T3."LabourRateCode", 2, 1)) as "2_Stelle_Labourratecode", CASE WHEN (((substring(T3."LabourRateCode", 2, 1))) = 'W') THEN ('GWL') WHEN (((substring(T3."LabourRateCode", 2, 1))) = 'I') THEN ('Intern') ELSE ('Extern') END as "Umsatzart_Labourratecode", T1."AccountNo" + ' - ' + T1."CustomerName" as "Kunde", T1."SaleType" as "Kundenart", '' as "Kostenstelle", T8."DocumentNumber" as "Documentnumber_Sltransactions", T8."DocumentDate" as "Documentdate_Sltransactions", CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END as "Invoice Date_ori", CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END as "Invoice Date", CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END))) <= 30) THEN ((T1."Client_DB" + '_' + (rtrim((((T1."WIPNumber"))))))) ELSE ('Aufträge älter 30 Tage') END as "Order_Desc_30", CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END))) <= 30) THEN ((rtrim((((T2."InvoiceNumber"))))) + ' - ' + (CASE WHEN (T4."UserName" IS NULL) THEN ('n.N.') ELSE (T4."UserName") END) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName") + ' - ' + T1."ChassisNumber") ELSE ('Rechnungen älter 30 Tage') END as "Invoice_Desc_30", CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END))) <= 100) THEN ((T1."Client_DB" + '_' + (rtrim((((T1."WIPNumber")))))) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName") + ' - ' + T1."ChassisNumber") ELSE ('Aufträge älter 100 Tage') END as "Order_Desc_100", CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END))) <= 100) THEN ((rtrim((((T2."InvoiceNumber"))))) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName") + ' - ' + T1."ChassisNumber") ELSE ('Rechnungen älter 100 Tage') END as "Invoice_Desc_100", CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T8."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T8."DocumentDate") END)) END))) <= 30) THEN ((rtrim((((T2."LineNumber"))))) + ' - ' + T3."LabourRateCode" + ' - ' + T2."Description") ELSE ('Auftragspositionen älter 30 Tage') END as "Auftragsposition", CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END as "Zuordnung_Produktbuchungsgruppe", T3."LabourRateCode" + ' - ' + T3."RateDescription002" as "Produktbuchungsgruppe", '' as "FZG-Altersstaffel", '' as "Repair_Group_Desc", 'VK > EK' as "DB1_><_EK", 'Rechnung' as "Rechnung_Gutschrift", '' as "Parts_Focus_Group", '' as "Parts_Make_Desc", '' as "Parts_Group_Desc", T9."Postcode" as "Postcode_Slsalesledgeraccount", (left(T9."Postcode",1)) as "PLZ_1_Stelle", (left(T9."Postcode",2)) as "PLZ_2_Stelle", (left(T9."Postcode",3)) as "PLZ_3_Stelle", (left(T9."Postcode",4)) as "PLZ_4_Stelle", T9."Postcode" as "PLZ", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('M - Lack','C - Karo.','A - Mech.')) THEN (T2."ExtendedPrice") ELSE (0) END as "Lohn", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('S - Fremdl.')) THEN (T2."ExtendedPrice") ELSE (0) END as "Fremdl.", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('Sonst.')) THEN (T2."ExtendedPrice") ELSE (0) END as "Sonst.", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('Y - Mietw.')) THEN (T2."ExtendedPrice") ELSE (0) END as "Mietw.", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('R - Rädereinl.')) THEN (T2."ExtendedPrice") ELSE (0) END as "Rädereinl.", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('M - Lack','C - Karo.','A - Mech.')) THEN (T2."AllowedTime") ELSE (0) END as "verk. Std.", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('M - Lack','C - Karo.','A - Mech.')) THEN (T2."TimeTaken") ELSE (0) END as "ben. Zeit", CASE WHEN ((CASE WHEN (T2."LabourAnalysisCode" IN ('A','AW','W')) THEN ('A - Mech.') WHEN (T2."LabourAnalysisCode" IN ('C')) THEN ('C - Karo./Lack') WHEN (T2."LabourAnalysisCode" IN ('R')) THEN ('R - Rädereinl.') WHEN (T2."LabourAnalysisCode" IN ('S')) THEN ('S - Fremdl.') WHEN (T2."LabourAnalysisCode" IN ('Y','L','F')) THEN ('Y - Mietw.') WHEN (T2."LabourAnalysisCode" IN ('P')) THEN ('P - MFK') ELSE ('Sonst.') END) IN ('M - Lack','C - Karo.','A - Mech.')) THEN (T2."AllowedTime" * T2."RatePerHour" - T2."ExtendedPrice") ELSE (0) END as "NL Lohn", CASE WHEN (T1."SaleType" IN ('W')) THEN ('GWL') WHEN (T1."SaleType" IN ('I')) THEN ('Intern') ELSE ('Extern') END as "Umsatzart", T1."DateCreated" as "Order Date", (T1."Client_DB" + '_' + (rtrim((((T1."WIPNumber")))))) + '/' + T1."BookingStatus" + ' - ' + (CASE WHEN (T4."UserName" IS NULL) THEN ('n.N.') ELSE (T4."UserName") END) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName") + ' - ' + T1."ChassisNumber" as "Order Number", (-1 * datediff(day, (getdate()), T1."DateCreated")) as "Tage offen", T7."Franchise" as "Franchise_MK_Vehiclerecords" from (((((((("AUTOLINE"."import"."SO_HeaderRecords" T1 left outer join "AUTOLINE"."import"."SO_LabourLines" T2 on (T1."WIPNumber" = T2."WIPNumber") and (T1."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."SO_LabourRates" T3 on ((T2."LabourTypeCode" = T3."LabourRateCode") and (T2."Client_DB" = T3."Client_DB")) and (T2."FranchiseCode" = T3."FranchiseCode")) left outer join "AUTOLINE"."import"."GB_UserDetails" T4 on ((convert(float, T1."BookedInOperator")) = T4."POSOperator") and ((T1."Department" = T4."CRMDepartment") or (T4."POSOperator" = 888))) left outer join "AUTOLINE"."import"."DD_FranchiseRecords" T5 on T1."FranchiseCode" = T5."FranchiseCode") left outer join "AUTOLINE"."import"."GB_CompanyLocations" T6 on T6."LocationCode" = T1."CompanyNumber") left outer join "AUTOLINE"."import"."MK_VehicleRecords" T7 on T7."VehicleNumber" = T1."MagicMKVehicle") left outer join "AUTOLINE"."import"."SL_SLTransactions" T8 on ((T8."DocumentNumber" = T2."InvoiceNumber") and (T8."TruePostingOrigin" = T2."Client_DB")) and (T8."AccountCode" = T2."AccountCode")) left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T9 on (T1."AccountNo" = T9."AccountCode") and (T9."Client_DB" = '10')) where ((((((T1."DateCreated" >= convert(datetime, '2020-01-01 00:00:00.000')) and (T1."InvoiceNumber" = 0)) and (not T2."InvoiceStatus" IN ('X'))) and (T2."LabourAnalysisCode" <> 'T')) and (not T2."Description" LIKE '%Selbstbehalt%')) and (not T1."BookingStatus" IN ('M'))) -- order by "Order_Number_eindeutig" asc,"Wipnumber_2" asc