P_GMAVCDataUser

@AbapCatalog.sqlViewName: 'PGMAVCDATAUSER'
//@ClientDependent: true

@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin

@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality:  #P
@ObjectModel.usageType.sizeCategory: #L //Inserted by VDM CDS Suite Plugin

@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private:true
//@EndUserText.label: 'Personalized GM AVC data'

define view P_GMAVCDataUser
  with parameters
    @Environment.systemField : #USER
    P_uname: syuname,
    P_CompanyCodeCurrency: vdm_v_company_code_currency,
    @Consumption.defaultValue: '9K'
    P_AvailabilityControlLedger: buavc_aldnr
  
  as select from P_GMAVCT_Compat as AVC 
    inner join t001 as CC on CC.waers = :P_CompanyCodeCurrency
  
    inner join gmgr as MD on  MD.grant_nbr    = AVC.rgrant_nbr
                          and MD.company_code = CC.bukrs
                     
// Personalization (FPB application), for Grant Type and Sponsor selection:                          

    left outer join   fpb_persparm    as p1  on  p1.perskeytp       = 'U'
                                             and p1.perskey         = :P_uname
                                             and p1.applid          = 'GM-MY_GRANTS'
                                             and p1.fieldname       = 'GRANT_TYPE'
                                             and p1.pers_subcontext = ''
                                             and p1.noauth          = ' '
                                             and p1.sel_sign        = 'I'
                                             and p1.fieldtype       = 'V'
                                             and p1.sel_option      = 'EQ'

    left outer join   fpb_persparm    as p2  on  p2.perskeytp       = 'U'
                                             and p2.perskey         = :P_uname
                                             and p2.applid          = 'GM-MY_GRANTS'
                                             and p2.fieldname       = 'SPONSOR'
                                             and p2.pers_subcontext = ''
                                             and p2.noauth          = ' '
                                             and p2.sel_sign        = 'I'
                                             and p2.fieldtype       = 'V'
                                             and p2.sel_option      = 'EQ'

// ToDo: Personalization via responsibility in Grant Master...


{
  key rbukrs,
  key rrcty,
  key rldnr,
  key ryear,        //Sponsor fiscal year

  key grantee_fy_9, //Company Code fiscal year

  key ceffyear_9,   //not used

  key rgrant_nbr,
  key rfund,
  key rsponsored_prog,
  key rsponsored_class,
  key rbdgt_vldty_nbr,
  key alloctype_9,
  key wfstate_9,
  key rtcur, //Grant currency (for TSL fields); HSL fields use Company Code currency


      case rrcty when '0'
           then cast(0 as abap.curr(24,2))
           else cast((hsl01 + hsl02 +
                      hsl03 + hsl04 +
                      hsl05 + hsl06 +
                      hsl07 + hsl08 +
                      hsl09 + hsl10 +
                      hsl11 + hsl12 +
                      hsl13 + hsl14 +
                      hsl15 + hsl16) as abap.curr(24,2))
           end  as HSLAmount1,

      case rrcty when '0'
           then cast((hsl01 + hsl02 +
                      hsl03 + hsl04 +
                      hsl05 + hsl06 +
                      hsl07 + hsl08 +
                      hsl09 + hsl10 +
                      hsl11 + hsl12 +
                      hsl13 + hsl14 +
                      hsl15 + hsl16) as abap.curr(24,2))
           else cast(0 as abap.curr(24,2))
           end  as HSLAmount0,

      case rrcty when '0'
           then cast(0 as abap.curr(24,2))
           else cast((tsl01 + tsl02 +
                      tsl03 + tsl04 +
                      tsl05 + tsl06 +
                      tsl07 + tsl08 +
                      tsl09 + tsl10 +
                      tsl11 + tsl12 +
                      tsl13 + tsl14 +
                      tsl15 + tsl16) as abap.curr(24,2))
           end  as TSLAmount1,

      case rrcty when '0'
           then cast((tsl01 + tsl02 +
                      tsl03 + tsl04 +
                      tsl05 + tsl06 +
                      tsl07 + tsl08 +
                      tsl09 + tsl10 +
                      tsl11 + tsl12 +
                      tsl13 + tsl14 +
                      tsl15 + tsl16) as abap.curr(24,2))
           else cast(0 as abap.curr(24,2))
           end  as TSLAmount0

}

where rldnr  = :P_AvailabilityControlLedger 
  and rbukrs = CC.bukrs
  and
      (
      AVC.wfstate_9 = 'P' or
      AVC.wfstate_9 = 'R'
      )
  and (
      p1.low        is null or
      p1.low        = '*' or
      MD.grant_type = p1.low
      )
  and (
      p2.low     is null or 
      p2.low     = '*' or
      MD.partner = p2.low
      )