@AbapCatalog.sqlViewName: 'PFIMFGORDTGTCUBE'
@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
//From/To fiscal year period should restrict actual output quantity only
//While plan cost and standard costs should be independent of fiscal year period
// PLANORD02 (MTS + MTO Valuation Strategy 3)
// PLANORD03 (MTO Valuation Strategy 1)
//union all PLANORD01 (MTO Valuation Strategy 2)
define view P_COMFGOrderTargetCube
with parameters
P_FromFiscalYearPeriod : fis_jahrper,
P_ToFiscalYearPeriod : fis_jahrper,
P_PlanningCategory : fcom_category
as select from P_PCCOrderSpclStockValStrategy as _TargetCostStrategy
inner join P_PCCOrderActualOutputQuantity( P_FromFiscalYearPeriod : :P_FromFiscalYearPeriod,
P_ToFiscalYearPeriod : :P_ToFiscalYearPeriod ) as _ActualQuantity on _ActualQuantity.ControllingObject = _TargetCostStrategy.ObjectInternalID
and _ActualQuantity.OrderID = _TargetCostStrategy.OrderID
and _ActualQuantity.Material = _TargetCostStrategy.Material
and _ActualQuantity.ActualOutputQuantity <> 0
inner join P_PCCCostEstimateItem as _CostEstimate on _CostEstimate.Plant = _TargetCostStrategy.Plant
and _CostEstimate.ProducedProduct = _TargetCostStrategy.Material
inner join I_Ledger as _ledger on _ledger.Ledger = _CostEstimate.Ledger
and _ledger.IsLeadingLedger = 'X'
{
key _TargetCostStrategy.OrderID,
key _TargetCostStrategy.OrderItem,
key ControllingObject,
key ControllingArea,
key FiscalYear,
key FiscalPeriod,
key FiscalYearPeriod,
key '05' as ControllingValueType, //05 for target cost
key GLAccount,
key cast('' as co_subkey) as ControllingKeySubNumber,
key ControllingDebitCreditCode,
key PlanningCategory,
key BusinessTransactionType,
key cast ( substring(ControllingObject, 1,2 ) as j_obart ) as AccountAssignmentType,
key PartnerAccountAssignmentType,
key PartnerCostCenter,
key PartnerCostCtrActivityType,
key OriginCostCenter,
key OriginCostCtrActivityType,
key '' as PartnerOrder,
key '' as PartnerCostObject,
key '' as OriginSenderObject,
key (case when BusinessTransactionType = 'KPPP' then 'P' else 'S' end) as CtrlgOriginClassification, //Origin Indicator
key WorkCenterInternalID,
key OrderOperation,
key _CostEstimate.Material,
key _CostEstimate.ProducedProduct,
key CostOriginGroup,
key _TargetCostStrategy.Plant,
key _TargetCostStrategy.OrderCategory,
key _TargetCostStrategy.OrderType,
key CostSourceUnit,
key GlobalCurrency,
key CompanyCodeCurrency,
key _CostEstimate.CompanyCode,
key _ledger.Ledger,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8,
//Grobal Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInGlobalCurrency
else
case
when CostingLotSize <> 0
then cast( round(cast(division( -ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInGlobalCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInGlobalCurrency,
sum(case
when IsLotSizeIndependent = 'X' then FixedAmountInGlobalCurrency
else
case
when CostingLotSize <> 0
then cast( round(cast(division( -ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * FixedAmountInGlobalCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as FixedAmountInGlobalCurrency,
//Company Code Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInCompanyCodeCurrency
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInCompanyCodeCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInCompanyCodeCurrency,
sum( case
when IsLotSizeIndependent = 'X' then FixedAmountInCoCodeCurrency
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * FixedAmountInCoCodeCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as FixedAmountInCoCodeCurrency,
// Free Defined Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency1
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency1, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency1,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency2
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency2, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency2,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency3
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency3, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency3,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency4
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency4, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency4,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency5
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency5, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency5,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency6
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency6, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency6,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency7
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency7, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency7,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency8
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency8, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency8,
sum( case
when IsLotSizeIndependent = 'X' then TotalQuantity
else
case
when CostingLotSize <> 0
then cast( round(cast(division(-ActualOutputQuantity, CostingLotSize, 6) as abap.dec(14, 6)) * TotalQuantity, 3) as abap.quan(23, 3) )
else 0
end
end ) as TotalQuantity
}
where
(
_CostEstimate.PlngCatIsForMatlCostEstPlnCost = 'X'
and _TargetCostStrategy.ValuationStrategy = '3'
and _ActualQuantity.PostingDate >= _CostEstimate.CostEstimateValidityStartDate
and _ActualQuantity.PostingDate <= _CostEstimate.CostEstimateValidityEndDate
)
or(
_CostEstimate.PlnCatIsForSalesOrderCostEst = 'X'
and _TargetCostStrategy.ValuationStrategy = '1'
and _CostEstimate.SalesOrder = _TargetCostStrategy.SalesOrder
and _CostEstimate.SalesOrderItem = _TargetCostStrategy.SalesOrderItem
)
group by
_TargetCostStrategy.OrderID,
_TargetCostStrategy.OrderItem,
ControllingObject,
ControllingArea,
FiscalYear,
FiscalPeriod,
FiscalYearPeriod,
GLAccount,
ControllingDebitCreditCode,
PlanningCategory,
BusinessTransactionType,
AccountAssignmentType,
PartnerAccountAssignmentType,
PartnerCostCenter,
PartnerCostCtrActivityType,
OriginCostCenter,
OriginCostCtrActivityType,
WorkCenterInternalID,
OrderOperation,
_CostEstimate.Material,
_CostEstimate.ProducedProduct,
CostOriginGroup,
_TargetCostStrategy.Plant,
_TargetCostStrategy.OrderCategory,
_TargetCostStrategy.OrderType,
CostSourceUnit,
GlobalCurrency,
CompanyCodeCurrency,
_CostEstimate.CompanyCode,
_ledger.Ledger,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8
//union all PLANORD01 (MTO Valuation Strategy 2)
union all select from P_PCCOrderSpclStockValStrategy as _TargetCostStrategy
inner join P_COMFGOrderOutputQuantities( P_FromFiscalYearPeriod : :P_FromFiscalYearPeriod,
P_ToFiscalYearPeriod : :P_ToFiscalYearPeriod ) as _ActualQuantity on _ActualQuantity.ControllingObject = _TargetCostStrategy.ObjectInternalID
and _ActualQuantity.OrderID = _TargetCostStrategy.OrderID
and _ActualQuantity.ActualOutputQuantity <> 0
inner join P_COMFGOrderPlanStdCube as _PlanCost on _PlanCost.OrderID = _TargetCostStrategy.OrderID
and _PlanCost.OrderItem = _TargetCostStrategy.OrderItem
and _PlanCost.ProducedProduct = _TargetCostStrategy.Material
inner join I_MfgOrdPlngCat as _MfgOrdPlngCat on _MfgOrdPlngCat.PlanningCategory = _PlanCost.PlanningCategory
{
key _TargetCostStrategy.OrderID,
key _TargetCostStrategy.OrderItem,
key _ActualQuantity.ControllingObject,
key ControllingArea,
key FiscalYear,
key FiscalPeriod,
key FiscalYearPeriod,
key '05' as ControllingValueType, //05 for target cost
key GLAccount,
key cast('' as co_subkey) as ControllingKeySubNumber,
key ControllingDebitCreditCode,
key _PlanCost.PlanningCategory,
key BusinessTransactionType,
key AccountAssignmentType,
key PartnerAccountAssignmentType,
key PartnerCostCenter,
key PartnerCostCtrActivityType,
key OriginCostCenter,
key OriginCostCtrActivityType,
key '' as PartnerOrder,
key '' as PartnerCostObject,
key '' as OriginSenderObject,
key (case when BusinessTransactionType = 'KPPP' then 'P' else 'S' end) as CtrlgOriginClassification, //Origin Indicator
key WorkCenterInternalID,
key OrderOperation,
key _PlanCost.Material,
key _PlanCost.ProducedProduct,
key CostOriginGroup,
key _TargetCostStrategy.Plant,
key _TargetCostStrategy.OrderCategory,
key _TargetCostStrategy.OrderType,
key CostSourceUnit,
key GlobalCurrency,
key CompanyCodeCurrency,
key _PlanCost.CompanyCode,
key _PlanCost.Ledger,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8,
//Grobal Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInGlobalCurrency
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInGlobalCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInGlobalCurrency,
sum(case
when IsLotSizeIndependent = 'X' then FixedAmountInGlobalCurrency
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * FixedAmountInGlobalCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as FixedAmountInGlobalCurrency,
//Company Code Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInCompanyCodeCurrency
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInCompanyCodeCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInCompanyCodeCurrency,
sum( case
when IsLotSizeIndependent = 'X' then FixedAmountInCoCodeCurrency
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * FixedAmountInCoCodeCurrency, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as FixedAmountInCoCodeCurrency,
// Free Defined Currency
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency1
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency1, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency1,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency2
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency2, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency2,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency3
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency3, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency3,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency4
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency4, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency4,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency5
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency5, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency5,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency6
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency6, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency6,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency7
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency7, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency7,
sum( case
when IsLotSizeIndependent = 'X' then AmountInFreeDefinedCurrency8
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * AmountInFreeDefinedCurrency8, 2) as abap.curr( 23, 2 ) )
else 0
end
end ) as AmountInFreeDefinedCurrency8,
sum( case
when IsLotSizeIndependent = 'X' then TotalQuantity
else
case
when PlanOutputQuantity <> 0
then cast( round(cast(division(ActualOutputQuantity, PlanOutputQuantity, 6) as abap.dec(14, 6)) * TotalQuantity, 3) as abap.quan(23, 3) )
else 0
end
end ) as TotalQuantity
}
where
_TargetCostStrategy.ValuationStrategy = '2'
and _MfgOrdPlngCat.PlngCatIsForOrdReltdPlnCost = 'X'
group by
_TargetCostStrategy.OrderID,
_TargetCostStrategy.OrderItem,
_ActualQuantity.ControllingObject,
ControllingArea,
FiscalYear,
FiscalPeriod,
FiscalYearPeriod,
GLAccount,
ControllingDebitCreditCode,
_PlanCost.PlanningCategory,
BusinessTransactionType,
AccountAssignmentType,
PartnerAccountAssignmentType,
PartnerCostCenter,
PartnerCostCtrActivityType,
OriginCostCenter,
OriginCostCtrActivityType,
WorkCenterInternalID,
OrderOperation,
_PlanCost.Material,
_PlanCost.ProducedProduct,
CostOriginGroup,
_TargetCostStrategy.Plant,
_TargetCostStrategy.OrderCategory,
_TargetCostStrategy.OrderType,
CostSourceUnit,
GlobalCurrency,
CompanyCodeCurrency,
_PlanCost.CompanyCode,
_PlanCost.Ledger,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8