P_NO_SAFTJournalCustSup

@AbapCatalog.sqlViewName: 'PNOSAFTJCS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #P
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #REQUIRED
define view P_NO_SAFTJournalCustSup

  as select from I_NO_SAFTJournalItem as JournalLineItem
    inner join   I_Customer           as Customer on Customer.Customer = JournalLineItem.Customer
{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,
      JournalLineItem.VATRegistration,
      ''                                 as IsOneTimeAccount,
      JournalLineItem.GLAccount,
      Customer.TaxNumber1,
      Customer.TaxNumber2,
      
      //Associations

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._JournalEntry,
      JournalLineItem._Ledger      
}
where
      Customer.IsOneTimeAccount            = ' '
  and JournalLineItem.FinancialAccountType = 'D'


union select from I_NO_SAFTJournalItem as JournalLineItem
  inner join      I_Supplier           as Supplier on Supplier.Supplier = JournalLineItem.Supplier
{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,
      JournalLineItem.VATRegistration,
      ''                                 as IsOneTimeAccount,
      JournalLineItem.GLAccount,
      Supplier.TaxNumber1,
      Supplier.TaxNumber2,
      
      //Associations

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._JournalEntry,
      JournalLineItem._Ledger      
}
where
      Supplier.IsOneTimeAccount            = ' '
  and JournalLineItem.FinancialAccountType = 'K'


union select from I_NO_SAFTJournalItem as JournalLineItem
  inner join      I_Customer           as Customer on Customer.Customer = JournalLineItem.Customer
  inner join      I_JournalEntryItemOneTimeData   as OTC      on  JournalLineItem.CompanyCode            = OTC.CompanyCode
                                                   and JournalLineItem.FiscalYear             = OTC.FiscalYear
                                                   and JournalLineItem.AccountingDocument     = OTC.AccountingDocument
                                                   and JournalLineItem.AccountingDocumentItem = OTC.AccountingDocumentItem

{

      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,
      case
        when OTC.TaxID1 is not initial
            then OTC.TaxID1
        else substring(JournalLineItem.VATRegistration ,3,18)
      end                                as VATRegistration,
      'X'                                as IsOneTimeAccount,
      ''                                 as GLAccount,
      ''                                 as TaxNumber1,
      ''                                 as TaxNumber2,
      
      //Associations

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._JournalEntry,
      JournalLineItem._Ledger      
      
}
where
      Customer.IsOneTimeAccount            = 'X'
  and JournalLineItem.FinancialAccountType = 'D'


union select from I_NO_SAFTJournalItem as JournalLineItem
  inner join      I_Supplier           as Supplier on Supplier.Supplier = JournalLineItem.Supplier
  inner join      I_JournalEntryItemOneTimeData   as OTS      on  JournalLineItem.CompanyCode            = OTS.CompanyCode
                                                   and JournalLineItem.FiscalYear             = OTS.FiscalYear
                                                   and JournalLineItem.AccountingDocument     = OTS.AccountingDocument
                                                   and JournalLineItem.AccountingDocumentItem = OTS.AccountingDocumentItem

{

      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,
      case
         when OTS.TaxID1 is not initial
             then substring(OTS.TaxID1 ,3,18)
         when JournalLineItem.VATRegistration is not initial
             then substring(JournalLineItem.VATRegistration ,3,18)
       else ''
       end                               as VATRegistration,
      'X'                                as IsOneTimeAccount,
      ''                                 as GLAccount,
      ''                                 as TaxNumber1,
      ''                                 as TaxNumber2,
      
      //Associations

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._JournalEntry,
      JournalLineItem._Ledger      
}
where
      Supplier.IsOneTimeAccount            = 'X'
  and JournalLineItem.FinancialAccountType = 'K'