C11/System/AUTOLINE/IQD/Serv_Teile/Auftraege_Lohn.sql

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",
	   '' as "Userid",
	   T4."OperatorName" as "Username",
	   '' 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."OperatorName" IS NULL) THEN ('n.N.') ELSE (T4."OperatorName") 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') or (T3."LabourRateCode" = '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",
	   '010 - Hauptbetrieb' 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")))))) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName")) 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."OperatorName" IS NULL) THEN ('n.N.') ELSE (T4."OperatorName") END) + ' - ' + (T9."AccountCode" + ' - ' + T9."CustomerSName") + ' - ' + 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"))))) + ' - ' + (T9."AccountCode" + ' - ' + T9."CustomerSName") + ' - ' + 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",
	   T10."Postcode" as "Postcode_Slsalesledgeraccount",
	   (left(T10."Postcode",1)) as "PLZ_1_Stelle",
	   (left(T10."Postcode",2)) as "PLZ_2_Stelle",
	   (left(T10."Postcode",3)) as "PLZ_3_Stelle",
	   (left(T10."Postcode",4)) as "PLZ_4_Stelle",
	   T10."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.','P - MFK')) 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.','P - MFK')) THEN (T2."AllowedTime" * T2."RatePerHour" - T2."ExtendedPrice") ELSE (0) END as "NL Lohn",
	   CASE WHEN (T1."SaleType" IN ('W')) THEN ('Garantie') WHEN (T1."SaleType" IN ('I')) THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   T10."AccountCode" as "Accountcode_SL_Salesledger_Account",
	   T10."CustomerSName" as "Customersname_SL_Salesledger_Account",
	   T9."AccountCode" as "Accountcode_SalesLedger_Labour",
	   T9."CustomerSName" as "Customersname_SalesLedger_Labour",
	   T9."AccountCode" + ' - ' + T9."CustomerSName" as "Kunde_Labour_Line",
	   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"."SO_User_Parameter" T4 on ((convert(float, T1."BookedInOperator")) = T4."OperatorNumber") and (T1."Client_DB" = T4."Client_DB")) 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 (T9."AccountCode" = T2."AccountCode") and (T9."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T10 on (T1."AccountNo" = T10."AccountCode") and (T10."Client_DB" = '10'))
where (((((T1."DateCreated" >= convert(datetime, '2020-01-01 00:00:00.000')) and (T2."InvoiceNumber" <> 0)) and (T2."InvoiceStatus" IN ('X'))) and (T2."LabourAnalysisCode" <> 'T')) and (not T2."Description" LIKE '%Selbstbehalt%'))
-- order by "Order_Number_eindeutig" asc,"Wipnumber_2" asc