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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\Gaps\Portal\System\IQD\Serv_Teile\Auftraege.imr
TITLE,Auftraege.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,
	   (od_left(T1."DEPARTMENT",2)) 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','3G','4G','FS','FG')) 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','3G','4G','FS','FG')) 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 (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T2."INV_TIME_INT") END) * 12 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 (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T2."USED_TIME_INT") END) * 12 as c91,
	   T2."INV_TIME_INT" as c92,
	   T2."EST_TIME_INT" as c93,
	   T2."USED_TIME_INT" 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,
	   CASE WHEN (T1."STATUS" = '41') THEN ('Kostenvoranschlag') ELSE ('offener Auftrag') END as c98,
	   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 c99,
	   T1."SPLIT_MAIN_ORDERNO" as c100,
	   T1."SPLIT_SUB1_ORDERNO" as c101,
	   T1."SPLIT_SUB2_ORDERNO" as c102,
	   T1."SPLIT_PCT_MAIN" as c103,
	   T1."SPLIT_PCT_SUB1" as c104,
	   T1."SPLIT_PCT_SUB2" as c105,
	   CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T2."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T2."USED_TIME_INT") END as c106,
	   CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN (T2."INV_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100)) ELSE (T2."INV_TIME_INT") END as c107,
	   T3."LICENCE_ID" as c108,
	   CASE WHEN ((T3."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 c109,
	   CASE WHEN (T3."LICENCE_ID" <> '') THEN (T3."SEL_NAME") ELSE ('kein aktiver SB') END as c110
from "deop01"."dbo"."ORDER_HEADER_v" T1,
	"deop01"."dbo"."ORDER_LINE_v" T2,
	"deop01"."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','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,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_vor_Split
COLUMN,92,Soll-Stunden (Auftrag)
COLUMN,93,benutzte Zeit (Auftrag)_vor_Split
COLUMN,94,Umsatz Lohn (geleistet)
COLUMN,95,Umsatz Lohn (abgerechnet)
COLUMN,96,Umsatz Sonstiges
COLUMN,97,Auftragsstatus
COLUMN,98,Order Number Rg_Ausgang
COLUMN,99,Split Main Orderno
COLUMN,100,Split Sub1 Orderno
COLUMN,101,Split Sub2 Orderno
COLUMN,102,Split Pct Main
COLUMN,103,Split Pct Sub1
COLUMN,104,Split Pct Sub2
COLUMN,105,benutzte Zeit (Auftrag)
COLUMN,106,verk. Stunden
COLUMN,107,Licence Id
COLUMN,108,Order Nr_SB
COLUMN,109,Serviceberater SB