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_Period : 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_Period, '01'
      )
    )

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

  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_Period                   as PPeriod,
       _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_Period 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     _CalendarDateByPostingDate.CalendarYear   = $parameters.P_Year
  and(
          GLAcctLineItem.PubSecBudgetCnsmpnType     = '56'
    and   _CalendarDateByPostingDate.CalendarMonth  > '00'
    and   _CalendarDateByPostingDate.CalendarMonth  <= $parameters.P_Period
    or(
          GLAcctLineItem.AccountingDocumentCategory = 'C'
      and GLAcctLineItem.FiscalPeriod               = '000'
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_GLACCOUNTLINEITEM",
"I_PUBSECINTRSTAPPRTMT",
"I_PUBSECPRTCPNTACCTS",
"I_YEARMONTH"
],
"ASSOCIATED":
[
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_CURRENCY",
"I_FINANCIALMANAGEMENTAREA",
"I_FUND",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_GRANT",
"I_PROFITCENTER",
"I_SEGMENT"
],
"BASE":
[
"I_GLACCOUNTLINEITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/