PiqCds_ProgressionData

@AbapCatalog.sqlViewName: 'PIQCDSPRGRSSN'
@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Progression Data'
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #X

define view PiqCds_ProgressionData
with parameters
p_date:abap.dats,
p_lang:abap.lang
    as select from hrp1737 as prog1
    inner join hrt1737 as prog2 on prog1.tabnr = prog2.tabnr
    inner join hrp1000 as hrp on prog1.objid = hrp.objid and prog1.plvar = hrp.plvar and prog1.otype = hrp.otype
        and hrp.begda <= $parameters.p_date and hrp.endda >= $parameters.p_date
    inner join dd07t as text on text.domname = 'PIQPROG_TYPE' and text.ddlanguage = $parameters.p_lang and text.domvalue_l = prog2.prog_type
    inner join dd07t as text1 on text1.domname = 'PIQPROG_GR_RESULT_STATUS' and text1.ddlanguage = $parameters.p_lang  and text1.domvalue_l = prog2.result_status

    association [0..*] to PiqCdsProgramType                 as _ProgramType on _ProgramType.ProgramOfStudyType = prog1.progc_var
    association [0..*] to PiqCdsAcdmcPrgrsClassification    as _ProgressClassification on _ProgressClassification.AcdmcPrgrsClassification = prog2.prcl
    association [0..*] to PiqCdsAcdmcPrgrsClassification    as _ProgressClassification1 on _ProgressClassification1.AcdmcPrgrsClassification = prog2.prclfa
    association [0..*] to PiqCdsAcademicYear                as _AcademicYear on _AcademicYear.AcademicYear = prog2.peryr
    association [0..*] to PiqCdsAcademicSession             as _AcademicSession on _AcademicSession.AcademicSession = prog2.perid
    association [0..*] to PiqCdsAcdmcPrgrssnStanding        as _AcademicPrgrssnStanding on _AcademicPrgrssnStanding.AcdmcPrgrssnStanding = prog2.acst
                                                            and _AcademicPrgrssnStanding.ProgramType = prog1.progc_var
    association [0..*] to PiqCdsAcdmcPrgrssnStndgFin        as _AcademicPrgrssnStandingFin on _AcademicPrgrssnStandingFin.AcdmcPrgrssnStandingFinAid = prog2.acstfa
                                                            and _AcademicPrgrssnStandingFin.ProgramType = prog1.progc_var
    association [0..*] to PiqCdsAcademicHonors              as _AcademicHonors on _AcademicHonors.AcademicHonors = prog2.award
                                                            and _AcademicHonors.ProgramType = prog1.progc_var


    {
    prog1.plvar as ActivePlanVersion,
    prog1.objid as StudentObjectId,
    hrp.stext   as StudentName,
    hrp.short   as StudentNumber,

    _ProgramType.ProgramOfStudyType,
    _ProgramType._ProgramTypeText[1:Language = $parameters.p_lang].ProgramOfStudyTypeName,

    prog1.prog_type         as AcdmcPrgrssnCategory,
    text.ddtext             as AcdmcPrgrssnCategoryName,
    prog2.tabnr             as AcdmcProgression,
    prog2.tabseqnr          as AcdmcProgressionSequence,
    prog2.valid_from        as AcdmcPrgrssnValidFrom,
    prog2.mc_valid_to       as AcdmcPrgrssnValidTo,
    prog2.check_from        as AcdmcPrgrssnStartDate,
    prog2.check_to          as AcdmcPrgrssnEndDate,

    _AcademicPrgrssnStanding.AcdmcPrgrssnStanding,
    _AcademicPrgrssnStanding._PrgrssnStandingText[1:Language = $parameters.p_lang].AcdmcPrgrssnStandingName,

    _AcademicPrgrssnStandingFin.AcdmcPrgrssnStandingFinAid,
    _AcademicPrgrssnStandingFin._PrgrssnStandingFinText[1:Language = $parameters.p_lang].AcdmcPrgrssnStandingFinAidName,

    _AcademicHonors.AcademicHonors,
    _AcademicHonors._AcademicHonorsText[1: Language = $parameters.p_lang].AcademicHonorsName,

    _ProgressClassification.AcdmcPrgrsClassification,
    _ProgressClassification._ProgressClassificationText[1:Language = $parameters.p_lang].AcdmcPrgrsClassificationName,

    _ProgressClassification1.AcdmcPrgrsClassification as AcdmcPrgrsClassfctnFinAid,
    _ProgressClassification1._ProgressClassificationText[1:Language = $parameters.p_lang].AcdmcPrgrsClassificationName as AcdmcPrgrsClassfctnFinAidName,

    prog2.result_status as AcdmcPrgrssnResultStatus,
    text1.ddtext        as AcdmcPrgrssnResultStatusName,

    _AcademicYear.AcademicYear,
    _AcademicYear._AcademicYearText[1:Language = $parameters.p_lang].AcademicYearName,

    _AcademicSession.AcademicSession,
    _AcademicSession._AcademicSessionText[1:Language = $parameters.p_lang].AcademicSessionName
    }

    where prog1.otype = 'ST'
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"PIQCDSACADEMICHONORS",
"PIQCDSACADEMICHONORSTEXT",
"PIQCDSACADEMICSESSION",
"PIQCDSACADEMICSESSIONTEXT",
"PIQCDSACADEMICYEAR",
"PIQCDSACADEMICYEARTEXT",
"PIQCDSACDMCPRGRSCLASSFCTNTEXT",
"PIQCDSACDMCPRGRSCLASSIFICATION",
"PIQCDSACDMCPRGRSSNSTANDING",
"PIQCDSACDMCPRGRSSNSTANDINGTEXT",
"PIQCDSACDMCPRGRSSNSTNDGFIN",
"PIQCDSACDMCPRGRSSNSTNDGFINTEXT",
"PIQCDSPROGRAMTYPE",
"PIQCDSPROGRAMTYPETEXT",
"DD07T",
"HRP1000",
"HRP1737",
"HRT1737"
],
"ASSOCIATED":
[
"PIQCDSACADEMICHONORS",
"PIQCDSACADEMICSESSION",
"PIQCDSACADEMICYEAR",
"PIQCDSACDMCPRGRSCLASSIFICATION",
"PIQCDSACDMCPRGRSSNSTANDING",
"PIQCDSACDMCPRGRSSNSTNDGFIN",
"PIQCDSPROGRAMTYPE"
],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0
}
}*/