I_REIntegObjectValidity

@AbapCatalog.sqlViewName: 'IREINTOBJVAL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Real Estate Integ Obj Validity'
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L

@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
define view I_REIntegObjectValidity
  as select from I_REIntegrationObjectBasicData as _REIntegrationObject
  association [0..1] to I_REIntegrationObjectTimeline as _REIntegrationObjectTimeline on  _REIntegrationObject.InternalRealEstateNumber            = _REIntegrationObjectTimeline.InternalRealEstateNumber
                                                                                      and _REIntegrationObjectTimeline.REIntegrationObjectIsActive = 'X'
{
  key  _REIntegrationObject.InternalRealEstateNumber,
       _REIntegrationObject.RealEstateExternalID,
       cast( min( case when _REIntegrationObjectTimeline.InternalRealEstateNumber is not null and _REIntegrationObjectRelation.InternalRealEstateNumber is null
                 then _REIntegrationObjectTimeline.ValidityStartDate
                 else '00000000' end ) as rebdvdmvalidfrom ) as ValidityStartDate,
       cast( max( case when _REIntegrationObjectTimeline.InternalRealEstateNumber is not null and _REIntegrationObjectRelation.InternalRealEstateNumber is null
                 then _REIntegrationObjectTimeline.ValidityEndDate
                 else '99991231' end ) as rebdvdmvalidto  )  as ValidityEndDate
}
where
  _REIntegrationObject.REIntegrationObjectType <> 'C4SG'
group by
  _REIntegrationObject.InternalRealEstateNumber,
  _REIntegrationObject.RealEstateExternalID
union all select from    I_REIntegrationObjectBasicData as _REIntegrationObject
  left outer to one join I_REIntegrationObjectRelation  as _REIntegrationObjectRelation on _REIntegrationObject.InternalRealEstateNumber = _REIntegrationObjectRelation.InternalRealEstateNumber
//  association [0..1] to I_REIntegrationObjectTimeline as _REIntegObjectTimelineRltn   on  _REIntegrationObjectRelation.InternalRealEstateNumberRltn = _REIntegObjectTimelineRltn.InternalRealEstateNumber

association [0..1] to I_REIntegrationObjectTimeline as _REIntegObjectTimelineRltn on  _REIntegrationObjectRelation.InternalRealEstateNumberRltn = _REIntegObjectTimelineRltn.InternalRealEstateNumber
                                                                                  and _REIntegObjectTimelineRltn.REIntegrationObjectIsActive    = 'X'
{
  key _REIntegrationObject.InternalRealEstateNumber,
      _REIntegrationObject.RealEstateExternalID,
      cast( min( case when _REIntegrationObjectRelation.InternalRealEstateNumber is not null and _REIntegObjectTimelineRltn.InternalRealEstateNumber is null
                then _REIntegrationObjectRelation.ValidityStartDate
                when _REIntegrationObjectRelation.InternalRealEstateNumber is not null and _REIntegObjectTimelineRltn.InternalRealEstateNumber is not null
                then case when _REIntegrationObjectRelation.ValidityStartDate > _REIntegObjectTimelineRltn.ValidityStartDate
                          then _REIntegrationObjectRelation.ValidityStartDate
                          else _REIntegObjectTimelineRltn.ValidityStartDate end
                else '00000000' end ) as rebdvdmvalidfrom ) as ValidityStartDate,
      cast( max( case when _REIntegrationObjectRelation.InternalRealEstateNumber is not null and _REIntegObjectTimelineRltn.InternalRealEstateNumber is null
                then _REIntegrationObjectRelation.ValidityEndDate
                when _REIntegrationObjectRelation.InternalRealEstateNumber is not null and _REIntegObjectTimelineRltn.InternalRealEstateNumber is not null
                then case when _REIntegrationObjectRelation.ValidityEndDate < _REIntegObjectTimelineRltn.ValidityEndDate
                          then _REIntegrationObjectRelation.ValidityEndDate
                          else _REIntegObjectTimelineRltn.ValidityEndDate end
                else '99991231' end ) as rebdvdmvalidto )   as ValidityEndDate
}
where
  _REIntegrationObject.REIntegrationObjectType = 'C4SG'
group by
  _REIntegrationObject.InternalRealEstateNumber,
  _REIntegrationObject.RealEstateExternalID;

//  as select from P_REIntegObjectValidityCalc2

//{

//  key InternalRealEstateNumber,

//      RealEstateExternalID,

//      cast( ValidityStartDate as rebdvdmvalidfrom preserving type ) as ValidityStartDate,

//      cast( ValidityEndDate as rebdvdmvalidto preserving type ) as  ValidityEndDate

////      _REIntegrationObject

//}

//union select from P_REIntegObjectValidityCalc4

//{

//  key InternalRealEstateNumber,

//      RealEstateExternalID,

//      cast( ValidityStartDate as rebdvdmvalidfrom preserving type ) as ValidityStartDate,

//      cast( ValidityEndDate as rebdvdmvalidto preserving type ) as  ValidityEndDate

////      _REIntegrationObject

//}

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_REINTEGRATIONOBJECTBASICDATA",
"I_REINTEGRATIONOBJECTRELATION",
"I_REINTEGRATIONOBJECTTIMELINE"
],
"ASSOCIATED":
[
"I_REINTEGRATIONOBJECTTIMELINE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/