ICA_CONSJOURNALENTRIES_4

@AbapCatalog.sqlViewName: 'ICACONSJES4'
@AbapCatalog.compiler.compareFilter:true
@AbapCatalog.viewEnhancementCategory: [#PROJECTION_LIST,#UNION]
@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
define view ICA_CONSJOURNALENTRIES_4
  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
-- Cons Unit input versions
  left outer join        fincs_bunit_atvd              on  fincs_bunit_atvd.dimen     = 'Y1'
                                                       and fincs_bunit_atvd.bunit     = acdoca.rbunit
                                                       and fincs_bunit_atvd.trans     = 'H'
-- In case extension ledger, union extenstion ledger with leading ledger
  inner join             finsc_ledger_rep              on  acdoca.rldnr       = finsc_ledger_rep.rldnr_pers
-- Map input version to cons version
  inner join             tf200                         on  fincs_bunit_atvd.cuavs = tf200.cuavs
                                                       and finsc_ledger_rep.rldnr = tf200.rldnr_source_acc_int
                                                       and tf200.planvers         = ''

-- 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

  left outer join ICA_FS_ITEM as _assignment on  tf200.iatvs   = _assignment.iatvs
                                                 and acdoca.ritclg = _assignment.itclg
                                                 and acdoca.ritem  = _assignment.item
                                                 and finsc_fisc_date.fiscal_year_period between _assignment.fromyearper and _assignment.toyearper
-- Consolidation Unit Attributes Time Dependent
  inner join             fincs_bunit_atd            on  fincs_bunit_atd.dimen              =       'Y1'
                                                    and fincs_bunit_atd.bunit              =       acdoca.rbunit
                                                    and finsc_fisc_date.fiscal_year_period between fincs_bunit_atd.fromyearper and fincs_bunit_atd.toyearper

  inner join             fincs_fsitem               on  fincs_fsitem.itclg = acdoca.ritclg
                                                    and fincs_fsitem.item  = acdoca.ritem

--  TF103A: Breakdown info for unit of measure (RUNIT) and quantity (MSL) depending on breakdown category (ITGRP)
  left outer to one join tf103a                     on  tf103a.itclg     = acdoca.ritclg
                                                    and tf103a.itgrp     = fincs_fsitem.itbrkdwn
                                                    and tf103a.fieldname = 'RUNIT'
{
  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
  rwcur                                                                      as  rtcur,
  @Semantics.currencyCode
  cast( fincs_bunit_atd.curr as lcurr preserving type )                      as  rhcur,
  @Semantics.currencyCode
  tf200.group_curr                                                           as  rkcur,
  @Semantics.unitOfMeasure
  cast ( case tf103a.breakdown
    when '3'
      then ( case tf200.source_kf_qty
              when 'A'
                then runit
              when 'B'
                then runit
              when 'C'
                then rvunit // Unit of valuation quantity --> CostSourceUnit

              when 'D'
                then rvunit // Unit of valuation quantity --> CostSourceUnit

              when 'E'
                then rrunit
              when 'F'
                then qunit1
              when 'G'
                then qunit2
              when 'H'
                then qunit3
              else
                runit
              end )
    else
      cast ( '' as meins )
  end as meins preserving type )                                             as  runit,
  finsc_fisc_date.fiscal_period                                              as  poper,
  cast('A' as docct)                                                         as  docct,
  acdoca.rcomp                                                               as  rcomp,
  rbunit                                                                     as  rbunit,
  ritclg                                                                     as  ritclg,
  ritem                                                                      as  ritem,
  coalesce(_assignment.sa_role, '')                                          as  sa_role,
  coalesce(_assignment.sa_elimination, '')                                   as  sa_elimination,
  rbuptr                                                                     as  rbuptr,
  cast('' as fc_congr)                                                       as  rcongr,
  rbukrs                                                                     as  robukrs,
  sityp                                                                      as  sityp,
  subit                                                                      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: 'RTCUR'
  wsl                                                                        as  tsl,
  @Semantics.amount.currencyCode: 'RHCUR'
  case tf200.source_kf_lc
    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 'M'
      then fcsl
    else
      fcsl
  end                                                                        as  hsl,
  @Semantics.amount.currencyCode: 'RKCUR'
  case tf200.source_kf_gc
  when 'H'
    then fcsl
  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 'M'
    then fcsl
  when ' '
    then case 
         when fincs_bunit_atd.curr = tf200.group_curr
          then ( case tf200.source_kf_lc
                    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 'M'
                      then fcsl
                    else
                      fcsl
                  end )
        else cast ( 0 as vgcur9 )
    end
  else cast ( 0 as vgcur9 )    
  end                                                                        as  ksl,
  @Semantics.quantity.unitOfMeasure: 'RUNIT'
  cast ( case tf103a.breakdown
    when '3' 
      then ( case tf200.source_kf_qty
              when 'A'
                then msl
              when 'B'
                then mfsl
              when 'C'
                then vmsl
              when 'D'
                then vmfsl
              when 'E'
                then rmsl
              when 'F'
                then quant1
              when 'G'
                then quant2
              when 'H'
                then quant3
              else
                msl
            end )
    else 
      cast ( 0 as quan1_12 )
  end as quan1_12 preserving type )                                          as  msl,
  sgtxt,
  budat,
  timestamp,
  awtyp,
  aworg,
  ktopl,
  racct,
  zuonr,
  rcntr,
  acdoca.prctr,
  rfarea,
  acdoca.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 =       ''  //normal documents

    or bstat =       'U' //documents without lines in BSEG

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

    or bstat =       'P' //predicted documents, posted in a separate extension ledger

    or bstat =       '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 poper   > '000'
  // Year End Closing postings are not relevant for consolidation

  and awtyp   <>      'GLYEC'
  and fiscal_year_period between fincs_bunit_atvd.fromyearper and fincs_bunit_atvd.toyearper
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ICA_FS_ITEM",
"ACDOCA",
"ACDOCU",
"FINCS_BUNIT_ATD",
"FINCS_BUNIT_ATVD",
"FINCS_FSITEM",
"FINSC_FISC_DATE",
"FINSC_LEDGER_REP",
"TF103A",
"TF200"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/