P_RealTimeMfgOrdBalance

@AbapCatalog.sqlViewName: 'PRTMOBALANCE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
//@EndUserText.label: 'Event-based Order Relevant Actual Cost'

@VDM.viewType: #COMPOSITE
@VDM.private:true
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@AbapCatalog.preserveKey:true

define view P_RealTimeMfgOrdBalance
  with parameters
    P_FiscalPeriodStartDate    : budat,
    P_FiscalPeriodEndDate      : budat
  //as select from acdoca   as _item

  as select from I_GLAccountLineItemRawData   as _item
    inner join   I_Ledger as _ledger on  _ledger.Ledger     = _item.SourceLedger
    //and _ledger.IsLeadingLedger = 'X'  //support only leading leader in CE2005

                                     and _ledger.LedgerType = '' //standard ledger only

{

  key OrderID as aufnr,
  //key kokrs,

  key SourceLedger as rldnr,
  key  CompanyCode,
      OrderID,
      //kokrs                 as ControllingArea,

      SourceLedger                 as Ledger,



      sum(AmountInCompanyCodeCurrency)              as AmountInCompanyCodeCurrency,
      sum(AmountInGlobalCurrency)              as AmountInGlobalCurrency,
      sum(AmountInFreeDefinedCurrency1)              as AmountInFreeDefinedCurrency1,
      sum(AmountInFreeDefinedCurrency2)              as AmountInFreeDefinedCurrency2,
      sum(AmountInFreeDefinedCurrency3)              as AmountInFreeDefinedCurrency3,
      sum(AmountInFreeDefinedCurrency4)              as AmountInFreeDefinedCurrency4,
      sum(AmountInFreeDefinedCurrency5)              as AmountInFreeDefinedCurrency5,
      sum(AmountInFreeDefinedCurrency6)              as AmountInFreeDefinedCurrency6,
      sum(AmountInFreeDefinedCurrency7)              as AmountInFreeDefinedCurrency7,
      sum(AmountInFreeDefinedCurrency8)              as AmountInFreeDefinedCurrency8,

      /////////////////////////////////////////////////////////////////////////////////////

      // Period Amount Fields

      /////////////////////////////////////////////////////////////////////////////////////

      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInCompanyCodeCurrency
            end)     as PeriodAmountInCompanyCodeCrcy,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInGlobalCurrency
            end) as PeriodAmountInGlobalCurrency,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency1
            end)     as PeriodAmountInFreeDfndCrcy1,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency2
            end)     as PeriodAmountInFreeDfndCrcy2,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency3
            end)     as PeriodAmountInFreeDfndCrcy3,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency4
            end)     as PeriodAmountInFreeDfndCrcy4,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency5
            end)     as PeriodAmountInFreeDfndCrcy5,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency6
            end)     as PeriodAmountInFreeDfndCrcy6,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency7
            end)     as PeriodAmountInFreeDfndCrcy7,
      sum(case
            when (PostingDate >= $parameters.P_FiscalPeriodStartDate and PostingDate <= $parameters.P_FiscalPeriodEndDate) then
                AmountInFreeDefinedCurrency8
            end)     as PeriodAmountInFreeDfndCrcy8

}
where
  (
       _item.AccountAssignmentType  =  'OR'
    or _item.AccountAssignmentType  =  'OP'
  )
  and _item.SubLedgerAcctLineItemType <> '09101' 
  and _item.SubLedgerAcctLineItemType <> '09111'
  and(
       _item.OrderCategory    =  '10'
    or _item.OrderCategory    =  '40'
  )
  and  _item.BusinessTransactionCategory   <> 'TBCS' //ignore TBCS posting

  and  _item.OriginCtrlgDebitCreditCode <> ''
  and _item.ControllingObject <> ''
  and (_item.TransactionTypeDetermination    <> 'PRD' or _item.BusinessTransactionCategory <> 'EBVP')
  and(
       _item.PostingDate    <= $parameters.P_FiscalPeriodEndDate
  )
group by
  _item.CompanyCode,
  _item.OrderID,
 // kokrs,

  _item.SourceLedger