v_ml_inv_base_view

@AbapCatalog.sqlViewName: 'FMLV_INV_BASE'
@EndUserText.label: 'ML Delta Entries - Aggregate of ACDOCA'
// should not be used anymore - GL has to fix the totals on GL-granularity

define view v_ml_inv_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,
         kalnr,
         matnr,
         a.bwkey  as bwkey,
         bwtar,
         kzbws,
         sobkz,
         mat_kdauf,
         mat_kdpos,
         mat_pspnr,
         mat_lifnr,
         mat_ps_posid,
         ktosl,
         koart,
         racct,
         a.ktopl,
         a.periv,
         werks,
         segment,
         prctr,
         rhcur,
         rkcur,
         rocur,
         runit,
         rvunit,
         rrunit,
--         sum(tsl) as tsl,
         sum(hsl) as hsl,
--         sum(ksl) as ksl,
         sum(osl) as osl,
         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 does the INSERT into ACDOCA
         from acdoca           as a
//   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 = ''

   inner join  fmlv_relind_bwke as r on r.mandt    = a.rclnt
                                    and r.rldnr    = a.rldnr
                                    and r.bwkey    = a.bwkey
                                    and r.mlrelindh <> 'X'
                                    and r.mlrelindk <> 'X'
                                    and r.mlrelindo <> 'X'
                                    and r.mlrelindv <> '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,
         kalnr,
         matnr,
         a.bwkey,
         bwtar,
         kzbws,
         sobkz,
         mat_kdauf,
         mat_kdpos,
         mat_pspnr,
         mat_lifnr,
         mat_ps_posid,
         ktosl,
         koart,
         racct,
         a.ktopl,
         a.periv,
         werks,
         segment,
         prctr,
         rhcur,
         rkcur,
         rocur,
         runit,
         rhcur,
         rkcur,
         rocur,
         rvunit,
         rrunit
   having  sum(hsl)  <> 0
---      or  sum(tsl)  <> 0
---      or  sum(ksl)  <> 0
       or  sum(osl)  <> 0
       or  sum(msl)  <> 0
       or  sum(vmsl) <> 0
       or  sum(rmsl) <> 0
   ;
     
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"ACDOCA",
"TKA01",
"TKA02",
"FMLV_RELIND_BWKE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/