@AbapCatalog.sqlViewName: 'PREINTOBJOCCCAL1'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
define view P_REIntegObjectOccpcyCalc1
as select from I_REObjectAssgmt as _REObjectAssgmt
inner join I_REIntegrationObject as _REIntegrationObject on _REObjectAssgmt.REStatusObjectTarget = _REIntegrationObject.REStatusObject
and _REObjectAssgmt.REObjectAssignmentType = '10'
and _REObjectAssgmt.REOnlyInfoAssgmt = ' '
inner join I_REContract as _REContract on _REObjectAssgmt.REStatusObjectSource = _REContract.REStatusObject
and _REObjectAssgmt.REObjectAssignmentType = '10'
and _REObjectAssgmt.REOnlyInfoAssgmt = ' '
{
_REIntegrationObject.REStatusObject as REIntegObjectStatusObject,
_REContract.REStatusObject as REContractStatusObject,
_REIntegrationObject.InternalRealEstateNumber as REIntegObjectInternalNumber,
_REContract.InternalRealEstateNumber as REContractInternalNumber,
_REContract.CompanyCode as CompanyCode,
_REContract.RealEstateContract as RealEstateContract,
case when _REObjectAssgmt.ValidityStartDate > _REContract.ContractStartDate then _REObjectAssgmt.ValidityStartDate
when _REIntegrationObject.ValidityStartDate > _REContract.ContractStartDate then _REIntegrationObject.ValidityStartDate
else _REContract.ContractStartDate
end as REOccupancyFromDate,
case when _REObjectAssgmt.ValidityEndDate < _REContract.ContractEndDate then _REObjectAssgmt.ValidityEndDate
when _REIntegrationObject.ValidityEndDate < _REContract.ContractEndDate then _REIntegrationObject.ValidityEndDate
else _REContract.ContractEndDate
end as REOccupancyToDate,
_REIntegrationObject.ValidityStartDate as REIntegObjectValidityStartDate,
_REIntegrationObject.ValidityEndDate as REIntegObjectValidityEndDate,
_REContract.ContractStartDate as ContractStartDate,
_REContract.ContractEndDate as ContractEndDate,
_REObjectAssgmt.ValidityStartDate as REObjAssgmtValidityStartDate,
_REObjectAssgmt.ValidityEndDate as REObjAssgmtValidityEndDate,
_REObjectAssgmt.REObjectPossessionStartDate as REObjectPossessionStartDate,
_REObjectAssgmt.REObjectPossessionEndDate as REObjectPossessionEndDate,
_REContract.REPossessionStartDate as REPossessionStartDate,
_REContract.REPossessionEndDate as REPossessionEndDate,
_REObjectAssgmt.REStatusObjectSourceIsArchived as REStatusObjectSourceIsArchived,
case when _REContract.REContractActivateDate <> '00000000' then 'X' else ' ' end as REContractIsActive
}
where
_REContractType.REContractUserSupplier = '0'
and(
_REIntegrationObject.REIntegObjSpaceGroupType = 'EO01' //External Occupancy Object
or _REIntegrationObject.REIntegObjSpaceGroupType = 'IC01' //Intercompany Object
or _REIntegrationObject.REIntegObjSpaceGroupType = 'IO01' //Internal Occupancy Object
)
union select from I_REObjectAssgmt as _REObjectAssgmt
inner join I_REIntegrationObject as _REIntegrationObject on _REObjectAssgmt.REStatusObjectTarget = _REIntegrationObject.REStatusObject
and _REObjectAssgmt.REObjectAssignmentType = '10'
and _REObjectAssgmt.REOnlyInfoAssgmt = ' '
and _REObjectAssgmt.REStatusObjectSourceIsArchived = 'X'
inner join I_REObjectAssgmtArchived as _REObjectAssgmtArchived on _REObjectAssgmt.REStatusObjectSource = _REObjectAssgmtArchived.REStatusObjectSource
and _REObjectAssgmt.REObjectAssignmentType = _REObjectAssgmtArchived.REObjectAssignmentType
and _REObjectAssgmt.REStatusObjectTarget = _REObjectAssgmtArchived.REStatusObjectTarget
inner join I_REObjectArchived as _REObjectArchived on _REObjectAssgmtArchived.REStatusObjectSource = _REObjectArchived.REStatusObject
{
_REIntegrationObject.REStatusObject as REIntegObjectStatusObject,
_REObjectArchived.REStatusObject as REContractStatusObject,
_REIntegrationObject.InternalRealEstateNumber as REIntegObjectInternalNumber,
_REObjectArchived.InternalRealEstateNumber as REContractInternalNumber,
substring(_REObjectArchived.REStatusObject, 3, 4) as CompanyCode,
substring(_REObjectArchived.REStatusObject, 7, 13) as RealEstateContract,
_REObjectAssgmtArchived.ValidityStartDate as REOccupancyFromDate,
_REObjectAssgmtArchived.ValidityEndDate as REOccupancyToDate,
_REIntegrationObject.ValidityStartDate as REIntegObjectValidityStartDate,
_REIntegrationObject.ValidityEndDate as REIntegObjectValidityEndDate,
_REObjectAssgmtArchived.ContractStartDate as ContractStartDate,
_REObjectAssgmtArchived.ContractEndDate as ContractEndDate,
_REObjectAssgmt.ValidityStartDate as REObjAssgmtValidityStartDate,
_REObjectAssgmt.ValidityEndDate as REObjAssgmtValidityEndDate,
_REObjectAssgmt.REObjectPossessionStartDate as REObjectPossessionStartDate,
_REObjectAssgmt.REObjectPossessionEndDate as REObjectPossessionEndDate,
_REObjectAssgmtArchived.REObjectPossessionStartDate as REPossessionStartDate,
_REObjectAssgmtArchived.REObjectPossessionEndDate as REPossessionEndDate,
_REObjectAssgmt.REStatusObjectSourceIsArchived as REStatusObjectSourceIsArchived,
' ' as REContractIsActive
}
where
_REObjectArchived.REArchiveObjectType = 'REFX_CN'
and _REContractType.REContractUserSupplier = '0'
and(
_REIntegrationObject.REIntegObjSpaceGroupType = 'EO01' //External Occupancy Object
or _REIntegrationObject.REIntegObjSpaceGroupType = 'IC01' //Intercompany Object
or _REIntegrationObject.REIntegObjSpaceGroupType = 'IO01'
) //Internal Occupancy Object