I_SAFTGeneralLedgerOneTimeBP

@AbapCatalog.sqlViewName: 'ISAFTGENLEDOTBP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@EndUserText.label: 'SAFT GLedger One Time Business Partner'
define view I_SAFTGeneralLedgerOneTimeBP
  as select from           P_SAFT_GeneralLedgerOneTimeBP as GenLedBP
    inner join             I_JournalEntryItem            as Journal   on  GenLedBP.CompanyCode        = Journal.CompanyCode
                                                                      and GenLedBP.FiscalYear         = Journal.FiscalYear
                                                                      and GenLedBP.AccountingDocument = Journal.AccountingDocument
                                                                      and GenLedBP.LedgerGLLineItem   = Journal.LedgerGLLineItem
                                                                      and GenLedBP.Ledger             = Journal.Ledger

    inner join             I_JournalEntryItemOneTimeData as OneTimeBP on  Journal.CompanyCode            = OneTimeBP.CompanyCode
                                                                      and Journal.FiscalYear             = OneTimeBP.FiscalYear
                                                                      and Journal.AccountingDocument     = OneTimeBP.AccountingDocument
                                                                      and Journal.AccountingDocumentItem = OneTimeBP.AccountingDocumentItem

  association [0..1] to I_OperationalAcctgDocItem as _AccdocItem on  Journal.CompanyCode            =  _AccdocItem.CompanyCode
                                                                 and Journal.AccountingDocument     =  _AccdocItem.AccountingDocument
                                                                 and Journal.FiscalYear             =  _AccdocItem.FiscalYear
                                                                 and Journal.AccountingDocumentItem =  _AccdocItem.AccountingDocumentItem
                                                                 and _AccdocItem.VATRegistration    <> ''
{
  key GenLedBP.CompanyCode                                                                   as CompanyCode,
  key GenLedBP.FiscalYear                                                                    as FiscalYear,
  key GenLedBP.AccountingDocument                                                            as AccountingDocument,
  key GenLedBP.LedgerGLLineItem                                                              as LedgerGLLineItem,
  key GenLedBP.Ledger                                                                        as Ledger,

      @ObjectModel.foreignKey.association: '_ChartOfAccounts'
      Journal.ChartOfAccounts,
      @ObjectModel.foreignKey.association: '_GLAccountInChartOfAccounts'
      Journal.GLAccount,

      @ObjectModel.foreignKey.association: '_AccountingDocumentCategory'
      Journal.AccountingDocumentCategory,

      Journal.PostingDate,

      BusinessPartnerType,

      GenLedBP.BusinessPartnerNumber,
      case when Journal.Customer is not initial then
      Journal.Customer
      else case when Journal.Supplier is not initial then
      Journal.Supplier end end as BusinessPartner,
      Journal.Customer,
      Journal.Supplier,

      Journal.AccountingDocumentItem,

      OneTimeBP.AddressID                                                                    as AddressID,

      case when OneTimeBP.TaxID1 is not initial
            then OneTimeBP.TaxID1
           else _AccdocItem.VATRegistration
      end                                                                                    as VATRegistration,

      concat( concat( OneTimeBP.BusinessPartnerName1, ' '), OneTimeBP.BusinessPartnerName2 ) as CompanyName,
      OneTimeBP._Address.StreetName                                                          as StreetAddressName,
      OneTimeBP._Address.StreetPrefixName                                                    as StreetPrefixName,
      OneTimeBP._Address.AdditionalStreetPrefixName                                          as AdditionalStreetPrefixName,
      OneTimeBP._Address.StreetSuffixName                                                    as StreetSuffixName,
      OneTimeBP._Address.AdditionalStreetSuffixName                                          as AdditionalStreetSuffixName,
      OneTimeBP._Address.HouseNumber                                                         as HouseNumber,

      OneTimeBP.CityName                                                                     as CityName,
      OneTimeBP.PostalCode                                                                   as PostalCode,
      OneTimeBP.Country                                                                      as Country,
      OneTimeBP.POBox,
      OneTimeBP.POBoxPostalCode,
      OneTimeBP.Region,
      coalesce(OneTimeBP._Address._Region._RegionText[1: Language = 'F'].RegionName,
      coalesce(OneTimeBP._Address._Region._RegionText[1: Language = 'E'].RegionName,
      coalesce(OneTimeBP._Address._Region._RegionText[1: Language = $session.system_language].RegionName, ''))) as RegionName,      
      
      //Associations

      _Address,
      _ChartOfAccounts,
      _GLAccountInChartOfAccounts,
      _AccountingDocumentCategory
}
where
  (
       Journal.FinancialAccountType       = 'K'
    or Journal.FinancialAccountType       = 'D'
  )
  and(
       Journal._Customer.IsOneTimeAccount = 'X'
    or Journal._Supplier.IsOneTimeAccount = 'X'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_CUSTOMER",
"I_JOURNALENTRYITEM",
"I_JOURNALENTRYITEMONETIMEDATA",
"I_OPERATIONALACCTGDOCITEM",
"I_REGION",
"I_REGIONTEXT",
"I_SUPPLIER",
"P_SAFT_GENERALLEDGERONETIMEBP"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTCATEGORY",
"I_ADDRESS",
"I_CHARTOFACCOUNTS",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_OPERATIONALACCTGDOCITEM"
],
"BASE":
[
"I_JOURNALENTRYITEM",
"I_JOURNALENTRYITEMONETIMEDATA"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/