@AbapCatalog.sqlViewName: 'IBPCUSTADDRINFO'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'BP and Cust Addr Depdnt Info'
@ClientHandling.algorithm : #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MASTER
@VDM.viewType: #COMPOSITE
@Consumption.dbHints: [ 'USE_HEX_PLAN' ]
define view I_BPCustomerGeneralAddressInfo
as select from I_BusPartAddress as BusinessPartnerAddress
inner join I_BusinessPartnerCustomer as BusinessPartnerCustomer on BusinessPartnerAddress.BusinessPartner = BusinessPartnerCustomer.BusinessPartner
left outer to one join I_CustAddrDepdntInformation as CustAddrDepdntInformation on BusinessPartnerCustomer.Customer = CustAddrDepdntInformation.Customer
and BusinessPartnerAddress.AddressID = CustAddrDepdntInformation.AddressID
association [1..1] to I_BusinessPartner as _BusinessPartner on _BusinessPartner.BusinessPartner = $projection.BusinessPartner
association [1..1] to I_Customer as _Customer on $projection.Customer = _Customer.Customer
// association [0..*] to I_BPDataControllerUsage as _BPDataControllerUsage on $projection.BusinessPartner = _BPDataControllerUsage.BusinessPartner
{
key BusinessPartnerAddress.BusinessPartner as BusinessPartner,
key BusinessPartnerAddress.AddressID as AddressID,
BusinessPartnerCustomer.Customer as Customer,
// Logic to determine customer address number: If Address is marked as standard address at business partner level, then check if any addr dependent info is maintained in kna1_addr,
// If addr dependent data is maintained use 'kna1_addr' tables address number If no deviation data is maintained, fallback to 'kna1' tables addr number
// For a non standard BP address with no address dependent data , The address number will be fetched from 'KNA1' table
// case when BusinessPartnerAddress.AddressNumber = _BPCurrentDefaultAddress.AddressID then coalesce( CustAddrDepdntInformation.AddressID , BusinessPartnerCustomer.AddressID)
// else case
// when ( CustAddrDepdntInformation.AddressID is not initial ) then CustAddrDepdntInformation.AddressID
// else BusinessPartnerCustomer.AddressID end // For a non standard BP address with no address dependent data
//
// end as CustomerAddressID,
// Fields common to both KNA1_ADDR and KNA1 tables
// When an address number exists for customer address depdedent information, Fetch data from KNA1_ADDR table
// Else fetch data from KNA1 table
case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.ExpressTrainStationName
else BusinessPartnerCustomer.ExpressTrainStationName end as ExpressTrainStationName,
case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.TrainStationName
else BusinessPartnerCustomer.TrainStationName end as TrainStationName,
// case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.InternationalLocationNumber1
// else BusinessPartnerCustomer.InternationalLocationNumber1 end as InternationalLocationNumber1,
//
// case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.InternationalLocationNumber2
// else BusinessPartnerCustomer.InternationalLocationNumber2 end as InternationalLocationNumber2,
// case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.InternationalLocationNumber3
// else BusinessPartnerCustomer.InternationalLocationNumber3 end as InternationalLocationNumber3,
case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.CityCode
else BusinessPartnerCustomer.CityCode end as CityCode,
case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.County
else BusinessPartnerCustomer.County end as County,
case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.CustomerHasUnloadingPoint
else BusinessPartnerCustomer.CustomerHasUnloadingPoint end as CustomerHasUnloadingPoint,
// case when CustAddrDepdntInformation.AddressID is not initial then CustAddrDepdntInformation.CustomerWorkingTimeCalendar
// else BusinessPartnerCustomer.CustomerWorkingTimeCalendar end as CustomerWorkingTimeCalendar,
// KNA1 only fields
BusinessPartnerCustomer.CustomerAccountGroup,
BusinessPartnerCustomer.CustomerClassification,
BusinessPartnerCustomer.AuthorizationGroup,
BusinessPartnerCustomer.DeliveryIsBlocked,
BusinessPartnerCustomer.PostingIsBlocked,
BusinessPartnerCustomer.BillingIsBlockedForCustomer,
BusinessPartnerCustomer.OrderIsBlockedForCustomer,
BusinessPartnerCustomer.CustomerCorporateGroup,
BusinessPartnerCustomer.Supplier,
BusinessPartnerCustomer.IndustryCode1,
BusinessPartnerCustomer.IndustryCode2,
BusinessPartnerCustomer.IndustryCode3,
BusinessPartnerCustomer.AlternativePayerAccount,
BusinessPartnerCustomer.DataMediumExchangeIndicator,
BusinessPartnerCustomer.VATLiability,
BusinessPartnerCustomer.FiscalAddress,
@Semantics.booleanIndicator:true
BusinessPartnerCustomer.IsNaturalPerson,
BusinessPartnerCustomer.CityLocationCoordinatesCode,
BusinessPartnerCustomer.DeliveryDateTypeRule,
BusinessPartnerCustomer.Plant,
BusinessPartnerCustomer.DataExchangeInstructionKey,
BusinessPartnerCustomer.IsCompetitor,
BusinessPartnerCustomer.IsSalesPartner,
BusinessPartnerCustomer.IsDefaultSoldToParty,
BusinessPartnerCustomer.IsConsumer,
BusinessPartnerCustomer.FreeDefinedAttribute03,
BusinessPartnerCustomer.CustomerConditionGroup1,
BusinessPartnerCustomer.CustomerConditionGroup2,
BusinessPartnerCustomer.CustomerConditionGroup3,
BusinessPartnerCustomer.CustomerConditionGroup4,
BusinessPartnerCustomer.CustomerConditionGroup5,
_BusinessPartner,
_Customer
// non existent fields in BP cust to be added in I_BusinessPartnerCustomer
// BusinessPartnerCustomer.VATRegistration,
// BusinessPartnerCustomer.IsOneTimeAccount,
// BusinessPartnerCustomer.TaxJurisdiction,
// BusinessPartnerCustomer.Industry,
// BusinessPartnerCustomer.TaxNumberType,
// BusinessPartnerCustomer.TaxNumber1, // same as kna1.stcd1 ?
// BusinessPartnerCustomer.TaxNumber2, // same as kna1.stcd2 ?
// BusinessPartnerCustomer.TaxNumber3,
// BusinessPartnerCustomer.TaxNumber4,
// BusinessPartnerCustomer.TaxNumber5,
// BusinessPartnerCustomer.NielsenRegion,
// BusinessPartnerCustomer.IndustryCode4,
// BusinessPartnerCustomer.IndustryCode5,
// BusinessPartnerCustomer.Country,
// BusinessPartnerCustomer.OrganizationBPName1,
// BusinessPartnerCustomer.OrganizationBPName2,
// BusinessPartnerCustomer.CityName,
// BusinessPartnerCustomer.PostalCode,
// BusinessPartnerCustomer.StreetName,
// BusinessPartnerCustomer.SortField,
// BusinessPartnerCustomer.FaxNumber,
// BusinessPartnerCustomer.BR_SUFRAMACode,
// BusinessPartnerCustomer.Region,
// BusinessPartnerCustomer.TelephoneNumber1,
// BusinessPartnerCustomer.TelephoneNumber2,
// BusinessPartnerCustomer.IsBusinessPurposeCompleted,
// BusinessPartnerCustomer.ResponsibleType,
// BusinessPartnerCustomer.CustomerFullName,
// BusinessPartnerCustomer.CreatedByUser,
// BusinessPartnerCustomer.CreationDate,
// BusinessPartnerCustomer.NFPartnerIsNaturalPerson,
// BusinessPartnerCustomer.DeletionIndicator,
// BusinessPartnerCustomer.Language,
// BusinessPartnerCustomer.TradingPartner,
// BusinessPartnerCustomer.TaxInvoiceRepresentativeName,
// BusinessPartnerCustomer.BusinessType,
// BusinessPartnerCustomer.IndustryType,
// BusinessPartnerCustomer.TW_CollvBillingIsSupported,
// BusinessPartnerCustomer.AlternativePayeeIsAllowed,
}