ICA_CONSJOURNALENTRIES_5

@AbapCatalog.sqlViewName: 'ICACONSJES5'
@AbapCatalog.compiler.compareFilter:true
@EndUserText.label: 'Match Consolidation Journal Entry Lines'
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@Consumption.dbHints: ['USE_HEX_PLAN']
define view ICA_CONSJOURNALENTRIES_5
  as select from acdocu
  left outer join tf200 on acdocu.rvers = tf200.rvers
  left outer join ICA_FS_ITEM as _assignment on  tf200.iatvs   = _assignment.iatvs
                                                 and acdocu.ritclg = _assignment.itclg
                                                 and acdocu.ritem  = _assignment.item
                                                 and acdocu.fiscyearper between _assignment.fromyearper and _assignment.toyearper
{
  rclnt,
  cast ('' as ica_method_id)                                           as method_id,
  cast ('0000000000' as ica_docnr)                                     as DOCNR,
  cast (0 as ica_docln)                                                as DOCLN,
  cast ('000000000000' as ica_grref)                                   as GRREF,
  cast ('00' as ica_pstat)                                             as PSTAT,
  cast ('' as ica_cstat)                                               as CSTAT,
  cast ('00000000' as ica_due_date)                                    as DUE_DATE,
  cast ('0' as ica_clearing_status )                                   as CLEARING_STATUS,
  fiscyearper,
  rldnr,
  rdimen,
  ryear,
  ryear                                                                as gjahr,
  docnr                                                                as ref_belnr,
  docln                                                                as ref_docln,
  rrcty,
  cast('' as fins_bttype)                                              as BTTYPE,
  cast('' as shkzg)                                                    as drcrk,
  acdocu.rvers,
  @Semantics.currencyCode
  rtcur,
  @Semantics.currencyCode
  rhcur,
  @Semantics.currencyCode
  rkcur,
  @Semantics.unitOfMeasure
  runit,
  poper,
  docct,
  rcomp,
  rbunit,
  ritclg,
  ritem,
  coalesce(_assignment.sa_role, '')                                    as sa_role,
  coalesce(_assignment.sa_elimination, '')                             as sa_elimination,
  rbuptr,
  rcongr,
  robukrs,
  sityp,
  subit,
  plevl,
  --rpflg,
  rtflg,
  docty,
  cast('' as blart)                                                    as blart,
  --yracq,
  --pracq,
  --coicu,
  //  uppcu,

  @Semantics.amount.currencyCode: 'RTCUR'
  tsl,
  @Semantics.amount.currencyCode: 'RHCUR'
  hsl,
  @Semantics.amount.currencyCode: 'RKCUR'
  ksl,
  @Semantics.quantity.unitOfMeasure: 'RUNIT'
  msl,
  sgtxt,
  budat,
  timestamp,
  awtyp,
  aworg,
  ktopl,
  racct,
  zuonr,
  rcntr,
  prctr,
  rfarea,
  --rbusa,
  kokrs,
  segment,
  scntr,
  pprctr,
  sfarea,
  --sbusa,
  rassc,
  psegment,
  aufnr,
  kunnr,
  cast('' as koart)                                                    as koart,
  cast('' as umskz)                                                    as umskz,
  cast('' as mwskz)                                                    as mwskz,
  cast('' as xopvw)                                                    as xopvw,
  cast('00000000' as augdt)                                            as augdt,
  cast('' as augbl)                                                    as augbl,
  cast('' as auggj)                                                    as 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,
  case when orndocnr <> ''
       then cast('X' as fins_xreversing)
       else cast('' as fins_xreversing)   end                          as XREVERSING,
  case when rvsdocnr <> ''
       then cast('X' as fins_xreversed)
       else cast('' as fins_xreversed)    end                          as XREVERSED,
  cast('' as awref)                                                    as awref,
  case when orndocnr <> ''
       then cast(orndocnr as awref_rev)
       else cast(rvsdocnr as awref_rev)    end                         as AWREF_REV
}
where
       rrcty = '0'
  and(
       plevl = ''
    or plevl = '00'
    or plevl = '01'
    or plevl = '0C'
    or plevl = '10'
  ) and  draft <> 'X'
union all 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
-- 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 = ''
-- Cons Unit ledger dependent
--  left outer join        tf168                         on  fincs_bunit_2.dimen = tf168.dimen
--                                                       and fincs_bunit_2.bunit = tf168.bunit
-- Map consolidation ledger to G/L ledger
//  inner join             t881                          on  tf200.rldnr_source  = t881.rldnr

//                                                       and acdoca.rldnr = t881.orient_ledger

-- 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
-- fiscal year variant from dimension
--  inner join             tf150                         on tf150.dimen = 'Y1'

-- 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.rcomp = rassc

--left outer to one join tf110                         on tf110.sityp = '1'
-- 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

  left outer join ICA_FS_ITEM as _assignment on  tf200.iatvs   = _assignment.iatvs
                                                 and I_FSItemMappingAssgmtWithVers.ConsolidationChartOfAccounts = _assignment.itclg
                                                 and I_FSItemMappingAssgmtWithVers.FinancialStatementItem = _assignment.item
                                                 and finsc_fisc_date.fiscal_year_period between _assignment.fromyearper and _assignment.toyearper
--  left outer join        skb1                          on  rbukrs = skb1.bukrs
--                                                       and racct  = skb1.saknr
-- 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,
  cast ('' as ica_method_id)                                                 as  method_id,
  cast ('0000000000' as ica_docnr)                                           as  DOCNR,
  cast (0 as ica_docln)                                                      as  DOCLN,
  cast ('000000000000' as ica_grref)                                         as  GRREF,
  cast ('00' as ica_pstat)                                                   as  PSTAT,
  cast ('' as ica_cstat)                                                     as  CSTAT,
  cast ('00000000' as ica_due_date)                                          as  DUE_DATE,
  --0 N/A， 1   Open，2   Parked，3   Cleared
  case when xopvw = 'X'
       then ( case when augbl <> '' then cast ('3' as ica_clearing_status )
                                    else cast ('1' as ica_clearing_status )
             end  )
     else cast ('0' as ica_clearing_status )
     end                                                                     as  CLEARING_STATUS,
  finsc_fisc_date.fiscal_year_period                                         as fiscyearper,
  tf200.rldnr_cons                                                           as  rldnr,
  cast('Y1' as fc_dimen)                                                     as  rdimen,
  finsc_fisc_date.fiscal_year                                                as  ryear,
  gjahr,
  belnr                                                                      as  ref_belnr,
  docln                                                                      as  ref_docln,
  rrcty,
  bttype,
  drcrk,
  tf200.rvers                                                                as  rvers,
  @Semantics.currencyCode
  case when poper = '000' then rtcur else rwcur end                          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(_assignment.sa_role, '')                                          as sa_role,
  coalesce(_assignment.sa_elimination, '')                                   as sa_elimination,
  coalesce( fincs_bunit_3.bunit, '')                                         as  rbuptr,
  cast('' as fc_congr)                                                       as  rcongr,
  rbukrs                                                                     as  robukrs,
  cast('1' as fc_sityp)                                                      as  sityp,
  cast(rmvct as fc_sitem)                                                    as  subit,
  cast('' as fc_plevl)                                                       as  plevl,
  --cast('0' as fc_rpflg)                                                 as  rpflg,
  cast('0' as fc_rtflg)                                                      as  rtflg,
  cast('0F' as fc_docty)                                                     as  docty,
  blart,
  --cast('0000' as fc_ryacq)                                              as  yracq,
  --cast('000' as rpacq)                                                  as  pracq,
  --cast('' as fc_coicu)                                                  as  coicu,
  //  uppcu,

  @Semantics.amount.currencyCode: 'RTCUR'
  case when poper = '000' then tsl else wsl end                              as  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,
  --rbusa,
  acdoca.kokrs,
  segment,
  scntr,
  pprctr,
  sfarea,
  --sbusa,
  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