C11/System/AUTOLINE/IQD/NW/NW_GW_VK_GW_final.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\nw\NW_GW_VK_GW_final.imr
TITLE,NW_GW_VK_GW_final.imr
BEGIN SQL
select T1."StockbookNumber" as c1,
	   T1."Franchise" as c2,
	   T1."ModelCode" as c3,
	   T1."ModelVariant" as c4,
	   T1."VehicleDescriptio001" as c5,
	   T1."VehicleDescriptio002" as c6,
	   T1."VehicleDescriptio003" as c7,
	   T1."VehicleDescriptio004" as c8,
	   T1."PhysicalBranch" as c9,
	   T1."ACSStatus" as c10,
	   T1."FullChassis" as c11,
	   T1."RegistrationNumber" as c12,
	   T1."RegDate" as c13,
	   T1."CustomerName" as c14,
	   T1."CustomerAddress001" as c15,
	   T1."CustomerAddress002" as c16,
	   T1."CustomerAddress003" as c17,
	   T1."CustomerAddress004" as c18,
	   T1."CustomerAddress005" as c19,
	   T1."CustomerPostcode" as c20,
	   T1."InvoiceName" as c21,
	   T1."InvoiceAddress001" as c22,
	   T1."InvoiceAddress002" as c23,
	   T1."InvoiceAddress003" as c24,
	   T1."InvoiceAddress004" as c25,
	   T1."InvoiceAddress005" as c26,
	   T1."InvAddrPostcode" as c27,
	   T1."CustomerOrderNo" as c28,
	   T1."OrderDate" as c29,
	   T1."SOrderType" as c30,
	   T1."SalesExecutive001" as c31,
	   T1."DeliveryDate" as c32,
	   T1."SalesInvoiceNumber" as c33,
	   T1."InvoiceAccount" as c34,
	   T1."CustomerAccount" as c35,
	   T1."InvoiceTotal" as c36,
	   T1."LatestProfitValue" as c37,
	   T1."Cost" as c38,
	   T1."MainInvoiceTotal" as c39,
	   T1."OverallSaleNet" as c40,
	   T2."VehicleType" as c41,
	   T2."VehicleNumber" as c42,
	   T2."InvoiceNumber" as c43,
	   T2."InvoiceSequence" as c44,
	   T2."InvoiceDate" as c45,
	   T2."InvoiceValue" as c46,
	   T3."FranchiseCode" as c47,
	   T3."FullName001" as c48,
	   T3."FullName002" as c49,
	   T3."FullName003" as c50,
	   T4."LocationCode" as c51,
	   T4."Description" as c52,
	   T4."LocationName" as c53,
	   T5."UserID" as c54,
	   T5."UserName" as c55,
	   T6."OrderType" as c56,
	   T6."Description001" as c57,
	   T6."Description002" as c58,
	   T6."Description003" as c59,
	   T1."Client_DB" as c60,
	   '' as c61,
	   T4."LocationCode" as c62,
	   T4."LocationCode" || ' - ' || T4."Description" as c63,
	   T5."UserName" as c64,
	   CASE WHEN (T1."Franchise" = 'M') THEN ('Mercedes PKW') WHEN (T1."Franchise" = 'T') THEN ('Mercedes TRAPO') WHEN (T1."Franchise" = 'Z') THEN ('Fremd') WHEN (T1."Franchise" = 'S') THEN ('Smart') WHEN (T1."Franchise" = 'F') THEN ('Fremd TRAPO') ELSE null END as c65,
	   T7."Description002" as c66,
	   T2."VehicleType" || (rtrim((cast_numberToString(cast_integer(T2."VehicleNumber"))))) || '/' || (od_right(T1."FullChassis",7)) || ' - ' || T5."UserName" || ' - ' || T1."CustomerName" as c67,
	   T1."CustomerAccount" || ' - ' || T1."CustomerName" as c68,
	   T8."VehicleMagicNumber" as c69,
	   T8."VehicleType" as c70,
	   CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END as c71,
	   CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END as c72,
	   '' as c73,
	   '' as c74,
	   T6."OrderType" || ' - ' || T6."Description002" as c75,
	   T2."VehicleType" || (rtrim((cast_numberToString(cast_integer(T2."VehicleNumber"))))) || '/' || (od_right(T1."FullChassis",7)) || ' - ' || T4."LocationCode" || ' - ' || (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END) || ' - ' || T5."UserName" || ' - ' || T1."CustomerName" || ' - ' || (asciiz(extract(YEAR FROM T2."InvoiceDate"),4) || '-' || asciiz(extract(MONTH FROM T2."InvoiceDate"),2) || '-' || asciiz(extract(DAY FROM T2."InvoiceDate"),2)) as c76,
	   (od_left(T1."CustomerPostcode",1)) as c77,
	   (od_left(T1."CustomerPostcode",2)) as c78,
	   (od_left(T1."CustomerPostcode",3)) as c79,
	   (od_left(T1."CustomerPostcode",4)) as c80,
	   T1."CustomerPostcode" as c81,
	   T1."SupplierName" as c82,
	   T1."VehicleDescriptio002" as c83,
	   T1."BoughtBy" as c84,
	   CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as c85,
	   1 as c86,
	   T1."OverallSaleNet" - T1."OtherCosts012" as c87,
	   (T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue" as c88,
	   0 as c89,
	   (T1."OverallSaleNet" - T1."OtherCosts012") - ((T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue") as c90,
	   T2."InvoiceDate" as c91,
	   T1."InvoiceDate" as c92,
	   (extract(DAY FROM T2."InvoiceDate" - T1."InvoiceDate")) as c93,
	   T7."Description002" as c94,
	   T1."OtherCosts012" as c95
from ((((((("AUTOLINE"."import"."VS_UsedVehicleStock" 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"."DD_ModelRecords" T7 on (T7."ShortFranchise" = T1."Franchise") and (T7."ModelCode" = T1."ModelCode")) left outer join "AUTOLINE"."import"."MK_DemonstrationVehicles" T8 on (T1."CRMVehicleMagic" = T8."VehicleMagicNumber") and (T8."Client_DB" <> '00'))
where ((((((T1."SalesInvoiceNumber" IS NOT NULL) and (T1."SalesInvoiceNumber" <> 0)) and (T1."Client_DB" <> '00')) and (T1."InvoiceTotal" <> 0)) and (T2."InvoiceDate" >= TIMESTAMP '2018-01-01 00:00:00.000')) and (T1."Client_DB" = '10'))
order by c42 asc

END SQL
COLUMN,0,Stockbooknumber
COLUMN,1,Franchise
COLUMN,2,Modelcode
COLUMN,3,Modelvariant
COLUMN,4,Vehicledescriptio001
COLUMN,5,Vehicledescriptio002
COLUMN,6,Vehicledescriptio003
COLUMN,7,Vehicledescriptio004
COLUMN,8,Physicalbranch
COLUMN,9,Acsstatus
COLUMN,10,Fullchassis
COLUMN,11,Registrationnumber
COLUMN,12,Regdate
COLUMN,13,Customername
COLUMN,14,Customeraddress001
COLUMN,15,Customeraddress002
COLUMN,16,Customeraddress003
COLUMN,17,Customeraddress004
COLUMN,18,Customeraddress005
COLUMN,19,Customerpostcode
COLUMN,20,Invoicename
COLUMN,21,Invoiceaddress001
COLUMN,22,Invoiceaddress002
COLUMN,23,Invoiceaddress003
COLUMN,24,Invoiceaddress004
COLUMN,25,Invoiceaddress005
COLUMN,26,Invaddrpostcode
COLUMN,27,Customerorderno
COLUMN,28,Orderdate
COLUMN,29,Sordertype
COLUMN,30,Salesexecutive001
COLUMN,31,Deliverydate
COLUMN,32,Salesinvoicenumber
COLUMN,33,Invoiceaccount
COLUMN,34,Customeraccount
COLUMN,35,Invoicetotal
COLUMN,36,Latestprofitvalue_DB1
COLUMN,37,Cost_EW
COLUMN,38,Maininvoicetotal
COLUMN,39,Overallsalenet_Erlös_FZG
COLUMN,40,Vehicletype
COLUMN,41,Vehiclenumber
COLUMN,42,Invoicenumber
COLUMN,43,Invoicesequence
COLUMN,44,Invoicedate
COLUMN,45,Invoicevalue
COLUMN,46,Franchisecode
COLUMN,47,Fullname001
COLUMN,48,Fullname002
COLUMN,49,Fullname003
COLUMN,50,Locationcode
COLUMN,51,Description
COLUMN,52,Locationname
COLUMN,53,Userid
COLUMN,54,Username
COLUMN,55,Ordertype
COLUMN,56,Description001
COLUMN,57,Description002
COLUMN,58,Description003
COLUMN,59,Hauptbetrieb_ID
COLUMN,60,Hauptbetrieb_Name
COLUMN,61,Standort_ID
COLUMN,62,Standort_Name
COLUMN,63,Verkäufer
COLUMN,64,Fabrikat
COLUMN,65,Model
COLUMN,66,FZG
COLUMN,67,Kunde
COLUMN,68,Vehiclemagicnumber_MK_Demo_Veh
COLUMN,69,Vehicletype_MK_Demo_Veh
COLUMN,70,Fahrzeugart
COLUMN,71,Fahrzeugtyp
COLUMN,72,Konto
COLUMN,73,Fahrzeugtyp_1
COLUMN,74,Kundenart
COLUMN,75,FZG_Detail
COLUMN,76,PLZ_1
COLUMN,77,PLZ_2
COLUMN,78,PLZ_3
COLUMN,79,PLZ_4
COLUMN,80,PLZ Code_Deb
COLUMN,81,Name_Lieferant
COLUMN,82,Modell_Beschreibung
COLUMN,83,Name_Einkäufer
COLUMN,84,DB1 < 0
COLUMN,85,Menge
COLUMN,86,Erlös_FZG
COLUMN,87,Einsatz FZG
COLUMN,88,Boni
COLUMN,89,DB1_berechnet
COLUMN,90,Invoice Date
COLUMN,91,Invoicedate_VS_UsedVehicle
COLUMN,92,Standtage
COLUMN,93,Description002_Dd_Modelrecords
COLUMN,94,Othercosts012