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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_3
DATASOURCENAME,D:\Gaps\Portal\System\IQD\Serv_Teile\Auftraege_op03.imr
TITLE,Auftraege_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,
	   T2."LINE_NUMBER" as c24,
	   T2."KEY_PROD_CODE" as c25,
	   T2."KEY_MAKE_CD" as c26,
	   T2."PERSON_CODE" as c27,
	   T2."PROGRAM" as c28,
	   T2."FUNCTION_CODE" as c29,
	   T2."ORDER_LINETYPE" as c30,
	   T2."ORDER_LINETYPE_2" as c31,
	   T2."ORDER_LINETYPE_3" as c32,
	   T2."REDUCTION_CODE" as c33,
	   T2."REDUCTION_AMOUNT" as c34,
	   T2."MECHANIC_CODE" as c35,
	   T2."STATUS" as c36,
	   T2."GROSS_DISCOUNT" as c37,
	   T2."LINES_NET_VALUE" as c38,
	   T2."PROD_CODE" as c39,
	   T2."PRODUCT_GROUP" as c40,
	   T2."PROD_NAME" as c41,
	   T2."ORDER_QUANTITY" as c42,
	   T2."LINE_COSTS" as c43,
	   T2."REPAIR_GROUP" as c44,
	   T2."REPAIR_CODE" as c45,
	   T2."REPAIR_NAME" as c46,
	   T2."TIME_RATE" as c47,
	   T2."USED_TIME" as c48,
	   T2."EST_TIME" as c49,
	   T2."INV_TIME" as c50,
	   T2."KIT_GROUP" as c51,
	   T2."KIT_CODE" as c52,
	   T2."WORKSHOP_MODEL" as c53,
	   T2."INV_TIME_COST" as c54,
	   T2."USED_TIME_INT" as c55,
	   T2."EST_TIME_INT" as c56,
	   T2."INV_TIME_INT" as c57,
	   T2."MAKE_TIME_UNIT" as c58,
	   T1."INVOICE_DATE" as c59,
	   T1."PMT_TERM" as c60,
	   T1."PAYMENT_TEXT" as c61,
	   T3."SELLER_CODE" as c62,
	   T3."SEL_NAME" as c63,
	   '1' as c64,
	   '03' as c65,
	   T3."SEL_NAME" as c66,
	   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 c67,
	   T1."MODEL_TEXT" as c68,
	   T1."BASIS_NUMBER" || ' - ' || T1."CHASSIS_NUMBER" as c69,
	   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 c70,
	   T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c71,
	   T1."CUSTOMER_GROUP" as c72,
	   T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME" as c73,
	   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 c74,
	   CASE WHEN ((((((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINE_COSTS") ELSE null END))) < 0) or (((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END)) < 0)) and ((CASE WHEN (T1."STATUS" IN ('35','37','47','50')) THEN ('Rechnung') WHEN (T1."STATUS" IN ('36','39','49','51')) THEN ('Gutschrift') ELSE null END) = 'Rechnung')) THEN ('VK < EK') ELSE ('VK > EK') END as c75,
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINES_NET_VALUE") ELSE null END as c76,
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINE_COSTS") ELSE null END as c77,
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END as c78,
	   CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END as c79,
	   ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '51')) THEN (T2."LINE_COSTS") ELSE null END)) as c80,
	   (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) as c81,
	   T3."SEL_NAME" as c82,
	   T3."SEL_NAME" as c83,
	   T1."MAKE_CD" as c84,
	   (substring(T1."DEPARTMENT" from 4 for 1)) as c85,
	   (substring(T1."DEPARTMENT" from 3 for 1)) as c86,
	   (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME") as c87,
	   (database()) as c88,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV')) THEN (T2."INV_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."INV_TIME" * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."INV_TIME" / 100 * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."INV_TIME" / 120 * 12) ELSE null END as c89,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV')) THEN (T2."EST_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."EST_TIME" * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."EST_TIME" / 100 * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."EST_TIME" / 120 * 12) ELSE null END as c90,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV')) THEN (T2."USED_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."USED_TIME" * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."USED_TIME" / 100 * 12) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."USED_TIME" / 120 * 12) ELSE null END as c91,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV')) THEN (T2."INV_TIME" / 12) WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."INV_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."INV_TIME" / 100) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."INV_TIME" / 120) ELSE null END as c92,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV  ')) THEN (T2."EST_TIME" / 12) WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."EST_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."EST_TIME" / 100) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."EST_TIME" / 120) ELSE null END as c93,
	   CASE WHEN ((T2."MAKE_TIME_UNIT" = 'AW12') or (T2."MAKE_TIME_UNIT" = 'CV  ')) THEN (T2."USED_TIME" / 12) WHEN (T2."MAKE_TIME_UNIT" IN ('S100')) THEN (T2."USED_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('A100')) THEN (T2."USED_TIME" / 100) WHEN (T2."MAKE_TIME_UNIT" IN ('A120')) THEN (T2."USED_TIME" / 120) ELSE null END as c94,
	   CASE WHEN ((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" <> .00)) THEN (T2."LINES_NET_VALUE") WHEN ((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" = .00)) THEN (T2."REDUCTION_AMOUNT") ELSE null END as c95,
	   CASE WHEN ((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" <> .00)) THEN (T2."LINES_NET_VALUE") WHEN ((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" = .00)) THEN (T2."LINES_NET_VALUE") ELSE null END as c96,
	   CASE WHEN ((T2."ORDER_LINETYPE" = '3') and (T2."PROD_CODE" <> 'TU')) THEN (T2."LINES_NET_VALUE") ELSE null END as c97
from "deop03"."dbo"."ORDER_HEADER_v" T1,
	"deop03"."dbo"."ORDER_LINE_v" T2,
	"deop03"."dbo"."vPP43" T3
where (T1."ORDER_NUMBER" = T2."ORDER_NUMBER") and (T1."SALESMAN" = T3."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,Line Number
COLUMN,24,Key Prod Code
COLUMN,25,Key Make Cd
COLUMN,26,Person Code
COLUMN,27,Program
COLUMN,28,Function Code
COLUMN,29,Order Linetype
COLUMN,30,Order Linetype 2
COLUMN,31,Order Linetype 3
COLUMN,32,Reduction Code
COLUMN,33,Reduction Amount
COLUMN,34,Mechanic Code
COLUMN,35,Status
COLUMN,36,Gross Discount
COLUMN,37,Lines Net Value
COLUMN,38,Prod Code
COLUMN,39,Product Group
COLUMN,40,Prod Name
COLUMN,41,Order Quantity
COLUMN,42,Line Costs
COLUMN,43,Repair Group
COLUMN,44,Repair Code
COLUMN,45,Repair Name
COLUMN,46,Time Rate
COLUMN,47,Used Time
COLUMN,48,Est Time
COLUMN,49,Inv Time
COLUMN,50,Kit Group
COLUMN,51,Kit Code
COLUMN,52,Workshop Model
COLUMN,53,Inv Time Cost
COLUMN,54,Used Time Int
COLUMN,55,Est Time Int
COLUMN,56,Inv Time Int
COLUMN,57,Make Time Unit
COLUMN,58,Invoice Date
COLUMN,59,Pmt Term
COLUMN,60,Payment Text
COLUMN,61,Seller Code
COLUMN,62,Sel Name
COLUMN,63,Hauptbetrieb
COLUMN,64,Standort
COLUMN,65,Serviceberater
COLUMN,66,Umsatzart
COLUMN,67,Model
COLUMN,68,Fahrzeug
COLUMN,69,Auftragsart
COLUMN,70,Geschäftsart
COLUMN,71,Kundenart
COLUMN,72,Kunde
COLUMN,73,Rechnung/Gutschrift
COLUMN,74,VK < EK
COLUMN,75,Umsatz Teile Service
COLUMN,76,Einsatz Teile Service
COLUMN,77,Umsatz Teile (nur Teile)
COLUMN,78,Einsatz Teile (nur Teile)
COLUMN,79,DB 1 Teile SC
COLUMN,80,DB 1 Teile T
COLUMN,81,Sel Name_Monteur
COLUMN,82,Monteur
COLUMN,83,Fabrikat
COLUMN,84,Kostenstelle
COLUMN,85,Marke
COLUMN,86,Order Number
COLUMN,87,Mandant
COLUMN,88,verk. AW
COLUMN,89,Soll AW
COLUMN,90,benutzte AW
COLUMN,91,verk. Stunden
COLUMN,92,Soll-Stunden (Auftrag)
COLUMN,93,benutzte Zeit (Auftrag)
COLUMN,94,Umsatz Lohn (geleistet)
COLUMN,95,Umsatz Lohn (abgerechnet)
COLUMN,96,Umsatz Sonstiges