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