R_WorkAssgmtPrivateAddressTP

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@EndUserText.label: 'Private Address for Work Assignment'
@Metadata.ignorePropagatedAnnotations: true
@VDM: {
  viewType: #TRANSACTIONAL,
  lifecycle.contract.type: #SAP_INTERNAL_API
}
@ObjectModel.usageType:{
  serviceQuality: #C,
  sizeCategory: #S,
  dataClass: #MIXED
}
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Consumption.dbHints: [ '&PREFER_JOIN_WITH_FDA 0&', 'USE_HEX_PLAN' ]
define view entity R_WorkAssgmtPrivateAddressTP

  as select distinct from I_Businesspartneraddressusage as _BPAddressUsage
    inner join            R_WorkAssignmentBP            as _BuPaIdentification on _BuPaIdentification.WorkAssignmentBusinessPartner = _BPAddressUsage.BusinessPartner

    inner join            I_BusPartAddress              as _BPAddress          on  _BPAddressUsage.BusinessPartner = _BPAddress.BusinessPartner
                                                                               and _BPAddressUsage.AddressNumber   = _BPAddress.AddressID
  //  association [1..1] to I_WorkAssignmentDetail_1 as _AssgmtDetails      on  _AssgmtDetails.WorkforceAssignment       = _BuPaIdentification.WorkforceAssignment

  //                                                                        and _AssgmtDetails.WorkforceAssgmtStartDate  <= $session.system_date

  //                                                                        and _AssgmtDetails.WorkforceAssgmtEndDate    >= $session.system_date

  //                                                                        and _AssgmtDetails.WorkforceAssignmentStatus = '1'

  association [0..1] to I_BPProtectedAddress      as _BPProtectedAddress  on  $projection.BusinessPartner = _BPProtectedAddress.BusinessPartner
                                                                          and $projection.AddressNumber   = _BPProtectedAddress.AddressID
  association [0..1] to I_CountryText             as _CountryText         on  $projection.Country   = _CountryText.Country
                                                                          and _CountryText.Language = $session.system_language
  association [0..1] to I_RegionText              as _RegionText          on  $projection.Region   = _RegionText.Region
                                                                          and $projection.Country  = _RegionText.Country
                                                                          and _RegionText.Language = $session.system_language
  association [0..1] to I_TaxJurisdictionText     as _TaxJurisdictionText on  $projection.TaxJurisdiction   = _TaxJurisdictionText.TaxJurisdiction
                                                                          and _TaxJurisdictionText.Language = $session.system_language
  association [0..1] to I_BusPartDelivSrvcTxt     as _BusPartDelivSrvcTxt on  $projection.DeliveryServiceTypeCode = _BusPartDelivSrvcTxt.DeliveryServiceTypeCode
                                                                          and _BusPartDelivSrvcTxt.Language       = $session.system_language
  //Below association/Join is to get the UUID

  association [1..1] to I_BusinessPartner         as _WorkAssignmentBP    on  _WorkAssignmentBP.BusinessPartner = _BuPaIdentification.WorkAssignmentBusinessPartner

  //  association [1..1] to I_BusinessPartner         as _BP                 on  _BP.BusinessPartner = _BPAddressUsage.BusinessPartner


  //Below association required for deriving PersonUUID - because BEHDEF authorization/lock is depends on PersonUUID

  association [1..1] to I_BusinessPartner         as _WorkforcePersonBP   on  _WorkforcePersonBP.BusinessPartner = _BuPaIdentification.BusinessPartner

  //Below associations for handling DCL

  association [1..1] to R_WorkforcePersonTP       as _WorkforcePerson     on  $projection.PersonUUID = _WorkforcePerson.PersonUUID
  association        to parent R_WorkAssignmentTP as _WorkAssignment      on  $projection.WorkAssignmentUUID = _WorkAssignment.WorkAssignmentUUID

{
  key      _WorkAssignmentBP.BusinessPartnerUUID                                                          as WorkAssignmentUUID,
  key      _BPAddressUsage.AddressNumber                                                                  as AddressNumber,
  key      cast (tstmp_to_dats( _BPAddressUsage.ValidityStartDate,
           abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ) as vdm_v_start_date) as ValidityStartDate,
           cast (tstmp_to_dats( _BPAddressUsage.ValidityEndDate,
           abap_system_timezone( $session.client,'NULL' ), $session.client, 'NULL' ) as vdm_v_end_date)   as ValidityEndDate,
           _BPAddressUsage.BusinessPartner                                                                as BusinessPartner,
           _BPAddress._AddressDefaultRepresentation.HouseNumber                                           as HouseNumber,
           _BPAddress._AddressDefaultRepresentation.HouseNumberSupplementText                             as HouseNumberSupplementText, //HouseSupplement,

           _BPAddress._AddressDefaultRepresentation.Region                                                as Region, //AddressRegion,

           _BPAddress._AddressDefaultRepresentation.PostalCode                                            as PostalCode, //AddressPostalCode,

           //           _BPAddress._AddressDefaultRepresentation.DistrictNumber                                        as DistrictNumber,

           _BPAddress._AddressDefaultRepresentation.DistrictName                                          as DistrictName,
           _BPAddress._AddressDefaultRepresentation.Street                                                as Street,
           _BPAddress._AddressDefaultRepresentation.StreetName                                            as StreetName,
           _BPAddress._AddressDefaultRepresentation.CityNumber                                            as CityNumber,
           _BPAddress._AddressDefaultRepresentation.CityName                                              as CityName, //City,

           _BPAddress._AddressDefaultRepresentation.Floor                                                 as Floor,
           _BPAddress._AddressDefaultRepresentation.Country                                               as Country, //CountryOrRegion

           _BPAddress._AddressDefaultRepresentation.RoomNumber                                            as RoomNumber,
           _BPAddress._AddressDefaultRepresentation.TaxJurisdiction                                       as TaxJurisdiction,
           _BPAddress._AddressDefaultRepresentation.CareOfName                                            as CareOfName,
           _BPAddress._AddressDefaultRepresentation.StreetPrefixName1                                     as StreetPrefixName1,
           _BPAddress._AddressDefaultRepresentation.StreetPrefixName2                                     as StreetPrefixName2,
           _BPAddress._AddressDefaultRepresentation.StreetSuffixName1                                     as StreetSuffixName1,
           _BPAddress._AddressDefaultRepresentation.StreetSuffixName2                                     as StreetSuffixName2,

           //API Fields to avoid Data loss

           _BPAddress._AddressDefaultRepresentation.SecondaryRegion                                       as SecondaryRegion,
           _BPAddress._AddressDefaultRepresentation.SecondaryRegionName                                   as SecondaryRegionName,
           _BPAddress._AddressDefaultRepresentation.TertiaryRegion                                        as TertiaryRegion,
           _BPAddress._AddressDefaultRepresentation.TertiaryRegionName                                    as TertiaryRegionName,
           _BPAddress._AddressDefaultRepresentation.AddressRepresentationCode                             as AddressRepresentationCode,

           //PO BOX related fields

           _BPAddress._AddressDefaultRepresentation.POBoxPostalCode                                       as POBoxPostalCode, //PostOfficeBoxPostalCode,

           _BPAddress._AddressDefaultRepresentation.POBox                                                 as POBox,
           _BPAddress._AddressDefaultRepresentation.POBoxLobbyName                                        as POBoxLobbyName,
           _BPAddress._AddressDefaultRepresentation.POBoxDeviatingCountry                                 as POBoxDeviatingCountry,
           _BPAddress._AddressDefaultRepresentation.POBoxDeviatingRegion                                  as POBoxDeviatingRegion,
           _BPAddress._AddressDefaultRepresentation.POBoxDeviatingCityCode                                as POBoxDeviatingCityCode,
           _BPAddress._AddressDefaultRepresentation.POBoxDeviatingCityName                                as POBoxDeviatingCityName,
           _BPAddress._AddressDefaultRepresentation.POBoxIsWithoutNumber                                  as POBoxIsWithoutNumber,

           _BPAddress._AddressDefaultRepresentation.DeliveryServiceTypeCode                               as DeliveryServiceTypeCode,
           _BPAddress._AddressDefaultRepresentation.DeliveryServiceNumber                                 as DeliveryServiceNumber,

           _WorkforcePersonBP.BusinessPartnerUUID                                                         as PersonUUID,

           /* Associations */
           _CountryText,
           _RegionText,
           _TaxJurisdictionText,
           _BusPartDelivSrvcTxt,
           _WorkAssignment,
           _WorkforcePerson,
           _BPProtectedAddress

}
where
  _BPAddressUsage.AddressUsage = 'HCM001'