P_BG_SAFTCustomer

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@VDM.private:true
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity P_BG_SAFTCustomer
  with parameters
    P_FromPostingDate            : dats,
    P_ToPostingDate              : dats,
    P_FiscalYear                 : gjahr,
    P_AlternativeGLAccountIsUsed : saft_bg_alt_gl_account_flag
  as select from           P_BG_SAFTCUSTOMERID     as CustomerTaxType

    inner join             P_BG_SAFTCustAcctBal(
                                         P_FromPostingDate: $parameters.P_FromPostingDate,
                                         P_ToPostingDate: $parameters.P_ToPostingDate,
                                         P_FiscalYear: $parameters.P_FiscalYear,
                                         P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed
                                       )           as Balance  on  Balance.Customer   = CustomerTaxType.Customer
                                                               and Balance.FiscalYear = $parameters.P_FiscalYear

    inner join             I_Customer              as Customer on Customer.Customer = CustomerTaxType.Customer

    left outer to one join I_Address_2             as Address  on  Customer.AddressID                = Address.AddressID
                                                               and Address.AddressRepresentationCode is initial
                                                               and Address.AddressPersonID           is initial

    left outer to one join I_BG_SAFTAccountMapping as Account  on  Account.ChartOfAccounts = Balance.ActiveChartOfAccounts
                                                               and Account.GLAccount       = Balance.ActiveGLAccount

{
  key Balance.SourceLedger,
  key Balance.Ledger,
  key Balance.CompanyCode,
  key max(Balance.Customer)                                                     as Customer,
  key CustomerTaxType.BPTaxLongNumber                                           as TaxID5,
  key coalesce(Account.BG_SAFTStandardAccount, Balance.ActiveGLAccount)         as BG_SAFTStandardAccount,
      Balance.GLAccount,
      max(Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner) as BusinessPartner,
      Customer.BusinessPartnerName1                                             as CustomerName,
      cast( Address._Country.CountryISOCode as land1 )                          as CountryISOCode,
      Address.Region,
      Address.PostalCode,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.Building,
      @Semantics.amount.currencyCode: 'ReportingCurrency'
      sum(Balance.StartingBalanceAmtInRptgCrcy)                                 as StartingBalanceAmtInRptgCrcy,

      @Semantics.amount.currencyCode: 'ReportingCurrency'
      sum(Balance.EndingBalanceAmtInRptgCrcy)                                   as EndingBalanceAmtInRptgCrcy,
      Balance.ReportingCurrency
}
where
       CustomerTaxType.BPTaxLongNumber      <> 'EXCLUDE'
  and  CustomerTaxType.BPTaxLongNumber      <> '0'
  and(
       Balance.StartingBalanceAmtInRptgCrcy <> 0
    or Balance.NumberOfItems                >  0
  )
group by
  Balance.SourceLedger,
  Balance.Ledger,
  Balance.CompanyCode,
  CustomerTaxType.BPTaxLongNumber,
  Balance.ActiveGLAccount,
  Account.BG_SAFTStandardAccount,
  Balance.GLAccount,
  Customer.BusinessPartnerName1,
  Address._Country.CountryISOCode,
  Address.Region,
  Address.PostalCode,
  Address.CityName,
  Address.StreetName,
  Address.HouseNumber,
  Address.Building,
  Balance.ReportingCurrency

union all select from    P_BG_SAFTCUSTOMERID      as CustomerTaxType

  inner join             P_BG_SAFTOneTimeCustomer as otc     on otc.Customer = CustomerTaxType.Customer

  inner join             P_BG_SAFTCustAcctBal(
                                       P_FromPostingDate: $parameters.P_FromPostingDate,
                                       P_ToPostingDate: $parameters.P_ToPostingDate,
                                       P_FiscalYear: $parameters.P_FiscalYear,
                                       P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed
                                     )            as Balance on  Balance.CompanyCode     = otc.CompanyCode
                                                             and Balance.Customer        = otc.Customer
                                                             and Balance.VATRegistration = otc.VATRegistration
                                                             and Balance.FiscalYear      = $parameters.P_FiscalYear


  left outer to one join I_BG_SAFTAccountMapping  as Account on  Account.ChartOfAccounts = Balance.ActiveChartOfAccounts
                                                             and Account.GLAccount       = Balance.ActiveGLAccount

{
  key Balance.SourceLedger,
  key Balance.Ledger,
  key Balance.CompanyCode,
  key max(Balance.Customer)                                             as Customer,
  key otc.VATRegistration                                               as TaxID5,
  key coalesce(Account.BG_SAFTStandardAccount, Balance.ActiveGLAccount) as BG_SAFTStandardAccount,
      Balance.GLAccount,
      ''                                                                as BusinessPartner,
      otc.CustomerName,
      otc.CountryISOCode,
      otc.Region,
      otc.PostalCode,
      otc.CityName,
      otc.StreetName,
      ''                                                                as HouseNumber,
      ''                                                                as Building,

      sum(Balance.StartingBalanceAmtInRptgCrcy)                         as StartingBalanceAmtInRptgCrcy,
      sum(Balance.EndingBalanceAmtInRptgCrcy)                           as EndingBalanceAmtInRptgCrcy,
      Balance.ReportingCurrency
}
where
       CustomerTaxType.BPTaxLongNumber      =  '0'
  and(
       Balance.StartingBalanceAmtInRptgCrcy <> 0
    or Balance.NumberOfItems                >  0
  )
group by
  Balance.SourceLedger,
  Balance.Ledger,
  Balance.CompanyCode,
  otc.VATRegistration,
  Balance.ActiveGLAccount,
  Account.BG_SAFTStandardAccount,
  Balance.GLAccount,
  otc.CustomerName,
  otc.CountryISOCode,
  otc.Region,
  otc.PostalCode,
  otc.CityName,
  otc.StreetName,
  Balance.ReportingCurrency