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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Zeiten\Sollstunden_MA.imr
TITLE,Sollstunden_MA.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(T2."SEL_DEPARTMENT",2)) 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,
	   T8."bundeslaender_id" as c25,
	   T8."datum" as c26,
	   T8."wochentage_id" as c27,
	   T8."arbeitstag_mofr" as c28,
	   T8."zaehler_mofr" as c29,
	   T8."summe_mofr" as c30,
	   T8."arbeitstag_mosa" as c31,
	   T8."zaehler_mosa" as c32,
	   T8."summe_mosa" as c33,
	   T8."feiertage_id" as c34,
	   T8."Jahr" as c35,
	   T8."Arbeitstag Nr Jahr" as c36,
	   T8."Gesamt Arbeitstage" as c37,
	   T3."CORE_WORKING_HOURS" as c38,
	   (@CURRENT_DATE) - INTERVAL '001 00:00:00.000' as c39,
	   T8."wochentage_id" as c40
from "deop01"."dbo"."WORKING_PERIOD" T3,
	"deop01"."dbo"."EMPLOYEE" T7,
	"deop01"."dbo"."vPP91" T4,
	QSS."C:\GAPS\Portal\System\IQD\Zeiten\Kalender.ims" T8,
	(("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")
where (T5."PROFILE_CODE" = T9."REFERENCE_NAME") and (T1."RECURRING_PATTERN_ID" = T3."RECURRING_PATTERN_ID") 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 ('1000 - gewerbl. Mitarbeiter Spandau','1010 - gewerbl. Mitarbeiter Dallgow','1020 - gewerb. Mitarbeiter Falkensee','1030 - gewerbl. Mitarbeiter Nauen','2000 - Karosserie/Lack'))) and (T7."JOB_END_DATE" = TIMESTAMP '1800-01-01 00:00:00.000')) and ((T1."RECURRING_PATTERN_PRIORITY" = 0) or (T1."RECURRING_PATTERN_PRIORITY" IS NULL))) and (T8."wochentage_id" IN (4,5,1,2,3))) and (T3."START_DAY_ID" = T8."wochentage_id" + 1)) and (T8."datum" <= ((@CURRENT_DATE) - INTERVAL '001 00:00:00.000')))
order by c6 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,Bundeslaender Id
COLUMN,25,Datum
COLUMN,26,Wochentage Id
COLUMN,27,Arbeitstag Mofr
COLUMN,28,Zaehler Mofr
COLUMN,29,Summe Mofr
COLUMN,30,Arbeitstag Mosa
COLUMN,31,Zaehler Mosa
COLUMN,32,Summe Mosa
COLUMN,33,Feiertage Id
COLUMN,34,Jahr_alt
COLUMN,35,Arbeitstag Nr Jahr
COLUMN,36,Gesamt Arbeitstage
COLUMN,37,Soll-Std.
COLUMN,38,Jahr
COLUMN,39,Wochentag_Kalender_Zahl