| 123456789101112131415161718192021222324 |
- select T1."ORDER_NUMBER" as "Order Number",
- T1."INVOICE_DATE" as "Datum",
- T1."CLIENT_DB" as "Hauptbetrieb",
- (substring(T1."DEPARTMENT", 2, 1)) as "Marke",
- (substring(T1."DEPARTMENT", 4, 1)) as "Kostenstelle",
- T2."Zuordnung" as "Umsatzart",
- (rtrim(T3."PROFILE_CODE")) + ' - ' + T4."SEL_NAME" as "Monteur",
- T5."EDITED_INV_TIME_INT" as "berechn. AW",
- T6."WORK_LEADER_GROUP" as "Work Leader Group",
- T6."WORKLEADER_TEXT" as "Workleader Text",
- T6."WORK_LEADER_GROUP" + ' - ' + T6."WORKLEADER_TEXT" as "Monteur_Gruppe",
- 'ben. Zeit' as "Activity_Codes_Group1",
- '' as "Activity_Codes_Group2",
- '' as "Activity_Desc",
- T7."Hauptbetrieb_ID" as "Hauptbetrieb Id",
- T7."Hauptbetrieb_Name" as "Hauptbetrieb Name",
- T7."Standort_ID" as "Standort Id",
- T7."Standort_Name" as "Standort Name",
- CASE WHEN ((day((getdate()) - T1."INVOICE_DATE")) <= 4) THEN ((left((((T1."ORDER_NUMBER"))),7)) + ' - ' + T1."MODEL_TEXT" + ' - ' + T1."NAME") ELSE null END as "Order Number_Mont"
- from ((("OPTIMA"."import"."ORDER_HEADER" T1 left outer join "OPTIMA"."data"."GC_Umsatzart" T2 on (T2."Client_DB" = T1."CLIENT_DB") and (T2."Geschaeftsbuchungsgruppe" = T1."CUSTOMER_GROUP")) left outer join "OPTIMA"."import"."ORDER_LINE" T8 on (T1."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on (left(T1."DEPARTMENT",2)) = T7."Standort"),
- (("OPTIMA"."import"."APPORTIONED_INV_TIME" T5 left outer join "OPTIMA"."import"."VPP43" T4 on (T4."SELLER_CODE" = T5."PROFILE_CODE") and (T4."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."PROFILE" T3 on (T3."PROFILE_CODE" = T4."SELLER_CODE") and (T3."CLIENT_DB" = T4."CLIENT_DB")),
- ("OPTIMA"."import"."EMPLOYEE" T9 left outer join "OPTIMA"."import"."VPP91" T6 on (T9."WORK_LEADER_GROUP_ID" = T6."WORK_LEADER_GROUP") and (T9."CLIENT_DB" = T6."CLIENT_DB"))
- where (((T8."ORDER_NUMBER" = T5."ORDER_NUMBER") and (T8."UNIQUE_IDENT" = T5."ORDER_LINE_ID")) and (T8."CLIENT_DB" = T5."CLIENT_DB")) and ((T9."PERSON_ID" = T3."PERSON_ID") and (T9."CLIENT_DB" = T3."CLIENT_DB"))
- and ((T1."INVOICE_DATE" >= convert(datetime, '2020-01-01 00:00:00.000')) and (T1."STATUS" IN ('35','37','39','47','48','49','50','51','52','36','34')))
|
