P_HR_CompanyCodeAddress

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@VDM.viewType: #COMPOSITE
@VDM.private:true
@ObjectModel.usageType:{
  serviceQuality: #D,
  sizeCategory: #L,
  dataClass: #MASTER
}
define view entity P_HR_CompanyCodeAddress
  as select from I_CompanyTaxDetail
  //            join t000f         on t000f.xwiaa = 'X'  not needed when RITA this is not set

    join         I_CompanyCode on  I_CompanyCode.CompanyCode =  I_CompanyTaxDetail.CompanyCode
                               and I_CompanyCode.Country     <> I_CompanyTaxDetail.Country
    join         I_Address_2   on  I_Address_2.AddressID                 = I_CompanyTaxDetail.AddressID
                               and I_Address_2.AddressPersonID           is initial
                               and I_Address_2.AddressRepresentationCode is initial
{

  key I_CompanyTaxDetail.CompanyCode,
  key I_CompanyTaxDetail.Country,
      I_CompanyTaxDetail.VATRegistration,
      cast ( (substring(I_CompanyTaxDetail.VATRegistration, 3, 11)) as fiisvat_vatreg_without_prefix) as VATRegistration_wo_prefix,
      case when I_CompanyTaxDetail.CompanyCodeName is not initial
      then I_CompanyTaxDetail.CompanyCodeName
      when I_Address_2.OrganizationName1 is not initial
       then I_Address_2.OrganizationName1 end                                                         as CompanyCodeName,
      I_CompanyTaxDetail.AddressID,
      I_Address_2.PostalCode,
      concat_with_space(
         concat(concat_with_space( I_Address_2.StreetName, I_Address_2.HouseNumber, 1), ', ') ,
         concat_with_space(I_Address_2.PostalCode, I_Address_2.CityName, 1), 1
       )                                                                                              as CompanyCodeAddress,
      I_Address_2.StreetName,
      I_Address_2.HouseNumber,
      I_Address_2.CityName,

      I_Address_2._CurrentDfltLandlinePhoneNmbr.PhoneAreaCodeSubscriberNumber                         as PhoneNumber

}

union select from        I_CompanyTaxDetail
//            join t000f         on t000f.xwiaa = 'X'  not needed when RITA this is not set

  join                   I_CompanyCode on  I_CompanyCode.CompanyCode =  I_CompanyTaxDetail.CompanyCode
                                       and I_CompanyCode.Country     <> I_CompanyTaxDetail.Country
  left outer to one join I_Address_2   on  I_Address_2.AddressID                 = I_CompanyCode.AddressID
                                       and I_Address_2.AddressPersonID           is initial
                                       and I_Address_2.AddressRepresentationCode is initial
{

  key I_CompanyTaxDetail.CompanyCode,
  key I_CompanyTaxDetail.Country,
      I_CompanyTaxDetail.VATRegistration,
      cast ( (substring(I_CompanyTaxDetail.VATRegistration, 3, 11)) as fiisvat_vatreg_without_prefix) as VATRegistration_wo_prefix,
      case when I_CompanyTaxDetail.CompanyCodeName is not initial
      then I_CompanyTaxDetail.CompanyCodeName
      when I_Address_2.OrganizationName1 is not initial
       then I_Address_2.OrganizationName1 end                                                         as CompanyCodeName,
      I_CompanyCode.AddressID                                                                         as AddressID,
      I_Address_2.PostalCode,
      concat_with_space(
         concat(concat_with_space( I_Address_2.StreetName, I_Address_2.HouseNumber, 1), ', ') ,
         concat_with_space(I_Address_2.PostalCode, I_Address_2.CityName, 1), 1
       )                                                                                              as CompanyCodeAddress,
      I_Address_2.StreetName,
      I_Address_2.HouseNumber,
      I_Address_2.CityName,

      I_Address_2._CurrentDfltLandlinePhoneNmbr.PhoneAreaCodeSubscriberNumber                         as PhoneNumber

}

union select from        I_CompanyCode

  left outer to one join I_Address_2 on  I_Address_2.AddressID                 = I_CompanyCode.AddressID
                                     and I_Address_2.AddressPersonID           is initial
                                     and I_Address_2.AddressRepresentationCode is initial
{
  key I_CompanyCode.CompanyCode,
  key I_CompanyCode.Country,
      I_CompanyCode.VATRegistration,
      cast ( (substring(I_CompanyCode.VATRegistration, 3, 18) ) as fiisvat_vatreg_without_prefix) as VATRegistration_wo_prefix,
      I_CompanyCode.CompanyCodeName                                                               as CompanyCodeName,
      I_Address_2.AddressID,
      I_Address_2.PostalCode,

      concat_with_space( concat(concat_with_space(I_Address_2.StreetName, I_Address_2.HouseNumber, 1), ', '),
                         concat_with_space(I_Address_2.PostalCode, I_Address_2.CityName, 1), 1
                                           )                                                      as CompanyCodeAddress,
      I_Address_2.StreetName,
      I_Address_2.HouseNumber,
      I_Address_2.CityName,

      I_Address_2._CurrentDfltLandlinePhoneNmbr.PhoneAreaCodeSubscriberNumber                     as PhoneNumber
}