Fclm_Bm_Contacts

@AbapCatalog.sqlViewName: 'VFCLM_BM_CONTACT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK  //#NOT_REQUIRED

@AccessControl.personalData.blocking: #REQUIRED //#NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #ORGANIZATIONAL
@EndUserText.label: 'Contact person for bank'
define view Fclm_Bm_Contacts
  with parameters
    p_spras : sylangu
  as select from    I_RiskBusinessPartner as _RiskPartner

    inner join      but051 on _RiskPartner.BusinessPartnerNumber = but051.partner1

    inner join      but000 on but051.partner2 = but000.partner

    inner join      but100 on but051.partner2 = but100.partner

    left outer join but020 on but051.partner2 = but020.partner

    left outer join tsad3  on but000.title = tsad3.title

    left outer join adrc   on but020.addrnumber = adrc.addrnumber

    left outer join adcp   on  adrc.addrnumber = adcp.addrnumber
                           and adrc.nation     = adcp.nation
                           and adcp.date_from  = '00010101'
                           and adcp.comp_pers  = 'P'

  association [0..1] to I_Address_2             as _address           on  _address.AddressID = $projection.AddressId

  association [0..1] to I_AddressPhoneNumber_2  as _PhoneNumber       on  _PhoneNumber.AddressID       = $projection.AddressId
                                                                    and _PhoneNumber.AddressPersonID          = $projection.persnumber
                                                                    and _PhoneNumber.PhoneNumberType = '1'

  association [0..1] to I_AddressPhoneNumber_2  as _MobilePhoneNumber on  _MobilePhoneNumber.AddressID       = $projection.AddressId
                                                                    and _MobilePhoneNumber.AddressPersonID          = $projection.persnumber
                                                                    and _MobilePhoneNumber.PhoneNumberType = '3'

  association [0..1] to I_AddressFaxNumber_2    as _FaxNumber         on  _FaxNumber.AddressID          = $projection.AddressId
                                                                    and _FaxNumber.AddressPersonID             = $projection.persnumber
                                                                    and _FaxNumber.FaxNumberIsCurrentDefault = 'X'

  association [0..1] to I_AddressEmailAddress_2 as _EmailAddress      on  _EmailAddress.AddressID             = $projection.AddressId
                                                                    and _EmailAddress.AddressPersonID                = $projection.persnumber
                                                                    and _EmailAddress.EmailAddressIsCurrentDefault = 'X'

  association [0..1] to tb913                 as _Function //Function of partner TEXT

                                                                    on  _Function.pafkt = but051.pafkt
                                                                    and _Function.spras = $parameters.p_spras

  association [0..1] to tb911                 as _Department //Department TEXT

                                                                    on  _Department.abtnr = but051.abtnr
                                                                    and _Department.spras = $parameters.p_spras
  association [0..1] to I_BusinessPartner     as _BusinessPartner   on  $projection.BusinessPartnerNo = _BusinessPartner.BusinessPartner
  association [0..1] to I_BusinessPartner     as _ContactBusinessPartner   on $projection.ContactPersonNo = _ContactBusinessPartner.BusinessPartner
  association [0..1] to I_Bank as _Bank on $projection.BankCountry = _Bank.BankCountry
                                         and $projection.BankInternalId = _Bank.BankInternalID
  association [0..1] to I_CountryText as _Country on $projection.BankCountry = _Country.Country
                                                   and _Country.Language = $parameters.p_spras                                                                       
{

  key _RiskPartner.BusinessPartnerNumber as BusinessPartnerNo,
  key but000.partner                   as ContactPersonNo,
  key relnr                            as RELNR,
  key _RiskPartner.BankCountry         as BankCountry,
  key _RiskPartner.BankInternalID      as BankInternalId,
      _Bank.BankName                   as BankName,
      _Country.CountryName             as CountryName,
      but020.addrnumber                as AddressId,

      // Personal Data

      tsad3.title                      as Title,
      but000.name_first                as FirstName,
      but000.name_last                 as LastName,
      but000.birthdt                   as BIRTHDT,
      but051.pafkt                     as Function,
      but051.abtnr                     as Department,
      but100.rltyp                     as Role,

      // Person Number

      adcp.persnumber                  as persnumber,

      // Communication Dat

      _PhoneNumber.PhoneAreaCodeSubscriberNumber         as TelephoneNumber,
      _MobilePhoneNumber.PhoneAreaCodeSubscriberNumber   as MobileNumber,
      _FaxNumber.FaxAreaCodeSubscriberNumber             as FaxNumber,
      _EmailAddress.EmailAddress       as EmailAddress,

      //Address

      _address.Country                 as AddressCountry,
      _address.Region                  as State,
      _address.StreetName              as Street,
      _address.HouseNumber             as HouseNumber,
      _address.CityName                as AddressCity,
      _address.PostalCode              as PostalCode,
      _address.Building                as Building,
      _address.Floor                   as Floor,
      _address.RoomNumber              as RoomNumber,
      _Function.bez30                  as FunctionName,
      _Department.bez20                as DepartmentName,
      @EndUserText.label: 'Business Partner Name'      
      _BusinessPartner.BusinessPartnerFullName as BusinessPartnerFullName,
      _ContactBusinessPartner.BusinessPartnerFullName as ContactBusinessNoFullname,
      @Semantics.booleanIndicator: true
      _ContactBusinessPartner.IsBusinessPurposeCompleted as IsCntctPurposeCompleted,
      @Semantics.booleanIndicator: true
      _RiskPartner._BusinessPartner.IsBusinessPurposeCompleted as IsBusinessPurposeCompleted
}
where
  but100.rltyp = 'BUP001'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESSEMAILADDRESS_2",
"I_ADDRESSFAXNUMBER_2",
"I_ADDRESSPHONENUMBER_2",
"I_ADDRESS_2",
"I_BANK",
"I_BUSINESSPARTNER",
"I_COUNTRYTEXT",
"I_RISKBUSINESSPARTNER",
"ADCP",
"ADRC",
"BUT000",
"BUT020",
"BUT051",
"BUT100",
"TB911",
"TB913",
"TSAD3"
],
"ASSOCIATED":
[
"I_ADDRESSEMAILADDRESS_2",
"I_ADDRESSFAXNUMBER_2",
"I_ADDRESSPHONENUMBER_2",
"I_ADDRESS_2",
"I_BANK",
"I_BUSINESSPARTNER",
"I_COUNTRYTEXT",
"TB911",
"TB913"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/