ABAP-CDS采购订单收货视图

@AbapCatalog.sqlViewName: 'ZCDS_VIEW_PO01'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: '采购订单收货发票信息'
define view zcds_po001 as
select from ekbe as a
inner join ekpo as b on b.ebeln = a.ebeln and b.ebelp = a.ebelp
inner join matdoc as c on c.mblnr = a.belnr and c.mjahr = a.gjahr and c.zeile = a.buzei
inner join ekko as d on b.ebeln = d.ebeln
{
@EndUserText.label: '采购订单'
a.ebeln,
@EndUserText.label: '采购订单项目'
a.ebelp,
a.vgabe,
a.belnr,
a.gjahr,
a.buzei,
a.mwskz,

cast('B' as abap.char( 1 )) as ZBUSINESSTYPE,
--收货信息
@EndUserText.label: '收货数量'
( case when a.shkzg = 'H' then a.menge * -1 else a.menge end ) as m_menge1,
@EndUserText.label: '收货金额'
( case when a.shkzg = 'H' then a.wrbtr * -1 else a.wrbtr end ) as m_wrbtr1,
@EndUserText.label: '运费收货数量'
cast( 0 as abap.quan( 13,3 )) as m_menge2,
@EndUserText.label: '运费收货金额'
cast( 0 as abap.curr( 13,2 )) as m_wrbtr2,
@EndUserText.label: '收货货币'
a.waers as m_waers,
@EndUserText.label: '收货计量单位'
b.meins as m_meins ,
@EndUserText.label: '收货过账日期'
cast(a.budat as abap.dats( 8 )) as m_budat,
--发票信息
@EndUserText.label: '发票参考收货凭证年度'
a.lfgja,
@EndUserText.label: '发票参考收货凭证'
a.lfbnr,
@EndUserText.label: '发票参考收货凭证行项目'
a.lfpos,
d.lifnr,
d.lifnr as lifnr_y, //承运商
cast('PBXX' as abap.char( 4 )) as KSCHL,
@EndUserText.label: '发票数量'
cast( 0 as abap.quan( 13,3 )) as r_menge1 ,
@EndUserText.label: '发票金额'
cast( 0 as abap.curr( 13,2 )) as r_wrbtr1 ,
@EndUserText.label: '发票金额'
cast( 0 as abap.curr( 13,2 )) as r_wrbtr ,//货物含税价格
@EndUserText.label: '运费发票数量'
cast( 0 as abap.quan( 13,3 )) as r_menge2 ,
@EndUserText.label: '运费发票金额'
cast( 0 as abap.curr( 13,2 )) as r_wrbtr2 ,
@EndUserText.label: '运费货币'
cast('' as abap.cuky( 5 )) as r_waers ,
@EndUserText.label: '运费计量单位'
cast('' as abap.unit( 3 )) as r_meins ,
@EndUserText.label: '运费过账日期'
cast('' as abap.dats( 8 )) as r_budat ,
@EndUserText.label: '是否冲销'
// ( case when c.cancelled = 'X' or c.cancellation_type <> '' then
// ( case when c.cancelled = 'X' then
( case when c.cancelled = 'X' or c.cancellation_type = '2' then
'X'
else
''
end ) as CANCELLED

} where vgabe = '1'

union all
---运费收货指标
select from ekbz as a
inner join ekpo as b on b.ebeln = a.ebeln and b.ebelp = a.ebelp
inner join matdoc as c on c.mblnr = a.belnr and c.mjahr = a.gjahr and c.zeile = a.buzei
inner join ekko as d on b.ebeln = d.ebeln
//inner join rseg as d on a.gjahr = d.gjahr and a.belnr = d.belnr // and a.buzei = d.buzei
{
a.ebeln,
a.ebelp,
a.vgabe,
a.belnr,
a.gjahr,
a.buzei,
b.mwskz,
cast('A' as abap.char( 1 )) as ZBUSINESSTYPE,

--收货信息
cast( 0 as abap.quan( 13,3 )) as m_menge1,
cast( 0 as abap.curr( 13,2 )) as m_wrbtr1,
( case when a.shkzg = 'S' then a.menge * -1 else a.menge end ) as m_menge2,
( case when a.shkzg = 'S' then a.wrbtr * -1 else a.wrbtr end ) as m_wrbtr2,
a.waers as m_waers,
b.meins as m_meins ,
cast(a.budat as abap.dats( 8 )) as m_budat,
--发票信息
a.gjahr as lfgja,
a.belnr as lfbnr,
a.buzei as lfpos,
d.lifnr,
a.lifnr as lifnr_y,
a.kschl,
cast( 0 as abap.quan( 13,3 )) as r_menge1 ,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr1 ,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr ,//货物含税价格
cast( 0 as abap.quan( 13,3 )) as r_menge2 ,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr2 ,
cast('' as abap.cuky( 5 )) as r_waers ,
cast('' as abap.unit( 3 )) as r_meins ,
cast('' as abap.dats( 8 )) as r_budat ,
// ( case when c.cancelled = 'X' or c.cancellation_type <> '' then
// ( case when c.cancelled = 'X' then
( case when c.cancelled = 'X' or c.cancellation_type = '2' then
'X'
else
''
end ) as CANCELLED
} where vgabe = '1'

union all
---发票指标
select from ekbe as a
inner join ekpo on ekpo.ebeln = a.ebeln and ekpo.ebelp = a.ebelp
inner join rbkp on rbkp.belnr = a.belnr and rbkp.gjahr = a.gjahr
inner join ekko on ekpo.ebeln = ekko.ebeln
{
a.ebeln,
a.ebelp,
a.vgabe,
a.belnr,
a.gjahr,
a.buzei,
a.mwskz,
cast('B' as abap.char( 1 )) as ZBUSINESSTYPE,
--收货信息
cast( 0 as abap.quan( 13,3 )) as m_menge1,
cast( 0 as abap.curr( 13,2 )) as m_wrbtr1,
cast( 0 as abap.quan( 13,3 )) as m_menge2,
cast( 0 as abap.curr( 13,2 )) as m_wrbtr2,
cast('' as abap.cuky( 5 )) as m_waers,
cast('' as abap.unit( 3 )) as m_meins,
cast('00000000' as abap.dats( 8 )) as m_budat,
--发票信息
a.lfgja,
a.lfbnr,
a.lfpos,
ekko.lifnr,
ekko.lifnr as lifnr_y,
cast('PBXX' as abap.char( 4 )) as KSCHL,
( case when a.shkzg = 'H' then a.menge * -1 else a.menge end ) as r_menge1,
( case when a.shkzg = 'H' then a.wrbtr * -1 else a.wrbtr end ) as r_wrbtr1,
//cast( 0 as abap.curr( 13,2 )) as r_wrbtr ,//货物含税价格
( case
when a.mwskz = 'J0' then ( a.wrbtr * 0 )
when a.mwskz = 'J1' and a.shkzg = 'H' then ( a.wrbtr * 16 * -1 )
when a.mwskz = 'J1' and a.shkzg = 'S' then ( a.wrbtr * 16 )
when a.mwskz = 'J2' and a.shkzg = 'H' then ( a.wrbtr * 12 * -1 )
when a.mwskz = 'J2' and a.shkzg = 'S' then ( a.wrbtr * 12 )
when a.mwskz = 'J3' and a.shkzg = 'H' then ( a.wrbtr * 10 * -1 )
when a.mwskz = 'J3' and a.shkzg = 'S' then ( a.wrbtr * 10 )
when a.mwskz = 'J4' and a.shkzg = 'H' then ( a.wrbtr * 6 * -1 )
when a.mwskz = 'J4' and a.shkzg = 'S' then ( a.wrbtr * 6 )
when a.mwskz = 'J5' and a.shkzg = 'H' then ( a.wrbtr * 5 * -1 )
when a.mwskz = 'J5' and a.shkzg = 'S' then ( a.wrbtr * 5 )
when a.mwskz = 'J6' and a.shkzg = 'H' then ( a.wrbtr * 4 * -1 )
when a.mwskz = 'J6' and a.shkzg = 'S' then ( a.wrbtr * 4 )
when a.mwskz = 'J7' and a.shkzg = 'H' then ( a.wrbtr * 3 * -1 )
when a.mwskz = 'J7' and a.shkzg = 'S' then ( a.wrbtr * 3 )
when a.mwskz = 'J8' and a.shkzg = 'H' then ( a.wrbtr * 17 * -1 )
when a.mwskz = 'J8' and a.shkzg = 'S' then ( a.wrbtr * 17 )
when a.mwskz = 'J9' and a.shkzg = 'H' then ( a.wrbtr * 11 * -1 )
when a.mwskz = 'J9' and a.shkzg = 'S' then ( a.wrbtr * 11 )
when a.mwskz = 'JA' and a.shkzg = 'H' then ( a.wrbtr * 13 * -1 )
when a.mwskz = 'JA' and a.shkzg = 'S' then ( a.wrbtr * 13 )
when a.mwskz = 'JB' and a.shkzg = 'H' then ( a.wrbtr * 9 * -1 )
when a.mwskz = 'JB' and a.shkzg = 'S' then ( a.wrbtr * 9 )
when a.mwskz = 'JC' and a.shkzg = 'H' then ( a.wrbtr * 1 * -1 )
when a.mwskz = 'JC' and a.shkzg = 'S' then ( a.wrbtr * 1 )
end )
as r_wrbtr,

cast( 0 as abap.quan( 13,3 )) as r_menge2,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr2,
a.waers as r_waers,
ekpo.meins as r_meins,
cast(a.budat as abap.dats( 8 )) as r_budat,
( case when rbkp.stblg <> '' then
'X'
else
''
end
) as CANCELLED
} where ( vgabe = 'P' or vgabe = '2' )

union all
select from ekbz as a
inner join ekpo on ekpo.ebeln = a.ebeln and ekpo.ebelp = a.ebelp
inner join rbkp on rbkp.belnr = a.belnr and rbkp.gjahr = a.gjahr
inner join ekko on ekpo.ebeln = ekko.ebeln
{
a.ebeln,
a.ebelp,
a.vgabe,
a.belnr,
a.gjahr,
a.buzei,
ekpo.mwskz,
cast('A' as abap.char( 1 )) as ZBUSINESSTYPE,
--收货信息
cast( 0 as abap.quan( 13,3 )) as m_menge1,
cast( 0 as abap.curr( 13,2 )) as m_wrbtr1,
cast( 0 as abap.quan( 13,3 )) as m_menge2,
cast( 0 as abap.curr( 13,2 )) as m_wrbtr2,
cast('' as abap.cuky( 5 )) as m_waers,
cast('' as abap.unit( 3 )) as m_meins,
cast('00000000' as abap.dats( 8 )) as m_budat,
--发票信息
a.gjahr as lfgja,
a.belnr as lfbnr,
a.buzei as lfpos,
ekko.lifnr,
a.lifnr as lifnr_y,
a.kschl,
cast( 0 as abap.quan( 13,3 )) as r_menge1,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr1,
cast( 0 as abap.curr( 13,2 )) as r_wrbtr ,//货物含税价格
( case when a.shkzg = 'H' then a.menge * -1 else a.menge end ) as r_menge2,
( case when a.shkzg = 'H' then a.wrbtr * -1 else a.wrbtr end ) as r_wrbtr2,
a.waers as r_waers,
ekpo.meins as r_meins,
cast(a.budat as abap.dats( 8 )) as r_budat,
( case when rbkp.stblg <> '' then
'X'
else
''
end
) as CANCELLED
} where ( vgabe = 'P' or vgabe = '2' )