P_CompanyCodeDetailsPA

@AbapCatalog.sqlViewName: 'PCOCODETAILSPA'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@AbapCatalog.preserveKey:true
@VDM.viewType: #COMPOSITE
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
// returns company code details from T001->Address

// if plants abroad is enabled additionaly returns data

// from T001N->Address per country

// T001N entry for country = CompanyCodeCountry is NOT returned

// might return nothing if plants abroad is not active in contrast to

// method CL_BRF_PROCEDURES=>GET_COMPANY_CODE_INFO

// The address is formatted differently but apart from that the same data should be returned

define view P_CompanyCodeDetailsPA
  as select from    t001n
  //            join t000f         on t000f.xwiaa = 'X'  not needed when RITA this is not set

    join            I_CompanyCode on  I_CompanyCode.CompanyCode =  t001n.bukrs
                                  and I_CompanyCode.Country     <> t001n.land1
    left outer join I_Address_2   on  I_Address_2.AddressID                 = t001n.adrnr
                                  and I_Address_2.AddressPersonID           is initial
                                  and I_Address_2.AddressRepresentationCode is initial
{

  key t001n.bukrs                                                             as CompanyCode,
  key t001n.land1                                                             as Country,
      t001n.stceg                                                             as VATRegistration,
      substring(t001n.stceg, 3, 18)                                           as VATRegistration_wo_prefix,
      I_Address_2.OrganizationName1                                           as CompanyCodeName,
      t001n.adrnr                                                             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._CurrentDfltLandlinePhoneNmbr.PhoneAreaCodeSubscriberNumber as PhoneNumber,
      I_Address_2.OrganizationName2,
      I_Address_2.StreetName,
      I_Address_2.HouseNumber,
      I_Address_2.HouseNumberSupplementText,
      I_Address_2.CityName
}

union select from I_CompanyCode

  left outer 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,
      substring(I_CompanyCode.VATRegistration, 3, 18)                         as VATRegistration_wo_prefix,
      I_Address_2.OrganizationName1                                           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._CurrentDfltLandlinePhoneNmbr.PhoneAreaCodeSubscriberNumber  as PhoneNumber,
     I_Address_2.OrganizationName2,
     I_Address_2.StreetName,
     I_Address_2.HouseNumber,
     I_Address_2.HouseNumberSupplementText,
     I_Address_2.CityName         
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRCURDFLTLANDLINEPHONENMBR",
"I_ADDRESS_2",
"I_COMPANYCODE",
"T001N"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/