P_COMFGORDERPLANSTDCUBE

@AbapCatalog.sqlViewName: 'PFIMFGORPLNSTDCB'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@VDM.private:true
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
//@EndUserText.label: 'Plan and Standard Costs Cube for Manufacturing Orders'

//Planning category to distinguish order plan costs and standard costs

//No fiscal year period restricition, for target costs calculation


define view P_COMFGOrderPlanStdCube
  as select from I_FinancialPlanningEntryItem as _PlanningEntryItem
    inner join   I_Ledger                     as _Ledger on  _PlanningEntryItem.Ledger = _Ledger.Ledger
                                                         and _Ledger.IsLeadingLedger   = 'X'
{
  key   cast( '' as j_objnr) as   ControllingObject,
  key   cast( '' as j_objnr) as   ControllingObjectForOutput,
  key   ControllingArea,
  key   FiscalYear,
  key   FiscalPeriod,
  key   FiscalYearPeriod,
  key   '01'                                                                                as   ControllingValueType, //plan costs 01

  key   GLAccount,
  key   cast('' as co_subkey)                                                               as   ControllingKeySubNumber,
  key   ControllingDebitCreditCode,
  key   PlanningCategory, // plan cost & standard cost category

  key   OrderID,
  //key   OrderItem,

  key   cast ( case AccountAssignmentType when 'OR' then '0000' when 'OP' then _PlanningEntryItem.OrderItem end as co_posnr ) as  OrderItem,
  key   BusinessTransactionType,
  key   AccountAssignmentType,
  key   PartnerAccountAssignmentType,
  key   PartnerCostCenter,
  key   PartnerCostCtrActivityType,
  key   PartnerOrder_2 as PartnerOrder, 
  key   CompanyCode,
  key   _PlanningEntryItem.Ledger                                                           as   Ledger,

  key  ''  as PartnerCostObject, 
  key  '' as OriginSenderObject,
  key  OriginCostCenter,
  key  OriginCostCtrActivityType,

  key   (case when BusinessTransactionType = 'KPPP'
         then 'P'
         else 'S'
         end)                                                                               as   CtrlgOriginClassification, //Origin Indicator


        //Logistics related fields

  key   WorkCenterInternalID,
  key   OrderOperation,

  key   Material,
  key   ProducedProduct,
  key   CostOriginGroup,
        //units

  key   Plant,      
  key   CostSourceUnit,
  key   GlobalCurrency,
  key   CompanyCodeCurrency,
  FreeDefinedCurrency1,
  FreeDefinedCurrency2,
  FreeDefinedCurrency3,
  FreeDefinedCurrency4,
  FreeDefinedCurrency5,
  FreeDefinedCurrency6,
  FreeDefinedCurrency7,
  FreeDefinedCurrency8,
  
        //Lot-size independent indicator for target cost calculation

        IsLotSizeIndependent,

        sum(AmountInGlobalCurrency)                                                         as   AmountInGlobalCurrency,
        sum(FixedAmountInGlobalCrcy)                                                        as   FixedAmountInGlobalCurrency,
        sum(AmountInCompanyCodeCurrency)                                                    as   AmountInCompanyCodeCurrency,
        sum(case
             when AmountInGlobalCurrency <> 0
                 then cast( round(cast(division(FixedAmountInGlobalCrcy, AmountInGlobalCurrency, 6) as abap.dec(14, 6))  //fixed amount is always less than total amount here

                            * AmountInCompanyCodeCurrency, 2)
                          as abap.curr(23, 2) )
                 else 0
             end)                                                                           as   FixedAmountInCoCodeCurrency,
        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,

        //Quantity

        sum(ValuationQuantity)                                                              as   TotalQuantity
}
where
  (
       AccountAssignmentType =  'OR'
    or AccountAssignmentType =  'OP'
  )
  //and  OrderID               <> ''

group by
  ControllingArea,
  FiscalYear,
  FiscalPeriod,
  FiscalYearPeriod,
  GLAccount,
  ControllingDebitCreditCode,
  PlanningCategory,
  AccountAssignmentType,
  OrderID,
  OrderItem,
  BusinessTransactionType,
  PartnerAccountAssignmentType,
  PartnerCostCenter,
  PartnerCostCtrActivityType,
  PartnerOrder_2,
  WorkCenterInternalID,
  OrderOperation,
  Material,
  ProducedProduct,
  CostOriginGroup,
  Plant,
  CostSourceUnit,
  GlobalCurrency,
  CompanyCodeCurrency,
  IsLotSizeIndependent,
  CompanyCode,
  _PlanningEntryItem.Ledger,
  FreeDefinedCurrency1,
  FreeDefinedCurrency2,
  FreeDefinedCurrency3,
  FreeDefinedCurrency4,
  FreeDefinedCurrency5,
  FreeDefinedCurrency6,
  FreeDefinedCurrency7,
  FreeDefinedCurrency8,
  OriginCostCenter,
  OriginCostCtrActivityType