I_SAFTGenLedgerOneTimeCustomer

@AbapCatalog.sqlViewName: 'ISAFTGENLEDOTC'
@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: 'SAF-T Gen Ledger One Time Customer'
define view I_SAFTGenLedgerOneTimeCustomer
as select from I_JournalEntryItem as a
  inner join I_JournalEntryItemOneTimeData     as b on  a.CompanyCode       = b.CompanyCode
                                              and a.FiscalYear              = b.FiscalYear
                                              and a.AccountingDocument      = b.AccountingDocument
                                              and a.AccountingDocumentItem  = b.AccountingDocumentItem 
                                     
  association [0..1] to I_OperationalAcctgDocItem as _AccdocItem on  a.CompanyCode            = _AccdocItem.CompanyCode
                                                                 and a.AccountingDocument     = _AccdocItem.AccountingDocument
                                                                 and a.FiscalYear             = _AccdocItem.FiscalYear
                                                                 and a.AccountingDocumentItem = _AccdocItem.AccountingDocumentItem
                                                                 and _AccdocItem.VATRegistration       <> ''
                                                                 
  association [0..1] to I_CustomerCompany as _CustCompany on  a.Customer    = _CustCompany.Customer
                                                          and a.CompanyCode = _CustCompany.CompanyCode

{
  @ObjectModel.foreignKey.association: '_SourceLedger'
  key a.SourceLedger                                                     as SourceLedger,
  @ObjectModel.foreignKey.association: '_CompanyCode'
  key a.CompanyCode                                                      as CompanyCode,
  @ObjectModel.foreignKey.association: '_FiscalYear'
  key a.FiscalYear                                                       as FiscalYear,
  @ObjectModel.foreignKey.association: '_JournalEntry'
  key a.AccountingDocument                                               as AccountingDocument,
  key a.LedgerGLLineItem                                                 as LedgerGLLineItem,
  @ObjectModel.foreignKey.association: '_Ledger'
  key a.Ledger                                                           as Ledger,
  
  @ObjectModel.foreignKey.association: '_ChartOfAccounts'
  a.ChartOfAccounts,
  @ObjectModel.foreignKey.association: '_GLAccountInChartOfAccounts'
  a.GLAccount,
  
  @ObjectModel.foreignKey.association: '_AccountingDocumentCategory'
  a.AccountingDocumentCategory,
  
  a.AccountingDocumentItem                                               as AccountingDocumentItem,
  a.PostingDate,
  a.Customer                                                             as Customer,
  
  a.CompanyCodeCurrency,
  
  case when b.TaxID1 is not initial 
        then b.TaxID1
       else substring(_AccdocItem.VATRegistration ,3,18)                                                                                          
  end                                                                    as CustomerVATRegistration,
  
  _CustCompany.ReconciliationAccount                                     as ReconciliationAccount,
  concat(b.BusinessPartnerName1, concat (' ', b.BusinessPartnerName2))   as CompanyName,
  
  //b.StreetAddressName                                                    as StreetName,

  b._Address.StreetName                                                  as StreetName,
  b._Address.StreetPrefixName                                            as StreetPrefixName,
  b._Address.AdditionalStreetPrefixName                                  as AdditionalStreetPrefixName,
  b._Address.StreetSuffixName                                            as StreetSuffixName,
  b._Address.AdditionalStreetSuffixName                                  as AdditionalStreetSuffixName,
  b._Address.HouseNumber                                                 as HouseNumber,
  b.CityName                                                             as CityName,
  b.PostalCode                                                           as PostalCode,
  b.Country                                                              as Country,
  b.Region                                                               as Region,
  b.POBox                                                                as POBox,
  b.POBoxPostalCode                                                      as POBoxPostalCode,
  
  b.AddressID,
  b._Address._DefaultPhoneNumber.PhoneNumber                             as PhoneNumber,
  b._Address._DefaultFaxNumber.FaxNumber                                 as FaxNumber,
  b._Address._DefaultEmailAddress.EmailAddress                           as EmailAddress,
  cast(lower(b._Address._DefaultURLAddress.SearchURLAddress) as ad_uri2) as WebsiteURL,
  
  //Associations

  a._Customer,
  a._ChartOfAccounts,
  a._GLAccountInChartOfAccounts,
  a._SourceLedger,
  a._CompanyCode,
  a._FiscalYear,
  a._JournalEntry,
  a._Ledger,
  a._AccountingDocumentCategory
}
where
  a.FinancialAccountType = 'D'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_ADDRESSEMAILADDRESS",
"I_ADDRESSFAXNUMBER",
"I_ADDRESSHOMEPAGEURL",
"I_ADDRESSPHONENUMBER",
"I_CUSTOMERCOMPANY",
"I_JOURNALENTRYITEM",
"I_JOURNALENTRYITEMONETIMEDATA",
"I_OPERATIONALACCTGDOCITEM"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTCATEGORY",
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_CUSTOMER",
"I_CUSTOMERCOMPANY",
"I_FISCALYEARFORCOMPANYCODE",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_JOURNALENTRY",
"I_LEDGER",
"I_OPERATIONALACCTGDOCITEM"
],
"BASE":
[
"I_JOURNALENTRYITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/