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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_3
DATASOURCENAME,D:\Gaps\Portal\System\IQD\NW\NW_GW_VK_Pos_op03.imr
TITLE,NW_GW_VK_Pos_op03.imr
BEGIN SQL
select T1."ORDER_NUMBER" as c1,
	   T1."DEBIT_ACCOUNT" as c2,
	   T1."STATUS" as c3,
	   T1."STATE_KEY_DATE" as c4,
	   T1."INVOICE_NUMBER" as c5,
	   T1."DEPARTMENT" as c6,
	   T1."HANDLER" as c7,
	   T1."SALESMAN" as c8,
	   T1."ORDER_DATE" as c9,
	   T1."DELIVERY_DATE" as c10,
	   T1."INVOICE_DATE" as c11,
	   T1."FLEET_OWNER" as c12,
	   T1."OPTION_SPECIFIC" as c13,
	   T1."TITLE" as c14,
	   T1."NAME" as c15,
	   T1."VEHICLE_SOLD" as c16,
	   T1."VEHICLE_PURCH" as c17,
	   T1."CUSTOMER_GROUP" as c18,
	   T1."STOCK" as c19,
	   T1."VEH_ORDER_TYPE" as c20,
	   T1."CONTRACT_DATE" as c21,
	   T1."FAC_MODEL_CODE_L" as c22,
	   T2."LINE_NUMBER" as c23,
	   T2."UNIT_NUMBER" as c24,
	   T2."PERSON_CODE" as c25,
	   T2."VEHICLE_TYPE_VO" as c26,
	   T2."REGISTER_NUMBER" as c27,
	   T2."CHASSIS_NUMBER" as c28,
	   T2."LINE_TYPE_VEH" as c29,
	   T2."ACCOUNTING_CODE" as c30,
	   '1' as c31,
	   '03' as c32,
	   T3."SELLER_CODE" as c33,
	   T3."SEL_NAME" as c34,
	   T3."SEL_NAME" || ' - ' || T3."SELLER_CODE" as c35,
	   T4."ECC_STATUS" as c36,
	   T4."VEHICLE_TYPE" as c37,
	   T4."BUD_SALE_DET" as c38,
	   T4."BUD_PURCH_PRICE" as c39,
	   T4."BUD_REG_FEE" as c40,
	   T4."BUD_DEDUCT_FEE" as c41,
	   T4."BUD_COST" as c42,
	   T4."PREV_OWNER" as c43,
	   T4."OWNER" as c44,
	   T4."PURCH_PRICE_UNIT" as c45,
	   T4."COST_UNIT" as c46,
	   T4."SALES_PRICE_UNIT" as c47,
	   T5."CAR_GROUP" as c48,
	   T5."CAR_STATUS" as c49,
	   T5."ORIG_INV_DATE" as c50,
	   T5."MAKE" as c51,
	   T5."MAKE_CD" as c52,
	   T5."MAKE_CD" as c53,
	   T6."MODEL_LINE" as c54,
	   T6."MOD_LIN_SPECIFY" as c55,
	   T6."MOD_LIN_SPECIFY" as c56,
	   T5."MODEL_TEXT" as c57,
	   T5."MODEL_TEXT" as c58,
	   T2."CHASSIS_NUMBER" as c59,
	   T1."DELIVERY_ACCOUNT" as c60,
	   T1."DELIVERY_ACCOUNT" || ' - ' || T1."NAME" as c61,
	   T7."FLEET_OWNER_CODE" as c62,
	   T7."SPECIFY" as c63,
	   (T1."FLEET_OWNER" || ' - ' || T7."SPECIFY") as c64,
	   T8."TRANSACTION_CODE" as c65,
	   T8."ORDERS_GROSSVALUE" as c66,
	   T8."DISCOUNT" as c67,
	   T9."TRANSACTION_CODE" as c68,
	   T9."SPECIFY" as c69,
	   CASE WHEN ((T8."TRANSACTION_CODE" = '10') and (T2."LINE_TYPE_VEH" <> 'Z')) THEN (((T1."FLEET_OWNER" || ' - ' || T7."SPECIFY"))) ELSE null END as c70,
	   T1."PMT_TERM" as c71,
	   T1."PAYMENT_TEXT" as c72,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c73,
	   CASE WHEN ((T8."TRANSACTION_CODE" = '10') and (T2."LINE_TYPE_VEH" <> 'Z')) THEN ((T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT")) ELSE null END as c74,
	   T5."COLOUR_CF" as c75,
	   T5."COLOUR_CODE" as c76,
	   T5."COLOUR_CODE" || ' - ' || T5."COLOUR_CF" as c77,
	   CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END as c78,
	   CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END as c79,
	   CASE WHEN (((((T8."TRANSACTION_CODE" = '10') and (T1."STATUS" = 'FR')) and (T2."LINE_TYPE_VEH" <> 'Z')) and ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen')) and ((CASE WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) >= 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN (T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) < 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN ((T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") * -1) ELSE null END) > 0)) THEN (1) WHEN (((((T8."TRANSACTION_CODE" = '10') and (T1."STATUS" = 'FG')) and (T2."LINE_TYPE_VEH" <> 'Z')) and ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen')) and ((CASE WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) >= 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN (T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) < 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN ((T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") * -1) ELSE null END) < 0)) THEN (-1) WHEN ((((T8."TRANSACTION_CODE" = '10') and (T1."STATUS" = 'FR')) and (T2."LINE_TYPE_VEH" <> 'Z')) and ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen')) THEN (1) WHEN ((((T8."TRANSACTION_CODE" = '10') and (T1."STATUS" = 'FG')) and (T2."LINE_TYPE_VEH" <> 'Z')) and ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen')) THEN (-1) ELSE null END as c80,
	   CASE WHEN (T8."TRANSACTION_CODE" IN ('14')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END as c81,
	   0 as c82,
	   CASE WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) >= 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN (T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") WHEN (((T8."TRANSACTION_CODE" = '10') and ((CASE WHEN ((((T8."TRANSACTION_CODE" = '10') or (T8."TRANSACTION_CODE" = '24')) or (T8."TRANSACTION_CODE" = '12')) or (T8."TRANSACTION_CODE" = '11')) THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END) < 0)) and (T2."LINE_TYPE_VEH" <> 'Z')) THEN ((T4."PURCH_PRICE_UNIT" + T4."COST_UNIT") * -1) ELSE null END as c83,
	   CASE WHEN (T8."TRANSACTION_CODE" = '92') THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END as c84,
	   CASE WHEN (T8."TRANSACTION_CODE" = '93') THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END as c85,
	   CASE WHEN (T8."TRANSACTION_CODE" = '91') THEN (T8."ORDERS_GROSSVALUE" * -1) ELSE null END as c86,
	   T2."UNIT_NUMBER" || ' / ' || T4."OWNER" || ' / ' || (asciiz(extract(YEAR FROM T5."ORIG_INV_DATE"),4) || '-' || asciiz(extract(MONTH FROM T5."ORIG_INV_DATE"),2) || '-' || asciiz(extract(DAY FROM T5."ORIG_INV_DATE"),2)) as c87,
	   T2."UNIT_NUMBER" as c88,
	   CASE WHEN (T4."VEHICLE_TYPE" IN ('N','P','Q')) THEN ('N - Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('V','W','X')) THEN ('V - Vorführwagen') WHEN (T4."VEHICLE_TYPE" IN ('M','Y')) THEN ('M - Mietwagen/Opel Rent') WHEN (T4."VEHICLE_TYPE" IN ('R')) THEN ('R - GW regelbesteuert') WHEN (T4."VEHICLE_TYPE" IN ('D')) THEN ('D - GW §25A UStG') WHEN (T4."VEHICLE_TYPE" IN ('G')) THEN ('G - Geschäftsfahrzeug') ELSE null END as c89,
	   (database()) as c90,
	   T5."MOTOR_CODE" as c91,
	   CASE WHEN (T8."TRANSACTION_CODE" = '13') THEN (T8."ORDERS_GROSSVALUE") ELSE null END as c92,
	   CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END as c93,
	   CASE WHEN ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '10') THEN ('10 - Privatkunde') WHEN ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) IN ('20','30')) THEN ('30 - Großkunde') WHEN ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '12') THEN ('12 - Leasing') WHEN ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '80') THEN ('80 - Händlertausch') WHEN ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = 'GW') THEN ('GW') ELSE ('nicht zuzuordnen') END as c94,
	   CASE WHEN ((substring((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 1 for 1)) = '1') THEN ('Privatkunde') WHEN ((substring((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) 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 (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 1 for 1)) = '8') THEN ('Händlertausch') ELSE ((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END)) END as c95,
	   CASE WHEN (((substring((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '0') or (T4."BOOK_KEEPING_CODE" IN ('DZ100     ','RZ100     ','RG100     ','M0P10     '))) THEN ('Bar') WHEN (((substring((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '1') or (T4."BOOK_KEEPING_CODE" IN ('DZ111     ','RZ111     ','M0P11     '))) THEN ('Finanzierung') WHEN (((substring((CASE WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 5)) THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN (((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (((length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1)) = 4)) THEN ((substring(T2."ACCOUNTING_CODE" from 3 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X','G')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) from 2 for 1)) = '2') or (T4."BOOK_KEEPING_CODE" IN ('DZ112     ','RZ112     ','M0P12     '))) THEN ('Leasing') ELSE ('Zahlart fehlt') END as c96,
	   (rtrim(T2."ACCOUNTING_CODE")) as c97,
	   (length(((rtrim(T2."ACCOUNTING_CODE"))) || 'Z') - 1) as c98
from "deop03"."dbo"."VEH_ORDER_HEADER" T1,
	"deop03"."dbo"."VEH_ORDER_LINE" T2,
	"deop03"."dbo"."vPP43" T3,
	"deop03"."dbo"."UNIT_FILE" T4,
	"deop03"."dbo"."VEHICLE_v" T5,
	"deop03"."dbo"."vPP5Q" T6,
	"deop03"."dbo"."vPP4K" T7,
	"deop03"."dbo"."UNIT_HISTORY" T8,
	"deop03"."dbo"."vPP5A" T9
where (T1."ORDER_NUMBER" = T2."ORDER_NUMBER") and (T1."SALESMAN" = T3."SELLER_CODE") and (T2."UNIT_NUMBER" = T4."UNIT_NUMBER") and (T4."BASIS_NUMBER" = T5."BASIS_NUMBER") and (T5."MODEL_LINE" = T6."MODEL_LINE") and (T1."FLEET_OWNER" = T7."FLEET_OWNER_CODE") and ((T2."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T2."UNIT_NUMBER" = T8."UNIT_NUMBER")) and (T9."TRANSACTION_CODE" = T8."TRANSACTION_CODE")
and (((((T1."STATUS" IN ('FG','FR')) and (T2."UNIT_NUMBER" <> '/0005/')) and (T4."ECC_STATUS" = '64')) and (not T8."TRANSACTION_CODE" IN ('76','77','25'))) and (T1."INVOICE_DATE" <= TIMESTAMP '2011-12-31 00:00:00.000'))
order by c24 asc,c1 asc

END SQL
COLUMN,0,Order Number
COLUMN,1,Debit Account
COLUMN,2,Status
COLUMN,3,State Key Date
COLUMN,4,Invoice Number
COLUMN,5,Department
COLUMN,6,Handler
COLUMN,7,Salesman
COLUMN,8,Order Date
COLUMN,9,Delivery Date
COLUMN,10,Invoice Date
COLUMN,11,Fleet Owner
COLUMN,12,Option Specific
COLUMN,13,Title
COLUMN,14,Name
COLUMN,15,Vehicle Sold
COLUMN,16,Vehicle Purch
COLUMN,17,Customer Group
COLUMN,18,Stock
COLUMN,19,Veh Order Type
COLUMN,20,Contract Date
COLUMN,21,Fac Model Code L
COLUMN,22,Line Number
COLUMN,23,Unit Number
COLUMN,24,Person Code
COLUMN,25,Vehicle Type Vo
COLUMN,26,Register Number
COLUMN,27,Chassis Number
COLUMN,28,Line Type Veh
COLUMN,29,Accounting Code
COLUMN,30,Hauptbetrieb
COLUMN,31,Standort
COLUMN,32,Seller Code
COLUMN,33,Sel Name
COLUMN,34,Verkäufer
COLUMN,35,Ecc Status
COLUMN,36,Vehicle Type
COLUMN,37,Bud Sale Det
COLUMN,38,Bud Purch Price
COLUMN,39,Bud Reg Fee
COLUMN,40,Bud Deduct Fee
COLUMN,41,Bud Cost
COLUMN,42,Prev Owner
COLUMN,43,Owner
COLUMN,44,Purch Price Unit
COLUMN,45,Cost Unit
COLUMN,46,Sales Price Unit
COLUMN,47,Car Group
COLUMN,48,Car Status
COLUMN,49,Orig Inv Date
COLUMN,50,Make
COLUMN,51,Make Cd
COLUMN,52,Fabrikat
COLUMN,53,Model Line
COLUMN,54,Mod Lin Specify
COLUMN,55,Model
COLUMN,56,Model Text
COLUMN,57,Modellbez
COLUMN,58,Fahrgestellnr
COLUMN,59,Delivery Account
COLUMN,60,Kunde
COLUMN,61,Fleet Owner Code
COLUMN,62,Specify
COLUMN,63,Kundenart
COLUMN,64,Transaction Code
COLUMN,65,Orders Grossvalue
COLUMN,66,Discount
COLUMN,67,Transaction Code
COLUMN,68,Specify
COLUMN,69,Kundenart_neu
COLUMN,70,Pmt Term
COLUMN,71,Payment Text
COLUMN,72,Geschäftsart
COLUMN,73,Geschäftsart_neu
COLUMN,74,Colour Cf
COLUMN,75,Colour Code
COLUMN,76,Farbe
COLUMN,77,Fahrzeugart
COLUMN,78,Erlös
COLUMN,79,Menge
COLUMN,80,Ums. Fracht und Überf.
COLUMN,81,Nachlass
COLUMN,82,Einsatz
COLUMN,83,Sonst. Erl. MwSt.pfl
COLUMN,84,Sonst. Erl. steuerfrei
COLUMN,85,Car Garantie
COLUMN,86,FZG_1
COLUMN,87,FZG
COLUMN,88,Fahrzeugtyp
COLUMN,89,Mandant
COLUMN,90,Motor Code
COLUMN,91,OSP Provisionen
COLUMN,92,Buchungscode_ori
COLUMN,93,Buchungscode
COLUMN,94,Abnehmergruppe
COLUMN,95,Zahlart
COLUMN,96,Buchungscode_Stellen
COLUMN,97,Buchungscode_Stellen1