COGNOS QUERY
STRUCTURE,1,1
DATABASE,werwiso
DATASOURCENAME,C:\GlobalCube\System\WERWISO\IQD\serv_teile\Service_intern_fuer_Bestand.imr
TITLE,Service_intern_fuer_Bestand.imr
BEGIN SQL
select c151 as c1,
	   c150 as c2,
	   c90 as c3,
	   c131 as c4,
	   c149 as c5,
	   c148 as c6,
	   c137 as c7,
	   c147 as c8,
	   c146 as c9,
	   c145 as c10,
	   c144 as c11,
	   c143 as c12,
	   c142 as c13,
	   c141 as c14,
	   c140 as c15,
	   c139 as c16,
	   c138 as c17,
	   c137 as c18,
	   '' as c19,
	   '' as c20,
	   c136 as c21,
	   'Serviceberater' as c22,
	   c90 as c23,
	   c133 as c24,
	   c135 as c25,
	   c132 as c26,
	   c134 as c27,
	   c134 as c28,
	   c133 as c29,
	   c132 as c30,
	   'Serviceberater' as c31,
	   c131 as c32,
	   c130 as c33,
	   c129 as c34,
	   c128 as c35,
	   c127 as c36,
	   c97 as c37,
	   c126 as c38,
	   c125 as c39,
	   c124 as c40,
	   c123 as c41,
	   c122 as c42,
	   c112 as c43,
	   c121 as c44,
	   c120 as c45,
	   c119 as c46,
	   c118 as c47,
	   c117 as c48,
	   c116 as c49,
	   c115 as c50,
	   c114 as c51,
	   c113 as c52,
	   c112 as c53,
	   c111 as c54,
	   c110 as c55,
	   c109 as c56,
	   c108 as c57,
	   c107 as c58,
	   c106 as c59,
	   '' as c60,
	   '' as c61,
	   '' as c62,
	   c105 as c63,
	   c104 as c64,
	   c103 as c65,
	   c102 as c66,
	   c101 as c67,
	   c100 as c68,
	   c99 as c69,
	   c98 as c70,
	   c97 as c71,
	   c96 as c72,
	   c95 as c73,
	   c94 as c74,
	   c93 as c75,
	   (0) as c76,
	   1 as c77,
	   XCOUNT(c129 for c90) as c78,
	   1 / (XCOUNT(c129 for c90)) as c79,
	   XSUM(c96 for c90) as c80,
	   c92 as c81,
	   c91 as c82,
	   '' as c83,
	   '' as c84,
	   c91 as c85
from 
(select T1."Vorgangsnummer" as c90,
	   (CASE WHEN ((T6."Warengruppe" IS NULL) and ((CASE WHEN (((T6."Kostenstelle" IN ('41        ','44        ','45        ')) and (T6."KostRechMerkKostentraeger" <> '73 ')) and (not T6."Erloeskonto" LIKE '1%')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END) <> 0)) THEN ('Lohn') WHEN ((T6."Warengruppe" IS NULL) and ((CASE WHEN (T6."Erloeskonto" LIKE '1592%') THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END) <> 0)) THEN ('TÜV') WHEN ((T6."Warengruppe" IS NULL) and ((CASE WHEN (T6."Kostenstelle" IN ('42        ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END) <> 0)) THEN ('Fremdl.') WHEN ((T6."Warengruppe" IS NULL) and ((CASE WHEN (T6."KostRechMerkKostentraeger" IN ('73 ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END) <> 0)) THEN ('Mietw.') WHEN ((T6."Warengruppe" IS NULL) and ((CASE WHEN (T6."Kostenstelle" IN ('52        ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END) <> 0)) THEN ('Rädereinl.') ELSE (T6."Warengruppe") END) as c91,
	   (rtrim(T6."Kostenstelle")) || ' - ' || T7."KSTName" as c92,
	   CASE WHEN (T6."Kostenstelle" IN ('30        ','31        ','32        ','33        ')) THEN ((CASE WHEN (T1."RechArt" <> 'Gutschrift                                        ') THEN (T6."RabattBetrag1") ELSE (T6."RabattBetrag1" * -1) END)) ELSE (0) END as c93,
	   CASE WHEN ((T6."Kostenstelle" IN ('41        ','44        ','45        ')) and (T6."KostRechMerkKostentraeger" <> '73 ')) THEN ((CASE WHEN (T1."RechArt" <> 'Gutschrift                                        ') THEN (T6."RabattBetrag1") ELSE (T6."RabattBetrag1" * -1) END)) ELSE (0) END as c94,
	   CASE WHEN (T1."RechArt" <> 'Gutschrift                                        ') THEN (T6."RabattBetrag1") ELSE (T6."RabattBetrag1" * -1) END as c95,
	   CASE WHEN ((T6."Kostenstelle" IN ('41        ','44        ','45        ')) and (T6."KostRechMerkKostentraeger" <> '73 ')) THEN (((T6."Menge")) / 10) ELSE (0) END as c96,
	   (T6."Menge") as c97,
	   CASE WHEN (T6."Kostenstelle" IN ('52        ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c98,
	   CASE WHEN (T6."KostRechMerkKostentraeger" IN ('73 ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c99,
	   CASE WHEN (T6."Kostenstelle" IN ('42        ')) THEN (((T6."Menge" * T6."Einkaufspreis"))) ELSE (0) END as c100,
	   CASE WHEN (T6."Kostenstelle" IN ('42        ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c101,
	   CASE WHEN (T6."Erloeskonto" LIKE '1592%') THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c102,
	   CASE WHEN (T6."Kostenstelle" IN ('30        ','31        ','32        ','33        ')) THEN (((T6."Menge" * T6."Einkaufspreis"))) ELSE (0) END as c103,
	   CASE WHEN (T6."Kostenstelle" IN ('30        ','31        ','32        ','33        ')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c104,
	   CASE WHEN (((T6."Kostenstelle" IN ('41        ','44        ','45        ')) and (T6."KostRechMerkKostentraeger" <> '73 ')) and (not T6."Erloeskonto" LIKE '1%')) THEN ((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1"))) ELSE (0) END as c105,
	   CASE WHEN ((CASE WHEN (T6."Kostenstelle" BETWEEN '30' AND '39') THEN ((absolute((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1")))) - (absolute(((T6."Menge" * T6."Einkaufspreis"))))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as c106,
	   CASE WHEN (T6."Kostenstelle" BETWEEN '30' AND '39') THEN ((absolute((((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1")))) - (absolute(((T6."Menge" * T6."Einkaufspreis"))))) ELSE (0) END as c107,
	   (T6."Menge" * T6."Einkaufspreis") as c108,
	   ((T6."Menge" * T6."Verkaufspreis") - T6."RabattBetrag1") as c109,
	   T6."KostRechMerkKostentraeger" as c110,
	   T6."KostRechMerkKostenstelle" as c111,
	   T6."RabattBetrag1" as c112,
	   T6."Kostenstelle" as c113,
	   T6."MengeMonteurID3" as c114,
	   T6."MonteurID3" as c115,
	   T6."MengeMonteurID2" as c116,
	   T6."MonteurID2" as c117,
	   T6."MengeMonteurID1" as c118,
	   T6."MonteurID1" as c119,
	   T6."Warengruppe" as c120,
	   T6."RabattBetrag2" as c121,
	   T6."RabattProzent" as c122,
	   T6."Verkaufspreis100" as c123,
	   T6."Verkaufspreis" as c124,
	   T6."Einkaufspreis100" as c125,
	   T6."Einkaufspreis" as c126,
	   T6."Text1" as c127,
	   T6."Nummer" as c128,
	   T6."Positionsnummer" as c129,
	   T6."Positionskennzeichen" as c130,
	   ((cdatetime(T1."Rechnungsdatum"))) as c131,
	   (T2."KostRechMerkMarke" || ' - ' || T5."Bezeichnung") as c132,
	   T4."FahrgestellNummer" as c133,
	   T4."Typ" as c134,
	   T4."Kennzeichen" as c135,
	   ((rtrim(T3."Name")) || ', ' || T3."Vorname") as c136,
	   T1."Nettobetrag" as c137,
	   T1."Gebrauchtfahrzeugbetrag" as c138,
	   T1."Neufahrzeugbetrag" as c139,
	   T1."Auslagenbetrag" as c140,
	   T1."Fremdleistungbetrag" as c141,
	   T1."Altteilsteuerbetrag" as c142,
	   T1."Kleinmaterialbetrag" as c143,
	   T1."Leistungsbetrag" as c144,
	   T1."Artikelbetrag" as c145,
	   T1."Uebergabekennzeichen" as c146,
	   T1."FibuKonto" as c147,
	   T2."Vorgangsart" as c148,
	   T1."RechArt" as c149,
	   T1."Rechnungsnummer" as c150,
	   T1."Filial_ID" as c151
from "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."SplitVorgang" T4,
	((("\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."INREBUCH" T1 left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."VORGANG" T2 on T1."Vorgangsnummer" = T2."Vorgangsnummer") left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."personal" T3 on T2."SachbearbeiterNr" = T3."Personalnummer") left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."KostenStMerkMarke" T5 on (od_left(T5."Marke",2)) = (od_left(T2."KostRechMerkMarke",2))),
	("\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."Vorgposi" T6 left outer join "\\210.3.206.23\werwiso\wwsDaten\WerWiSoTabs\SuK\Mandant.add"."KOSTENST" T7 on T7."KostenStelle" = T6."Kostenstelle")
where (T2."SplitNr" = T4."SplitNr") and ((T2."Vorgangsnummer" = T6."Vorgangsnummer") and (T2."SplitNr" = T6."SplitNr"))
and ((((((T2."Vorgangsart" IN ('Barverkauf          ','Garantie            ','Service             ','Interne Rechnun')) and (T1."Name" <> 'Storniert')) and (not T6."Positionskennzeichen" IN ('T ','TB'))) and (not T6."KostRechMerkKostenstelle" BETWEEN '10' AND '29')) and (not T6."Kostenstelle" BETWEEN '10' AND '29')) and (not T6."Erloeskonto" LIKE '4%'))
) D1
order by c24 asc,c3 asc,c2 asc

END SQL
COLUMN,0,Filial Id
COLUMN,1,Rechnungsnummer
COLUMN,2,Vorgangsnummer
COLUMN,3,Rechnungsdatum
COLUMN,4,Rechart
COLUMN,5,Vorgangsart_Vorgang
COLUMN,6,Nettobetrag
COLUMN,7,Fibukonto
COLUMN,8,Uebergabekennzeichen
COLUMN,9,Artikelbetrag
COLUMN,10,Leistungsbetrag
COLUMN,11,Kleinmaterialbetrag
COLUMN,12,Altteilsteuerbetrag
COLUMN,13,Fremdleistungbetrag
COLUMN,14,Auslagenbetrag
COLUMN,15,Neufahrzeugbetrag
COLUMN,16,Gebrauchtfahrzeugbetrag
COLUMN,17,Betrag_GC
COLUMN,18,KST_Vorg_Posi
COLUMN,19,Kostentraeger_Vorg_Posi
COLUMN,20,Serviceberater
COLUMN,21,Employee_Function
COLUMN,22,Order Number
COLUMN,23,Fahrgestellnummer_Splitvorgang
COLUMN,24,Kennzeichen_Splitvorgang
COLUMN,25,Fabrikat
COLUMN,26,Typ_Splitvorgang
COLUMN,27,Model
COLUMN,28,Fahrzeug
COLUMN,29,Marke
COLUMN,30,Zuordnung_Funktion
COLUMN,31,Invoice Date
COLUMN,32,Positionskennzeichen
COLUMN,33,Positionsnummer
COLUMN,34,Nummer
COLUMN,35,Text1
COLUMN,36,Menge
COLUMN,37,Einkaufspreis
COLUMN,38,Einkaufspreis100
COLUMN,39,Verkaufspreis
COLUMN,40,Verkaufspreis100
COLUMN,41,Rabattprozent
COLUMN,42,Rabattbetrag1
COLUMN,43,Rabattbetrag2
COLUMN,44,Warengruppe
COLUMN,45,Monteurid1
COLUMN,46,Mengemonteurid1
COLUMN,47,Monteurid2
COLUMN,48,Mengemonteurid2
COLUMN,49,Monteurid3
COLUMN,50,Mengemonteurid3
COLUMN,51,Kostenstelle_VorgPosi
COLUMN,52,Rabattbetrag1
COLUMN,53,Kostrechmerkkostenstelle
COLUMN,54,Kostrechmerkkostentraeger
COLUMN,55,VK_Betrag_VorgPosi
COLUMN,56,EK_Betrag_Vorg_Posi
COLUMN,57,DB1_Teile_fuer_Kenner
COLUMN,58,DB1_><_EK
COLUMN,59,Parts_Focus_Group
COLUMN,60,Parts_Make_Desc
COLUMN,61,Parts_Group_Desc
COLUMN,62,Lohn
COLUMN,63,Teile
COLUMN,64,EW Teile
COLUMN,65,TÜV
COLUMN,66,Fremdl.
COLUMN,67,EW Fremdl.
COLUMN,68,Mietw.
COLUMN,69,Rädereinl.
COLUMN,70,Menge_VorgPosi
COLUMN,71,verk. Std.
COLUMN,72,Rabattbetrag_VorgPosi
COLUMN,73,NL Lohn
COLUMN,74,NL Teile
COLUMN,75,NL Sonst.
COLUMN,76,DG1
COLUMN,77,DG2
COLUMN,78,DG
COLUMN,79,Summe_verk_Std.
COLUMN,80,Kostenstelle
COLUMN,81,Produktbuchungsgruppe
COLUMN,82,Repair_Group_Desc
COLUMN,83,Auftragsposition
COLUMN,84,Zuordnung_Produktbuchungsgruppe