GAPS/Portal/System/IQD/NW/NW_AE_Archiv.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,EDS_1
DATASOURCENAME,C:\GAPS\Portal\System\IQD\NW\NW_AE_Archiv.imr
TITLE,NW_AE_Archiv.imr
BEGIN SQL
select T1."Belegart" as c1,
	   T1."Nr_" as c2,
	   T1."Verk_ an Deb_-Nr_" as c3,
	   T1."Rech_ an Deb_-Nr_" as c4,
	   T1."Rech_ an Name" as c5,
	   T1."Rech_ an Name 2" as c6,
	   T1."Auftragsdatum" as c7,
	   T1."Buchungsdatum" as c8,
	   T1."Lieferdatum" as c9,
	   T1."Lagerortcode" as c10,
	   T1."Kostenstellencode" as c11,
	   T1."Markencode" as c12,
	   T1."Debitorenbuchungsgruppe" as c13,
	   T1."VK-Preise inkl_ MWSt" as c14,
	   T1."Mengenrabatt zulassen" as c15,
	   T1."Verkäufercode" as c16,
	   T1."Auftragsgruppe" as c17,
	   T1."Rechnung" as c18,
	   T1."Buchungsnr_" as c19,
	   T1."Letzte Buchungsnr_" as c20,
	   T1."Sammelrechnung" as c21,
	   T1."Geschäftsbuchungsgruppe" as c22,
	   T1."Verk_ an Name" as c23,
	   T1."Verk_ an Name 2" as c24,
	   T1."Belegdatum" as c25,
	   T1."Archivierungsdatum" as c26,
	   T1."Arch_ durch Benutzer" as c27,
	   T1."Archivierungsgrund" as c28,
	   T1."Fahrgestellnummer" as c29,
	   T1."Amtliches Kennzeichen" as c30,
	   T1."Modell" as c31,
	   T1."Auftragsart" as c32,
	   T1."Kundenbestätigungdatum" as c33,
	   T1."Bestellart" as c34,
	   T1."Erstes Lieferdatum" as c35,
	   T1."Letztes Lieferdatum" as c36,
	   T1."Haupt Lieferdatum" as c37,
	   T1."Fahrzeug VK-Preisdatum" as c38,
	   T1."Finanzierung_Leasing" as c39,
	   T1."Vermittlung" as c40,
	   '1' as c41,
	   (od_left(T1."Lagerortcode",2)) as c42,
	   T2."Code" as c43,
	   T2."Name" as c44,
	   T2."Name" as c45,
	   T3."Markencode" as c46,
	   T3."Markencode" as c47,
	   T1."Modell" as c48,
	   T1."Verk_ an Name" as c49,
	   CASE WHEN (T1."Fahrgestellnummer" IS NOT NULL) THEN (T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || T1."Verk_ an Name") ELSE (T1."Nr_" || ' - ' || T1."Verk_ an Name") END as c50,
	   T3."Fahrzeugstatus" as c51,
	   T3."Statistikgruppe" as c52,
	   T3."Erstzulassung" as c53,
	   T3."Anzahl Vorbesitzer" as c54,
	   T3."Lagerbuchungsgruppe" as c55,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END as c56,
	   T3."Produktbuchungsgruppe" as c57,
	   CASE WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('GW regelb.','GW diffb.')) THEN ('Gebrauchtwagen') WHEN ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%VFW%') THEN ('Vorführwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",4)) IN ('RENT')) THEN ('Mietwagen') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWD')) THEN ('GW diffb.') WHEN ((od_left(T3."Produktbuchungsgruppe",3)) IN ('GWR')) THEN ('GW regelb.') ELSE ('Neuwagen') END) IN ('Vorführwagen','Neuwagen','Mietwagen')) THEN ('Neuwagen') ELSE null END as c58,
	   '' as c59,
	   T3."Produktbuchungsgruppe" as c60,
	   T1."Nr_" || ' - ' || T1."Fahrgestellnummer" || ' - ' || ((od_left(T1."Lagerortcode",2))) || ' - ' || T3."Produktbuchungsgruppe" || ' - ' || T2."Name" || ' / ' || T1."Verk_ an Name" || ' - ' || (asciiz(extract(YEAR FROM T1."Auftragsdatum"),4) || '-' || asciiz(extract(MONTH FROM T1."Auftragsdatum"),2) || '-' || asciiz(extract(DAY FROM T1."Auftragsdatum"),2)) as c61,
	   1 as c62
from (("DE0682"."dbo"."Tretter Bad Bergzabern$Archivierter Verkaufskopf" T1 left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Verkäufer_Einkäufer" T2 on T1."Verkäufercode" = T2."Code") left outer join "DE0682"."dbo"."Tretter Bad Bergzabern$Fahrzeug" T3 on T3."Fahrgestellnummer" = T1."Fahrgestellnummer")
where (((((T1."Belegart" = 1) and (T1."Nr_" LIKE '%FVAN%')) and (T1."Auftragsdatum" >= TIMESTAMP '2009-01-01 00:00:00.000')) and (T1."Archivierungsgrund" <> 1)) and (T1."Lieferdatum" <> TIMESTAMP '1753-01-01 00:00:00.000'))
order by c29 asc

END SQL
COLUMN,0,Belegart
COLUMN,1,Nr
COLUMN,2,Verk An Deb -nr
COLUMN,3,Rech An Deb -nr
COLUMN,4,Rech An Name
COLUMN,5,Rech An Name 2
COLUMN,6,Auftragsdatum
COLUMN,7,Buchungsdatum
COLUMN,8,Lieferdatum
COLUMN,9,Lagerortcode
COLUMN,10,Kostenstellencode
COLUMN,11,Markencode
COLUMN,12,Debitorenbuchungsgruppe
COLUMN,13,Vk-preise Inkl Mwst
COLUMN,14,Mengenrabatt Zulassen
COLUMN,15,Verkäufercode
COLUMN,16,Auftragsgruppe
COLUMN,17,Rechnung
COLUMN,18,Buchungsnr
COLUMN,19,Letzte Buchungsnr
COLUMN,20,Sammelrechnung
COLUMN,21,Geschäftsbuchungsgruppe
COLUMN,22,Verk An Name
COLUMN,23,Verk An Name 2
COLUMN,24,Belegdatum
COLUMN,25,Archivierungsdatum
COLUMN,26,Arch Durch Benutzer
COLUMN,27,Archivierungsgrund
COLUMN,28,Fahrgestellnummer
COLUMN,29,Amtliches Kennzeichen
COLUMN,30,Modell
COLUMN,31,Auftragsart
COLUMN,32,Kundenbestätigungdatum
COLUMN,33,Bestellart
COLUMN,34,Erstes Lieferdatum
COLUMN,35,Letztes Lieferdatum
COLUMN,36,Haupt Lieferdatum
COLUMN,37,Fahrzeug Vk-preisdatum
COLUMN,38,Finanzierung Leasing
COLUMN,39,Vermittlung
COLUMN,40,Hauptbetrieb
COLUMN,41,Standort
COLUMN,42,Code_verkäufer_einkäufer
COLUMN,43,Name_verkäufer_einkäufer
COLUMN,44,Verkäufer
COLUMN,45,Markencode_FZG
COLUMN,46,Fabrikat
COLUMN,47,Model
COLUMN,48,Kunde
COLUMN,49,FZG
COLUMN,50,Fahrzeugstatus
COLUMN,51,Statistikgruppe
COLUMN,52,Erstzulassung
COLUMN,53,Anzahl Vorbesitzer
COLUMN,54,Lagerbuchungsgruppe
COLUMN,55,Fahrzeugtyp
COLUMN,56,Produktbuchungsgruppe
COLUMN,57,Fahrzeugart
COLUMN,58,Kundenart
COLUMN,59,Fahrzeugtyp_1
COLUMN,60,FZG_Detail
COLUMN,61,Menge