C11/System/CARLO/IQD/Serv_Teile/service_gutschriften_c11.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\serv_teile\service_gutschriften_c11.imr
TITLE,service_gutschriften_c11.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   c62 as c62,
	   c63 as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   c68 as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77,
	   c78 as c78,
	   c79 as c79,
	   c80 as c80,
	   c81 as c81,
	   c82 as c82,
	   c83 as c83,
	   c84 as c84,
	   c85 as c85,
	   c86 as c86,
	   c87 as c87,
	   c88 as c88,
	   c89 as c89,
	   c90 as c90,
	   c91 as c91,
	   c92 as c92,
	   c93 as c93,
	   c94 as c94,
	   c95 as c95,
	   c96 as c96,
	   c97 as c97,
	   c98 as c98,
	   c99 as c99,
	   c100 as c100,
	   c101 as c101,
	   c102 as c102,
	   XCOUNT(c92 for c240) as c103,
	   c104 as c104,
	   c105 as c105,
	   c106 as c106,
	   c107 as c107,
	   c108 as c108,
	   c109 as c109,
	   c110 as c110,
	   c111 as c111,
	   c112 as c112,
	   c113 as c113,
	   c114 as c114,
	   c115 as c115,
	   c116 as c116,
	   c117 as c117,
	   c118 as c118,
	   c119 as c119,
	   c120 as c120,
	   c121 as c121,
	   c122 as c122,
	   c123 as c123,
	   c124 as c124,
	   c125 as c125,
	   c126 as c126,
	   c127 as c127,
	   c128 as c128,
	   c129 as c129,
	   c130 as c130,
	   c131 as c131,
	   c132 as c132,
	   c133 as c133,
	   c134 as c134,
	   c135 as c135
from 
(select c239 as c1,
	   c238 as c2,
	   c237 as c3,
	   c236 as c4,
	   c235 as c5,
	   c234 as c6,
	   c233 as c7,
	   c232 as c8,
	   c231 as c9,
	   c199 as c10,
	   c192 as c11,
	   c230 as c12,
	   c229 as c13,
	   c228 as c14,
	   c227 as c15,
	   c226 as c16,
	   '1' as c17,
	   c209 as c18,
	   c225 as c19,
	   c224 as c20,
	   c223 as c21,
	   c222 as c22,
	   c208 as c23,
	   c207 as c24,
	   c221 as c25,
	   c220 as c26,
	   c219 as c27,
	   c191 as c28,
	   c201 as c29,
	   c184 as c30,
	   'Gutschrift' as c31,
	   'abgerechnet' as c32,
	   c156 as c33,
	   c218 as c34,
	   c197 as c35,
	   c196 as c36,
	   c195 as c37,
	   c194 as c38,
	   c193 as c39,
	   c217 as c40,
	   c216 as c41,
	   c215 as c42,
	   c153 as c43,
	   c149 as c44,
	   -1 as c45,
	   XCOUNT(c172 for c142) as c46,
	   -1 / (XCOUNT(c172 for c142)) as c47,
	   c158 as c48,
	   c214 as c49,
	   c213 as c50,
	   c212 as c51,
	   c211 as c52,
	   c210 as c53,
	   c157 as c54,
	   c187 as c55,
	   '1' as c56,
	   c209 as c57,
	   'Serviceberater' as c58,
	   c208 as c59,
	   c207 as c60,
	   c206 as c61,
	   c205 as c62,
	   c204 as c63,
	   c203 as c64,
	   c202 as c65,
	   c201 as c66,
	   c184 as c67,
	   c200 as c68,
	   c199 as c69,
	   c198 as c70,
	   c197 as c71,
	   c196 as c72,
	   c195 as c73,
	   c194 as c74,
	   c193 as c75,
	   c192 as c76,
	   c191 as c77,
	   c190 as c78,
	   c189 as c79,
	   c188 as c80,
	   c187 as c81,
	   c186 as c82,
	   c185 as c83,
	   c184 as c84,
	   c183 as c85,
	   c182 as c86,
	   c181 as c87,
	   c180 as c88,
	   c179 as c89,
	   c178 as c90,
	   c177 as c91,
	   c142 as c92,
	   c176 as c93,
	   c175 as c94,
	   c174 as c95,
	   c173 as c96,
	   c172 as c97,
	   c171 as c98,
	   c170 as c99,
	   c169 as c100,
	   c168 as c101,
	   c167 as c102,
	   c166 as c104,
	   c165 as c105,
	   c164 as c106,
	   c163 as c107,
	   c155 as c108,
	   c154 as c109,
	   c152 as c110,
	   c151 as c111,
	   c162 as c112,
	   c161 as c113,
	   c160 as c114,
	   c150 as c115,
	   c159 as c116,
	   c158 as c117,
	   'Gutschrift' as c118,
	   c157 as c119,
	   'abgerechnet' as c120,
	   c156 as c121,
	   c155 as c122,
	   c154 as c123,
	   c153 as c124,
	   c152 as c125,
	   c151 as c126,
	   c150 as c127,
	   c149 as c128,
	   (-1 / (XCOUNT(c172 for c142))) as c129,
	   c148 as c130,
	   c147 as c131,
	   c146 as c132,
	   c145 as c133,
	   c144 as c134,
	   c143 as c135,
	   c141 as c240
from 
(select (T5."Nr_" || '_' || (cast_numberToString(cast_integer(T9."Zeilennr_")))) as c141,
	   T5."Werkstattauftragsnummer" as c142,
	   CASE WHEN ((CASE WHEN (((not T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabattbetrag"))) ELSE (0) END as c143,
	   CASE WHEN ((CASE WHEN (((not T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabatt %"))) ELSE (0) END as c144,
	   CASE WHEN ((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabattbetrag"))) ELSE (0) END as c145,
	   CASE WHEN ((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) THEN (((T9."Zeilenrabatt %"))) ELSE (0) END as c146,
	   ((T9."Zeilenrabattbetrag")) as c147,
	   ((T9."Zeilenrabatt %")) as c148,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Menge"))))) ELSE (0) END) as c149,
	   (((T10."Ist-Zeit")) * -1) as c150,
	   (CASE WHEN ((T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE')) and (T9."Nr_" <> 'E002540')) THEN (((T9."Menge")) * -1) ELSE (0) END) as c151,
	   (CASE WHEN (((not T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) as c152,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T8."Einstandsbetrag"))) ELSE (0) END) as c153,
	   (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) as c154,
	   (CASE WHEN ((T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (T9."Nr_" <> 'E002540')) THEN ((((T9."Betrag"))) * -1) ELSE (0) END) as c155,
	   (CASE WHEN (T5."Nr_" LIKE 'T%') THEN ('Teile') WHEN (T5."Nr_" LIKE 'W%') THEN ('Service') ELSE null END) as c156,
	   (CASE WHEN ((CASE WHEN ((T9."Produktbuchungsgruppe" LIKE '%TZ%') or (T9."Produktbuchungsgruppe" LIKE '%REIF%')) THEN (((((T9."Betrag"))))) ELSE (0) END) - (CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T8."Einstandsbetrag"))) ELSE (0) END) < 0) THEN ('VK < EK') ELSE ('VK > EK') END) as c157,
	   T10."Einheitencode" as c158,
	   CASE WHEN ((((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T5."Ausgleich-mit Belegnr_" = T10."Belegnr_")) or (((CASE WHEN (T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T10."Belegnr_" IS NULL))) THEN ('ja') WHEN ((((CASE WHEN (((not T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T5."Ausgleich-mit Belegnr_" = T10."Belegnr_")) or (((CASE WHEN (((not T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and (not T9."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T9."Nr_" = 'E002540')) THEN (((((T9."Betrag")) * -1))) ELSE (0) END) <> 0) and (T10."Belegnr_" IS NULL))) THEN ('ja') WHEN (((T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and ((T5."Ausgleich-mit Belegnr_" = T10."Belegnr_") or (T10."Belegnr_" IS NULL)))) THEN ('ja') WHEN (((T9."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE','W_ELEK','W_ELEK_NL','W_KARO_NL','W_LACK_NL','W_MECH_NL','W_SATT')) and ((T5."Ausgleich-mit Belegnr_" = '') and (T10."Belegnr_" LIKE 'WVRG%')))) THEN ('ja') ELSE ('nein') END as c159,
	   T10."Belegnr_" as c160,
	   (T10."Ist-Zeit") as c161,
	   T5."Ausgleich-mit Belegnr_" as c162,
	   (T9."Einstandspreis") as c163,
	   T9."Produktbuchungsgruppe" as c164,
	   T9."Kostenstellencode" as c165,
	   (T9."Betrag") as c166,
	   (T9."Menge") as c167,
	   T9."Einheit" as c168,
	   T9."Beschreibung" as c169,
	   T9."Nr_" as c170,
	   T9."Art" as c171,
	   T9."Werkst_ Auftragszeilennr_" as c172,
	   T9."Werkst_ Auftragsnr_" as c173,
	   T9."Zeilennr_" as c174,
	   T5."Serviceberaternr_" as c175,
	   T5."Filialcode" as c176,
	   T5."Modell" as c177,
	   T5."Amtliches Kennzeichen" as c178,
	   T5."Fahrgestellnummer" as c179,
	   T5."Benutzer ID" as c180,
	   T5."Belegdatum" as c181,
	   T5."Geschäftsbuchungsgruppe" as c182,
	   T5."Debitorenbuchungsgruppe" as c183,
	   T5."Markencode" as c184,
	   T5."Buchungsbeschreibung" as c185,
	   T5."Lieferdatum" as c186,
	   T5."Buchungsdatum" as c187,
	   T5."Rech_ an Deb_-Nr_" as c188,
	   T5."Verk_ an Deb_-Nr_" as c189,
	   T5."Nr_" as c190,
	   (CASE WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END) as c191,
	   T2."PLZ Code" as c192,
	   ((od_left(T2."PLZ Code",4))) as c193,
	   ((od_left(T2."PLZ Code",3))) as c194,
	   ((od_left(T2."PLZ Code",2))) as c195,
	   ((od_left(T2."PLZ Code",1))) as c196,
	   (CASE WHEN (((T2."Ländercode" IS NULL) or (T2."Ländercode" = ' ')) or (T2."Ländercode" IN ('D','DE'))) THEN ('Deutschland') ELSE ('Ausland') END) as c197,
	   T2."Nr_" || ' - ' || T2."Name" as c198,
	   T2."Geschäftsbuchungsgruppe" as c199,
	   (od_left((ucase(T5."Modell")),3)) as c200,
	   (CASE WHEN (T5."Markencode" IN ('OPEL')) THEN (T5."Markencode") ELSE ('Fremdfabrikat') END) as c201,
	   T9."Kostenstellencode" || ' - ' || T12."Name" as c202,
	   CASE WHEN ((extract(DAY FROM (now()) - T5."Buchungsdatum")) <= 35) THEN (T5."Nr_" || ' - ' || (CASE WHEN (T6."Nr_" IS NOT NULL) THEN (T6."Nr_" || ' - ' || T6."Vorname" || ' ' || T6."Nachname") ELSE ('SB fehlt') END) || ' - ' || T1."Name" || ' - ' || T5."Fahrgestellnummer") ELSE ('Rechnungen älter 35 Tage') END as c203,
	   CASE WHEN ((extract(DAY FROM (now()) - T5."Buchungsdatum")) <= 35) THEN (T5."Werkstattauftragsnummer") ELSE ('Aufträge älter 35 Tage') END as c204,
	   CASE WHEN ((extract(DAY FROM (now()) - T5."Buchungsdatum")) <= 100) THEN (T5."Nr_" || ' - ' || T1."Name" || ' - ' || T5."Fahrgestellnummer") ELSE ('Rechnungen älter 100 Tage') END as c205,
	   CASE WHEN ((extract(DAY FROM (now()) - T5."Buchungsdatum")) <= 100) THEN (T5."Werkstattauftragsnummer" || ' - ' || T2."Name") ELSE ('Aufträge älter 100 Tage') END as c206,
	   (CASE WHEN (T1."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T1."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) as c207,
	   (CASE WHEN (T6."Nr_" IS NOT NULL) THEN (T6."Nr_" || ' - ' || T6."Vorname" || ' ' || T6."Nachname") ELSE ('SB fehlt') END) as c208,
	   (CASE WHEN ('1' = '2') THEN ('20') ELSE (T5."Filialcode") END) as c209,
	   CASE WHEN (T9."Produktbuchungsgruppe" LIKE '%LOHN%') THEN (T9."Produktbuchungsgruppe") ELSE null END as c210,
	   T11."GMPD Code" || T11."GMPD Subcode" as c211,
	   T11."Bonuscode" as c212,
	   T11."GMPD Subcode" as c213,
	   T11."GMPD Code" as c214,
	   (T8."Einstandsbetrag") as c215,
	   (T8."Menge") as c216,
	   (od_left(T2."PLZ Code",5)) as c217,
	   T2."Ländercode" as c218,
	   (CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END) / 365 as c219,
	   CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM T5."Buchungsdatum" - T7."Erstzulassung"))) ELSE (0) END as c220,
	   T7."Erstzulassung" as c221,
	   T6."Funktion" as c222,
	   T6."Nachname" as c223,
	   T6."Vorname" as c224,
	   T6."Nr_" as c225,
	   T4."Code" || ' - ' || T4."Beschreibung" as c226,
	   T3."Code" || ' - ' || T3."Beschreibung" as c227,
	   T2."Kundenart" as c228,
	   T2."Vorname" as c229,
	   T2."Nachname" as c230,
	   T2."Name" as c231,
	   T2."Nr_" as c232,
	   T1."Kundenart" as c233,
	   T1."Vorname" as c234,
	   T1."Nachname" as c235,
	   T1."PLZ Code" as c236,
	   T1."Geschäftsbuchungsgruppe" as c237,
	   T1."Name" as c238,
	   T1."Nr_" as c239
from (((((("CARLO"."import"."Verkaufsgutschriftskopf" T5 left outer join "CARLO"."import"."Debitor" T1 on (T5."Rech_ an Deb_-Nr_" = T1."Nr_") and (T5."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Debitor" T2 on (T5."Verk_ an Deb_-Nr_" = T2."Nr_") and (T5."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T3 on (T3."Code" = T1."Geschäftsbuchungsgruppe") and (T3."Client_DB" = T1."Client_DB")) left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T4 on (T4."Code" = T2."Geschäftsbuchungsgruppe") and (T4."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Employee" T6 on (T5."Serviceberaternr_" = T6."Nr_") and (T5."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T5."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T5."Client_DB" = T7."Client_DB")),
	(((("CARLO"."import"."Verkaufsgutschriftszeile" T9 left outer join "CARLO"."import"."Werkstattposten" T8 on ((((T9."Belegnr_" = T8."Belegnr_") and (T9."Nr_" = T8."Nr_")) and (T8."Nummernserie" LIKE 'WSG%')) and (T8."Client_DB" = T9."Client_DB")) and (T9."Werkst_ Auftragszeilennr_" = T8."Auftragszeilennr_")) left outer join "CARLO"."import"."Arbeitswertposten" T10 on ((T9."Werkst_ Auftragsnr_" = T10."Werkstattauftragsnr_") and (T9."Werkst_ Auftragszeilennr_" = T10."Werkstattauftragszeilennr_")) and (T9."Client_DB" = T10."Client_DB")) left outer join "CARLO"."import"."Artikel" T11 on (T11."Nr_" = T8."Nr_") and (T11."Client_DB" = T8."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T12 on T12."Code" = T9."Kostenstellencode")
where ((T5."Nr_" = T9."Belegnr_") and (T5."Client_DB" = T9."Client_DB"))
and ((((T5."Nr_" LIKE 'W%') and ((od_year(T5."Buchungsdatum")) >= (od_year((now()))) - 2)) and (not T9."Art" IN (0,11,12))) and (T9."Produktbuchungsgruppe" <> 'DIREKT19'))
) D1
) D4
order by c78 asc,c97 asc,c96 asc

END SQL
COLUMN,0,Nr_Rechnungskunde
COLUMN,1,Name_Rechnungskunde
COLUMN,2,Geschäftsbuchungsgruppe_Rechnungskunde
COLUMN,3,Plz Code_Rechnungskunde
COLUMN,4,Nachname_Rechnungskunde
COLUMN,5,Vorname_Rechnungskunde
COLUMN,6,Kundenart_Rechnungskunde
COLUMN,7,Nr_Fahrer
COLUMN,8,Name_Fahrer
COLUMN,9,Geschäftsbuchungsgruppe_Fahrer
COLUMN,10,Plz Code_Fahrer
COLUMN,11,Nachname_Fahrer
COLUMN,12,Vorname_Fahrer
COLUMN,13,Kundenart_Fahrer
COLUMN,14,Kundengruppe_Rechnungskunde
COLUMN,15,Kundengruppe_Fahrer
COLUMN,16,Hauptbetrieb
COLUMN,17,Standort
COLUMN,18,Nr_Employee
COLUMN,19,Vorname_Employee
COLUMN,20,Nachname_Employee
COLUMN,21,Funktion_Employee
COLUMN,22,Serviceberater
COLUMN,23,Umsatzart
COLUMN,24,Erstzulassung
COLUMN,25,Fahrzeugalter_Tage
COLUMN,26,Fahrzeugalter
COLUMN,27,FZG-Altersstaffel
COLUMN,28,Marke
COLUMN,29,Fabrikat
COLUMN,30,Rechnung_Gutschrift
COLUMN,31,Auftragsstatus
COLUMN,32,Auftragsart
COLUMN,33,Ländercode_Fahrer
COLUMN,34,Land
COLUMN,35,PLZ_1_Stelle
COLUMN,36,PLZ_2_Stelle
COLUMN,37,PLZ_3_Stelle
COLUMN,38,PLZ_4_Stelle
COLUMN,39,PLZ
COLUMN,40,Menge_Werkstattposten
COLUMN,41,Einstandsbetrag_Werkstattposten
COLUMN,42,Einsatz Teile
COLUMN,43,Menge_Teile
COLUMN,44,DG_1
COLUMN,45,DG_2
COLUMN,46,DG
COLUMN,47,Nr_Artikel
COLUMN,48,Gmpd Code_Artikel
COLUMN,49,Gmpd Subcode_Artikel
COLUMN,50,Bonuscode_Artikel
COLUMN,51,GMPD_Group
COLUMN,52,Repair_Group_Desc
COLUMN,53,DB1_><_EK
COLUMN,54,Date
COLUMN,55,Company_ID
COLUMN,56,Department_ID
COLUMN,57,Employee_Function
COLUMN,58,Service_Advisor_Name
COLUMN,59,Turnover_Type_Desc
COLUMN,60,Order_Desc_100
COLUMN,61,Invoice_Desc_100
COLUMN,62,Order_Desc_30
COLUMN,63,Invoice_Desc_30
COLUMN,64,Cost_Centre_ID
COLUMN,65,Make_Desc
COLUMN,66,Make_Group
COLUMN,67,Model_Desc
COLUMN,68,Customer_Group_Owner
COLUMN,69,Customer_Name_Owner
COLUMN,70,Cust_Zipcode_Country
COLUMN,71,Cust_Zipcode_First
COLUMN,72,Cust_Zipcode_Second
COLUMN,73,Cust_Zipcode_Third
COLUMN,74,Cust_Zipcode_Fourth
COLUMN,75,Cust_Zipcode
COLUMN,76,Car_Age
COLUMN,77,Nr_Gutschrift
COLUMN,78,Verk An Deb -nr
COLUMN,79,Rech An Deb -nr
COLUMN,80,Buchungsdatum
COLUMN,81,Lieferdatum
COLUMN,82,Buchungsbeschreibung
COLUMN,83,Markencode
COLUMN,84,Debitorenbuchungsgruppe
COLUMN,85,Geschäftsbuchungsgruppe
COLUMN,86,Belegdatum
COLUMN,87,Benutzer Id
COLUMN,88,Fahrgestellnummer
COLUMN,89,Amtliches Kennzeichen
COLUMN,90,Modell
COLUMN,91,Werkstattauftragsnummer
COLUMN,92,Filialcode
COLUMN,93,Serviceberaternr
COLUMN,94,Zeilennr
COLUMN,95,Werkst Auftragsnr_Verk_Rg_Zeile
COLUMN,96,Werkst Auftragszeilennr_Verk_Rg_Zeile
COLUMN,97,Art
COLUMN,98,Nr_APO_Teile
COLUMN,99,Beschreibung
COLUMN,100,Einheit
COLUMN,101,Menge_alles
COLUMN,102,Einstandspreis (mw)
COLUMN,103,Betrag
COLUMN,104,Kostenstellencode
COLUMN,105,Produktbuchungsgruppe
COLUMN,106,Einstandspreis
COLUMN,107,Umsatz Lohn
COLUMN,108,Umsatz Teile
COLUMN,109,Umsatz Sonst.
COLUMN,110,verk. Std.
COLUMN,111,Ausgleich-mit Belegnr
COLUMN,112,Ist-zeit_Arb_Wert_Posten
COLUMN,113,Belegnr_Arb_Wert_Posten
COLUMN,114,ben. Zeit
COLUMN,115,ja/nein
COLUMN,116,Parts_Number
COLUMN,117,Invoice_Credit_Note
COLUMN,118,Sign_DB1
COLUMN,119,Order_Status
COLUMN,120,Order_Type_Desc
COLUMN,121,Job_Amount
COLUMN,122,Parts_Amount
COLUMN,123,Parts_Purch_Amount
COLUMN,124,Misc_Amount
COLUMN,125,Invoiced_Time
COLUMN,126,Used_Time
COLUMN,127,Quantity
COLUMN,128,Order_Count
COLUMN,129,Rabatt Lohn %
COLUMN,130,Rabatt Lohn
COLUMN,131,Rabatt Teile %
COLUMN,132,Rabatt Teile
COLUMN,133,Rabatt Sonst. %
COLUMN,134,Rabatt Sonst.