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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\nw\NW_GW_Bestand_NW.imr
TITLE,NW_GW_Bestand_NW.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,
	   T5."UserName" as c66,
	   T3."FullName002" as c67,
	   (od_left((ucase(T1."VehicleDescriptio002")),4)) as c68,
	   CASE WHEN (T1."FullChassis" IS NOT NULL) THEN ((rtrim((cast_numberToString(cast_integer(T1."StockbookNumber"))))) || '/' || T1."FullChassis" || ' - ' || (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) ELSE ((rtrim((cast_numberToString(cast_integer(T1."StockbookNumber"))))) || ' - ' || (CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') END)) END as c69,
	   T1."CustomerAccount" || ' - ' || T1."CustomerName" as c70,
	   T7."VehicleMagicNumber" as c71,
	   T7."VehicleType" as c72,
	   ('Neuwagen') as c73,
	   CASE WHEN (((('Neuwagen')) = 'Neuwagen') and (T7."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 (((('Neuwagen')) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW NW') ELSE ('NW') 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 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" as c89,
	   T1."OverallSaleNet" - T1."LatestProfitValue" as c90,
	   T1."RecovOptionPrice015" as c91,
	   T1."OverallSaleNet" - (T1."OverallSaleNet" - T1."LatestProfitValue") as c92,
	   (@CURRENT_DATE) as c93,
	   T1."ConsignmentDate" as c94,
	   T1."EstimDeliveryDate" as c95,
	   T1."EstimDateReceived" as c96,
	   (extract(DAY FROM (now()) - T1."ConsignmentDate")) as c97,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN (((extract(DAY FROM (now()) - T1."ConsignmentDate"))) > 360) THEN ('> 360 Tage') ELSE null END as c98,
	   T1."ColourWheelbaseDesc" as c99,
	   T1."ColourWheelbaseDesc" as c100,
	   T1."PhysicalBranch" as c101,
	   T1."SupplierName" as c102,
	   T1."SupplierName" as c103,
	   '' as c104,
	   '' as c105,
	   '' as c106,
	   '' as c107,
	   '' as c108,
	   '' as c109,
	   T1."Cost" as c110
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 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,Estimdeliverydate
COLUMN,95,Estimdatereceived
COLUMN,96,Standtage
COLUMN,97,Standtagestaffel
COLUMN,98,Colourwheelbasedesc
COLUMN,99,Farbe
COLUMN,100,Standort_1
COLUMN,101,Suppliername
COLUMN,102,Name_Lieferant
COLUMN,103,Lieferant/Einkäufer
COLUMN,104,Einkäufer
COLUMN,105,FZG_Einkäufer
COLUMN,106,EZ
COLUMN,107,Km-stand
COLUMN,108,Bestandsart
COLUMN,109,Einsatz