select T1."UNIT_NUMBER", case  when substring(T4."DEPARTMENT_TYPE_ID",1,3) = '015' then 'Autofit' else case  when T15."Standort_Name" is null then 'Rahlstedt' else T15."Standort_Name" end  end , case  when substring(T4."DEPARTMENT_TYPE_ID",1,3) = '015' then '05' else case  when T15."Standort_ID" is null then '01' else T15."Standort_ID" end  end , rtrim(T1."UNIT_NUMBER") + ' - ' + rtrim(case  when T15."Standort_Name" is null then 'Rahlstedt' else T15."Standort_Name" end ) + ' - ' + rtrim(case  when T1."VEHICLE_TYPE" is null then T1."VEHICLE_TYPE" else T1."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT" end ) + ' - ' + rtrim(T5."SEL_NAME") + ' - ' + rtrim(rtrim(T6."CUSTOMER_NUMBER") + ' - ' + rtrim(T6."NAME")), ((((case  when (T12."TRANSACTION_CODE" in ('10', '12', '24')) then T12."ORDERS_GROSSVALUE" * -1 else 0 end  + case  when (T12."TRANSACTION_CODE" in ('94', '92', '93', '91')) then T12."ORDERS_GROSSVALUE" * -1 else 0 end ) + case  when (T12."TRANSACTION_CODE" in ('14')) then T12."ORDERS_GROSSVALUE" * -1 else 0 end ) - case  when (T12."TRANSACTION_CODE" in ('11 ', '26')) then T12."ORDERS_GROSSVALUE" else 0 end ) - case  when (T12."TRANSACTION_CODE" in ('13')) then T12."ORDERS_GROSSVALUE" else 0 end ) - (case  when (T12."TRANSACTION_CODE" in ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17')) then T12."ORDERS_GROSSVALUE" else 0 end  + case  when (T12."TRANSACTION_CODE" in ('34', '35', '36', '37 ', '38', '39 ', '40 ', '41 ', '42', '45', '47 ', '48 ', '49 ', '59', '61', '63', '67', '69', '70 ', '75 ')) then T12."ORDERS_GROSSVALUE" else 0 end ), case  when (T12."TRANSACTION_CODE" in ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17')) then T12."ORDERS_GROSSVALUE" else 0 end  + case  when (T12."TRANSACTION_CODE" in ('34', '35', '36', '37 ', '38', '39 ', '40 ', '41 ', '42', '45', '47 ', '48 ', '49 ', '59', '61', '63', '67', '69', '70 ', '75 ')) then T12."ORDERS_GROSSVALUE" else 0 end , case  when T12."TRANSACTION_CODE" in ('14') then T12."ORDERS_GROSSVALUE" * -1 else 0 end , case  when T12."TRANSACTION_CODE" in ('10', '12', '24') then T12."ORDERS_GROSSVALUE" * -1 else 0 end , case  when T12."TRANSACTION_CODE" in ('13') then T12."ORDERS_GROSSVALUE" else 0 end , T1."PREV_OWNER", T11."SEL_NAME", rtrim(T11."SEL_NAME") + ' / ' + T1."PREV_OWNER", case  when T16."Order_By" is null then 1 else T16."Order_By" end , case  when T12."TRANSACTION_CODE" in ('94', '92', '93', '91') then T12."ORDERS_GROSSVALUE" * -1 else 0 end , T6."ZIPCODE", substring(T6."ZIPCODE",4,1), substring(T6."ZIPCODE",3,1), substring(T6."ZIPCODE",2,1), substring(T6."ZIPCODE",1,1), T8."MODEL_TEXT", T16."Order_By", case  when T8."MAKE_CD" in ('OP') then 'Opel' else 'Fremdfabrikat' end , case  when T15."Standort_Name" is null then 'Rahlstedt' else T15."Standort_Name" end , case  when T15."Standort_ID" is null then '01' else T15."Standort_ID" end , case  when T15."Hauptbetrieb_Name" is null then 'AH Lauße' else T15."Hauptbetrieb_Name" end , case  when T15."Hauptbetrieb_ID" is null then '1' else T15."Hauptbetrieb_ID" end , T16."Fabrikat", T15."Standort_Name", T15."Standort_ID", T15."Hauptbetrieb_Name", T15."Hauptbetrieb_ID", rtrim(T12."TRANSACTION_CODE") + ' - ' + rtrim(T13."SPECIFY"), substring(T14."DEPARTMENT",1,2), T14."UNIQUE_IDENT", T14."DEPARTMENT", db_name(), case  when T10."DESCRIPTION" in ('Opel') then T10."DESCRIPTION" else 'Fremdfabrikat' end , T1."RECEPTION_DATE", T1."SALE_DATE", T1."PURCH_DATE", case  when T12."TRANSACTION_CODE" in ('26', '27', '28', '29', '31', '30', '33', '17') then T12."ORDERS_GROSSVALUE" else 0 end , case  when T12."TRANSACTION_CODE" in ('34', '35', '36', '37 ', '38', '39 ', '40 ', '41 ', '42', '45', '47 ', '48 ', '49 ', '59', '61', '63', '67', '69', '70 ', '75 ') then T12."ORDERS_GROSSVALUE" else 0 end , case  when T12."TRANSACTION_CODE" in ('25 ', '26 ', '27 ', '28 ', '29 ', '30', '31', '32', '33', '17') then T12."ORDERS_GROSSVALUE" else 0 end , case  when T12."TRANSACTION_CODE" in ('11 ', '26') then T12."ORDERS_GROSSVALUE" else 0 end , T12."INT_VOUCHER_NO", T12."DESTINATION", T12."DEPARTMENT", T12."BOOK_KEEPING_CODE", T12."CLASSIFICATION_DTE", T12."DISCOUNT", T12."ORDERS_GROSSVALUE", T12."COSTS", T12."REFERENCE", T13."SPECIFY", T13."TRANSACTION_CODE", T12."TRANSACTION_CODE", T12."ORDER_NUMBER", T12."ORDER_DATE", T12."DOCUMENT_NR", T12."DOCUMENT_DATE", T12."FUNCTION_CODE", T12."PROGRAM", T12."HANDLER", T12."TRANSACT_DATE", T12."LINE_NO", T12."LINE_TYPE", T12."UNIT_NUMBER", T5."SEL_NAME" + ' / ' + rtrim(T6."CUSTOMER_NUMBER") + ' - ' + rtrim(T6."NAME"), case  when T11."SEL_NAME" is null then ' - ' + ' / ' + T1."PREV_OWNER" else T11."SEL_NAME" + ' / ' + T1."PREV_OWNER" end , rtrim(T1."UNIT_NUMBER") + ' - ' + rtrim(T6."CUSTOMER_NUMBER") + ' - ' + rtrim(T6."NAME") + ' - ' + T1."VEHICLE_TYPE", T1."UNIT_NUMBER" + ' - ' + T1."OWNER" + ' - ', T8."ORIG_INV_DATE", rtrim(T1."UNIT_NUMBER") + ' - ' + rtrim(T6."CUSTOMER_NUMBER") + ' - ' + rtrim(T6."NAME"), 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 , T8."COLOUR_CODE" + ' - ' + T8."COLOUR_CF", T6."CUSTOMER_GROUP" + ' - ' + T7."CUST_GROUP_SPECIFY", rtrim(T6."CUSTOMER_NUMBER") + ' - ' + rtrim(T6."NAME"), case  when T1."VEHICLE_TYPE" is null then T1."VEHICLE_TYPE" else T1."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT" end , case  when T1."VEHICLE_TYPE" in ('N', 'T', 'W', 'M', 'V') then 'Neuwagen' when T1."VEHICLE_TYPE" in ('D', 'G', 'K', 'R') then 'Gebrauchtwagen' else NULL end , T8."CHASSIS_NUMBER", case  when case  when (T8."MAKE_CD" in ('OP')) then 'Opel' else 'Fremdfabrikat' end  = 'Fremdfabrikat' then T8."MODEL_TEXT" else T9."MOD_LIN_SPECIFY" end , T10."DESCRIPTION", T5."SEL_NAME", substring(T1."SALES_DEPARTMENT",1,2), T1."CLIENT_DB", T8."LATEST_INV_DATE", T10."GLOBAL_MAKE_CD", T8."MAKE_CD", T8."MAKE", T8."COLOUR_CODE", T8."DRIVE_CODE", T8."MOTOR_CODE", T8."MILEAGE", T8."WORKSHOP_MODEL", T9."MOD_LIN_SPECIFY", T9."MODEL_LINE", T8."MODEL_LINE", T8."COLOUR_CF", T8."SALESMAN_SALE", T8."SALESMAN_BUY", T8."ARRIVAL_DATE", T8."LATEST_REG_DATE", T8."FIRST_REG_DATE", T8."OWNER_CODE", T8."REGISTER_NUMBER", T1."CONTRACT_DATE", T1."ORDER_CONFIRM_DATE", T1."ORDER_ARRIVAL_DATE", T7."CUST_GROUP_SPECIFY", T7."CUSTOMER_GROUP", T6."CUSTOMER_GROUP", T6."NAME", T6."CUSTOMER_NUMBER", T1."ACCOUNT_SALES", T1."ACCOUNT_PURCHASE", T1."OWNER", T1."LOCATION_CODE", T1."PA_NUMBER", T1."BUYING_ORDER_DATE", T1."BUYING_ORDER_NO", T1."STOCK_DAYS", T1."REG_FEE", T1."SALES_PRICE_UNIT", T1."COST_UNIT", T1."PURCH_PRICE_UNIT", T1."BUD_COST", T1."BUD_DEDUCT_FEE", T1."BUD_REG_FEE", T1."BUD_PURCH_PRICE", T1."BUD_SALE_DET", T1."SALE_INV_NUMBER", T5."SEL_FAMILY_NAME", T5."SELLER_CODE", T1."SALE_SALESMAN", T4."DESCRIPTION", T4."DEPARTMENT_TYPE_ID", T1."SALES_DEPARTMENT", T1."PURCH_SALSMAN_CODE", T1."PURCH_DEPARTMENT", T1."MILEAGE", T3."SPECIFY", T3."BOOK_KEEPING_CODE", T1."BOOK_KEEPING_CODE", T1."DEPRECIATION", T1."CAR_CREATION_DATE", T2."VEHICLE_TYPE_TEXT", T2."VEHICLE_TYPE", T1."VEHICLE_TYPE", T1."ECC_STATUS", T1."BASIS_NUMBER"
 from (((((((((((((("OPTIMA"."import"."UNIT_FILE" T1 LEFT OUTER JOIN "OPTIMA"."import"."vPP5R" T2 on T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE" and T1."CLIENT_DB" = T2."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."vPP5M" T3 on T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE" and T1."CLIENT_DB" = T3."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID" and T1."CLIENT_DB" = T4."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."VPP43" T5 on T1."SALE_SALESMAN" = T5."SELLER_CODE" and T1."CLIENT_DB" = T5."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."CUSTOMER" T6 on T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER" and T1."CLIENT_DB" = T6."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."VPP48" T7 on T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP" and T6."CLIENT_DB" = T7."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."VEHICLE" T8 on T1."BASIS_NUMBER" = T8."BASIS_NUMBER" and T1."CLIENT_DB" = T8."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."VPP5Q" T9 on T8."MODEL_LINE" = T9."MODEL_LINE" and T8."MAKE_CD" = T9."MAKE_CD" and T8."CLIENT_DB" = T9."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."GLOBAL_MAKE" T10 on T8."MAKE_CD" = T10."GLOBAL_MAKE_CD" and T8."CLIENT_DB" = T10."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."VPP43" T11 on T1."PURCH_SALSMAN_CODE" = T11."SELLER_CODE" and T1."CLIENT_DB" = T11."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."UNIT_HISTORY" T12 on T1."UNIT_NUMBER" = T12."UNIT_NUMBER" and T1."CLIENT_DB" = T12."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."vPP5A" T13 on T12."TRANSACTION_CODE" = T13."TRANSACTION_CODE" and T12."CLIENT_DB" = T13."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."import"."UNIT_CHANGE_HIST" T14 on T1."UNIT_NUMBER" = T14."UNIT_NUMBER" and ' ' <> T14."DEPARTMENT" and T1."CLIENT_DB" = T14."CLIENT_DB") LEFT OUTER JOIN "OPTIMA"."data"."GC_Department" T15 on T14."CLIENT_DB" = T15."Hauptbetrieb" and substring(T14."DEPARTMENT",1,2) = T15."Standort") LEFT OUTER JOIN "OPTIMA"."data"."GC_Marken" T16 on T8."CLIENT_DB" = T16."Client_DB" and T8."MAKE_CD" = T16."Make"
 where T1."ECC_STATUS" = '64' and T12."TRANSACTION_CODE"  NOT  in ('76 ', '77 ') and T1."SALE_DATE" >= {ts '2020-01-01 00:00:00.000'}
 order by 1 asc