COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GlobalCube\system\OPTIMA\IQD\Serv_Teile\Auftraege_SPP_Split.imr TITLE,Auftraege_SPP_Split.imr BEGIN SQL select T1."ORDER_NUMBER" as c1, T1."REGISTER_NUMBER" as c2, T1."STATUS" as c3, T1."STATE_KEY_DATE" as c4, T1."DEBIT_ACCOUNT" as c5, T1."INVOICE_NUMBER" as c6, T1."WORKSHOP_MODEL" as c7, T1."STATE_CODE" as c8, T1."TRANSACT_DATE" as c9, T1."HANDLER" as c10, T1."DELIVERY_ACCOUNT" as c11, T1."DEPARTMENT" as c12, T1."DEBET_DEPARTMENT" as c13, T1."SALESMAN" as c14, T1."DEBIT_PERM" as c15, T1."ORDER_DATE" as c16, T1."DELIVERY_DATE" as c17, T1."INVOICE_DATE" as c18, T1."PMT_TERM" as c19, T1."DUEDATE_1" as c20, CASE WHEN (T2."SEL_NAME" IN ('Dietmar Brehmer','Ömer Cokbilir','Carsten Göbelt','Carsten Goebelt','Silke Liebe','Rene Schäfer','Thomas Kienbaum','Marcos Pazos Nieto','Michael Zeller','Thomas Barnebeck','Christian Libowski','Bünyamin Tanrikulu','Andre Biller','Felice Mastrandrea','Matthias Molter','Alexander Lappöhn','Sinan Cetin','Jeremias Perske','Andre Scheurich','Janek Van Der Lucht','Alexander Englert','Christian Hartig','Holger Conrad','Markus Gawlik','Ibrahim Küyner','Michael Christ','Manfred Peter','Christian Heim','Andreas Podlipny ','Nadine Wierschin','Andreas Müller ','Arsenio Rosso ','Roberto Bombardieri','Christian Jung','Marcus Schwarz','Thomas Schulz','Michael Staudinger','Dennis Rapp','Gerhard Janetzko','Christian Agneskirchner','Nico Amend','Francesco Galoppo','Bernd Fahm','Hagen Borth','Frank Lieske','Matthias Ernst','Stefan Bohn','Guenter Lewold','Reinhard Zucker','Juergen Klug')) THEN ('Serviceberater') ELSE ('Sonstige') END as c21, T1."COSTS" as c22, T1."PURCH_TAX" as c23, T1."ORDERS_GROSSVALUE" as c24, T1."TAX_SHARE" as c25, T1."DISCOUNT_AMOUNT" as c26, T1."PRICE_CODE" as c27, T1."MISC_ADDS" as c28, T1."STOCK" as c29, T1."PLACE_CODE" as c30, T1."INTERNAL_CODE" as c31, T1."CUSTOMER_GROUP" as c32, T1."BASIS_NUMBER" as c33, T1."MILEAGE" as c34, T1."PREV_STATUS" as c35, T1."SALES_CLASS_NUMBER" as c36, T1."INVOICE_DISC_PERC" as c37, T1."SALES_TAX_FREE" as c38, T1."TITLE" as c39, T1."NAME" as c40, T1."STREET_ADDR" as c41, T1."ADDR_2" as c42, T1."ZIPCODE" as c43, T1."MAIL_ADDR" as c44, T1."DISCOUNT_LIMIT" as c45, T1."REFERENCE_NUMBER" as c46, T1."EXPECTED_ORDER_TIM" as c47, T1."MODEL_TEXT" as c48, T1."WORKSHOP_PRICECODE" as c49, T1."SPLIT_COUNTER" as c50, T1."ARRIVAL_TIME" as c51, T1."ARRIVAL_DATE" as c52, T1."END_DATE" as c53, T1."END_TIME" as c54, T1."FAC_MODEL_CODE_S" as c55, T1."MAKE_CD" as c56, T1."YEAR_MODEL" as c57, T1."TRANSFER_MAKE_CD" as c58, T1."CHASSIS_NUMBER" as c59, T1."WORKSHOP_TEAM" as c60, T1."COMMISSION_SALESMAN" as c61, T1."ACTUAL_INV_DATE_TIME" as c62, T1."UNIQUE_IDENT" as c63, T3."ORDER_NUMBER" as c64, T3."LINE_NUMBER" as c65, T3."ORDER_LINETYPE" as c66, T3."REDUCTION_CODE" as c67, T3."REDUCTION_AMOUNT" as c68, T3."MECHANIC_CODE" as c69, T3."SALESMAN" as c70, T3."DISCOUNT" as c71, T3."STDPRICE" as c72, T3."LINES_NET_VALUE" as c73, T3."PROD_CODE" as c74, T3."MAKE_CD" as c75, T3."PRODUCT_GROUP" as c76, T3."PROD_NAME" as c77, T3."ORDER_QUANTITY" as c78, T3."DELIVERY_QUANTITY" as c79, T3."LINE_COSTS" as c80, T3."REPAIR_CODE" as c81, T3."REPAIR_GROUP" as c82, T3."REPAIR_NAME" as c83, T3."USED_TIME" as c84, T3."EST_TIME" as c85, T3."INV_TIME" as c86, T3."USED_TIME_INT" as c87, T3."EST_TIME_INT" as c88, T3."INV_TIME_INT" as c89, T3."MAKE_TIME_UNIT" as c90, T3."UNIQUE_IDENT" as c91, T4."STAT_CODE" as c92, T4."STAT_SPECIFY" as c93, T5."DEPARTMENT_TYPE_ID" as c94, T5."DESCRIPTION" as c95, T2."SELLER_CODE" as c96, T2."SEL_NAME" as c97, T2."SEL_DEPARTMENT" as c98, T2."SEL_FIRST_NAME" as c99, T2."SEL_FAMILY_NAME" as c100, T6."CUSTOMER_GROUP" as c101, T6."CUST_GROUP_SPECIFY" as c102, T7."SELLER_CODE" as c103, T7."SEL_NAME" as c104, T7."SEL_DEPARTMENT" as c105, T7."SEL_FIRST_NAME" as c106, T7."SEL_FAMILY_NAME" as c107, T8."REPAIR_GROUP" as c108, T8."MAKE_CD" as c109, T8."REPAIR_GRP_SPECIFY" as c110, T1."CLIENT_DB" as c111, CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END as c112, (substring(T1."DEPARTMENT" from 2 for 1)) as c113, (substring(T1."DEPARTMENT" from 3 for 2)) || ' - ' || T5."DESCRIPTION" as c114, T6."CUSTOMER_GROUP" || ' - ' || T6."CUST_GROUP_SPECIFY" as c115, CASE WHEN ((T1."CUSTOMER_GROUP" BETWEEN '10' AND '69') or (T1."CUSTOMER_GROUP" IN ('90','94'))) THEN ('Extern') WHEN (T1."CUSTOMER_GROUP" IN ('91','92')) THEN ('GWL') WHEN (((T1."CUSTOMER_GROUP" IN ('99','00')) or (T1."PMT_TERM" = 'IN')) or ((od_left(T1."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('Intern') ELSE null END as c116, T1."PMT_TERM" || ' - ' || (CASE WHEN (T2."SEL_NAME" IN ('Dietmar Brehmer','Ömer Cokbilir','Carsten Göbelt','Carsten Goebelt','Silke Liebe','Rene Schäfer','Thomas Kienbaum','Marcos Pazos Nieto','Michael Zeller','Thomas Barnebeck','Christian Libowski','Bünyamin Tanrikulu','Andre Biller','Felice Mastrandrea','Matthias Molter','Alexander Lappöhn','Sinan Cetin','Jeremias Perske','Andre Scheurich','Janek Van Der Lucht','Alexander Englert','Christian Hartig','Holger Conrad','Markus Gawlik','Ibrahim Küyner','Michael Christ','Manfred Peter','Christian Heim','Andreas Podlipny ','Nadine Wierschin','Andreas Müller ','Arsenio Rosso ','Roberto Bombardieri','Christian Jung','Marcus Schwarz','Thomas Schulz','Michael Staudinger','Dennis Rapp','Gerhard Janetzko','Christian Agneskirchner','Nico Amend','Francesco Galoppo','Bernd Fahm','Hagen Borth','Frank Lieske','Matthias Ernst','Stefan Bohn','Guenter Lewold','Reinhard Zucker','Juergen Klug')) THEN ('Serviceberater') ELSE ('Sonstige') END) as c117, CASE WHEN ((T2."SEL_FAMILY_NAME" IS NULL) or (T2."SEL_FAMILY_NAME" = ' ')) THEN (T1."SALESMAN" || ' - ' || T2."SEL_NAME") ELSE ((rtrim(T2."SEL_FAMILY_NAME")) || ', ' || T2."SEL_FIRST_NAME") END as c118, T2."LICENCE_ID" as c119, T7."SEL_NAME" as c120, CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '59') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as c121, T1."STATUS" || ' - ' || T4."STAT_SPECIFY" as c122, CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINES_NET_VALUE") ELSE null END as c123, CASE WHEN (((T3."INV_TIME" <> 0) and (T3."LINES_NET_VALUE" <> .00)) or ((T3."PROD_CODE" = 'EP') and (T3."STDPRICE" <> 0))) THEN (T3."LINES_NET_VALUE") WHEN ((T3."INV_TIME" <> 0) and (T3."LINES_NET_VALUE" = .00)) THEN (T3."REDUCTION_AMOUNT") ELSE null END as c124, CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINES_NET_VALUE") ELSE null END as c125, CASE WHEN (T1."STATUS" IN ('35','37','39','47','49','34','36')) THEN ('Rechnung/Gutschrift') WHEN (T1."STATUS" IN ('30','40','32')) THEN ('offen') WHEN (T1."STATUS" IN ('41','31')) THEN ('Kostenvoranschlag') ELSE null END as c126, CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END as c127, T3."EST_TIME_INT" as c128, T3."USED_TIME_INT" as c129, CASE WHEN ((T3."ORDER_LINETYPE" = '3') and (not T3."PROD_CODE" IN ('TU','EP'))) THEN (T3."LINES_NET_VALUE") ELSE null END as c130, CASE WHEN (T3."MAKE_TIME_UNIT" = 'AW12') THEN (T3."INV_TIME") WHEN (T3."MAKE_TIME_UNIT" IN ('S100','CV')) THEN (T3."INV_TIME" * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('A100')) THEN (T3."INV_TIME" / 100 * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('A120')) THEN (T3."INV_TIME" / 120 * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('AW10')) THEN (T3."INV_TIME" / 10 * 12) ELSE null END as c131, (rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || T1."PRICE_CODE" as c132, T3."EST_TIME_INT" * 12 as c133, CASE WHEN (T3."MAKE_TIME_UNIT" = 'AW12') THEN (T3."USED_TIME") WHEN (T3."MAKE_TIME_UNIT" IN ('S100','CV')) THEN (T3."USED_TIME" * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('A100')) THEN (T3."USED_TIME" / 100 * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('A120')) THEN (T3."USED_TIME" / 120 * 12) WHEN (T3."MAKE_TIME_UNIT" IN ('AW10')) THEN (T3."USED_TIME" / 10 * 12) ELSE null END as c134, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END)) END as c135, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T3."USED_TIME_INT") END as c136, 12 as c137, T9."GLOBAL_MAKE_CD" as c138, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END)) END as c139, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END)) END as c140, T9."DESCRIPTION" as c141, T9."DESCRIPTION" as c142, T10."MOD_LIN_SPECIFY" as c143, T1."BASIS_NUMBER" || ' - ' || T1."REGISTER_NUMBER" as c144, CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END as c145, CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END as c146, CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END as c147, T3."LINES_NET_VALUE" - T3."LINE_COSTS" as c148, ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END)) END)) as c149, (CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END)) END) as c150, CASE WHEN ((((((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T3."LINE_COSTS") ELSE null END)) END))) < 0) or (((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END)) END)) < 0)) and ((CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END) = 'Rechnung')) THEN ('VK < EK') ELSE ('VK > EK') END as c151, (database()) as c152, CASE WHEN ((CASE WHEN ((T2."SEL_FAMILY_NAME" IS NULL) or (T2."SEL_FAMILY_NAME" = ' ')) THEN (T1."SALESMAN" || ' - ' || T2."SEL_NAME") ELSE ((rtrim(T2."SEL_FAMILY_NAME")) || ', ' || T2."SEL_FIRST_NAME") END) IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || T1."PRICE_CODE") || ' - ' || (rtrim(T2."SEL_FAMILY_NAME"))) ELSE ((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || T1."PRICE_CODE") || ' - ' || T1."SALESMAN") END as c153, CASE WHEN ((extract(DAY FROM (now()) - T1."INVOICE_DATE")) <= 7) THEN ((CASE WHEN ((CASE WHEN ((T2."SEL_FAMILY_NAME" IS NULL) or (T2."SEL_FAMILY_NAME" = ' ')) THEN (T1."SALESMAN" || ' - ' || T2."SEL_NAME") ELSE ((rtrim(T2."SEL_FAMILY_NAME")) || ', ' || T2."SEL_FIRST_NAME") END) IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || T1."PRICE_CODE") || ' - ' || (rtrim(T2."SEL_FAMILY_NAME"))) ELSE ((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || T1."PRICE_CODE") || ' - ' || T1."SALESMAN") END) || ' - ' || (asciiz(extract(YEAR FROM T1."INVOICE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."INVOICE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."INVOICE_DATE"),2))) ELSE null END as c154, (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN (((T1."CLIENT_DB" IN ('dese01')) and ((CASE WHEN (((T1."INVOICE_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and (T1."CLIENT_DB" = 'deop01')) and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('65','S5'))) and (T3."MAKE_TIME_UNIT" = 'S100')) THEN (T3."INV_TIME_INT" / 100) ELSE (T3."INV_TIME_INT") END)) END) * 12 as c155, (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T3."USED_TIME_INT") END) * 12 as c156, CASE WHEN (T9."DESCRIPTION" IN ('Opel')) THEN (T9."DESCRIPTION") ELSE ('Fremdfabrikat') END as c157, T11."FIRST_REG_DATE" as c158, CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END as c159, (CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365 as c160, CASE WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 0.01 AND 0.99) THEN ('1') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 1.00 AND 1.99) THEN ('2') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 2.00 AND 2.99) THEN ('3') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 3.00 AND 3.99) THEN ('4') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 4.00 AND 4.99) THEN ('5') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 5.00 AND 5.99) THEN ('6') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 6.00 AND 6.99) THEN ('7') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 7.00 AND 7.99) THEN ('8') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 8.00 AND 8.99) THEN ('9') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 9.00 AND 9.99) THEN ('10') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) > 9.99) THEN ('> 10') WHEN (((CASE WHEN (T11."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T11."FIRST_REG_DATE"))) ELSE (0) END) / 365) = 0) THEN ('keine Angabe') ELSE null END as c161, CASE WHEN ((T3."INV_TIME" <> 0) and (T3."LINES_NET_VALUE" = .00)) THEN (T3."REDUCTION_AMOUNT" * -1) WHEN ((T3."ORDER_LINETYPE" = '3') and (T3."PROD_CODE" = 'EP')) THEN (T3."LINES_NET_VALUE") ELSE null END as c162, CASE WHEN (T3."ORDER_LINETYPE" = '1') THEN (T3."DISCOUNT") ELSE (0) END as c163, CASE WHEN (T3."ORDER_LINETYPE" <> '1') THEN (T3."DISCOUNT") ELSE (0) END as c164, (rtrim(T1."PRICE_CODE")) || ' - ' || (rtrim(T1."WORKSHOP_PRICECODE")) as c165 from (((((((((("OPTIMA"."import"."ORDER_HEADER" T1 left outer join "OPTIMA"."import"."VPP43" T2 on (T1."SALESMAN" = T2."SELLER_CODE") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."ORDER_LINE" T3 on (T1."ORDER_NUMBER" = T3."ORDER_NUMBER") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP25" T4 on (T1."STATUS" = T4."STAT_CODE") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T5 on ((od_right(T1."DEPARTMENT",2)) = T5."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T6 on (T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T7 on (T3."MECHANIC_CODE" = T7."SELLER_CODE") and (T3."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP73" T8 on ((T3."REPAIR_GROUP" = T8."REPAIR_GROUP") and (T3."MAKE_CD" = T8."MAKE_CD")) and (T3."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T9 on (T1."MAKE_CD" = T9."GLOBAL_MAKE_CD") and (T1."CLIENT_DB" = T9."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T11 on ((T1."CHASSIS_NUMBER" = T11."CHASSIS_NUMBER") and (T1."BASIS_NUMBER" = T11."BASIS_NUMBER")) and (T1."CLIENT_DB" = T11."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP5Q" T10 on ((T11."MODEL_LINE" = T10."MODEL_LINE") and (T11."MAKE_CD" = T10."MAKE_CD")) and (T11."CLIENT_DB" = T10."CLIENT_DB")) where ((T1."STATUS" IN ('35','37','39','47','49','50','51','36','34')) and (T1."INVOICE_DATE" >= TIMESTAMP '2019-01-01 00:00:00.000')) order by c111 asc,c1 asc,c5 asc,c40 asc,c27 asc,c14 asc,c100 asc,c153 asc,c132 asc END SQL COLUMN,0,Order Number_ori COLUMN,1,Register Number COLUMN,2,Status COLUMN,3,State Key Date COLUMN,4,Debit Account COLUMN,5,Invoice Number COLUMN,6,Workshop Model COLUMN,7,State Code COLUMN,8,Transact Date COLUMN,9,Handler COLUMN,10,Delivery Account COLUMN,11,Department COLUMN,12,Debet Department COLUMN,13,Salesman COLUMN,14,Debit Perm COLUMN,15,Order Date COLUMN,16,Delivery Date COLUMN,17,Invoice Date COLUMN,18,Pmt Term COLUMN,19,Duedate 1 COLUMN,20,Payment Text COLUMN,21,Costs COLUMN,22,Purch Tax COLUMN,23,Orders Grossvalue COLUMN,24,Tax Share COLUMN,25,Discount Amount COLUMN,26,Price Code COLUMN,27,Misc Adds COLUMN,28,Stock COLUMN,29,Place Code COLUMN,30,Internal Code COLUMN,31,Customer Group COLUMN,32,Basis Number COLUMN,33,Mileage COLUMN,34,Prev Status COLUMN,35,Sales Class Number COLUMN,36,Invoice Disc Perc COLUMN,37,Sales Tax Free COLUMN,38,Title COLUMN,39,Name COLUMN,40,Street Addr COLUMN,41,Addr 2 COLUMN,42,Zipcode COLUMN,43,Mail Addr COLUMN,44,Discount Limit COLUMN,45,Reference Number COLUMN,46,Expected Order Tim COLUMN,47,Model Text COLUMN,48,Workshop Pricecode COLUMN,49,Split Counter COLUMN,50,Arrival Time COLUMN,51,Arrival Date COLUMN,52,End Date COLUMN,53,End Time COLUMN,54,Fac Model Code S COLUMN,55,Make Cd COLUMN,56,Year Model COLUMN,57,Transfer Make Cd COLUMN,58,Chassis Number COLUMN,59,Workshop Team COLUMN,60,Commission Salesman COLUMN,61,Actual Inv Date Time COLUMN,62,Unique Ident COLUMN,63,Order Number_ori2 COLUMN,64,Line Number COLUMN,65,Order Linetype COLUMN,66,Reduction Code COLUMN,67,Reduction Amount COLUMN,68,Mechanic Code COLUMN,69,Salesman COLUMN,70,Discount COLUMN,71,Stdprice COLUMN,72,Lines Net Value COLUMN,73,Prod Code COLUMN,74,Make Cd COLUMN,75,Product Group COLUMN,76,Prod Name COLUMN,77,Order Quantity COLUMN,78,Delivery Quantity COLUMN,79,Line Costs COLUMN,80,Repair Code COLUMN,81,Repair Group COLUMN,82,Repair Name COLUMN,83,Used Time COLUMN,84,Est Time COLUMN,85,Inv Time COLUMN,86,Used Time Int COLUMN,87,Est Time Int COLUMN,88,Inv Time Int COLUMN,89,Make Time Unit COLUMN,90,Unique Ident COLUMN,91,Stat Code COLUMN,92,Stat Specify COLUMN,93,Department Type Id COLUMN,94,Description COLUMN,95,Seller Code COLUMN,96,Sel Name COLUMN,97,Sel Department COLUMN,98,Sel First Name COLUMN,99,Sel Family Name COLUMN,100,Customer Group COLUMN,101,Cust Group Specify COLUMN,102,Seller Code COLUMN,103,Sel Name_Monteur COLUMN,104,Sel Department COLUMN,105,Sel First Name_Monteur COLUMN,106,Sel Family Name_Monteur COLUMN,107,Repair Group COLUMN,108,Make Cd COLUMN,109,Repair Grp Specify COLUMN,110,Hauptbetrieb COLUMN,111,Standort COLUMN,112,Marke COLUMN,113,Kostenstelle COLUMN,114,Kundenart COLUMN,115,Umsatzart COLUMN,116,Geschäftsart COLUMN,117,Serviceberater COLUMN,118,Licence Id COLUMN,119,Monteur COLUMN,120,Auftragsart COLUMN,121,Auftragsstatus COLUMN,122,Umsatz Teile Service COLUMN,123,Umsatz Lohn COLUMN,124,Umsatz Teile (nur Teile) COLUMN,125,Status_1 COLUMN,126,verk. Stunden_vor_Split COLUMN,127,Soll-Stunden (Auftrag) COLUMN,128,benutzte Zeit (Auftrag)_vor_Split COLUMN,129,Umsatz Sonstiges COLUMN,130,verk. AW_vor_Split COLUMN,131,Kunde COLUMN,132,Soll AW COLUMN,133,benutzte AW_vor_Split COLUMN,134,verk. Stunden COLUMN,135,benutzte Zeit (Auftrag) COLUMN,136,AW_Faktor COLUMN,137,Global Make Cd COLUMN,138,Einsatz Teile Service COLUMN,139,Einsatz Teile (nur Teile) COLUMN,140,Description COLUMN,141,Fabrikat_ori COLUMN,142,Model COLUMN,143,Fahrzeug COLUMN,144,Einsatz Teile Service_vor_Split COLUMN,145,Einsatz Teile (nur Teile)_vor_Split COLUMN,146,Rechnung/Gutschrift COLUMN,147,DB COLUMN,148,DB 1 Teile SC COLUMN,149,DB 1 Teile T COLUMN,150,VK < EK COLUMN,151,Mandant COLUMN,152,Order Number COLUMN,153,Order Number Rg_Ausgang COLUMN,154,verk. AW COLUMN,155,benutzte AW COLUMN,156,Fabrikat COLUMN,157,First Reg Date COLUMN,158,Fahrzeugalter_Tage COLUMN,159,Fahrzeugalter_Jahr COLUMN,160,FZG-Altersstaffel COLUMN,161,Nachlass COLUMN,162,Rabatt Teile COLUMN,163,Rabatt Lohn COLUMN,164,Preiscode