P_RepFinData_RT_Aggr_01

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PRFDRTAG_01'
@AbapCatalog.compiler.compareFilter: true

@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
//@AccessControl.authorizationCheck: #NOT_ALLOWED

@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view P_RepFinData_RT_Aggr_01
  with parameters
    P_CreationDateTimeRgFr :timestamp,
    P_CreationDateTimeRgTo :timestamp

  as select from I_JournalEntryItem as Source //acdoca


  //  Aggregate over line item fields like username, timestamp, etc.


{

  // mandatory standard fields

  Source.PartnerCompany,
  Source.BalanceTransactionCurrency,
  Source.BaseUnit,
  Source.FinancialTransactionType,
  Source.FunctionalArea,
  Source.BusinessArea,
  Source.CostCenter,
  Source.GLAccount,
  Source.ProfitCenter,
  sum(Source.AmountInBalanceTransacCrcy)                  as AmountInTransactionCurrency,
  sum(Source.AmountInCompanyCodeCurrency)                 as AmountInLocalCurrency,
  sum(Source.AmountInGlobalCurrency)                      as AmountInGroupCurrency,
  cast(sum(Source.Quantity) as quan1_12 preserving type ) as QuantityInBaseUnit,
  sum(Source.AmountInFreeDefinedCurrency1)                as osl,
  sum(Source.AmountInFreeDefinedCurrency2)                as vsl,
  sum(Source.AmountInFreeDefinedCurrency3)                as bsl,
  sum(Source.AmountInFreeDefinedCurrency4)                as csl,
  sum(Source.AmountInFreeDefinedCurrency5)                as dsl,
  sum(Source.AmountInFreeDefinedCurrency6)                as esl,
  sum(Source.AmountInFreeDefinedCurrency7)                as fsl,
  sum(Source.AmountInFreeDefinedCurrency8)                as gsl,
  Source.Segment,

  // additional fields

  Source.CompanyCode,
  Source.ChartOfAccounts,
  Source.AssignmentReference,
  Source.ControllingArea,
  Source.PartnerCostCenter,
  Source.PartnerProfitCenter,
  Source.PartnerFunctionalArea,
  Source.PartnerBusinessArea,
  Source.PartnerSegment,
  Source.OrderID,
  Source.Customer,
  Source.Supplier,
  Source.Material,
  Source.MaterialGroup,
  Source.ProductGroup,
  Source.Plant,
  Source.WBSElement                                       as WBSElementExternalID,
  Source.Project,
  Source.BillingDocumentType,
  Source.SalesOrganization,
  Source.DistributionChannel,
  Source.OrganizationDivision,
  Source.SoldProduct,
  Source.SoldProductGroup,
  Source.CustomerGroup,
  Source.CustomerSupplierCountry,
  Source.CustomerSupplierIndustry,
  Source.SalesDistrict,
  Source.BillToParty,
  Source.ShipToParty,
  Source.CustomerSupplierCorporateGroup,
  Source.WBSElementInternalID,
  cast(0 as timestamp)                                    as CreationDateTime,

  // financial services (industry specific)

  Source.FinancialServicesProductGroup,
  Source.FinancialServicesBranch,
  Source.FinancialDataSource,

  // public sector (industry specific)

  Source.FinancialManagementArea,
  Source.GrantID,
  Source.Fund,
  Source.PartnerFund,
  Source.CashLedgerAccount,
  Source.CashLedgerCompanyCode,

  // specific fileds for this cds view

  Source.Ledger,
  Source.PostingDate,
  Source.FiscalYearPeriod

}

where
  (
       Source.AccountingDocumentCategory =       ''  //normal documents

    or Source.AccountingDocumentCategory =       'U' //documents without lines in BSEG

    or Source.AccountingDocumentCategory =       'L' //postings not in leading ledger, relevant in case an orientation ledger is used

    or Source.AccountingDocumentCategory =       'P' //predicted documents, posted in a separate extension ledger

    or Source.AccountingDocumentCategory =       'C' //balance Carryforward and Balance Adjustments from Migration

  )

  //  Balance carry forward is done differently in consolidation (e.g. shifted fiscal year)

  //  and must not be taken over from FI

  and  Source.FiscalPeriod               <>      '000'

  // Year End Closing postings are not relevant for consolidation

  and  Source.ReferenceDocumentType      <>      'GLYEC'

  and  Source.CreationDateTime           between :P_CreationDateTimeRgFr and :P_CreationDateTimeRgTo

group by
// mandatory standard fields

  Source.PartnerCompany,
  Source.BalanceTransactionCurrency,
  Source.BaseUnit,
  Source.FinancialTransactionType,
  Source.FunctionalArea,
  Source.BusinessArea,
  Source.CostCenter,
  Source.GLAccount,
  Source.ProfitCenter,
  Source.Segment,

  // additional fields

  Source.CompanyCode,
  Source.ChartOfAccounts,
  Source.AssignmentReference,
  Source.ControllingArea,
  Source.PartnerCostCenter,
  Source.PartnerProfitCenter,
  Source.PartnerFunctionalArea,
  Source.PartnerBusinessArea,
  Source.PartnerSegment,
  Source.OrderID,
  Source.Customer,
  Source.Supplier,
  Source.Material,
  Source.MaterialGroup,
  Source.ProductGroup,
  Source.Plant,
  Source.WBSElement,
  Source.Project,
  Source.BillingDocumentType,
  Source.SalesOrganization,
  Source.DistributionChannel,
  Source.OrganizationDivision,
  Source.SoldProduct,
  Source.SoldProductGroup,
  Source.CustomerGroup,
  Source.CustomerSupplierCountry,
  Source.CustomerSupplierIndustry,
  Source.SalesDistrict,
  Source.BillToParty,
  Source.ShipToParty,
  Source.CustomerSupplierCorporateGroup,
  Source.WBSElementInternalID,

  // financial services (industry specific)

  Source.FinancialServicesProductGroup,
  Source.FinancialServicesBranch,
  Source.FinancialDataSource,

  // public sector (industry specific)

  Source.FinancialManagementArea,
  Source.GrantID,
  Source.Fund,
  Source.PartnerFund,
  Source.CashLedgerAccount,
  Source.CashLedgerCompanyCode,

  // specific fileds for this cds view

  Source.Ledger,
  Source.PostingDate,
  Source.FiscalYearPeriod