P_EBOC_EventBasedVariance1

@AbapCatalog.sqlViewName: 'PEBOCEBVARC1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #COMPOSITE
@VDM.private: true

define view P_EBOC_EventBasedVariance1
  with parameters
    P_OrderID              : fis_order_number,
    P_FromFiscalYearPeriod : fis_jahrper,
    P_ToFiscalYearPeriod   : fis_jahrper,
    P_Ledger               : fins_ledger
  as select from I_JournalEntryItem
{
  key CompanyCode,
  key Plant,
  key ControllingArea,
  key cast(
        case
            when VarianceOriginGLAccount is not initial then VarianceOriginGLAccount
            else OffsettingAccount
        end
      as fis_racct)                                    as CostElement,
  key BusinessTransactionCategory,
  key OriginCtrlgDebitCreditCode                       as ControllingDebitCreditCode,
  key OriginSenderObject,
  key case SubLedgerAcctLineItemType
        when '09139'
          then 'C'  //LotSizeVarc is from Goods Receipt

        else 'B'
      end                                              as CtrlgOriginClassification,
  key case
        when OrderID <> '' then OrderID
        else OriginOrder // V-Price Variance (BSX transaction)

      end                                              as OrderID,
  key OrderItem,
  key AccountAssignmentType,
  key PartnerAccountAssignment,
  key PartnerAccountAssignmentType,
  key cast(OriginCostCenter as fis_skost)              as PartnerCostCenter,
  key cast(OriginCostCtrActivityType as fis_par_lstar) as PartnerCostCtrActivityType,
  key PartnerBusinessProcess,
  key PartnerOrder_2                                   as PartnerOrder,
  key cast(ReferenceQuantityUnit as meins)             as UnitOfMeasure,
  key OriginProduct                                    as Material,
  key VarianceOriginGroup                              as CostOriginGroup,

  key SubLedgerAcctLineItemType,
  key cast(
        case
            when SubLedgerAcctLineItemType = '09139' then 'X' // Lot-Size Variance

            else ''
        end
      as fco_psknz )                                   as IsLotSizeIndependent,

      // Orig. Objects

  key OriginCostCenter,
  key OriginCostCtrActivityType,

      // Actual Amounts from ACDOCA

      sum(AmountInCompanyCodeCurrency)                 as AmountInCompanyCodeCurrency,
      sum(AmountInGlobalCurrency)                      as AmountInGlobalCurrency,
      sum(FixedAmountInGlobalCrcy)                     as FixedAmountInGlobalCrcy,

      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
}
where
       BusinessTransactionCategory  =  'EBVP'
  and(
       TransactionTypeDetermination =  'PRD'
    or TransactionTypeDetermination =  'BSX'
    or TransactionTypeDetermination =  ''
  )
  and(
       OrderID                      =  :P_OrderID
    or OriginOrder                  =  :P_OrderID
  )
  and  FiscalYearPeriod             >= :P_FromFiscalYearPeriod
  and  FiscalYearPeriod             <= :P_ToFiscalYearPeriod
  and  Ledger                       =  :P_Ledger

group by
  CompanyCode,
  Plant,
  ControllingArea,
  SubLedgerAcctLineItemType,
  VarianceOriginGLAccount,
  OffsettingAccount,
  BusinessTransactionCategory,
  OriginCtrlgDebitCreditCode,
  OriginSenderObject,
  OrderID,
  OriginOrder,
  OrderItem,
  AccountAssignmentType,
  PartnerAccountAssignment,
  PartnerAccountAssignmentType,
  OriginCostCenter,
  OriginCostCtrActivityType,
  PartnerBusinessProcess,
  PartnerOrder_2,
  ReferenceQuantityUnit,
  OriginProduct,
  VarianceOriginGroup,
  OriginCostCenter,
  OriginCostCtrActivityType