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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\Gaps\Portal\System\IQD\Serv_Teile\Auftraege_Kopf.imr
TITLE,Auftraege_Kopf.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,
	   (od_left(T1."DEPARTMENT",2)) 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','3G','4G','FG','FS')) 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,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."INVOICE_DATE")) <= 90) THEN (((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME")) || ' - ' || (asciiz(extract(YEAR FROM T1."INVOICE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."INVOICE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."INVOICE_DATE"),2))) ELSE null END as c48,
	   T1."SPLIT_MAIN_ORDERNO" as c49,
	   T1."SPLIT_SUB1_ORDERNO" as c50,
	   T1."SPLIT_SUB2_ORDERNO" as c51,
	   T1."SPLIT_PCT_MAIN" as c52,
	   T1."SPLIT_PCT_SUB1" as c53,
	   T1."SPLIT_PCT_SUB2" as c54,
	   CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN (T1."STATUS" IN ('36','39','49')) THEN (-1) ELSE (1) END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN (T1."STATUS" IN ('36','39','49')) THEN (-1) ELSE (1) END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN (T1."STATUS" IN ('36','39','49')) THEN (-1) ELSE (1) END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN (T1."STATUS" IN ('36','39','49')) THEN (-1) ELSE (1) END)) END as c55,
	   T2."LICENCE_ID" as c56,
	   CASE WHEN ((T2."LICENCE_ID" <> '') and ((extract(DAY FROM (now()) - T1."INVOICE_DATE")) <= 30)) THEN (((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME"))) ELSE null END as c57,
	   CASE WHEN (T2."LICENCE_ID" <> '') THEN (T2."SEL_NAME") ELSE ('kein aktiver SB') END as c58
from "deop01"."dbo"."ORDER_HEADER_v" T1,
	"deop01"."dbo"."vPP43" T2
where (T1."SALESMAN" = T2."SELLER_CODE")
and ((T1."STATUS" IN ('35','37','39','47','49','50','51','36','34','3G','4G','FG','FS')) and (T1."INVOICE_DATE" >= TIMESTAMP '2021-01-01 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)_vor_Split
COLUMN,47,Order Number Rg_Ausgang
COLUMN,48,Split Main Orderno
COLUMN,49,Split Sub1 Orderno
COLUMN,50,Split Sub2 Orderno
COLUMN,51,Split Pct Main
COLUMN,52,Split Pct Sub1
COLUMN,53,Split Pct Sub2
COLUMN,54,Durchgänge (Auftrag)
COLUMN,55,Licence Id
COLUMN,56,Order Nr_SB
COLUMN,57,Serviceberater SB