GAPS/Portal/System/IQD/Zeiten/Monteur_neu.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Monteur_neu.imr
TITLE,Monteur_neu.imr
BEGIN SQL
select T1."PERSON_GROUP" as c1,
	   T1."PERSON_NUMBER" as c2,
	   T1."WORK_TRANS_DATE" as c3,
	   T1."WORK_START_TIME" as c4,
	   T1."ORDER_NUMBER" as c5,
	   T1."LINE_NUMBER" as c6,
	   T1."WORK_STATE_CODE" as c7,
	   T1."HANDLER" as c8,
	   T1."WORK_ACT_CODE" as c9,
	   T1."WORK_ACT_TEXT" as c10,
	   T1."WORK_END_DATE" as c11,
	   T1."WORK_END_TIME" as c12,
	   T1."REPAIR_CODE" as c13,
	   T1."REPAIR_NAME" as c14,
	   T1."WORK_ORDER_TIME" as c15,
	   T1."WORK_USED_TIME" as c16,
	   T1."WORK_INVOICED_TIME" as c17,
	   T1."WORK_ESTIM_TIME" as c18,
	   T1."WORK_PRINT_INVOICE" as c19,
	   T1."WORK_SALESPRICE" as c20,
	   T1."DAYTIME_START_1" as c21,
	   T1."DAYTIME_END_1" as c22,
	   T1."DAYTIME_START_2" as c23,
	   T1."DAYTIME_END_2" as c24,
	   T1."DAYTIME_START_3" as c25,
	   T1."DAYTIME_END_3" as c26,
	   T1."DAYTIME_START_4" as c27,
	   T1."DAYTIME_END_4" as c28,
	   T1."DAYTIME_START_5" as c29,
	   T1."DAYTIME_END_5" as c30,
	   T1."EXTRACTED" as c31,
	   T1."REDUCE_TIME" as c32,
	   T1."PROGRAM" as c33,
	   T1."CLOCK_IN_PROGRAM" as c34,
	   T1."FUNCTION_CODE" as c35,
	   T1."CONV_FLAG" as c36,
	   T1."UNIQUE_IDENT" as c37,
	   T2."ACT_CODE" as c38,
	   T2."ACTIVITY_TEXT" as c39,
	   T3."WORK_LEADER_GROUP" as c40,
	   T3."WORKLEADER_TEXT" as c41,
	   T4."SELLER_CODE" as c42,
	   T4."SEL_NAME" as c43,
	   T4."SEL_DEPARTMENT" as c44,
	   T4."SEL_FIRST_NAME" as c45,
	   T4."SEL_FAMILY_NAME" as c46,
	   CASE WHEN ((T1."WORK_ACT_CODE" IN ('000 ')) and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('000 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END as c47,
	   CASE WHEN (T1."WORK_ACT_CODE" IN ('011 ','013')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c48,
	   CASE WHEN (T1."WORK_ACT_CODE" IN ('999','012','014')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c49,
	   '' as c50,
	   '' as c51,
	   CASE WHEN ((od_left(T1."WORK_ACT_CODE",2)) = '02') THEN (T1."WORK_USED_TIME") WHEN (T1."WORK_ACT_CODE" IN ('24','25')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c52,
	   CASE WHEN ((od_left(T1."WORK_ACT_CODE",2)) IN ('06','05')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c53,
	   CASE WHEN (T1."WORK_ACT_CODE" IN ('091 ')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c54,
	   CASE WHEN ((T1."WORK_ACT_CODE" IN ('090 ')) and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('090 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END as c55,
	   CASE WHEN ((od_left(T1."WORK_ACT_CODE",3)) = '092') THEN (T1."WORK_USED_TIME") ELSE (0) END as c56,
	   CASE WHEN ((T1."WORK_ACT_CODE" = '101 ') and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('101 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END as c57,
	   CASE WHEN (T1."WORK_ACT_CODE" = '102 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c58,
	   CASE WHEN ((T1."WORK_ACT_CODE" = '105 ') and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('105 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END as c59,
	   CASE WHEN (T1."WORK_ACT_CODE" = '107 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c60,
	   CASE WHEN (T1."WORK_ACT_CODE" = '113 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c61,
	   CASE WHEN (T1."WORK_ACT_CODE" = '120 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c62,
	   '1' as c63,
	   (od_left(T5."DEPARTMENT_TYPE_ID",2)) as c64,
	   T5."DEPARTMENT_TYPE_ID" as c65,
	   T5."DESCRIPTION" as c66,
	   (substring(T5."DEPARTMENT_TYPE_ID" from 4 for 1)) as c67,
	   CASE WHEN (T4."SEL_NAME" IS NOT NULL) THEN (T1."PERSON_NUMBER" || ' - ' || T4."SEL_NAME") ELSE (T1."PERSON_NUMBER") END as c68,
	   T6."ORDER_NUMBER" as c69,
	   T6."CUSTOMER_GROUP" as c70,
	   CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c71,
	   CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'extern') THEN (T1."WORK_USED_TIME") ELSE (0) END as c72,
	   CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'GWL') THEN (T1."WORK_USED_TIME") ELSE (0) END as c73,
	   CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'intern') THEN (T1."WORK_USED_TIME") ELSE (0) END as c74,
	   CASE WHEN (T1."WORK_ACT_CODE" IN ('250','251','252','253','254','255','257','258')) THEN (T1."WORK_USED_TIME") ELSE (0) END as c75,
	   (CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'extern') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'GWL') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN ((CASE WHEN ((T6."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T6."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T6."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T6."CUSTOMER_GROUP" LIKE '9%') or (T6."PMT_TERM" = 'IN')) or ((od_left(T6."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END) = 'intern') THEN (T1."WORK_USED_TIME") ELSE (0) END) as c76,
	   (CASE WHEN ((od_left(T1."WORK_ACT_CODE",2)) = '02') THEN (T1."WORK_USED_TIME") WHEN (T1."WORK_ACT_CODE" IN ('24','25')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" IN ('011 ','013')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" IN ('999','012','014')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN ((od_left(T1."WORK_ACT_CODE",2)) IN ('06','05')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" IN ('091 ')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN ((od_left(T1."WORK_ACT_CODE",3)) = '092') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '159 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" IN ('250','251','252','253','254','255','257','258')) THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '998 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) as c77,
	   (CASE WHEN ((T1."WORK_ACT_CODE" IN ('090 ')) and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('090 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END) + (CASE WHEN ((T1."WORK_ACT_CODE" = '101 ') and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('101 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '102 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN ((T1."WORK_ACT_CODE" = '105 ') and (T1."WORK_USED_TIME" <> 0.00)) THEN (T1."WORK_USED_TIME") WHEN (((T1."WORK_ACT_CODE" IN ('105 ')) and (T1."WORK_USED_TIME" = 0.00)) and (T1."FUNCTION_CODE" = 'E390')) THEN (8) ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '107 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '120 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '095 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) + (CASE WHEN (T1."WORK_ACT_CODE" = '113 ') THEN (T1."WORK_USED_TIME") ELSE (0) END) as c78,
	   CASE WHEN (T7."EMPLOYEE_GROUP" IN ('D ')) THEN ('Lehrling Lgb.') WHEN (T7."EMPLOYEE_GROUP" IN ('E ')) THEN ('Lehrling H.haus') WHEN (T7."EMPLOYEE_GROUP" IN ('F ')) THEN ('Meister Lgb.') WHEN (T7."EMPLOYEE_GROUP" IN ('G ')) THEN ('Meister H.haus') WHEN (T7."EMPLOYEE_GROUP" IN ('H ')) THEN ('Gesellen Lgb.') WHEN (T7."EMPLOYEE_GROUP" IN ('I ')) THEN ('Gesellen H.haus') WHEN (T7."EMPLOYEE_GROUP" IN ('J ')) THEN ('Karosserie') WHEN (T7."EMPLOYEE_GROUP" IN ('K ')) THEN ('Lack') WHEN (T7."EMPLOYEE_GROUP" IN ('L ')) THEN ('ausgeschieden') WHEN (T7."EMPLOYEE_GROUP" IN ('12')) THEN ('Test') ELSE null END as c79,
	   T1."WORK_TRANS_DATE" as c80,
	   CASE WHEN (T1."WORK_ACT_CODE" = '095 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c81,
	   CASE WHEN (T1."WORK_ACT_CODE" = '998 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c82,
	   CASE WHEN (T1."WORK_ACT_CODE" = '159 ') THEN (T1."WORK_USED_TIME") ELSE (0) END as c83,
	   T7."UNIQUE_IDENT" as c84,
	   T7."PERSON_ID" as c85,
	   T7."FLEX_BALANCE" as c86,
	   T7."OVERWORK_CODE" as c87,
	   T7."WAGE_NO" as c88,
	   T7."EFFICIENCY_PRC" as c89,
	   T7."JOB_START_DATE" as c90,
	   T7."JOB_END_DATE" as c91,
	   T7."PROFESSION_GROUP_ID" as c92,
	   T7."WORK_LEADER_GROUP_ID" as c93,
	   T7."EMPLOYEE_GROUP" as c94,
	   T7."WORKSHOP_TEAM" as c95,
	   T7."EXTERNAL_PERSON_NO" as c96,
	   T7."TRAINEE_YEARS" as c97,
	   T7."MAX_FLEX_HOURS" as c98,
	   T7."MIN_FLEX_HOURS" as c99,
	   T7."FLEX_PATTERN_ALLOWED" as c100,
	   T7."CH_WORK_PATTERN_ALLOWED" as c101,
	   T7."TRANSACT_DATE" as c102,
	   T7."HANDLER" as c103,
	   T7."FUNCTION_CODE" as c104,
	   T7."PROGRAM" as c105,
	   T7."CONV_FLAG" as c106,
	   T7."EMPLOYEE_GROUP" as c107
from ((((((("deop01"."dbo"."TIME_CONTROL_END" T1 left outer join "deop01"."dbo"."vPP93" T2 on T1."WORK_ACT_CODE" = T2."ACT_CODE") left outer join "deop01"."dbo"."vPP43" T4 on T1."PERSON_NUMBER" = T4."SELLER_CODE") left outer join "deop01"."dbo"."PROFILE" T8 on T8."PROFILE_CODE" = T4."SELLER_CODE") left outer join "deop01"."dbo"."EMPLOYEE" T7 on T7."PERSON_ID" = T8."PERSON_ID") left outer join "deop01"."dbo"."vPP91" T3 on T7."WORK_LEADER_GROUP_ID" = T3."WORK_LEADER_GROUP") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T5 on T4."SEL_DEPARTMENT" = T5."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."ORDER_HEADER" T6 on T1."ORDER_NUMBER" = T6."ORDER_NUMBER")
where (T1."WORK_TRANS_DATE" >= TIMESTAMP '2011-01-01 00:00:00.000')
order by c3 asc

END SQL
COLUMN,0,Person Group
COLUMN,1,Person Number
COLUMN,2,Work Trans Date
COLUMN,3,Work Start Time
COLUMN,4,Order Number
COLUMN,5,Line Number
COLUMN,6,Work State Code
COLUMN,7,Handler
COLUMN,8,Work Act Code
COLUMN,9,Work Act Text
COLUMN,10,Work End Date
COLUMN,11,Work End Time
COLUMN,12,Repair Code
COLUMN,13,Repair Name
COLUMN,14,Work Order Time
COLUMN,15,Work Used Time
COLUMN,16,Work Invoiced Time
COLUMN,17,Work Estim Time
COLUMN,18,Work Print Invoice
COLUMN,19,Work Salesprice
COLUMN,20,Daytime Start 1
COLUMN,21,Daytime End 1
COLUMN,22,Daytime Start 2
COLUMN,23,Daytime End 2
COLUMN,24,Daytime Start 3
COLUMN,25,Daytime End 3
COLUMN,26,Daytime Start 4
COLUMN,27,Daytime End 4
COLUMN,28,Daytime Start 5
COLUMN,29,Daytime End 5
COLUMN,30,Extracted
COLUMN,31,Reduce Time
COLUMN,32,Program
COLUMN,33,Clock In Program
COLUMN,34,Function Code
COLUMN,35,Conv Flag
COLUMN,36,Unique Ident
COLUMN,37,Act Code
COLUMN,38,Activity Text
COLUMN,39,Work Leader Group
COLUMN,40,Workleader Text
COLUMN,41,Seller Code
COLUMN,42,Sel Name
COLUMN,43,Sel Department
COLUMN,44,Sel First Name
COLUMN,45,Sel Family Name
COLUMN,46,Anwesenheit
COLUMN,47,Nacharbeit
COLUMN,48,Wartezeit
COLUMN,49,Prob.fahrt/Endkontr._
COLUMN,50,GW-Bewertung_
COLUMN,51,Unproduktiv
COLUMN,52,Hilfslohn
COLUMN,53,Schulung intern
COLUMN,54,Schulung extern
COLUMN,55,Betriebsrat
COLUMN,56,Krank
COLUMN,57,Arzt
COLUMN,58,Urlaub
COLUMN,59,Sonderurlaub
COLUMN,60,Feiertag
COLUMN,61,Fehlstunden
COLUMN,62,Hauptbetrieb
COLUMN,63,Standort
COLUMN,64,Department Type Id
COLUMN,65,Description
COLUMN,66,Kostenstelle
COLUMN,67,Monteur
COLUMN,68,Order Number_Auftrag
COLUMN,69,Customer Group
COLUMN,70,Umsatzart
COLUMN,71,Extern
COLUMN,72,GWL
COLUMN,73,Intern
COLUMN,74,Überstunden
COLUMN,75,produktiv
COLUMN,76,unproduktiv
COLUMN,77,abwesend
COLUMN,78,Monteur_Gruppe
COLUMN,79,Datum
COLUMN,80,Überstundenabbau
COLUMN,81,Mehrarbeit
COLUMN,82,Sucharbeit/Endkontrolle
COLUMN,83,Unique Ident
COLUMN,84,Person Id
COLUMN,85,Flex Balance
COLUMN,86,Overwork Code
COLUMN,87,Wage No
COLUMN,88,Efficiency Prc
COLUMN,89,Job Start Date
COLUMN,90,Job End Date
COLUMN,91,Profession Group Id
COLUMN,92,Work Leader Group Id
COLUMN,93,Employee Group
COLUMN,94,Workshop Team
COLUMN,95,External Person No
COLUMN,96,Trainee Years
COLUMN,97,Max Flex Hours
COLUMN,98,Min Flex Hours
COLUMN,99,Flex Pattern Allowed
COLUMN,100,Ch Work Pattern Allowed
COLUMN,101,Transact Date
COLUMN,102,Handler
COLUMN,103,Function Code
COLUMN,104,Program
COLUMN,105,Conv Flag
COLUMN,106,Employee Group