GAPS/Portal/System/IQD/NW/NW_GW_VK_2_Buchungscode.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_SKR51
DATASOURCENAME,D:\Gaps\Portal\System\IQD\NW\NW_GW_VK_2_Buchungscode.imr
TITLE,NW_GW_VK_2_Buchungscode.imr
BEGIN SQL
select T1."UNIT_NUMBER" as c1,
	   T1."BASIS_NUMBER" as c2,
	   T1."ECC_STATUS" as c3,
	   T1."VEHICLE_TYPE" as c4,
	   T2."VEHICLE_TYPE" as c5,
	   T2."VEHICLE_TYPE_TEXT" as c6,
	   T1."CAR_CREATION_DATE" as c7,
	   T1."DEPRECIATION" as c8,
	   T1."BOOK_KEEPING_CODE" as c9,
	   T3."BOOK_KEEPING_CODE" as c10,
	   T3."SPECIFY" as c11,
	   T1."MILEAGE" as c12,
	   T1."PURCH_DEPARTMENT" as c13,
	   T1."PURCH_SALSMAN_CODE" as c14,
	   T1."PURCH_DATE" as c15,
	   T1."RECEPTION_DATE" as c16,
	   T1."SALES_DEPARTMENT" as c17,
	   T4."DEPARTMENT_TYPE_ID" as c18,
	   T4."DESCRIPTION" as c19,
	   T1."SALE_SALESMAN" as c20,
	   T5."SELLER_CODE" as c21,
	   T5."SEL_NAME" as c22,
	   T5."SEL_FAMILY_NAME" as c23,
	   T1."SALE_INV_NUMBER" as c24,
	   T1."SALE_DATE" as c25,
	   T1."BUD_SALE_DET" as c26,
	   T1."BUD_PURCH_PRICE" as c27,
	   T1."BUD_REG_FEE" as c28,
	   T1."BUD_DEDUCT_FEE" as c29,
	   T1."BUD_COST" as c30,
	   T1."PURCH_PRICE_UNIT" as c31,
	   T1."COST_UNIT" as c32,
	   T1."SALES_PRICE_UNIT" as c33,
	   T1."REG_FEE" as c34,
	   T1."STOCK_DAYS" as c35,
	   T1."BUYING_ORDER_NO" as c36,
	   T1."BUYING_ORDER_DATE" as c37,
	   T1."PA_NUMBER" as c38,
	   T1."LOCATION_CODE" as c39,
	   T1."OWNER" as c40,
	   T1."PREV_OWNER" as c41,
	   T1."ACCOUNT_PURCHASE" as c42,
	   T1."ACCOUNT_SALES" as c43,
	   T6."CUSTOMER_NUMBER" as c44,
	   T6."NAME" as c45,
	   T6."CUSTOMER_GROUP" as c46,
	   T7."CUSTOMER_GROUP" as c47,
	   T7."CUST_GROUP_SPECIFY" as c48,
	   T1."ORDER_ARRIVAL_DATE" as c49,
	   T1."ORDER_CONFIRM_DATE" as c50,
	   T1."CONTRACT_DATE" as c51,
	   T8."REGISTER_NUMBER" as c52,
	   T8."CHASSIS_NUMBER" as c53,
	   T8."OWNER_CODE" as c54,
	   T8."FIRST_REG_DATE" as c55,
	   T8."LATEST_REG_DATE" as c56,
	   T8."ARRIVAL_DATE" as c57,
	   T8."SALESMAN_BUY" as c58,
	   T8."SALESMAN_SALE" as c59,
	   T8."MODEL_TEXT" as c60,
	   T8."COLOUR_CF" as c61,
	   T8."MODEL_LINE" as c62,
	   T9."MODEL_LINE" as c63,
	   T9."MOD_LIN_SPECIFY" as c64,
	   T8."WORKSHOP_MODEL" as c65,
	   T8."MILEAGE" as c66,
	   T8."MOTOR_CODE" as c67,
	   T8."DRIVE_CODE" as c68,
	   T8."COLOUR_CODE" as c69,
	   T8."MAKE" as c70,
	   T8."MAKE_CD" as c71,
	   T10."GLOBAL_MAKE_CD" as c72,
	   T10."DESCRIPTION" as c73,
	   T8."ORIG_INV_DATE" as c74,
	   T8."LATEST_INV_DATE" as c75,
	   T1."SALE_DATE" as c76,
	   '1' as c77,
	   (od_left(T4."DEPARTMENT_TYPE_ID",2)) as c78,
	   CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN (T5."SEL_NAME" || ' - ' || T11."ACCOUNT_NO") ELSE (T5."SEL_NAME" || ' - ' || T5."SELLER_CODE") END as c79,
	   T10."DESCRIPTION" as c80,
	   T9."MOD_LIN_SPECIFY" as c81,
	   T8."MODEL_TEXT" as c82,
	   T8."CHASSIS_NUMBER" as c83,
	   CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END as c84,
	   T1."VEHICLE_TYPE" || ' - ' || T2."VEHICLE_TYPE_TEXT" as c85,
	   T6."CUSTOMER_NUMBER" || '- ' || T6."NAME" as c86,
	   T6."CUSTOMER_GROUP" || ' - ' || T7."CUST_GROUP_SPECIFY" as c87,
	   '' as c88,
	   T8."COLOUR_CODE" || ' - ' || T8."COLOUR_CF" as c89,
	   CASE WHEN (T1."ECC_STATUS" = '21') THEN ('21 - Vorlauf') WHEN (T1."ECC_STATUS" = '25') THEN ('25 - für Kd best. FZG') WHEN (T1."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T1."ECC_STATUS" = '44') THEN ('44 - am Hof nicht fakt.') WHEN (T1."ECC_STATUS" = '64') THEN ('64 - verkauft') ELSE null END as c90,
	   (od_left(T1."UNIT_NUMBER",7)) || ' / ' || (od_left(T1."BASIS_NUMBER",8)) || ' / ' || T8."CHASSIS_NUMBER" as c91,
	   CASE WHEN (T8."ORIG_INV_DATE" IS NOT NULL) THEN ((od_left(T1."UNIT_NUMBER",7)) || ' / ' || (od_left(T1."BASIS_NUMBER",8)) || ' / ' || T1."OWNER" || ' / ' || (asciiz(extract(YEAR FROM T8."ORIG_INV_DATE"),4) || '-' || asciiz(extract(MONTH FROM T8."ORIG_INV_DATE"),2) || '-' || asciiz(extract(DAY FROM T8."ORIG_INV_DATE"),2))) ELSE ((od_left(T1."UNIT_NUMBER",7)) || ' / ' || (od_left(T1."BASIS_NUMBER",8)) || ' / ' || T1."OWNER") END as c92,
	   ((od_left(T1."UNIT_NUMBER",7)) || ' / ' || (od_left(T1."BASIS_NUMBER",8)) || ' / ' || T8."CHASSIS_NUMBER") || ' / ' || T1."VEHICLE_TYPE" as c93,
	   T12."SEL_NAME" as c94,
	   T12."SEL_NAME" as c95,
	   T1."PREV_OWNER" as c96,
	   CASE WHEN (T12."SEL_NAME" IS NULL) THEN (' / ' || ' / ' || T1."PREV_OWNER") ELSE (T12."SEL_NAME" || ' / ' || T1."PREV_OWNER") END as c97,
	   T8."MODEL_TEXT" as c98,
	   (CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN (T5."SEL_NAME" || ' - ' || T11."ACCOUNT_NO") ELSE (T5."SEL_NAME" || ' - ' || T5."SELLER_CODE") END) || ' / ' || (T6."CUSTOMER_NUMBER" || '- ' || T6."NAME") as c99,
	   T13."UNIT_NUMBER" as c100,
	   T13."LINE_TYPE" as c101,
	   T13."LINE_NO" as c102,
	   T13."TRANSACT_DATE" as c103,
	   T13."HANDLER" as c104,
	   T13."PROGRAM" as c105,
	   T13."FUNCTION_CODE" as c106,
	   T13."DOCUMENT_DATE" as c107,
	   T13."DOCUMENT_NR" as c108,
	   T13."ORDER_DATE" as c109,
	   T13."ORDER_NUMBER" as c110,
	   T13."TRANSACTION_CODE" as c111,
	   T14."TRANSACTION_CODE" as c112,
	   T14."SPECIFY" as c113,
	   T13."REFERENCE" as c114,
	   T13."COSTS" as c115,
	   T13."ORDERS_GROSSVALUE" as c116,
	   T13."DISCOUNT" as c117,
	   T13."CLASSIFICATION_DTE" as c118,
	   T13."BOOK_KEEPING_CODE" as c119,
	   T13."DEPARTMENT" as c120,
	   T13."DESTINATION" as c121,
	   T13."INT_VOUCHER_NO" as c122,
	   CASE WHEN ((T13."TRANSACTION_CODE" IN ('10','12','24')) or ((T13."TRANSACTION_CODE" IN ('11')) and (T10."DESCRIPTION" = 'Chevrolet'))) THEN (T13."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c123,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('15','16','90','91','92','93','94','72')) THEN (T13."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c124,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('14')) THEN (T13."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c125,
	   CASE WHEN ((T13."TRANSACTION_CODE" = '11') and (T10."DESCRIPTION" <> 'Chevrolet')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c126,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('13')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c127,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('25','26','27','28','29','30','31','32','71','73')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c128,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('33','34','37','39','40','41','47','49')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c129,
	   (CASE WHEN (T13."TRANSACTION_CODE" IN ('25','26','27','28','29','30','31','32','71','73')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END) + (CASE WHEN (T13."TRANSACTION_CODE" IN ('33','34','37','39','40','41','47','49')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END) as c130,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('26','27','28','29','31','32')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c131,
	   CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM (now()) - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM (now()) - T1."RECEPTION_DATE"))) END as c132,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('70')) THEN (T13."ORDERS_GROSSVALUE") ELSE (0) END as c133,
	   T11."BIRTH_PLACE" as c134,
	   T11."BANK_NAME" as c135,
	   (rtrim(T1."BOOK_KEEPING_CODE")) as c136,
	   (length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1) as c137,
	   CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END as c138,
	   CASE WHEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '10') THEN ('10 - Privatkunde') WHEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) IN ('20','30','32','33')) THEN ('30 - Großkunde') WHEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '12') THEN ('12 - Leasing') WHEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '80') THEN ('80 - Händlertausch') WHEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = 'GW') THEN ('GW') ELSE ('nicht zuzuordnen') END as c139,
	   CASE WHEN ((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 1 for 1)) = '1') THEN ('Privatkunde') WHEN ((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 1 for 1)) IN ('2','3')) THEN ('Großkunde') WHEN ((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 1 for 1)) = '8') THEN ('Händlertausch') ELSE ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END)) END as c140,
	   CASE WHEN ((((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '0') or ((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '3')) or (T1."BOOK_KEEPING_CODE" IN ('DZ100     ','RZ100     ','RZ120     ','RZ130     ','RG100     ','M0P10     ','EE100     ','EZ100     ','M0P13     ','RM100     ','DE100     ','RM113     ','M6P10     '))) THEN ('Bar') WHEN (((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '1') or (T1."BOOK_KEEPING_CODE" IN ('DZ111     ','RZ111     ','RZ121     ','RZ131     ','M0P11     ','RG111     ','RM111     '))) THEN ('Finanzierung') WHEN (((substring((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T1."BOOK_KEEPING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T1."BOOK_KEEPING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '2') or (T1."BOOK_KEEPING_CODE" IN ('DZ112     ','RZ112     ','RZ122     ','RZ132     ','M0P12     ','RG112     ','RM112     '))) THEN ('Leasing') ELSE ('Zahlart fehlt') END as c141,
	   T15."FLEET_CODE_GMD" as c142,
	   T15."DRIVE_GMD" as c143,
	   CASE WHEN (T15."FLEET_CODE_GMD" = '0922     ') THEN ('Opel Rent-Einstellung') WHEN (T15."DRIVE_GMD" = 'L') THEN ('VFW-Einstellung') ELSE null END as c144,
	   T1."LOCATION_CODE" as c145,
	   CASE WHEN (((T1."VEHICLE_TYPE" IN ('V','T')) and ((extract(DAY FROM T1."SALE_DATE" - T8."FIRST_REG_DATE")) >= 366)) and (T8."OPTIONAL_DATE_1" = TIMESTAMP '1800-01-01 00:00:00.000')) THEN ('Gebrauchtwagen') ELSE ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END)) END as c146,
	   CASE WHEN ((((CASE WHEN (((T1."VEHICLE_TYPE" IN ('V','T')) and ((extract(DAY FROM T1."SALE_DATE" - T8."FIRST_REG_DATE")) >= 366)) and (T8."OPTIONAL_DATE_1" = TIMESTAMP '1800-01-01 00:00:00.000')) THEN ('Gebrauchtwagen') ELSE ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END)) END) <> 'Gebrauchtwagen') and ((CASE WHEN (T15."FLEET_CODE_GMD" = '0922     ') THEN ('Opel Rent-Einstellung') WHEN (T15."DRIVE_GMD" = 'L') THEN ('VFW-Einstellung') ELSE null END) = 'VFW-Einstellung')) and (T8."LATEST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000')) THEN ('V - Vorführwagen') WHEN (T1."BOOK_KEEPING_CODE" LIKE 'RZ12%') THEN ('R - Jahreswagen') ELSE ((T1."VEHICLE_TYPE" || ' - ' || T2."VEHICLE_TYPE_TEXT")) END as c147,
	   CASE WHEN (T13."TRANSACTION_CODE" IN ('79')) THEN (T13."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c148,
	   T1."DELIVERY_DATE_VEH" as c149,
	   CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END as c150,
	   CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END as c151,
	   CASE WHEN (((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL)) THEN (T1."DELIVERY_DATE_VEH") ELSE (T1."SALE_DATE") END as c152,
	   (substring(T1."BOOK_KEEPING_CODE" from 4 for 2)) as c153,
	   CASE WHEN ((((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) BETWEEN '10' AND '12') and (T15."FLEET_CODE_GMD" = '         ')) THEN ('Privat') WHEN ((((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) BETWEEN '10' AND '12') and (T15."FLEET_CODE_GMD" <> '         ')) THEN ('KFP') WHEN ((((substring(T1."BOOK_KEEPING_CODE" from 4 for 2))) BETWEEN '30' AND '33') and (T15."FLEET_CODE_GMD" <> '         ')) THEN ('Großkunden') ELSE ('keine Zuordnung') END as c154,
	   T8."OPTIONAL_DATE_1" as c155,
	   CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '1')) THEN ('Privat') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '2')) THEN ('KFP') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '3')) THEN ('GK') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '4')) THEN ('Verband') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '8')) THEN ('Händler') WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END as c156,
	   CASE WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) IN ('0','3')) THEN ('Bar') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '1') THEN ('Finanzierung') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '2') THEN ('Leasing') ELSE null END as c157,
	   CASE WHEN (((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '1')) THEN ('Privat') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '2')) THEN ('KFP') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '3')) THEN ('GK') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '4')) THEN ('Verband') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '8')) THEN ('Händler') WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) IS NOT NULL) and ((CASE WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) IN ('0','3')) THEN ('Bar') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '1') THEN ('Finanzierung') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '2') THEN ('Leasing') ELSE null END) IS NOT NULL)) THEN ((CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '1')) THEN ('Privat') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '2')) THEN ('KFP') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '3')) THEN ('GK') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '4')) THEN ('Verband') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '8')) THEN ('Händler') WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) || ' - ' || (CASE WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) IN ('0','3')) THEN ('Bar') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '1') THEN ('Finanzierung') WHEN ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 5 for 1)) = '2') THEN ('Leasing') ELSE null END)) ELSE null END as c158,
	   T8."USER_CODE" as c159,
	   T16."LAST_NAME" as c160,
	   T16."FIRST_NAME" as c161,
	   CASE WHEN (T16."FIRST_NAME" IS NULL) THEN (T16."LAST_NAME") ELSE (T16."LAST_NAME" || ', ' || T16."FIRST_NAME") END as c162,
	   CASE WHEN (T16."ADDR_2" IS NULL) THEN ('-') ELSE (T16."ADDR_2") END as c163,
	   CASE WHEN (T16."ZIPCODE" IS NULL) THEN ('-') ELSE (T16."ZIPCODE") END as c164,
	   CASE WHEN (T16."MAIL_ADDR" IS NULL) THEN ('-') ELSE (T16."MAIL_ADDR") END as c165,
	   (CASE WHEN (T16."ADDR_2" IS NULL) THEN ('-') ELSE (T16."ADDR_2") END) || ', ' || (truncate((CASE WHEN (T16."ZIPCODE" IS NULL) THEN ('-') ELSE (T16."ZIPCODE") END))) || ' ' || (CASE WHEN (T16."MAIL_ADDR" IS NULL) THEN ('-') ELSE (T16."MAIL_ADDR") END) as c166,
	   CASE WHEN (T16."MOBILE_PHONE" IS NULL) THEN ('(-)') ELSE ('(' || T16."MOBILE_PHONE" || ')') END as c167,
	   CASE WHEN (T16."PHONE_1" IS NULL) THEN ('(-)') ELSE ('(' || T16."PHONE_1" || ')') END as c168,
	   CASE WHEN (T16."TELEFAX_NUMBER" IS NULL) THEN ('(-)') ELSE ('(' || T16."TELEFAX_NUMBER" || ')') END as c169,
	   (CASE WHEN (T16."MOBILE_PHONE" IS NULL) THEN ('(-)') ELSE ('(' || T16."MOBILE_PHONE" || ')') END) || ' - ' || (CASE WHEN (T16."PHONE_1" IS NULL) THEN ('(-)') ELSE ('(' || T16."PHONE_1" || ')') END) || ' - ' || (CASE WHEN (T16."TELEFAX_NUMBER" IS NULL) THEN ('(-)') ELSE ('(' || T16."TELEFAX_NUMBER" || ')') END) as c170,
	   CASE WHEN (T16."DECLARATION_STATE" IS NULL) THEN (' - ') ELSE (T16."DECLARATION_STATE") END as c171,
	   (CASE WHEN (T16."FIRST_NAME" IS NULL) THEN (T16."LAST_NAME") ELSE (T16."LAST_NAME" || ', ' || T16."FIRST_NAME") END) || ' / ' || ((CASE WHEN (T16."ADDR_2" IS NULL) THEN ('-') ELSE (T16."ADDR_2") END) || ', ' || (truncate((CASE WHEN (T16."ZIPCODE" IS NULL) THEN ('-') ELSE (T16."ZIPCODE") END))) || ' ' || (CASE WHEN (T16."MAIL_ADDR" IS NULL) THEN ('-') ELSE (T16."MAIL_ADDR") END)) || ' / ' || ((CASE WHEN (T16."MOBILE_PHONE" IS NULL) THEN ('(-)') ELSE ('(' || T16."MOBILE_PHONE" || ')') END) || ' - ' || (CASE WHEN (T16."PHONE_1" IS NULL) THEN ('(-)') ELSE ('(' || T16."PHONE_1" || ')') END) || ' - ' || (CASE WHEN (T16."TELEFAX_NUMBER" IS NULL) THEN ('(-)') ELSE ('(' || T16."TELEFAX_NUMBER" || ')') END)) || ' / ' || (CASE WHEN (T16."DECLARATION_STATE" IS NULL) THEN (' - ') ELSE (T16."DECLARATION_STATE") END) as c172,
	   CASE WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '10')) THEN ('10 Privat Bar') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '11')) THEN ('11 Privat Fin.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '12')) THEN ('12 Privat Leas.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '20')) THEN (' 20 KFP Bar') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '21')) THEN (' 21 KFP Fin.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '22')) THEN (' 22 KFP Leas.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '30')) THEN ('30 Großkunden Bar') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '31')) THEN ('31 Großkunden Fin.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '32')) THEN ('32 Großkunden Leas.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '40')) THEN ('40 Verbände Bar') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '41')) THEN ('41 Verbände Fin.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 2)) = '42')) THEN ('42 Verbände Leas.') WHEN (((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and ((substring(((rtrim(T1."BOOK_KEEPING_CODE"))) from 4 for 1)) = '8')) THEN ('Händler') WHEN ((CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','B','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G','M')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END as c173,
	   T15."FLEET_CODE_GMD" as c174,
	   (od_left(T1."UNIT_NUMBER",7)) || ' / ' || (od_left(T1."BASIS_NUMBER",8)) as c175,
	   (asciiz(extract(YEAR FROM (CASE WHEN (((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL)) THEN (T1."DELIVERY_DATE_VEH") ELSE (T1."SALE_DATE") END)),4) || '-' || asciiz(extract(MONTH FROM (CASE WHEN (((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL)) THEN (T1."DELIVERY_DATE_VEH") ELSE (T1."SALE_DATE") END)),2) || '-' || asciiz(extract(DAY FROM (CASE WHEN (((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL)) THEN (T1."DELIVERY_DATE_VEH") ELSE (T1."SALE_DATE") END)),2)) || ' / ' || (asciiz(extract(YEAR FROM T8."FIRST_REG_DATE"),4) || '-' || asciiz(extract(MONTH FROM T8."FIRST_REG_DATE"),2) || '-' || asciiz(extract(DAY FROM T8."FIRST_REG_DATE"),2)) as c176,
	   CASE WHEN (T15."FLEET_CODE_GMD" IS NOT NULL) THEN (T5."SELLER_CODE" || ' / ' || (od_left(T1."BOOK_KEEPING_CODE",5)) || ' / ' || T15."FLEET_CODE_GMD") ELSE (T5."SELLER_CODE" || ' / ' || T1."BOOK_KEEPING_CODE") END as c177,
	   CASE WHEN ((T1."PREV_OWNER" IS NOT NULL) or (T1."PREV_OWNER" <> '')) THEN (T1."PREV_OWNER") ELSE (' / ') END as c178,
	   T17."LEASING_RECIEPIENT" as c179,
	   T17."LEASING_RECIEPIENT" as c180,
	   T18."FIRST_NAME" as c181,
	   T18."LAST_NAME" as c182,
	   T18."PERSON_CODE" as c183,
	   T18."LAST_NAME" || ', ' || T18."FIRST_NAME" as c184,
	   CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN ((T18."LAST_NAME" || ', ' || T18."FIRST_NAME") || ' - ' || T11."ACCOUNT_NO") ELSE ((T18."LAST_NAME" || ', ' || T18."FIRST_NAME") || ' - ' || T1."SALE_SALESMAN") END as c185,
	   CASE WHEN ((CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN (T5."SEL_NAME" || ' - ' || T11."ACCOUNT_NO") ELSE (T5."SEL_NAME" || ' - ' || T5."SELLER_CODE") END) IS NULL) THEN ((CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN ((T18."LAST_NAME" || ', ' || T18."FIRST_NAME") || ' - ' || T11."ACCOUNT_NO") ELSE ((T18."LAST_NAME" || ', ' || T18."FIRST_NAME") || ' - ' || T1."SALE_SALESMAN") END)) ELSE ((CASE WHEN (T11."ACCOUNT_NO" <> ' ') THEN (T5."SEL_NAME" || ' - ' || T11."ACCOUNT_NO") ELSE (T5."SEL_NAME" || ' - ' || T5."SELLER_CODE") END)) END as c186,
	   T18."BIRTH_PLACE" as c187,
	   T18."BANK_NAME" as c188
from ((((((((("deop01"."dbo"."UNIT_FILE" T1 left outer join "deop01"."dbo"."vPP5R" T2 on T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE") left outer join "deop01"."dbo"."vPP5M" T3 on T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T4 on T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."vPP43" T5 on T1."SALE_SALESMAN" = T5."SELLER_CODE") left outer join "deop01"."dbo"."PERSON" T11 on T5."SELLER_CODE" = T11."PERSON_CODE") left outer join "deop01"."dbo"."vPP43" T12 on T1."PURCH_SALSMAN_CODE" = T12."SELLER_CODE") left outer join "deop01"."dbo"."GM_DRIVE_ORDER" T15 on T1."BASIS_NUMBER" = T15."VEHICLE_NUMBER_GMD") left outer join "deop01"."dbo"."VEHICLE_DOCKETCARD" T17 on T1."UNIT_NUMBER" = T17."UNIT_NUMBER") left outer join "deop01"."dbo"."PERSON" T18 on ((od_left(T1."SALE_SALESMAN",4)) = (od_left(T18."PERSON_CODE",4))) and (T18."PERSON_CODE" <> ' ')),
	("deop01"."dbo"."CUSTOMER" T6 left outer join "deop01"."dbo"."vPP48" T7 on T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP"),
	((("deop01"."dbo"."VEHICLE" T8 left outer join "deop01"."dbo"."vPP5Q" T9 on (T8."MODEL_LINE" = T9."MODEL_LINE") and (T8."MAKE_CD" = T9."MAKE_CD")) left outer join "deop01"."dbo"."GLOBAL_MAKE" T10 on T8."MAKE_CD" = T10."GLOBAL_MAKE_CD") left outer join "deop01"."dbo"."CUSTOMER" T16 on T16."CUSTOMER_NUMBER" = T8."USER_CODE"),
	("deop01"."dbo"."UNIT_HISTORY" T13 left outer join "deop01"."dbo"."vPP5A" T14 on T13."TRANSACTION_CODE" = T14."TRANSACTION_CODE")
where (T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER") and (T1."BASIS_NUMBER" = T8."BASIS_NUMBER") and (T1."UNIT_NUMBER" = T13."UNIT_NUMBER")
and ((((T1."ECC_STATUS" = '64') and (not T13."TRANSACTION_CODE" IN ('76 ','77 '))) and ((CASE WHEN (((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL)) THEN (T1."DELIVERY_DATE_VEH") ELSE (T1."SALE_DATE") END) >= TIMESTAMP '2020-01-01 00:00:00.000')) and ((CASE WHEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END) = 'raus') THEN ((CASE WHEN (T5."SELLER_CODE" LIKE 'VF%') THEN ('raus') ELSE null END)) ELSE null END) IS NULL))
order by c1 asc

END SQL
COLUMN,0,Unit Number
COLUMN,1,Basis Number
COLUMN,2,Ecc Status
COLUMN,3,Vehicle Type
COLUMN,4,Vehicle Type_vpp5r
COLUMN,5,Vehicle Type Text_vpp5r
COLUMN,6,Car Creation Date
COLUMN,7,Depreciation
COLUMN,8,Book Keeping Code
COLUMN,9,Book Keeping Code_vpp5m
COLUMN,10,Specify_vpp5m
COLUMN,11,Mileage
COLUMN,12,Purch Department
COLUMN,13,Purch Salsman Code
COLUMN,14,Purch Date
COLUMN,15,Reception Date
COLUMN,16,Sales Department
COLUMN,17,Department Type Id_Dep
COLUMN,18,Description_Dep
COLUMN,19,Sale Salesman
COLUMN,20,Seller Code_vpp43
COLUMN,21,Sel Name_vpp43
COLUMN,22,Sel Family Name_vpp43
COLUMN,23,Sale Inv Number
COLUMN,24,Sale Date
COLUMN,25,Bud Sale Det
COLUMN,26,Bud Purch Price
COLUMN,27,Bud Reg Fee
COLUMN,28,Bud Deduct Fee
COLUMN,29,Bud Cost
COLUMN,30,Purch Price Unit
COLUMN,31,Cost Unit
COLUMN,32,Sales Price Unit
COLUMN,33,Reg Fee
COLUMN,34,Stock Days
COLUMN,35,Buying Order No
COLUMN,36,Buying Order Date
COLUMN,37,Pa Number
COLUMN,38,Location Code
COLUMN,39,Owner
COLUMN,40,Prev Owner
COLUMN,41,Account Purchase
COLUMN,42,Account Sales
COLUMN,43,Customer Number_Cust
COLUMN,44,Name_Cust
COLUMN,45,Customer Group_Cust
COLUMN,46,Customer Group_vpp48
COLUMN,47,Cust Group Specify_vpp48
COLUMN,48,Order Arrival Date
COLUMN,49,Order Confirm Date
COLUMN,50,Contract Date
COLUMN,51,Register Number
COLUMN,52,Chassis Number
COLUMN,53,Owner Code
COLUMN,54,First Reg Date
COLUMN,55,Latest Reg Date
COLUMN,56,Arrival Date
COLUMN,57,Salesman Buy
COLUMN,58,Salesman Sale
COLUMN,59,Model Text
COLUMN,60,Colour Cf
COLUMN,61,Model Line
COLUMN,62,Model Line_vpp5q
COLUMN,63,Mod Lin Specify_vpp5q
COLUMN,64,Workshop Model
COLUMN,65,Mileage
COLUMN,66,Motor Code
COLUMN,67,Drive Code
COLUMN,68,Colour Code
COLUMN,69,Make
COLUMN,70,Make Cd
COLUMN,71,Global Make Cd
COLUMN,72,Description_Global_Make
COLUMN,73,Orig Inv Date
COLUMN,74,Latest Inv Date
COLUMN,75,Invoice Date_ori
COLUMN,76,Hauptbetrieb
COLUMN,77,Standort
COLUMN,78,Verkäufer_ori
COLUMN,79,Fabrikat
COLUMN,80,Model
COLUMN,81,Modellbez
COLUMN,82,Fahrgestellnr
COLUMN,83,Fahrzeugart_ori
COLUMN,84,Fahrzeugtyp_ori
COLUMN,85,Kunde
COLUMN,86,Kundenart
COLUMN,87,Geschäftsart
COLUMN,88,Farbe
COLUMN,89,ECC_Status_Text
COLUMN,90,FZG
COLUMN,91,FZG_1
COLUMN,92,FZG_Liste_1
COLUMN,93,Sel Name_EK_vpp43
COLUMN,94,Einkäufer
COLUMN,95,Vorbesitzer
COLUMN,96,FZG_Liste_2
COLUMN,97,FZG_Liste_3
COLUMN,98,FZG_Liste_4
COLUMN,99,Unit Number_Unit_Hist
COLUMN,100,Line Type
COLUMN,101,Line No
COLUMN,102,Transact Date
COLUMN,103,Handler
COLUMN,104,Program
COLUMN,105,Function Code
COLUMN,106,Document Date
COLUMN,107,Document Nr
COLUMN,108,Order Date
COLUMN,109,Order Number
COLUMN,110,Transaction Code
COLUMN,111,Transaction Code_vpp5a
COLUMN,112,Specify_vpp5a
COLUMN,113,Reference
COLUMN,114,Costs
COLUMN,115,Orders Grossvalue
COLUMN,116,Discount
COLUMN,117,Classification Dte
COLUMN,118,Book Keeping Code_
COLUMN,119,Department
COLUMN,120,Destination
COLUMN,121,Int Voucher No
COLUMN,122,Erlös
COLUMN,123,Sonst. Erlöse
COLUMN,124,Ums. Fracht und Überf.
COLUMN,125,Nachlass
COLUMN,126,Provisionen
COLUMN,127,EK Fahrzeug
COLUMN,128,FZG-Kosten
COLUMN,129,Einsatz
COLUMN,130,VK_Hilfen
COLUMN,131,Standtage
COLUMN,132,AFA
COLUMN,133,Birth Place_ori
COLUMN,134,Bank Name_ori
COLUMN,135,Buchungscode_Stellen
COLUMN,136,Buchungscode_Stellen1
COLUMN,137,Buchungscode_ori
COLUMN,138,Buchungscode_ori2
COLUMN,139,Abnehmergruppe
COLUMN,140,Zahlart
COLUMN,141,Fleet Code Gmd
COLUMN,142,Drive Gmd
COLUMN,143,Einstellungsart
COLUMN,144,AOS CODE
COLUMN,145,Fahrzeugart
COLUMN,146,Fahrzeugtyp
COLUMN,147,RWU
COLUMN,148,Delivery Date Veh
COLUMN,149,Filterfeld
COLUMN,150,Filterfeld_1
COLUMN,151,Invoice Date
COLUMN,152,Book Keeping Code_4_5
COLUMN,153,KD-Art_Siebrecht
COLUMN,154,Optional Date 1
COLUMN,155,Buchungscode_1
COLUMN,156,Buchungscode_2
COLUMN,157,Buchungscode
COLUMN,158,User Code
COLUMN,159,User_Last Name
COLUMN,160,User_First Name
COLUMN,161,User_Name
COLUMN,162,User_Addr 2
COLUMN,163,User_Zipcode
COLUMN,164,User_Mail Addr
COLUMN,165,User_Anschrift
COLUMN,166,Mobile Phone
COLUMN,167,User_Phone 1
COLUMN,168,Telefax Number
COLUMN,169,User_Tel
COLUMN,170,Declaration State
COLUMN,171,User_Info
COLUMN,172,Buchungscode_1_1
COLUMN,173,Kundeninfo
COLUMN,174,FZG_BC_1
COLUMN,175,FZG_BC_2
COLUMN,176,FZG_BC_3
COLUMN,177,FZG_BC_4
COLUMN,178,Leasing Reciepient
COLUMN,179,FZG_BC_5
COLUMN,180,First Name_Person_Code
COLUMN,181,Last Name_Person_Code
COLUMN,182,Person Code_Person_Code
COLUMN,183,Name_Person_Code
COLUMN,184,Verkäufer_aus_Person_Code
COLUMN,185,Verkäufer
COLUMN,186,Birth Place
COLUMN,187,Bank Name