P_RO_SAFTSupplier

@AbapCatalog.sqlViewName: 'PROSAFTSUPPLIER'
@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: 'Supplier Data for RO SAFT'
define view P_RO_SAFTSupplier
  with parameters
    P_FromPostingDate            : dats,
    P_ToPostingDate              : dats,
    P_FiscalYear                 : gjahr,
    P_AlternativeGLAccountIsUsed : saft_ro_alt_gl_account_flag
  as select from           P_RO_SAFTSupplierVATNumber as SupplierTaxType

    inner join             P_RO_SAFTSuplrAcctBal(
                                         P_FromPostingDate: $parameters.P_FromPostingDate,
                                         P_ToPostingDate: $parameters.P_ToPostingDate,
                                         P_FiscalYear: $parameters.P_FiscalYear,
                                         P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed
                                       )              as Balance  on  Balance.Supplier   = SupplierTaxType.Supplier
                                                                  and Balance.FiscalYear = $parameters.P_FiscalYear

    inner join             I_Supplier                 as Supplier on Supplier.Supplier = SupplierTaxType.Supplier

    inner join             I_RO_SAFTAccountMapping    as Account  on  Account.ChartOfAccounts = Balance.ActiveChartOfAccounts
                                                                  and Account.GLAccount       = Balance.ActiveGLAccount

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


{
  key Balance.SourceLedger,
  key Balance.Ledger,
  key Balance.CompanyCode,
  key max(Balance.Supplier)                                                     as Supplier,
  key SupplierTaxType.BPTaxLongNumber                                           as TaxID5,
  key Account.RO_SAFTStandardAccount,
      Balance.GLAccount,
      max(Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner) as BusinessPartner,
      cast(Supplier.BusinessPartnerName1 as abap.char(81))                      as SupplierName,
      cast( Address._Country.CountryISOCode as land1 )                          as CountryISOCode,
      Address.Region,
      Address.PostalCode,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.Building,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      sum(Balance.StartingBalanceAmtInCoCodeCrcy)                               as StartingBalanceAmtInCoCodeCrcy,

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

union all select from P_RO_SAFTSupplierVATNumber as SupplierTaxType

  inner join          P_RO_SAFTOneTimeSupplier   as ots     on ots.Supplier = SupplierTaxType.Supplier
  inner join          P_RO_SAFTSuplrAcctBal(
                                    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     = ots.CompanyCode
                                                            and Balance.Supplier        = ots.Supplier
                                                            and Balance.VATRegistration = ots.VATRegistration
                                                            and Balance.FiscalYear      = $parameters.P_FiscalYear
  inner 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.Supplier)                       as Supplier,
  key ots.VATRegistration                         as TaxID5,
  key Account.RO_SAFTStandardAccount,

      Balance.GLAccount,
      ''                                          as BusinessPartner,
      ots.SupplierName,
      ots.CountryISOCode,
      ots.Region,
      ots.PostalCode,
      ots.CityName,
      ots.StreetName,
      ''                                          as HouseNumber,
      ''                                          as Building,

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