C11/System/AUTOLINE/IQD/Serv_Teile/Auftraege_Teile_offen.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_2",
	   T1."SaleType" as "Saletype",
	   T1."CustomerName" as "Customername",
	   T1."MakeModel" as "Makemodel",
	   T1."DiscountCategory" as "Discountcategory_2",
	   T1."ChassisNumber" as "Chassisnumber",
	   T1."Department" as "Department",
	   T1."DateFirstReg" as "Datefirstreg",
	   T1."FranchiseCode" as "Franchisecode_2",
	   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",
	   T1."BookedInOperator" as "Bookedinoperator",
	   T1."InvoicingOperator" as "Invoicingoperator",
	   T1."BookedOutOperator" as "Bookedoutoperator",
	   T1."OwningOperator" as "Owningoperator",
	   T1."Client_DB" as "Client Db_2",
	   T2."UserID" as "Userid_2",
	   T2."UserName" as "Username",
	   T2."JobTitle" as "Jobtitle",
	   T3."FranchiseCode" as "Franchisecode",
	   T3."FullName001" as "Fullname001",
	   T3."FullName002" as "Fullname002",
	   T3."FullName003" as "Fullname003",
	   T4."LocationCode" as "Locationcode",
	   T4."Description" as "Description_2",
	   T4."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",
	   T4."LocationCode" as "Standort_ID",
	   T4."LocationCode" + ' - ' + T4."Description" as "Standort_Name",
	   'Serviceberater' as "Zuordnung_Funktion",
	   CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."UserName") END as "Serviceberater",
	   CASE WHEN (T5."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T5."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T5."Franchise" = 'Z') THEN ('Fremd') WHEN (T5."Franchise" = 'S') THEN ('Smart') WHEN (T5."Franchise" = 'F') THEN ('Fremd TRAPO') WHEN ((T5."Franchise" IS NULL) and (T1."FranchiseCode" = 'M')) THEN ('Mercedes PKW') ELSE null END as "Fabrikat",
	   (left((ucase(T5."Description")),4)) as "Model",
	   T1."ChassisNumber" as "Fahrzeug",
	   (CASE WHEN (T5."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T5."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T5."Franchise" = 'Z') THEN ('Fremd') WHEN (T5."Franchise" = 'S') THEN ('Smart') WHEN (T5."Franchise" = 'F') THEN ('Fremd TRAPO') WHEN ((T5."Franchise" IS NULL) and (T1."FranchiseCode" = 'M')) THEN ('Mercedes PKW') ELSE null END) as "Marke",
	   CASE WHEN (T1."Department" = 'P') THEN ('Teile') ELSE ('Service') END as "Auftragsart",
	   CASE WHEN (T1."SaleType" IN ('W')) THEN ('GWL') WHEN (T1."SaleType" IN ('I')) THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   T1."AccountNo" + ' - ' + T1."CustomerName" as "Kunde",
	   T1."SaleType" as "Kundenart",
	   '' as "Kostenstelle",
	   T6."DocumentNumber" as "Documentnumber_Sltransactions",
	   T6."DocumentDate" as "Documentdate_Sltransactions",
	   CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END as "Invoice Date_ori",
	   CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END as "Invoice Date",
	   CASE WHEN ((-1 * datediff(day, (getdate()), (CASE WHEN ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."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 (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 30) THEN ((rtrim((((T7."InvoiceNumber"))))) + ' - ' + (CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."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 (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."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 (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 100) THEN ((rtrim((((T7."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 (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END) IS NULL) THEN (T1."DateUpdated") ELSE ((CASE WHEN (T6."DocumentDate" IS NULL) THEN (T1."DateDueOut") ELSE (T6."DocumentDate") END)) END))) <= 30) THEN ((rtrim((((T7."LineNumber"))))) + ' - ' + T7."PartNumberPacked" + ' - ' + T7."Description") ELSE ('Auftragspositionen älter 30 Tage') END as "Auftragsposition",
	   ('Teile') as "Zuordnung_Produktbuchungsgruppe",
	   T7."AnalysisCode" as "Produktbuchungsgruppe",
	   '' as "FZG-Altersstaffel",
	   '' as "Repair_Group_Desc",
	   CASE WHEN ((CASE WHEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) > 0) THEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) - (CASE WHEN ((('Teile')) IN ('Teile')) THEN ((convert(float, T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END)) ELSE (0) END) >= 0) THEN ('VK > EK') ELSE ('VK < EK') END as "DB1_><_EK",
	   'Rechnung' as "Rechnung_Gutschrift",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc",
	   T8."Postcode" as "Postcode_Slsalesledgeraccount",
	   (left(T8."Postcode",1)) as "PLZ_1_Stelle",
	   (left(T8."Postcode",2)) as "PLZ_2_Stelle",
	   (left(T8."Postcode",3)) as "PLZ_3_Stelle",
	   (left(T8."Postcode",4)) as "PLZ_4_Stelle",
	   T8."Postcode" as "PLZ",
	   CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END as "Teile",
	   CASE WHEN ((('Teile')) IN ('Sonst.','W - Waschen','B - Kraftstoffe','D - Kleinmat.')) THEN (T7."ExtendedPrice") ELSE (0) END as "Sonst.",
	   (convert(float, T7."OrderQuantity" * T7."SellingPrice" - T7."ExtendedPrice")) as "NL Teile",
	   CASE WHEN ((('Teile')) IN ('Teile')) THEN ((convert(float, T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END as "EW Teile",
	   CASE WHEN ((('Teile')) IN ('Sonst.','W - Waschen','B - Kraftstoffe','D - Kleinmat.')) THEN ((convert(float, T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END as "EW Sonst.",
	   CASE WHEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) > 0) THEN ((CASE WHEN ((('Teile')) IN ('Teile')) THEN (T7."ExtendedPrice") ELSE (0) END) - (CASE WHEN ((('Teile')) IN ('Teile')) THEN ((convert(float, T7."OrderQuantity" * T7."CostPrice"))) ELSE (0) END)) ELSE (0) END as "DB 1 Teile",
	   T7."PartNumberPacked" as "Partnumberpacked",
	   T7."AccountNo" as "Accountno",
	   T7."OrderQuantity" as "Orderquantity",
	   T7."SupplyQuantity" as "Supplyquantity",
	   T7."ToFollowQuantity" as "Tofollowquantity",
	   T7."ProcessedFlag" as "Processedflag",
	   T7."OriginalDiscount" as "Originaldiscount",
	   T7."VATCode" as "Vatcode",
	   T7."OrderStatus" as "Orderstatus",
	   T7."RetailPrice" as "Retailprice",
	   T7."DiscountCategory" as "Discountcategory",
	   T7."BinLocation" as "Binlocation",
	   T7."AnalysisCode" as "Analysiscode",
	   T7."Surcharge" as "Surcharge",
	   T7."Description" as "Description",
	   T7."MenuPartFlag" as "Menupartflag",
	   T7."Salesman" as "Salesman",
	   T7."PriceListNo" as "Pricelistno",
	   T7."CostPrice" as "Costprice",
	   T7."SellingPrice" as "Sellingprice",
	   T7."DiscountPercent" as "Discountpercent",
	   T7."PartsDiscountCode" as "Partsdiscountcode",
	   T7."ReturnsCategory" as "Returnscategory",
	   T7."UnitIssue" as "Unitissue",
	   T7."DateInserted" as "Dateinserted",
	   T7."InvoiceStatus" as "Invoicestatus_Partslines",
	   T7."AdvicePONumber" as "Adviceponumber",
	   T7."ProductGroup" as "Productgroup",
	   T7."DateRequired" as "Daterequired",
	   T7."LineNumber" as "Linenumber",
	   T7."SaleType" as "Saletype_Partslines",
	   T7."WIPNumber" as "Wipnumber",
	   T7."PartNumber" as "Partnumber",
	   T7."SupplierAccount" as "Supplieraccount",
	   T7."InvoiceNumber" as "Invoicenumber_Partslines",
	   T7."DateLastEditted" as "Datelasteditted",
	   T7."PostedToWarranty" as "Postedtowarranty",
	   T7."WarrantyCode" as "Warrantycode",
	   T7."TransferLineNumber" as "Transferlinenumber",
	   T7."DepartmentCode" as "Departmentcode",
	   T7."VehicleMagic" as "Vehiclemagic",
	   T7."QuantityCredited" as "Quantitycredited",
	   T7."LastCreditNote" as "Lastcreditnote",
	   T7."SOLoghdMagic001" as "Sologhdmagic001",
	   T7."SOLoghdMagic002" as "Sologhdmagic002",
	   T7."TargetMagicNumber" as "Targetmagicnumber",
	   T7."CompanyMagicNumber" as "Companymagicnumber",
	   T7."WarrantyInformation" as "Warrantyinformation",
	   T7."ExtendedPrice" as "Extendedprice",
	   T7."ActualLineNoSort" as "Actuallinenosort",
	   T7."UserId" as "Userid",
	   T7."Client_DB" as "Client Db",
	   T1."DateCreated" as "Order Date",
	   (T1."Client_DB" + '_' + (rtrim((((T1."WIPNumber")))))) + '/' + T1."BookingStatus" + ' - ' + (CASE WHEN (T2."UserName" IS NULL) THEN ('n.N.') ELSE (T2."UserName") END) + ' - ' + (T1."AccountNo" + ' - ' + T1."CustomerName") + ' - ' + T1."ChassisNumber" as "Order Number",
	   (-1 * datediff(day, (getdate()), T1."DateCreated")) as "Tage offen",
	   T5."Franchise" as "Franchise_MK_Vehiclerecords"
from ((((("AUTOLINE"."import"."SO_HeaderRecords" T1 left outer join "AUTOLINE"."import"."GB_UserDetails" T2 on ((convert(float, T1."BookedInOperator")) = T2."POSOperator") and ((T1."Department" = T2."CRMDepartment") or (T2."POSOperator" = 888))) left outer join "AUTOLINE"."import"."DD_FranchiseRecords" T3 on T1."FranchiseCode" = T3."FranchiseCode") left outer join "AUTOLINE"."import"."GB_CompanyLocations" T4 on T4."LocationCode" = T1."CompanyNumber") left outer join "AUTOLINE"."import"."MK_VehicleRecords" T5 on T5."VehicleNumber" = T1."MagicMKVehicle") left outer join "AUTOLINE"."import"."SL_SalesLedgerAccounts" T8 on (T1."AccountNo" = T8."AccountCode") and (T8."Client_DB" = '10')),
	("AUTOLINE"."import"."SO_PartsLines" T7 left outer join "AUTOLINE"."import"."SL_SLTransactions" T6 on ((T6."DocumentNumber" = T7."InvoiceNumber") and (T6."TruePostingOrigin" = T7."Client_DB")) and (T6."AccountCode" = T7."AccountNo"))
where ((T1."WIPNumber" = T7."WIPNumber") and (T1."Client_DB" = T7."Client_DB"))
and ((((T1."DateCreated" >= convert(datetime, '2020-01-01 00:00:00.000')) and (T7."InvoiceStatus" <> 'X')) and (T1."InvoiceNumber" = 0)) and (not T1."BookingStatus" IN ('M')))
-- order by "Order_Number_eindeutig" asc,"Wipnumber_2" asc