COGNOS QUERY
STRUCTURE,1,1
DATABASE,werwiso
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\op\Service_Rechnungsausgangsbuch_SB_OP_ims.imr
TITLE,Service_Rechnungsausgangsbuch_SB_OP_ims.imr
BEGIN SQL
select distinct '001' as c1,
	   c17 as c2,
	   c21 as c3,
	   c27 as c4,
	   CASE WHEN ((c22 = 'Barverkauf     ') and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') WHEN ((((c22 = 'Verkauf        ') and (c23 IN ('Gutschrift                                        ','Service                                           ','Verkauf                                           '))) and (c24 = 0)) and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') ELSE ('Service') END as c5,
	   c26 as c6,
	   c25 as c7,
	   XMIN(c28 for c17) as c8,
	   CASE WHEN ((substring((XMIN(c28 for c17)) from 1 for 1)) = '1') THEN ('Neuwagen') WHEN ((substring((XMIN(c28 for c17)) from 1 for 1)) = '2') THEN ('Gebrauchtwagen') ELSE ((CASE WHEN ((c22 = 'Barverkauf     ') and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') WHEN ((((c22 = 'Verkauf        ') and (c23 IN ('Gutschrift                                        ','Service                                           ','Verkauf                                           '))) and (c24 = 0)) and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') ELSE ('Service') END)) END as c9,
	   CASE WHEN ((CASE WHEN ((substring((XMIN(c28 for c17)) from 1 for 1)) = '1') THEN ('Neuwagen') WHEN ((substring((XMIN(c28 for c17)) from 1 for 1)) = '2') THEN ('Gebrauchtwagen') ELSE ((CASE WHEN ((c22 = 'Barverkauf     ') and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') WHEN ((((c22 = 'Verkauf        ') and (c23 IN ('Gutschrift                                        ','Service                                           ','Verkauf                                           '))) and (c24 = 0)) and (not (substring((XMIN(c28 for c17)) from 1 for 1)) IN ('1','2'))) THEN ('Teile') ELSE ('Service') END)) END) IN ('Neuwagen','Gebrauchtwagen')) THEN ((c20)) ELSE ((c21)) END as c10,
	   c20 as c11,
	   c19 as c12,
	   c18 as c13
from 
(select T1."Rechnungsnummer" as c17,
	   CASE WHEN (((asciiz(round(T1."Rechnungsnummer",0,0),9))) LIKE '0%') THEN ((substring(((asciiz(round(T1."Rechnungsnummer",0,0),9))) from 2 for 8))) ELSE (((asciiz(round(T1."Rechnungsnummer",0,0),9)))) END as c18,
	   (asciiz(round(T1."Rechnungsnummer",0,0),9)) as c19,
	   ((reverse(truncate(reverse((((truncate((substring(T3."Vertreter" from ((locate(' ',T3."Vertreter",1))) for 20))))) || ', ' || ((substring(pack(T3."Vertreter") from 1 for POSITION(' ' IN pack(T3."Vertreter") || ' ') - 1))))))))) as c20,
	   ((rtrim(T2."Name")) || ', ' || T2."Vorname") as c21,
	   T3."Vorgangsart" as c22,
	   T1."RechArt" as c23,
	   T1."Leistungsbetrag" as c24,
	   T3."Sachbearbeiter" as c25,
	   T3."Vertreter" as c26,
	   CASE WHEN (T3."Vorgangsart" IN ('Garantie       ')) THEN ('GWL') WHEN (T3."Vorgangsart" IN ('Interne Rechnun')) THEN ('Intern') ELSE ('Extern') END as c27,
	   CASE WHEN (((T4."Text1" LIKE '%Werkstattersatz%') and (T4."Kostenstelle" = '14')) or ((T4."Text1" LIKE '%Kraftstoffp%') and (T4."Kostenstelle" = '14'))) THEN ('51') ELSE (T4."Kostenstelle") END as c28
from "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."Vorgposi" T4,
	(("\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."REAUBUCH" T1 left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."VORGANG" T3 on T3."Vorgangsnummer" = T1."Vorgangsnummer") left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."personal" T2 on T3."SachbearbeiterNr" = T2."Personalnummer")
where ((T3."Vorgangsnummer" = T4."Vorgangsnummer") and (T3."SplitNr" = T4."SplitNr"))
and (((((T3."Vorgangsart" IN ('Barverkauf          ','Garantie            ','Service             ')) or ((T3."Vorgangsart" = 'Verkauf        ') and (T1."RechArt" IN ('Gutschrift                                        ','Service                                           ','Verkauf                                           ')))) and (T1."Name" <> 'Storniert')) and (not T4."Positionskennzeichen" IN ('T ','TB'))) and (not T4."Erloeskonto" LIKE '4%'))
) D1
order by c2 asc

END SQL
COLUMN,0,Mandant_ID
COLUMN,1,Rechnungsnummer
COLUMN,2,Serviceberater_ori
COLUMN,3,Umsatzart
COLUMN,4,Auftragsart_Service_ori
COLUMN,5,Vertreter
COLUMN,6,Sachbearbeiter
COLUMN,7,Minimum_Kostenstelle_Vorg_Posi
COLUMN,8,Auftragsart_Service
COLUMN,9,Serviceberater
COLUMN,10,Verkäufer
COLUMN,11,RGnr_Zeichen
COLUMN,12,RGnr_Zeichen_1