C11/System/OPTIMA/IQD/Belege/NW_BE_Bilanz.sql

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