P_ReportedFinancialData_RT_01

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PREPFINDRT_01'
@AbapCatalog.compiler.compareFilter: true

@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
//@AccessControl.authorizationCheck: #NOT_ALLOWED

@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view P_ReportedFinancialData_RT_01
  with parameters
    p_timestamp_from :timestamp,
    p_timestamp_to   :timestamp
  as select from           I_JournalEntryItem //acdoca


  //  Find all relevant dimen, vers, rldnr.

  //  For one line in acdocp this may result in several lines.


    left outer to one join I_CompanyCode             on I_JournalEntryItem.CompanyCode = I_CompanyCode.CompanyCode

    left outer to one join tfin010                   on  tfin010.rcomp = I_CompanyCode.Company
                                                     and tfin010.contp = '1'


    left outer join        tf165                     on  tfin010.dimen = tf165.dimen
                                                     and tfin010.bunit = tf165.bunit
                                                     and tf165.trans   = 'H'

    inner join             I_SAPClient as _SAPClient on _SAPClient.sapclient = $session.client

  //                                                                              begin of bbrCC1911

  // ledger dependent customizing for cons. units is optional,

  // cons. units are relevant for all cons. ledgers

    inner join             t881                      on  I_JournalEntryItem.Ledger = t881.orient_ledger
                                                     and t881.appl                 = 'EC'
                                                     and t881.subappl              = 'CS'
  //                                                                                end of bbrCC1911


    left outer to one join tf168                     on  tfin010.dimen = tf168.dimen
                                                     and t881.rldnr    = tf168.rldnr //bbrCC1911

                                                     and tfin010.bunit = tf168.bunit

    left outer join        tf200                     on  tf165.rvers    = tf200.inpvs
                                                     and tf200.planvers = ''

  //////    inner join      t881            on  tf168.rldnr               = t881.rldnr            //bbrCC1911

  //////                                    and I_JournalEntryItem.Ledger = t881.orient_ledger    //bbrCC1911


  // restrict to source ledger (extension ledger evaluation done in I_JournalEntryItem)

  //    inner join             finsc_ledger_rep on  t881.orient_ledger        = finsc_ledger_rep.rldnr

  //                                            and I_JournalEntryItem.Ledger = finsc_ledger_rep.rldnr_pers


  // Derive fiscal year and period from the posting date


  //                                                                              begin of bbrCC1911

  // fiscal year variant can be defined in the version

  // default is 'K4'

  //    inner join      finsc_fisc_date on  tf168.periv                    = finsc_fisc_date.fiscal_year_variant

  //                                    and I_JournalEntryItem.PostingDate = finsc_fisc_date.calendar_date

    left outer to one join I_FiscalCalendarDate      on(
           tf168.periv                                                                        =  I_FiscalCalendarDate.FiscalYearVariant
           and I_JournalEntryItem.PostingDate                                                 =  I_FiscalCalendarDate.CalendarDate
           and tf168.periv                                                                    <> ''
           and tf168.periv                                                                    is not null
         )
                                                     or(
                                                       tf200.periv                            =  I_FiscalCalendarDate.FiscalYearVariant
                                                       and I_JournalEntryItem.PostingDate     =  I_FiscalCalendarDate.CalendarDate
                                                       and(
                                                         tf168.periv                          =  ''
                                                         or tf168.periv                       is null
                                                       )
                                                       and tf200.periv                        <> ''
                                                     )
                                                     or(
                                                       I_FiscalCalendarDate.FiscalYearVariant =  'K4'
                                                       and I_JournalEntryItem.PostingDate     =  I_FiscalCalendarDate.CalendarDate
                                                       and(
                                                         tf168.periv                          =  ''
                                                         or tf168.periv                       is null
                                                       )
                                                       and tf200.periv                        =  ''
                                                     )
  //                                                                                end of bbrCC1911

{
  // GroupCurrency

  case
  // ledger currency is irrelevant

      when t881.curt2 = '30'
          then _SAPClient.GroupCurrency
  // ledger currency

      else t881.gcurr // use even if initial

  end                                                             as gcurr,

  I_JournalEntryItem.SourceLedger                                 as rldnr_fin,
  I_JournalEntryItem.LedgerGLLineItem                             as docln_fin,
  I_JournalEntryItem.CompanyCode                                  as robukrs,
  I_JournalEntryItem.PartnerCompany                               as rassc,
  I_JournalEntryItem.PostingDate                                  as budat,
  I_JournalEntryItem.BalanceTransactionCurrency                   as rtcur,
  I_JournalEntryItem.BaseUnit                                     as runit,
  I_JournalEntryItem.FiscalYear                                   as gjahr,
  I_JournalEntryItem.AccountingDocument                           as belnr,
  I_JournalEntryItem.LedgerGLLineItem                             as docln,
  I_JournalEntryItem.PartnerBusinessArea                          as sbusa,
  //  different fields can be taken over as subitem, depending

  //  on TF110-sitem_send

  I_JournalEntryItem.FinancialTransactionType                     as rmvct, // TF110-sitem_send = 01 Transaction Type

  I_JournalEntryItem.FunctionalArea                               as rfarea, // 02 Functional Area (FKBER)

  I_JournalEntryItem.BusinessArea                                 as rbusa, // 03 Business Area (GSBER)

  //             // 04 Customer Country (CNTRY) -> currently not supported

  I_JournalEntryItem.CostCenter                                   as rcntr, // 05 Cost Center (KOSTL)

  I_JournalEntryItem.GLAccount                                    as racct, // 06 G/L account (SAKNR)

  I_JournalEntryItem.ProfitCenter                                 as prctr, // 07 Profit Center

  I_JournalEntryItem.AccountingDocumentType                       as blart,
  I_JournalEntryItem.AmountInBalanceTransacCrcy                   as tsl,
  I_JournalEntryItem.AmountInCompanyCodeCurrency                  as hsl,
  I_JournalEntryItem.AmountInGlobalCurrency                       as ksl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency1                 as osl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency2                 as vsl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency3                 as bsl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency4                 as csl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency5                 as dsl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency6                 as esl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency7                 as fsl,
  I_JournalEntryItem.AmountInFreeDefinedCurrency8                 as gsl,
  I_JournalEntryItem.Quantity                                     as msl,

  //  I_JournalEntryItem.vorgn                        as activ, //not available in I_JournalEntryItem

  ''                                                              as activ,
  I_JournalEntryItem.ReferenceDocument                            as refdocnr,
  I_JournalEntryItem.FiscalYear                                   as refryear,
  //  I_JournalEntryItem.vorgn                        as refactiv,

  ''                                                              as refactiv,
  I_JournalEntryItem.AccountingDocCreatedByUser                   as usnam,
  I_JournalEntryItem.ReferenceDocumentType                        as awtyp,
  I_JournalEntryItem.ReferenceDocumentContext                     as aworg,
  I_JournalEntryItem.LogicalSystem                                as logsys,
  I_JournalEntryItem.CreationDateTime                             as timestamp,

  I_JournalEntryItem.Segment,

  tfin010.dimen                                                   as rdimen,
  tfin010.bunit                                                   as rbunit,

  tf200.rvers                                                     as rvers,
  tf200.impvs, //bbrCC1808


  I_CompanyCode.ChartOfAccounts                                   as ktopl,

  //  tf168.rldnr,                                                                    //bbrCC1911

  t881.rldnr, //bbrCC1911

  tf168.curr_group_send,

  //t881.gcurr,


  cast('000000000000000' as timestamp)                            as const_zero,

  I_FiscalCalendarDate.FiscalPeriod                               as poper,
  I_FiscalCalendarDate.FiscalYear                                 as ryear,
  cast(I_FiscalCalendarDate.FiscalYearPeriod as fins_fyearperiod) as fiscal_year_period //bbrCC1808

}
where
  (
       I_JournalEntryItem.AccountingDocumentCategory =  ''  //normal documents

    or I_JournalEntryItem.AccountingDocumentCategory =  'U' //documents without lines in BSEG

    or I_JournalEntryItem.AccountingDocumentCategory =  'L' //postings not in leading ledger, relevant in case an orientation ledger is used

    or I_JournalEntryItem.AccountingDocumentCategory =  'P' //predicted documents, posted in a separate extension ledger

    or I_JournalEntryItem.AccountingDocumentCategory =  'C' //balance Carryforward and Balance Adjustments from Migration

  )

  //  Balance carry forward is done differently in consolidation (e.g. shifted fiscal year)

  //  and must not be taken over from FI

  and  I_JournalEntryItem.FiscalPeriod               <> '000'

  // Year End Closing postings are not relevant for consolidation

  and  I_JournalEntryItem.ReferenceDocumentType      <> 'GLYEC'

//  and  I_JournalEntryItem.CreationDateTime           between :p_timestamp_from and :p_timestamp_to  //bbrCC2002