COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GAPS\Portal\System\IQD\Serv_Teile\Auftraege_SPP_Split.imr TITLE,Auftraege_SPP_Split.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."PMT_TERM" as c19, T1."DUEDATE_1" as c20, T1."PAYMENT_TEXT" as c21, T1."COSTS" as c22, T1."PURCH_TAX" as c23, T1."ORDERS_GROSSVALUE" as c24, T1."TAX_SHARE" as c25, T1."DISCOUNT_AMOUNT" as c26, T1."PRICE_CODE" as c27, T1."MISC_ADDS" as c28, T1."STOCK" as c29, T1."PLACE_CODE" as c30, T1."INTERNAL_CODE" as c31, T1."CUSTOMER_GROUP" as c32, T1."BASIS_NUMBER" as c33, T1."MILEAGE" as c34, T1."PREV_STATUS" as c35, T1."SALES_CLASS_NUMBER" as c36, T1."INVOICE_DISC_PERC" as c37, T1."SALES_TAX_FREE" as c38, T1."TITLE" as c39, T1."NAME" as c40, T1."STREET_ADDR" as c41, T1."ADDR_2" as c42, T1."ZIPCODE" as c43, T1."MAIL_ADDR" as c44, T1."DISCOUNT_LIMIT" as c45, T1."REFERENCE_NUMBER" as c46, T1."EXPECTED_ORDER_TIM" as c47, T1."MODEL_TEXT" as c48, T1."WORKSHOP_PRICECODE" as c49, T1."SPLIT_COUNTER" as c50, T1."ARRIVAL_TIME" as c51, T1."ARRIVAL_DATE" as c52, T1."END_DATE" as c53, T1."END_TIME" as c54, T1."FAC_MODEL_CODE_S" as c55, T1."MAKE_CD" as c56, T1."YEAR_MODEL" as c57, T1."TRANSFER_MAKE_CD" as c58, T1."CHASSIS_NUMBER" as c59, T1."WORKSHOP_TEAM" as c60, T1."COMMISSION_SALESMAN" as c61, T1."ACTUAL_INV_DATE_TIME" as c62, T1."UNIQUE_IDENT" as c63, T2."ORDER_NUMBER" as c64, T2."LINE_NUMBER" as c65, T2."KEY_PROD_CODE" as c66, T2."KEY_MAKE_CD" as c67, T2."DELIVERY_DATE" as c68, T2."LINE_GROUP_CODE" as c69, T2."HANDLER" as c70, T2."STATE_CODE" as c71, T2."PERSON_CODE" as c72, T2."ORDER_LINETYPE" as c73, T2."ORDER_LINETYPE_2" as c74, T2."ORDER_LINETYPE_3" as c75, T2."RESULTING_LINE" as c76, T2."RESULTING_LINE_2" as c77, T2."PRINT_INVOICE" as c78, T2."REDUCTION_CODE" as c79, T2."REDUCTION_AMOUNT" as c80, T2."REDUCTION_CODE_2" as c81, T2."REDUCTION_POS" as c82, T2."SPLIT_CODE" as c83, T2."SPLIT_CODE_2" as c84, T2."MECHANIC_CODE" as c85, T2."SALESMAN" as c86, T2."DISCOUNT_TABLE" as c87, T2."DISCOUNT_CODE" as c88, T2."CLAIM_CODE_IND" as c89, T2."CLAIM_NUMBER" as c90, T2."KIT_FIXED_PRICE_CD" as c91, T2."R_DELIVERY_DATE" as c92, T2."WUST_CODE" as c93, T2."STATUS" as c94, T2."STOCK" as c95, T2."GROSS_DISCOUNT" as c96, T2."STATISTIC_CODE" as c97, T2."DECIMAL_INDICATOR" as c98, T2."ADD_COST_CODE" as c99, T2."DISCOUNT_PERC" as c100, T2."DISCOUNT_PERC_2" as c101, T2."DISCOUNT" as c102, T2."STDPRICE" as c103, T2."LINES_NET_VALUE" as c104, T2."TAX_CODE_1" as c105, T2."VAT_RATE_CODE" as c106, T2."TAX_C_U" as c107, T2."TAX_DRAWN" as c108, T2."LINE_MISC_ADDS" as c109, T2."LINE_MISC_ADDS_PER" as c110, T2."CLAIM_TROUBLE_CD" as c111, T2."PROD_CODE" as c112, T2."MAKE_CD" as c113, T2."PRODUCT_GROUP" as c114, T2."PROD_NAME" as c115, T2."LOCATION" as c116, T2."ORDER_QUANTITY" as c117, T2."DELIVERY_QUANTITY" as c118, T2."LINE_COSTS" as c119, T2."CLAIM_RETURN_CODE" as c120, T2."INV_TOTAL_NUMBER" as c121, T2."SPECIAL_PRICE" as c122, T2."SALES_PRICE" as c123, T2."SALES_PRICE_INCL" as c124, T2."ORIG_ORDER_NO" as c125, T2."ORIG_LINE_NO" as c126, T2."ORIG_CREATION_CODE" as c127, T2."BACK_ORDER_CD" as c128, T2."BACK_ORDER" as c129, T2."INTERNAL_TRANSFER" as c130, T2."STDPRICE_2" as c131, T2."QUANTITY_DISC_CODE" as c132, T2."CAMPAIGN_CODE" as c133, T2."REPLACE_CODE" as c134, T2."N_MAIN_PART_NO" as c135, T2."LINE_TEXTLINE_1" as c136, T2."LINE_TEXTLINE_2" as c137, T2."LINE_TEXTLINE_3" as c138, T2."LINE_TEXT_CODE" as c139, T2."LINE_CODE" as c140, T2."LINE_WORKSHOP" as c141, T2."BACK_REG_CODE" as c142, T2."REQUISITION_NUM" as c143, T2."REPAIR_CODE" as c144, T2."REPAIR_GROUP" as c145, T2."REPAIR_NAME" as c146, T2."RATE_PRICE_CODE" as c147, T2."TIME_RATE" as c148, T2."USED_TIME" as c149, T2."EST_TIME" as c150, T2."INV_TIME" as c151, T2."REPAIR_CODE_2" as c152, T2."REPAIR_POS" as c153, T2."LINE_COMMENT" as c154, T2."INSPECTION_1" as c155, T2."INSPECTION_2" as c156, T2."WORK_DATE" as c157, T2."START_TIME" as c158, T2."END_DATE" as c159, T2."END_TIME" as c160, T2."WORKSHOP_AREA" as c161, T2."W_MAIN_REPAIR_NO" as c162, T2."KIT_GROUP" as c163, T2."KIT_CODE" as c164, T2."WORKSHOP_MODEL" as c165, T2."KIT_TEXT" as c166, T2."KIT_FIXED_PRICE" as c167, T2."CLASS_RULE" as c168, T2."KIT_ONE_LINE" as c169, T2."KIT_ONE_LINEPRICE" as c170, T2."KIT_CATALOG_CODE" as c171, T2."KIT_PARTS_DISC_PER" as c172, T2."KIT_WORK_DISC_PER" as c173, T2."KIT_MISC_DISC_PER" as c174, T2."KIT_DELETE" as c175, T2."KIT_PRICE_DIFF" as c176, T2."KIT_PRINT_SPLIT" as c177, T2."TYPE_OL" as c178, T2."TRANSACTION_CODE_2" as c179, T2."TXT_OL" as c180, T2."AMOUNT_OL" as c181, T2."CASH_ACCOUNT" as c182, T2."REPAIR_CODE_ACCU" as c183, T2."RECOMMENDED_PRICE" as c184, T2."COST_PRICE_WORK" as c185, T2."TIME_CODE_1" as c186, T2."USED_TIME_TYPE_1" as c187, T2."COST_PRICE_TYPE_1" as c188, T2."TIME_CODE_2" as c189, T2."USED_TIME_TYPE_2" as c190, T2."COST_PRICE_TYPE_2" as c191, T2."DISCOUNT_ALLOWANCE" as c192, T2."CREATE_AFTER_RC" as c193, T2."REPEAT_REPAIR" as c194, T2."INV_TIME_COST" as c195, T2."TIME_CODE" as c196, T2."FACTOR_CODE" as c197, T2."REDUCTION_FACTOR_1" as c198, T2."RESULTING_LINE_1" as c199, T2."EXTERNAL_STOCK" as c200, T2."REPAIR_GROUP_TYPE" as c201, T2."AUTOMATIC_CREATED" as c202, T2."INCOMPLETE_LINE" as c203, T2."INCOMPLETE_LINE_2" as c204, T2."ORDER_LINETYPE_4" as c205, T2."CONSIGNMENT_CODE" as c206, T2."PART_GROUP" as c207, T2."MISC_1" as c208, T2."MISC_2" as c209, T2."MISC_3" as c210, T2."MISC_4" as c211, T2."MISC_5" as c212, T2."MISC_6" as c213, T2."ORDER_LINE_TRANSM" as c214, T2."ORIG_PROD_CODE" as c215, T2."ORIG_MAKE_CD" as c216, T2."ORIG_PROD_GROUP" as c217, T2."STOCK_SHOP_LIST" as c218, T2."LINE_REFERENCE" as c219, T2."WORK_TYPE_CODE" as c220, T2."CUSTOMER_COMPLAINT" as c221, T2."JOB_CODE_DESCR" as c222, T2."TEXT_200" as c223, T2."CREATE_MSC" as c224, T2."SPECIAL_LOCK_NO" as c225, T2."SUPPLIER_CODE" as c226, T2."REQUISITION_NUMBER" as c227, T2."USED_TIME_INT" as c228, T2."EST_TIME_INT" as c229, T2."INV_TIME_INT" as c230, T2."MAKE_TIME_UNIT" as c231, T2."DEPOSIT_TYPE" as c232, T2."LINE_STATUS" as c233, T2."SALES_UNIT_STD" as c234, T2."SALES_UNIT_ALT" as c235, T2."ORDER_QUANTITY_ALT_UNIT" as c236, T2."OIL" as c237, T2."DEDUCTIBLE_VAT" as c238, T2."QTY_SOLD_NEGATIVE" as c239, T2."ORIG_COST_PRICE" as c240, T2."REQUESTED_QUANTITY" as c241, T2."UNIQUE_IDENT" as c242, T3."STAT_CODE" as c243, T3."STAT_SPECIFY" as c244, T4."DEPARTMENT_TYPE_ID" as c245, T4."DESCRIPTION" as c246, T5."SELLER_CODE" as c247, T5."SEL_NAME" as c248, T5."SEL_DEPARTMENT" as c249, T5."SEL_FIRST_NAME" as c250, T5."SEL_FAMILY_NAME" as c251, T6."CUSTOMER_GROUP" as c252, T6."CUST_GROUP_SPECIFY" as c253, T7."SELLER_CODE" as c254, T7."SEL_NAME" as c255, T7."SEL_DEPARTMENT" as c256, T7."SEL_FIRST_NAME" as c257, T7."SEL_FAMILY_NAME" as c258, T8."REPAIR_GROUP" as c259, T8."MAKE_CD" as c260, T8."REPAIR_GRP_SPECIFY" as c261, (rtrim(T1."PRICE_CODE")) || ' - ' || (rtrim(T1."WORKSHOP_PRICECODE")) as c262, '1' as c263, (od_left(T4."DEPARTMENT_TYPE_ID",2)) as c264, (substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1)) as c265, ((substring(T4."DEPARTMENT_TYPE_ID" from 4 for 1))) as c266, T6."CUSTOMER_GROUP" || ' - ' || T6."CUST_GROUP_SPECIFY" as c267, CASE WHEN (((T1."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T1."CUSTOMER_GROUP" LIKE '7%')) or (T1."CUSTOMER_GROUP" LIKE '8%')) 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 c268, T1."PMT_TERM" || ' - ' || T1."PAYMENT_TEXT" as c269, CASE WHEN (T5."SEL_NAME" IS NULL) THEN (T1."SALESMAN") ELSE (T5."SEL_NAME") END as c270, T7."SEL_NAME" as c271, CASE WHEN (T1."STATUS" BETWEEN '30' AND '39') THEN ('Teile') WHEN (T1."STATUS" BETWEEN '40' AND '59') THEN ('Service') WHEN (T1."STATUS" = '70') THEN ('sonst. Auftrag') WHEN (T1."STATUS" = '91') THEN ('Anfrage') ELSE null END as c272, T1."STATUS" || ' - ' || T3."STAT_SPECIFY" as c273, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINES_NET_VALUE") ELSE null END as c274, CASE WHEN (((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" <> .00)) or ((T2."PROD_CODE" = 'EP') and (T2."STDPRICE" <> 0))) THEN (T2."LINES_NET_VALUE") ELSE null END as c275, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END as c276, 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 c277, T2."INV_TIME_INT" as c278, T2."EST_TIME_INT" as c279, T2."USED_TIME_INT" as c280, CASE WHEN ((T2."ORDER_LINETYPE" = '3') and (not T2."PROD_CODE" IN ('TU','EP'))) THEN (T2."LINES_NET_VALUE") ELSE null END as c281, CASE WHEN (T2."MAKE_TIME_UNIT" = 'AW12') THEN (T2."INV_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('S100','CV')) 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) WHEN (T2."MAKE_TIME_UNIT" IN ('AW10')) THEN (T2."INV_TIME" / 10 * 12) ELSE null END as c282, (rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || ((rtrim(T1."PRICE_CODE")) || ' - ' || (rtrim(T1."WORKSHOP_PRICECODE"))) as c283, T2."EST_TIME_INT" * 12 as c284, CASE WHEN (T2."MAKE_TIME_UNIT" = 'AW12') THEN (T2."USED_TIME") WHEN (T2."MAKE_TIME_UNIT" IN ('S100','CV')) 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) WHEN (T2."MAKE_TIME_UNIT" IN ('AW10')) THEN (T2."USED_TIME" / 10 * 12) ELSE null END as c285, 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 c286, 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 c287, 12 as c288, T9."GLOBAL_MAKE_CD" as c289, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END)) END as c290, CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END)) END as c291, T9."DESCRIPTION" as c292, T9."DESCRIPTION" as c293, T1."MODEL_TEXT" as c294, T1."BASIS_NUMBER" || ' - ' || T1."REGISTER_NUMBER" as c295, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END as c296, CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END as c297, 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 c298, T2."LINES_NET_VALUE" - T2."LINE_COSTS" as c299, ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END)) END)) as c300, (CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END)) END) as c301, CASE WHEN ((((((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINES_NET_VALUE") ELSE null END) - (CASE WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '40' AND '59')) THEN (T2."LINE_COSTS") ELSE null END)) 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 (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_MAIN" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB1" / 100)) WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO") THEN ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END) * (T1."SPLIT_PCT_SUB2" / 100)) ELSE ((CASE WHEN ((T2."ORDER_LINETYPE" = '1') and (T1."STATUS" BETWEEN '30' AND '39')) THEN (T2."LINE_COSTS") ELSE null END)) 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 c302, (database()) as c303, (od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || ((rtrim(T1."PRICE_CODE")) || ' - ' || (rtrim(T1."WORKSHOP_PRICECODE")))) as c304, CASE WHEN ((extract(DAY FROM (now()) - T1."INVOICE_DATE")) <= 7) THEN (((od_left((cast_numberToString(cast_integer(T1."ORDER_NUMBER"))),7)) || ' - ' || ((rtrim(T1."DEBIT_ACCOUNT")) || ' - ' || T1."NAME" || ' - ' || ((rtrim(T1."PRICE_CODE")) || ' - ' || (rtrim(T1."WORKSHOP_PRICECODE"))))) || ' - ' || (asciiz(extract(YEAR FROM T1."INVOICE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."INVOICE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."INVOICE_DATE"),2)) || ' - ' || (CASE WHEN (T5."SEL_NAME" IS NULL) THEN (T1."SALESMAN") ELSE (T5."SEL_NAME") END)) ELSE null END as c305, (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 c306, (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 c307, CASE WHEN (T9."DESCRIPTION" IN ('Opel','Honda')) THEN (T9."DESCRIPTION") ELSE ('Fremdfabrikat') END as c308, T10."FIRST_REG_DATE" as c309, CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END as c310, (CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365 as c311, CASE WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 0.01 AND 0.99) THEN ('1') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 1.00 AND 1.99) THEN ('2') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 2.00 AND 2.99) THEN ('3') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 3.00 AND 3.99) THEN ('4') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 4.00 AND 4.99) THEN ('5') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 5.00 AND 5.99) THEN ('6') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 6.00 AND 6.99) THEN ('7') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 7.00 AND 7.99) THEN ('8') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 8.00 AND 8.99) THEN ('9') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) BETWEEN 9.00 AND 9.99) THEN ('10') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) > 9.99) THEN ('> 10') WHEN (((CASE WHEN (T10."FIRST_REG_DATE" <> TIMESTAMP '1800-01-01 00:00:00.000') THEN ((extract(DAY FROM T1."INVOICE_DATE" - T10."FIRST_REG_DATE"))) ELSE (0) END) / 365) = 0) THEN ('keine Angabe') ELSE null END as c312, CASE WHEN ((T2."INV_TIME" <> 0) and (T2."LINES_NET_VALUE" = .00)) THEN (T2."REDUCTION_AMOUNT" * -1) WHEN ((T2."ORDER_LINETYPE" = '3') and (T2."PROD_CODE" = 'EP')) THEN (T2."LINES_NET_VALUE") ELSE null END as c313, CASE WHEN (T2."ORDER_LINETYPE" = '1') THEN (T2."DISCOUNT") ELSE (0) END as c314, CASE WHEN (T2."ORDER_LINETYPE" <> '1') THEN (T2."DISCOUNT") ELSE (0) END as c315 from ((((((((("deop01"."dbo"."ORDER_HEADER" T1 left outer join "deop01"."dbo"."ORDER_LINE" T2 on T1."ORDER_NUMBER" = T2."ORDER_NUMBER") left outer join "deop01"."dbo"."vPP25" T3 on T1."STATUS" = T3."STAT_CODE") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T4 on T1."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."vPP43" T5 on T1."SALESMAN" = T5."SELLER_CODE") left outer join "deop01"."dbo"."vPP48" T6 on T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP") left outer join "deop01"."dbo"."vPP43" T7 on T2."MECHANIC_CODE" = T7."SELLER_CODE") left outer join "deop01"."dbo"."vPP73" T8 on (T2."REPAIR_GROUP" = T8."REPAIR_GROUP") and (T2."MAKE_CD" = T8."MAKE_CD")) left outer join "deop01"."dbo"."GLOBAL_MAKE" T9 on T1."MAKE_CD" = T9."GLOBAL_MAKE_CD") left outer join "deop01"."dbo"."VEHICLE" T10 on (T1."CHASSIS_NUMBER" = T10."CHASSIS_NUMBER") and (T1."BASIS_NUMBER" = T10."BASIS_NUMBER")) where ((T1."STATUS" IN ('35','37','39','47','49','50','51','36','34')) and (T1."INVOICE_DATE" >= TIMESTAMP '2017-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,Pmt Term COLUMN,19,Duedate 1 COLUMN,20,Payment Text COLUMN,21,Costs COLUMN,22,Purch Tax COLUMN,23,Orders Grossvalue COLUMN,24,Tax Share COLUMN,25,Discount Amount COLUMN,26,Price Code COLUMN,27,Misc Adds COLUMN,28,Stock COLUMN,29,Place Code COLUMN,30,Internal Code COLUMN,31,Customer Group COLUMN,32,Basis Number COLUMN,33,Mileage COLUMN,34,Prev Status COLUMN,35,Sales Class Number COLUMN,36,Invoice Disc Perc COLUMN,37,Sales Tax Free COLUMN,38,Title COLUMN,39,Name COLUMN,40,Street Addr COLUMN,41,Addr 2 COLUMN,42,Zipcode COLUMN,43,Mail Addr COLUMN,44,Discount Limit COLUMN,45,Reference Number COLUMN,46,Expected Order Tim COLUMN,47,Model Text COLUMN,48,Workshop Pricecode COLUMN,49,Split Counter COLUMN,50,Arrival Time COLUMN,51,Arrival Date COLUMN,52,End Date COLUMN,53,End Time COLUMN,54,Fac Model Code S COLUMN,55,Make Cd COLUMN,56,Year Model COLUMN,57,Transfer Make Cd COLUMN,58,Chassis Number COLUMN,59,Workshop Team COLUMN,60,Commission Salesman COLUMN,61,Actual Inv Date Time COLUMN,62,Unique Ident COLUMN,63,Order Number_ori2 COLUMN,64,Line Number COLUMN,65,Key Prod Code COLUMN,66,Key Make Cd COLUMN,67,Delivery Date COLUMN,68,Line Group Code COLUMN,69,Handler COLUMN,70,State Code COLUMN,71,Person Code COLUMN,72,Order Linetype COLUMN,73,Order Linetype 2 COLUMN,74,Order Linetype 3 COLUMN,75,Resulting Line COLUMN,76,Resulting Line 2 COLUMN,77,Print Invoice COLUMN,78,Reduction Code COLUMN,79,Reduction Amount COLUMN,80,Reduction Code 2 COLUMN,81,Reduction Pos COLUMN,82,Split Code COLUMN,83,Split Code 2 COLUMN,84,Mechanic Code COLUMN,85,Salesman COLUMN,86,Discount Table COLUMN,87,Discount Code COLUMN,88,Claim Code Ind COLUMN,89,Claim Number COLUMN,90,Kit Fixed Price Cd COLUMN,91,R Delivery Date COLUMN,92,Wust Code COLUMN,93,Status COLUMN,94,Stock COLUMN,95,Gross Discount COLUMN,96,Statistic Code COLUMN,97,Decimal Indicator COLUMN,98,Add Cost Code COLUMN,99,Discount Perc COLUMN,100,Discount Perc 2 COLUMN,101,Discount COLUMN,102,Stdprice COLUMN,103,Lines Net Value COLUMN,104,Tax Code 1 COLUMN,105,Vat Rate Code COLUMN,106,Tax C U COLUMN,107,Tax Drawn COLUMN,108,Line Misc Adds COLUMN,109,Line Misc Adds Per COLUMN,110,Claim Trouble Cd COLUMN,111,Prod Code COLUMN,112,Make Cd COLUMN,113,Product Group COLUMN,114,Prod Name COLUMN,115,Location COLUMN,116,Order Quantity COLUMN,117,Delivery Quantity COLUMN,118,Line Costs COLUMN,119,Claim Return Code COLUMN,120,Inv Total Number COLUMN,121,Special Price COLUMN,122,Sales Price COLUMN,123,Sales Price Incl COLUMN,124,Orig Order No COLUMN,125,Orig Line No COLUMN,126,Orig Creation Code COLUMN,127,Back Order Cd COLUMN,128,Back Order COLUMN,129,Internal Transfer COLUMN,130,Stdprice 2 COLUMN,131,Quantity Disc Code COLUMN,132,Campaign Code COLUMN,133,Replace Code COLUMN,134,N Main Part No COLUMN,135,Line Textline 1 COLUMN,136,Line Textline 2 COLUMN,137,Line Textline 3 COLUMN,138,Line Text Code COLUMN,139,Line Code COLUMN,140,Line Workshop COLUMN,141,Back Reg Code COLUMN,142,Requisition Num COLUMN,143,Repair Code COLUMN,144,Repair Group COLUMN,145,Repair Name COLUMN,146,Rate Price Code COLUMN,147,Time Rate COLUMN,148,Used Time COLUMN,149,Est Time COLUMN,150,Inv Time COLUMN,151,Repair Code 2 COLUMN,152,Repair Pos COLUMN,153,Line Comment COLUMN,154,Inspection 1 COLUMN,155,Inspection 2 COLUMN,156,Work Date COLUMN,157,Start Time COLUMN,158,End Date COLUMN,159,End Time COLUMN,160,Workshop Area COLUMN,161,W Main Repair No COLUMN,162,Kit Group COLUMN,163,Kit Code COLUMN,164,Workshop Model COLUMN,165,Kit Text COLUMN,166,Kit Fixed Price COLUMN,167,Class Rule COLUMN,168,Kit One Line COLUMN,169,Kit One Lineprice COLUMN,170,Kit Catalog Code COLUMN,171,Kit Parts Disc Per COLUMN,172,Kit Work Disc Per COLUMN,173,Kit Misc Disc Per COLUMN,174,Kit Delete COLUMN,175,Kit Price Diff COLUMN,176,Kit Print Split COLUMN,177,Type Ol COLUMN,178,Transaction Code 2 COLUMN,179,Txt Ol COLUMN,180,Amount Ol COLUMN,181,Cash Account COLUMN,182,Repair Code Accu COLUMN,183,Recommended Price COLUMN,184,Cost Price Work COLUMN,185,Time Code 1 COLUMN,186,Used Time Type 1 COLUMN,187,Cost Price Type 1 COLUMN,188,Time Code 2 COLUMN,189,Used Time Type 2 COLUMN,190,Cost Price Type 2 COLUMN,191,Discount Allowance COLUMN,192,Create After Rc COLUMN,193,Repeat Repair COLUMN,194,Inv Time Cost COLUMN,195,Time Code COLUMN,196,Factor Code COLUMN,197,Reduction Factor 1 COLUMN,198,Resulting Line 1 COLUMN,199,External Stock COLUMN,200,Repair Group Type COLUMN,201,Automatic Created COLUMN,202,Incomplete Line COLUMN,203,Incomplete Line 2 COLUMN,204,Order Linetype 4 COLUMN,205,Consignment Code COLUMN,206,Part Group COLUMN,207,Misc 1 COLUMN,208,Misc 2 COLUMN,209,Misc 3 COLUMN,210,Misc 4 COLUMN,211,Misc 5 COLUMN,212,Misc 6 COLUMN,213,Order Line Transm COLUMN,214,Orig Prod Code COLUMN,215,Orig Make Cd COLUMN,216,Orig Prod Group COLUMN,217,Stock Shop List COLUMN,218,Line Reference COLUMN,219,Work Type Code COLUMN,220,Customer Complaint COLUMN,221,Job Code Descr COLUMN,222,Text 200 COLUMN,223,Create Msc COLUMN,224,Special Lock No COLUMN,225,Supplier Code COLUMN,226,Requisition Number COLUMN,227,Used Time Int COLUMN,228,Est Time Int COLUMN,229,Inv Time Int COLUMN,230,Make Time Unit COLUMN,231,Deposit Type COLUMN,232,Line Status COLUMN,233,Sales Unit Std COLUMN,234,Sales Unit Alt COLUMN,235,Order Quantity Alt Unit COLUMN,236,Oil COLUMN,237,Deductible Vat COLUMN,238,Qty Sold Negative COLUMN,239,Orig Cost Price COLUMN,240,Requested Quantity COLUMN,241,Unique Ident COLUMN,242,Stat Code COLUMN,243,Stat Specify COLUMN,244,Department Type Id COLUMN,245,Description COLUMN,246,Seller Code COLUMN,247,Sel Name COLUMN,248,Sel Department COLUMN,249,Sel First Name COLUMN,250,Sel Family Name COLUMN,251,Customer Group COLUMN,252,Cust Group Specify COLUMN,253,Seller Code COLUMN,254,Sel Name_Monteur COLUMN,255,Sel Department COLUMN,256,Sel First Name_Monteur COLUMN,257,Sel Family Name_Monteur COLUMN,258,Repair Group COLUMN,259,Make Cd COLUMN,260,Repair Grp Specify COLUMN,261,Preiscode COLUMN,262,Hauptbetrieb COLUMN,263,Standort COLUMN,264,Marke COLUMN,265,Kostenstelle COLUMN,266,Kundenart COLUMN,267,Umsatzart COLUMN,268,Geschäftsart COLUMN,269,Serviceberater COLUMN,270,Monteur COLUMN,271,Auftragsart COLUMN,272,Auftragsstatus COLUMN,273,Umsatz Teile Service COLUMN,274,Umsatz Lohn COLUMN,275,Umsatz Teile (nur Teile) COLUMN,276,Status_1 COLUMN,277,verk. Stunden_vor_Split COLUMN,278,Soll-Stunden (Auftrag) COLUMN,279,benutzte Zeit (Auftrag)_vor_Split COLUMN,280,Umsatz Sonstiges COLUMN,281,verk. AW_vor_Split COLUMN,282,Kunde COLUMN,283,Soll AW COLUMN,284,benutzte AW_vor_Split COLUMN,285,verk. Stunden COLUMN,286,benutzte Zeit (Auftrag) COLUMN,287,AW_Faktor COLUMN,288,Global Make Cd COLUMN,289,Einsatz Teile Service COLUMN,290,Einsatz Teile (nur Teile) COLUMN,291,Description COLUMN,292,Fabrikat_ori COLUMN,293,Model COLUMN,294,Fahrzeug COLUMN,295,Einsatz Teile Service_vor_Split COLUMN,296,Einsatz Teile (nur Teile)_vor_Split COLUMN,297,Rechnung/Gutschrift COLUMN,298,DB COLUMN,299,DB 1 Teile SC COLUMN,300,DB 1 Teile T COLUMN,301,VK < EK COLUMN,302,Mandant COLUMN,303,Order Number COLUMN,304,Order Number Rg_Ausgang COLUMN,305,verk. AW COLUMN,306,benutzte AW COLUMN,307,Fabrikat COLUMN,308,First Reg Date COLUMN,309,Fahrzeugalter_Tage COLUMN,310,Fahrzeugalter_Jahr COLUMN,311,FZG-Altersstaffel COLUMN,312,Nachlass COLUMN,313,Rabatt Teile COLUMN,314,Rabatt Lohn