Fclm_Bm_Address

@AbapCatalog.sqlViewName: 'VFCLM_BM_ADDRESS'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #CHECK //#NOT_REQUIRED

@AccessControl.personalData.blocking: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #ORGANIZATIONAL
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Address data for bank master and business partner'
define view Fclm_Bm_Address
  with parameters
    p_spras : sylangu
  as select from    adrc
    left outer join bnka                    on adrc.addrnumber = bnka.adrnr

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

    left outer join adrct                   on  adrc.addrnumber = adrct.addrnumber
                                            and adrc.nation     = adrct.nation
                                            and adrct.langu     = $parameters.p_spras

    left outer join Fclm_Bm_Adcp as _person on  adrc.addrnumber = _person.addrnumber
                                            and adrc.nation     = _person.nation

    left outer join adr2         as _Telephone      on  _Telephone.addrnumber = adrc.addrnumber
                                                    and _Telephone.r3_user    = '1'
                                                    and _Telephone.persnumber = _person.persnumber

    left outer join adr2         as _Mobile         on  _Mobile.addrnumber = adrc.addrnumber
                                                    and _Mobile.r3_user    = '3'
                                                    and _Mobile.persnumber = _person.persnumber

    left outer join adr3         as _Fax            on  _Fax.addrnumber = adrc.addrnumber
                                                    and _Fax.flgdefault = 'X'
                                                    and _Fax.persnumber = _person.persnumber

    left outer join adr6         as _Email          on  _Email.addrnumber = adrc.addrnumber
                                                    and _Email.flgdefault = 'X'
                                                    and _Email.persnumber = _person.persnumber
                                                   
    left outer join adrreggrpt   as _StructureGroupText on  _StructureGroupText.langu     = $parameters.p_spras  //CE2108  

                                                        and _StructureGroupText.regiogroup = adrc.regiogroup   //CE2108

  //   association [0..1] to I_Address as _BankAddress on _BankAddress.AddressID = $projection.AddressID

  //text

  association [1..1] to t005u  as _RegionText             on  _RegionText.land1 = $projection.AddressCountry
                                                          and _RegionText.bland = $projection.State
                                                          and _RegionText.spras = $parameters.p_spras

  association [1..1] to t005u  as _PoRegionText           on  _PoRegionText.land1 = $projection.PoBoxCountry
                                                          and _PoRegionText.bland = $projection.PoBoxRegion
                                                          and _PoRegionText.spras = $parameters.p_spras

  association [1..1] to t005t  as _CountryName            on  _CountryName.land1 = $projection.AddressCountry
                                                          and _CountryName.spras = $parameters.p_spras

  association [1..1] to t005t  as _PoCountryName          on  _PoCountryName.land1 = $projection.PoBoxCountry
                                                          and _PoCountryName.spras = $parameters.p_spras

  association [0..1] to I_CountryText as _BankCountry    on $projection.BankCountry = _BankCountry.Country
                                                         and _BankCountry.Language = $parameters.p_spras
  association [1..1] to ttzzt  as _TimeZoneText           on  _TimeZoneText.langu = $parameters.p_spras
                                                          and _TimeZoneText.tzone = $projection.AddressTimeZone
  association [1..1] to tzont  as _TransportationZoneText on  _TransportationZoneText.spras = $parameters.p_spras
                                                          and _TransportationZoneText.land1 = $projection.AddressCountry
                                                          and _TransportationZoneText.zone1 = $projection.TransportationZone //AddressTimeZone

  association [1..1] to tsadvt as _NationText             on  _NationText.langu  = $parameters.p_spras
                                                          and _NationText.nation = $projection.Nation

  association [1..1] to t005k  as _TelephoneDialingNumber on  _TelephoneDialingNumber.land1 = _Telephone.country
  association [1..1] to t005k  as _MobileDialingNumber    on  _MobileDialingNumber.land1 = _Mobile.country
  association [1..1] to t005k  as _FaxDialingNumber       on  _FaxDialingNumber.land1 = _Fax.country
  association [0..1] to I_BusinessPartner   as _BusinessPartner                on  $projection.BusinessPartnerNo = _BusinessPartner.BusinessPartner 
  //Add advance address fields  

  association [0..1] to I_BusPartDelivSrvcVH           as _BusPartDelivSrvcTxt on  $projection.DeliveryServiceType = _BusPartDelivSrvcTxt.DeliveryServiceTypeCode  
  association [0..1] to I_LanguageText                 as _LanguageText        on  $projection.CommunicationLanguage = _LanguageText.LanguageCode 
                                                                               and  _LanguageText.Language = $parameters.p_spras
  association [0..1] to I_BusinessPartnerFormOfAddrTxt as _BusPartFormAddrTxt  on  $projection.Title = _BusPartFormAddrTxt.FormOfAddress 
                                                                               and _BusPartFormAddrTxt.Language = $parameters.p_spras 
  association [0..1] to I_AddressNonDeliverableReasonT as _AddrNonDeliReasonStreetTxt  on  $projection.StreetAddressUndeliverableFlag = _AddrNonDeliReasonStreetTxt.AddressNonDeliverableReason 
                                                                                       and _AddrNonDeliReasonStreetTxt.Language = $parameters.p_spras  
  association [0..1] to I_AddressNonDeliverableReasonT as _AddrNonDeliReasonPOTxt      on  $projection.POBoxAddressUndeliverableFlag = _AddrNonDeliReasonPOTxt.AddressNonDeliverableReason 
                                                                                       and _AddrNonDeliReasonPOTxt.Language = $parameters.p_spras                                                                        

{
  key adrc.addrnumber                                  as  AddressID,
  key adrc.nation                                      as  Nation,
      _NationText.nation_tex                           as  NationText,
      bnka.banks                                       as  BankCountry,
      _BankCountry.CountryName                         as  BankCountryName,
      bnka.bankl                                       as  BankInternalId,
      bnka.banka                                       as  BankName,
      partner                                          as  BusinessPartnerNo,

      adrc.name_co                                     as  CareOfName, //  c/o name

      adrc.str_suppl1                                  as  AddressLine1, //  Street 2

      adrc.str_suppl2                                  as  AddressLine2, //  Street 3

      adrc.house_num1                                  as  HouseNumber, //  House Number

      adrc.street                                      as  Street, //  Street

      adrc.house_num2                                  as  HouseNumberSupplement, //  House number supplement

      adrc.str_suppl3                                  as  AddressLine3, //  Street 4

      adrc.location                                    as  AddressLine4, //  Street 5

      adrc.home_city                                   as  Village, //  City (different from postal city)

      adrc.city2                                       as  District, //  District

      adrc.post_code1                                  as  PostalCode, //  City postal code

      adrc.city1                                       as  AddressCity, //  City

      adrc.country                                     as  AddressCountry, //  Country Key

      _CountryName.landx                               as  AddressCountryText, //Country name

      adrc.region                                      as  State, //  Region (State, Province, County)

      _RegionText.bezei                                as  StateText,
      adrc.time_zone                                   as  AddressTimeZone, //  Address time zone

      _TimeZoneText.descript                           as  TimeZoneText,
      adrc.transpzone                                  as  TransportationZone, //  Transportation zone to or from which the goods are delivered

      _TransportationZoneText.vtext                    as  TransportationZoneText,
      adrc.taxjurcode                                  as  TaxJurisdiction, //  Tax Jurisdiction

      adrc.post_code3                                  as  CompanyPostalCode, //  Company Postal Code (for Large Customers)

      adrc.deli_serv_type                              as  DeliveryServiceType,
      adrc.deli_serv_number                            as  DeliveryServiceNumber, //  Number of Delivery Service

      adrc.po_box                                      as  PoBox, //  PO Box

      adrc.po_box_num                                  as  PoBoxWithoutNumber, //  Flag: PO Box Without Number

      adrc.post_code2                                  as  PoBoxPostalCode, //  PO Box Postal Code

      adrc.po_box_lobby                                as  PoBoxLobby, //  PO Box Lobby

      adrc.po_box_loc                                  as  PoBoxCity, //  PO Box city

      adrc.po_box_reg                                  as  PoBoxRegion, //  Region for PO Box (Country, State, Province, ...)

      _PoRegionText.bezei                              as  PoBoxRegionText,
      adrc.po_box_cty                                  as  PoBoxCountry, //  PO box country

      _PoCountryName.landx                             as  PoBoxCountryText,
      adrc.langu                                       as  CommunicationLanguage,
      adrct.remark                                     as  Comments, 

      adrc.title                                       as  Title,
      adrc.name1                                       as  Name,
      adrc.name2                                       as  Name2,                          //Add advance address field:Name2

      adrc.name3                                       as  Name3,                          //Add advance address field:Name3

      adrc.name4                                       as  Name4,                          //Add advance address field:Name4             

      adrc.extension1                                  as  DataLine,
      adrc.extension2                                  as  TeleBox,
      adrc.sort1                                       as  SearchTeam1,
      adrc.sort2                                       as  SearchTeam2,
      adrc.township                                    as  TownShip,                       //Add advance address field:TownShip

      adrc.regiogroup                                  as  StructureGroup,                 //Add advance address field:Structure Group

      adrc.dont_use_s                                  as  StreetAddressUndeliverableFlag, //Add advance address field:Street Undeliverable

      adrc.dont_use_p                                  as  POBoxAddressUndeliverableFlag,  //Add advance address field:POBox Undeliverable

      adrc.county                                      as  County,                         //Add advance address field:County

      _BusPartDelivSrvcTxt.DeliveryServiceText         as  DeliveryServiceText,            //Add advance address field: Delivery Service Type Text

      _LanguageText.LanguageName                       as  CommunicationLanguageText,      //Add advance address field:Communication Language Text

      _BusPartFormAddrTxt.FormOfAddressName            as  TitleText,                      //Add advance address field:Title Text

      _StructureGroupText.descript                     as  StructureGroupText,             //Add advance address field:Structure Group

      _AddrNonDeliReasonStreetTxt.AddressNonDeliverableReasonTxt as  StreetAddrUndeliverableFlagTxt, //Add advance address field: Undeliverable Text

      _AddrNonDeliReasonPOTxt.AddressNonDeliverableReasonTxt     as  POBoxAddrUndeliverableFlagTxt, //Add advance address field: POBox Undeliverable Text

      //COMMUNICATION ITEM

      // case when _BankAddress._DefaultMobilePhoneNumber.IsDefaultPhoneNumber = 'X' then 'M'

      //        else ''

      //   end as  PreferredCommunication,


      //   concat('+',_CountryCode.telefto ) as CountryPhoneCode,+

      _Telephone.country                               as  TelephoneNumberCountry,
      concat('+' , _TelephoneDialingNumber.telefto)    as  TelephoneDialingNumber,
      _Telephone.tel_number                            as  TelephoneNumber,
      _Telephone.tel_extens                            as  PhoneNumberExtension,

      _Mobile.country                                  as  MobileNumberCountry,
      concat('+' , _MobileDialingNumber.telefto)       as  MobileDialingNumber,
      _Mobile.tel_number                               as  MobileNumber,


      _Fax.country                                     as  FaxCountry,
      concat('+' , _FaxDialingNumber.telefto)          as  FaxDialingNumber,
      _Fax.fax_number                                  as  FaxNumber,
      _Fax.fax_extens                                  as  FaxNumberExtension,
      _Email.smtp_addr                                 as  EmailAddress,
      adrc.building                                    as  Building,
      adrc.floor                                       as  Floor,
      adrc.roomnumber                                  as  RoomNumber,
      adrc.addr_group                                  as  AddressGroup,
      //RATING

      0                                                as  RatingMethod,
      0                                                as  RatingLevel,
      @EndUserText.label: 'Business Partner Name'      
      _BusinessPartner.BusinessPartnerFullName         as BusinessPartnerFullName    


}
where
  adrc.date_from = '00010101'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FCLM_BM_ADCP",
"I_ADDRESSNONDELIVERABLEREASONT",
"I_BUSINESSPARTNER",
"I_BUSINESSPARTNERFORMOFADDRTXT",
"I_BUSPARTDELIVSRVCVH",
"I_COUNTRYTEXT",
"I_LANGUAGETEXT",
"ADR2",
"ADR3",
"ADR6",
"ADRC",
"ADRCT",
"ADRREGGRPT",
"BNKA",
"BUT020",
"T005K",
"T005T",
"T005U",
"TSADVT",
"TTZZT",
"TZONT"
],
"ASSOCIATED":
[
"I_ADDRESSNONDELIVERABLEREASONT",
"I_BUSINESSPARTNER",
"I_BUSINESSPARTNERFORMOFADDRTXT",
"I_BUSPARTDELIVSRVCVH",
"I_COUNTRYTEXT",
"I_LANGUAGETEXT",
"T005K",
"T005T",
"T005U",
"TSADVT",
"TTZZT",
"TZONT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/