P_COOrderPlanTargetCube

@AbapCatalog.sqlViewName: 'PFIORDPLNTGTCUB'
@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: 'Order Plan Target Cube'

//Union plan and target costs for all order category


define view P_COOrderPlanTargetCube
  with parameters
    P_FromFiscalYearPeriod : fis_jahrper,
    P_ToFiscalYearPeriod   : fis_jahrper,
    P_PlanningCategory     : fcom_category,
    P_ControllingObject    : j_objnr
  as select from P_COMFGOrderPlanStdCube as _PlanStdCube //plan or standard cost for 10/40, incl. both header and items

    inner join   I_MfgOrdPlngCat         as _MfgOrdPlngCat on _MfgOrdPlngCat.PlanningCategory = _PlanStdCube.PlanningCategory
{
  key  ControllingObject,
  key  ControllingArea,
  key  FiscalYear,
  key  FiscalPeriod,
  key  FiscalYearPeriod,
  key  ControllingValueType,
  key  GLAccount,
  key  ControllingKeySubNumber,
  key  ControllingDebitCreditCode,
  key  _PlanStdCube.PlanningCategory,
  key  OrderID,
  key  OrderItem,
  key  BusinessTransactionType,
  key  AccountAssignmentType,
  key  PartnerAccountAssignmentType,
  key  PartnerCostCenter,
  key  PartnerCostCtrActivityType,
  key  PartnerOrder,
  key  PartnerCostObject,
  key  OriginSenderObject,
  key  OriginCostCenter,
  key  OriginCostCtrActivityType,
  key  CtrlgOriginClassification,
  key  WorkCenterInternalID,
  key  OrderOperation,
  key  Material,
  key  CostOriginGroup,
  key  CostSourceUnit,
  key  GlobalCurrency,
  key  CompanyCodeCurrency,
       sum(AmountInGlobalCurrency)      as AmountInGlobalCurrency,
       sum(FixedAmountInGlobalCurrency) as FixedAmountInGlobalCurrency,
       sum(AmountInCompanyCodeCurrency) as AmountInCompanyCodeCurrency,
       sum(FixedAmountInCoCodeCurrency) as FixedAmountInCoCodeCurrency,
       sum(TotalQuantity)               as TotalQuantity
}
where
      PlngCatIsForOrdReltdPlnCost = 'X'
  and FiscalYearPeriod            >= $parameters.P_FromFiscalYearPeriod
  and FiscalYearPeriod            <= $parameters.P_ToFiscalYearPeriod
//       or    PlngCatIsForMatlCostEstPlnCost = 'X' //  Remove standard cost

group by
  ControllingObject,
  ControllingArea,
  FiscalYear,
  FiscalPeriod,
  FiscalYearPeriod,
  ControllingValueType,
  GLAccount,
  ControllingKeySubNumber,
  ControllingDebitCreditCode,
  _PlanStdCube.PlanningCategory,
  OrderID,
  OrderItem,
  BusinessTransactionType,
  AccountAssignmentType,
  PartnerAccountAssignmentType,
  PartnerCostCenter,
  PartnerCostCtrActivityType,
  PartnerOrder,
  PartnerCostObject,
  OriginSenderObject,
  OriginCostCenter,
  OriginCostCtrActivityType,
  CtrlgOriginClassification,
  WorkCenterInternalID,
  OrderOperation,
  Material,
  CostOriginGroup,
  CostSourceUnit,
  GlobalCurrency,
  CompanyCodeCurrency
union select from P_COMFGOrderTargetCube //target costs for 10/40

                               (
                               P_FromFiscalYearPeriod   : $parameters.P_FromFiscalYearPeriod,
                               P_ToFiscalYearPeriod     : $parameters.P_ToFiscalYearPeriod,
                               P_PlanningCategory       : $parameters.P_PlanningCategory
                               )
{
  key  ControllingObject,
  key  ControllingArea,
  key  FiscalYear,
  key  FiscalPeriod,
  key  FiscalYearPeriod,
  key  ControllingValueType,
  key  GLAccount,
  key  ControllingKeySubNumber,
  key  ControllingDebitCreditCode,
  key  PlanningCategory,
  key  OrderID,
  key  OrderItem,
  key  BusinessTransactionType,
  key  AccountAssignmentType,
  key  PartnerAccountAssignmentType,
  key  PartnerCostCenter,
  key  PartnerCostCtrActivityType,
  key  PartnerOrder,
  key  PartnerCostObject,
  key  OriginSenderObject,
  key  OriginCostCenter,
  key  OriginCostCtrActivityType,
  key  CtrlgOriginClassification,
  key  WorkCenterInternalID,
  key  OrderOperation,
  key  Material,
  key  CostOriginGroup,
  key  CostSourceUnit,
  key  GlobalCurrency,
  key  CompanyCodeCurrency,
       AmountInGlobalCurrency,
       FixedAmountInGlobalCurrency,
       AmountInCompanyCodeCurrency,
       FixedAmountInCoCodeCurrency,
       TotalQuantity
}
union select from I_ProductCostCtrlgOrder                                                    as _order //plan & target for 04/05,

  inner join      P_COPlanTargetCube ( P_PlanningCategory : $parameters.P_PlanningCategory ) as _plantarget        on  _order.ObjectInternalID = _plantarget.ControllingObject
                                                                                                                   and _order.OrderID          = _plantarget.OrderID
  left outer join I_TargetCostVariant                                                        as _TargetCostVariant on  _order.ControllingArea         = _TargetCostVariant.ControllingArea
                                                                                                                   and _plantarget.ControllingVersion = _TargetCostVariant.TargetCostVariant
{
  key  _order.ObjectInternalID                         as ControllingObject,
  key  _order.ControllingArea,
  key  FiscalYear,
  key  FiscalPeriod,
  key  FiscalYearPeriod,
  key  ControllingValueType,
  key  CostElement                                     as GLAccount,
  key  ControllingKeySubNumber,
  key  ControllingDebitCreditCode,
  key  _plantarget.PlanningCategory,
  key  _order.OrderID,
  key  _plantarget.OrderItem,
  key  BusinessTransactionType,
  key  AccountAssignmentType,
  key  PartnerAccountAssignmentType,
       //Derive Partner related fields from PartnerAccountAssignmentType

  key  case PartnerAccountAssignmentType
        when 'KL'
          then substring(PartnerCostObject, 7, 10)
        when 'KS'
          then substring(PartnerCostObject, 7, 10)
        else ''
    end                                                as PartnerCostCenter,
  key  case PartnerAccountAssignmentType
        when 'KL' then substring(PartnerCostObject, 17, 6)
        else ''
    end                                                as PartnerCostCtrActivityType,
  key  case PartnerAccountAssignmentType
       when 'OR' then substring(PartnerCostObject, 3, 12)
       when 'OP' then substring(PartnerCostObject, 3, 12)
       else ''
    end                                                as PartnerOrder,
  key  PartnerCostObject,
  key  OriginSenderObject,
  
  key  case left(OriginSenderObject, 2)
       when 'KS'
       then substring(OriginSenderObject, 7, 10)
       when 'KL'
       then substring(OriginSenderObject, 7, 10) end as OriginCostCenter,
       
  key  case left(OriginSenderObject, 2)
       when 'KL'
       then substring(OriginSenderObject, 17, 6) end as OriginCostCtrActivityType,
  key  CtrlgOriginClassification,
  key  '00000000'                                      as WorkCenterInternalID,
  key  ''                                              as OrderOperation,
  key  ''                                              as Material,
  key  ''                                              as CostOriginGroup,
  key  UnitOfMeasure                                   as CostSourceUnit,
  key  _order._ControllingArea.ControllingAreaCurrency as GlobalCurrency,
  key  CompanyCodeCurrency,
       AmountInCtrlgAreaCrcy                           as AmountInGlobalCurrency,
       FixedAmountInCtrlgAreaCrcy                      as FixedAmountInGlobalCurrency,
       AmountInObjectCurrency                          as AmountInCompanyCodeCurrency,
       FixedAmountInObjectCurrency                     as FixedAmountInCoCodeCurrency,
       TotalQuantity
}
where
           FiscalYearPeriod                >= $parameters.P_FromFiscalYearPeriod
  and      FiscalYearPeriod                <= $parameters.P_ToFiscalYearPeriod
  and      ControllingObject               = $parameters.P_ControllingObject
  and(
    (
      (
           OrderCategory                   = '10'
        or OrderCategory                   = '40'
      )
      and  ControllingValueType            = '06'
    ) // Targert WIP for 10 and 40

    or(
      (
           OrderCategory                   = '04 '
        or OrderCategory                   = '05'
      )
      and  ControllingValueType            = '01'
    ) // Plan for 04 and 05

    or(
      (
           OrderCategory                   = '04'
        or OrderCategory                   = '05'
      )
      and  ControllingValueType            = '05'
      and  ComparisonCostingVariantType    = 'P'
    ) // Target for 04 and 05, only support version 0

  )
  and(
           _order.OrderIsEventBasedPosting = ''
    or     _order.OrderIsEventBasedPosting = 'M'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CONTROLLINGAREA",
"I_MFGORDPLNGCAT",
"I_PRODUCTCOSTCTRLGORDER",
"I_TARGETCOSTVARIANT",
"P_COMFGORDERPLANSTDCUBE",
"P_COMFGORDERTARGETCUBE",
"P_COPLANTARGETCUBE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/