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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Schichtplan_pro_MA_neu.imr
TITLE,Schichtplan_pro_MA_neu
BEGIN SQL
select T1."REFERENCE_IDENT" as c1,
	   T1."REFERENCE_NAME" as c2,
	   T1."CALENDAR_TYPE_ID" as c3,
	   T1."TRANSACT_DATE" as c4,
	   T2."CALENDAR_INFO_ID" as c5,
	   T2."CALENDAR_TYPE_ID" as c6,
	   T2."RECURRING_PATTERN_ID" as c7,
	   T2."RECURRING_PATTERN_PRIORITY" as c8,
	   T2."TRANSACT_DATE" as c9,
	   T3."SEL_NAME" as c10,
	   (od_right(T2."RECURRING_PATTERN_ID",3)) as c11,
	   T4."CORE_WORKING_HOURS" as c12,
	   T5."WORK_LEADER_GROUP" as c13,
	   T5."WORKLEADER_TEXT" as c14,
	   T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT" as c15,
	   T6."CALENDAR_EVENT_NAME" as c16,
	   T6."EVENT_START" as c17,
	   T6."EVENT_END" as c18,
	   T6."OCCURRENCE" as c19,
	   T6."ALL_DAY_EVENT" as c20,
	   T6."RECURRING_PATTERN_ID" as c21,
	   T6."ACTIVITY_ID" as c22,
	   T6."CALENDAR_INFO_ID" as c23,
	   T6."CALENDAR_TYPE_ID" as c24,
	   T6."RECURRING_TYPE_ID" as c25,
	   T6."HOLIDAY_ID" as c26,
	   (dayofweek(T6."EVENT_START")) as c27,
	   T3."SEL_NAME" as c28,
	   T6."EVENT_START" as c29,
	   T7."PERS_DEPARTMENT" as c30,
	   '1' as c31,
	   CASE WHEN ((od_left(T5."WORK_LEADER_GROUP",2)) = '10') THEN (1) WHEN ((od_left(T5."WORK_LEADER_GROUP",2)) = '11') THEN (2) WHEN ((od_left(T5."WORK_LEADER_GROUP",2)) = '12') THEN (3) ELSE null END as c32,
	   T8."PROFILE_CODE" as c33,
	   CASE WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '20') THEN ('2') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) IN ('30','80')) THEN ('6') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '40') THEN ('3') WHEN ((substring(T3."SEL_DEPARTMENT" from 3 for 2)) = '41') THEN ('4') WHEN (T3."SEL_DEPARTMENT" IN ('1F  ')) THEN ('3') ELSE null END as c34,
	   T4."CORE_WORKING_HOURS" as c35,
	   '' as c36,
	   T9."WORK_LEADER_GROUP_ID" as c37,
	   T9."JOB_END_DATE" as c38,
	   T4."CORE_WORKING_HOURS" as c39,
	   T4."RECURRING_PATTERN_ID" as c40,
	   T4."CORE_WORKING_HOURS" as c41,
	   T4."WORKINGDAY" as c42,
	   T4."START_DAY_ID" as c43
from "deop02"."dbo"."WORKING_PERIOD" T4,
	"deop02"."dbo"."EMPLOYEE" T9,
	"deop02"."dbo"."vPP91" T5,
	"deop02"."dbo"."CALENDAR_EVENT" T6,
	(("deop02"."dbo"."CALENDAR_INFO" T1 left outer join "deop02"."dbo"."CALENDAR_PATTERN" T2 on T2."CALENDAR_INFO_ID" = T1."REFERENCE_IDENT") left outer join "deop02"."dbo"."PERSON_INFO" T7 on T1."REFERENCE_NAME" = T7."PERSON_NUMBER"),
	("deop02"."dbo"."vPP43" T3 left outer join "deop02"."dbo"."PROFILE" T8 on T3."SELLER_CODE" = T8."PROFILE_CODE")
where (T8."PROFILE_CODE" = T1."REFERENCE_NAME") and (T2."RECURRING_PATTERN_ID" = T4."RECURRING_PATTERN_ID") and (T9."PERSON_ID" = T8."PERSON_ID") and (T5."WORK_LEADER_GROUP" = T9."WORK_LEADER_GROUP_ID")
and (((((((((T6."HOLIDAY_ID" IS NOT NULL) and (T6."EVENT_START" >= TIMESTAMP '2013-01-02 00:00:00.000')) and (T6."HOLIDAY_ID" IN (1,3,5,6,7,9,12,15,17,18))) and (((dayofweek(T6."EVENT_START"))) BETWEEN 2 AND 6)) and (T6."EVENT_START" < (now()))) and ((T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT") IN ('1010 - ÖSI Mechanik','1020 - ÖSI Karosserie','1030 - ÖSI Lack','1040 - ÖSI Aufbereiter','1110 - PIR Mechanik','1120 - PIR Karosserie','1140 - PIR Aufbereiter','1210 - TIT Mechanik','1220 - TIT Karosserie','1240 - TIT Aufbereiter'))) and ((T9."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000') or (T6."EVENT_START" < T9."JOB_END_DATE"))) and ((T2."RECURRING_PATTERN_PRIORITY" = 0) or (T2."RECURRING_PATTERN_PRIORITY" IS NULL))) and (((dayofweek(T6."EVENT_START"))) = T4."START_DAY_ID"))
order by c2 asc

END SQL
COLUMN,0,Reference Ident
COLUMN,1,Reference Name
COLUMN,2,Calendar Type Id
COLUMN,3,Transact Date
COLUMN,4,Calendar Info Id
COLUMN,5,Calendar Type Id
COLUMN,6,Recurring Pattern Id
COLUMN,7,Recurring Pattern Priority
COLUMN,8,Transact Date
COLUMN,9,Name
COLUMN,10,Pattern_rechts_3
COLUMN,11,Std_Tag
COLUMN,12,Work Leader Group
COLUMN,13,Workleader Text
COLUMN,14,Monteur_Gruppe
COLUMN,15,Calendar Event Name
COLUMN,16,Event Start
COLUMN,17,Event End
COLUMN,18,Occurrence
COLUMN,19,All Day Event
COLUMN,20,Recurring Pattern Id
COLUMN,21,Activity Id
COLUMN,22,Calendar Info Id
COLUMN,23,Calendar Type Id
COLUMN,24,Recurring Type Id
COLUMN,25,Holiday Id
COLUMN,26,Wochentag
COLUMN,27,Monteur
COLUMN,28,Datum
COLUMN,29,Pers Department
COLUMN,30,Hauptbetrieb
COLUMN,31,Standort
COLUMN,32,Profile Code
COLUMN,33,Kostenstelle
COLUMN,34,Feiertag
COLUMN,35,Order Number
COLUMN,36,Work Leader Group Id
COLUMN,37,Job End Date
COLUMN,38,abwesend
COLUMN,39,Recurring Pattern Id_aus_Workin_Period
COLUMN,40,Core Working Hours
COLUMN,41,Workingday
COLUMN,42,Start Day Id