P_IntApportnmntBasic

@AbapCatalog.sqlViewName: 'PINTAPPRTMTBASIC'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #COMPOSITE
@VDM.private:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'PSM Interest Apportionment Query Basic'

@ObjectModel.usageType: {
  dataClass: #MASTER,
  serviceQuality: #D,
  sizeCategory: #S
}

define view P_IntApportnmntBasic

  with parameters
    P_PoolID : psm_pool_id,
    P_Year   : num4,
    P_Month  : num2

  as select from I_GLAccountLineItem           as GLAcctLineItem
    join         I_PubSecIntrstApprtmt         as _IntrstApprtmt             on _IntrstApprtmt.InvestmentPoolID = $parameters.P_PoolID

    inner join   I_PubSecPrtcpntAccts          as _PrtcpntAccts              on  _PrtcpntAccts.InvestmentPoolUUID = _IntrstApprtmt.InvestmentPoolUUID
                                                                             and GLAcctLineItem.GLAccount         = _PrtcpntAccts.InvestmentParticipantAccount

    join         I_CalendarDate                as _CalendarDateByPostingDate on GLAcctLineItem.PostingDate = _CalendarDateByPostingDate.CalendarDate

    join         I_CalendarDate                as _CalendarDate              on _CalendarDate.CalendarDate = concat(
      $parameters.P_Year, concat(
        $parameters.P_Month, '01'
      )
    )

    join         I_YearMonth                   as _CalendarYearMonth         on  _CalendarDate.CalendarYear  = _CalendarYearMonth.CalendarYear
                                                                             and _CalendarDate.CalendarMonth = _CalendarYearMonth.CalendarMonth

    inner join   I_FiscCalendarDateForCompCode as _FiscalData                on  _FiscalData.CompanyCode  = _IntrstApprtmt.CompanyCode
                                                                             and _FiscalData.CalendarDate = _CalendarDate.CalendarDate

  association [1..1] to I_ProfitCenter as _RealProfitCenter on  $projection.ControllingArea =  _RealProfitCenter.ControllingArea
                                                            and $projection.ProfitCenter    =  _RealProfitCenter.ProfitCenter
                                                            and $projection.PostingDate     >= _RealProfitCenter.ValidityStartDate
                                                            and $projection.PostingDate     <= _RealProfitCenter.ValidityEndDate

{

  key  GLAcctLineItem.CompanyCode             as CompanyCode,
  key  GLAcctLineItem.Fund                    as Fund,
  key  GLAcctLineItem.GrantID                 as GrantID,
       @ObjectModel.foreignKey.association: '_RealProfitCenter'
  key  GLAcctLineItem.ProfitCenter            as ProfitCenter,
  key  GLAcctLineItem.Segment                 as Segment,

       _IntrstApprtmt.InvestmentPooledFund    as InvestmentPooledFund,
       $parameters.P_Year                     as PYear,
       $parameters.P_Month                    as PMonth,
       GLAcctLineItem.FiscalYear              as FiscalYear,
       GLAcctLineItem.FiscalPeriod            as FiscalPeriod,
       _PrtcpntAccts.InvestmentPoolUUID       as InvestmentPoolUUID,
       GLAcctLineItem.ChartOfAccounts         as ChartOfAccounts,
       GLAcctLineItem.FinancialManagementArea as FinancialManagementArea,
       GLAcctLineItem.ControllingArea         as ControllingArea,
       GLAcctLineItem.GLAccount               as InvestmentParticipantAccount,
       GLAcctLineItem.PostingDate             as PostingDate,

       case
        when ( _CalendarDateByPostingDate.CalendarMonth = $parameters.P_Month and GLAcctLineItem.AccountingDocumentCategory <> 'C' )
        then _CalendarDateByPostingDate.CalendarDay
        else '00'
       end                                    as PoolDay,

       _CalendarYearMonth.NumberOfDays        as LastDay,

       @DefaultAggregation: #SUM
       @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
       GLAcctLineItem.AmountInCompanyCodeCurrency,
       @Semantics.currencyCode:true
       GLAcctLineItem.CompanyCodeCurrency,

       //_PoolID,

       //_CalendarYearMonth,

       GLAcctLineItem._ChartOfAccounts,
       GLAcctLineItem._CompanyCode,
       GLAcctLineItem._FinancialManagementArea,
       GLAcctLineItem._ControllingArea,
       GLAcctLineItem._Fund,
       GLAcctLineItem._Grant,
       _RealProfitCenter,
       GLAcctLineItem._Segment,
       GLAcctLineItem._GLAccountInChartOfAccounts,
       GLAcctLineItem._CompanyCodeCurrency

}

where
            GLAcctLineItem.CompanyCode                =  _IntrstApprtmt.CompanyCode
  and       GLAcctLineItem.Ledger                     =  '0L'
  and       GLAcctLineItem.GLAccount                  =  _PrtcpntAccts.InvestmentParticipantAccount
  and       GLAcctLineItem.FiscalYear                 =  _FiscalData.FiscalYear
  and(
    (
            GLAcctLineItem.PubSecBudgetCnsmpnType     =  '56'
      or    GLAcctLineItem.PubSecBudgetCnsmpnType     =  '59'
    )
    and(
      (
            GLAcctLineItem.FiscalPeriod               >  '000'
        and GLAcctLineItem.FiscalPeriod               <= _FiscalData.FiscalPeriod
      )
    )
    or(
            GLAcctLineItem.AccountingDocumentCategory =  'C'
      and   GLAcctLineItem.FiscalPeriod               =  '000'
      and   _FiscalData.FiscalPeriod                  =  '001'
    )
  )