I_PostalWorkplaceAddress

@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.supportedCapabilities: [#CDS_MODELING_ASSOCIATION_TARGET, #CDS_MODELING_DATA_SOURCE, #SQL_DATA_SOURCE]
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@VDM: {
  viewType: #BASIC,
  lifecycle.contract.type: #PUBLIC_LOCAL_API
}
@EndUserText.label: 'Person Address type from ADRC & ADCP'
define view entity I_PostalWorkplaceAddress
  as select from I_AddressWorkplaceDetails as wp //ADCP

  association [0..1] to I_AddrOrgNamePostalAddress as _Postal_1 on  $projection.AddressID                 = _Postal_1.AddressID //ADRC

                                                                and $projection.AddressRepresentationCode = _Postal_1.AddressRepresentationCode
{
  key wp.AddressID,
  key wp.AddressPersonID,
      wp.AddressRepresentationCode,
      wp.PersonAddressObjectType,
      wp.WorkplaceBuilding,
      wp.WorkplaceFloor,
      wp.WorkplaceRoomNumber,
      wp.WorkplaceFunctionalTitleName,
      wp.WorkplaceDepartmentName,
      wp.WorkplacePrfrdCommMediumType,
      wp.WorkplaceCorrespncShortName,
      wp.WorkplaceInhouseMail,
      _Postal_1.CorrespondenceLanguage,
      _Postal_1.AddressIsPersonAddress,
      _Postal_1.PrfrdCommMediumType,
      _Postal_1.CityNumber,
      _Postal_1.CityName,
      _Postal_1.DistrictName,
      _Postal_1.VillageName,
      _Postal_1.PostalCode,
      _Postal_1.CompanyPostalCode,
      _Postal_1.POBox,
      _Postal_1.POBoxAddrNonDeliverableReason,
      _Postal_1.POBoxIsWithoutNumber,
      _Postal_1.POBoxPostalCode,
      _Postal_1.POBoxLobbyName,
      _Postal_1.POBoxDeviatingCityName,
      _Postal_1.POBoxDeviatingCityCode,
      _Postal_1.POBoxDeviatingRegion,
      _Postal_1.POBoxDeviatingCountry,
      _Postal_1.Street,
      _Postal_1.StreetName,
      _Postal_1.StreetAddrNonDeliverableReason,
      _Postal_1.StreetPrefixName1,
      _Postal_1.StreetPrefixName2,
      _Postal_1.StreetSuffixName1,
      _Postal_1.StreetSuffixName2,
      _Postal_1.HouseNumber,
      _Postal_1.HouseNumberSupplementText,
      _Postal_1.Building,
      _Postal_1.Floor,
      _Postal_1.RoomNumber,
      _Postal_1.Country,
      _Postal_1.Region,
      _Postal_1.FormOfAddress,
      _Postal_1.CareOfName,
      _Postal_1.AddresseeName1,
      _Postal_1.AddresseeName2,
      _Postal_1.AddresseeName3,
      _Postal_1.AddresseeName4,
      _Postal_1.TaxJurisdiction,
      _Postal_1.TransportZone,
      _Postal_1.DeliveryServiceTypeCode,
      _Postal_1.DeliveryServiceNumber,
      _Postal_1.AddressTimeZone,
      _Postal_1.SecondaryRegion,
      _Postal_1.SecondaryRegionName,
      _Postal_1.TertiaryRegion,
      _Postal_1.TertiaryRegionName,
      _Postal_1.AddressSearchTerm1,
      _Postal_1.AddressSearchTerm2,
      _Postal_1.RegionalStructureCheckStatus,
      _Postal_1.AddressGroup,
      _Postal_1.AddressCreatedByUser,
      _Postal_1.AddressCreatedOnDateTime,
      _Postal_1.AddressChangedByUser,
      _Postal_1.AddressChangedOnDateTime,
      _Postal_1._Country,
      _Postal_1._Region,
      _Postal_1._TransportationZone,

      case
        when wp.AddressPersonID is not initial
            and _Postal_1.AddressIsPersonAddress = 'X'
            then '2' // Person Type

        when wp.AddressPersonID is not initial
            and _Postal_1.AddressIsPersonAddress is initial
            then '3' //Workplace Type

        else '3'
       end as AddressType
}

union

select from I_AddrOrgNamePostalAddress as _Postal_2
{
  key   _Postal_2.AddressID,
  key   ''  as AddressPersonID,
        _Postal_2.AddressRepresentationCode,
        ''  as PersonAddressObjectType,
        ''  as WorkplaceBuilding,
        ''  as WorkplaceFloor,
        ''  as WorkplaceRoomNumber,
        ''  as WorkplaceFunctionalTitleName,
        ''  as WorkplaceDepartmentName,
        ''  as WorkplacePrfrdCommMediumType,
        ''  as WorkplaceCorrespncShortName,
        ''  as WorkplaceInhouseMail,
        _Postal_2.CorrespondenceLanguage,
        _Postal_2.AddressIsPersonAddress,
        _Postal_2.PrfrdCommMediumType,
        _Postal_2.CityNumber,
        _Postal_2.CityName,
        _Postal_2.DistrictName,
        _Postal_2.VillageName,
        _Postal_2.PostalCode,
        _Postal_2.CompanyPostalCode,
        _Postal_2.POBox,
        _Postal_2.POBoxAddrNonDeliverableReason,
        _Postal_2.POBoxIsWithoutNumber,
        _Postal_2.POBoxPostalCode,
        _Postal_2.POBoxLobbyName,
        _Postal_2.POBoxDeviatingCityName,
        _Postal_2.POBoxDeviatingCityCode,
        _Postal_2.POBoxDeviatingRegion,
        _Postal_2.POBoxDeviatingCountry,
        _Postal_2.Street,
        _Postal_2.StreetName,
        _Postal_2.StreetAddrNonDeliverableReason,
        _Postal_2.StreetPrefixName1,
        _Postal_2.StreetPrefixName2,
        _Postal_2.StreetSuffixName1,
        _Postal_2.StreetSuffixName2,
        _Postal_2.HouseNumber,
        _Postal_2.HouseNumberSupplementText,
        _Postal_2.Building,
        _Postal_2.Floor,
        _Postal_2.RoomNumber,
        _Postal_2.Country,
        _Postal_2.Region,
        _Postal_2.FormOfAddress,
        _Postal_2.CareOfName,
        _Postal_2.AddresseeName1,
        _Postal_2.AddresseeName2,
        _Postal_2.AddresseeName3,
        _Postal_2.AddresseeName4,
        _Postal_2.TaxJurisdiction,
        _Postal_2.TransportZone,
        _Postal_2.DeliveryServiceTypeCode,
        _Postal_2.DeliveryServiceNumber,
        _Postal_2.AddressTimeZone,
        _Postal_2.SecondaryRegion,
        _Postal_2.SecondaryRegionName,
        _Postal_2.TertiaryRegion,
        _Postal_2.TertiaryRegionName,
        _Postal_2.AddressSearchTerm1,
        _Postal_2.AddressSearchTerm2,
        _Postal_2.RegionalStructureCheckStatus,
        _Postal_2.AddressGroup,
        _Postal_2.AddressCreatedByUser,
        _Postal_2.AddressCreatedOnDateTime,
        _Postal_2.AddressChangedByUser,
        _Postal_2.AddressChangedOnDateTime,
        _Postal_2._Country,
        _Postal_2._Region,
        _Postal_2._TransportationZone,
        '1' as AddressType
}
where
  _Postal_2.AddressIsPersonAddress is initial