GAPS/Portal/System/IQD/NW/NW_AE_op26.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_op26
DATASOURCENAME,C:\GAPS\Portal\System\IQD\NW\NW_AE_op26.imr
TITLE,NW_AE_op26
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."TEXT_CODE_1" as c23,
	   T1."TEXT_CODE_2" as c24,
	   T1."TEXT_CODE_3" as c25,
	   T1."TEXT1_1_VO" as c26,
	   T1."TEXT1_2_VO" as c27,
	   T1."TEXT_FL1_VO" as c28,
	   T1."TEXT2_1_VO" as c29,
	   T1."TEXT2_2_VO" as c30,
	   T1."TEXT_FL2_VO" as c31,
	   T1."TEXT3_1_VO" as c32,
	   T1."TEXT3_2_VO" as c33,
	   T1."TEXT_FL3_VO" as c34,
	   T1."TEXT4_1_VO" as c35,
	   T1."TEXT4_2_VO" as c36,
	   T1."TEXT_FL4_VO" as c37,
	   T1."FLEET_OWNER" as c38,
	   T1."OPTION_SPECIFIC" as c39,
	   T1."VEHICLE_LINES_VO" as c40,
	   T1."ORDER_SUM_VO" as c41,
	   T1."REQ_NO" as c42,
	   T1."REFERENCE_NUMBER" as c43,
	   T1."DUEDATE_1" as c44,
	   T1."TITLE" as c45,
	   T1."NAME" as c46,
	   T1."STREET_ADDR" as c47,
	   T1."ADDR_2" as c48,
	   T1."ZIPCODE" as c49,
	   T1."MAIL_ADDR" as c50,
	   T1."ADDR_3" as c51,
	   T1."COUNTY" as c52,
	   T1."VEHICLE_SOLD" as c53,
	   T1."VEHICLE_SOLD_SUM" as c54,
	   T1."VEHICLE_PURCH" as c55,
	   T1."VEHICLE_PURCH_SUM" as c56,
	   T1."CHAIN_NUMBER" as c57,
	   T1."SYSTEM_INV_PERM" as c58,
	   T1."INTERNAL_CODE" as c59,
	   T1."PREV_STATUS" as c60,
	   T1."CREDIT_ORDER_VEH" as c61,
	   T1."TAX_HANDLING" as c62,
	   T1."TAX_PERC" as c63,
	   T1."DELIVERY_WAY_CODE" as c64,
	   T1."DELIVERY_TERM" as c65,
	   T1."CUSTOMER_GROUP" as c66,
	   T1."PRICE_CODE" as c67,
	   T1."STOCK" as c68,
	   T1."PLACE_CODE_2" as c69,
	   T1."INVOICE_DISC_PERC" as c70,
	   T1."ORDER_ARR_DATE_VO" as c71,
	   T1."OFFER" as c72,
	   T1."VEH_ORDER_TYPE" as c73,
	   T1."MAIN_ORDER_NO" as c74,
	   T1."SUB_ORDER_NO_1" as c75,
	   T1."SUB_ORDER_NO_2" as c76,
	   T1."SUB_ORDER_NO_3" as c77,
	   T1."SUB_ORDER_NO_4" as c78,
	   T1."SUB_ORDER_NO_5" as c79,
	   T1."SUB_ORDER_NO_6" as c80,
	   T1."SUB_ORDER_NO_7" as c81,
	   T1."SUB_ORDER_NO_8" as c82,
	   T1."SUB_ORDER_NO_9" as c83,
	   T1."SUB_ORDER_NO_10" as c84,
	   T1."TO_MICROFICHE" as c85,
	   T1."LANGUAGE_CODE" as c86,
	   T1."TAX_CODE_VO" as c87,
	   T1."INV_ROUNDING_VO" as c88,
	   T1."DEBT_NEW" as c89,
	   T1."CASH_AMOUNT" as c90,
	   T1."CNTRACT_DUE_DATE" as c91,
	   T1."CONTRACT_DATE" as c92,
	   T1."FAC_MODEL_CODE_L" as c93,
	   T1."CUSTOMER_SOLD" as c94,
	   T1."EXP_ARRIVAL_TIME" as c95,
	   T1."BOOK_NO" as c96,
	   T1."BOOK_NO_FLAG" as c97,
	   T1."REFERENCE_DATE" as c98,
	   T1."DEBET_DEPARTMENT" as c99,
	   T1."CUSTOMER_EAN_NUMBER" as c100,
	   T1."EAN_DEPARTMENT" as c101,
	   T1."EAN_REFERENCE" as c102,
	   T1."EAN_INTERNAL_ACCT" as c103,
	   T1."NWFAKT_MARKING" as c104,
	   T1."EXT_ORDER_NUMBER" as c105,
	   T1."DEAL_ID" as c106,
	   T1."PREORDER_ID" as c107,
	   T1."CONV_FLAG" as c108,
	   T1."UNIQUE_IDENT" as c109,
	   T2."STAT_CODE" as c110,
	   T2."STAT_SPECIFY" as c111,
	   T3."DEPARTMENT_TYPE_ID" as c112,
	   T3."DESCRIPTION" as c113,
	   T4."SELLER_CODE" as c114,
	   T4."SEL_NAME" as c115,
	   T4."SEL_DEPARTMENT" as c116,
	   T4."SEL_FIRST_NAME" as c117,
	   T4."SEL_FAMILY_NAME" as c118,
	   T5."FLEET_OWNER_CODE" as c119,
	   T5."SPECIFY" as c120,
	   T6."CUSTOMER_GROUP" as c121,
	   T6."CUST_GROUP_SPECIFY" as c122,
	   T7."STOCK" as c123,
	   T7."STOCK_NAME" as c124,
	   T8."ORDER_NUMBER" as c125,
	   T8."LINE_NUMBER" as c126,
	   T8."UNIT_NUMBER" as c127,
	   T8."VEHICLE_TYPE_VO" as c128,
	   T8."REGISTER_NUMBER" as c129,
	   T8."CHASSIS_NUMBER" as c130,
	   T8."VEHICLE_SUM_OLD" as c131,
	   T8."VEHICLE_SUM_NEW" as c132,
	   T8."OPTION_LINES" as c133,
	   T8."LINE_TYPE_VEH" as c134,
	   T8."MILEAGE" as c135,
	   T8."ACCOUNTING_CODE" as c136,
	   T9."VEHICLE_TYPE" as c137,
	   T9."VEHICLE_TYPE_TEXT" as c138,
	   T10."BOOK_KEEPING_CODE" as c139,
	   T10."SPECIFY" as c140,
	   T11."UNIT_NUMBER" as c141,
	   T11."BASIS_NUMBER" as c142,
	   T11."ECC_STATUS" as c143,
	   T11."VEHICLE_TYPE" as c144,
	   T11."CATEGORY" as c145,
	   T11."PMT_TERM" as c146,
	   T12."BASIS_NUMBER" as c147,
	   T12."CHASSIS_NUMBER" as c148,
	   T12."CAR_GROUP" as c149,
	   T12."CAR_STATUS" as c150,
	   T12."FAC_MODEL_CODE_L" as c151,
	   T12."MODEL_TEXT" as c152,
	   T12."ORIG_MODEL_CODE1" as c153,
	   T12."ORIG_MODEL_CODE2" as c154,
	   T12."COLOUR_CF" as c155,
	   T12."TRIM_TXT" as c156,
	   T12."MODEL_LINE" as c157,
	   T12."WORKSHOP_MODEL" as c158,
	   T12."FAC_MODEL_CODE_S" as c159,
	   T12."CLASSIFICATION" as c160,
	   T12."MOTOR_NUMBER" as c161,
	   T12."KEY_CODE" as c162,
	   T12."YEAR_MODEL_CF" as c163,
	   T12."CYLINDER_VOLUME" as c164,
	   T12."SEATS" as c165,
	   T12."MISC_1_CF" as c166,
	   T12."MISC_2_CF" as c167,
	   T12."MISC_3_CF" as c168,
	   T12."MISC_4_CF" as c169,
	   T12."MISC_5_CF" as c170,
	   T12."MISC_6_CF" as c171,
	   T12."MISC_7_CF" as c172,
	   T12."MISC_8_CF" as c173,
	   T12."MISC_9_CF" as c174,
	   T12."MISC_10_CF" as c175,
	   T12."MOTOR_CODE" as c176,
	   T12."GEAR_CODE" as c177,
	   T12."DRIVE_CODE" as c178,
	   T12."COLOUR_CODE" as c179,
	   T12."TRIM_CODE" as c180,
	   T12."TYPE_YEAR" as c181,
	   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 c182,
	   T13."MODEL_LINE" as c183,
	   T13."MOD_LIN_SPECIFY" as c184,
	   T13."MAKE_CD" as c185,
	   T14."WORKSHOP_MODEL" as c186,
	   T14."MAKE_CD" as c187,
	   T14."MODEL_TEXT" as c188,
	   T14."ORIG_MODEL_CODE" as c189,
	   T14."MAKE_CODE" as c190,
	   CASE WHEN (T9."VEHICLE_TYPE" IN ('N','T','V')) THEN ('Neuwagen') WHEN (T9."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END as c191,
	   '1' as c192,
	   (od_left(T3."DEPARTMENT_TYPE_ID",2)) as c193,
	   T4."SEL_NAME" as c194,
	   T13."MAKE_CD" as c195,
	   T13."MOD_LIN_SPECIFY" as c196,
	   T12."MODEL_TEXT" as c197,
	   T12."CHASSIS_NUMBER" as c198,
	   T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME" as c199,
	   T6."CUSTOMER_GROUP" || ' - ' || T6."CUST_GROUP_SPECIFY" as c200,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c201
from (((((((("deop26"."dbo"."VEH_ORDER_HEADER" T1 left outer join "deop26"."dbo"."vPP25" T2 on T1."STATUS" = T2."STAT_CODE") left outer join "deop26"."dbo"."DEPARTMENT_TYPE" T3 on T1."DEPARTMENT" = T3."DEPARTMENT_TYPE_ID") left outer join "deop26"."dbo"."vPP43" T4 on T1."SALESMAN" = T4."SELLER_CODE") left outer join "deop26"."dbo"."vPP4K" T5 on T1."FLEET_OWNER" = T5."FLEET_OWNER_CODE") left outer join "deop26"."dbo"."vPP48" T6 on T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") left outer join "deop26"."dbo"."vPP61" T7 on T1."STOCK" = T7."STOCK") left outer join "deop26"."dbo"."VEH_ORDER_LINE" T8 on T1."ORDER_NUMBER" = T8."ORDER_NUMBER") left outer join "deop26"."dbo"."vPP5M" T10 on T8."ACCOUNTING_CODE" = T10."BOOK_KEEPING_CODE"),
	(((("deop26"."dbo"."UNIT_FILE" T11 left outer join "deop26"."dbo"."vPP5R" T9 on T11."VEHICLE_TYPE" = T9."VEHICLE_TYPE") left outer join "deop26"."dbo"."VEHICLE" T12 on T11."BASIS_NUMBER" = T12."BASIS_NUMBER") left outer join "deop26"."dbo"."vPP5Q" T13 on T12."MODEL_LINE" = T13."MODEL_LINE") left outer join "deop26"."dbo"."vPP74" T14 on (T12."WORKSHOP_MODEL" = T14."WORKSHOP_MODEL") and (T12."MAKE_CD" = T14."MAKE_CD"))
where (T8."UNIT_NUMBER" = T11."UNIT_NUMBER")
and (((T8."LINE_NUMBER" IS NULL) or (T8."LINE_NUMBER" = 5)) and ((CASE WHEN (T9."VEHICLE_TYPE" IN ('N','T','V')) THEN ('Neuwagen') WHEN (T9."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen'))
order by c127 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,Text Code 1
COLUMN,23,Text Code 2
COLUMN,24,Text Code 3
COLUMN,25,Text1 1 Vo
COLUMN,26,Text1 2 Vo
COLUMN,27,Text Fl1 Vo
COLUMN,28,Text2 1 Vo
COLUMN,29,Text2 2 Vo
COLUMN,30,Text Fl2 Vo
COLUMN,31,Text3 1 Vo
COLUMN,32,Text3 2 Vo
COLUMN,33,Text Fl3 Vo
COLUMN,34,Text4 1 Vo
COLUMN,35,Text4 2 Vo
COLUMN,36,Text Fl4 Vo
COLUMN,37,Fleet Owner
COLUMN,38,Option Specific
COLUMN,39,Vehicle Lines Vo
COLUMN,40,Order Sum Vo
COLUMN,41,Req No
COLUMN,42,Reference Number
COLUMN,43,Duedate 1
COLUMN,44,Title
COLUMN,45,Name
COLUMN,46,Street Addr
COLUMN,47,Addr 2
COLUMN,48,Zipcode
COLUMN,49,Mail Addr
COLUMN,50,Addr 3
COLUMN,51,County
COLUMN,52,Vehicle Sold
COLUMN,53,Vehicle Sold Sum
COLUMN,54,Vehicle Purch
COLUMN,55,Vehicle Purch Sum
COLUMN,56,Chain Number
COLUMN,57,System Inv Perm
COLUMN,58,Internal Code
COLUMN,59,Prev Status
COLUMN,60,Credit Order Veh
COLUMN,61,Tax Handling
COLUMN,62,Tax Perc
COLUMN,63,Delivery Way Code
COLUMN,64,Delivery Term
COLUMN,65,Customer Group
COLUMN,66,Price Code
COLUMN,67,Stock
COLUMN,68,Place Code 2
COLUMN,69,Invoice Disc Perc
COLUMN,70,Order Arr Date Vo
COLUMN,71,Offer
COLUMN,72,Veh Order Type
COLUMN,73,Main Order No
COLUMN,74,Sub Order No 1
COLUMN,75,Sub Order No 2
COLUMN,76,Sub Order No 3
COLUMN,77,Sub Order No 4
COLUMN,78,Sub Order No 5
COLUMN,79,Sub Order No 6
COLUMN,80,Sub Order No 7
COLUMN,81,Sub Order No 8
COLUMN,82,Sub Order No 9
COLUMN,83,Sub Order No 10
COLUMN,84,To Microfiche
COLUMN,85,Language Code
COLUMN,86,Tax Code Vo
COLUMN,87,Inv Rounding Vo
COLUMN,88,Debt New
COLUMN,89,Cash Amount
COLUMN,90,Cntract Due Date
COLUMN,91,Contract Date
COLUMN,92,Fac Model Code L
COLUMN,93,Customer Sold
COLUMN,94,Exp Arrival Time
COLUMN,95,Book No
COLUMN,96,Book No Flag
COLUMN,97,Reference Date
COLUMN,98,Debet Department
COLUMN,99,Customer Ean Number
COLUMN,100,Ean Department
COLUMN,101,Ean Reference
COLUMN,102,Ean Internal Acct
COLUMN,103,Nwfakt Marking
COLUMN,104,Ext Order Number
COLUMN,105,Deal Id
COLUMN,106,Preorder Id
COLUMN,107,Conv Flag
COLUMN,108,Unique Ident
COLUMN,109,Stat Code
COLUMN,110,Stat Specify
COLUMN,111,Department Type Id
COLUMN,112,Description
COLUMN,113,Seller Code
COLUMN,114,Sel Name
COLUMN,115,Sel Department
COLUMN,116,Sel First Name
COLUMN,117,Sel Family Name
COLUMN,118,Fleet Owner Code
COLUMN,119,Specify
COLUMN,120,Customer Group
COLUMN,121,Cust Group Specify
COLUMN,122,Stock
COLUMN,123,Stock Name
COLUMN,124,Order Number
COLUMN,125,Line Number
COLUMN,126,Unit Number
COLUMN,127,Vehicle Type Vo
COLUMN,128,Register Number
COLUMN,129,Chassis Number
COLUMN,130,Vehicle Sum Old
COLUMN,131,Vehicle Sum New
COLUMN,132,Option Lines
COLUMN,133,Line Type Veh
COLUMN,134,Mileage
COLUMN,135,Accounting Code
COLUMN,136,Vehicle Type
COLUMN,137,Vehicle Type Text
COLUMN,138,Book Keeping Code
COLUMN,139,Specify
COLUMN,140,Unit Number
COLUMN,141,Basis Number
COLUMN,142,Ecc Status
COLUMN,143,Vehicle Type
COLUMN,144,Category
COLUMN,145,Pmt Term
COLUMN,146,Basis Number
COLUMN,147,Chassis Number
COLUMN,148,Car Group
COLUMN,149,Car Status
COLUMN,150,Fac Model Code L
COLUMN,151,Model Text
COLUMN,152,Orig Model Code1
COLUMN,153,Orig Model Code2
COLUMN,154,Colour Cf
COLUMN,155,Trim Txt
COLUMN,156,Model Line
COLUMN,157,Workshop Model
COLUMN,158,Fac Model Code S
COLUMN,159,Classification
COLUMN,160,Motor Number
COLUMN,161,Key Code
COLUMN,162,Year Model Cf
COLUMN,163,Cylinder Volume
COLUMN,164,Seats
COLUMN,165,Misc 1 Cf
COLUMN,166,Misc 2 Cf
COLUMN,167,Misc 3 Cf
COLUMN,168,Misc 4 Cf
COLUMN,169,Misc 5 Cf
COLUMN,170,Misc 6 Cf
COLUMN,171,Misc 7 Cf
COLUMN,172,Misc 8 Cf
COLUMN,173,Misc 9 Cf
COLUMN,174,Misc 10 Cf
COLUMN,175,Motor Code
COLUMN,176,Gear Code
COLUMN,177,Drive Code
COLUMN,178,Colour Code
COLUMN,179,Trim Code
COLUMN,180,Type Year
COLUMN,181,Ecc_Status_Text
COLUMN,182,Model Line
COLUMN,183,Mod Lin Specify
COLUMN,184,Make Cd
COLUMN,185,Workshop Model
COLUMN,186,Make Cd
COLUMN,187,Model Text
COLUMN,188,Orig Model Code
COLUMN,189,Make Code
COLUMN,190,Fahrzeugart
COLUMN,191,Hauptbetrieb
COLUMN,192,Standort
COLUMN,193,Verkäufer
COLUMN,194,Fabrikat
COLUMN,195,Model
COLUMN,196,Modellbez
COLUMN,197,Fahrgestellnr
COLUMN,198,Kunde
COLUMN,199,Kundenart
COLUMN,200,Geschäftsart