select T1."UNIT_NUMBER" as "Unit Number", T1."BASIS_NUMBER" as "Basis Number_2", T1."ECC_STATUS" as "Ecc Status", T1."VEHICLE_TYPE" as "Vehicle Type_2", T1."TRANSACT_DATE" as "Transact Date_2", T1."HANDLER" as "Handler_2", T1."CAR_CREATION_DATE" as "Car Creation Date", T1."PMT_TERM" as "Pmt Term", T1."SMALL_ACCESSORIES" as "Small Accessories", T1."SMALL_INV_CHARGE" as "Small Inv Charge", T1."DELIVERY_STOP_CODE" as "Delivery Stop Code", T1."PRICE_CODE" as "Price Code", T1."DISCOUNT_PERC_WORK" as "Discount Perc Work_2", T1."DISCOUNT_LIMIT" as "Discount Limit", T1."INTERNAL_CODE" as "Internal Code", T1."TAX_CODE" as "Tax Code", T1."UNIT_LAST_UPD_DTE" as "Unit Last Upd Dte", T1."DEPRECIATION" as "Depreciation", T1."TRANSFER_TAX" as "Transfer Tax", T1."BOOK_KEEPING_CODE" as "Book Keeping Code", T1."TAX_HANDLING" as "Tax Handling", T1."INTEREST_CODE" as "Interest Code", T1."MILEAGE" as "Mileage_2", T1."PURCH_DEPARTMENT" as "Purch Department", T1."PURCH_SALSMAN_CODE" as "Purch Salsman Code", T1."PURCH_INVNO" as "Purch Invno", T1."PURCH_DATE" as "Purch Date", T1."SALES_DEPARTMENT" as "Sales Department", T1."SALE_SALESMAN" as "Sale Salesman", T1."SALE_INV_NUMBER" as "Sale Inv Number", T1."SALE_DATE" as "Sale Date", T1."SCRAP_CODE" as "Scrap Code", T1."UNIT_IN_CHANGE" as "Unit In Change", T1."BUD_SALE_DET" as "Bud Sale Det", T1."BUD_PURCH_PRICE" as "Bud Purch Price", T1."BUD_REG_FEE" as "Bud Reg Fee", T1."BUD_DEDUCT_FEE" as "Bud Deduct Fee", T1."BUD_COST" as "Bud Cost", T1."PREV_OWNER" as "Prev Owner", T1."OWNER" as "Owner", T1."PURCH_PRICE_UNIT" as "Purch Price Unit", T1."COST_UNIT" as "Cost Unit", T1."SALES_PRICE_UNIT" as "Sales Price Unit", T1."REG_FEE" as "Reg Fee", T1."INTEREST_1" as "Interest 1", T1."INTEREST_2" as "Interest 2", T1."NEXT_LINE_UNIT" as "Next Line Unit", T1."NEXT_LINE_1" as "Next Line 1", T1."TRANSFER_LINE" as "Transfer Line", T1."TRANSFER_TAX_HAND" as "Transfer Tax Hand", T1."TRANSFER_ACC_CODE" as "Transfer Acc Code", T1."TRANSFER_TAX_BASIS" as "Transfer Tax Basis", T1."TRANSFER_TAX_PCT" as "Transfer Tax Pct", T1."TRANSFER_SOLD" as "Transfer Sold", T1."PURCH_TAX" as "Purch Tax", T1."COST_TAX" as "Cost Tax", T1."OR_MARK" as "Or Mark", T1."TRANSFER_DATE" as "Transfer Date", T1."STOCK_DAYS" as "Stock Days", T1."DELETE_PROPOSAL" as "Delete Proposal", T1."TRANSFER_BBDB" as "Transfer Bbdb", T1."CONDITION_UNIT" as "Condition Unit", T1."BUYING_ORDER_NO" as "Buying Order No", T1."BUYING_ORDER_DATE" as "Buying Order Date", T1."PA_NUMBER" as "Pa Number", T1."VEHICLE_UPDATE" as "Vehicle Update", T1."LATEST_UPDATE" as "Latest Update", T1."INVOICE_PERMISSION" as "Invoice Permission", T1."LOCATION_CODE" as "Location Code", T1."ACCOUNT_PURCHASE" as "Account Purchase", T1."ACCOUNT_SALES" as "Account Sales", T1."BUDG_PRICE_VAT" as "Budg Price Vat", T1."RECALC_CODE" as "Recalc Code", T1."COMMISION_NO" as "Commision No", T1."DELIVERY_DATE_VEH" as "Delivery Date Veh", T1."RESERVATION_DATE" as "Reservation Date", T1."RESERVATION_PERSON" as "Reservation Person", T1."RESERVATION_TEXT" as "Reservation Text", T1."FREIGHT_BALANCE" as "Freight Balance", T1."TRF_TAXBASIS_SALES" as "Trf Taxbasis Sales", T1."TRF_TAXBASIS_PURCH" as "Trf Taxbasis Purch", T1."SALE_ORDER_NO" as "Sale Order No", T1."UNIT_SOLD" as "Unit Sold", T1."TRF_DEPARTMENT" as "Trf Department", T1."ORDER_ARRIVAL_DATE" as "Order Arrival Date", T1."ORDER_CONFIRM_DATE" as "Order Confirm Date", T1."PRICE_LABEL_DATE" as "Price Label Date", T1."VAT_TABLE_WORK" as "Vat Table Work", T1."VAT_TABLE_NORMAL" as "Vat Table Normal", T1."VAT_RATE_CODE" as "Vat Rate Code", T1."TRANSFER_VAT_RC" as "Transfer Vat Rc", T1."CNTRACT_DUE_DATE" as "Cntract Due Date", T1."CONTRACT_DATE" as "Contract Date", T1."EXP_ARRIVAL_TIME" as "Exp Arrival Time", T1."DEALER_MARKING" as "Dealer Marking_2", T1."BOOKING_DEALER_NR" as "Booking Dealer Nr", T1."DESIRED_DELIV_DATE" as "Desired Deliv Date", T1."VALUATION_DATE" as "Valuation Date", T1."FORD_INT_DATE" as "Ford Int Date", T1."EST_SALES_PRICE" as "Est Sales Price", T1."EST_REFERENCE_NO" as "Est Reference No", T1."RECEPTION_DATE" as "Reception Date", T1."BUD_OPT_PRICE" as "Bud Opt Price", T1."SALE_PRICE_DATE" as "Sale Price Date", T1."PURCH_ORIGIN_CODE" as "Purch Origin Code", T1."PRICE_LIST_VERSION" as "Price List Version", T1."PRICE_LIST_DATE" as "Price List Date", T1."UNIQUE_IDENT" as "Unique Ident", T2."VEHICLE_TYPE" as "Vehicle Type", T2."VEHICLE_TYPE_TEXT" as "Vehicle Type Text", 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 "ECC_Status_Text", T3."BASIS_NUMBER" as "Basis Number", T3."TRANSACT_DATE" as "Transact Date", T3."HANDLER" as "Handler", T3."REGISTER_NUMBER" as "Register Number", T3."CHASSIS_NUMBER" as "Chassis Number", T3."CAR_GROUP" as "Car Group", T3."CAR_STATUS" as "Car Status", T3."FAC_MODEL_CODE_L" as "Fac Model Code L", T3."FIRST_REG_DATE" as "First Reg Date", T3."LATEST_REG_DATE" as "Latest Reg Date", T3."FIRST_OCCUR_DATE" as "First Occur Date", T3."EXPECTED_ARR_DATE" as "Expected Arr Date", T3."ARRIVAL_DATE" as "Arrival Date", T3."SALESMAN_BUY" as "Salesman Buy", T3."SALESMAN_SALE" as "Salesman Sale", T3."MODEL_TEXT" as "Model Text_2", T3."ORIG_MODEL_CODE1" as "Orig Model Code1", T3."ORIG_MODEL_CODE2" as "Orig Model Code2", T3."COLOUR_CF" as "Colour Cf", T3."TRIM_TXT" as "Trim Txt", T3."MODEL_LINE" as "Model Line_2", T3."WORKSHOP_MODEL" as "Workshop Model_2", T3."FAC_MODEL_CODE_S" as "Fac Model Code S", T3."CLASSIFICATION" as "Classification", T3."MOTOR_NUMBER" as "Motor Number", T3."KEY_CODE" as "Key Code", T3."YEAR_MODEL_CF" as "Year Model Cf", T3."CYLINDER_VOLUME" as "Cylinder Volume", T3."SEATS" as "Seats", T3."DECLARATION_DATE" as "Declaration Date", T3."DECLARATION_PLACE" as "Declaration Place", T3."DECLARATION_NUMBER" as "Declaration Number", T3."EQUIPMENT_1" as "Equipment 1", T3."EQUIPMENT_2" as "Equipment 2", T3."EQUIPMENT_3" as "Equipment 3", T3."EQUIPMENT_4" as "Equipment 4", T3."MILEAGE" as "Mileage", T3."MOTOR_CODE" as "Motor Code", T3."GEAR_CODE" as "Gear Code", T3."DRIVE_CODE" as "Drive Code", T3."COLOUR_CODE" as "Colour Code", T3."TRIM_CODE" as "Trim Code", T3."NO_OF_OWNERS" as "No Of Owners", T3."TUV_TYPE" as "Tuv Type", T3."TUV_DATE" as "Tuv Date", T3."TUV_INSP" as "Tuv Insp", T3."ASU_TYPE" as "Asu Type", T3."ASU_DATE" as "Asu Date", T3."ASU_INSP" as "Asu Insp", T3."TYPE_YEAR" as "Type Year", T3."AVAILABLE" as "Available", T3."CAL_COST" as "Cal Cost", T3."CAL_COST_DATE" as "Cal Cost Date", T3."STOCK_DATE" as "Stock Date", T3."PURCH_CAL_DATE" as "Purch Cal Date", T3."MODEL_LINE_GROUP" as "Model Line Group", T3."MODEL_TYPE_2" as "Model Type 2", T3."MAKE" as "Make", T3."HOUR_RATE_TABLE" as "Hour Rate Table", T3."PAID_DATE" as "Paid Date", T3."LATEST_INV_DATE" as "Latest Inv Date", T3."PURCH_AMOUNT" as "Purch Amount", T3."DEALER_NUMBER" as "Dealer Number", T3."DEALER_MARKING" as "Dealer Marking", T3."FUELTYPE" as "Fueltype", T3."SERVICE_DAYS" as "Service Days", T3."HOURLY_REPAIR_RATE" as "Hourly Repair Rate", T3."WORKSHOP_PRICECODE" as "Workshop Pricecode", T3."DISCOUNT_PERC_WORK" as "Discount Perc Work", T3."SOLDMARK_DATE" as "Soldmark Date", T3."MAKE_CD" as "Make Cd_3", T4."MODEL_LINE" as "Model Line", T4."MOD_LIN_SPECIFY" as "Mod Lin Specify", T4."MAKE_CD" as "Make Cd_2", T5."WORKSHOP_MODEL" as "Workshop Model", T5."MAKE_CD" as "Make Cd", T5."MODEL_TEXT" as "Model Text", T5."ORIG_MODEL_CODE" as "Orig Model Code", T5."MODEL_TYPE" as "Model Type", T5."MODEL_GROUP" as "Model Group", T5."MAKE_CODE" as "Make Code", '1' as "Hauptbetrieb", T1."LOCATION_CODE" as "Standort", T6."DEPARTMENT_TYPE_ID" as "Department Type Id", T6."DESCRIPTION" as "Description_2", CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','M','B')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') WHEN (T1."VEHICLE_TYPE" IN ('G')) THEN ('Geschäftsfahrzeug') ELSE null END as "Fahrzeugart", T7."DESCRIPTION" as "Fabrikat", T4."MOD_LIN_SPECIFY" as "Model", T3."MODEL_TEXT" as "Modellbez", T3."CHASSIS_NUMBER" as "Fahrgestellnr", T3."COLOUR_CODE" + ' - ' + T3."COLOUR_CF" as "Farbe", T1."UNIT_NUMBER" + ' - ' + T3."CHASSIS_NUMBER" as "FZG", T2."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT" as "Fahrzeugtyp", T7."GLOBAL_MAKE_CD" as "Global Make Cd", T7."DESCRIPTION" as "Description", T1."PURCH_PRICE_UNIT" as "Einsatz", 1 as "Menge_Bestand", (getdate()) as "Bookkeep Date", CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END as "Standtage", CASE WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) > 180) THEN ('> 180 Tage') ELSE null END as "Standtagestaffel", T3."CAR_LETTER" as "Car Letter", CASE WHEN (T3."CAR_LETTER" = 'Konsi') THEN ('Konsi-FZG') ELSE ('Lagerfahrzeug') END as "Fahrzeugart_Konsi", (CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((day(((getdate())) - T1."PURCH_DATE"))) ELSE ((day(((getdate())) - T1."RECEPTION_DATE"))) END) * 15 as "kalk_Kosten", CASE WHEN (T1."LOCATION_CODE" = '01 ') THEN ('Selm') WHEN (T1."LOCATION_CODE" = '02 ') THEN ('Lünen') WHEN (T1."LOCATION_CODE" = '03 ') THEN ('Werne') WHEN (T1."LOCATION_CODE" = '10 ') THEN ('Lüdingh.') WHEN (T1."LOCATION_CODE" = '99 ') THEN ('AOV Ros.') WHEN (T1."LOCATION_CODE" = '98') THEN ('AOV Wink.') ELSE (T1."LOCATION_CODE") END as "Betrieb_ori", T8."SELLER_CODE" as "Seller Code", T8."SEL_NAME" as "Sel Name", T8."SEL_NAME" as "VB_Einkauf", T1."BUD_COST" as "geplante Kosten", T1."COST_UNIT" as "tatsächl. Kosten", T1."BUD_SALE_DET" as "geplanter VK", 'FZG-Bestand' as "Ebene1", (CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','M','B')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') WHEN (T1."VEHICLE_TYPE" IN ('G')) THEN ('Geschäftsfahrzeug') ELSE null END) as "Ebene2", (T2."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT") as "Ebene3", T7."DESCRIPTION" as "Ebene4", 'G.V.O.' as "Rechtseinheit", '' as "Betrieb", '1' as "Rechtseinheit_ID", T1."LOCATION_CODE" as "Betrieb_ID" 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"."VEHICLE" T3 on T1."BASIS_NUMBER" = T3."BASIS_NUMBER") left outer join "OPTIMA"."import"."VPP5Q" T4 on ((T3."MODEL_LINE" = T4."MODEL_LINE") and (T3."MAKE_CD" = T4."MAKE_CD")) and (T3."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP74" T5 on ((T3."WORKSHOP_MODEL" = T5."WORKSHOP_MODEL") and (T3."MAKE_CD" = T5."MAKE_CD")) and (T3."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T6 on (T1."PURCH_DEPARTMENT" = T6."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T7 on (T3."MAKE_CD" = T7."GLOBAL_MAKE_CD") and (T3."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T8 on (T1."PURCH_SALSMAN_CODE" = T8."SELLER_CODE") and (T1."CLIENT_DB" = T8."CLIENT_DB")) where (T1."ECC_STATUS" IN ('41','44')) -- order by "Standtage" desc