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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_CARLO
DATASOURCENAME,C:\GlobalCube\System\CARLO\IQD\serv_teile\auftraege_eds_intern_c11.imr
TITLE,auftraege_eds_intern_c11.imr
BEGIN SQL
select c290 as c1,
	   c289 as c2,
	   c288 as c3,
	   c213 as c4,
	   c287 as c5,
	   c286 as c6,
	   c285 as c7,
	   c284 as c8,
	   c283 as c9,
	   c282 as c10,
	   c281 as c11,
	   c280 as c12,
	   c279 as c13,
	   c208 as c14,
	   c207 as c15,
	   c278 as c16,
	   c277 as c17,
	   c276 as c18,
	   c275 as c19,
	   c250 as c20,
	   c274 as c21,
	   c273 as c22,
	   c249 as c23,
	   c272 as c24,
	   c271 as c25,
	   c270 as c26,
	   c269 as c27,
	   c199 as c28,
	   c268 as c29,
	   c267 as c30,
	   c197 as c31,
	   c266 as c32,
	   c265 as c33,
	   c264 as c34,
	   c263 as c35,
	   c236 as c36,
	   c235 as c37,
	   c262 as c38,
	   c261 as c39,
	   c234 as c40,
	   c260 as c41,
	   c259 as c42,
	   c258 as c43,
	   c257 as c44,
	   c232 as c45,
	   c256 as c46,
	   c231 as c47,
	   '1' as c48,
	   c253 as c49,
	   c255 as c50,
	   c254 as c51,
	   c253 as c52,
	   c252 as c53,
	   c251 as c54,
	   c206 as c55,
	   c250 as c56,
	   c249 as c57,
	   c248 as c58,
	   c247 as c59,
	   c246 as c60,
	   c245 as c61,
	   c244 as c62,
	   c196 as c63,
	   c208 as c64,
	   c243 as c65,
	   c199 as c66,
	   c242 as c67,
	   c242 as c68,
	   c241 as c69,
	   c240 as c70,
	   'Service' as c71,
	   c239 as c72,
	   c178 as c73,
	   c177 as c74,
	   c175 as c75,
	   c238 as c76,
	   c176 as c77,
	   c237 as c78,
	   c236 as c79,
	   c235 as c80,
	   c234 as c81,
	   c233 as c82,
	   c232 as c83,
	   c231 as c84,
	   c174 as c85,
	   c230 as c86,
	   c229 as c87,
	   c172 as c88,
	   c228 as c89,
	   c205 as c90,
	   c200 as c91,
	   c227 as c92,
	   1 as c93,
	   c226 as c94,
	   c225 as c95,
	   c181 as c96,
	   c224 as c97,
	   c223 as c98,
	   c222 as c99,
	   c221 as c100,
	   c220 as c101,
	   c219 as c102,
	   c221 as c103,
	   c220 as c104,
	   c219 as c105,
	   c218 as c106,
	   c217 as c107,
	   c216 as c108,
	   c203 as c109,
	   c215 as c110,
	   1 as c111,
	   XCOUNT(c281 for c172) as c112,
	   1 / (XCOUNT(c281 for c172)) as c113,
	   ('Serviceberater') as c114,
	   c214 as c115,
	   (c214) / (XCOUNT(c281 for c172)) as c116,
	   c213 as c117,
	   c212 as c118,
	   c211 as c119,
	   c210 as c120,
	   c209 as c121,
	   c208 as c122,
	   '1' as c123,
	   c207 as c124,
	   'Serviceberater' as c125,
	   c206 as c126,
	   c205 as c127,
	   c204 as c128,
	   '' as c129,
	   c203 as c130,
	   c202 as c131,
	   c201 as c132,
	   c200 as c133,
	   c199 as c134,
	   c198 as c135,
	   c197 as c136,
	   c196 as c137,
	   c195 as c138,
	   c194 as c139,
	   c194 as c140,
	   c189 as c141,
	   c193 as c142,
	   c192 as c143,
	   c191 as c144,
	   c190 as c145,
	   c189 as c146,
	   c188 as c147,
	   c187 as c148,
	   c186 as c149,
	   c185 as c150,
	   c185 as c151,
	   c181 as c152,
	   c184 as c153,
	   c183 as c154,
	   c182 as c155,
	   c181 as c156,
	   c180 as c157,
	   'Rechnung' as c158,
	   c179 as c159,
	   'abgerechnet' as c160,
	   'Service' as c161,
	   c178 as c162,
	   c177 as c163,
	   c176 as c164,
	   c175 as c165,
	   c174 as c166,
	   c173 as c167,
	   (1 / (XCOUNT(c281 for c172))) as c168
from 
(select T3."Auftragsnr_" as c172,
	   CASE WHEN (T3."Art" = 1) THEN (((T3."Menge"))) ELSE (0) END as c173,
	   (CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) as c174,
	   (CASE WHEN (((not T3."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 T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T3."Nr_" = 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c175,
	   (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."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 (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END) as c176,
	   (CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c177,
	   (CASE WHEN ((T3."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 (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) as c178,
	   CASE WHEN (((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) - (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."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 (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END)) < 0) THEN ('VK < EK') ELSE ('VK > EK') END as c179,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE '%LOHN%') THEN (T3."Produktbuchungsgruppe") ELSE null END as c180,
	   T8."Nr_" as c181,
	   T8."GMPD Code" || T8."GMPD Subcode" as c182,
	   T8."GMPD Subcode" as c183,
	   T8."GMPD Code" as c184,
	   (CASE WHEN ((CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - 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 (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - T7."Erstzulassung"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END) as c185,
	   (CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - T7."Erstzulassung"))) ELSE (0) END) / 365 as c186,
	   CASE WHEN (T7."Erstzulassung" <> TIMESTAMP '1753-01-01 00:00:00.000') THEN ((extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END) - T7."Erstzulassung"))) ELSE (0) END as c187,
	   T7."Erstzulassung" as c188,
	   T6."PLZ Code" as c189,
	   (od_left(T6."PLZ Code",4)) as c190,
	   (od_left(T6."PLZ Code",3)) as c191,
	   (od_left(T6."PLZ Code",2)) as c192,
	   (od_left(T6."PLZ Code",1)) as c193,
	   (CASE WHEN (((T11."Ländercode" IS NULL) or (T11."Ländercode" = ' ')) or (T11."Ländercode" IN ('D','DE'))) THEN ('Deutschland') ELSE ('Ausland') END) as c194,
	   T6."Ländercode" as c195,
	   (T6."Nr_" || ' - ' || T6."Name") as c196,
	   T3."Geschäftsbuchungsgruppe" as c197,
	   (od_left((ucase(T7."Modell")),3)) as c198,
	   T3."Markencode" as c199,
	   (CASE WHEN (T3."Markencode" IN ('OPEL','VW')) THEN (T3."Markencode") ELSE ('Fremdfabrikat') END) as c200,
	   T3."Kostenstellencode" || ' - ' || T10."Name" as c201,
	   CASE WHEN ((extract(DAY FROM (now()) - ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)))) <= 30) THEN ((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (T6."Nr_" || ' - ' || T6."Name")) ELSE ('Rechnungen älter 30 Tage') END as c202,
	   CASE WHEN ((extract(DAY FROM (now()) - (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) <= 30) THEN ('Intern') ELSE ('Aufträge älter 30 Tage') END as c203,
	   CASE WHEN ((extract(DAY FROM (now()) - ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)))) <= 100) THEN (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name")) ELSE ('Aufträge älter 100 Tage') END as c204,
	   (CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) as c205,
	   (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) as c206,
	   (CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END) as c207,
	   ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) as c208,
	   CASE WHEN (T9."Funktion" IN ('Service Berater','Serviceberater','Serviceverater')) THEN ('Serviceberater') ELSE ('sonstige MA') END as c209,
	   T9."Funktion" as c210,
	   T3."Herkunftsnr_(Zahlung)" as c211,
	   T1."Rech_ an Name" as c212,
	   T1."Rech_ an Deb_-Nr_" as c213,
	   (extract(DAY FROM ((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) - (CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END))) as c214,
	   (((CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name") || ' - ' || (CASE WHEN ((T5."Nachname" IS NOT NULL) or (T5."Nachname" <> '')) THEN (T1."Serviceberaternr_" || ' - ' || T5."Vorname" || ' ' || T5."Nachname") ELSE ('SB fehlt') END) || ' - ' || (asciiz(extract(YEAR FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),4) || '-' || asciiz(extract(MONTH FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2) || '-' || asciiz(extract(DAY FROM (CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)),2))) as c215,
	   (cdate((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END))) as c216,
	   (dayofweek(((now())))) as c217,
	   (now()) as c218,
	   (T8."Einstandspreis (durchschn_)") as c219,
	   (T8."EK-Preis (neuester)") as c220,
	   (T8."Einstandspreis") as c221,
	   T8."VK-Preis" as c222,
	   T8."Basiseinheitencode" as c223,
	   T8."Beschreibung" as c224,
	   (T3."Verrechnungsbetrag") as c225,
	   (CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) - (CASE WHEN ((((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END) = 0) and ((CASE WHEN ((T3."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 (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) = 0)) and ((CASE WHEN ((T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') and (T3."Nr_" <> 'E002540')) THEN ((CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END)) ELSE (0) END) <> 0)) THEN (((T3."Menge")) * T8."Einstandspreis") ELSE ((CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END)) END) as c226,
	   T3."Kundengruppencode" as c227,
	   T1."Archivierungsdatum" as c228,
	   CASE WHEN (((T3."Menge pro Stunde")) <> 0) THEN ((((T3."AW Mont_ 1")) + ((T3."AW Mont_ 2"))) / ((T3."Menge pro Stunde"))) ELSE (((T3."AW Mont_ 1")) + ((T3."AW Mont_ 2"))) END as c229,
	   CASE WHEN (((((T3."Menge pro Stunde")) <> 0) and ((CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) >= 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((T3."Vorgabezeit")) / ((T3."Menge pro Stunde"))) WHEN (((((T3."Menge pro Stunde")) <> 0) and ((CASE WHEN (((not T3."Nr_" LIKE 'HU-K%') and (not T3."Produktbuchungsgruppe" IN ('W_DP'))) and (not T3."Nr_" IN ('E002540'))) THEN (((T3."Menge (Stunde)"))) ELSE (0) END) < 0)) and (not T3."Nr_" LIKE 'HU-K%')) THEN (((T3."Vorgabezeit")) / ((T3."Menge pro Stunde")) * -1) ELSE (((T3."Vorgabezeit"))) END as c230,
	   (T3."AW Mont_ 2") as c231,
	   (T3."AW Mont_ 1") as c232,
	   T3."Vorgabezeitart" as c233,
	   (T3."Vorgabezeit") as c234,
	   (T3."Menge (Stunde)") as c235,
	   (T3."Menge pro Stunde") as c236,
	   CASE WHEN (((not T3."Produktbuchungsgruppe" IN ('W_KARO','W_MECH','W_LACK','W_KARO_AZK','W_LACK_AZE')) and (not T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\')) or (T3."Nr_" = 'E002540')) THEN (((T3."Einstandsbetrag"))) ELSE (0) END as c237,
	   CASE WHEN (T3."Produktbuchungsgruppe" LIKE 'T\_%' ESCAPE '\') THEN (((T3."Einstandsbetrag"))) ELSE (0) END as c238,
	   CASE WHEN ((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') THEN (((T3."Verrechnungsbetrag"))) ELSE ((CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END)) END as c239,
	   CASE WHEN (T3."Kostenstellencode" LIKE '1%') THEN ('1') WHEN (T3."Kostenstellencode" LIKE '2%') THEN ('2') WHEN (T3."Kostenstellencode" IN ('30','31','60')) THEN ('6') WHEN (T3."Kostenstellencode" IN ('40','62')) THEN ('3') WHEN (T3."Kostenstellencode" = '41') THEN ('4') WHEN (T3."Kostenstellencode" = '42') THEN ('5') WHEN (T3."Kostenstellencode" LIKE '7%') THEN ('7') WHEN (T3."Kostenstellencode" LIKE '9%') THEN ('0') ELSE null END as c240,
	   T3."Fahrgestellnummer" || ' - ' || T7."Modell" as c241,
	   T7."Modell" as c242,
	   (CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END) || ' - ' || T6."Name" as c243,
	   T6."Name" as c244,
	   T6."Nr_" as c245,
	   CASE WHEN (((T3."Herkunftsnr_" LIKE '%INT%') or ((CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or ((CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%')) THEN ('Intern') WHEN ((T3."Belegtyp" = 2) and (((not T3."Herkunftsnr_" LIKE '%INT%') or (not (CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or (not (CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%'))) THEN ('Extern') WHEN ((T3."Belegtyp" = 3) and (((not T3."Herkunftsnr_" LIKE '%INT%') or (not (CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END) LIKE '%INT%')) or (not (CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END) LIKE '%INT%'))) THEN ('GWL') ELSE null END as c246,
	   CASE WHEN (T1."Rech_ an Deb_-Nr_" IS NULL) THEN (T2."Rech_ an Deb_-Nr_") ELSE (T1."Rech_ an Deb_-Nr_") END as c247,
	   CASE WHEN (T3."Offen" = 0) THEN ((T3."Verkaufsbetrag" * -1)) ELSE ((T3."Verkaufsbetrag")) END as c248,
	   (T3."Einstandsbetrag") as c249,
	   (T3."Menge") as c250,
	   T5."Nachname" as c251,
	   T5."Vorname" as c252,
	   (CASE WHEN ('1' = '2') THEN ('20') ELSE ((CASE WHEN (T1."Filialcode" IS NULL) THEN (T2."Filialcode") ELSE (T1."Filialcode") END)) END) as c253,
	   T4."Bezeichnung" as c254,
	   T4."Code" as c255,
	   T3."Monteur Nr_ 2" as c256,
	   T3."Monteur Nr_ 1" as c257,
	   T1."Serviceberaternr_" as c258,
	   T3."Auftragszeilennr_" as c259,
	   T3."Herkunftsnr_" as c260,
	   T3."Fahrzeugartencode" as c261,
	   T3."Artikelgruppencode" as c262,
	   T3."Fahrgestellnummer" as c263,
	   T3."Nummernserie" as c264,
	   T3."Belegdatum" as c265,
	   T3."Produktbuchungsgruppe" as c266,
	   T3."Herkunftscode" as c267,
	   T3."Benutzer ID" as c268,
	   T3."Kostenstellencode" as c269,
	   T3."Werkstattbuchungsgruppe" as c270,
	   T3."Verkaufsbetrag" as c271,
	   T3."VK-Preis" as c272,
	   T3."Einstandspreis" as c273,
	   T3."EK-Preis" as c274,
	   T3."Beschreibung" as c275,
	   T3."Nr_" as c276,
	   T3."Art" as c277,
	   T3."Belegnr_" as c278,
	   CASE WHEN (T1."Auftragsdatum" IS NULL) THEN (T2."Auftragsdatum") ELSE (T1."Auftragsdatum") END as c279,
	   CASE WHEN (T1."Verk_ an Deb_-Nr_" IS NULL) THEN (T2."Verk_ an Deb_-Nr_") ELSE (T1."Verk_ an Deb_-Nr_") END as c280,
	   CASE WHEN (T1."Nr_" IS NULL) THEN (T2."Nr_") ELSE (T1."Nr_") END as c281,
	   CASE WHEN (T1."Belegart" IS NULL) THEN (T2."Belegart") ELSE (T1."Belegart") END as c282,
	   T1."Interne Belegnr_" as c283,
	   T1."Interner Auftrag" as c284,
	   T1."Filialcode" as c285,
	   T1."Buchungsdatum" as c286,
	   T1."Auftragsdatum" as c287,
	   T1."Verk_ an Deb_-Nr_" as c288,
	   T1."Nr_" as c289,
	   T1."Belegart" as c290
from ((((((((((("CARLO"."import"."Werkstattposten" T3 full outer join "CARLO"."import"."Archiv_Werkstattkopf" T1 on (T3."Auftragsnr_" = T1."Nr_") and (T3."Client_DB" = T1."Client_DB")) full outer join "CARLO"."import"."Werkstattkopf" T2 on (T2."Nr_" = T3."Auftragsnr_") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T4 on (T4."Code" = T3."Filialcode") and (T4."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Employee" T5 on (T1."Serviceberaternr_" = T5."Nr_") and (T1."Client_DB" = T5."Client_DB")) left outer join "CARLO"."import"."Debitor" T6 on (T3."Herkunftsnr_" = T6."Nr_") and (T3."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T3."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T3."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."Artikel" T8 on (T3."Nr_" = T8."Nr_") and (T3."Client_DB" = T8."Client_DB")) left outer join "CARLO"."import"."Employee" T9 on (T9."Nr_" = T3."Serviceberaternr_") and (T9."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T10 on (T3."Kostenstellencode" = T10."Code") and (T3."Client_DB" = T10."Client_DB")) full outer join "CARLO"."import"."Verkaufsrechnungskopf" T12 on (T12."Nr_" = T3."Belegnr_") and (T12."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Debitor" T11 on (T12."Verk_ an Deb_-Nr_" = T11."Nr_") and (T12."Client_DB" = T11."Client_DB"))
where (((CASE WHEN (T3."Geschäftsbuchungsgruppe" IN ('GEWL')) THEN ('GWL') WHEN (T3."Geschäftsbuchungsgruppe" LIKE '%INTERN%') THEN ('Intern') ELSE ('Extern') END) = 'Intern') and ((((((((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Nummernserie" <> '')) and (T3."Offen" = 0)) and ((T3."Belegnr_" LIKE 'WVRG%') or (T3."Belegnr_" LIKE 'WSGG%'))) and (T3."Nummernserie" <> 'WSLIEFG')) or ((((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Nummernserie" <> '')) and (T3."Offen" = 1)) and ((T3."Belegnr_" LIKE 'WVLG%') or (T3."Belegnr_" LIKE 'WVGL%')))) or (((((T3."Art" <> 2) and (not T3."Belegnr_" LIKE 'G%')) and (T3."Abgeschlossen" = 1)) and (T3."Offen" = 1)) and (T3."Belegnr_" LIKE 'WVAN%'))) and (((CASE WHEN (T1."Buchungsdatum" IS NULL) THEN (T2."Buchungsdatum") ELSE (T1."Buchungsdatum") END)) >= TIMESTAMP '2018-01-01 00:00:00.000')))
) D1
order by c11 asc

END SQL
COLUMN,0,A_Belegart
COLUMN,1,A_Nr
COLUMN,2,A_Verk An Deb -nr
COLUMN,3,A_Rech An Deb -nr
COLUMN,4,A_Auftragsdatum
COLUMN,5,A_Buchungsdatum
COLUMN,6,A_Filialcode
COLUMN,7,A_Interner Auftrag
COLUMN,8,A_Interne Belegnr
COLUMN,9,Belegart
COLUMN,10,Nr
COLUMN,11,Verk An Deb -nr
COLUMN,12,Auftragsdatum
COLUMN,13,Buchungsdatum
COLUMN,14,Filialcode
COLUMN,15,Belegnr
COLUMN,16,Art
COLUMN,17,APO_TEILE_Nr
COLUMN,18,Beschreibung
COLUMN,19,Menge_ori
COLUMN,20,Ek-preis
COLUMN,21,Einstandspreis
COLUMN,22,Einstandsbetrag_ori
COLUMN,23,Vk-preis
COLUMN,24,Verkaufsbetrag_ori
COLUMN,25,Werkstattbuchungsgruppe
COLUMN,26,Kostenstellencode
COLUMN,27,Markencode
COLUMN,28,Benutzer Id
COLUMN,29,Herkunftscode
COLUMN,30,Geschäftsbuchungsgruppe
COLUMN,31,Produktbuchungsgruppe
COLUMN,32,Belegdatum
COLUMN,33,Nummernserie
COLUMN,34,Fahrgestellnummer
COLUMN,35,Menge Pro Stunde
COLUMN,36,Menge (stunde)
COLUMN,37,Artikelgruppencode
COLUMN,38,Fahrzeugartencode
COLUMN,39,Vorgabezeit
COLUMN,40,Herkunftsnr
COLUMN,41,Auftragszeilennr
COLUMN,42,Serviceberaternr
COLUMN,43,Monteur Nr 1
COLUMN,44,Aw Mont 1
COLUMN,45,Monteur Nr 2
COLUMN,46,Aw Mont 2
COLUMN,47,Hauptbetrieb
COLUMN,48,Standort
COLUMN,49,FIL-Code
COLUMN,50,FIL-Bezeichnung
COLUMN,51,Standort_1_ori
COLUMN,52,Vorname
COLUMN,53,Nachname
COLUMN,54,Serviceberater
COLUMN,55,Menge
COLUMN,56,Einstandsbetrag
COLUMN,57,Verkaufsbetrag
COLUMN,58,Rech An Deb -nr
COLUMN,59,Umsatzart_ori
COLUMN,60,KD_Nr
COLUMN,61,KD_Name
COLUMN,62,Kunde
COLUMN,63,Invoice Date
COLUMN,64,Order Number
COLUMN,65,Fabrikat
COLUMN,66,Modell
COLUMN,67,Model
COLUMN,68,Fahrzeug
COLUMN,69,Kostenstelle
COLUMN,70,Auftragsart
COLUMN,71,Lines Net Value
COLUMN,72,Umsatz Lohn
COLUMN,73,Umsatz Teile Service
COLUMN,74,Umsatz Sonstiges
COLUMN,75,Einsatz Teile Service_ori
COLUMN,76,Einsatz Teile Service
COLUMN,77,Einsatz Sonstiges
COLUMN,78,Menge_pro_Stunde
COLUMN,79,Menge_stunde
COLUMN,80,Sollzeit
COLUMN,81,Sollzeit_Art
COLUMN,82,AW_Mont_1
COLUMN,83,AW_Mont_2
COLUMN,84,verk. Stunden
COLUMN,85,Soll-Stunden
COLUMN,86,benutzte Zeit_ori
COLUMN,87,Auftragsnr
COLUMN,88,Archivierungsdatum
COLUMN,89,Umsatzart
COLUMN,90,Marke
COLUMN,91,Kundenart
COLUMN,92,DG_1
COLUMN,93,DB Teile
COLUMN,94,Verr_Betrag
COLUMN,95,Nr
COLUMN,96,Beschreibung
COLUMN,97,Basiseinheitencode
COLUMN,98,Vk-preis
COLUMN,99,Einstandspreis_ART
COLUMN,100,Ek-preis (neuester)_ART
COLUMN,101,Einstandspreis (durchschn )_ART
COLUMN,102,Einstandspreis_Art
COLUMN,103,EK_preis_neu_Art
COLUMN,104,Einstandspreis_Durch_Art
COLUMN,105,Heute
COLUMN,106,Tag
COLUMN,107,Buchungsdatum_Datum
COLUMN,108,Rechnungsausgang
COLUMN,109,Order_Number_RG_Ausg
COLUMN,110,DG_1
COLUMN,111,DG_2
COLUMN,112,Durchgänge
COLUMN,113,Standort_1
COLUMN,114,Tage bis Rechnung_ori
COLUMN,115,Tage bis Rechnung
COLUMN,116,Rech An Deb -nr
COLUMN,117,Rech An Name
COLUMN,118,Rechnunsgkunde
COLUMN,119,Funktion
COLUMN,120,Servicberater / sonst. MA
COLUMN,121,Date
COLUMN,122,Company_ID
COLUMN,123,Department_ID
COLUMN,124,Employee_Function
COLUMN,125,Service_Advisor_Name
COLUMN,126,Turnover_Type_Desc
COLUMN,127,Order_Desc_100
COLUMN,128,Invoice_Desc_100
COLUMN,129,Order_Desc_30
COLUMN,130,Invoice_Desc_30
COLUMN,131,Cost_Centre_ID
COLUMN,132,Make_Desc
COLUMN,133,Make_Group
COLUMN,134,Model_Desc
COLUMN,135,Customer_Group_Owner
COLUMN,136,Customer_Name_Owner
COLUMN,137,Ländercode
COLUMN,138,Land
COLUMN,139,Cust_Zipcode_Country
COLUMN,140,Plz Code
COLUMN,141,Cust_Zipcode_First
COLUMN,142,Cust_Zipcode_Second
COLUMN,143,Cust_Zipcode_Third
COLUMN,144,Cust_Zipcode_Fourth
COLUMN,145,Cust_Zipcode
COLUMN,146,Erstzulassung
COLUMN,147,Fahrzeugalter_Tage
COLUMN,148,Fahrzeugalter
COLUMN,149,FZG_Altersstaffel
COLUMN,150,Car_Age
COLUMN,151,Nr_Artikel
COLUMN,152,Gmpd Code_Artikel
COLUMN,153,Gmpd Subcode_Artikel
COLUMN,154,GMPD_Group
COLUMN,155,Parts_Number
COLUMN,156,Repair_Group_Desc
COLUMN,157,Invoice_Credit_Note
COLUMN,158,Sign_DB1
COLUMN,159,Order_Status
COLUMN,160,Order_Type_Desc
COLUMN,161,Job_Amount
COLUMN,162,Parts_Amount
COLUMN,163,Parts_Purch_Amount
COLUMN,164,Misc_Amount
COLUMN,165,Invoiced_Time
COLUMN,166,Quantity
COLUMN,167,Order_Count