@AbapCatalog.sqlViewName: 'FCML_REP_AVR_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 struct.of table FCML_REP f. AVR'
define view FCML_REP_AVR_DDL
-- read data related directly to AVR from MLDOC (single period AVR)
as select from mldoc as doc
inner join mlrunlist as mlrun on doc.runref = mlrun.runref
and doc.kalnr = mlrun.kalnr
and doc.jahrper = mlrun.jahrper
and doc.runref >= '000000000000'
and doc.runref <= '999999999999'
and doc.runref <> ''
inner join fcml_runperavr_v as rperiod on -- mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and rperiod.run_appl = 'CUM'
and rperiod.x_multiper = ' '
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl 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 doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0
else
cast ( sum(doc.stval) as ck_salk3_1 )
end as salk3,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0 - cast ( sum (doc.prd) as ckml_estprd )
else
cast ( sum (doc.prd) as ckml_estprd )
end as estprd,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0 - cast ( sum (doc.kdm) as ckml_estkdm )
else
cast ( sum (doc.kdm) as ckml_estkdm )
end as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
rperiod.jahrper_to,
rperiod.runref,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
doc.meins,
doc.waers
union all
-- read data related directly to AVR from MLDOC (multi period AVR)
select from mldoc as doc
inner join mlrunlist as mlrun on doc.runref = mlrun.runref
and doc.kalnr = mlrun.kalnr
and doc.jahrper = mlrun.jahrper
and doc.runref >= '000000000000'
and doc.runref <= '999999999999'
and doc.runref <> ''
inner join fcml_runperavr_v as rperiod on --mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and mlrun.from_jahrper = rperiod.jahrper_from
and rperiod.run_appl = 'CUM'
and rperiod.x_multiper = 'X'
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl 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 doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0
else
cast ( sum(doc.stval) as ck_salk3_1 )
end as salk3,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0 - cast ( sum (doc.prd) as ckml_estprd )
else
cast ( sum (doc.prd) as ckml_estprd )
end as estprd,
@Semantics.amount.currencyCode: 'waers'
case doc.categ
when 'EB' then
0 - cast ( sum (doc.kdm) as ckml_estkdm )
else
cast ( sum (doc.kdm) as ckml_estkdm )
end as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
rperiod.jahrper_to,
rperiod.runref,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
doc.meins,
doc.waers
union all
-- periodic data related to AVR (single period AVR)
select from mldoc as doc
inner join mlrunlist as mlrun on doc.kalnr = mlrun.kalnr
and doc.runref = ''
and doc.jahrper = mlrun.jahrper
inner join fcml_runperavr_v as rperiod on --mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper = ' '
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl 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 doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum(doc.stval) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (doc.prd) as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (doc.kdm) as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
where
doc.jahrper = rperiod.jahrper_to
and doc.posart <> 'CE'
and doc.posart <> 'CR'
and doc.posart <> 'ST'
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
rperiod.jahrper_to,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
rperiod.jahrper_to,
rperiod.jahrper_from,
doc.meins,
doc.waers
union all
-- periodic data related to AVR (multi period AVR)
select from mldoc as doc
inner join mlrunlist as mlrun on doc.kalnr = mlrun.kalnr
and doc.runref = ''
and doc.jahrper <= mlrun.jahrper
and doc.jahrper >= mlrun.from_jahrper
inner join fcml_runperavr_v as rperiod on -- mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and mlrun.from_jahrper = rperiod.jahrper_from
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper = 'X'
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl 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 doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum(doc.stval) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (doc.prd) as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (doc.kdm) as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
where
doc.posart <> 'CE'
and doc.posart <> 'CR'
and doc.posart <> 'ST'
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
rperiod.jahrper_to,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
rperiod.jahrper_to,
rperiod.jahrper_from,
doc.meins,
doc.waers
--beginning inventory from FCML_REP_V (multi period AVR)
union all select from fcml_rep_v as rep
inner join mlrunlist as mlrun on rep.kalnr_mat = mlrun.kalnr
and rep.poper = substring(
mlrun.from_jahrper, 5, 3
)
and rep.bdatj = substring(
mlrun.from_jahrper, 1, 4
)
inner join fcml_runperavr_v as rperiod on --mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and mlrun.from_jahrper = rperiod.jahrper_from
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper = 'X'
{
key cast (rep.kalnr_mat as ck_kalnr preserving type ) as kalnr_mat,
key cast (rperiod.poper as poper) as poper,
key cast (rperiod.bdatj as bdatj) as BDATJ,
key cast (rperiod.run_act as ckml_run_type) as RUN_ACT,
key cast (rperiod.run_appl as ckml_run_appl) as RUN_APPL,
key cast('AB' as ckml_categ preserving type ) as CATEG,
key cast(' ' as ck_ptyp_bvalt preserving type ) as PTYP,
key cast(' ' as ck_psart preserving type ) as PSART,
key cast ( '000000000000' as ck_kalnr preserving type ) as KALNR_PMAT,
key cast ( '000000000000' as ckml_alprocnr preserving type ) as bvalt,
key cast ( '000000000000' as ckml_f_procnr preserving type ) as process,
key rep.curtp,
key rep.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (rep.lbkum) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( rep.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (rep.salk3) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
rep.waers
}
where
rep.categ = 'AB'
group by
rep.curtp,
rep.rldnr,
rep.kalnr_mat,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
rperiod.jahrper_to,
rperiod.jahrper_from,
rep.meins,
rep.waers
--beginning inventory from FCML_REP_V (single period AVR)
--in MLRUNLIST field FROM_JAHRPER is not filled for single period AVR
union all select from fcml_rep_v as rep
inner join mlrunlist as mlrun on rep.kalnr_mat = mlrun.kalnr
and rep.poper = substring(
mlrun.jahrper, 5, 3
)
and rep.bdatj = substring(
mlrun.jahrper, 1, 4
)
inner join fcml_runperavr_v as rperiod on -- mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and mlrun.jahrper = rperiod.jahrper_from
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper <> 'X'
{
key cast (rep.kalnr_mat as ck_kalnr preserving type ) as kalnr_mat,
key cast (rperiod.poper as poper) as poper,
key cast (rperiod.bdatj as bdatj) as BDATJ,
key cast (rperiod.run_act as ckml_run_type) as RUN_ACT,
key cast (rperiod.run_appl as ckml_run_appl) as RUN_APPL,
key cast ('AB' as ckml_categ preserving type ) as CATEG,
key cast (' ' as ck_ptyp_bvalt preserving type ) as PTYP,
key cast (' ' as ck_psart preserving type ) as PSART,
key cast ( '000000000000' as ck_kalnr preserving type ) as KALNR_PMAT,
key cast ( '000000000000' as ckml_alprocnr preserving type ) as bvalt,
key cast ( '000000000000' as ckml_f_procnr preserving type ) as process,
key rep.curtp,
key rep.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (rep.lbkum) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( rep.meins as meins preserving type ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (rep.salk3) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
rep.waers
}
where
rep.categ = 'AB'
group by
rep.curtp,
rep.rldnr,
rep.kalnr_mat,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
rperiod.jahrper_to,
rperiod.jahrper_from,
rep.meins,
rep.waers
--ending inventory from FCML_REP_V (multi period AVR AND single period AVR)
union all select from fcml_rep_v as rep
inner join mlrunlist as mlrun on rep.kalnr_mat = mlrun.kalnr
and rep.poper = substring(
mlrun.jahrper, 5, 3
)
and rep.bdatj = substring(
mlrun.jahrper, 1, 4
)
inner join fcml_runperavr_v as rperiod on --mlrun.runref = rperiod.runref
mlrun.run_id = rperiod.run_id -- note 2835282
and mlrun.jahrper = rperiod.jahrper_to
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
{
key cast (rep.kalnr_mat as ck_kalnr preserving type ) as kalnr_mat,
key cast (rperiod.poper as poper) as poper,
key cast (rperiod.bdatj as bdatj) as BDATJ,
key cast (rperiod.run_act as ckml_run_type) as RUN_ACT,
key cast (rperiod.run_appl as ckml_run_appl) as RUN_APPL,
key cast ( 'EB' as ckml_categ preserving type ) as CATEG,
key cast ( ' ' as ck_ptyp_bvalt preserving type ) as PTYP,
key cast ( ' ' as ck_psart preserving type ) as PSART,
key cast ( '000000000000' as ck_kalnr preserving type ) as KALNR_PMAT,
key cast ( '000000000000' as ckml_alprocnr preserving type ) as bvalt,
key cast ( '000000000000' as ckml_f_procnr preserving type ) as process,
key rep.curtp,
key rep.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (rep.lbkum) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( rep.meins as meins preserving type ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum (rep.salk3) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
rep.waers
}
where
rep.categ = 'EB'
group by
rep.curtp,
rep.rldnr,
rep.kalnr_mat,
rperiod.poper,
rperiod.bdatj,
rperiod.run_act,
rperiod.run_appl,
rperiod.jahrper_to,
rperiod.jahrper_from,
rep.meins,
rep.waers
-- read additional ending inventory caused by BAdI FCML4H_MODIFY_VALUES (vgart = 'MV') to AVR from MLDOC (single period AVR)
-- note 3089473
union all select from mldoc as doc
inner join mlrunlist as mlrun on doc.runref = mlrun.runref
and doc.kalnr = mlrun.kalnr
and doc.jahrper = mlrun.jahrper
and doc.runref <> ''
inner join fcml_runperavr_v as rperiod on mlrun.run_id = rperiod.run_id
and mlrun.jahrper = rperiod.jahrper_to
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper = ' '
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl as ckml_run_appl preserving type ) as RUN_APPL,
key cast ( 'EB' as ckml_categ preserving type ) as CATEG,
key cast ( ' ' as ck_ptyp_bvalt preserving type ) as PTYP,
key cast ( ' ' as ck_psart preserving type ) as PSART,
key cast ( '000000000000' as ck_kalnr preserving type ) as KALNR_PMAT,
key cast ( '000000000000' as ckml_alprocnr preserving type ) as bvalt,
key cast ( '000000000000' as ckml_f_procnr preserving type ) as process,
key doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum(doc.stval) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
where
doc.vgart = 'MV' and
doc.categ <> 'EB'
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
rperiod.runref,
rperiod.poper,
rperiod.bdatj,
rperiod.jahrper_to,
rperiod.run_act,
rperiod.run_appl,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
doc.meins,
doc.waers
-- read additional ending inventory caused by BAdI FCML4H_MODIFY_VALUES (vgart = 'MV') to AVR from MLDOC/MLDOCCS (multi period AVR)
-- note 3089473
union all select from mldoc as doc
inner join mlrunlist as mlrun on doc.runref = mlrun.runref
and doc.kalnr = mlrun.kalnr
and doc.jahrper <= mlrun.jahrper
and doc.jahrper >= mlrun.from_jahrper
and doc.runref <> ''
inner join fcml_runperavr_v as rperiod on mlrun.run_id = rperiod.run_id
and mlrun.jahrper = rperiod.jahrper_to
and mlrun.from_jahrper = rperiod.jahrper_from
and ( rperiod.run_appl = 'CUM'
or rperiod.run_appl = 'PACR' )
and rperiod.x_multiper = 'X'
{
key cast ( doc.kalnr as ck_kalnr preserving type ) as kalnr_mat,
key cast ( rperiod.poper as poper preserving type ) as poper,
key cast ( rperiod.bdatj as bdatj preserving type ) as BDATJ,
key cast ( rperiod.run_act as ckml_run_type preserving type ) as RUN_ACT,
key cast ( rperiod.run_appl as ckml_run_appl preserving type ) as RUN_APPL,
key cast ( 'EB' as ckml_categ preserving type ) as CATEG,
key cast ( ' ' as ck_ptyp_bvalt preserving type ) as PTYP,
key cast ( ' ' as ck_psart preserving type ) as PSART,
key cast ( '000000000000' as ck_kalnr preserving type ) as KALNR_PMAT,
key cast ( '000000000000' as ckml_alprocnr preserving type ) as bvalt,
key cast ( '000000000000' as ckml_f_procnr preserving type ) as process,
key doc.curtp,
key doc.rldnr,
cast( rperiod.jahrper_to as jahrper) as jahrper,
@Semantics.quantity.unitOfMeasure: 'meins'
cast ( sum (doc.quant) as ck_lbkum ) as lbkum,
@Semantics.unitOfMeasure
cast ( doc.meins as meins ) as meins,
@Semantics.amount.currencyCode: 'waers'
cast ( sum(doc.stval) as ck_salk3_1 ) as salk3,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estprd ) as estprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_estkdm ) as estkdm,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstprd ) as mstprd,
@Semantics.amount.currencyCode: 'waers'
cast ( 0 as ckml_mstkdm ) as mstkdm,
@Semantics.currencyCode
doc.waers
}
where
doc.vgart = 'MV' and
doc.categ <> 'EB'
group by
doc.curtp,
doc.rldnr,
doc.kalnr,
rperiod.runref,
rperiod.poper,
rperiod.bdatj,
rperiod.jahrper_to,
rperiod.run_act,
rperiod.run_appl,
doc.categ,
doc.ptyp,
doc.posart,
doc.bvalt,
doc.process,
doc.pkalnr,
doc.meins,
doc.waers