ICA_P_CONSJOURNALENTRIES_01

@AbapCatalog.sqlViewName: 'ICAPCJES01'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'ICA: Consolidation Journal Entry Line Items from ACDOCA'
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
define view ICA_P_CONSJOURNALENTRIES_01
  as select from           acdoca
  -- Join company code to company
    inner join             I_CompanyCode                 on rbukrs = I_CompanyCode.CompanyCode
  -- Join company to cons unit
    left outer join        fincs_bunit as fincs_bunit_2  on fincs_bunit_2.rcomp = I_CompanyCode.Company
                                                        and fincs_bunit_2.dimen = 'Y1'
  -- Cons Unit input versions
    left outer join        fincs_bunit_atvd              on  fincs_bunit_2.dimen    = fincs_bunit_atvd.dimen
                                                         and fincs_bunit_2.bunit    = fincs_bunit_atvd.bunit
                                                         and fincs_bunit_atvd.trans = 'H'
  -- Map input version to cons version
    left outer join        tf200                         on  fincs_bunit_atvd.cuavs = tf200.cuavs
                                                         and tf200.planvers         = ''

  -- In case extension ledger, union extenstion ledger with leading ledger
    inner join             finsc_ledger_rep              on  tf200.rldnr_source = finsc_ledger_rep.rldnr
                                                         and acdoca.rldnr       = finsc_ledger_rep.rldnr_pers

  -- Map calendar to fiscal
    inner join             finsc_fisc_date               on  tf200.periv = finsc_fisc_date.fiscal_year_variant
                                                         and budat       = finsc_fisc_date.calendar_date
  -- Map Trading Partner to Cons Partner unit
    left outer to one join fincs_bunit as fincs_bunit_3  on fincs_bunit_3.dimen = 'Y1'
                                                        and rassc               = fincs_bunit_3.rcomp
                                                        and rassc               <> ''
  -- Map GL Account to FS Item
    inner join             I_FSItemMappingAssgmtWithVers on  tf200.impvs        =       I_FSItemMappingAssgmtWithVers.FSItemMappingVersion
                                                         and fiscal_year_period between I_FSItemMappingAssgmtWithVers.ValidityStartFiscalYearPeriod and I_FSItemMappingAssgmtWithVers.ValidityEndFiscalYearPeriod
                                                         and ktopl              =       I_FSItemMappingAssgmtWithVers.ChartOfAccounts
                                                         and racct              =       I_FSItemMappingAssgmtWithVers.GLAccount
                                                         -- &NORELEASE is a dummy FS item, G/L accounts with this target must be ignored
                                                         and I_FSItemMappingAssgmtWithVers.FinancialStatementItem <>      '&NORELEASE' //bbr n2843448

  -- Get fs item attributes
    left outer to one join fincs_fsitem                  on  I_FSItemMappingAssgmtWithVers.ConsolidationChartOfAccounts = fincs_fsitem.itclg
                                                         and I_FSItemMappingAssgmtWithVers.FinancialStatementItem       = fincs_fsitem.item
  -- Get fs item's breakdown categories
    left outer to one join tf103a                        on  I_FSItemMappingAssgmtWithVers.ConsolidationChartOfAccounts = tf103a.itclg
                                                         and fincs_fsitem.itbrkdwn                                      = tf103a.itgrp
                                                         and tf103a.fieldname                                           = 'SITYP'
  -- Get subitem categories
    left outer to one join tf110                         on tf103a.value = tf110.sityp
-- Consolidation Unit Attributes Time Dependent
    left outer to one join  fincs_bunit_atd              on fincs_bunit_atd.dimen              =       'Y1'
                                                        and fincs_bunit_atd.bunit              =       fincs_bunit_2.bunit
                                                        and finsc_fisc_date.fiscal_year_period between fincs_bunit_atd.fromyearper and fincs_bunit_atd.toyearper      
{
  rclnt,
  tf200.rldnr_cons                                                          as rldnr,
  cast('Y1' as fc_dimen)                                                    as rdimen,
  finsc_fisc_date.fiscal_year                                               as ryear,
  gjahr,
  finsc_fisc_date.fiscal_year_period                                        as fiscyearper,
  belnr                                                                     as ref_belnr,
  docln                                                                     as ref_docln,
  rrcty,
  bttype,
  drcrk,
  tf200.rvers                                                               as rvers,
  tf200.iatvs                                                               as iatvs,
  @Semantics.currencyCode
  rwcur                                                                     as rwcur,
  @Semantics.currencyCode
  rtcur                                                                     as rtcur,
  @Semantics.currencyCode
  case fincs_bunit_atvd.local_curr_source
  when 'H'
  then rhcur
  when 'K'
  then rkcur
  when 'O'
  then rocur
  when 'V'
  then rvcur
  when 'B'
  then rbcur
  when 'C'
  then acdoca.rccur
  when 'D'
  then rdcur
  when 'E'
  then recur
  when 'F'
  then rhcur
  when 'G'
  then rgcur
  else rhcur
  end                                                                       as rhcur,
  @Semantics.currencyCode
  case fincs_bunit_atvd.group_curr_source
  when 'H'
  then rhcur
  when 'K'
  then rkcur
  when 'O'
  then rocur
  when 'V'
  then rvcur
  when 'B'
  then rbcur
  when 'C'
  then acdoca.rccur
  when 'D'
  then rdcur
  when 'E'
  then recur
  when 'F'
  then rhcur
  when 'G'
  then rgcur
  else tf200.group_curr
  end                                                                       as rkcur,
  @Semantics.unitOfMeasure
  runit,
  finsc_fisc_date.fiscal_period                                             as poper,
  cast('A' as docct)                                                        as docct,
  I_CompanyCode.Company                                                     as rcomp,
  fincs_bunit_2.bunit                                                       as rbunit,
  coalesce( I_FSItemMappingAssgmtWithVers.ConsolidationChartOfAccounts, '') as ritclg,
  coalesce( I_FSItemMappingAssgmtWithVers.FinancialStatementItem, '' )      as ritem,
  coalesce( fincs_bunit_3.bunit, '')                                        as rbuptr,
  cast('' as fc_congr)                                                      as rcongr,
  rbukrs                                                                    as robukrs,
  fincs_fsitem.itbrkdwn                                                     as itbrkdwn, //Breakdown Category

  //Subitem category

  case // only 0 and 3 are possible for SITYP

    when tf103a.breakdown = '0'
      or tf103a.breakdown is null
  // no breakdown

          then ''
    when tf103a.breakdown = '3'
  // 3 for SITYP only possible with fixed value

          then tf103a.value
   else ''
  end                                                                       as sityp,
  //Subitem: Evaluate sender field

  case tf110.sitem_send
    when '01' then cast(rmvct as fis_rmvct preserving type)
    when '02' then cast(rfarea as fm_farea preserving type)
    when '03' then cast(rbusa as fis_rbusa preserving type)
  //      when '04' then '' //currently not supported

    when '05' then cast(rcntr as fis_kostl preserving type)
    when '06' then cast(racct as fis_racct preserving type)
    when '07' then cast(prctr as fis_prctr preserving type)
    when '08' then segment
  //  else ''  // not possible

  end                                                                       as subit,
  cast('' as fc_plevl)                                                      as plevl,
  cast('0' as fc_rtflg)                                                     as rtflg,
  cast('0F' as fc_docty)                                                    as docty,
  blart,
  @Semantics.amount.currencyCode: 'RWCUR'
  wsl,
  @Semantics.amount.currencyCode: 'RTCUR'
  tsl,
  @Semantics.amount.currencyCode: 'RHCUR'
  case fincs_bunit_atvd.local_curr_source
  when 'H'
  then hsl
  when 'K'
  then ksl
  when 'O'
  then osl
  when 'V'
  then vsl
  when 'B'
  then bsl
  when 'C'
  then csl
  when 'D'
  then dsl
  when 'E'
  then esl
  when 'F'
  then fsl
  when 'G'
  then gsl
  else 0
  end                                                                       as hsl,
  @Semantics.amount.currencyCode: 'RKCUR'
  case fincs_bunit_atvd.group_curr_source
  when 'H'
    then hsl
  when 'K'
    then ksl
  when 'O'
    then osl
  when 'V'
    then vsl
  when 'B'
    then bsl
  when 'C'
    then csl
  when 'D'
    then dsl
  when 'E'
    then esl
  when 'F'
    then fsl
  when 'G'
    then gsl
  when ' '
    then case tf200.group_curr
      when fincs_bunit_atd.curr
        then hsl
      else cast( '0' as vgcur12 )
    end
  else cast( '0' as vgcur12 )
  end                                                                       as  ksl,
  @Semantics.quantity.unitOfMeasure: 'RUNIT'
  msl,
  sgtxt,
  budat,
  timestamp,
  awtyp,
  aworg,
  ktopl,
  racct,
  zuonr,
  rcntr,
  acdoca.prctr,
  rfarea,
  kokrs,
  segment,
  scntr,
  pprctr,
  sfarea,
  rassc,
  psegment,
  aufnr,
  kunnr,
  koart,
  umskz,
  acdoca.mwskz,
  acdoca.xopvw,
  augdt,
  augbl,
  auggj,
  lifnr,
  matnr,
  matkl_mm,
  werks,
  rmvct,
  ps_psp_pnr,
  ps_posid,
  ps_pspid,
  fkart,
  vkorg,
  vtweg,
  spart,
  matnr_copa,
  matkl,
  kdgrp,
  land1,
  brsch,
  bzirk,
  kunre,
  kunwe,
  konzs,
  xreversing,
  xreversed,
  awref,
  awref_rev
}
where
      bstat              <>      'D'
  and bstat              <>      'M'
  and poper              >       '000'
  and fiscal_year_period between fincs_bunit_atvd.fromyearper and fincs_bunit_atvd.toyearper