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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\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,
	   0 as c38,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c39,
	   '' as c40,
	   0 as c41,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2110','2130','2120')) THEN (T1."USED_TIME_INT") ELSE (0) END as c42,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END as c43,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END as c44,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c45,
	   '' as c46,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5000','5010','3100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c47,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) THEN (T1."USED_TIME_INT") ELSE (0) END as c48,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END as c49,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END as c50,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) THEN (T1."USED_TIME_INT") ELSE (0) END as c51,
	   '' as c52,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4200')) 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 ('1010','1011','1L20','1012','1L10','1K12','1L22','1K11','1K10','1L11','1K20','1L31','1L21','1L30','1K21','1K22','1L12','1L32','1013')) THEN (T1."USED_TIME_INT") ELSE (0) END as c57,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1031','1032','1033')) THEN (T1."USED_TIME_INT") ELSE (0) END as c58,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1021','1022','1023','1K80','1K81','1K82','1K90','1K91','1K92','1L70','1L71','1L72','1L80','1L81','1L82','1L90','1L91','1L92')) THEN (T1."USED_TIME_INT") ELSE (0) END as c59,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('1010','1011','1L20','1012','1L10','1K12','1L22','1K11','1K10','1L11','1K20','1L31','1L21','1L30','1K21','1K22','1L12','1L32','1013')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1031','1032','1033')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1020','1021','1022','1023','1K80','1K81','1K82','1K90','1K91','1K92','1L70','1L71','1L72','1L80','1L81','1L82','1L90','1L91','1L92')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2100')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c60,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('2110','2130','2120')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2000')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2200','2210','2220')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('2900')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1001','1000','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5130')) THEN (T1."USED_TIME_INT") ELSE (0) END) as c61,
	   (CASE WHEN (T1."ACTIVITY_CODE" IN ('5100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5000','5010','3100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('3000')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5200')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5210')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5110','5120')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('5300')) 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 ('5220')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('4200')) 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 ('4100')) THEN (T1."USED_TIME_INT") ELSE (0) END) + (CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) 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,
	   (od_left(T6."DEPARTMENT_TYPE_ID",2)) as c68,
	   (substring(T6."DEPARTMENT_TYPE_ID" from 4 for 1)) as c69,
	   (rtrim(T1."PROFILE_CODE")) || ' - ' || (T2."SEL_NAME") as c70,
	   '' as c71,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5110','5120')) THEN (T1."USED_TIME_INT") ELSE (0) END as c72,
	   '' as c73,
	   T7."WORK_LEADER_GROUP_ID" as c74,
	   '' as c75,
	   '' as c76,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1009')) THEN (T1."USED_TIME_INT") ELSE (0) END as c77,
	   '' as c78,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('996')) THEN (T1."USED_TIME_INT") ELSE (0) END as c79,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('1001','1000','1002','1003')) THEN (T1."USED_TIME_INT") ELSE (0) END as c80,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('2100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c81,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5130')) THEN (T1."USED_TIME_INT") ELSE (0) END as c82,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('5220')) THEN (T1."USED_TIME_INT") ELSE (0) END as c83,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4100')) THEN (T1."USED_TIME_INT") ELSE (0) END as c84,
	   CASE WHEN (T1."ACTIVITY_CODE" IN ('4010')) THEN (T1."USED_TIME_INT") ELSE (0) END as c85,
	   CASE WHEN (T1."PROFILE_CODE" = '34') THEN ('4000 - Azubi') ELSE ((T3."WORK_LEADER_GROUP" || ' - ' || T3."WORKLEADER_TEXT")) END as c86
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 ((((cdate(T1."START_DATE_TIME"))) >= DATE '2011-01-01') and (not T1."ACTIVITY_CODE" IN ('996')))
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,Wartezeit
COLUMN,39,Prob.fahrt/Endkontr._
COLUMN,40,GW-Bewertung_
COLUMN,41,Unproduktiv
COLUMN,42,Hilfslohn
COLUMN,43,Schulung intern
COLUMN,44,Schulung extern
COLUMN,45,Betriebsrat_
COLUMN,46,Krank
COLUMN,47,Arzt
COLUMN,48,Urlaub
COLUMN,49,Sonderurlaub
COLUMN,50,Feiertag
COLUMN,51,Fehlstunden_
COLUMN,52,Zeitausgleich
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
COLUMN,70,Werkstatt aufräumen_
COLUMN,71,Berufsschule
COLUMN,72,Kurzarbeit_
COLUMN,73,Work Leader Group Id
COLUMN,74,Leerlauf_
COLUMN,75,Aufräumarbeiten_
COLUMN,76,Tag beenden
COLUMN,77,Rg Stempeln_
COLUMN,78,Abzug T390
COLUMN,79,unprod. Anwesenh.
COLUMN,80,Abschleppen
COLUMN,81,Ausstellung
COLUMN,82,Erziehungsurlaub
COLUMN,83,unbez Abwesenh.
COLUMN,84,spät. AZ Beginn
COLUMN,85,Monteur_Gruppe