P_PurchaseOrderCommitment

@AbapCatalog.sqlViewName: 'PPURORDCOMMIT'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_ALLOWED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@VDM.private: true
@VDM.viewType: #COMPOSITE

define view P_PurchaseOrderCommitment
  as select from I_GLAccountLineItem as GLAItem

    inner join   R_PurchaseOrderItem as PurchaseOrderItem on GLAItem.PurchasingDocument     = PurchaseOrderItem.PurchaseOrder
                                                         and GLAItem.PurchasingDocumentItem = PurchaseOrderItem.PurchaseOrderItem
    association[0..1] to I_PredictionLedger as _PredictionLedger on $projection.Ledger = _PredictionLedger.PredictionLedger

  -- :* association to the Account Assignment child node
  //  association [1..1] to I_PurOrdAccountAssignment       as _PurOrdAcctAssignment        on  _PurOrdAcctAssignment.PurchaseOrder              = $projection.PurchasingDocument

  //                                                                                        and _PurOrdAcctAssignment.PurchaseOrderItem          = $projection.PurchasingDocumentItem

  //                                                                                        and _PurOrdAcctAssignment.AccountAssignmentNumber    = $projection.AccountAssignmentNumber

{

  key GLAItem.PurchasingDocument,
  key GLAItem.PurchasingDocumentItem,
  key GLAItem.AccountAssignmentNumber,
  key GLAItem.SourceReferenceDocSubitem,
  key GLAItem.GLAccount,
  key GLAItem.ControllingArea,
  key GLAItem.FunctionalArea,
  key GLAItem.CompanyCode,
  key GLAItem.FiscalYearPeriod,
  key GLAItem.CashLedgerAccount,
  key GLAItem.CashLedgerCompanyCode,
  key GLAItem.Fund,
  key GLAItem.GrantID,
  key GLAItem.BudgetPeriod,
  key GLAItem.PubSecBudgetAccount,
  key GLAItem.PubSecBudgetAccountCoCode,
  key GLAItem.PubSecBudgetIsRelevant,
  key GLAItem.PubSecBudgetCnsmpnType,
  key GLAItem.CostCenter,
  key GLAItem.ProfitCenter,
  key GLAItem.Segment,
  key GLAItem.WBSElementInternalID,
  key GLAItem.WBSElement,
  key GLAItem.AccountAssignmentType,
  key GLAItem.AccountAssignment,
  key GLAItem.SponsoredProgram,
  key GLAItem.SponsoredClass,
  key PurchaseOrderItem.Plant,
  key PurchaseOrderItem.PurchaseOrderType,
  key PurchaseOrderItem.PurchasingOrganization,
  key PurchaseOrderItem.PurchasingGroup,
  key PurchaseOrderItem.PurchaseOrderItemText,
  

      GLAItem.Ledger,
      GLAItem.IsCommitment,

      // ------------------AGGREGATIONS----------------------


      // aggregation of amounts in transaction currency is commited amount

      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      sum (
       GLAItem.AmountInTransactionCurrency
      ) as AmountInTransactionCurrency,

      //// aggregation of positive amounts (created by PO) in transaction currency is planned amount

      //      @DefaultAggregation: #SUM

      //      @Semantics.amount.currencyCode: 'TransactionCurrency'

      //     sum (

      //      case

      //          when ( AmountInTransactionCurrency < 0 ) // ignore negative amounts

      //              then

      //                  0

      //              else

      //                  AmountInTransactionCurrency end

      //      ) as PlannedAmountInTransactnCrncy,


      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      sum (
        GLAItem.AmountInCompanyCodeCurrency
      ) as AmountInCompanyCodeCurrency,


      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'GlobalCurrency'
      sum (
        GLAItem.AmountInGlobalCurrency
      ) as AmountInGlobalCurrency,

      //------------------------------------------------------


      //  @Semantics: { unitOfMeasure: true }

      //  BaseUnit,

      //  @Semantics: { quantity.unitOfMeasure: 'BaseUnit' }

      //  _PurOrdAcctAssignment.Quantity            as  PlannedQuantity,



      @Semantics.currencyCode: true
      GLAItem.TransactionCurrency,
      @Semantics.currencyCode: true
      GLAItem.CompanyCodeCurrency,
      @Semantics.currencyCode: true
      GLAItem.GlobalCurrency,
      
      GLAItem.FinancialManagementArea,

      GLAItem._FunctionalArea,
//      GLAItem._GLAccountInChartOfAccounts,

      GLAItem._ControllingArea,

      GLAItem._CompanyCodeText ,


//GLAItem._CashLedgerAccount,

     GLAItem._FundText,
     GLAItem._Grant,
     GLAItem._BudgetPeriod,
     GLAItem._PubSecBudgetAccount,
     GLAItem._PubSecBudgetAccountCoCode,
     GLAItem._BudgetPeriodText,
     GLAItem. _CashLedgerAccount,
     GLAItem._PubSecBudgetCnsmpnType,
     GLAItem._CostCenterText ,
     GLAItem._ProfitCenterText,
     GLAItem._SegmentText,
     GLAItem._WBSElementBasicDataText,
     GLAItem._WBSElement,
     GLAItem._AccountAssignmentType,
     GLAItem._SponsoredProgram,
     GLAItem._SponsoredClass

}
where
      GLAItem.SourceReferenceDocumentType = 'PORD'
  and GLAItem.IsCommitment                = 'X'
  and _PredictionLedger.LedgerIsRelevantForAvailyCtrl = 'X'

group by
  GLAItem.PurchasingDocument,
  GLAItem.PurchasingDocumentItem,
  GLAItem.AccountAssignmentNumber,
  GLAItem.SourceReferenceDocSubitem,
  GLAItem.IsCommitment,
  GLAItem.GLAccount,
  GLAItem.ControllingArea,
  GLAItem.FunctionalArea,
  GLAItem.TransactionCurrency,
  GLAItem.CompanyCodeCurrency,
  GLAItem.GlobalCurrency,
  GLAItem.ControllingObjectCurrency,
  GLAItem.SourceLedger,
  GLAItem.CompanyCode,
  GLAItem.FiscalYearPeriod,
  GLAItem.FiscalYear,
  GLAItem.Ledger,
  GLAItem.CashLedgerAccount,
  GLAItem.CashLedgerCompanyCode,
  GLAItem.Fund,
  GLAItem.GrantID,
  GLAItem.BudgetPeriod,
  GLAItem.PubSecBudgetAccount,
  GLAItem.PubSecBudgetAccountCoCode,
  GLAItem.PubSecBudgetIsRelevant,
  GLAItem.PubSecBudgetCnsmpnType,
  GLAItem.CostCenter,
  GLAItem.ProfitCenter,
  GLAItem.Segment,
  GLAItem.WBSElementInternalID,
  GLAItem.WBSElement,
  GLAItem.AccountAssignmentType,
  GLAItem.AccountAssignment,
  GLAItem.SponsoredProgram,
  GLAItem.SponsoredClass,
  GLAItem.FinancialManagementArea,
  PurchaseOrderItem.Plant,
  PurchaseOrderItem.PurchaseOrderType,
  PurchaseOrderItem.PurchasingOrganization,
  PurchaseOrderItem.PurchasingGroup,
  PurchaseOrderItem.PurchaseOrderItemText