I_ScriptedPersonAddress

@AbapCatalog.sqlViewName: 'ISCRPTPRSNADDR'
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true
//@Analytics : {dataCategory: #DIMENSION}

@AccessControl.authorizationCheck:#PRIVILEGED_ONLY
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #B
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.representativeKey: 'AddressNumber'
@Metadata.allowExtensions:true
@EndUserText.label: 'Interational Address Version for Persons'
define view I_ScriptedPersonAddress
  as select from adrp
    inner join   adcp on  adcp.persnumber = adrp.persnumber
                      and adcp.date_from  = '00010101'
                      and adcp.nation    != ' '
                      and adcp.nation     = adrp.nation
                      and adcp.comp_pers  = 'P'
    inner join   adrc on  adcp.addrnumber = adrc.addrnumber
                      and adcp.nation     = adrc.nation
{
  key adcp.addrnumber   as AddressNumber,
  key adrp.nation       as AddressRepresentationCode,
  key adrp.persnumber   as Person,

      name_co           as CareOfName,
      location          as AdditionalStreetSuffixName,
      adrc.langu        as CorrespondenceLanguage,

      adrc.deflt_comm   as PrfrdCommMediumType,

      po_box            as POBox,
      po_box_num        as POBoxIsWithoutNumber,
      post_code2        as POBoxPostalCode,
      po_box_lobby      as POBoxLobbyName,
      po_box_loc        as POBoxDeviatingCityName,
      po_box_reg        as POBoxDeviatingRegion,
      po_box_cty        as POBoxDeviatingCountry,

      deli_serv_type    as DeliveryServiceTypeCode,
      deli_serv_number  as DeliveryServiceNumber,
      time_zone         as AddressTimeZone,
      adrc.chckstatus   as CityFileTestStatus,
      adrc.dont_use_s   as AddressNonDeliverableReason,
      adrc.dont_use_p   as PostBoxNonDeliverableReason,

      cast(concat_with_space( name_first, name_last,1)
        as adfullname ) as FullName,

      city1             as CityName,
      city2             as District,
      city_code         as CityCode,

      home_city         as HomeCityName,
      post_code1        as PostalCode,
      post_code3        as CompanyPostalCode,

      street            as StreetName,
      str_suppl1        as StreetPrefixName,
      str_suppl2        as AdditionalStreetPrefixName,
      str_suppl3        as StreetSuffixName,

      house_num1        as HouseNumber,
      house_num2        as HouseNumberSupplementText,
      adrc.building     as Building,
      adrc.floor        as Floor,
      adrc.roomnumber   as RoomNumber,
      country           as Country,
      region            as Region,
      county            as County,
      county_code       as CountyCode,

      //      adrc.title        as FormOfAddress,

      adrc.tel_number   as PhoneNumber,
      adrc.fax_number   as FaxNumber,
      mc_street         as StreetSearch,
      mc_city1          as CitySearch,

      taxjurcode        as TaxJurisdiction,
      transpzone        as TransportZone,
      city_code2        as AddressCityPostBoxCode,

      // Disabled NULL handling due to Performance. Do Null Handling in consuming layer, please

      //  We tested that it is cheaper to add the OR conditions into the child-associations

      //      case

      //          when adcp.persnumber is null then '          '

      //      -- possible alternative to the above when adrc.pers_addr = ' ' then '          '

      //          else adcp.persnumber

      //        end                                                                                       as Person,

      //      adcp.persnumber   as Person,

      //      adrp.persnumber,

      //      adrp.date_from,

      //adrp.nation,

      //      adrp.date_to,

      adrp.title        as FormOfAddress,
      adrp.sort1        as SearchTerm1,
      adrp.sort2        as SearchTerm2,

      adrp.name_first   as FirstName,
      adrp.name_last    as LastName,
      adrp.name2        as BusinessPartnerBirthName,
      adrp.namemiddle   as MiddleName,
      adrp.name_last2   as AdditionalLastName,
      adrp.name_text    as PersonFullName,
      //      adrp.converted,

      adrp.title_aca1   as AcademicTitle,
      adrp.title_aca2   as AcademicTitle2,
      adrp.prefix1      as LastNamePrefix,
      adrp.prefix2      as LastNameSecondPrefix,
      adrp.title_sppl   as BusinessPartnerSupplementName,
      adrp.nickname     as BusinessPartnerNicknameLabel,
      adrp.initials     as Initials,
      adrp.nameformat   as NameFormat,
      adrp.namcountry   as NameCountry,
      //      adrp.profession,

      //      adrp.sex,

      //      adrp.langu,

      //      adrp.pers_group,

      //      adrp.flaggroups,

      //      adrp.sort_phn,

      //      adrp.addr_comp,

      //      adrp.addr_pers,

      //      adrp.flperstel,

      //      adrp.flperscomm,

      //      adrp.persorigin,

      adrp.mc_namefir   as BPFirstNameSearchHelp,
      adrp.mc_namelas   as BPLastNameSearchHelp
      //      adrp.mc_name2,

      //      adrp.langu_crea,

      //      adrp.adrp_uuid,

      //      adrp.uuid_belated,

      //      adrp.id_category,

      //      adrp.adrp_err_status,

      //      adrp._dataaging

}
where
      adrp.date_from = '00010101'
  and adrp.nation != ' '