@AbapCatalog.sqlViewName: 'FACV_LG_WLI_03'
@EndUserText.label: 'OPEN ITEMS'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
define view FACVD_GLLGMC_WLI_03
with parameters
to_budat :abap.dats
as select from skb1
left outer join bseg //postings with ledger group SPACE or leading ledger group
on skb1.bukrs = bseg.bukrs
and skb1.saknr = bseg.hkont
inner join bkpf on bkpf.bukrs = bseg.bukrs
and bkpf.belnr = bseg.belnr
and bkpf.gjahr = bseg.gjahr
left outer join fagl_tldgrp_map on fagl_tldgrp_map.ldgrp = bkpf.ldgrp
and fagl_tldgrp_map.represent = 'X'
{
key skb1.bukrs,
key bseg.hkont,
key bseg.h_hwaer as hwaer,
key bkpf.ldgrp,
//normal debit postings
case
when bseg.shkzg = 'S'
and bseg.xnegp = ''
then sum(bseg.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as debit,
//identify negative postings debit
case
when bseg.shkzg ='S'
and bseg.xnegp = 'X'
then sum(bseg.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as debit_xnegp,
//normal credit postings
case
when bseg.shkzg ='H'
and bseg.xnegp = ''
then sum(bseg.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as credit,
//identify negative postings credit
case
when bseg.shkzg ='H'
and bseg.xnegp = 'X'
then sum(bseg.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as credit_xnegp,
//representative ledger is used for authorization check
case
when fagl_tldgrp_map.rldnr is null
then ''
else fagl_tldgrp_map.rldnr
end as rldnr_auth,
count (*) as items_count,
bseg.h_blart as blart,
skb1.begru,
skb1.altkt
}
where
skb1.xlgclr = 'X' //clearing specific to ledger groups
and skb1.mitkz = ''
and skb1.xintb <> 'X'
and bseg.augdt = '00000000'
and bseg.koart = 'S'
and bseg.xopvw = ''
and bseg.h_bstat <> 'D'
and bseg.h_bstat <> 'M'
and bseg.h_bstat <> 'O'
and bseg.h_bstat <> 'J'
and bseg.awtyp <> 'GLYEC'
and bkpf.xstov <> 'X'
and h_budat <= $parameters.to_budat //filtered by the given date
group by
skb1.bukrs,
bseg.hkont,
bseg.h_hwaer,
bkpf.ldgrp,
bseg.xnegp,
fagl_tldgrp_map.rldnr,
bseg.h_blart,
bseg.shkzg,
skb1.begru,
skb1.altkt
union
select from skb1
left outer join bseg_add //postings with not leading ledger groups
on skb1.bukrs = bseg_add.bukrs
and skb1.saknr = bseg_add.hkont
inner join bkpf on bkpf.bukrs = bseg_add.bukrs
and bkpf.belnr = bseg_add.belnr
and bkpf.gjahr = bseg_add.gjahr
left outer join fagl_tldgrp_map on fagl_tldgrp_map.ldgrp = bkpf.ldgrp
and fagl_tldgrp_map.represent = 'X'
{
key skb1.bukrs,
key bseg_add.hkont,
key bkpf.hwaer as hwaer,
key bkpf.ldgrp,
//normal debit postings
case
when bseg_add.shkzg = 'S'
and bseg_add.xnegp = ''
then sum(bseg_add.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as debit,
//identify negative postings debit
case
when bseg_add.shkzg ='S'
and bseg_add.xnegp = 'X'
then sum(bseg_add.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as debit_xnegp,
//normal credit postings
case
when bseg_add.shkzg ='H'
and bseg_add.xnegp = ''
then sum(bseg_add.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as credit,
//identify negative postings credit
case
when bseg_add.shkzg ='H'
and bseg_add.xnegp = 'X'
then sum(bseg_add.dmbtr)
else cast ('0' as abap.curr( 23, 2))
end as credit_xnegp,
//representative ledger is used for authorization check
case
when fagl_tldgrp_map.rldnr is null
then ''
else fagl_tldgrp_map.rldnr
end as rldnr_auth,
count (*) as items_count,
bkpf.blart as blart,
skb1.begru,
skb1.altkt
}
where
skb1.xlgclr = 'X' //clearing specific to ledger groups
and skb1.mitkz = ''
and skb1.xintb <> 'X'
and bseg_add.augdt = '00000000'
and bseg_add.koart = 'S'
and bseg_add.xopvw = ''
and bkpf.bstat <> 'D'
and bkpf.bstat <> 'M'
and bkpf.bstat <> 'O'
and bkpf.bstat <> 'J'
and bkpf.awtyp <> 'GLYEC'
and bkpf.xstov <> 'X'
and bkpf.budat <= $parameters.to_budat //filtered by the given date
group by
skb1.bukrs,
bseg_add.hkont,
bkpf.hwaer,
bkpf.ldgrp,
bseg_add.xnegp,
fagl_tldgrp_map.rldnr,
bkpf.blart,
bseg_add.shkzg,
skb1.begru,
skb1.altkt
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"BKPF",
"BSEG",
"BSEG_ADD",
"FAGL_TLDGRP_MAP",
"SKB1"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/