GAPS/Portal/System/IQD/Serv_Teile/service_del_kopf.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\service_del_kopf.imr
TITLE,service_del_kopf.imr
BEGIN SQL
select distinct T1."ORDER_NUMBER" as c1,
	   T1."REGISTER_NUMBER" as c2,
	   T1."DEBIT_CUSTOMER" as c3,
	   T1."DELETE_DATE" as c4,
	   T1."DELETE_TIME" as c5,
	   T1."STATUS" as c6,
	   T1."STATE_KEY_DATE" as c7,
	   T1."DEPARTMENT" as c8,
	   T1."SALESMAN" as c9,
	   T1."ORDERS_GROSSVALUE" as c10,
	   T1."DISCOUNT_AMOUNT" as c11,
	   T1."PLACE_CODE" as c12,
	   T1."NAME" as c13,
	   T1."PRINT_CODE" as c14,
	   T1."CONDITION_CODE" as c15,
	   T1."TOTAL_DELETE_DATE" as c16,
	   T1."LOGIN" as c17,
	   T1."PROGRAM" as c18,
	   T1."FUNCTION_CODE" as c19,
	   T1."CONV_FLAG" as c20,
	   T1."UNIQUE_IDENT" as c21,
	   T1."INTERNAL_CODE" as c22,
	   T1."SPLIT_TYPE_2" as c23,
	   1 as c24,
	   '1' as c25,
	   (od_left(T1."DEPARTMENT",2)) as c26,
	   (substring(T1."DEPARTMENT" from 3 for 1)) as c27,
	   (substring(T2."DEPARTMENT_TYPE_ID" from 4 for 1)) as c28,
	   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 c29,
	   T1."DEBIT_CUSTOMER" || ' - ' || T1."NAME" || ' - ' || (asciiz(extract(YEAR FROM T1."DELETE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DELETE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DELETE_DATE"),2)) as c30,
	   CASE WHEN ((T3."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T3."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T3."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN ((((T3."CUSTOMER_GROUP" LIKE '9%') or (T4."PMT_TERM" = 'IN')) or (T3."CUSTOMER_GROUP" IS NULL)) or ((od_left(T3."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c31,
	   T5."SEL_NAME" as c32,
	   (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || T1."LOGIN" || ' - ' || (T1."DEBIT_CUSTOMER" || ' - ' || T1."NAME" || ' - ' || (asciiz(extract(YEAR FROM T1."DELETE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DELETE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DELETE_DATE"),2))) as c33
from (((("deop01"."dbo"."ORDER_HEADER_DEL" T1 left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T2 on T1."DEPARTMENT" = T2."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."CUSTOMER" T3 on T1."DEBIT_CUSTOMER" = T3."CUSTOMER_NUMBER") left outer join "deop01"."dbo"."ORDER_HEADER" T4 on T1."ORDER_NUMBER" = T4."ORDER_NUMBER") left outer join "deop01"."dbo"."vPP43" T5 on T1."SALESMAN" = T5."SELLER_CODE")
where ((T1."DELETE_DATE" >= TIMESTAMP '2012-01-01 00:00:00.000') and (T4."STATUS" IS NULL))
order by c1 asc

END SQL
COLUMN,0,Order Number_ori
COLUMN,1,Register Number
COLUMN,2,Debit Customer
COLUMN,3,Delete Date
COLUMN,4,Delete Time
COLUMN,5,Status
COLUMN,6,State Key Date
COLUMN,7,Department
COLUMN,8,Salesman
COLUMN,9,Orders Grossvalue
COLUMN,10,Discount Amount
COLUMN,11,Place Code
COLUMN,12,Name
COLUMN,13,Print Code
COLUMN,14,Condition Code
COLUMN,15,Total Delete Date
COLUMN,16,Login
COLUMN,17,Program
COLUMN,18,Function Code
COLUMN,19,Conv Flag
COLUMN,20,Unique Ident
COLUMN,21,Internal Code
COLUMN,22,Split Type 2
COLUMN,23,Durchgänge (Auftrag)
COLUMN,24,Hauptbetrieb
COLUMN,25,Standort
COLUMN,26,Marke
COLUMN,27,Kostenstelle
COLUMN,28,Auftragsart
COLUMN,29,Kunde
COLUMN,30,Umsatzart
COLUMN,31,Serviceberater
COLUMN,32,Order Number