FCML_CCS_REP_DDL

@AbapCatalog.sqlViewName: 'FCML_CCS_REP_V'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #AUTOMATED
@EndUserText.label: 'CDS View w.struct. of table FCML_CCS_REP'

define view FCML_CCS_REP_DDL
  with parameters
    @Environment.systemField: #SYSTEM_LANGUAGE
    p_sys_langu : syst_langu
  as select from    mldoc     as doc
    inner join      mlrunlist as mlrun on  doc.kalnr   = mlrun.kalnr
                                       and doc.jahrper = mlrun.jahrper
                                       and (
                                          mlrun.runref like 'ACT%'
                                        )
  -- to avoid multipling rows when AVR with same JAHRPER exists.
    inner join      mldocccs  as ccs   on  doc.docref = ccs.docref
                                       and doc.curtp  = ccs.curtp
    inner join      tckh3     as t3    on  ccs.elesmhk = t3.elehk
                                       and ccs.element = t3.elemt
    left outer join tckh1     as t1    on  t1.spras    = $parameters.p_sys_langu
                                       and ccs.elesmhk = t1.elehk
                                       and ccs.element = t1.elemt
{
  key cast ( doc.kalnr as ck_kalnr           preserving type )      as kalnr_mat,
  key cast ( substring(doc.jahrper, 5, 3) as poper)                 as poper,
  key cast ( substring(doc.jahrper, 1, 4) as bdatj)                 as BDATJ,
  key cast ( '        ' as ckml_run_type     preserving type )      as RUN_ACT,
  key cast ( '    ' as ckml_run_appl         preserving type )      as RUN_APPL,
  key cast ( doc.categ   as ckml_categ       preserving type )      as CATEG,
  key cast ( doc.ptyp    as ck_ptyp_bvalt    preserving type )      as PTYP,
  key cast ( doc.posart  as ck_psart         preserving type )      as PSART,
  key cast ( doc.pkalnr as ck_kalnr          preserving type )      as KALNR_PMAT,
  key cast ( doc.bvalt as ckml_alprocnr      preserving type )      as bvalt,
  key cast ( doc.process as ckml_f_procnr    preserving type )      as process,
  key cast ( ccs.elesmhk     as ck_elesmhk   preserving type )      as elesmhk,
  key cast ( ccs.element     as ck_element   preserving type )      as element,
  key cast ( ccs.mlcct       as fcml_mlcct   preserving type )      as mlcct,
  key doc.curtp, 
  key doc.rldnr,
      cast ( t3.egrup        as ck_el_grup   preserving type )      as egrup,
      @Semantics.amount.currencyCode: 'waers'
      case doc.categ
      when 'EB' then
                 0 - cast ( sum (ccs.fix)   as fcml_ccsvalf )
      else
                 cast ( sum (ccs.fix)   as fcml_ccsvalf )
      end                                                           as PRCDIF_FIX,
      @Semantics.amount.currencyCode: 'waers'
      case doc.categ
      when 'EB' then
                 cast (sum ( ccs.fix)   as fcml_ccsvalv )
                 - cast ( sum ( ccs.tot)  as fcml_ccsvalv )
      else
                 cast ( sum ( ccs.tot)  as fcml_ccsvalv )
                 - cast (sum ( ccs.fix)   as fcml_ccsvalv )
      end                                                           as PRCDIF_VAR,
      @Semantics.currencyCode
      doc.waers,
      cast ( t1.txele as fcml_eletxt  preserving type )             as ELETXT,
      @Semantics.amount.currencyCode: 'waers'
      case doc.categ
      when 'EB' then
                 0 - cast ( sum (ccs.fixll) as fcml_ccsvalf_s )
      else
                 cast ( sum (ccs.fixll) as fcml_ccsvalf_s )
      end                                                           as PRCDIF_FIX_S,
      @Semantics.amount.currencyCode: 'waers'
      case doc.categ
      when 'EB' then
                 cast ( sum (ccs.fixll)   as fcml_ccsvalv_s )
                 - cast ( sum (ccs.totll)  as fcml_ccsvalv_s )
      else
                 cast ( sum (ccs.totll)  as fcml_ccsvalv_s )
                 - cast ( sum (ccs.fixll)   as fcml_ccsvalv_s )
      end                                                           as PRCDIF_VAR_S
}
where
     doc.runref = ''
  or doc.runref like 'ACT%'
group by
  doc.kalnr,
  doc.jahrper,
  categ,
  ptyp,
  posart,
  pkalnr,
  bvalt,
  process,
  elesmhk,
  element,
  mlcct,
  doc.curtp,
  doc.rldnr,
  egrup,
  doc.waers,
  t1.txele

union all select from fcml_ccs_r_ae_v as ccs_ext_ae
  inner join          tckh3           as t3 on  ccs_ext_ae.elesmhk = t3.elehk
                                            and ccs_ext_ae.element = t3.elemt
  left outer join     tckh1           as t1 on  spras              = $parameters.p_sys_langu
                                            and ccs_ext_ae.elesmhk = t1.elehk
                                            and ccs_ext_ae.element = t1.elemt
{
  kalnr_mat,
  poper,
  bdatj,
  run_act,
  run_appl,
  categ,
  ptyp,
  psart,
  kalnr_pmat,
  bvalt,
  process,
  ccs_ext_ae.elesmhk,
  ccs_ext_ae.element,
  ccs_ext_ae.mlcct,
  ccs_ext_ae.curtp,
  ccs_ext_ae.rldnr,
  t3.egrup,
  @Semantics.amount.currencyCode: 'waers'
  ccs_ext_ae.prcdif_fix,
  @Semantics.amount.currencyCode: 'waers'
  ccs_ext_ae.prcdif_var,
  @Semantics.currencyCode
  waers,
  cast ( t1.txele as fcml_eletxt  preserving type )              as ELETXT,
  @Semantics.amount.currencyCode: 'waers'
  ccs_ext_ae.prcdif_fix_s,
  @Semantics.amount.currencyCode: 'waers'
  ccs_ext_ae.prcdif_var_s
} 
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"MLDOC",
"MLDOCCCS",
"MLRUNLIST",
"TCKH1",
"TCKH3",
"FCML_CCS_R_AE_V"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/