P_RO_SAFTCustomer

@AbapCatalog.sqlViewName: 'PROSAFTCUSTOMER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.private:true
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MASTER
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Customer Data for RO SAFT'
define view P_RO_SAFTCustomer
  with parameters
    P_FromPostingDate            : dats,
    P_ToPostingDate              : dats,
    P_FiscalYear                 : gjahr,
    P_AlternativeGLAccountIsUsed : saft_ro_alt_gl_account_flag
  as select from           P_RO_SAFTCustomerVATNumber as CustomerTaxType

    inner join             P_RO_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_RO_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.RO_SAFTStandardAccount, Balance.ActiveGLAccount)                as RO_SAFTStandardAccount,
      Balance.GLAccount,
      max(Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner)        as BusinessPartner,
      concat_with_space(Address.OrganizationName1, concat_with_space(Address.OrganizationName2, concat_with_space(Address.OrganizationName3, Address.OrganizationName4, 1), 1), 1) as CustomerName,
      cast( Address._Country.CountryISOCode as land1 )                                 as CountryISOCode,
      Address.Region,
      Address.PostalCode,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.Building,

      sum(Balance.StartingBalanceAmtInCoCodeCrcy)                                      as StartingBalanceAmtInCoCodeCrcy,
      sum(Balance.EndingBalanceAmtInCoCodeCrcy)                                        as EndingBalanceAmtInCoCodeCrcy,
      @Semantics.currencyCode:true
      Balance.CompanyCodeCurrency
}
where
       CustomerTaxType.BPTaxLongNumber        <> 'EXCLUDE'
  and  CustomerTaxType.BPTaxLongNumber        <> '0'
  and(
       Balance.StartingBalanceAmtInCoCodeCrcy        <> 0
    or Balance.NumberOfItems                         >  0
  )
group by
  Balance.SourceLedger,
  Balance.Ledger,
  Balance.CompanyCode,
  CustomerTaxType.BPTaxLongNumber,
  Balance.ActiveGLAccount,
  Account.RO_SAFTStandardAccount,
  Balance.GLAccount,
  Address.OrganizationName1,
  Address.OrganizationName2,
  Address.OrganizationName3,
  Address.OrganizationName4,
  Address._Country.CountryISOCode,
  Address.Region,
  Address.PostalCode,
  Address.CityName,
  Address.StreetName,
  Address.HouseNumber,
  Address.Building,
  Balance.CompanyCodeCurrency

union all select from    P_RO_SAFTCustomerVATNumber as CustomerTaxType

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

  inner join             P_RO_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_RO_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.RO_SAFTStandardAccount, Balance.ActiveGLAccount) as RO_SAFTStandardAccount,
      Balance.GLAccount,
      ''                                                                as BusinessPartner,
      otc.CustomerName,
      otc.CountryISOCode,
      otc.Region,
      otc.PostalCode,
      otc.CityName,
      otc.StreetName,
      ''                                                                as HouseNumber,
      ''                                                                as Building,

      sum(Balance.StartingBalanceAmtInCoCodeCrcy)                       as StartingBalanceAmtInCoCodeCrcy,
      sum(Balance.EndingBalanceAmtInCoCodeCrcy)                         as EndingBalanceAmtInCoCodeCrcy,
      @Semantics.currencyCode:true
      Balance.CompanyCodeCurrency
}
where
       CustomerTaxType.BPTaxLongNumber        =  '0'
  and(
       Balance.StartingBalanceAmtInCoCodeCrcy        <> 0
    or Balance.NumberOfItems                         >  0
  )
group by
  Balance.SourceLedger,
  Balance.Ledger,
  Balance.CompanyCode,
  otc.VATRegistration,
  Balance.ActiveGLAccount,
  Account.RO_SAFTStandardAccount,
  Balance.GLAccount,
  otc.CustomerName,
  otc.CountryISOCode,
  otc.Region,
  otc.PostalCode,
  otc.CityName,
  otc.StreetName,
  Balance.CompanyCodeCurrency