ICA_CONSJOURNALENTRIES_7

@Metadata.ignorePropagatedAnnotations: true
@EndUserText.label: 'ICA: Consolidation Journal Entry Line Items'
@ObjectModel: { usageType.sizeCategory: #XXL,
                usageType.dataClass:  #TRANSACTIONAL,
                usageType.serviceQuality: #X,
                supportedCapabilities: [ #SQL_DATA_SOURCE ] }
define view entity ICA_CONSJOURNALENTRIES_7
  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
{
  $session.client                                  as 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,
  acdocu.fiscyearper,
  acdocu.rldnr,
  acdocu.rdimen,
  acdocu.ryear,
  acdocu.ryear                                     as gjahr,
  acdocu.docnr                                     as ref_belnr,
  acdocu.docln                                     as ref_docln,
  acdocu.rrcty,
  cast('' as fins_bttype)                          as BTTYPE,
  cast('' as shkzg)                                as drcrk,
  acdocu.rvers,
  acdocu.rtcur,
  acdocu.rhcur,
  acdocu.rkcur,
  acdocu.runit,
  acdocu.poper,
  acdocu.docct,
  acdocu.rcomp,
  acdocu.rbunit,
  acdocu.ritclg,
  acdocu.ritem,
  coalesce(_assignment.sa_role, '')                as sa_role,
  coalesce(_assignment.sa_elimination, '')         as sa_elimination,
  acdocu.rbuptr,
  acdocu.rcongr,
  acdocu.robukrs,
  acdocu.sityp,
  acdocu.subit,
  acdocu.plevl,
  acdocu.rtflg,
  acdocu.docty,
  cast('' as blart)                                as blart,
  @Semantics.amount.currencyCode: 'RTCUR'
  acdocu.tsl,
  @Semantics.amount.currencyCode: 'RHCUR'
  acdocu.hsl,
  @Semantics.amount.currencyCode: 'RKCUR'
  acdocu.ksl,
  @Semantics.quantity.unitOfMeasure: 'RUNIT'
  acdocu.msl,
  acdocu.sgtxt,
  acdocu.budat,
  acdocu.timestamp,
  acdocu.awtyp,
  acdocu.aworg,
  acdocu.ktopl,
  acdocu.racct,
  acdocu.zuonr,
  acdocu.rcntr,
  acdocu.prctr,
  acdocu.rfarea,
  acdocu.kokrs,
  acdocu.segment,
  acdocu.scntr,
  acdocu.pprctr,
  acdocu.sfarea,
  acdocu.rassc,
  acdocu.psegment,
  acdocu.aufnr,
  acdocu.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,
  acdocu.lifnr,
  acdocu.matnr,
  acdocu.matkl_mm,
  acdocu.werks,
  acdocu.rmvct,
  acdocu.ps_psp_pnr,
  acdocu.ps_posid,
  acdocu.ps_pspid,
  acdocu.fkart,
  acdocu.vkorg,
  acdocu.vtweg,
  acdocu.spart,
  acdocu.matnr_copa,
  acdocu.matkl,
  acdocu.kdgrp,
  acdocu.land1,
  acdocu.brsch,
  acdocu.bzirk,
  acdocu.kunre,
  acdocu.kunwe,
  acdocu.konzs,
  case when acdocu.orndocnr <> ''
       then cast('X' as fins_xreversing)
       else cast('' as fins_xreversing)   end      as XREVERSING,
  case when acdocu.rvsdocnr <> ''
       then cast('X' as fins_xreversed)
       else cast('' as fins_xreversed)    end      as XREVERSED,
  cast('' as awref)                                as awref,
  case when acdocu.orndocnr <> ''
       then cast(acdocu.orndocnr as awref_rev)
       else cast(acdocu.rvsdocnr as awref_rev) end as AWREF_REV
}
where
       acdocu.rrcty =  '0'
  and(
       acdocu.plevl =  ''
    or acdocu.plevl =  '00'
    or acdocu.plevl =  '01'
    or acdocu.plevl =  '0C'
    or acdocu.plevl =  '10'
  )
  and  acdocu.draft <> 'X'
union all select from    acdoca
-- Join company code to company
  inner join             I_CompanyCode                 on acdoca.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         = ''
-- 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 Trading Partner to Cons Partner unit
  left outer to one join fincs_bunit as fincs_bunit_3  on fincs_bunit_3.rcomp = acdoca.rassc

-- Map GL Account to FS Item
  inner join             I_FSItemMappingAssgmtWithVers on  tf200.impvs        =       I_FSItemMappingAssgmtWithVers.FSItemMappingVersion
                                                       and acdoca.fiscyearper between I_FSItemMappingAssgmtWithVers.ValidityStartFiscalYearPeriod and I_FSItemMappingAssgmtWithVers.ValidityEndFiscalYearPeriod
                                                       and acdoca.ktopl       =       I_FSItemMappingAssgmtWithVers.ChartOfAccounts
                                                       and acdoca.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 acdoca.fiscyearper                                         between _assignment.fromyearper and _assignment.toyearper
-- 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 acdoca.fiscyearper        between fincs_bunit_atd.fromyearper and fincs_bunit_atd.toyearper                                                           
{
  $session.client                                                           as 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 acdoca.xopvw = 'X'
       then ( case when acdoca.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,
  acdoca.fiscyearper                                                        as fiscyearper,
  tf200.rldnr_cons                                                          as rldnr,
  cast('Y1' as fc_dimen)                                                    as rdimen,
  acdoca.gjahr                                                              as ryear,
  acdoca.gjahr,
  acdoca.belnr                                                              as ref_belnr,
  acdoca.docln                                                              as ref_docln,
  acdoca.rrcty,
  acdoca.bttype,
  acdoca.drcrk,
  tf200.rvers                                                               as rvers,
  case when acdoca.poper = '000' then acdoca.rtcur else acdoca.rwcur end    as rtcur,
  case fincs_bunit_atvd.local_curr_source
  when 'H'
  then acdoca.rhcur
  when 'K'
  then acdoca.rkcur
  when 'O'
  then acdoca.rocur
  when 'V'
  then acdoca.rvcur
  when 'B'
  then acdoca.rbcur
  when 'C'
  then acdoca.rccur
  when 'D'
  then acdoca.rdcur
  when 'E'
  then acdoca.recur
  when 'F'
  then acdoca.rhcur
  when 'G'
  then acdoca.rgcur
  else acdoca.rhcur
  end                                                                       as rhcur,
  case fincs_bunit_atvd.group_curr_source
  when 'H'
  then acdoca.rhcur
  when 'K'
  then acdoca.rkcur
  when 'O'
  then acdoca.rocur
  when 'V'
  then acdoca.rvcur
  when 'B'
  then acdoca.rbcur
  when 'C'
  then acdoca.rccur
  when 'D'
  then acdoca.rdcur
  when 'E'
  then acdoca.recur
  when 'F'
  then acdoca.rhcur
  when 'G'
  then acdoca.rgcur
  else tf200.group_curr
  end                                                                       as rkcur,
  acdoca.runit,
  acdoca.poper                                                              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,
  acdoca.rbukrs                                                             as robukrs,
  cast('1' as fc_sityp)                                                     as sityp,
  cast(acdoca.rmvct as fc_sitem)                                            as subit,
  cast('' as fc_plevl)                                                      as plevl,
  cast('0' as fc_rtflg)                                                     as rtflg,
  cast('0F' as fc_docty)                                                    as docty,
  acdoca.blart,
  //  @Semantics.amount.currencyCode: 'RTCUR'

  case when acdoca.poper = '000' then acdoca.tsl else acdoca.wsl end        as tsl,
  //  @Semantics.amount.currencyCode: 'RHCUR'

  case fincs_bunit_atvd.local_curr_source
  when 'H'
  then acdoca.hsl
  when 'K'
  then acdoca.ksl
  when 'O'
  then acdoca.osl
  when 'V'
  then acdoca.vsl
  when 'B'
  then acdoca.bsl
  when 'C'
  then acdoca.csl
  when 'D'
  then acdoca.dsl
  when 'E'
  then acdoca.esl
  when 'F'
  then acdoca.fsl
  when 'G'
  then acdoca.gsl
  else cast('0' as vgcur12)
  end                                                                       as hsl,
  //  @Semantics.amount.currencyCode: 'RKCUR'

  case fincs_bunit_atvd.group_curr_source
  when 'H'
    then acdoca.hsl
  when 'K'
    then acdoca.ksl
  when 'O'
    then acdoca.osl
  when 'V'
    then acdoca.vsl
  when 'B'
    then acdoca.bsl
  when 'C'
    then acdoca.csl
  when 'D'
    then acdoca.dsl
  when 'E'
    then acdoca.esl
  when 'F'
    then acdoca.fsl
  when 'G'
    then acdoca.gsl
  when ' '
    then case tf200.group_curr
      when fincs_bunit_atd.curr
        then acdoca.hsl
      else cast( '0' as vgcur12 )
    end
  else cast( '0' as vgcur12 )
  end                                                                       as ksl,
  //  @Semantics.quantity.unitOfMeasure: 'RUNIT'

  acdoca.msl,
  acdoca.sgtxt,
  acdoca.budat,
  acdoca.timestamp,
  acdoca.awtyp,
  acdoca.aworg,
  acdoca.ktopl,
  acdoca.racct,
  acdoca.zuonr,
  acdoca.rcntr,
  acdoca.prctr,
  acdoca.rfarea,
  acdoca.kokrs,
  acdoca.segment,
  acdoca.scntr,
  acdoca.pprctr,
  acdoca.sfarea,
  acdoca.rassc,
  acdoca.psegment,
  acdoca.aufnr,
  acdoca.kunnr,
  acdoca.koart,
  acdoca.umskz,
  acdoca.mwskz,
  acdoca.xopvw,
  acdoca.augdt,
  acdoca.augbl,
  acdoca.auggj,
  acdoca.lifnr,
  acdoca.matnr,
  acdoca.matkl_mm,
  acdoca.werks,
  acdoca.rmvct,
  acdoca.ps_psp_pnr,
  acdoca.ps_posid,
  acdoca.ps_pspid,
  acdoca.fkart,
  acdoca.vkorg,
  acdoca.vtweg,
  acdoca.spart,
  acdoca.matnr_copa,
  acdoca.matkl,
  acdoca.kdgrp,
  acdoca.land1,
  acdoca.brsch,
  acdoca.bzirk,
  acdoca.kunre,
  acdoca.kunwe,
  acdoca.konzs,
  acdoca.xreversing,
  acdoca.xreversed,
  acdoca.awref,
  acdoca.awref_rev
}
where
      acdoca.bstat       <>      'D'
  and acdoca.bstat       <>      'M'
  and acdoca.poper       >       '000'
  and acdoca.fiscyearper between fincs_bunit_atvd.fromyearper and fincs_bunit_atvd.toyearper