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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Sollstunden_MA_neu.imr
TITLE,Sollstunden_MA_neu.imr
BEGIN SQL
select T1."CALENDAR_INFO_ID" as c1,
	   T1."CALENDAR_TYPE_ID" as c2,
	   T1."RECURRING_PATTERN_ID" as c3,
	   T1."RECURRING_PATTERN_PRIORITY" as c4,
	   T1."TRANSACT_DATE" as c5,
	   T2."SEL_NAME" as c6,
	   (od_right(T1."RECURRING_PATTERN_ID",3)) as c7,
	   T3."CORE_WORKING_HOURS" as c8,
	   T4."WORK_LEADER_GROUP" as c9,
	   T4."WORKLEADER_TEXT" as c10,
	   T4."WORK_LEADER_GROUP" || ' - ' || T4."WORKLEADER_TEXT" as c11,
	   ((rtrim(T5."PROFILE_CODE"))) || ' - ' || T2."SEL_NAME" as c12,
	   T6."PERS_DEPARTMENT" as c13,
	   '1' as c14,
	   (od_left(T4."WORK_LEADER_GROUP",1)) as c15,
	   (rtrim(T5."PROFILE_CODE")) as c16,
	   CASE WHEN ((substring(T2."SEL_DEPARTMENT" from 3 for 2)) = '20') THEN ('2') WHEN ((substring(T2."SEL_DEPARTMENT" from 3 for 2)) IN ('30','80')) THEN ('6') WHEN ((substring(T2."SEL_DEPARTMENT" from 3 for 2)) = '40') THEN ('3') WHEN ((substring(T2."SEL_DEPARTMENT" from 3 for 2)) = '41') THEN ('4') WHEN (T2."SEL_DEPARTMENT" IN ('1F  ')) THEN ('3') ELSE null END as c17,
	   '' as c18,
	   T7."WORK_LEADER_GROUP_ID" as c19,
	   T7."JOB_END_DATE" as c20,
	   T3."RECURRING_PATTERN_ID" as c21,
	   T3."CORE_WORKING_HOURS" as c22,
	   T3."WORKINGDAY" as c23,
	   T3."START_DAY_ID" as c24,
	   T3."CORE_WORKING_HOURS" as c25,
	   (@CURRENT_DATE) - INTERVAL '001 00:00:00.000' as c26,
	   T8."PROFILE_CODE" as c27,
	   T8."START_DATE_TIME" as c28,
	   T8."END_DATE_TIME" as c29,
	   T8."WORK_PATTERN" as c30,
	   (dayofweek(T8."START_DATE_TIME")) as c31,
	   (cdate(T8."START_DATE_TIME")) as c32
from "deop01"."dbo"."EMPLOYEE" T7,
	"deop01"."dbo"."vPP91" T4,
	(("deop01"."dbo"."CALENDAR_INFO" T9 left outer join "deop01"."dbo"."CALENDAR_PATTERN" T1 on T1."CALENDAR_INFO_ID" = T9."REFERENCE_IDENT") left outer join "deop01"."dbo"."PERSON_INFO" T6 on T9."REFERENCE_NAME" = T6."PERSON_NUMBER"),
	("deop01"."dbo"."vPP43" T2 left outer join "deop01"."dbo"."PROFILE" T5 on T2."SELLER_CODE" = T5."PROFILE_CODE"),
	("deop01"."dbo"."PUNCH_PERIOD_START" T8 left outer join "deop01"."dbo"."WORKING_PERIOD" T3 on (T3."RECURRING_PATTERN_ID" = T8."WORK_PATTERN") and (T3."START_DAY_ID" = (dayofweek(T8."START_DATE_TIME"))))
where (T5."PROFILE_CODE" = T9."REFERENCE_NAME") and (T5."PROFILE_CODE" = T8."PROFILE_CODE") and (T7."PERSON_ID" = T5."PERSON_ID") and (T4."WORK_LEADER_GROUP" = T7."WORK_LEADER_GROUP_ID")
and (((((T3."WORKINGDAY" = 1) and ((T4."WORK_LEADER_GROUP" || ' - ' || T4."WORKLEADER_TEXT") IN ('10   - MGN Mechanik                  ','110  - MGN Mechanik','113  - Aufbereitung','12   - SHL Mechanik                  ','120  - MGN Karosserie','13   - Aufbereiter','130  - MGN Lackierung','140  - MGN Azubi','210  - SHL Mechanik','213  - SHL Aufbereitung','220  - SHL Karosserie','240  - SHL Azubi','30   - MGN Lackiererei               ','40   - MGN Lehrlinge                 ','42   - SHL Lehrlinge                 '))) and ((T7."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000') or (((cdate(T8."START_DATE_TIME"))) < T7."JOB_END_DATE"))) and ((T1."RECURRING_PATTERN_PRIORITY" = 0) or (T1."RECURRING_PATTERN_PRIORITY" IS NULL))) and (T3."START_DAY_ID" = ((dayofweek(T8."START_DATE_TIME")))))
order by c6 asc,c28 asc

END SQL
COLUMN,0,Calendar Info Id
COLUMN,1,Calendar Type Id
COLUMN,2,Recurring Pattern Id
COLUMN,3,Recurring Pattern Priority
COLUMN,4,Transact Date
COLUMN,5,Name
COLUMN,6,Pattern_rechts_3
COLUMN,7,Std_Tag
COLUMN,8,Work Leader Group
COLUMN,9,Workleader Text
COLUMN,10,Monteur_Gruppe
COLUMN,11,Monteur
COLUMN,12,Pers Department
COLUMN,13,Hauptbetrieb
COLUMN,14,Standort
COLUMN,15,Profile Code
COLUMN,16,Kostenstelle
COLUMN,17,Order Number
COLUMN,18,Work Leader Group Id
COLUMN,19,Job End Date
COLUMN,20,Recurring Pattern Id_aus_Workin_Period
COLUMN,21,Core Working Hours
COLUMN,22,Workingday
COLUMN,23,Start Day Id
COLUMN,24,Soll-Std.
COLUMN,25,Jahr
COLUMN,26,Profile Code_Punch_Period_Start
COLUMN,27,Start Date Time_Punch_Period_Start
COLUMN,28,End Date Time_Punch_Period_Start
COLUMN,29,Work Pattern_Punch_Period_Start
COLUMN,30,Wochentag
COLUMN,31,Datum