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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\nw\nw_auftragsbestand.imr
TITLE,nw_auftragsbestand.imr
BEGIN SQL
select T1."ORDER_NUMBER" as c1,
	   T1."DEBIT_ACCOUNT" as c2,
	   T1."STATUS" as c3,
	   T1."STATE_KEY_DATE" as c4,
	   T1."INVOICE_NUMBER" as c5,
	   T1."DEPARTMENT" as c6,
	   T1."STATE_DELIV_DATE" as c7,
	   T1."STATE_CODE_VO" as c8,
	   T1."TRANSACT_DATE" as c9,
	   T1."HANDLER" as c10,
	   T1."DELIVERY_ACCOUNT" as c11,
	   T1."USER_UPDATE_VO" as c12,
	   T1."SALESMAN" as c13,
	   T1."DEBIT_PERM" as c14,
	   T1."ORDER_DATE" as c15,
	   T1."DELIVERY_DATE" as c16,
	   T1."DELIVERY_PLACE" as c17,
	   T1."INVOICE_DATE" as c18,
	   T1."PMT_TERM" as c19,
	   T1."NEXT_LINE_NUMBER" as c20,
	   T1."PAYMENT_TEXT" as c21,
	   T1."INVOICE_COPY_CODE" as c22,
	   T1."FLEET_OWNER" as c23,
	   T1."OPTION_SPECIFIC" as c24,
	   T1."VEHICLE_LINES_VO" as c25,
	   T1."ORDER_SUM_VO" as c26,
	   T1."REQ_NO" as c27,
	   T1."REFERENCE_NUMBER" as c28,
	   T1."DUEDATE_1" as c29,
	   T1."TITLE" as c30,
	   T1."NAME" as c31,
	   T1."VEHICLE_SOLD" as c32,
	   T1."VEHICLE_SOLD_SUM" as c33,
	   T1."VEHICLE_PURCH" as c34,
	   T1."VEHICLE_PURCH_SUM" as c35,
	   T1."CHAIN_NUMBER" as c36,
	   T1."SYSTEM_INV_PERM" as c37,
	   T1."INTERNAL_CODE" as c38,
	   T1."PREV_STATUS" as c39,
	   T1."CREDIT_ORDER_VEH" as c40,
	   T1."TAX_HANDLING" as c41,
	   T1."TAX_PERC" as c42,
	   T1."DELIVERY_WAY_CODE" as c43,
	   T1."DELIVERY_TERM" as c44,
	   T1."CUSTOMER_GROUP" as c45,
	   T1."PRICE_CODE" as c46,
	   T1."STOCK" as c47,
	   T1."PLACE_CODE_2" as c48,
	   T1."INVOICE_DISC_PERC" as c49,
	   T1."ORDER_ARR_DATE_VO" as c50,
	   T1."OFFER" as c51,
	   T1."VEH_ORDER_TYPE" as c52,
	   T1."CNTRACT_DUE_DATE" as c53,
	   T1."CUSTOMER_SOLD" as c54,
	   T1."EXP_ARRIVAL_TIME" as c55,
	   T1."UNIQUE_IDENT" as c56,
	   T2."STAT_CODE" as c57,
	   T2."STAT_SPECIFY" as c58,
	   T3."DEPARTMENT_TYPE_ID" as c59,
	   T3."DESCRIPTION" as c60,
	   T4."SELLER_CODE" as c61,
	   T4."SEL_NAME" as c62,
	   T4."SEL_DEPARTMENT" as c63,
	   T4."SEL_FIRST_NAME" as c64,
	   T4."SEL_FAMILY_NAME" as c65,
	   T5."FLEET_OWNER_CODE" as c66,
	   T5."SPECIFY" as c67,
	   T6."CUSTOMER_GROUP" as c68,
	   T6."CUST_GROUP_SPECIFY" as c69,
	   T7."STOCK" as c70,
	   T7."STOCK_NAME" as c71,
	   T8."ORDER_NUMBER" as c72,
	   T8."LINE_NUMBER" as c73,
	   T8."UNIT_NUMBER" as c74,
	   T8."VEHICLE_TYPE_VO" as c75,
	   T8."REGISTER_NUMBER" as c76,
	   T8."CHASSIS_NUMBER" as c77,
	   T8."VEHICLE_SUM_OLD" as c78,
	   T8."VEHICLE_SUM_NEW" as c79,
	   T8."OPTION_LINES" as c80,
	   T8."LINE_TYPE_VEH" as c81,
	   T8."MILEAGE" as c82,
	   T8."ACCOUNTING_CODE" as c83,
	   T9."VEHICLE_TYPE" as c84,
	   T9."VEHICLE_TYPE_TEXT" as c85,
	   T10."BOOK_KEEPING_CODE" as c86,
	   T10."SPECIFY" as c87,
	   T11."UNIT_NUMBER" as c88,
	   T11."BASIS_NUMBER" as c89,
	   T11."ECC_STATUS" as c90,
	   T11."VEHICLE_TYPE" as c91,
	   T11."CATEGORY" as c92,
	   T11."PMT_TERM" as c93,
	   T12."BASIS_NUMBER" as c94,
	   T12."CHASSIS_NUMBER" as c95,
	   T12."CAR_GROUP" as c96,
	   T12."CAR_STATUS" as c97,
	   T12."FAC_MODEL_CODE_L" as c98,
	   T12."MODEL_TEXT" as c99,
	   T12."ORIG_MODEL_CODE1" as c100,
	   T12."ORIG_MODEL_CODE2" as c101,
	   T12."COLOUR_CF" as c102,
	   T12."TRIM_TXT" as c103,
	   T12."MODEL_LINE" as c104,
	   T12."WORKSHOP_MODEL" as c105,
	   T12."FAC_MODEL_CODE_S" as c106,
	   T12."CLASSIFICATION" as c107,
	   T12."MOTOR_NUMBER" as c108,
	   T12."KEY_CODE" as c109,
	   T12."YEAR_MODEL_CF" as c110,
	   T12."CYLINDER_VOLUME" as c111,
	   T12."SEATS" as c112,
	   T12."COLOUR_CODE" as c113,
	   T12."TYPE_YEAR" as c114,
	   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 c115,
	   T13."MODEL_LINE" as c116,
	   T13."MOD_LIN_SPECIFY" as c117,
	   T13."MAKE_CD" as c118,
	   T14."WORKSHOP_MODEL" as c119,
	   T14."MAKE_CD" as c120,
	   T14."MODEL_TEXT" as c121,
	   T14."ORIG_MODEL_CODE" as c122,
	   T14."MAKE_CODE" as c123,
	   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 c124,
	   '1' as c125,
	   (od_left(T3."DEPARTMENT_TYPE_ID",2)) as c126,
	   T4."SEL_NAME" as c127,
	   T15."DESCRIPTION" as c128,
	   T13."MOD_LIN_SPECIFY" as c129,
	   T12."MODEL_TEXT" as c130,
	   T12."CHASSIS_NUMBER" as c131,
	   T1."DELIVERY_ACCOUNT" || ' - ' || T1."NAME" as c132,
	   CASE WHEN (T10."BOOK_KEEPING_CODE" = 'AGENT') THEN ('Behörden FZG') ELSE (T1."FLEET_OWNER" || ' - ' || T5."SPECIFY") END as c133,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c134,
	   T12."COLOUR_CODE" || ' - ' || T12."COLOUR_CF" as c135,
	   T9."VEHICLE_TYPE" || ' - ' || T9."VEHICLE_TYPE_TEXT" as c136,
	   T8."UNIT_NUMBER" || ' - ' || T12."CHASSIS_NUMBER" as c137,
	   CASE WHEN (T1."STATUS" IN ('18','FR')) THEN (1) WHEN (T1."STATUS" IN ('FG','FS')) THEN (-1) ELSE null END as c138,
	   T15."GLOBAL_MAKE_CD" as c139,
	   T15."DESCRIPTION" as c140,
	   T11."CONTRACT_DATE" as c141,
	   (od_left(T8."UNIT_NUMBER",7)) || ' / ' || T11."BASIS_NUMBER" || ' - ' || T11."OWNER" || ' - ' || (asciiz(extract(YEAR FROM T11."CONTRACT_DATE"),4) || '-' || asciiz(extract(MONTH FROM T11."CONTRACT_DATE"),2) || '-' || asciiz(extract(DAY FROM T11."CONTRACT_DATE"),2)) || ' / ' || T11."ECC_STATUS" as c142,
	   CASE WHEN (T1."STATUS" IN ('FG','FR','FS')) THEN ('fakturiert') ELSE ('Auftragsbestand') END as c143,
	   T16."EVENT_CODE_GMD" as c144,
	   T16."EVENT_DATE_GMD" as c145,
	   T16."ALLOC_DATE_GMD" as c146,
	   T16."DELIV_DATE_GMD" as c147,
	   (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 ((asciiz(extract(YEAR FROM T16."EVENT_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."EVENT_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."EVENT_DATE_GMD"),2))) END) || ' - ' || (CASE WHEN (T16."ALLOC_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((asciiz(extract(YEAR FROM T16."ALLOC_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."ALLOC_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."ALLOC_DATE_GMD"),2))) END) || ' - ' || (CASE WHEN (T16."DELIV_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((asciiz(extract(YEAR FROM T16."DELIV_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."DELIV_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."DELIV_DATE_GMD"),2))) END) as c148,
	   CASE WHEN ((od_right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '10' AND '13') THEN ('Endkunden') WHEN ((od_right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '20' AND '23') THEN ('Geschäftskunden') WHEN ((od_right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '30' AND '33') THEN ('Großkunden') WHEN ((od_right(T10."BOOK_KEEPING_CODE",2)) BETWEEN '40' AND '43') THEN ('Vermittler') WHEN ((od_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" from 3 for 3)) = '100')) THEN ('Endkunde') WHEN ((T9."VEHICLE_TYPE" IN ('D','R')) and ((substring(T10."BOOK_KEEPING_CODE" from 3 for 3)) = '300')) THEN ('Aufkäufer') WHEN ((T9."VEHICLE_TYPE" IN ('D','R')) and (not (substring(T10."BOOK_KEEPING_CODE" from 3 for 3)) IN ('100','300'))) THEN ('GW Sonstige') ELSE null END) ELSE null END as c149,
	   CASE WHEN (T16."EVENT_CODE_GMD" IS NULL) THEN ('Info fehlt') ELSE (T16."EVENT_CODE_GMD") END as c150,
	   CASE WHEN (T16."EVENT_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((asciiz(extract(YEAR FROM T16."EVENT_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."EVENT_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."EVENT_DATE_GMD"),2))) END as c151,
	   CASE WHEN (T16."ALLOC_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((asciiz(extract(YEAR FROM T16."ALLOC_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."ALLOC_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."ALLOC_DATE_GMD"),2))) END as c152,
	   CASE WHEN (T16."DELIV_DATE_GMD" IS NULL) THEN ('Info fehlt') ELSE ((asciiz(extract(YEAR FROM T16."DELIV_DATE_GMD"),4) || '-' || asciiz(extract(MONTH FROM T16."DELIV_DATE_GMD"),2) || '-' || asciiz(extract(DAY FROM T16."DELIV_DATE_GMD"),2))) END as c153,
	   '1' as c154,
	   ((od_left(T3."DEPARTMENT_TYPE_ID",2))) as c155
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" >= TIMESTAMP '2021-01-01 00:00:00.000')) and ((CASE WHEN (T1."STATUS" IN ('FG','FR','FS')) THEN ('fakturiert') ELSE ('Auftragsbestand') END) = 'Auftragsbestand'))
order by c74 asc

END SQL
COLUMN,0,Order Number
COLUMN,1,Debit Account
COLUMN,2,Status
COLUMN,3,State Key Date
COLUMN,4,Invoice Number
COLUMN,5,Department
COLUMN,6,State Deliv Date
COLUMN,7,State Code Vo
COLUMN,8,Transact Date
COLUMN,9,Handler
COLUMN,10,Delivery Account
COLUMN,11,User Update Vo
COLUMN,12,Salesman
COLUMN,13,Debit Perm
COLUMN,14,Order Date
COLUMN,15,Delivery Date
COLUMN,16,Delivery Place
COLUMN,17,Invoice Date
COLUMN,18,Pmt Term
COLUMN,19,Next Line Number
COLUMN,20,Payment Text
COLUMN,21,Invoice Copy Code
COLUMN,22,Fleet Owner
COLUMN,23,Option Specific
COLUMN,24,Vehicle Lines Vo
COLUMN,25,Order Sum Vo
COLUMN,26,Req No
COLUMN,27,Reference Number
COLUMN,28,Duedate 1
COLUMN,29,Title
COLUMN,30,Name
COLUMN,31,Vehicle Sold
COLUMN,32,Vehicle Sold Sum
COLUMN,33,Vehicle Purch
COLUMN,34,Vehicle Purch Sum
COLUMN,35,Chain Number
COLUMN,36,System Inv Perm
COLUMN,37,Internal Code
COLUMN,38,Prev Status
COLUMN,39,Credit Order Veh
COLUMN,40,Tax Handling
COLUMN,41,Tax Perc
COLUMN,42,Delivery Way Code
COLUMN,43,Delivery Term
COLUMN,44,Customer Group
COLUMN,45,Price Code
COLUMN,46,Stock
COLUMN,47,Place Code 2
COLUMN,48,Invoice Disc Perc
COLUMN,49,Order Arr Date Vo
COLUMN,50,Offer
COLUMN,51,Veh Order Type
COLUMN,52,Cntract Due Date
COLUMN,53,Customer Sold
COLUMN,54,Exp Arrival Time
COLUMN,55,Unique Ident
COLUMN,56,Stat Code
COLUMN,57,Stat Specify
COLUMN,58,Department Type Id
COLUMN,59,Description
COLUMN,60,Seller Code
COLUMN,61,Sel Name
COLUMN,62,Sel Department
COLUMN,63,Sel First Name
COLUMN,64,Sel Family Name
COLUMN,65,Fleet Owner Code
COLUMN,66,Specify
COLUMN,67,Customer Group
COLUMN,68,Cust Group Specify
COLUMN,69,Stock
COLUMN,70,Stock Name
COLUMN,71,Order Number
COLUMN,72,Line Number
COLUMN,73,Unit Number
COLUMN,74,Vehicle Type Vo
COLUMN,75,Register Number
COLUMN,76,Chassis Number
COLUMN,77,Vehicle Sum Old
COLUMN,78,Vehicle Sum New
COLUMN,79,Option Lines
COLUMN,80,Line Type Veh
COLUMN,81,Mileage
COLUMN,82,Accounting Code
COLUMN,83,Vehicle Type
COLUMN,84,Vehicle Type Text
COLUMN,85,Book Keeping Code
COLUMN,86,Specify
COLUMN,87,Unit Number
COLUMN,88,Basis Number
COLUMN,89,Ecc Status
COLUMN,90,Vehicle Type
COLUMN,91,Category
COLUMN,92,Pmt Term
COLUMN,93,Basis Number
COLUMN,94,Chassis Number
COLUMN,95,Car Group
COLUMN,96,Car Status
COLUMN,97,Fac Model Code L
COLUMN,98,Model Text
COLUMN,99,Orig Model Code1
COLUMN,100,Orig Model Code2
COLUMN,101,Colour Cf
COLUMN,102,Trim Txt
COLUMN,103,Model Line
COLUMN,104,Workshop Model
COLUMN,105,Fac Model Code S
COLUMN,106,Classification
COLUMN,107,Motor Number
COLUMN,108,Key Code
COLUMN,109,Year Model Cf
COLUMN,110,Cylinder Volume
COLUMN,111,Seats
COLUMN,112,Colour Code
COLUMN,113,Type Year
COLUMN,114,Ecc_Status_Text
COLUMN,115,Model Line
COLUMN,116,Mod Lin Specify
COLUMN,117,Make Cd
COLUMN,118,Workshop Model
COLUMN,119,Make Cd
COLUMN,120,Model Text
COLUMN,121,Orig Model Code
COLUMN,122,Make Code
COLUMN,123,Fahrzeugart_AB
COLUMN,124,Hauptbetrieb
COLUMN,125,Standort
COLUMN,126,Verkäufer_AB
COLUMN,127,Fabrikat_AB
COLUMN,128,Model_AB
COLUMN,129,Modellbez
COLUMN,130,Fahrgestellnr
COLUMN,131,Kunde
COLUMN,132,Kundenart_alt
COLUMN,133,Geschäftsart
COLUMN,134,Farbe
COLUMN,135,Fahrzeugtyp_AB
COLUMN,136,FZG_AB
COLUMN,137,Menge_AB
COLUMN,138,Global Make Cd
COLUMN,139,Description
COLUMN,140,Contract Date
COLUMN,141,FZG_1_AB
COLUMN,142,Status_1
COLUMN,143,Event Code Gmd
COLUMN,144,Event Date Gmd
COLUMN,145,Alloc Date Gmd
COLUMN,146,Deliv Date Gmd
COLUMN,147,FZG_2(GD70)
COLUMN,148,Kundenart
COLUMN,149,Event Code Gmd
COLUMN,150,Event Date Gmd
COLUMN,151,Alloc Date Gmd
COLUMN,152,Del Date Gmd
COLUMN,153,Hauptbetrieb_ID
COLUMN,154,Standort_ID