@AbapCatalog.sqlViewName: 'PIQCDSSTUDDATA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Student Data CDS VIew'
@ClientDependent: true
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #X
define view PiqCds_StudentData
with parameters p_lang:abap.lang, p_date:abap.dats
as select distinct from cmacbpst as studentbp
left outer join hrp1702 as h1702 on h1702.objid = studentbp.stobjid
and h1702.begda <= $parameters.p_date and h1702.endda >= $parameters.p_date
left outer join hrp1706 as h1706 on h1706.plvar = h1702.plvar and h1706.otype = h1702.otype and h1706.objid = h1702.objid
and h1706.begda <= $parameters.p_date and h1706.endda >= $parameters.p_date
left outer join hrp1711 as h1711 on h1711.plvar = h1702.plvar and h1711.otype = h1702.otype and h1711.objid = h1702.objid
and h1711.begda <= $parameters.p_date and h1711.endda >= $parameters.p_date
left outer join hrp1705 as h1705 on h1705.plvar = h1702.plvar and h1705.otype = h1702.otype and h1705.objid = h1702.objid
and h1705.begda <= $parameters.p_date and h1705.endda >= $parameters.p_date
left outer join hrp1001 as h1001 on h1001.plvar = h1702.plvar and h1001.otype = h1702.otype and h1001.objid = h1702.objid
and h1001.subty = 'A502' and h1001.sclas = 'F'
and h1001.begda <= $parameters.p_date and h1001.endda >= $parameters.p_date
left outer join hrp1000 as h1000 on h1000.objid = h1001.sobid and h1000.begda <= $parameters.p_date and h1000.endda >= $parameters.p_date //campus name
left outer join hrp1001 as storg on storg.plvar = h1702.plvar and storg.otype = h1702.otype and storg.objid = h1702.objid
and storg.subty = 'A502' and storg.sclas = 'O'
and storg.begda <= $parameters.p_date and storg.endda >= $parameters.p_date
left outer join hrp1000 as orgname on orgname.objid = storg.sobid and orgname.begda <= $parameters.p_date and orgname.endda >= $parameters.p_date
left outer join hrp1701 as h1701 on h1701.plvar = h1702.plvar and h1701.otype = h1702.otype and h1701.objid = h1702.objid
and h1701.begda <= $parameters.p_date and h1701.endda >= $parameters.p_date
left outer join t005t as t005t1 on t005t1.land1 = h1702.gblnd and t005t1.spras = $parameters.p_lang
left outer join t005t as t005t2 on t005t2.land1 = h1702.gbnat and t005t2.spras = $parameters.p_lang
left outer join t005t as t005t3 on t005t3.land1 = h1702.natio and t005t3.spras = $parameters.p_lang
left outer join t005t as t005t4 on t005t4.land1 = h1702.nati2 and t005t4.spras = $parameters.p_lang
left outer join t005t as t005t5 on t005t5.land1 = h1702.nati3 and t005t5.spras = $parameters.p_lang
left outer join t005u as t005u1 on t005u1.land1 = h1702.gbdep and t005u1.bland = h1711.resid_regio and t005u1.spras = $parameters.p_lang
left outer join t002t as t002t on t002t.sprsl = h1702.langu and t002t.spras = $parameters.p_lang
left outer join t522t as t522t on t522t.anred = h1702.anred and t522t.sprsl = $parameters.p_lang
inner join dd07t as domtable on domtable.domname = 'GESCH' and domtable.domvalue_l = h1702.gesch and domtable.ddlanguage = $parameters.p_lang
left outer join t502t as t502t on t502t.famst = h1702.famst and t502t.sprsl = $parameters.p_lang
left outer join t500l as t500l on t500l.intca = h1702.gblnd
left outer join t505v as t505v on t505v.resis = h1711.resid_status and t505v.sprsl = $parameters.p_lang and t505v.molga = t500l.molga
left outer join t005t as t005t6 on t005t6.land1 = h1711.resid_country and t005t6.spras = $parameters.p_lang
left outer join t005u as t005u2 on t005u2.bland = h1711.resid_regio and t005u2.land1 = h1702.gblnd and t005u2.spras = $parameters.p_lang
left outer join t005f as t005f on t005f.counc = h1711.resid_counc and t005f.land1 = h1702.gblnd and t005f.regio = h1711.resid_regio and t005f.spras = $parameters.p_lang
association [0..*] to PiqCdsStudentOrigin as _StudentOrigin on _StudentOrigin.StudentOrigin = h1711.origin
association [0..*] to PiqCdsStudentGroup as _StudentGroup on _StudentGroup.StudentGroup = h1705.stgrp
association [0..*] to PiqCdsStudentData as _StudentData on _StudentData.StudentData = h1701.confid
association [0..*] to PiqCdsStudentFeeCategory as _StudentFeeCategory on _StudentFeeCategory.StudentFeeCategory = h1706.stfeecat {
h1702.objid as StudentObjectId,
studentbp.student12 as StudentNumber,
h1705.matrikel as StudentAdditionalIdNumber,
h1702.nachn as StudentLastName,
h1702.vorna as StudentFirstname,
h1702.midnm as StudentMiddleName,
h1702.inits as StudentInitials,
h1702.birthname as StudentBirthName,
h1702.titel as StudentTitle,
h1702.vorsw as StudentNamePrefix,
h1702.namzu as StudentOtherTitle,
h1702.rufnm as StudentKnownAs,
h1702.gbdat as StudentDateOfBirth,
h1702.gblnd as StudentCountryOfBirth,
t005t1.landx as StudentCountryOfBirthName,
h1702.gbdep as StudentRegionOfBirth,
t005u1.bezei as StudentRegionOfBirthName,
h1702.gbort as StudentBirthPlaceName,
h1702.gbnat as StudentNationalityAtBirth,
t005t2.natio as StudentNationalityAtBirthName,
h1702.natio as StudentNationality,
t005t3.natio as StudentNationalityName,
h1702.nati2 as StudentSecondNationality,
t005t4.natio as StudentSecondNationalityName,
h1702.nati3 as StudentThirdNationality,
t005t5.natio as StudentThirdNationalityName,
h1702.langu as StudentLanguageOfCommunication,
t002t.sptxt as StudentLanguageOfCommName,
h1702.anred as StudentFormOfAddressKey,
t522t.anrlt as StudentFormOfAddressKeyName,
h1702.gesch as StudentGenderKey,
domtable.ddtext as StudentGenderKeyName,
h1702.famst as StudentMaritalStatus,
t502t.ftext as StudentMaritalStatusName,
h1702.prdni as StudentIdentificationNumber,
h1001.sobid as StudentCampus,
h1000.short as StudentCampusShortName,
h1000.stext as StudentCampusName,
storg.sobid as StudentAssignedOrg,
orgname.short as StudentAssignedOrgShortName,
orgname.stext as StudentAssignedOrgName,
h1702.deathdate as StudentDateOfDeath,
h1702.dregdate as StudentRecordedDateOfDeath,
h1711.resid_status as StudentResidentStatus,
t505v.ltext as StudentResidentStatusName,
h1711.resid_country as StudentResidentCountry,
t005t6.landx as StudentResidentCountryName,
h1711.resid_regio as StudentResidentState,
t005u2.bezei as StudentResidentStateName,
h1711.resid_counc as StudentResidentCounty,
t005f.bezei as StudentResidentCountyName,
h1711.passn as StudentPassportNumber,
h1711.origin as StudentOrigin,
_StudentOrigin._StudentOriginText[ 1: Language = $parameters.p_lang ].StudentOriginText as StudentOriginName,
h1711.pass_issuedate as StudentPassportIssueDate,
h1711.pass_issueplace as StudentPassportPlaceOfIssue,
h1711.pass_expirydate as StudentPassportExpiryDate,
h1705.stgrp as StudentGroup,
_StudentGroup._StudentGroupText[ 1: Language = $parameters.p_lang ].StudentGroupText as StudentGroupName,
h1705.regwindow as StudentModuleBookingTimeWindow,
h1701.confid as StudentDataPrivacyLevel,
_StudentData._StudentDataText[ 1: Language = $parameters.p_lang ].StudentDataText as StudentDataPrivacyLevelName,
h1706.stfeecat as StudentFeeCategory,
_StudentFeeCategory._FeeCategoryText[ 1: Language = $parameters.p_lang ].StudentFeeCategoryName as StudentFeeCategoryName
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"PIQCDSSTUDENTDATA",
"PIQCDSSTUDENTDATATEXT",
"PIQCDSSTUDENTFEECATEGORY",
"PIQCDSSTUDENTFEECATEGORYTEXT",
"PIQCDSSTUDENTGROUP",
"PIQCDSSTUDENTGROUPTEXT",
"PIQCDSSTUDENTORIGIN",
"PIQCDSSTUDENTORIGINTEXT",
"CMACBPST",
"DD07T",
"HRP1000",
"HRP1001",
"HRP1701",
"HRP1702",
"HRP1705",
"HRP1706",
"HRP1711",
"T002T",
"T005F",
"T005T",
"T005U",
"T500L",
"T502T",
"T505V",
"T522T"
],
"ASSOCIATED":
[
"PIQCDSSTUDENTDATA",
"PIQCDSSTUDENTFEECATEGORY",
"PIQCDSSTUDENTGROUP",
"PIQCDSSTUDENTORIGIN"
],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/