C11/System/AUTOLINE/IQD/NW/NW_GW_Bestand_NW.sql

select T1."StockbookNumber" as "Stockbooknumber",
	   T1."Franchise" as "Franchise",
	   T1."ModelCode" as "Modelcode",
	   T1."ACSStatus" as "Acsstatus",
	   T1."VariantCode" as "Variantcode",
	   T1."VehicleDescriptio001" as "Vehicledescriptio001",
	   T1."VehicleDescriptio002" as "Vehicledescriptio002",
	   T1."VehicleDescriptio003" as "Vehicledescriptio003",
	   T1."VehicleDescriptio004" as "Vehicledescriptio004",
	   T1."PhysicalBranch" as "Physicalbranch",
	   T1."FullChassis" as "Fullchassis",
	   T1."RegistrationNumber" as "Registrationnumber",
	   T1."RegDate" as "Regdate",
	   T1."CustomerName" as "Customername",
	   T1."CustomerAddress001" as "Customeraddress001",
	   T1."CustomerAddress002" as "Customeraddress002",
	   T1."CustomerAddress003" as "Customeraddress003",
	   T1."CustomerAddress004" as "Customeraddress004",
	   T1."CustomerAddress005" as "Customeraddress005",
	   T1."CusAddressPostcode" as "Cusaddresspostcode",
	   T1."InvoiceName" as "Invoicename",
	   T1."InvoiceAddress001" as "Invoiceaddress001",
	   T1."InvoiceAddress002" as "Invoiceaddress002",
	   T1."InvoiceAddress003" as "Invoiceaddress003",
	   T1."InvoiceAddress004" as "Invoiceaddress004",
	   T1."InvoiceAddress005" as "Invoiceaddress005",
	   T1."InvAddressPostcode" as "Invaddresspostcode",
	   T1."CustomerOrderNo" as "Customerorderno",
	   T1."OrderDate" as "Orderdate",
	   T1."SOrderType" as "Sordertype",
	   T1."SalesExecutive001" as "Salesexecutive001",
	   T1."InvoiceAccount" as "Invoiceaccount",
	   T1."CustomerAccount" as "Customeraccount",
	   T1."InvoiceTotal" as "Invoicetotal",
	   T1."LatestProfitValue" as "Latestprofitvalue_DB1",
	   T1."Cost" as "Cost_EW",
	   T1."MainInvoiceTotal" as "Maininvoicetotal",
	   T1."ConsignmentTotal" as "Consignmenttotal",
	   T1."OverallSaleNet" as "Overallsalenet_Erlös_FZG",
	   T1."RecovOptionPrice015" as "Recovoptionprice015_VK-Hilfe",
	   T1."SupplierName" as "Suppliername_2",
	   T1."Client_DB" as "Client Db",
	   T2."VehicleType" as "Vehicletype",
	   T2."VehicleNumber" as "Vehiclenumber",
	   T2."InvoiceNumber" as "Invoicenumber",
	   T2."InvoiceSequence" as "Invoicesequence",
	   T2."InvoiceDate" as "Invoicedate",
	   T2."InvoiceValue" as "Invoicevalue",
	   T3."FranchiseCode" as "Franchisecode",
	   T3."FullName001" as "Fullname001",
	   T3."FullName002" as "Fullname002",
	   T3."FullName003" as "Fullname003",
	   T4."LocationCode" as "Locationcode",
	   T4."Description" as "Description",
	   T4."LocationName" as "Locationname",
	   T5."UserID" as "Userid",
	   T5."UserName" as "Username",
	   T6."OrderType" as "Ordertype",
	   T6."Description001" as "Description001",
	   T6."Description002" as "Description002",
	   T6."Description003" as "Description003",
	   T1."Client_DB" as "Hauptbetrieb_ID",
	   '' as "Hauptbetrieb_Name",
	   T4."LocationCode" as "Standort_ID",
	   T4."LocationCode" + ' - ' + T4."Description" as "Standort_Name",
	   T5."UserName" as "Verkäufer",
	   T3."FullName002" as "Fabrikat",
	   (left((ucase(T1."VehicleDescriptio002")),4)) as "Model",
	   CASE WHEN (T1."FullChassis" IS NOT NULL) THEN ((rtrim((((T1."StockbookNumber"))))) + '/' + T1."FullChassis" + ' - ' + (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) ELSE ((rtrim((((T1."StockbookNumber"))))) + ' - ' + (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) END as "FZG",
	   T1."CustomerAccount" + ' - ' + T1."CustomerName" as "Kunde",
	   T7."VehicleMagicNumber" as "Vehiclemagicnumber_MK_Demo_Veh",
	   T7."VehicleType" as "Vehicletype_MK_Demo_Veh",
	   ('Neuwagen') as "Fahrzeugart",
	   CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END as "Fahrzeugtyp",
	   '' as "Konto",
	   '' as "Fahrzeugtyp_1",
	   T6."OrderType" + ' - ' + T6."Description002" as "Kundenart",
	   T2."VehicleType" + (rtrim((((T2."VehicleNumber"))))) + '/' + (right(T1."FullChassis",7)) + ' - ' + T4."LocationCode" + ' - ' + (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END) + ' - ' + T5."UserName" + ' - ' + T1."CustomerName" + ' - ' + (convert(varchar(50), year(T2."InvoiceDate")) + '-' + convert(varchar(50), month(T2."InvoiceDate")) + '-' + convert(varchar(50), day(T2."InvoiceDate"))) as "FZG_Detail",
	   (left(T1."CusAddressPostcode",1)) as "PLZ_1",
	   (left(T1."CusAddressPostcode",2)) as "PLZ_2",
	   (left(T1."CusAddressPostcode",3)) as "PLZ_3",
	   (left(T1."CusAddressPostcode",4)) as "PLZ_4",
	   T1."CusAddressPostcode" as "PLZ Code_Deb",
	   T1."SupplierName" as "Name_Lieferant_2",
	   T1."VehicleDescriptio002" as "Modell_Beschreibung",
	   '' as "Name_Einkäufer",
	   CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as "DB1 < 0",
	   1 as "Menge",
	   T1."OverallSaleNet" as "Erlös_FZG",
	   T1."OverallSaleNet" - T1."LatestProfitValue" as "Einsatz FZG",
	   T1."RecovOptionPrice015" as "Boni",
	   T1."OverallSaleNet" - (T1."OverallSaleNet" - T1."LatestProfitValue") as "DB1_berechnet",
	   (getdate()) as "Invoice Date",
	   T1."ConsignmentDate" as "Consignmentdate",
	   T1."EstimDeliveryDate" as "Estimdeliverydate",
	   T1."EstimDateReceived" as "Estimdatereceived",
	   (-1 * datediff(day, (getdate()), T1."ConsignmentDate")) as "Standtage",
	   CASE WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN (((-1 * datediff(day, (getdate()), T1."ConsignmentDate"))) > 360) THEN ('> 360 Tage') ELSE null END as "Standtagestaffel",
	   T1."ColourWheelbaseDesc" as "Colourwheelbasedesc",
	   T1."ColourWheelbaseDesc" as "Farbe",
	   T1."PhysicalBranch" as "Standort_1",
	   T1."SupplierName" as "Suppliername",
	   T1."SupplierName" as "Name_Lieferant",
	   '' as "Lieferant/Einkäufer",
	   '' as "Einkäufer",
	   '' as "FZG_Einkäufer",
	   '' as "EZ",
	   '' as "Km-stand",
	   '' as "Bestandsart",
	   T1."Cost" as "Einsatz"
from (((((("AUTOLINE"."import"."VS_NewVehicleStock" T1 left outer join "AUTOLINE"."import"."VS_InvoiceStockFile" T2 on (T1."SalesInvoiceNumber" = T2."InvoiceNumber") and (T1."Client_DB" = T2."Client_DB")) left outer join "AUTOLINE"."import"."DD_FranchiseRecords" T3 on T1."Franchise" = T3."FranchiseCode") left outer join "AUTOLINE"."import"."GB_CompanyLocations" T4 on T1."SellingBranch" = T4."LocationCode") left outer join "AUTOLINE"."import"."GB_UserDetails" T5 on T1."SalesExecutive001" = T5."UserID") left outer join "AUTOLINE"."import"."VS_SalesOrderTypes" T6 on (T1."SOrderType" = T6."OrderType") and (T1."Client_DB" = T6."Client_DB")) left outer join "AUTOLINE"."import"."MK_DemonstrationVehicles" T7 on (T1."CRMVehicleMagic" = T7."VehicleMagicNumber") and (T7."Client_DB" <> '00'))
where (((((T1."SalesInvoiceNumber" IS NULL) or (T1."SalesInvoiceNumber" = 0)) or (T1."InvoiceTotal" = 0)) and (T1."Client_DB" <> '00')) and (T1."ConsignmentDate" IS NOT NULL))
-- order by "Stockbooknumber" asc