FACVD_GLMC_WLI_03

@AbapCatalog.sqlViewName: 'FACV_GLMC_WLI_03'
@EndUserText.label: 'OPEN ITEMS'

@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P

//this view are collecting all data needed for the calculation by the cds views above.

define view FACVD_GLMC_WLI_03


  with parameters
    to_budat :abap.dats

  as select from    skb1 //get filter condition only for the where clause from the table


    left outer join bseg //retrieve the bookings

    on  skb1.bukrs = bseg.bukrs
    and skb1.saknr = bseg.hkont
    and skb1.x_uj_clr = ''

    left outer join bkpf
        on  bkpf.bukrs = bseg.bukrs
        and bkpf.gjahr = bseg.gjahr
        and bkpf.belnr = bseg.belnr
        
    left outer to one join t001
        on t001.bukrs = skb1.bukrs
                                
    left outer to one join ska1 //retrieve the gl account type

                                on  ska1.ktopl = t001.ktopl
                                and ska1.saknr = bseg.hkont

  //collect debit data

{
  key  skb1.bukrs,
  key  bseg.hkont,
  key  bseg.h_hwaer,  
       ska1.glaccount_type,
       t001.ktopl, 
       t001.butxt,  
       t001.ktop2,   

       //normal debit bookings

       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 bookings 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 bookings

       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 bookings 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,

       count (*)  as items_count,
       bseg.h_blart,
       skb1.begru,
       skb1.altkt

}
where
      ska1.glaccount_type <>  'C' //for cash accounts there is a separate worklist (view FACVD_GLCASHMC_WLI)

  and skb1.xlgclr  =  ''
  and skb1.mitkz   =  ''
  and skb1.xintb   <> 'X'
  and bseg.augdt   =  '00000000'
  and bseg.koart   =  'S'
  and bseg.xopvw   =  'X'
  and bseg.h_bstat <> 'D' //Recurring entry document

  and bseg.h_bstat <> 'M' //Sample document

  and bseg.h_bstat <> 'O' //Financial Statements: Open Entry

  and bseg.h_bstat <> 'J' //Closing Operations

  and bseg.awtyp   <> 'GLYEC' //Year-End Closing Doc

  and bkpf.xstov   <> 'X'
  and h_budat      <= $parameters.to_budat //filtered by the given the date


group by
  skb1.bukrs,
  bseg.hkont,
  bseg.h_hwaer,
  ska1.glaccount_type,
  t001.ktopl,
  t001.butxt,
  t001.ktop2,
  bseg.xnegp,
  bseg.h_blart,
  bseg.shkzg,
  skb1.begru,
  skb1.altkt



/*+[internal] {
"BASEINFO":
{
"FROM":
[
"BKPF",
"BSEG",
"SKA1",
"SKB1",
"T001"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/