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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_AUTOLINE
DATASOURCENAME,C:\GlobalCube\System\Autoline\IQD\nw\NW_GW_Bestand_GW.imr
TITLE,NW_GW_Bestand_GW.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,
	   T3."FullName002" as c65,
	   (od_left((ucase(T1."VehicleDescriptio002")),4)) as c66,
	   CASE WHEN (T1."FullChassis" IS NOT NULL) THEN ((rtrim((cast_numberToString(cast_integer(T1."StockbookNumber"))))) || '/' || T1."FullChassis" || ' - ' || (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END)) ELSE ((rtrim((cast_numberToString(cast_integer(T1."StockbookNumber"))))) || ' - ' || (CASE WHEN (((CASE WHEN (T2."VehicleType" = 'U') THEN ('Gebrauchtwagen') ELSE ('Gebrauchtwagen') END) = 'Neuwagen') and (T7."VehicleMagicNumber" IS NOT NULL)) THEN ('VFW GW') ELSE ('GW') END)) END as c67,
	   T1."CustomerAccount" || ' - ' || T1."CustomerName" as c68,
	   T7."VehicleMagicNumber" as c69,
	   T7."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 (T7."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 (T7."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,
	   '' as c84,
	   CASE WHEN (T1."LatestProfitValue" < 0) THEN ('DB1 < 0') ELSE ('DB1 > 0') END as c85,
	   1 as c86,
	   T1."OverallSaleNet" as c87,
	   T1."OverallSaleNet" - T1."LatestProfitValue" as c88,
	   0 as c89,
	   T1."OverallSaleNet" - (T1."OverallSaleNet" - T1."LatestProfitValue") as c90,
	   (@CURRENT_DATE) as c91,
	   T1."InvoiceNumber" as c92,
	   T1."InvoiceDate" as c93,
	   CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END as c94,
	   CASE WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN ((CASE WHEN (T1."InvoiceDate" IS NOT NULL) THEN ((extract(DAY FROM (now()) - T1."InvoiceDate"))) ELSE (0) END) > 360) THEN ('> 360 Tage') ELSE null END as c95,
	   T1."ColourWheelbaseDesc" as c96,
	   T1."ColourWheelbaseDesc" as c97,
	   T1."PhysicalBranch" as c98,
	   T1."SupplierName" as c99,
	   T1."BoughtBy" as c100,
	   T1."SupplierName" || ' / ' || T1."BoughtBy" as c101,
	   T1."SupplierName" as c102,
	   T1."BoughtBy" as c103,
	   '' as c104,
	   '' as c105,
	   '' as c106,
	   '' as c107,
	   T1."Cost" as c108
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"."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'))
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,Invoicenumber
COLUMN,92,Invoicedate
COLUMN,93,Standtage
COLUMN,94,Standtagestaffel
COLUMN,95,Colourwheelbasedesc
COLUMN,96,Farbe
COLUMN,97,Standort_1
COLUMN,98,Suppliername
COLUMN,99,Boughtby
COLUMN,100,Lieferant/Einkäufer
COLUMN,101,Name_Lieferant
COLUMN,102,Name_Einkäufer
COLUMN,103,FZG_Einkäufer
COLUMN,104,EZ
COLUMN,105,Km-stand
COLUMN,106,Bestandsart
COLUMN,107,Einsatz