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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_3
DATASOURCENAME,D:\Gaps\Portal\System\IQD\NW\NW_AE_Pos_op03.imr
TITLE,NW_AE_Pos_op03.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."HANDLER" as c7,
	   T1."SALESMAN" as c8,
	   T1."ORDER_DATE" as c9,
	   T1."DELIVERY_DATE" as c10,
	   T1."INVOICE_DATE" as c11,
	   T1."FLEET_OWNER" as c12,
	   T1."OPTION_SPECIFIC" as c13,
	   T1."TITLE" as c14,
	   T1."NAME" as c15,
	   T1."VEHICLE_SOLD" as c16,
	   T1."VEHICLE_PURCH" as c17,
	   T1."CUSTOMER_GROUP" as c18,
	   T1."STOCK" as c19,
	   T1."VEH_ORDER_TYPE" as c20,
	   T1."CONTRACT_DATE" as c21,
	   T1."FAC_MODEL_CODE_L" as c22,
	   T2."LINE_NUMBER" as c23,
	   T2."UNIT_NUMBER" as c24,
	   T2."PERSON_CODE" as c25,
	   T2."VEHICLE_TYPE_VO" as c26,
	   T2."REGISTER_NUMBER" as c27,
	   T2."CHASSIS_NUMBER" as c28,
	   T2."LINE_TYPE_VEH" as c29,
	   T2."ACCOUNTING_CODE" as c30,
	   '1' as c31,
	   '03' as c32,
	   T3."SELLER_CODE" as c33,
	   T3."SEL_NAME" as c34,
	   T3."SEL_NAME" as c35,
	   T4."ECC_STATUS" as c36,
	   T4."VEHICLE_TYPE" as c37,
	   T4."BUD_SALE_DET" as c38,
	   T4."BUD_PURCH_PRICE" as c39,
	   T4."BUD_REG_FEE" as c40,
	   T4."BUD_DEDUCT_FEE" as c41,
	   T4."BUD_COST" as c42,
	   T4."PREV_OWNER" as c43,
	   T4."OWNER" as c44,
	   T4."PURCH_PRICE_UNIT" as c45,
	   T4."COST_UNIT" as c46,
	   T4."SALES_PRICE_UNIT" as c47,
	   T5."CAR_GROUP" as c48,
	   T5."CAR_STATUS" as c49,
	   T5."ORIG_INV_DATE" as c50,
	   T5."MAKE" as c51,
	   T5."MAKE_CD" as c52,
	   T5."MAKE_CD" as c53,
	   T6."MODEL_LINE" as c54,
	   T6."MOD_LIN_SPECIFY" as c55,
	   T6."MOD_LIN_SPECIFY" as c56,
	   T5."MODEL_TEXT" as c57,
	   T5."MODEL_TEXT" as c58,
	   T2."CHASSIS_NUMBER" as c59,
	   T1."DELIVERY_ACCOUNT" as c60,
	   T1."DELIVERY_ACCOUNT" || ' - ' || T1."NAME" as c61,
	   T7."FLEET_OWNER_CODE" as c62,
	   T7."SPECIFY" as c63,
	   (T1."FLEET_OWNER" || ' - ' || T7."SPECIFY") as c64,
	   ((T1."FLEET_OWNER" || ' - ' || T7."SPECIFY")) as c65,
	   T1."PMT_TERM" as c66,
	   T1."PAYMENT_TEXT" as c67,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c68,
	   (T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT") as c69,
	   T5."COLOUR_CF" as c70,
	   T5."COLOUR_CODE" as c71,
	   T5."COLOUR_CODE" || ' - ' || T5."COLOUR_CF" as c72,
	   CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END as c73,
	   CASE WHEN (T1."STATUS" IN ('18','FR')) THEN (1) WHEN (T1."STATUS" = 'FG') THEN (-1) ELSE null END as c74,
	   T2."UNIT_NUMBER" || ' - ' || T4."OWNER" || ' - ' || (asciiz(extract(YEAR FROM T5."ORIG_INV_DATE"),4) || '-' || asciiz(extract(MONTH FROM T5."ORIG_INV_DATE"),2) || '-' || asciiz(extract(DAY FROM T5."ORIG_INV_DATE"),2)) as c75,
	   T2."UNIT_NUMBER" || '-' || T2."CHASSIS_NUMBER" as c76,
	   CASE WHEN (T4."VEHICLE_TYPE" IN ('N','P','Q')) THEN ('N - Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('V','W','X')) THEN ('V - Vorführwagen') WHEN (T4."VEHICLE_TYPE" IN ('M','Y')) THEN ('M - Mietwagen/Opel Rent') WHEN (T4."VEHICLE_TYPE" IN ('R')) THEN ('R - GW regelbesteuert') WHEN (T4."VEHICLE_TYPE" IN ('D')) THEN ('D - GW §25A UStG') WHEN (T4."VEHICLE_TYPE" IN ('G')) THEN ('G - Geschäftsfahrzeug') ELSE null END as c77,
	   (database()) as c78,
	   T5."MOTOR_CODE" as c79,
	   CASE WHEN (T1."STATUS" IN ('FG','FR')) THEN ('fakturiert') ELSE ('Auftragsbestand') END as c80,
	   CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END as c81,
	   CASE WHEN ((CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '10') THEN ('10 - Privatkunde') WHEN ((CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) IN ('20','30')) THEN ('30 - Großkunde') WHEN ((CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '12') THEN ('12 - Leasing') WHEN ((CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = '80') THEN ('80 - Händlertausch') WHEN ((CASE WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') THEN ((substring(T2."ACCOUNTING_CODE" from 4 for 2))) WHEN ((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Gebrauchtwagen') THEN ('GW') ELSE null END) = 'GW') THEN ('GW') ELSE ('nicht zuzuordnen') END as c82
from "deop03"."dbo"."VEH_ORDER_HEADER" T1,
	"deop03"."dbo"."VEH_ORDER_LINE" T2,
	"deop03"."dbo"."vPP43" T3,
	"deop03"."dbo"."UNIT_FILE" T4,
	"deop03"."dbo"."VEHICLE_v" T5,
	"deop03"."dbo"."vPP5Q" T6,
	"deop03"."dbo"."vPP4K" T7
where (T1."ORDER_NUMBER" = T2."ORDER_NUMBER") and (T1."SALESMAN" = T3."SELLER_CODE") and (T2."UNIT_NUMBER" = T4."UNIT_NUMBER") and (T4."BASIS_NUMBER" = T5."BASIS_NUMBER") and (T5."MODEL_LINE" = T6."MODEL_LINE") and (T1."FLEET_OWNER" = T7."FLEET_OWNER_CODE")
and (((((CASE WHEN (T4."VEHICLE_TYPE" IN ('N','T','V','B','M','P','W','Y','Q','X')) THEN ('Neuwagen') WHEN (T4."VEHICLE_TYPE" IN ('D','R')) THEN ('Gebrauchtwagen') ELSE null END) = 'Neuwagen') and (T2."LINE_TYPE_VEH" <> 'Z')) and (T2."CHASSIS_NUMBER" <> ' ')) and (T1."INVOICE_DATE" <= TIMESTAMP '2011-12-31 00:00:00.000'))
order by c24 asc,c1 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,Handler
COLUMN,7,Salesman
COLUMN,8,Order Date
COLUMN,9,Delivery Date
COLUMN,10,Invoice Date
COLUMN,11,Fleet Owner
COLUMN,12,Option Specific
COLUMN,13,Title
COLUMN,14,Name
COLUMN,15,Vehicle Sold
COLUMN,16,Vehicle Purch
COLUMN,17,Customer Group
COLUMN,18,Stock
COLUMN,19,Veh Order Type
COLUMN,20,Contract Date
COLUMN,21,Fac Model Code L
COLUMN,22,Line Number
COLUMN,23,Unit Number
COLUMN,24,Person Code
COLUMN,25,Vehicle Type Vo
COLUMN,26,Register Number
COLUMN,27,Chassis Number
COLUMN,28,Line Type Veh
COLUMN,29,Accounting Code
COLUMN,30,Hauptbetrieb
COLUMN,31,Standort
COLUMN,32,Seller Code
COLUMN,33,Sel Name
COLUMN,34,Verkäufer
COLUMN,35,Ecc Status
COLUMN,36,Vehicle Type
COLUMN,37,Bud Sale Det
COLUMN,38,Bud Purch Price
COLUMN,39,Bud Reg Fee
COLUMN,40,Bud Deduct Fee
COLUMN,41,Bud Cost
COLUMN,42,Prev Owner
COLUMN,43,Owner
COLUMN,44,Purch Price Unit
COLUMN,45,Cost Unit
COLUMN,46,Sales Price Unit
COLUMN,47,Car Group
COLUMN,48,Car Status
COLUMN,49,Orig Inv Date
COLUMN,50,Make
COLUMN,51,Make Cd
COLUMN,52,Fabrikat
COLUMN,53,Model Line
COLUMN,54,Mod Lin Specify
COLUMN,55,Model
COLUMN,56,Model Text
COLUMN,57,Modellbez
COLUMN,58,Fahrgestellnr
COLUMN,59,Delivery Account
COLUMN,60,Kunde
COLUMN,61,Fleet Owner Code
COLUMN,62,Specify
COLUMN,63,Kundenart
COLUMN,64,Kundenart_neu
COLUMN,65,Pmt Term
COLUMN,66,Payment Text
COLUMN,67,Geschäftsart
COLUMN,68,Geschäftsart_neu
COLUMN,69,Colour Cf
COLUMN,70,Colour Code
COLUMN,71,Farbe
COLUMN,72,Fahrzeugart
COLUMN,73,Menge
COLUMN,74,FZG_1
COLUMN,75,FZG
COLUMN,76,Fahrzeugtyp
COLUMN,77,Mandant
COLUMN,78,Motor Code
COLUMN,79,Status_1
COLUMN,80,Buchungscode_ori
COLUMN,81,Buchungscode