ACE_ACEDSOI_CMPATBL

@AbapCatalog.sqlViewName: 'ACEV_ACEDSOI'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:  #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@EndUserText.label: 'Compatibility view for ACEDSOI'
@AbapCatalog.preserveKey:true

define view ACE_ACEDSOI_CMPATBL
  as select from    acesobj_item as _item
    left outer join acesobj      as _sobj on  _sobj.comp       = _item.comp
                                          and _sobj.logsys     = _item.logsys
                                          and _sobj.bukrs      = _item.bukrs
                                          and _sobj.ref_key    = _item.ref_key
                                          and _sobj.ref_subkey = _item.ref_subkey
    left outer join fagl_tldgrp_map       on  _item.rldnr               = fagl_tldgrp_map.rldnr
                                          and fagl_tldgrp_map.represent = 'X'
    left outer join finsc_ledger          on  finsc_ledger.xleading = 'X'
                                          and finsc_ledger.rldnr    = _item.rldnr
    inner join      tace_itemtypel        on  tace_itemtypel.comp      = _item.comp
                                          and tace_itemtypel.bukrs     = _item.bukrs
                                          and tace_itemtypel.itemtype  = _item.itemtype
                                          and tace_itemtypel.xinactive = ' ' //ignore inactive records

                                          and (
                                             tace_itemtypel.ldgrp      = fagl_tldgrp_map.ldgrp
                                             or tace_itemtypel.ldgrp   = ' '
                                           )
    left outer join tacc_trgt_ldgr        on tace_itemtypel.ldgrp = tacc_trgt_ldgr.ldgrp_gl
    inner join      tace_combination      on  tace_combination.comp      = tace_itemtypel.comp
                                          and tace_combination.bukrs     = _item.bukrs
                                          and (
                                             tace_combination.accrule    = tacc_trgt_ldgr.acc_principle
                                             or tace_combination.accrule = ' '
                                           )
{
  key _item.comp,
  key _item.bukrs,
  key _sobj.objid,
  key _sobj.subid,
  key tace_itemtypel.acrtype,
  key case when tace_combination.accrule <> '' then tace_combination.accrule else finsc_ledger.acc_principle end as accrule,
  key max( _item.date_to )                                                                                       as date_to,
      min( _item.date_from )                                                                                     as date_from,
      _item.timestmp,
      _item.total_accr_amnt_wsl                                                                                  as amount,
      _item.rwcurttl                                                                                             as currency,
      _item.quantity,
      _item.unit,
      _item.acrmethod,
      //      _item.life_start_date                  as svality_from,

      //      _item.life_end_date                    as svality_to,

      _item.xobsolete

}
where
  tace_itemtypel.acrtype = right(tace_itemtypel.itemtype, 6) // if accrual type is numeric, the migrated item type could be formated with ALPHA coversion. 001001 <> 0000001001

group by
  _item.comp,
  _item.bukrs,
  _sobj.objid,
  _sobj.subid,
  tace_itemtypel.acrtype,
  tace_itemtypel.ldgrp,
  finsc_ledger.acc_principle,
  tace_combination.accrule,
  _item.timestmp,
  _item.total_accr_amnt_wsl,
  _item.rwcurttl,
  _item.quantity,
  _item.unit,
  _item.acrmethod,
  //  _item.life_start_date,

  //  _item.life_end_date,

  _item.xobsolete

union all
// for these accrual objects which have more than two items and the accrual principles have the same legder group,

// it's impossible to covert ledger group to accrurl principle one by one successfully. So it's only one way that spliting

// the item type. In this situation, item type must be type of accrualtype_accruleprinciple.

select from       acesobj_item as _item
  left outer join acesobj      as _sobj on  _sobj.comp       = _item.comp
                                        and _sobj.logsys     = _item.logsys
                                        and _sobj.bukrs      = _item.bukrs
                                        and _sobj.ref_key    = _item.ref_key
                                        and _sobj.ref_subkey = _item.ref_subkey
  inner join      tace_itemtypel        on  tace_itemtypel.comp      = _item.comp
                                        and tace_itemtypel.bukrs     = _item.bukrs
                                        and tace_itemtypel.itemtype  = _item.itemtype
                                        and tace_itemtypel.xinactive = ' ' //ignore inactive records

{
  key _item.comp,
  key _item.bukrs,
  key _sobj.objid,
  key _sobj.subid,
  key tace_itemtypel.acrtype,
  key replace(tace_itemtypel.itemtype, concat(tace_itemtypel.acrtype, '_'), '') as accrule,
  key max( _item.date_to )                                                      as date_to,
      min( _item.date_from )                                                    as date_from,
      _item.timestmp,
      _item.total_accr_amnt_wsl                                                 as amount,
      _item.rwcurttl                                                            as currency,
      _item.quantity,
      _item.unit,
      _item.acrmethod,
      //      _item.life_start_date                  as svality_from,

      //      _item.life_end_date                    as svality_to,

      _item.xobsolete

}
where
  tace_itemtypel.acrtype <> right(tace_itemtypel.itemtype, 6)
group by
  _item.comp,
  _item.bukrs,
  _sobj.objid,
  _sobj.subid,
  tace_itemtypel.acrtype,
  tace_itemtypel.itemtype,
  tace_itemtypel.ldgrp,
  _item.timestmp,
  _item.total_accr_amnt_wsl,
  _item.rwcurttl,
  _item.quantity,
  _item.unit,
  _item.acrmethod,
  //  _item.life_start_date,

  //  _item.life_end_date,

  _item.xobsolete