@AbapCatalog.sqlViewName: 'PSCHORDERDETS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_SCHOrderDets
as select from I_CapacityRequirementItemCap as CapRqmtItmCap
// left outer join I_SchedProdnPlngDets as _plngdets on _plngdets.UserName = $session.user
// and _plngdets.SchedProdnAppName = 'SCH_PROD'
// left outer join I_SchedProdnChgOp as _chgoper on _plngdets.SimulationSessionID = _chgoper.SimulationSessionID
// and CapRqmtItmCap.OrderID = _chgoper.OrderID
// and CapRqmtItmCap.CapacityRequirementItem = _chgoper.CapacityRequirementItem
// /* added for Setup Group Key and Category */
// //association [0..1] to I_OrderOperation as _OrderOperations on _OrderOperations.OrderInternalID = CapRqmtItmCap.OrderInternalID
// // and _OrderOperations.Operation = _chgoper.Operation
//
// association [0..1] to I_SchedProdnVersRtgDetails as _ProdnVersRtgDets on //_ProdnVersRtgDets.Material = $projection.Material
// _ProdnVersRtgDets.ProductionVersion = _chgoper.ProductionVersion
// and _ProdnVersRtgDets.Operation = _chgoper.Operation
// and _ProdnVersRtgDets.WorkCenterInternalID = _chgoper.WorkCenterInternalID
// and _ProdnVersRtgDets.Plant = $projection.Plant
//
{
key cast(CapRqmtItmCap.OrderID as pph_cfs_ordernumber) as OrderID,
key CapRqmtItmCap.CapacityRequirementItem as CapacityRequirementItem,
key CapRqmtItmCap.CapacityRqmtItemCapacity as CapacityRqmtItemCapacity,
key cast(CapRqmtItmCap._Order.OrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
cast( CapRqmtItmCap._Order.Material as matnr) as Material,
cast ( CapRqmtItmCap._Order.OrderPlannedTotalQty as mgvrg ) as OrderPlannedTotalQty,
cast(CapRqmtItmCap._Order.BaseUnit as meins preserving type) as BaseUnit,
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(CapRqmtItmCap.OperationLatestEndDate,CapRqmtItmCap.OperationLatestEndTime)as abap.numc(15)) as timestamp)
end as SchedldOrdEndDteTme,
case
when CapRqmtItmCap.OperationLatestStartTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestStartDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(CapRqmtItmCap.OperationLatestStartDate,CapRqmtItmCap.OperationLatestStartTime)as abap.numc(15)) as timestamp)
end as SchedldOrdStartDteTme,
cast(CapRqmtItmCap.OrderID as aufnr) as ManufacturingOrder,
cast(CapRqmtItmCap.PlannedOrder as plnum) as PlannedOrder,
CapRqmtItmCap._Order._OrderItem.ProductionVersion as ProductionVersion,
CapRqmtItmCap.OrderInternalID as OrderInternalID,
/*Setup Group Key */
CapRqmtItmCap._OrderOperation.OperationSetupGroup
as OperationSetupGroup,
/* Setup Group Category */
CapRqmtItmCap._OrderOperation.OperationSetupGroupCategory
as OperationSetupGroupCategory,
CapRqmtItmCap._WorkCenter.Plant as Plant,
case
when CapRqmtItmCap._Order.LastChangeDate = '00000000'
then cast(cast( concat( CapRqmtItmCap._Order.CreationDate, CapRqmtItmCap._Order.CreationTime)as abap.numc(15)) as timestamp)
when CapRqmtItmCap._Order.LastChangeDate != '00000000'
then
cast(cast( concat( CapRqmtItmCap._Order.LastChangeDate, CapRqmtItmCap._Order.LastChangeTime)as abap.numc(15)) as timestamp)
end as LastChangeDateTime,
CapacityRequirement as CapacityRequirement
}
where
CapRqmtItmCap._CapacityRequirement.CapacityRequirementOrigin = '1'
and(
CapRqmtItmCap._Order.OrderCategory = '30'
or CapRqmtItmCap.Operation != ''
)
and CapRqmtItmCap.CapacityInternalID = CapRqmtItmCap._WorkCenter.CapacityInternalID
and CapRqmtItmCap._Capacity.CapacityIsFinite = 'X'
and(
CapRqmtItmCap._Order.OrderCategory = '10'
or CapRqmtItmCap._Order.OrderCategory = '40'
or CapRqmtItmCap._Order.OrderCategory = '30'
)
and(
CapRqmtItmCap.OperationIsPhase != '2' //Don't display phases
)
union
select from I_CapacityRequirementItemCap as CapRqmtItmCap
{
key cast ( CapRqmtItmCap.PlannedOrder as pph_cfs_ordernumber) as OrderID,
key CapRqmtItmCap.CapacityRequirementItem as CapacityRequirementItem,
key CapRqmtItmCap.CapacityRqmtItemCapacity as CapacityRqmtItemCapacity,
key cast(CapRqmtItmCap._PlannedOrder.PlannedOrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
cast ( CapRqmtItmCap._PlannedOrder.Material as matnr ) as Material,
cast ( CapRqmtItmCap._PlannedOrder.PlannedTotalQtyInBaseUnit as mgvrg ) as OrderPlannedTotalQty,
cast(CapRqmtItmCap._PlannedOrder.BaseUnit as meins preserving type) as BaseUnit,
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(CapRqmtItmCap.OperationLatestEndDate,CapRqmtItmCap.OperationLatestEndTime)as abap.numc(15)) as timestamp)
end as SchedldOrdEndDteTme,
case
when CapRqmtItmCap.OperationLatestStartTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestStartDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(CapRqmtItmCap.OperationLatestStartDate,CapRqmtItmCap.OperationLatestStartTime)as abap.numc(15)) as timestamp)
end as SchedldOrdStartDteTme,
cast(CapRqmtItmCap.OrderID as aufnr) as ManufacturingOrder,
cast(CapRqmtItmCap.PlannedOrder as plnum) as PlannedOrder,
CapRqmtItmCap._PlannedOrder.ProductionVersion as ProductionVersion,
CapRqmtItmCap.OrderInternalID
as OrderInternalID,
/*Setup Group */
CapRqmtItmCap._BOOOperationInternalVersion.OperationSetupGroup
as OperationSetupGroup,
/*Setup Group Category */
CapRqmtItmCap._BOOOperationInternalVersion.OperationSetupGroupCategory
as OperationSetupGroupCategory,
CapRqmtItmCap._WorkCenter.Plant as Plant,
cast( CapRqmtItmCap._PlannedOrder.LastChangeDateTime as timestamp) as LastChangeDateTime ,
CapacityRequirement as CapacityRequirement
}
where
CapRqmtItmCap._CapacityRequirement.CapacityRequirementOrigin = '2'
and CapRqmtItmCap.Operation != ''
and CapRqmtItmCap.CapacityInternalID = CapRqmtItmCap._WorkCenter.CapacityInternalID
and CapRqmtItmCap._Capacity.CapacityIsFinite = 'X'
and(
CapRqmtItmCap.OperationIsPhase != '2' //Don't display phases
)
/*as select from I_LogisticsOrder
{
/* OrderID,
cast(OrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
OrderType,
Material,
OrderPlannedTotalQty,
BaseUnit,
case
when ScheduledBasicEndTime >= '240000'
then cast(cast(concat(dats_add_days(ScheduledBasicEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(ScheduledBasicEndDate,ScheduledBasicEndTime)as abap.numc(15)) as timestamp)
end as ReceiptDate
} where OrderCategory = '10' or OrderCategory = '30' or OrderCategory = '40'
union
select from I_PlannedOrder
{
PlannedOrder as OrderID,
cast( PlannedOrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
PlannedOrderType as OrderType,
Material,
PlannedTotalQtyInBaseUnit as OrderPlannedTotalQty,
BaseUnit,
case
when PlndOrderPlannedEndTime >= '240000'
then cast(cast(concat(dats_add_days(PlndOrderPlannedEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
else
cast(cast(concat(PlndOrderPlannedEndDate,PlndOrderPlannedEndTime)as abap.numc(15)) as timestamp)
end as ReceiptDate
} where PlannedOrderCategory = '1'
*/
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLOFOPERATIONSOPBASIC",
"I_CAPACITY",
"I_CAPACITYREQUIREMENT",
"I_CAPACITYREQUIREMENTITEMCAP",
"I_LOGISTICSORDER",
"I_ORDERITEM",
"I_ORDEROPERATION",
"I_PLANNEDORDER",
"I_WORKCENTER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/