COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_op21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\service_del_deop21.imr
TITLE,service_del_deop21.imr
BEGIN SQL
select 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,
	   T2."ORDER_NUMBER" as c22,
	   T2."STATUS" as c23,
	   T2."INVOICE_NUMBER" as c24,
	   T2."INVOICE_DATE" as c25,
	   T3."LINE_NUMBER" as c26,
	   T3."DELETE_DATE" as c27,
	   T3."DELETE_TIME" as c28,
	   T3."KEY_PROD_CODE" as c29,
	   T3."MAKE_CD" as c30,
	   T3."ORDER_LINETYPE" as c31,
	   T3."PROD_NAME" as c32,
	   T3."STOCK" as c33,
	   T3."LOCATION" as c34,
	   T3."ORDER_QUANTITY" as c35,
	   T3."ORDER_SALES_PRICE" as c36,
	   T3."DISCOUNT_PERC" as c37,
	   T3."DISCOUNT_PERC_2" as c38,
	   T3."LINES_NET_VALUE" as c39,
	   T3."PRINT_CODE" as c40,
	   T3."CONDITION_CODE" as c41,
	   T3."LOGIN" as c42,
	   T3."PROGRAM" as c43,
	   T3."FUNCTION_CODE" as c44,
	   T3."REGISTER_NUMBER" as c45,
	   T3."DEBIT_ACCOUNT" as c46,
	   T3."STATUS" as c47,
	   T3."DEPARTMENT" as c48,
	   T3."SALESMAN" as c49,
	   T3."PLACE_CODE" as c50,
	   T3."TAX_CODE" as c51,
	   T3."TAX_PERC" as c52,
	   T3."VAT_TABLE_NORMAL" as c53,
	   T3."VAT_TABLE_WORK" as c54,
	   T3."WORKSHOP_MODEL" as c55,
	   T3."COSTS" as c56,
	   T3."PRICE_CODE" as c57,
	   T3."INTERNAL_CODE" as c58,
	   T3."SMALL_ACCESSORIES" as c59,
	   T3."INVOICE_DISC_PERC" as c60,
	   T3."SMALL_INV_CHARGE" as c61,
	   T3."WORKSHOP_PRICECODE" as c62,
	   T3."REDUCTION_CODE" as c63,
	   T3."DISCOUNT_PERC_BIG" as c64,
	   T3."DISPATCH_FEE_PCT" as c65,
	   T3."SPLIT_TYPE" as c66,
	   T3."CRED_LIMIT_EXCEED" as c67,
	   T3."DEBIT_PERM" as c68,
	   T3."TAX_CODE_1" as c69,
	   T3."VAT_RATE_CODE" as c70,
	   T3."DELIVERY_QUANTITY" as c71,
	   T3."BACK_ORDER" as c72,
	   T3."USED_TIME" as c73,
	   T3."INV_TIME" as c74,
	   T3."RE_PURCHASE_PRICE" as c75,
	   T3."ADD_COST_CODE" as c76,
	   T3."SPEC_PURCH_PRICE" as c77,
	   T3."LINE_COSTS" as c78,
	   T3."SPECIAL_PRICE" as c79,
	   T3."NO_QUANT_ON_HAND" as c80,
	   T3."COST_PRICE_WORK" as c81,
	   T3."TIME_CODE_1" as c82,
	   T3."USED_TIME_TYPE_1" as c83,
	   T3."COST_PRICE_TYPE_1" as c84,
	   T3."TIME_CODE_2" as c85,
	   T3."USED_TIME_TYPE_2" as c86,
	   T3."COST_PRICE_TYPE_2" as c87,
	   T3."JOB_CODE" as c88,
	   T3."LINE_TEXTLINE_1" as c89,
	   T3."LINE_TEXTLINE_2" as c90,
	   T3."LINE_TEXTLINE_3" as c91,
	   T3."QUANTITY_CHANGE" as c92,
	   T3."RETURN_REASON_CODE" as c93,
	   T3."QTY_SOLD_NEGATIVE" as c94,
	   T3."ORIG_COST_PRICE" as c95,
	   T3."LINE_STATUS" as c96,
	   T3."CONV_FLAG" as c97,
	   T3."UNIQUE_IDENT" as c98,
	   T3."PRINT_OWNER_DECL_TEXT" as c99,
	   T3."PRINT_PAYER_DECL_TEXT" as c100,
	   T3."PRINT_USER_DECL_TEXT" as c101,
	   '1' as c102,
	   (od_left(T1."DEPARTMENT",2)) as c103,
	   (substring(T1."DEPARTMENT" from 3 for 1)) as c104,
	   (substring(T4."DEPARTMENT_TYPE_ID" from 4 for 1)) as c105,
	   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 c106,
	   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 c107,
	   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 c108,
	   T5."SEL_NAME" as c109,
	   T5."SEL_NAME" as c110,
	   T6."CUSTOMER_GROUP" as c111,
	   T6."NAME" as c112,
	   T6."DEBIT_CUSTOMER" as c113,
	   CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN ((((T6."CUSTOMER_GROUP" LIKE '9%') or (T2."PMT_TERM" = 'IN')) or (T6."CUSTOMER_GROUP" IS NULL)) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c114,
	   CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T3."LINES_NET_VALUE") ELSE null END as c115,
	   CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINES_NET_VALUE") ELSE null END as c116,
	   CASE WHEN (T3."ORDER_LINETYPE" = '4') THEN (T3."LINES_NET_VALUE") ELSE null END as c117,
	   CASE WHEN (T3."ORDER_LINETYPE" = '3') THEN (T3."LINES_NET_VALUE") ELSE null END as c118,
	   CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T3."LINE_COSTS") ELSE null END as c119,
	   CASE WHEN ((T3."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T3."LINE_COSTS") ELSE null END as c120,
	   CASE WHEN (1 = 0) THEN (1) ELSE null END as c121,
	   (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 c122,
	   T2."DELIVERY_DATE" as c123,
	   (database()) as c124
from ((((("deop21"."dbo"."ORDER_HEADER_DEL" T1 left outer join "deop21"."dbo"."ORDER_HEADER" T2 on T2."ORDER_NUMBER" = T1."ORDER_NUMBER") left outer join "deop21"."dbo"."ORDER_LINE_DEL" T3 on T1."ORDER_NUMBER" = T3."ORDER_NUMBER") left outer join "deop21"."dbo"."DEPARTMENT_TYPE" T4 on T1."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop21"."dbo"."vPP43" T5 on T1."SALESMAN" = T5."SELLER_CODE") left outer join "deop21"."dbo"."CUSTOMER" T6 on T1."DEBIT_CUSTOMER" = T6."CUSTOMER_NUMBER")
where ((T1."DELETE_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000') and (T2."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,Order Number_Auftrag
COLUMN,22,Status_Auftrag
COLUMN,23,Invoice Number
COLUMN,24,Invoice Date
COLUMN,25,Line Number
COLUMN,26,Delete Date_Position
COLUMN,27,Delete Time
COLUMN,28,Key Prod Code
COLUMN,29,Make Cd
COLUMN,30,Order Linetype
COLUMN,31,Prod Name
COLUMN,32,Stock
COLUMN,33,Location
COLUMN,34,Order Quantity
COLUMN,35,Order Sales Price
COLUMN,36,Discount Perc
COLUMN,37,Discount Perc 2
COLUMN,38,Lines Net Value
COLUMN,39,Print Code
COLUMN,40,Condition Code
COLUMN,41,Login
COLUMN,42,Program
COLUMN,43,Function Code
COLUMN,44,Register Number
COLUMN,45,Debit Account
COLUMN,46,Status
COLUMN,47,Department
COLUMN,48,Salesman
COLUMN,49,Place Code
COLUMN,50,Tax Code
COLUMN,51,Tax Perc
COLUMN,52,Vat Table Normal
COLUMN,53,Vat Table Work
COLUMN,54,Workshop Model
COLUMN,55,Costs
COLUMN,56,Price Code
COLUMN,57,Internal Code
COLUMN,58,Small Accessories
COLUMN,59,Invoice Disc Perc
COLUMN,60,Small Inv Charge
COLUMN,61,Workshop Pricecode
COLUMN,62,Reduction Code
COLUMN,63,Discount Perc Big
COLUMN,64,Dispatch Fee Pct
COLUMN,65,Split Type
COLUMN,66,Cred Limit Exceed
COLUMN,67,Debit Perm
COLUMN,68,Tax Code 1
COLUMN,69,Vat Rate Code
COLUMN,70,Delivery Quantity
COLUMN,71,Back Order
COLUMN,72,Used Time
COLUMN,73,Inv Time
COLUMN,74,Re Purchase Price
COLUMN,75,Add Cost Code
COLUMN,76,Spec Purch Price
COLUMN,77,Line Costs
COLUMN,78,Special Price
COLUMN,79,No Quant On Hand
COLUMN,80,Cost Price Work
COLUMN,81,Time Code 1
COLUMN,82,Used Time Type 1
COLUMN,83,Cost Price Type 1
COLUMN,84,Time Code 2
COLUMN,85,Used Time Type 2
COLUMN,86,Cost Price Type 2
COLUMN,87,Job Code
COLUMN,88,Line Textline 1
COLUMN,89,Line Textline 2
COLUMN,90,Line Textline 3
COLUMN,91,Quantity Change
COLUMN,92,Return Reason Code
COLUMN,93,Qty Sold Negative
COLUMN,94,Orig Cost Price
COLUMN,95,Line Status
COLUMN,96,Conv Flag
COLUMN,97,Unique Ident
COLUMN,98,Print Owner Decl Text
COLUMN,99,Print Payer Decl Text
COLUMN,100,Print User Decl Text
COLUMN,101,Hauptbetrieb
COLUMN,102,Standort
COLUMN,103,Marke
COLUMN,104,Kostenstelle
COLUMN,105,Auftragsart
COLUMN,106,Kunde
COLUMN,107,Rechnung/Gutschrift
COLUMN,108,Sel Name
COLUMN,109,Serviceberater
COLUMN,110,Customer Group
COLUMN,111,Name_Kunde
COLUMN,112,Debit Customer_Kunde
COLUMN,113,Umsatzart
COLUMN,114,Umsatz Teile Service
COLUMN,115,Umsatz Teile (nur Teile)
COLUMN,116,Umsatz Lohn
COLUMN,117,Umsatz Sonstiges
COLUMN,118,Einsatz Teile Service
COLUMN,119,Einsatz Teile (nur Teile)
COLUMN,120,Durchgänge (Auftrag)
COLUMN,121,Order Number
COLUMN,122,Delivery Date
COLUMN,123,Mandant