v_ml_mig_base_view

@AbapCatalog.sqlViewName: 'FMLV_MIG_BASE'
@EndUserText.label: 'ML Delta Entries - Aggregate of ACDOCA'
define view v_ml_mig_base_view
as
  select rclnt, a.rldnr, rbukrs, gjahr, poper,            -- ryear,
         drcrk,                                           -- debit/credit
         perart,
         fiscyearper,
         coalesce(t3.kokrs, '')    as kokrs,
         coalesce(t4.erkrs, '')    as erkrs,
         matnr,
         a.bwkey,
         bwtar,
         kzbws,
         sobkz,
         xobew,
         kalnr,
         mat_kdauf,
         mat_kdpos,
         mat_pspnr,
         mat_lifnr,
         mat_ps_posid,
         ktosl,
         koart,
         racct,
         a.ktopl,
         a.periv,
         werks,
         prctr,
         segment,
         mlast,
         vprsv,
         rhcur,
         rkcur,
         rocur,
         rvcur,
         runit,
         rvunit,
         rrunit,
         sum(tsl) as tsl,
         sum(hsl) as hsl,
         sum(ksl) as ksl,
         sum(osl) as osl,
         sum(vsl) as vsl,
//         sum(hsalk3) as hsalk3,

//         sum(ksalk3) as ksalk3,

//         sum(osalk3) as osalk3,

//         sum(vsalk3) as vsalk3,

//         sum(hsalkv) as hsalkv,

//         sum(ksalkv) as ksalkv,

//         sum(osalkv) as osalkv,

//         sum(vsalkv) as vsalkv,

         sum(hslalt) as hslalt,
         sum(kslalt) as kslalt,
         sum(oslalt) as oslalt,
         sum(vslalt) as vslalt,

         sum(lbkum)  as lbkum,
         sum(msl)  as msl,
         sum(vmsl) as vmsl,
         sum(rmsl) as rmsl
--         max(timestamp) as timestamp              will be set in the ABAP-method that foes the INSERT to ACDOCA
         from acdoca             as a
   inner join fmlv_relind_bwke   as r
                                   on  r.mandt   = a.rclnt
                                  and  r.rldnr   = a.rldnr
                                  and  r.bukrs   = a.rbukrs
                                  and  r.bwkey   = a.bwkey
                                  and (   r.mlrelindh = 'X'
                                       or r.mlrelindk = 'X'
                                       or r.mlrelindo = 'X'
                                       or r.mlrelindv = 'X'
                                       )
//  inner join finsc_actve_appl as appl

//                                   on appl.mandt = a.rclnt

//  inner join finsc_ledger   as l   on l.mandt    = a.rclnt

//                                  and l.rldnr    = a.rldnr

//                                  and l.appl     = appl.appl

//                                  and l.subappl  = appl.subappl

//                                  and l.xleading = 'X'

//   inner join  finsc_ld_cmp as f   on  f.mandt   = a.rclnt

//                                  and  f.rldnr   = l.rldnr

//                                  and  f.bukrs   = a.rbukrs

//                                  and  f.mlrelindh = 'X'

   left outer join  tka02  as t3        on t3.mandt   = a.rclnt
                                       and t3.bukrs   = a.rbukrs
   left outer join  tka01  as t4        on t4.mandt   = a.rclnt
                                       and ( t3.kokrs is not null and t4.kokrs   = t3.kokrs )
   where  mig_source = 'M'        -- consider only items related to ML-migration ...
     and  bstat      = 'C'        -- ... GL-corrections will do the rest => in total less items and a better algorithm in case of DELTA-migration
//   where ktosl      = 'BSX'

//     and matnr      <> ''

//--     and bstat      <> 'C'  (also consider balance information from other applications)

//     and mig_source <> ''                                 -- consider migrated records only

  group by
         rclnt, a.rldnr, rbukrs, gjahr, poper,
         drcrk,
         perart,
         fiscyearper,
         t3.kokrs,
         t4.erkrs,
         matnr,
         a.bwkey,
         bwtar,
         kzbws,
         sobkz,
         xobew,
         kalnr,
         mat_kdauf,
         mat_kdpos,
         mat_pspnr,
         mat_lifnr,
         mat_ps_posid,
         ktosl,
         koart,
         racct,
         a.ktopl,
         a.periv,
         werks,
         prctr,
         segment,
         mlast,
         vprsv,
         rhcur,
         rkcur,
         rocur,
         rvcur,
         runit,
         rvunit,
         rrunit
   having  sum(tsl) <> 0
       or  sum(hsl) <> 0
       or  sum(ksl) <> 0
       or  sum(osl) <> 0
       or  sum(vsl) <> 0
       or  sum(hslalt) <> 0
       or  sum(kslalt) <> 0
       or  sum(oslalt) <> 0
       or  sum(vslalt) <> 0
       or  sum(msl) <> 0
       or  sum(vmsl) <> 0
       or  sum(rmsl) <> 0
   ;