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 - Lagerauftrag') WHEN (T1."ECC_STATUS" = '25') THEN ('25 - Kundenauftrag') WHEN (T1."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T1."ECC_STATUS" = '44') THEN ('44 - auf Lager verkauft') WHEN (T1."ECC_STATUS" = '64') THEN ('64 - verkauft') WHEN (T1."ECC_STATUS" = '42') THEN ('42') 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."MISC_1_CF" as "Misc 1 Cf", T3."MISC_2_CF" as "Misc 2 Cf", T3."MISC_3_CF" as "Misc 3 Cf", T3."MISC_4_CF" as "Misc 4 Cf", T3."MISC_5_CF" as "Misc 5 Cf", T3."MISC_6_CF" as "Misc 6 Cf", T3."MISC_7_CF" as "Misc 7 Cf", T3."MISC_8_CF" as "Misc 8 Cf", T3."MISC_9_CF" as "Misc 9 Cf", T3."MISC_10_CF" as "Misc 10 Cf", 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", T1."CLIENT_DB" as "Hauptbetrieb", (substring(T6."DEPARTMENT_TYPE_ID", 1, 2)) as "Standort", T6."DEPARTMENT_TYPE_ID" as "Department Type Id", T6."DESCRIPTION" as "Description_2", CASE WHEN (T2."VEHICLE_TYPE" IN ('N','T','V')) THEN ('Neuwagen') WHEN (T2."VEHICLE_TYPE" IN ('D','G','K','R','M')) THEN ('Gebrauchtwagen') ELSE null END as "Fahrzeugart", T7."DESCRIPTION" as "Fabrikat_ori", T3."MODEL_TEXT" as "Modellbez", T3."CHASSIS_NUMBER" as "Fahrgestellnr", T3."COLOUR_CODE" + ' - ' + T3."COLOUR_CF" as "Farbe", (rtrim(T1."UNIT_NUMBER")) + ' - ' + T3."CHASSIS_NUMBER" + ' - ' + (CASE WHEN (T2."VEHICLE_TYPE" IS NULL) THEN (T1."VEHICLE_TYPE") ELSE (T2."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT") END) as "FZG", CASE WHEN (T2."VEHICLE_TYPE" IS NULL) THEN (T1."VEHICLE_TYPE") ELSE (T2."VEHICLE_TYPE" + ' - ' + T2."VEHICLE_TYPE_TEXT") END as "Fahrzeugtyp", T7."GLOBAL_MAKE_CD" as "Global Make Cd", T7."DESCRIPTION" as "Description", T1."PURCH_PRICE_UNIT" + T1."COST_UNIT" as "Einsatz", 1 as "Menge", (getdate()) as "Heute", CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END as "Standtage", CASE WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) BETWEEN 0 AND 30) THEN ('0 - 30 Tage') WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) BETWEEN 31 AND 60) THEN ('31 - 60 Tage') WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) BETWEEN 61 AND 90) THEN ('61 - 90 Tage') WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) BETWEEN 91 AND 180) THEN ('91 - 180 Tage') WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) BETWEEN 181 AND 360) THEN ('181 - 360 Tage') WHEN ((CASE WHEN (T1."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) > 360) THEN ('> 360 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."ECC_STATUS" IN ('21','25')) THEN (0) WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((-1 * datediff(day, ((getdate())), T1."PURCH_DATE"))) ELSE ((-1 * datediff(day, ((getdate())), T1."RECEPTION_DATE"))) END) * 15 as "kalk_Kosten", (T1."LOCATION_CODE") as "Betrieb", 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 "int. Aufw.", T1."BUD_SALE_DET" as "geplanter VK", CASE WHEN (T7."DESCRIPTION" IN ('Opel','Kia')) THEN (T7."DESCRIPTION") ELSE ('Fremdfabrikat') END as "Fabrikat_manuell", CASE WHEN (T9."Hauptbetrieb_ID" IS NULL) THEN ('1') ELSE (T9."Hauptbetrieb_ID") END as "Hauptbetrieb_ID", 'Bergneustadt Ley' as "Hauptbetrieb_Name", CASE WHEN ((T9."Standort_ID" IS NULL) and (T1."CLIENT_DB" = '1')) THEN ('01') WHEN ((T9."Standort_ID" IS NULL) and (T1."CLIENT_DB" = '2')) THEN ('02') ELSE (T9."Standort_ID") END as "Standort_ID", CASE WHEN ((T9."Standort_Name" IS NULL) and (T1."CLIENT_DB" IN ('1'))) THEN ('Bergneustadt') WHEN ((T9."Standort_Name" IS NULL) and (T1."CLIENT_DB" IN ('2'))) THEN ('Verwaltung') ELSE (T9."Standort_Name") END as "Standort_Name", T10."Fabrikat" as "Fabrikat_GC_Marke", CASE WHEN (T3."MAKE_CD" IN ('OP')) THEN ('Opel') ELSE ('Fremdfabrikat') END as "Fabrikat", CASE WHEN (T10."Order_By" IS NULL) THEN (99) ELSE (T10."Order_By") END as "Fabrikat_Order_By", T4."MOD_LIN_SPECIFY" as "Modell_Beschreibung", T3."MODEL_TEXT" as "Model", '' as "Fahrzeugtyp_1", T1."LOCATION_CODE" as "Standort_1", (rtrim(T1."PREV_OWNER")) + ' / ' + T8."SEL_NAME" as "Lieferant/Einkäufer", T1."PREV_OWNER" as "Name_Lieferant", T8."SEL_NAME" as "Name_Einkäufer", (rtrim(T1."UNIT_NUMBER")) + ' - ' + T8."SEL_NAME" as "FZG_Einkäufer", (convert(varchar(50), year(T3."FIRST_REG_DATE")) + '-' + convert(varchar(50), month(T3."FIRST_REG_DATE")) + '-' + convert(varchar(50), day(T3."FIRST_REG_DATE"))) as "EZ", T1."MILEAGE" as "Km-stand", 0 as "Plan VK Netz brutto", T1."BUD_SALE_DET" as "Plan VK Netz netto", 0 as "Plan Vk Platz brutto", 0 as "Plan VK Platz netto", ((getdate())) as "Invoice Date", CASE WHEN (T3."MODEL_TEXT" IS NOT NULL) THEN ((rtrim(T3."MODEL_TEXT")) + ' - ' + T1."UNIT_NUMBER") ELSE (T1."UNIT_NUMBER") END as "FZG_Bestand_Dashboard", CASE WHEN (T1."ECC_STATUS" LIKE '2%') THEN ('Vorlauf') WHEN (T1."ECC_STATUS" LIKE '4%') THEN ('Bestand') ELSE null END as "Status", CASE WHEN (T1."ECC_STATUS" IN ('21','41')) THEN ('Lager') WHEN (T1."ECC_STATUS" IN ('25','44')) THEN ('verkauft') ELSE null END as "Lager / verkauft" 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")) left outer join "OPTIMA"."data"."GC_Department" T9 on (T1."CLIENT_DB" = T9."Hauptbetrieb") and ((substring(T1."PURCH_DEPARTMENT", 1, 2)) = T9."Standort")) left outer join "OPTIMA"."data"."GC_Marken" T10 on (T3."CLIENT_DB" = T10."Client_DB") and (T3."MAKE_CD" = T10."Make")) where (T1."ECC_STATUS" IN ('41','42','44')) -- order by "Unit Number" asc,"Standtage" desc