ICM_PARTNER_CONTACT

@AbapCatalog.sqlViewName: 'ICM_PARTNER_CONT'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'BusinessPartner_contact'
@ObjectModel.semanticKey: ['addrnumber','persnumber']

define view ICM_PARTNER_CONTACT
  as select from  but052 as b52
    inner join adrc as ac  on(
       b52.addrnumber = ac.addrnumber
    )

    inner join but000  as b00  on(
      b52.partner2 = b00.partner

    )
    inner join adcp  as ad  on(
      b00.persnumber = ad.persnumber and
      ac.addrnumber  = ad.addrnumber

    )

    left outer join adr2  as a2  on(
      ad.addrnumber = a2.addrnumber and
      ad.persnumber = a2.persnumber

    )
    left outer join adr6  as a6  on(
      ad.addrnumber = a6.addrnumber and
      ad.persnumber = a6.persnumber

    )

{

      key b52.relnr,
      key b52.partner1,
      key b52.partner2,
      key b52.date_to,
      key b52.addrnumber,

      key ac.date_from   as date_from_adrc,
      key ac.nation      as nation,

      key ad.addrnumber as addrnumber_adcp,
      key ad.date_from as date_from_adcp,
      key ad.nation    as nation_adcp,

      key a2.date_from   as date_from_adr2_adr,
      key a2.consnumber,

      key a6.date_from   as date_from_adr6_adr,
      key a6.consnumber  as consnumber_1,


      ac.name1,
      ac.name2,
      ac.name3,
      ac.name4,
      ac.city1,
      ac.post_code1,
      ac.post_code2,
      ac.po_box,
      ac.street,
      ac.house_num1,
      ac.country,
      ac.region,
      b00.name_last,
      b00.name_first,
      b00.augrp,
      b00.persnumber,
      a2.telnr_long as telnr_long,
      a6.smtp_addr as smtp_addr,
      ad.comp_pers

}

// contacts

where ad.comp_pers = 'C'