@AbapCatalog.sqlViewName: 'PVECSTDD_01'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
//@AccessControl.authorizationCheck: #NOT_ALLOWED
//@EndUserText.label: 'Get Standardized Data per Year'
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view P_VECStandardizedData_01
with parameters
p_rdimen : fc_dimen,
p_ritclg : fc_itclg,
p_rvers : fc_rvers,
p_rldnr : rldnr,
p_ryear : gjahr,
p_poper : poper
as select from
// P_StandardizedData
//{
// ryear,
// 'LC' as currency_type,
// @Semantics.currencyCode
// rhcur as cons_cur,
// docct,
// rdimen,
// rcomp,
// rbunit as entity,
// ritclg,
// ritem,
// rbuptr,
// sityp,
// subit,
//
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '000' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_slvt,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '001' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_sl01,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '002' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL02,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '003' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL03,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '004' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL04,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '005' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL05,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '006' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL06,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '007' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL07,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '008' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL08,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '009' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL09,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '010' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL10,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '011' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL11,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '012' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL12,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '013' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL13,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '014' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL14,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '015' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL15,
// @Semantics.amount.currencyCode: 'CONS_CUR'
// sum( case
// when poper = '016' then cast(hsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as cons_SL16,
//
// @Semantics.currencyCode
// rtcur,
// @Semantics.amount.currencyCode: 'RTCUR'
// sum( case
// when poper = :p_poper then cast(tsl as abap.dec( 23, 2 ))
// else cast(0 as abap.dec( 23, 2 ))
// end ) as tsl,
//
// rrcty,
// plevl,
// docty,
// yracq,
// pracq,
// coicu,
// autom,
// refdocct,
// coiac,
// coinr,
// ktopl,
// racct,
// @Semantics.unitOfMeasure: true
// runit,
// @Semantics.quantity.unitOfMeasure: 'RUNIT'
// sum( case
// when poper = :p_poper then msl
// else cast(0 as quan1_12) end ) as msl,
// rcntr,
// prctr,
// rfarea,
// rbusa,
// kokrs,
// segment,
// scntr,
// pprctr,
// sfarea,
// sbusa,
// rassc,
// psegment,
// // aufnr,
// // kunnr,
// // lifnr,
// // matnr,
// matkl_mm,
// werks,
// rmvct,
// // ps_psp_pnr,
// ps_posid,
// ps_pspid,
//
// fkart,
// vkorg,
// vtweg,
// spart,
// // matnr_copa,
// matkl,
// kdgrp,
// land1,
// brsch,
// bzirk,
// // kunre,
// // kunwe,
// konzs,
// timestamp
//}
//where
// rdimen = :p_rdimen
// and ritclg = :p_ritclg
// and rvers = :p_rvers
// and rldnr = :p_rldnr
// and ryear = :p_ryear
// and poper <= :p_poper
//group by
// ryear,
// rhcur,
// rdimen,
// docct,
// rcomp,
// rbunit,
// ritclg,
// ritem,
// rbuptr,
// sityp,
// subit,
// rrcty,
// plevl,
// docty,
// yracq,
// pracq,
// coicu,
// autom,
// refdocct,
// coiac,
// coinr,
// ktopl,
// racct,
// runit,
// rtcur,
// rcntr,
// prctr,
// rfarea,
// rbusa,
// kokrs,
// segment,
// scntr,
// pprctr,
// sfarea,
// sbusa,
// rassc,
// psegment,
// // aufnr,
// // kunnr,
// // lifnr,
// // matnr,
// matkl_mm,
// werks,
// rmvct,
// // ps_psp_pnr,
// ps_posid,
// ps_pspid,
// fkart,
// vkorg,
// vtweg,
// spart,
// // matnr_copa,
// matkl,
// kdgrp,
// land1,
// brsch,
// bzirk,
// // kunre,
// // kunwe,
// konzs,
// timestamp
//union all select from
P_StandardizedData
{
ryear,
'GC' as currency_type,
@Semantics.currencyCode
rkcur as cons_cur,
docct,
rdimen,
rcomp,
rbunit as entity,
ritclg,
ritem,
rbuptr,
sityp,
subit,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '000' then ksl else 0 end ) as cons_slvt,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '001' then ksl else 0 end ) as cons_sl01,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '002' then ksl else 0 end ) as cons_sl02,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '003' then ksl else 0 end ) as cons_sl03,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '004' then ksl else 0 end ) as cons_sl04,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '005' then ksl else 0 end ) as cons_sl05,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '006' then ksl else 0 end ) as cons_sl06,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '007' then ksl else 0 end ) as cons_sl07,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '008' then ksl else 0 end ) as cons_sl08,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '009' then ksl else 0 end ) as cons_sl09,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '010' then ksl else 0 end ) as cons_sl10,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '011' then ksl else 0 end ) as cons_sl11,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '012' then ksl else 0 end ) as cons_sl12,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '013' then ksl else 0 end ) as cons_sl13,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '014' then ksl else 0 end ) as cons_sl14,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '015' then ksl else 0 end ) as cons_sl15,
@Semantics.amount.currencyCode: 'CONS_CUR'
sum( case when poper = '016' then ksl else 0 end ) as cons_sl16,
@Semantics.currencyCode
rtcur,
@Semantics.amount.currencyCode: 'RTCUR'
sum( case when poper = :p_poper then tsl else 0 end ) as tsl,
rrcty,
plevl,
docty,
yracq,
pracq,
coicu,
autom,
refdocct,
coiac,
coinr,
ktopl,
racct,
@Semantics.unitOfMeasure: true
runit,
@Semantics.quantity.unitOfMeasure: 'RUNIT'
sum( case when poper = :p_poper then msl else 0 end ) as msl,
rcntr,
prctr,
rfarea,
rbusa,
kokrs,
segment,
scntr,
pprctr,
sfarea,
sbusa,
rassc,
psegment,
// aufnr,
// kunnr,
// lifnr,
// matnr,
matkl_mm,
werks,
rmvct,
// ps_psp_pnr,
ps_posid,
ps_pspid,
fkart,
vkorg,
vtweg,
spart,
// matnr_copa,
matkl,
kdgrp,
land1,
brsch,
bzirk,
// kunre,
// kunwe,
konzs,
timestamp,
robukrs
}
where
rdimen = :p_rdimen
and ritclg = :p_ritclg
and rvers = :p_rvers
and rldnr = :p_rldnr
and ryear = :p_ryear
and poper <= :p_poper
group by
ryear,
rkcur,
rdimen,
docct,
rcomp,
rbunit,
ritclg,
ritem,
rbuptr,
sityp,
subit,
rrcty,
plevl,
docty,
yracq,
pracq,
coicu,
autom,
refdocct,
coiac,
coinr,
ktopl,
racct,
runit,
rtcur,
rcntr,
prctr,
rfarea,
rbusa,
kokrs,
segment,
scntr,
pprctr,
sfarea,
sbusa,
rassc,
psegment,
// aufnr,
// kunnr,
// lifnr,
// matnr,
matkl_mm,
werks,
rmvct,
// ps_psp_pnr,
ps_posid,
ps_pspid,
fkart,
vkorg,
vtweg,
spart,
// matnr_copa,
matkl,
kdgrp,
land1,
brsch,
bzirk,
// kunre,
// kunwe,
konzs,
timestamp,
robukrs