I_ScriptedAddress

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

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

      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(substring(replace(concat(name1, concat(' &@', adrc.name2)), '&@', ''),1,80)
        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,


      name1             as BusinessPartnerName1,
      adrc.name2        as BusinessPartnerName2,
      name3             as BusinessPartnerName3,
      name4             as BusinessPartnerName4,
      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,

      case when
        adcp.persnumber is null then adrc.title else adrp.title
      end               as FormOfAddress,
      case when
        adcp.persnumber is null then adrc.sort1 else adrp.sort1
      end               as SearchTerm1,
      case when
        adcp.persnumber is null then adrc.sort2 else adrp.sort2
      end               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,

      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
      adrc.date_from = '00010101'
  and adrc.nation != ' '