COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GlobalCube\system\OPTIMA\IQD\Serv_Teile\offene_Auftraege.imr TITLE,offene_Auftraege.imr BEGIN SQL select T1."ORDER_NUMBER" as c1, T1."REGISTER_NUMBER" as c2, T1."STATUS" as c3, T1."STATE_KEY_DATE" as c4, T1."DEBIT_ACCOUNT" as c5, T1."INVOICE_NUMBER" as c6, T1."WORKSHOP_MODEL" as c7, T1."STATE_CODE" as c8, T1."TRANSACT_DATE" as c9, T1."HANDLER" as c10, T1."DELIVERY_ACCOUNT" as c11, T1."DEPARTMENT" as c12, T1."DEBET_DEPARTMENT" as c13, T1."SALESMAN" as c14, T1."DEBIT_PERM" as c15, T1."ORDER_DATE" as c16, T1."DELIVERY_DATE" as c17, T1."INVOICE_DATE" as c18, T1."TAX_CODE" as c19, T1."TAX_PERC" as c20, T1."PMT_TERM" as c21, T1."NEXT_LINE_NUMBER" as c22, T1."DUEDATE_1" as c23, T1."PAYMENT_TEXT" as c24, T1."COSTS" as c25, T1."PURCH_TAX" as c26, T1."ORDERS_GROSSVALUE" as c27, T1."TAX_SHARE" as c28, T1."DISCOUNT_AMOUNT" as c29, T1."PRICE_CODE" as c30, T1."MISC_ADDS" as c31, T1."STOCK" as c32, T1."PLACE_CODE" as c33, T1."INTERNAL_CODE" as c34, T1."CUSTOMER_GROUP" as c35, T1."SMALL_ACCESSORIES" as c36, T1."INVOICE_COPY_CODE" as c37, T1."BASIS_NUMBER" as c38, T1."MILEAGE" as c39, T1."PREV_STATUS" as c40, T1."SALES_CLASS_NUMBER" as c41, T1."INVOICE_DISC_PERC" as c42, T1."INVOICE_ROUNDED" as c43, T1."INVOICE_CHARGE" as c44, T1."SALES_TAX_FREE" as c45, T1."TITLE" as c46, T1."NAME" as c47, T1."STREET_ADDR" as c48, T1."ADDR_2" as c49, T1."ZIPCODE" as c50, T1."MAIL_ADDR" as c51, T1."DISCOUNT_LIMIT" as c52, T1."REFERENCE_NUMBER" as c53, T1."EXPECTED_ORDER_TIM" as c54, T1."BOL_TAX_SHARE" as c55, T1."MODEL_TEXT" as c56, T1."WORKSHOP_PRICECODE" as c57, T1."SPLIT_COUNTER" as c58, T1."ARRIVAL_TIME" as c59, T1."ARRIVAL_DATE" as c60, T1."END_DATE" as c61, T1."END_TIME" as c62, T1."FAC_MODEL_CODE_S" as c63, T1."MAKE_CD" as c64, T1."YEAR_MODEL" as c65, T1."TRANSFER_MAKE_CD" as c66, T1."CHASSIS_NUMBER" as c67, T1."WORKSHOP_TEAM" as c68, T1."COMMISSION_SALESMAN" as c69, T1."REF_IDENT_INV_TOTAL" as c70, T1."REF_IDENT_SALES_CLASS" as c71, T1."USE_PARTS_PRE_PICKING" as c72, T1."LDC_ORDER" as c73, T1."FHG_REPORT" as c74, T1."ACTUAL_INV_DATE_TIME" as c75, T1."CONV_FLAG" as c76, T1."UNIQUE_IDENT" as c77, T2."ORDER_NUMBER" as c78, T2."LINE_NUMBER" as c79, T2."ORDER_LINETYPE" as c80, T2."REDUCTION_CODE" as c81, T2."REDUCTION_AMOUNT" as c82, T2."MECHANIC_CODE" as c83, T2."SALESMAN" as c84, T2."DISCOUNT" as c85, T2."STDPRICE" as c86, T2."LINES_NET_VALUE" as c87, T2."PROD_CODE" as c88, T2."MAKE_CD" as c89, T2."PRODUCT_GROUP" as c90, T2."PROD_NAME" as c91, T2."ORDER_QUANTITY" as c92, T2."DELIVERY_QUANTITY" as c93, T2."LINE_COSTS" as c94, T2."REPAIR_CODE" as c95, T2."REPAIR_GROUP" as c96, T2."REPAIR_NAME" as c97, T2."USED_TIME" as c98, T2."EST_TIME" as c99, T2."INV_TIME" as c100, T2."USED_TIME_INT" as c101, T2."EST_TIME_INT" as c102, T2."INV_TIME_INT" as c103, T2."MAKE_TIME_UNIT" as c104, T2."UNIQUE_IDENT" as c105, T3."STAT_CODE" as c106, T3."STAT_SPECIFY" as c107, T4."DEPARTMENT_TYPE_ID" as c108, T4."DESCRIPTION" as c109, T5."SELLER_CODE" as c110, T5."SEL_NAME" as c111, T5."SEL_DEPARTMENT" as c112, T5."SEL_FIRST_NAME" as c113, T5."SEL_FAMILY_NAME" as c114, T6."CUSTOMER_GROUP" as c115, T6."CUST_GROUP_SPECIFY" as c116, T7."SELLER_CODE" as c117, T7."SEL_NAME" as c118, T7."SEL_DEPARTMENT" as c119, T7."SEL_FIRST_NAME" as c120, T7."SEL_FAMILY_NAME" as c121, T8."REPAIR_GROUP" as c122, T8."MAKE_CD" as c123, 12 as c124, T9."DESCRIPTION" as c125, T8."REPAIR_GRP_SPECIFY" as c126, T1."CLIENT_DB" as c127, CASE WHEN ((T1."ORDER_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END as c128, CASE WHEN ((CASE WHEN ((T1."ORDER_DATE" <= TIMESTAMP '2019-11-22 00:00:00.000') and ((od_left(T1."DEPARTMENT",2)) = '11')) THEN ('111') ELSE ((od_left(T1."DEPARTMENT",2))) END) IN ('05','09','111','06','08','01','10','14','12','03','04','02')) THEN ('1') ELSE ((substring(T1."DEPARTMENT" from 2 for 1))) END as c129, (substring(T1."DEPARTMENT" from 3 for 2)) as c130, T6."CUSTOMER_GROUP" || ' - ' || T6."CUST_GROUP_SPECIFY" as c131, CASE WHEN (T1."CUSTOMER_GROUP" BETWEEN '10' AND '65') THEN ('extern') WHEN (T1."CUSTOMER_GROUP" IN ('91','92')) THEN ('GWL') WHEN (((T1."CUSTOMER_GROUP" IN ('99')) or (T1."PMT_TERM" = 'IN')) or ((od_left(T1."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c132, T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c133, T5."SEL_NAME" as c134, T7."SEL_NAME" as c135, CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '49') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as c136, T1."STATUS" || ' - ' || T3."STAT_SPECIFY" as c137, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '49')) THEN (T2."LINES_NET_VALUE") ELSE null END as c138, CASE WHEN (T2."INV_TIME" <> 0) THEN (T2."LINES_NET_VALUE") ELSE null END as c139, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END as c140, CASE WHEN (T1."STATUS" IN ('35','37','39','47','49','34','36')) THEN ('Rechnung/Gutschrift') WHEN (T1."STATUS" IN ('30','40','32')) THEN ('offen') WHEN (T1."STATUS" IN ('41','31')) THEN ('Kostenvoranschlag') ELSE null END as c141, T2."INV_TIME_INT" as c142, T2."EST_TIME_INT" as c143, T2."USED_TIME_INT" as c144, CASE WHEN (T2."ORDER_LINETYPE" = '3') THEN (T2."LINES_NET_VALUE") ELSE null END as c145, T2."INV_TIME_INT" * 12 as c146, T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME" as c147, T2."EST_TIME_INT" * 12 as c148, T2."USED_TIME_INT" * 12 as c149, T9."GLOBAL_MAKE_CD" as c150, T9."DESCRIPTION" as c151, T10."MOD_LIN_SPECIFY" as c152, T1."BASIS_NUMBER" || ' - ' || T1."CHASSIS_NUMBER" as c153, (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (substring((asciiz(extract(YEAR FROM T1."ORDER_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."ORDER_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."ORDER_DATE"),2)) from 1 for 10)) || ' - ' || T1."DELIVERY_ACCOUNT" || T1."NAME" || ' - ' || T1."REGISTER_NUMBER" || ' - ' || T1."STATUS" as c154, (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (substring((asciiz(extract(YEAR FROM T1."ORDER_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."ORDER_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."ORDER_DATE"),2)) from 1 for 10)) || ' - ' || T1."DELIVERY_ACCOUNT" || T1."NAME" || ' - ' || T1."REGISTER_NUMBER" || ' - ' || T1."STATUS" as c155, (extract(DAY FROM (now()) - T1."ORDER_DATE")) as c156, (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || (T1."DEBIT_ACCOUNT" || ' - ' || T1."NAME") as c157, CASE WHEN (T9."DESCRIPTION" IN ('Opel')) THEN (T9."DESCRIPTION") ELSE ('Fremdfabrikat') END as c158 from (((((((((("OPTIMA"."import"."ORDER_HEADER" T1 left outer join "OPTIMA"."import"."ORDER_LINE" T2 on (T1."ORDER_NUMBER" = T2."ORDER_NUMBER") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP25" T3 on (T1."STATUS" = T3."STAT_CODE") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on (T1."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") and (T1."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T5 on (T1."SALESMAN" = T5."SELLER_CODE") and (T1."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP48" T6 on (T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T7 on (T2."MECHANIC_CODE" = T7."SELLER_CODE") and (T2."CLIENT_DB" = T7."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP73" T8 on ((T2."REPAIR_GROUP" = T8."REPAIR_GROUP") and (T2."MAKE_CD" = T8."MAKE_CD")) and (T2."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."import"."GLOBAL_MAKE" T9 on (T1."MAKE_CD" = T9."GLOBAL_MAKE_CD") and (T1."CLIENT_DB" = T9."CLIENT_DB")) left outer join "OPTIMA"."import"."VEHICLE" T11 on ((T11."CHASSIS_NUMBER" = T1."CHASSIS_NUMBER") and (T11."BASIS_NUMBER" = T1."BASIS_NUMBER")) and (T11."CLIENT_DB" = T1."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP5Q" T10 on ((T11."MODEL_LINE" = T10."MODEL_LINE") and (T11."MAKE_CD" = T10."MAKE_CD")) and (T11."CLIENT_DB" = T10."CLIENT_DB")) where ((not T1."STATUS" IN ('35','37','39','47','49','34','FR','36','91','69','4G','67','63','32','41')) and (T1."INVOICE_DATE" = TIMESTAMP '1800-01-01 00:00:00.000')) order by c1 asc END SQL COLUMN,0,Order Number_ori COLUMN,1,Register Number COLUMN,2,Status COLUMN,3,State Key Date COLUMN,4,Debit Account COLUMN,5,Invoice Number COLUMN,6,Workshop Model COLUMN,7,State Code COLUMN,8,Transact Date COLUMN,9,Handler COLUMN,10,Delivery Account COLUMN,11,Department COLUMN,12,Debet Department COLUMN,13,Salesman COLUMN,14,Debit Perm COLUMN,15,Order Date COLUMN,16,Delivery Date COLUMN,17,Invoice Date COLUMN,18,Tax Code COLUMN,19,Tax Perc COLUMN,20,Pmt Term COLUMN,21,Next Line Number COLUMN,22,Duedate 1 COLUMN,23,Payment Text COLUMN,24,Costs COLUMN,25,Purch Tax COLUMN,26,Orders Grossvalue COLUMN,27,Tax Share COLUMN,28,Discount Amount COLUMN,29,Price Code COLUMN,30,Misc Adds COLUMN,31,Stock COLUMN,32,Place Code COLUMN,33,Internal Code COLUMN,34,Customer Group COLUMN,35,Small Accessories COLUMN,36,Invoice Copy Code COLUMN,37,Basis Number COLUMN,38,Mileage COLUMN,39,Prev Status COLUMN,40,Sales Class Number COLUMN,41,Invoice Disc Perc COLUMN,42,Invoice Rounded COLUMN,43,Invoice Charge COLUMN,44,Sales Tax Free COLUMN,45,Title COLUMN,46,Name COLUMN,47,Street Addr COLUMN,48,Addr 2 COLUMN,49,Zipcode COLUMN,50,Mail Addr COLUMN,51,Discount Limit COLUMN,52,Reference Number COLUMN,53,Expected Order Tim COLUMN,54,Bol Tax Share COLUMN,55,Model Text COLUMN,56,Workshop Pricecode COLUMN,57,Split Counter COLUMN,58,Arrival Time COLUMN,59,Arrival Date COLUMN,60,End Date COLUMN,61,End Time COLUMN,62,Fac Model Code S COLUMN,63,Make Cd COLUMN,64,Year Model COLUMN,65,Transfer Make Cd COLUMN,66,Chassis Number COLUMN,67,Workshop Team COLUMN,68,Commission Salesman COLUMN,69,Ref Ident Inv Total COLUMN,70,Ref Ident Sales Class COLUMN,71,Use Parts Pre Picking COLUMN,72,Ldc Order COLUMN,73,Fhg Report COLUMN,74,Actual Inv Date Time COLUMN,75,Conv Flag COLUMN,76,Unique Ident COLUMN,77,Order Number COLUMN,78,Line Number COLUMN,79,Order Linetype COLUMN,80,Reduction Code COLUMN,81,Reduction Amount COLUMN,82,Mechanic Code COLUMN,83,Salesman COLUMN,84,Discount COLUMN,85,Stdprice COLUMN,86,Lines Net Value COLUMN,87,Prod Code COLUMN,88,Make Cd COLUMN,89,Product Group COLUMN,90,Prod Name COLUMN,91,Order Quantity COLUMN,92,Delivery Quantity COLUMN,93,Line Costs COLUMN,94,Repair Code COLUMN,95,Repair Group COLUMN,96,Repair Name COLUMN,97,Used Time COLUMN,98,Est Time COLUMN,99,Inv Time COLUMN,100,Used Time Int COLUMN,101,Est Time Int COLUMN,102,Inv Time Int COLUMN,103,Make Time Unit COLUMN,104,Unique Ident COLUMN,105,Stat Code COLUMN,106,Stat Specify COLUMN,107,Department Type Id COLUMN,108,Description COLUMN,109,Seller Code COLUMN,110,Sel Name COLUMN,111,Sel Department COLUMN,112,Sel First Name COLUMN,113,Sel Family Name COLUMN,114,Customer Group COLUMN,115,Cust Group Specify COLUMN,116,Seller Code COLUMN,117,Sel Name_Monteur COLUMN,118,Sel Department COLUMN,119,Sel First Name_Monteur COLUMN,120,Sel Family Name_Monteur COLUMN,121,Repair Group COLUMN,122,Make Cd COLUMN,123,AW_Faktor COLUMN,124,Fabrikat_ori COLUMN,125,Repair Grp Specify COLUMN,126,Hauptbetrieb COLUMN,127,Standort COLUMN,128,Marke COLUMN,129,Kostenstelle COLUMN,130,Kundenart COLUMN,131,Umsatzart COLUMN,132,Geschäftsart COLUMN,133,Serviceberater COLUMN,134,Monteur COLUMN,135,Auftragsart COLUMN,136,Auftragsstatus COLUMN,137,Umsatz Teile Service COLUMN,138,Umsatz Lohn COLUMN,139,Umsatz Teile (nur Teile) COLUMN,140,Status_1 COLUMN,141,verk. Stunden COLUMN,142,Soll-Stunden (Auftrag) COLUMN,143,benutzte Zeit (Auftrag) COLUMN,144,Umsatz Sonstiges COLUMN,145,verk. AW COLUMN,146,Kunde COLUMN,147,Soll AW COLUMN,148,benutzte AW COLUMN,149,Global Make Cd COLUMN,150,Description COLUMN,151,Model COLUMN,152,Fahrzeug COLUMN,153,Auftrag_Det_S COLUMN,154,Auftrag_Det_T COLUMN,155,Anzahl Tage COLUMN,156,Order Number COLUMN,157,Fabrikat