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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Monteur_neu_Zeiterf_neu.imr
TITLE,Monteur_neu_Zeiterf_neu.imr
BEGIN SQL
select T1."UNIQUE_IDENT" as c1,
	   T1."ACTIVITY_CODE" as c2,
	   T1."PROFILE_CODE" as c3,
	   T1."HANDLER" as c4,
	   T1."FUNCTION_CODE" as c5,
	   T1."PROGRAM" as c6,
	   T1."TRANSACT_DATE_LONG" as c7,
	   T1."START_PUNCH_FUNCTION" as c8,
	   T1."START_PUNCH_PROGRAM" as c9,
	   T1."END_PUNCH_FUNCTION" as c10,
	   T1."END_PUNCH_PROGRAM" as c11,
	   T1."DONE_FOR_DEPARTMENT" as c12,
	   T1."DONE_FOR_WORK_LEADER" as c13,
	   T1."ENDED_PUNCH" as c14,
	   T1."ORDER_NUMBER" as c15,
	   T1."LINE_NUMBER" as c16,
	   T1."START_DATE_TIME" as c17,
	   T1."END_DATE_TIME" as c18,
	   T1."TMCS_IDLE_PUNCH" as c19,
	   T1."DURATION_INT" as c20,
	   T1."USED_TIME_INT" as c21,
	   T1."WAGE_EXTRACTED" as c22,
	   T1."PUNCH_REMARK_CODE" as c23,
	   T1."REMARK_ACCEPTED" as c24,
	   T1."PUNCH_PERIOD_START_ID" as c25,
	   T1."CONV_FLAG" as c26,
	   T2."SELLER_CODE" as c27,
	   T2."SEL_NAME" as c28,
	   T2."SEL_DEPARTMENT" as c29,
	   T2."SEL_FIRST_NAME" as c30,
	   T2."SEL_FAMILY_NAME" as c31,
	   T3."WORK_LEADER_GROUP" as c32,
	   T3."WORKLEADER_TEXT" as c33,
	   T4."ACTIVITY_CODE" as c34,
	   T4."ACTIVITY_DESCRIPTION" as c35,
	   T4."PRESENT" as c36,
	   CASE WHEN (T4."PRESENT" = 1) THEN (T1."USED_TIME_INT") ELSE (0) END as c37,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2080','2081')) THEN (T1."USED_TIME_INT") ELSE (0) END as c38,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2010') THEN (T1."USED_TIME_INT") ELSE (0) END as c39,
	   CASE WHEN (T1."ACTIVITY_CODE" = '2020') THEN (T1."USED_TIME_INT") ELSE (0) END as c40,
	   CASE WHEN (T1."ACTIVITY_CODE" = '3010') THEN (T1."USED_TIME_INT") ELSE (0) END as c41,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2030','2040','2050','2060','2041','2031','2061','2070','2071','2090','2091')) THEN (T1."USED_TIME_INT") ELSE (0) END as c43,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3011')) THEN (T1."USED_TIME_INT") ELSE (0) END as c44,
	   CASE WHEN (T1."ACTIVITY_CODE" = '4080') THEN (T1."USED_TIME_INT") ELSE (0) END as c45,
	   CASE WHEN (T1."ACTIVITY_CODE" = '4090') THEN (T1."USED_TIME_INT") ELSE (0) END as c46,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5030','5031')) THEN (T1."USED_TIME_INT") ELSE (0) END as c47,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3020','3021')) THEN (T1."USED_TIME_INT") ELSE (0) END as c48,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5010','5011')) THEN (T1."USED_TIME_INT") ELSE (0) END as c49,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5020','5040','5041','5021')) THEN (T1."USED_TIME_INT") ELSE (0) END as c50,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5050')) THEN (T1."USED_TIME_INT") ELSE (0) END as c51,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c52,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4091')) THEN (T1."USED_TIME_INT") ELSE (0) END as c53,
	   T5."ORDER_NUMBER" as c54,
	   T5."CUSTOMER_GROUP" as c55,
	   CASE WHEN ((T5."CUSTOMER_GROUP" BETWEEN '10' AND '59') or (T5."CUSTOMER_GROUP" LIKE '7%')) THEN ('extern') WHEN (T5."CUSTOMER_GROUP" LIKE '6%') THEN ('GWL') WHEN (((T5."CUSTOMER_GROUP" LIKE '9%') or (T5."PMT_TERM" = 'IN')) or ((od_left(T5."CUSTOMER_GROUP",1)) BETWEEN 'A' AND 'Z')) THEN ('intern') ELSE null END as c56,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1010','1011','1021')) THEN (T1."USED_TIME_INT") ELSE (0) END as c57,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1041')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1030','1031')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1010','1011','1021')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1040','1041')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1030','1031')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c60,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('2080','2081')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2010') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '2020') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1000')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2030','2040','2050','2060','2041','2031','2061','2070','2071','2090','2091')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '4080') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3060')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1050')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3050')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c61,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('5030','5031')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3020','3021')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5010','5011')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5020','5040','5041','5021')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3040')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5050')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3060')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4020','4021')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4091')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '3010') THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3011')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" = '4090') THEN (T1."USED_TIME_INT") ELSE (0) END) as c62,
	   T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT" as c63,
	   (cdate(T1."START_DATE_TIME")) as c64,
	   '1' as c65,
	   T6."DEPARTMENT_TYPE_ID" as c66,
	   T6."DESCRIPTION" as c67,
	   '01' as c68,
	   (substring(T6."DEPARTMENT_TYPE_ID" from 4 for 1)) as c69,
	   T2."SEL_NAME" as c70,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3060')) THEN (T1."USED_TIME_INT") ELSE (0) END as c71,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3040')) THEN (T1."USED_TIME_INT") ELSE (0) END as c72,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4020','4021')) THEN (T1."USED_TIME_INT") ELSE (0) END as c73,
	   T7."WORK_LEADER_GROUP_ID" as c74,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1050')) THEN (T1."USED_TIME_INT") ELSE (0) END as c75,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3050')) THEN (T1."USED_TIME_INT") ELSE (0) END as c76,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c77,
	   (T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT") as c78,
	   T2."SEL_NAME" || ' - ' || T1."PROFILE_CODE" as c79
from "deop01"."dbo"."EMPLOYEE" T7,
	"deop01"."dbo"."vPP91" T3,
	((((("deop01"."dbo"."PUNCH" T1 left outer join "deop01"."dbo"."vPP43" T2 on T1."PROFILE_CODE" = T2."SELLER_CODE") left outer join "deop01"."dbo"."PROFILE" T8 on T2."SELLER_CODE" = T8."PROFILE_CODE") left outer join "deop01"."dbo"."ACTIVITY" T4 on T1."ACTIVITY_CODE" = T4."ACTIVITY_CODE") left outer join "deop01"."dbo"."ORDER_HEADER" T5 on T5."ORDER_NUMBER" = T1."ORDER_NUMBER") left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T6 on T2."SEL_DEPARTMENT" = T6."DEPARTMENT_TYPE_ID")
where (T7."PERSON_ID" = T8."PERSON_ID") and (T3."WORK_LEADER_GROUP" = T7."WORK_LEADER_GROUP_ID")
and (T1."TRANSACT_DATE_LONG" >= TIMESTAMP '2013-01-01 00:00:00.000')
order by c7 asc

END SQL
COLUMN,0,Unique Ident
COLUMN,1,Activity Code
COLUMN,2,Profile Code
COLUMN,3,Handler
COLUMN,4,Function Code
COLUMN,5,Program
COLUMN,6,Transact Date Long
COLUMN,7,Start Punch Function
COLUMN,8,Start Punch Program
COLUMN,9,End Punch Function
COLUMN,10,End Punch Program
COLUMN,11,Done For Department
COLUMN,12,Done For Work Leader
COLUMN,13,Ended Punch
COLUMN,14,Order Number
COLUMN,15,Line Number
COLUMN,16,Start Date Time
COLUMN,17,End Date Time
COLUMN,18,Tmcs Idle Punch
COLUMN,19,Duration Int
COLUMN,20,Used Time Int
COLUMN,21,Wage Extracted
COLUMN,22,Punch Remark Code
COLUMN,23,Remark Accepted
COLUMN,24,Punch Period Start Id
COLUMN,25,Conv Flag
COLUMN,26,Seller Code
COLUMN,27,Sel Name
COLUMN,28,Sel Department
COLUMN,29,Sel First Name
COLUMN,30,Sel Family Name
COLUMN,31,Work Leader Group
COLUMN,32,Workleader Text
COLUMN,33,Activity Code_Activity
COLUMN,34,Activity Description
COLUMN,35,Present_Activity
COLUMN,36,Anwesenheit
COLUMN,37,Nacharbeit
COLUMN,38,Leerlauf/Wartezeit
COLUMN,39,Werkstatt aufräumen
COLUMN,40,Schulung
COLUMN,41,Unprod. Anwes.
COLUMN,42,Hilfslohn
COLUMN,43,Schulung kfm.
COLUMN,44,Pause man.
COLUMN,45,unbezahlt
COLUMN,46,Krank
COLUMN,47,Arzt
COLUMN,48,Urlaub
COLUMN,49,Sonderurlaub
COLUMN,50,Feiertag
COLUMN,51,zu spät
COLUMN,52,Überstunden
COLUMN,53,Order Number_Auftrag
COLUMN,54,Customer Group
COLUMN,55,Umsatzart
COLUMN,56,Extern
COLUMN,57,GWL
COLUMN,58,Intern
COLUMN,59,produktiv
COLUMN,60,unproduktiv
COLUMN,61,abwesend
COLUMN,62,Monteur_Gruppe_ori
COLUMN,63,Datum
COLUMN,64,Hauptbetrieb
COLUMN,65,Department Type Id
COLUMN,66,Description
COLUMN,67,Standort
COLUMN,68,Kostenstelle
COLUMN,69,Monteur_ori
COLUMN,70,Aussendienst
COLUMN,71,Berufsschule
COLUMN,72,unbez. Urlaub
COLUMN,73,Work Leader Group Id
COLUMN,74,Meistervertretung
COLUMN,75,Systemfehler
COLUMN,76,Abzug T390
COLUMN,77,Monteur_Gruppe
COLUMN,78,Monteur