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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\nw\NW_GW_VK_NW_final.imr
TITLE,NW_GW_VK_NW_final.imr
BEGIN SQL
select T1."StockbookNumber" as c1,
	   T1."Franchise" as c2,
	   T1."ModelCode" as c3,
	   T1."ACSStatus" as c4,
	   T1."VariantCode" as c5,
	   T1."VehicleDescriptio001" as c6,
	   T1."VehicleDescriptio002" as c7,
	   T1."VehicleDescriptio003" as c8,
	   T1."VehicleDescriptio004" as c9,
	   T1."PhysicalBranch" 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."CusAddressPostcode" 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."InvAddressPostcode" as c27,
	   T1."CustomerOrderNo" as c28,
	   T1."OrderDate" as c29,
	   T1."SOrderType" as c30,
	   T1."SalesExecutive001" as c31,
	   T1."InvoiceAccount" as c32,
	   T1."CustomerAccount" as c33,
	   T1."InvoiceTotal" as c34,
	   T1."LatestProfitValue" as c35,
	   T1."Cost" as c36,
	   T1."MainInvoiceTotal" as c37,
	   T1."ConsignmentTotal" as c38,
	   T1."OverallSaleNet" as c39,
	   T1."RecovOptionPrice015" as c40,
	   T1."SupplierName" as c41,
	   T1."Client_DB" as c42,
	   T2."VehicleType" as c43,
	   T2."VehicleNumber" as c44,
	   T2."InvoiceNumber" as c45,
	   T2."InvoiceSequence" as c46,
	   T2."InvoiceDate" as c47,
	   T2."InvoiceValue" as c48,
	   T3."FranchiseCode" as c49,
	   T3."FullName001" as c50,
	   T3."FullName002" as c51,
	   T3."FullName003" as c52,
	   T4."LocationCode" as c53,
	   T4."Description" as c54,
	   T4."LocationName" as c55,
	   T5."UserID" as c56,
	   T5."UserName" as c57,
	   T6."OrderType" as c58,
	   T6."Description001" as c59,
	   T6."Description002" as c60,
	   T6."Description003" as c61,
	   T1."Client_DB" as c62,
	   '' as c63,
	   T4."LocationCode" as c64,
	   T4."LocationCode" || ' - ' || T4."Description" as c65,
	   CASE WHEN (T5."UserName" IS NULL) THEN ('n.N.') ELSE (T5."UserName") END as c66,
	   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 c67,
	   T7."Description002" as c68,
	   T2."VehicleType" || (rtrim((cast_numberToString(cast_integer(T2."VehicleNumber"))))) || '/' || (od_right(T1."FullChassis",7)) || ' - ' || (CASE WHEN (T5."UserName" IS NULL) THEN ('n.N.') ELSE (T5."UserName") END) || ' - ' || T1."CustomerName" as c69,
	   T1."CustomerAccount" || ' - ' || T1."CustomerName" as c70,
	   T8."VehicleMagicNumber" as c71,
	   T8."VehicleType" as c72,
	   CASE WHEN (T2."VehicleType" = 'N') THEN ('Neuwagen') ELSE ('Gebrauchtwagen') END as c73,
	   CASE WHEN (((CASE WHEN (T2."VehicleType" = 'N') THEN ('Neuwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END as c74,
	   '' as c75,
	   '' as c76,
	   T6."OrderType" || ' - ' || T6."Description002" as c77,
	   T2."VehicleType" || (rtrim((cast_numberToString(cast_integer(T2."VehicleNumber"))))) || '/' || (od_right(T1."FullChassis",7)) || ' - ' || T4."LocationCode" || ' - ' || (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'N') THEN ('Neuwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T8."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END) || ' - ' || (CASE WHEN (T5."UserName" IS NULL) THEN ('n.N.') ELSE (T5."UserName") END) || ' - ' || T1."CustomerName" || ' - ' || (asciiz(extract(YEAR FROM T2."InvoiceDate"),4) || '-' || asciiz(extract(MONTH FROM T2."InvoiceDate"),2) || '-' || asciiz(extract(DAY FROM T2."InvoiceDate"),2)) as c78,
	   (od_left(T1."CusAddressPostcode",1)) as c79,
	   (od_left(T1."CusAddressPostcode",2)) as c80,
	   (od_left(T1."CusAddressPostcode",3)) as c81,
	   (od_left(T1."CusAddressPostcode",4)) as c82,
	   T1."CusAddressPostcode" as c83,
	   T1."SupplierName" as c84,
	   T1."VehicleDescriptio002" as c85,
	   '' as c86,
	   CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as c87,
	   1 as c88,
	   T1."OverallSaleNet" - T1."OtherCosts012" as c89,
	   (T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue" as c90,
	   T1."RecovOptionPrice015" as c91,
	   (T1."OverallSaleNet" - T1."OtherCosts012") - ((T1."OverallSaleNet" - T1."OtherCosts012") - T1."LatestProfitValue") as c92,
	   T2."InvoiceDate" as c93,
	   T1."ConsignmentDate" as c94,
	   (extract(DAY FROM T2."InvoiceDate" - T1."ConsignmentDate")) as c95,
	   T7."Description002" as c96,
	   T1."OtherCosts012" as c97,
	   T9."Gesch_Kosten_Stuetzung" as c98,
	   T9."Gesch_Kosten_Gesamt" as c99,
	   T9."Tatsaechl_Kosten_Stuetzung" as c100,
	   T9."Tatsaechl_Kosten_Gesamt" as c101,
	   (cdate(T1."BuildDate")) as c102,
	   (cdate(T1."EstimDeliveryDate")) as c103,
	   (absolute(T9."Tatsaechl_Kosten_Gesamt")) - (absolute(T9."Gesch_Kosten_Gesamt")) as c104,
	   T9."Tatsaechl_Kosten_Stuetzung" - T9."Gesch_Kosten_Stuetzung" as c105,
	   (cdate(T1."RegDate")) as c106,
	   (cdate(T1."DeliveryDate")) as c107,
	   T1."CommNumber" as c108,
	   CASE WHEN (T10."Summe offene Aufträge" IS NULL) THEN (0) ELSE (T10."Summe offene Aufträge") END as c109
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"."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')) left outer join QSS."C:\GlobalCube\System\AUTOLINE\IQD\NW\VS_Vehicle_Specification_Kosten.ims" T9 on ((T1."StockbookNumber" = T9."VehicleQuoteNumber") and (T1."Client_DB" = T9."Client_DB")) and (T9."VehicleTypeNU" = 'N')) left outer join QSS."C:\GlobalCube\System\AUTOLINE\IQD\Serv_Teile\Auftraege_Lohn_offen_fuer_FZG_Liste.ims" T10 on T1."FullChassis" = T10."ChassisNumber")
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 c1 asc

END SQL
COLUMN,0,Stockbooknumber
COLUMN,1,Franchise
COLUMN,2,Modelcode
COLUMN,3,Acsstatus
COLUMN,4,Variantcode
COLUMN,5,Vehicledescriptio001
COLUMN,6,Vehicledescriptio002
COLUMN,7,Vehicledescriptio003
COLUMN,8,Vehicledescriptio004
COLUMN,9,Physicalbranch
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,Cusaddresspostcode
COLUMN,20,Invoicename
COLUMN,21,Invoiceaddress001
COLUMN,22,Invoiceaddress002
COLUMN,23,Invoiceaddress003
COLUMN,24,Invoiceaddress004
COLUMN,25,Invoiceaddress005
COLUMN,26,Invaddresspostcode
COLUMN,27,Customerorderno
COLUMN,28,Orderdate
COLUMN,29,Sordertype
COLUMN,30,Salesexecutive001
COLUMN,31,Invoiceaccount
COLUMN,32,Customeraccount
COLUMN,33,Invoicetotal
COLUMN,34,Latestprofitvalue_DB1
COLUMN,35,Cost_EW
COLUMN,36,Maininvoicetotal
COLUMN,37,Consignmenttotal
COLUMN,38,Overallsalenet_Erlös_FZG
COLUMN,39,Recovoptionprice015_VK-Hilfe
COLUMN,40,Suppliername
COLUMN,41,Client Db
COLUMN,42,Vehicletype
COLUMN,43,Vehiclenumber
COLUMN,44,Invoicenumber
COLUMN,45,Invoicesequence
COLUMN,46,Invoicedate
COLUMN,47,Invoicevalue
COLUMN,48,Franchisecode
COLUMN,49,Fullname001
COLUMN,50,Fullname002
COLUMN,51,Fullname003
COLUMN,52,Locationcode
COLUMN,53,Description
COLUMN,54,Locationname
COLUMN,55,Userid
COLUMN,56,Username
COLUMN,57,Ordertype
COLUMN,58,Description001
COLUMN,59,Description002
COLUMN,60,Description003
COLUMN,61,Hauptbetrieb_ID
COLUMN,62,Hauptbetrieb_Name
COLUMN,63,Standort_ID
COLUMN,64,Standort_Name
COLUMN,65,Verkäufer
COLUMN,66,Fabrikat
COLUMN,67,Model
COLUMN,68,FZG
COLUMN,69,Kunde
COLUMN,70,Vehiclemagicnumber_MK_Demo_Veh
COLUMN,71,Vehicletype_MK_Demo_Veh
COLUMN,72,Fahrzeugart
COLUMN,73,Fahrzeugtyp
COLUMN,74,Konto
COLUMN,75,Fahrzeugtyp_1
COLUMN,76,Kundenart
COLUMN,77,FZG_Detail
COLUMN,78,PLZ_1
COLUMN,79,PLZ_2
COLUMN,80,PLZ_3
COLUMN,81,PLZ_4
COLUMN,82,PLZ Code_Deb
COLUMN,83,Name_Lieferant
COLUMN,84,Modell_Beschreibung
COLUMN,85,Name_Einkäufer
COLUMN,86,DB1 < 0
COLUMN,87,Menge
COLUMN,88,Erlös_FZG
COLUMN,89,Einsatz FZG
COLUMN,90,Boni
COLUMN,91,DB1_berechnet
COLUMN,92,Invoice Date
COLUMN,93,Consignmentdate
COLUMN,94,Standtage
COLUMN,95,Description002_Dd_Modelrecords
COLUMN,96,Othercosts012
COLUMN,97,Gesch Kosten Stuetzung
COLUMN,98,Gesch Kosten Gesamt
COLUMN,99,Tatsaechl Kosten Stuetzung
COLUMN,100,Tatsaechl Kosten Gesamt
COLUMN,101,Baudatum
COLUMN,102,Vorauss. Lieferung
COLUMN,103,Differenz
COLUMN,104,Differenz Stuetzung
COLUMN,105,Zul.-Datum
COLUMN,106,GMK Datum (tatsächl.)
COLUMN,107,WB Nummer
COLUMN,108,Summe Offene Aufträge