COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_3
DATASOURCENAME,D:\Gaps\Portal\System\IQD\Serv_Teile\Auftraege_Kopf_op03.imr
TITLE,Auftraege_Kopf_op03.imr
BEGIN SQL
select T1."ORDER_NUMBER" as c1,
	   T1."REGISTER_NUMBER" as c2,
	   T1."STATUS" as c3,
	   T1."DEBIT_ACCOUNT" as c4,
	   T1."INVOICE_NUMBER" as c5,
	   T1."WORKSHOP_MODEL" as c6,
	   T1."DELIVERY_ACCOUNT" as c7,
	   T1."DEPARTMENT" as c8,
	   T1."SALESMAN" as c9,
	   T1."ORDER_DATE" as c10,
	   T1."COSTS" as c11,
	   T1."ORDERS_GROSSVALUE" as c12,
	   T1."DISCOUNT_AMOUNT" as c13,
	   T1."CUSTOMER_GROUP" as c14,
	   T1."BASIS_NUMBER" as c15,
	   T1."MILEAGE" as c16,
	   T1."PREV_STATUS" as c17,
	   T1."TITLE" as c18,
	   T1."NAME" as c19,
	   T1."MODEL_TEXT" as c20,
	   T1."REDUCTION_CODE" as c21,
	   T1."MAKE_CD" as c22,
	   T1."CHASSIS_NUMBER" as c23,
	   T1."INVOICE_DATE" as c24,
	   T1."PMT_TERM" as c25,
	   T1."PAYMENT_TEXT" as c26,
	   T2."SELLER_CODE" as c27,
	   T2."SEL_NAME" as c28,
	   '1' as c29,
	   '03' as c30,
	   T2."SEL_NAME" as c31,
	   CASE WHEN ((T1."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T1."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T1."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T1."CUSTOMER_GROUP" LIKE '9%') or (T1."PMT_TERM" = 'IN')) or ((od_left(T1."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c32,
	   T1."MODEL_TEXT" as c33,
	   T1."BASIS_NUMBER" || ' - ' || T1."CHASSIS_NUMBER" as c34,
	   CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '51') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as c35,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c36,
	   T1."CUSTOMER_GROUP" as c37,
	   T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME" as c38,
	   CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END as c39,
	   T2."SEL_NAME" as c40,
	   T2."SEL_NAME" as c41,
	   T1."MAKE_CD" as c42,
	   (substring(T1."DEPARTMENT" from 4 for 1)) as c43,
	   (substring(T1."DEPARTMENT" from 3 for 1)) as c44,
	   (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME") as c45,
	   (database()) as c46,
	   CASE WHEN (T1."STATUS" IN ('36','39','49')) THEN (-1) ELSE (1) END as c47
from "deop03"."dbo"."ORDER_HEADER_v" T1,
	"deop03"."dbo"."vPP43" T2
where (T1."SALESMAN" = T2."SELLER_CODE")
and ((T1."STATUS" IN ('35','37','39','47','49','50','51','36','34')) and (T1."INVOICE_DATE" <= TIMESTAMP '2011-12-31 00:00:00.000'))

END SQL
COLUMN,0,Order Number_ori
COLUMN,1,Register Number
COLUMN,2,Status
COLUMN,3,Debit Account
COLUMN,4,Invoice Number
COLUMN,5,Workshop Model
COLUMN,6,Delivery Account
COLUMN,7,Department
COLUMN,8,Salesman
COLUMN,9,Order Date
COLUMN,10,Costs
COLUMN,11,Orders Grossvalue
COLUMN,12,Discount Amount
COLUMN,13,Customer Group
COLUMN,14,Basis Number
COLUMN,15,Mileage
COLUMN,16,Prev Status
COLUMN,17,Title
COLUMN,18,Name
COLUMN,19,Model Text
COLUMN,20,Reduction Code
COLUMN,21,Make Cd
COLUMN,22,Chassis Number
COLUMN,23,Invoice Date
COLUMN,24,Pmt Term
COLUMN,25,Payment Text
COLUMN,26,Seller Code
COLUMN,27,Sel Name
COLUMN,28,Hauptbetrieb
COLUMN,29,Standort
COLUMN,30,Serviceberater
COLUMN,31,Umsatzart
COLUMN,32,Model
COLUMN,33,Fahrzeug
COLUMN,34,Auftragsart
COLUMN,35,Geschäftsart
COLUMN,36,Kundenart
COLUMN,37,Kunde
COLUMN,38,Rechnung/Gutschrift
COLUMN,39,Sel Name_Monteur
COLUMN,40,Monteur
COLUMN,41,Fabrikat
COLUMN,42,Kostenstelle
COLUMN,43,Marke
COLUMN,44,Order Number
COLUMN,45,Mandant
COLUMN,46,Durchgänge (Auftrag)