@AbapCatalog.sqlViewName: 'PIQCDSACDMCEVENT'
@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Academic Event Data'
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #X
define view PiqCds_AcademicEventData
with parameters p_date:abap.dats,
p_lang:abap.lang
as select distinct from PiqCdsHrp1001 as event1
left outer join hrp1000 as eventtype on event1.RelatedObjectType = eventtype.otype and event1.RelatedObjectID = eventtype.objid
and eventtype.langu = $parameters.p_lang //and eventtype.begda <= $parameters.p_date and eventtype.endda >= $parameters.p_date
left outer join hrp1000 as event on event1.ObjectType = event.otype and event1.ObjectID = event.objid
and event.langu = $parameters.p_lang //and event.begda <= $parameters.p_date and event.endda >= $parameters.p_date
left outer join hrp1716 as hrp1 on event1.ObjectType = hrp1.otype and event1.ObjectID = hrp1.objid and event1.ActivePlanVersion = hrp1.plvar
left outer join hrt1716 as hrt1 on hrp1.tabnr = hrt1.tabnr
left outer join hrp1026 as hrp2 on event1.ObjectType = hrp2.otype and event1.ObjectID = hrp2.objid and event1.ActivePlanVersion = hrp2.plvar
inner join hrp1739 as hrp3 on event1.ObjectType = hrp3.otype and event1.ObjectID = hrp3.objid and event1.ActivePlanVersion = hrp3.plvar
inner join hrp1731 as hrp4 on event1.RelatedObjectType = hrp4.otype and event1.RelatedObjectID = hrp4.objid and event1.ActivePlanVersion = hrp4.plvar
left outer join hrp1722 as hrp5 on event1.ObjectType = hrp5.otype and event1.ObjectID = hrp5.otype and event1.ActivePlanVersion = hrp5.plvar
left outer join hrp1061 as hrp6 on event1.ObjectType = hrp6.otype and event1.ObjectID = hrp6.otype and event1.ActivePlanVersion = hrp6.plvar
left outer join hrp1000 as roomresource on roomresource.otype = 'G' and hrt1.room_objid = roomresource.objid and hrp1.plvar = roomresource.plvar
and roomresource.langu = $parameters.p_lang and roomresource.begda <= $parameters.p_date and roomresource.endda >= $parameters.p_date
//left outer join hrp1000 as instresource on hrt1.instr_otype = instresource.otype and hrt1.instr_objid = instresource.objid and hrp1.plvar = instresource.plvar
//and instresource.langu = $parameters.p_lang and instresource.begda <= $parameters.p_date and instresource.endda >= $parameters.p_date
left outer join pa0002 as instresource on hrt1.instr_objid = instresource.pernr
and instresource.sprsl = $parameters.p_lang and instresource.begda <= $parameters.p_date and instresource.endda >= $parameters.p_date
left outer join t77crt as reasontext on hrp2.cancr = reasontext.cancr and reasontext.langu = $parameters.p_lang
left outer join t777o as insttext on hrt1.instr_otype = insttext.otype and insttext.langu = $parameters.p_lang
left outer join t002t as lang on hrp2.langu = lang.sprsl and lang.spras = $parameters.p_lang
left outer join PiqCds_AcademicEventLocation ( p_date:$parameters.p_date, p_lang:$parameters.p_lang ) as loc on event1.ObjectID = loc.AcademicEvent
left outer join PiqCds_AcademicEventModule ( p_date:$parameters.p_date, p_lang:$parameters.p_lang ) as mod on event1.RelatedObjectID = mod.AcademicEventType
left outer join PiqCds_AcademicEventPackage ( p_date:$parameters.p_date, p_lang:$parameters.p_lang ) as pack on event1.ObjectID = pack.AcademicEvent
association [0..*] to PiqCdsAcdmcEvtTypeCategory as _AcademicEventTypeCategory on _AcademicEventTypeCategory.AcademicEventTypeCategory = hrp4.category
association [0..*] to PiqCdsAcdmcTeachingMethod as _AcdmcEventTypeTeachingMethod on _AcdmcEventTypeTeachingMethod.AcdmcEvtTypeTeachingMethod = hrp4.method
association [0..*] to PiqCdsAcdmcEvtDeliveryMode as _AcdmcEvtDeliveryMode on _AcdmcEvtDeliveryMode.AcdmcEventTypeDeliveryMode = hrp4.delmode
association [0..*] to PiqCdsAcdmcEvtAprslType as _AcdmcEvtAprslType on _AcdmcEvtAprslType.AcdmcEvtTypeAppraisalType = hrp4.agrtype
association [0..*] to PiqCdsAcademicYear as _AcademicYear on _AcademicYear.AcademicYear = hrp3.peryr
association [0..*] to PiqCdsAcademicSession as _AcademicSession on _AcademicSession.AcademicSession = hrp3.perid
association [0..*] to PiqCdsAcdmcEvtPlngProcgSts as _AcdmcEvtPlngProcgSts on _AcdmcEvtPlngProcgSts.AcdmcEventPlngProcgStatus = hrp5.evprocstat
{
event1.ObjectID as AcademicEvent,
event.short as AcademicEventShortName,
event.stext as AcademicEventName,
event1.RelatedObjectID as AcademicEventType,
eventtype.short as AcademicEventTypeShortName,
eventtype.stext as AcademicEventTypeName,
mod.StudyModule,
mod.StudyModuleShortName,
mod.StudyModuleName,
pack.AcademicEventPackage,
pack.AcademicEventPackageShortName,
pack.AcademicEventPackageName,
_AcademicEventTypeCategory.AcademicEventTypeCategory,
_AcademicEventTypeCategory._AcademicEventTypeCategoryText[1:Language = $parameters.p_lang].AcademicEventTypeCategoryName,
_AcdmcEventTypeTeachingMethod.AcdmcEvtTypeTeachingMethod,
_AcdmcEventTypeTeachingMethod._AcdmcTeachingMethodText[1:Language = $parameters.p_lang].AcdmcEvtTypeTeachingMethodName,
_AcdmcEvtDeliveryMode.AcdmcEventTypeDeliveryMode,
_AcdmcEvtDeliveryMode._AcdmcEvtDeliveryModeText[1:Language = $parameters.p_lang].AcdmcEventTypeDeliveryModeName,
_AcdmcEvtAprslType.AcdmcEvtTypeAppraisalType,
_AcdmcEvtAprslType._AcdmcEvtAprslTypeText[1:Language = $parameters.p_lang].AcdmcEvtTypeAppraisalTypeName,
hrp4.attreq as IsEvTypeAttendanceCompulsory,
loc.AcademicEventLocation,
loc.AcademicEventLocationShortName,
loc.AcademicEventLocationName,
hrp1.begda as AcademicEventStartDate,
hrp1.endda as AcademucEventEndDate,
hrt1.beguz as AcademicEventStartTime,
hrt1.enduz as AcadelicEventEndTime,
hrt1.room_objid as AcademicEventRoom,
roomresource.short as AcademicEventRoomShortName,
roomresource.stext as AcademicEventRoomName,
hrt1.instr_otype as AcademicEventInstructorType,
insttext.otext as AcdmcEventInstructorTypeName,
hrt1.instr_objid as AcademicEventInstructor,
//instresource.stext as AcademicEventInstructorName,
concat_with_space(instresource.vorna, instresource.nachn,1) as AcademicEventInstructorName,
hrp2.langu as AcademicEventLanguage,
lang.sptxt as AcademicEventLanguageName,
hrp2.lockm as AcademicEventIsLocked,
hrp2.delet as AcademicEventIsDeleted,
hrp2.extrn as AcademicEventIsExternal,
hrp2.cancr as AcdmcEvtCancellationReason,
reasontext.cancrt as AcdmcEvtCancellationReasonName,
hrp2.ncont as NumberOfTotalBookings,
hrp6.linktxt as AcdmcEventWebLinkTitle,
_AcdmcEvtPlngProcgSts.AcdmcEventPlngProcgStatus,
_AcdmcEvtPlngProcgSts._AcdmcEvtPlngProcgStsText[1:Language = $parameters.p_lang].AcdmcEventPlngProcgStatusName,
_AcademicYear.AcademicYear,
_AcademicYear._AcademicYearText[1:Language = $parameters.p_lang].AcademicYearName,
_AcademicSession.AcademicSession,
_AcademicSession._AcademicSessionText[1:Language = $parameters.p_lang].AcademicSessionName
}
where ( event1.ObjectType = 'E' or event1.ObjectType = 'EL' ) and event1.RelatedObjectType = 'D' and event1.RelationshipSubtype = 'A020'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"PIQCDSACADEMICSESSION",
"PIQCDSACADEMICSESSIONTEXT",
"PIQCDSACADEMICYEAR",
"PIQCDSACADEMICYEARTEXT",
"PIQCDSACDMCEVTAPRSLTYPE",
"PIQCDSACDMCEVTAPRSLTYPETEXT",
"PIQCDSACDMCEVTDELIVERYMODE",
"PIQCDSACDMCEVTDELIVERYMODETEXT",
"PIQCDSACDMCEVTPLNGPROCGSTS",
"PIQCDSACDMCEVTPLNGPROCGSTSTEXT",
"PIQCDSACDMCEVTTYPECATEGORY",
"PIQCDSACDMCEVTTYPECATEGORYTEXT",
"PIQCDSACDMCTEACHINGMETHOD",
"PIQCDSACDMCTEACHINGMETHODTEXT",
"PIQCDSHRP1001",
"PIQCDS_ACADEMICEVENTLOCATION",
"PIQCDS_ACADEMICEVENTMODULE",
"PIQCDS_ACADEMICEVENTPACKAGE",
"HRP1000",
"HRP1026",
"HRP1061",
"HRP1716",
"HRP1722",
"HRP1731",
"HRP1739",
"HRT1716",
"PA0002",
"T002T",
"T777O",
"T77CRT"
],
"ASSOCIATED":
[
"PIQCDSACADEMICSESSION",
"PIQCDSACADEMICYEAR",
"PIQCDSACDMCEVTAPRSLTYPE",
"PIQCDSACDMCEVTDELIVERYMODE",
"PIQCDSACDMCEVTPLNGPROCGSTS",
"PIQCDSACDMCEVTTYPECATEGORY",
"PIQCDSACDMCTEACHINGMETHOD"
],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0
}
}*/