C11/System/OPTIMA/IQD/NW/nw_auftragsbestand.sql

select T1."ORDER_NUMBER" as "Order Number_2",
	   T1."DEBIT_ACCOUNT" as "Debit Account",
	   T1."STATUS" as "Status",
	   T1."STATE_KEY_DATE" as "State Key Date",
	   T1."INVOICE_NUMBER" as "Invoice Number",
	   T1."DEPARTMENT" as "Department",
	   T1."STATE_DELIV_DATE" as "State Deliv Date",
	   T1."STATE_CODE_VO" as "State Code Vo",
	   T1."TRANSACT_DATE" as "Transact Date",
	   T1."HANDLER" as "Handler",
	   T1."DELIVERY_ACCOUNT" as "Delivery Account",
	   T1."USER_UPDATE_VO" as "User Update Vo",
	   T1."SALESMAN" as "Salesman",
	   T1."DEBIT_PERM" as "Debit Perm",
	   T1."ORDER_DATE" as "Order Date",
	   T1."DELIVERY_DATE" as "Delivery Date",
	   T1."DELIVERY_PLACE" as "Delivery Place",
	   T1."INVOICE_DATE" as "Invoice Date",
	   T1."PMT_TERM" as "Pmt Term_2",
	   T1."NEXT_LINE_NUMBER" as "Next Line Number",
	   T1."PAYMENT_TEXT" as "Payment Text",
	   T1."INVOICE_COPY_CODE" as "Invoice Copy Code",
	   T1."FLEET_OWNER" as "Fleet Owner",
	   T1."OPTION_SPECIFIC" as "Option Specific",
	   T1."VEHICLE_LINES_VO" as "Vehicle Lines Vo",
	   T1."ORDER_SUM_VO" as "Order Sum Vo",
	   T1."REQ_NO" as "Req No",
	   T1."REFERENCE_NUMBER" as "Reference Number",
	   T1."DUEDATE_1" as "Duedate 1",
	   T1."TITLE" as "Title",
	   T1."NAME" as "Name",
	   T1."VEHICLE_SOLD" as "Vehicle Sold",
	   T1."VEHICLE_SOLD_SUM" as "Vehicle Sold Sum",
	   T1."VEHICLE_PURCH" as "Vehicle Purch",
	   T1."VEHICLE_PURCH_SUM" as "Vehicle Purch Sum",
	   T1."CHAIN_NUMBER" as "Chain Number",
	   T1."SYSTEM_INV_PERM" as "System Inv Perm",
	   T1."INTERNAL_CODE" as "Internal Code",
	   T1."PREV_STATUS" as "Prev Status",
	   T1."CREDIT_ORDER_VEH" as "Credit Order Veh",
	   T1."TAX_HANDLING" as "Tax Handling",
	   T1."TAX_PERC" as "Tax Perc",
	   T1."DELIVERY_WAY_CODE" as "Delivery Way Code",
	   T1."DELIVERY_TERM" as "Delivery Term",
	   T1."CUSTOMER_GROUP" as "Customer Group_2",
	   T1."PRICE_CODE" as "Price Code",
	   T1."STOCK" as "Stock_2",
	   T1."PLACE_CODE_2" as "Place Code 2",
	   T1."INVOICE_DISC_PERC" as "Invoice Disc Perc",
	   T1."ORDER_ARR_DATE_VO" as "Order Arr Date Vo",
	   T1."OFFER" as "Offer",
	   T1."VEH_ORDER_TYPE" as "Veh Order Type",
	   T1."CNTRACT_DUE_DATE" as "Cntract Due Date",
	   T1."CUSTOMER_SOLD" as "Customer Sold",
	   T1."EXP_ARRIVAL_TIME" as "Exp Arrival Time",
	   T1."UNIQUE_IDENT" as "Unique Ident",
	   T2."STAT_CODE" as "Stat Code",
	   T2."STAT_SPECIFY" as "Stat Specify",
	   T3."DEPARTMENT_TYPE_ID" as "Department Type Id",
	   T3."DESCRIPTION" as "Description_2",
	   T4."SELLER_CODE" as "Seller Code",
	   T4."SEL_NAME" as "Sel Name",
	   T4."SEL_DEPARTMENT" as "Sel Department",
	   T4."SEL_FIRST_NAME" as "Sel First Name",
	   T4."SEL_FAMILY_NAME" as "Sel Family Name",
	   T5."FLEET_OWNER_CODE" as "Fleet Owner Code",
	   T5."SPECIFY" as "Specify_2",
	   T6."CUSTOMER_GROUP" as "Customer Group",
	   T6."CUST_GROUP_SPECIFY" as "Cust Group Specify",
	   T7."STOCK" as "Stock",
	   T7."STOCK_NAME" as "Stock Name",
	   T8."ORDER_NUMBER" as "Order Number",
	   T8."LINE_NUMBER" as "Line Number",
	   T8."UNIT_NUMBER" as "Unit Number_2",
	   T8."VEHICLE_TYPE_VO" as "Vehicle Type Vo",
	   T8."REGISTER_NUMBER" as "Register Number",
	   T8."CHASSIS_NUMBER" as "Chassis Number_2",
	   T8."VEHICLE_SUM_OLD" as "Vehicle Sum Old",
	   T8."VEHICLE_SUM_NEW" as "Vehicle Sum New",
	   T8."OPTION_LINES" as "Option Lines",
	   T8."LINE_TYPE_VEH" as "Line Type Veh",
	   T8."MILEAGE" as "Mileage",
	   T8."ACCOUNTING_CODE" as "Accounting Code",
	   T9."VEHICLE_TYPE" as "Vehicle Type_2",
	   T9."VEHICLE_TYPE_TEXT" as "Vehicle Type Text",
	   T10."BOOK_KEEPING_CODE" as "Book Keeping Code",
	   T10."SPECIFY" as "Specify",
	   T11."UNIT_NUMBER" as "Unit Number",
	   T11."BASIS_NUMBER" as "Basis Number_2",
	   T11."ECC_STATUS" as "Ecc Status",
	   T11."VEHICLE_TYPE" as "Vehicle Type",
	   T11."CATEGORY" as "Category",
	   T11."PMT_TERM" as "Pmt Term",
	   T12."BASIS_NUMBER" as "Basis Number",
	   T12."CHASSIS_NUMBER" as "Chassis Number",
	   T12."CAR_GROUP" as "Car Group",
	   T12."CAR_STATUS" as "Car Status",
	   T12."FAC_MODEL_CODE_L" as "Fac Model Code L",
	   T12."MODEL_TEXT" as "Model Text_2",
	   T12."ORIG_MODEL_CODE1" as "Orig Model Code1",
	   T12."ORIG_MODEL_CODE2" as "Orig Model Code2",
	   T12."COLOUR_CF" as "Colour Cf",
	   T12."TRIM_TXT" as "Trim Txt",
	   T12."MODEL_LINE" as "Model Line_2",
	   T12."WORKSHOP_MODEL" as "Workshop Model_2",
	   T12."FAC_MODEL_CODE_S" as "Fac Model Code S",
	   T12."CLASSIFICATION" as "Classification",
	   T12."MOTOR_NUMBER" as "Motor Number",
	   T12."KEY_CODE" as "Key Code",
	   T12."YEAR_MODEL_CF" as "Year Model Cf",
	   T12."CYLINDER_VOLUME" as "Cylinder Volume",
	   T12."SEATS" as "Seats",
	   T12."COLOUR_CODE" as "Colour Code",
	   T12."TYPE_YEAR" as "Type Year",
	   CASE WHEN (T11."ECC_STATUS" = '21') THEN ('21 - Vorlauf') WHEN (T11."ECC_STATUS" = '25') THEN ('25 - für Kd best. FZG') WHEN (T11."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T11."ECC_STATUS" = '44') THEN ('44 - am Hof nicht fakt.') WHEN (T11."ECC_STATUS" = '64') THEN ('64 - verkauft') ELSE null END as "Ecc_Status_Text",
	   T13."MODEL_LINE" as "Model Line",
	   T13."MOD_LIN_SPECIFY" as "Mod Lin Specify",
	   T13."MAKE_CD" as "Make Cd_2",
	   T14."WORKSHOP_MODEL" as "Workshop Model",
	   T14."MAKE_CD" as "Make Cd",
	   T14."MODEL_TEXT" as "Model Text",
	   T14."ORIG_MODEL_CODE" as "Orig Model Code",
	   T14."MAKE_CODE" as "Make Code",
	   CASE WHEN (T9."VEHICLE_TYPE" IN ('N','T','V','B','M')) THEN ('Neuwagen') WHEN (T9."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END as "Fahrzeugart_AB",
	   '1' as "Hauptbetrieb",
	   (left(T3."DEPARTMENT_TYPE_ID",2)) as "Standort",
	   T4."SEL_NAME" as "Verkäufer_AB",
	   T15."DESCRIPTION" as "Fabrikat_AB",
	   T13."MOD_LIN_SPECIFY" as "Model_AB",
	   T12."MODEL_TEXT" as "Modellbez",
	   T12."CHASSIS_NUMBER" as "Fahrgestellnr",
	   T1."DELIVERY_ACCOUNT" + ' - ' + T1."NAME" as "Kunde",
	   CASE WHEN (T10."BOOK_KEEPING_CODE" = 'AGENT') THEN ('Behörden FZG') ELSE (T1."FLEET_OWNER" + ' - ' + T5."SPECIFY") END as "Kundenart_alt",
	   T1."PMT_TERM" + ' - ' + T1."PAYMENT_TEXT" as "Geschäftsart",
	   T12."COLOUR_CODE" + ' - ' + T12."COLOUR_CF" as "Farbe",
	   T9."VEHICLE_TYPE" + ' - ' + T9."VEHICLE_TYPE_TEXT" as "Fahrzeugtyp_AB",
	   T8."UNIT_NUMBER" + ' - ' + T12."CHASSIS_NUMBER" as "FZG_AB",
	   CASE WHEN (T1."STATUS" IN ('18','FR')) THEN (1) WHEN (T1."STATUS" IN ('FG','FS')) THEN (-1) ELSE null END as "Menge_AB",
	   T15."GLOBAL_MAKE_CD" as "Global Make Cd",
	   T15."DESCRIPTION" as "Description",
	   T11."CONTRACT_DATE" as "Contract Date",
	   (left(T8."UNIT_NUMBER",7)) + ' / ' + T11."BASIS_NUMBER" + ' - ' + T11."OWNER" + ' - ' + (convert(varchar(50), year(T11."CONTRACT_DATE")) + '-' + convert(varchar(50), month(T11."CONTRACT_DATE")) + '-' + convert(varchar(50), day(T11."CONTRACT_DATE"))) + ' / ' + T11."ECC_STATUS" as "FZG_1_AB",
	   CASE WHEN (T1."STATUS" IN ('FG','FR','FS')) THEN ('fakturiert') ELSE ('Auftragsbestand') END as "Status_1",
	   T16."EVENT_CODE_GMD" as "Event Code Gmd_2",
	   T16."EVENT_DATE_GMD" as "Event Date Gmd_2",
	   T16."ALLOC_DATE_GMD" as "Alloc Date Gmd_2",
	   T16."DELIV_DATE_GMD" as "Deliv Date Gmd",
	   (CASE WHEN (T16."EVENT_CODE_GMD" IS NULL) THEN ('Info fehlt') ELSE (T16."EVENT_CODE_GMD") END) + ' - ' + (CASE WHEN (T16."EVENT_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."EVENT_DATE_GMD")) + '-' + convert(varchar(50), month(T16."EVENT_DATE_GMD")) + '-' + convert(varchar(50), day(T16."EVENT_DATE_GMD")))) END) + ' - ' + (CASE WHEN (T16."ALLOC_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."ALLOC_DATE_GMD")) + '-' + convert(varchar(50), month(T16."ALLOC_DATE_GMD")) + '-' + convert(varchar(50), day(T16."ALLOC_DATE_GMD")))) END) + ' - ' + (CASE WHEN (T16."DELIV_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."DELIV_DATE_GMD")) + '-' + convert(varchar(50), month(T16."DELIV_DATE_GMD")) + '-' + convert(varchar(50), day(T16."DELIV_DATE_GMD")))) END) as "FZG_2(GD70)",
	   CASE WHEN ((right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '10' AND '13') THEN ('Endkunden') WHEN ((right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '20' AND '23') THEN ('Geschäftskunden') WHEN ((right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '30' AND '33') THEN ('Großkunden') WHEN ((right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '40' AND '43') THEN ('Vermittler') WHEN ((right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '80' AND '80') THEN ('Händler') WHEN ((CASE WHEN (T9."VEHICLE_TYPE" IN ('N','T','V','B','M')) THEN ('Neuwagen') WHEN (T9."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW' + ' ' + CASE WHEN ((T9."VEHICLE_TYPE" IN ('D','R')) and ((substring(T10."BOOK_KEEPING_CODE", 3, 3)) = '100')) THEN ('Endkunde') WHEN ((T9."VEHICLE_TYPE" IN ('D','R')) and ((substring(T10."BOOK_KEEPING_CODE", 3, 3)) = '300')) THEN ('Aufkäufer') WHEN ((T9."VEHICLE_TYPE" IN ('D','R')) and (not (substring(T10."BOOK_KEEPING_CODE", 3, 3)) IN ('100','300'))) THEN ('GW Sonstige') ELSE null END) ELSE null END as "Kundenart",
	   CASE WHEN (T16."EVENT_CODE_GMD" IS NULL) THEN ('Info fehlt') ELSE (T16."EVENT_CODE_GMD") END as "Event Code Gmd",
	   CASE WHEN (T16."EVENT_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."EVENT_DATE_GMD")) + '-' + convert(varchar(50), month(T16."EVENT_DATE_GMD")) + '-' + convert(varchar(50), day(T16."EVENT_DATE_GMD")))) END as "Event Date Gmd",
	   CASE WHEN (T16."ALLOC_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."ALLOC_DATE_GMD")) + '-' + convert(varchar(50), month(T16."ALLOC_DATE_GMD")) + '-' + convert(varchar(50), day(T16."ALLOC_DATE_GMD")))) END as "Alloc Date Gmd",
	   CASE WHEN (T16."DELIV_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((convert(varchar(50), year(T16."DELIV_DATE_GMD")) + '-' + convert(varchar(50), month(T16."DELIV_DATE_GMD")) + '-' + convert(varchar(50), day(T16."DELIV_DATE_GMD")))) END as "Del Date Gmd",
	   '1' as "Hauptbetrieb_ID",
	   ((left(T3."DEPARTMENT_TYPE_ID",2))) as "Standort_ID"
from ((((((("OPTIMA"."import"."VEH_ORDER_HEADER" T1 left outer join "OPTIMA"."import"."VPP25" T2 on (T1."STATUS" = T2."STAT_CODE") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T3 on (T1."DEPARTMENT" = T3."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."VEH_ORDER_LINE" T8 on (T1."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP4K" T5 on (T1."FLEET_OWNER" = T5."FLEET_OWNER_CODE") and (T1."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T6 on (T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP61" T7 on (T1."STOCK" = T7."STOCK") and (T1."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5M" T10 on (T8."ACCOUNTING_CODE" = T10."BOOK_KEEPING_CODE") and (T8."CLIENT_DB" = T10."CLIENT_DB")),
	((((((("OPTIMA"."import"."UNIT_FILE" T11 left outer join "OPTIMA"."import"."VPP43" T4 on (T4."SELLER_CODE" = T11."SALE_SALESMAN") and (T4."CLIENT_DB" = T11."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP5R" T9 on (T11."VEHICLE_TYPE" = T9."VEHICLE_TYPE") and (T11."CLIENT_DB" = T9."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T12 on T11."BASIS_NUMBER" = T12."BASIS_NUMBER") left outer join "OPTIMA"."import"."VPP5Q" T13 on ((T12."MODEL_LINE" = T13."MODEL_LINE") and (T12."MAKE_CD" = T13."MAKE_CD")) and (T12."CLIENT_DB" = T13."CLIENT_DB")) left outer join "OPTIMA"."import"."vPP74" T14 on ((T12."WORKSHOP_MODEL" = T14."WORKSHOP_MODEL") and (T12."MAKE_CD" = T14."MAKE_CD")) and (T12."CLIENT_DB" = T14."CLIENT_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T15 on (T12."MAKE_CD" = T15."GLOBAL_MAKE_CD") and (T12."CLIENT_DB" = T15."CLIENT_DB")) left outer join "OPTIMA"."import"."GM_DRIVE_ORDER" T16 on (T11."UNIT_NUMBER" = T16."UNIT_NUMBER") and (T11."CLIENT_DB" = T16."CLIENT_DB"))
where ((T8."UNIT_NUMBER" = T11."UNIT_NUMBER") and (T8."CLIENT_DB" = T11."CLIENT_DB"))
and (((((CASE WHEN (T9."VEHICLE_TYPE" IN ('N','T','V','B','M')) THEN ('Neuwagen') WHEN (T9."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (T8."LINE_TYPE_VEH" <> 'Z')) and (T1."ORDER_DATE" >= convert(datetime, '2021-01-01 00:00:00.000'))) and ((CASE WHEN (T1."STATUS" IN ('FG','FR','FS')) THEN ('fakturiert') ELSE ('Auftragsbestand') END) = 'Auftragsbestand'))
-- order by "Unit Number_2" asc