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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_deop02
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Schichtplan_pro_MA.imr
TITLE,Schichtplan_pro_MA.imr
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,
	   (od_left(T5."WORK_LEADER_GROUP",1)) as c32,
	   T8."PROFILE_CODE" as c33,
	   (substring(T9."DEPARTMENT_TYPE_ID" from 4 for 1)) as c34,
	   T4."CORE_WORKING_HOURS" as c35,
	   '' as c36,
	   T10."WORK_LEADER_GROUP_ID" as c37,
	   T10."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,
	   (rtrim(T8."PROFILE_CODE")) || ' - ' || T3."SEL_NAME" as c44,
	   (database()) as c45,
	   T3."LICENCE_ID" as c46,
	   CASE WHEN (not T3."LICENCE_ID" IN ('0         ','1         ')) THEN ('Info fehlt') WHEN ((T3."LICENCE_ID" = '1')) THEN ('Hauptbertieb') ELSE ('Nebenbetrieb') END as c47
from "deop02"."dbo"."WORKING_PERIOD" T4,
	"deop02"."dbo"."EMPLOYEE" T10,
	"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") left outer join "deop02"."dbo"."DEPARTMENT_TYPE" T9 on T3."SEL_DEPARTMENT" = T9."DEPARTMENT_TYPE_ID")
where (T8."PROFILE_CODE" = T1."REFERENCE_NAME") and (T2."RECURRING_PATTERN_ID" = T4."RECURRING_PATTERN_ID") and (T10."PERSON_ID" = T8."PERSON_ID") and (T5."WORK_LEADER_GROUP" = T10."WORK_LEADER_GROUP_ID")
and (((((((((((T6."HOLIDAY_ID" IS NOT NULL) and (T6."EVENT_START" >= TIMESTAMP '2022-01-02 00:00:00.000')) and (T6."EVENT_START" <= TIMESTAMP '2023-04-23 00:00:00.000')) and (T6."HOLIDAY_ID" IN (115,117,119,120,123,124,126,131,132))) and (((dayofweek(T6."EVENT_START"))) BETWEEN 2 AND 6)) and (T6."EVENT_START" < (now()))) and ((T5."WORK_LEADER_GROUP" || ' - ' || T5."WORKLEADER_TEXT") IN ('1000 - Kundendienst                  ','1110 - Kundendienst MK','1120 - Karosserie MK','1190 - Azubi MK','1500 - Auszubildende                 ','2000 - Karosserie                    ','2110 - Kundendienst WI','2120 - Karosserie WI','2190 - Azubi WI','3000 - Lack                          ','3110 - Kundendienst MZ','3190 - Azubi MZ'))) and ((T10."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000') or (T6."EVENT_START" < T10."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")) and ((CASE WHEN (not T3."LICENCE_ID" IN ('0         ','1         ')) THEN ('Info fehlt') WHEN ((T3."LICENCE_ID" = '1')) THEN ('Hauptbertieb') ELSE ('Nebenbetrieb') END) <> 'Nebenbetrieb'))
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_ori
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
COLUMN,43,Monteur
COLUMN,44,Mandant
COLUMN,45,Licence Id
COLUMN,46,Aktive_MA_DB