@AbapCatalog.sqlViewName: 'PSCHCOPERLC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.serviceQuality: #B
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_SchedChngOpersLiveCacheMap
with parameters
P_StartDate : datum,
P_EndDate : datum
as select from I_CapacityRequirementItemCap as CapRqmtItmCap
left outer join I_CapacityRequirementItemCap as SupCapRqmt on SupCapRqmt.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
and SupCapRqmt.CapacityRequirementItem = CapRqmtItmCap.SuperiorOperationInternalID
and SupCapRqmt.CapacityRqmtItemCapacity = CapRqmtItmCap.CapacityRqmtItemCapacity
left outer join I_SchedProdnOrderTimeZone as OrderTimeZone on OrderTimeZone.client = $session.client
association [1..1] to P_SchedOrderOperationStatus as _OrderStatus on CapRqmtItmCap.OrderObjectInternalID = _OrderStatus.StatusObject
{
key cast(CapRqmtItmCap.OrderID as pph_cfs_ordernumber) as OrderID,
key CapRqmtItmCap.CapacityRequirementItem,
key CapRqmtItmCap.CapacityRqmtItemCapacity,
key cast(CapRqmtItmCap._Order.OrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
CapRqmtItmCap.CapacityRequirement,
// Mapping Repetetive manufacturing to Discrete Industry
cast( case
when CapRqmtItmCap._Order._OrderMainItem._ProductionVersion._BillOfOperationsType.BillOfOperationsApplication = 'R'
then cast ('P' as plnaw)
else
cast (CapRqmtItmCap._Order._OrderMainItem._ProductionVersion._BillOfOperationsType.BillOfOperationsApplication as plnaw)
end as plnaw preserving type ) as IndustryType,
CapRqmtItmCap.OperationIsPhase,
CapRqmtItmCap.SuperiorOperationInternalID,
cast(CapRqmtItmCap.OrderInternalID as abap.char(10)) as OrderInternalID,
CapRqmtItmCap.OrderOperationInternalID,
case CapRqmtItmCap.Operation
when ''
then cast('1' as vornr)
else
cast ( CapRqmtItmCap.Operation as vornr )
end as Operation,
case CapRqmtItmCap.OperationIsPhase
when '2'
then cast(SupCapRqmt.Operation as vornr)
else
case CapRqmtItmCap.Operation
when ''
then cast('1' as vornr)
else
cast ( CapRqmtItmCap.Operation as vornr )
end
end as SupOperation,
CapRqmtItmCap._WorkCenter.Plant as Plant,
CapRqmtItmCap.WorkCenterInternalID as WorkCenterInternalID,
CapRqmtItmCap._WorkCenter.WorkCenter as WorkCenter,
CapRqmtItmCap.CapacityInternalID,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then CapRqmtItmCap._WorkCenter.WorkCenter
else
CapRqmtItmCap._Order._OrderMainItem._ProductionVersion.ProductionLine
end as ProductionLine,
case
when ( CapRqmtItmCap.OperationIsPhase = '0' and CapRqmtItmCap._OrderOperation.OpDispatchedExecStartDate != '00000000')
or ( CapRqmtItmCap.OperationIsPhase = '2' and SupCapRqmt._OrderOperation.OpDispatchedExecStartDate != '00000000' )
then cast('DSPT' as pph_cfs_sch_status)
else cast('DALL' as pph_cfs_sch_status)
end as OperationPlanningStatusCode,
concat(concat(concat(CapRqmtItmCap.WorkCenterInternalID,'A'),CapRqmtItmCap._WorkCenter.Plant), CapRqmtItmCap.CapacityInternalID) as ResourceID,
case CapRqmtItmCap._Order.OrderCategory
when '10'
then cast(concat(concat(CapRqmtItmCap.OrderID, '|'), '2') as /sapapo/om_orderuid )
when '40'
then cast(concat(concat(CapRqmtItmCap.OrderID, '|'), '3') as /sapapo/om_orderuid )
when '30'
then cast(concat(concat(CapRqmtItmCap.OrderID, '|'), '4') as /sapapo/om_orderuid )
else
cast(concat(CapRqmtItmCap.OrderID, '|') as /sapapo/om_orderuid )
end as LiveCacheOrderID,
case CapRqmtItmCap._Order.OrderCategory
when '10'
then cast(concat(CapRqmtItmCap.CapacityRequirementItem, concat(CapRqmtItmCap.OrderID, '2')) as /sapapo/om_orderuid )
when '40'
then cast(concat(CapRqmtItmCap.CapacityRequirementItem, concat(CapRqmtItmCap.OrderID, '3')) as /sapapo/om_orderuid )
when '30'
then cast(concat(CapRqmtItmCap.CapacityRequirementItem,concat(CapRqmtItmCap.OrderID, '4')) as /sapapo/om_orderuid )
else
cast(CapRqmtItmCap.OrderID as /sapapo/om_orderuid )
end as LiveCacheActivityID,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then cast (0 as /sapapo/om_order_status)
else
cast (1 as /sapapo/om_order_status )
end as LiveCacheOrderStatus,
case
when ( CapRqmtItmCap.OperationIsPhase = '0' and CapRqmtItmCap._OrderOperation.OpDispatchedExecStartDate != '00000000')
or ( CapRqmtItmCap.OperationIsPhase = '2' and SupCapRqmt._OrderOperation.OpDispatchedExecStartDate != '00000000' )
or CapRqmtItmCap._Order.OrderCategory = '30'
then cast ('2' as /sapapo/om_planned_status)
else
cast ('1' as /sapapo/om_planned_status )
end as LiveCachePlannedStatus,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then cast ('B' as /sapapo/om_fixed_times)
else
cast ('' as /sapapo/om_fixed_times)
end as LiveCachedFixedTimes,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then cast ('X' as /sapapo/om_method)
else
cast ('' as /sapapo/om_method )
end as LiveCacheUpdateInd,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then cast ('000' as /sapapo/om_method)
else
cast ('' as /sapapo/om_method )
end as LiveCacheTrpidType,
case
when CapRqmtItmCap._Order.OrderCategory = '30'
then cast (0 as /sapapo/om_is_issued)
else
cast (1 as /sapapo/om_is_issued )
end as LiveCacheIsIssued,
CapRqmtItmCap._Order.ScheduledBasicStartDate,
CapRqmtItmCap._Order.ScheduledBasicStartTime,
case
when CapRqmtItmCap._Order.ScheduledBasicStartTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap._Order.ScheduledBasicStartDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap._Order.ScheduledBasicStartDate, CapRqmtItmCap._Order.ScheduledBasicStartTime)
end as ScheduledBasicStartTmstmp,
CapRqmtItmCap._Order.ScheduledBasicEndDate,
CapRqmtItmCap._Order.ScheduledBasicEndTime,
case
when CapRqmtItmCap._Order.ScheduledBasicEndTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap._Order.ScheduledBasicEndDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap._Order.ScheduledBasicEndDate, CapRqmtItmCap._Order.ScheduledBasicEndTime)
end as ScheduledBasicEndTmstmp,
case
when CapRqmtItmCap.OperationLatestStartTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.OperationLatestStartDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.OperationLatestStartDate, CapRqmtItmCap.OperationLatestStartTime)
end
as OperationLatestStartTmstmp,
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.OperationLatestEndDate, CapRqmtItmCap.OperationLatestEndTime)
end
as OperationLatestEndTmstmp,
case
when CapRqmtItmCap.RemainingCapReqOpSegSetupDurn > 0
then
case
when CapRqmtItmCap.LtstSchedldProcStrtTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.LtstSchedldProcStrtDte,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.LtstSchedldProcStrtDte, CapRqmtItmCap.LtstSchedldProcStrtTime)
end
when CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn > 0
then
case
when CapRqmtItmCap.LtstSchedldTrdwnStrtTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.LtstSchedldTrdwnStrtDte,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.LtstSchedldTrdwnStrtDte, CapRqmtItmCap.LtstSchedldTrdwnStrtTime)
end
else
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.OperationLatestEndDate, CapRqmtItmCap.OperationLatestEndTime)
end
end as OpLtstSchedldSetupEndTmstmp,
case
when CapRqmtItmCap.LtstSchedldProcStrtTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.LtstSchedldProcStrtDte,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.LtstSchedldProcStrtDte, CapRqmtItmCap.LtstSchedldProcStrtTime)
end as OpLtstSchedldProcgStrtTmstmp,
case
when CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn > 0
then
case
when CapRqmtItmCap.LtstSchedldTrdwnStrtTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.LtstSchedldTrdwnStrtDte,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.LtstSchedldTrdwnStrtDte, CapRqmtItmCap.LtstSchedldTrdwnStrtTime)
end
else
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.OperationLatestEndDate, CapRqmtItmCap.OperationLatestEndTime)
end
end as OpLtstSchedldProcgEndTmstmp,
case
when CapRqmtItmCap.LtstSchedldTrdwnStrtTime >= '240000'
then concat(dats_add_days(CapRqmtItmCap.LtstSchedldTrdwnStrtDte,1,'NULL'), '000000')
else
concat(CapRqmtItmCap.LtstSchedldTrdwnStrtDte, CapRqmtItmCap.LtstSchedldTrdwnStrtTime)
end as OpLtstSchedldTrdwnStrtTmstmp,
case CapRqmtItmCap.RemainingCapReqOpSegSetupDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegSetupDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_krueres )
end as RemCapReqOpSegSetupDurnInSecs,
case CapRqmtItmCap.RemainingCapReqOpSegProcgDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegProcgDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kbeares )
end as RemCapReqOpSegProcgDurnInSecs,
case CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrres )
end as RemCapReqOpSegTrdwnDurnInSecs,
/*case CapRqmtItmCap.ScheduledCapReqOpSegSetupDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.ScheduledCapReqOpSegSetupDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kruesol )
end as SchCapReqOpSegSetupDurnInSecs,
case CapRqmtItmCap.ScheduledCapReqOpSegProcgDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.ScheduledCapReqOpSegProcgDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kbeasol )
end as SchCapReqOpSegProcgDurnInSecs,
case CapRqmtItmCap.ScheduledCapReqOpSegTrdwnDurn
when 0
then 0
else
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.ScheduledCapReqOpSegTrdwnDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrsol )
end as SchCapReqOpSegTrdwnDurnInSecs,
*/
case
when ((CapRqmtItmCap._Order.OrderCategory = '30' and CapRqmtItmCap.RemainingCapReqOpSegSetupDurn > 0) or _OrderStatus.StatusCode = 40)
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegSetupDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_krueres )
when CapRqmtItmCap._OrderOperation.OpPlannedSetupDurn > 0
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap._OrderOperation.OpPlannedSetupDurn,
SOURCE_UNIT => CapRqmtItmCap._OrderOperation.OpPlannedSetupDurnUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as ruest )
//"Only capacity Requirement" scheduling type then refering the KBED capacity since afvv has o durations//
when ((CapRqmtItmCap._Order.BasicSchedulingType = '3' and CapRqmtItmCap._Order.OrderCategory = '10' and CapRqmtItmCap._OrderOperation.OpPlannedSetupDurn = 0 ) )
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegSetupDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrres )
// end of "Only capacity Requirement" scheduling type change
else
0
end as SetupDurationInSecs,
case
when ((CapRqmtItmCap._Order.OrderCategory = '30' and CapRqmtItmCap.RemainingCapReqOpSegProcgDurn > 0) or _OrderStatus.StatusCode = 40)
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegProcgDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_krueres )
when CapRqmtItmCap._OrderOperation.OpPlannedProcessingDurn > 0
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap._OrderOperation.OpPlannedProcessingDurn,
SOURCE_UNIT => CapRqmtItmCap._OrderOperation.OpPlannedProcessingDurnUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as bearzeit )
//"Only capacity Requirement" scheduling type then refering the KBED capacity since afvv has o durations//
when ((CapRqmtItmCap._Order.BasicSchedulingType = '3' and CapRqmtItmCap._Order.OrderCategory = '10' and CapRqmtItmCap._OrderOperation.OpPlannedProcessingDurn = 0 ) )
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegProcgDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrres )
// end of "Only capacity Requirement" scheduling type change
else
0
end as ProcgDurationInSecs,
case
when ((CapRqmtItmCap._Order.OrderCategory = '30' and CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn > 0) or _OrderStatus.StatusCode = 40)
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_krueres )
when CapRqmtItmCap._OrderOperation.OpPlannedTeardownDurn > 0
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap._OrderOperation.OpPlannedTeardownDurn,
SOURCE_UNIT => CapRqmtItmCap._OrderOperation.OpPlannedTeardownDurnUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as abrue )
//"Only capacity Requirement" scheduling type then refering the KBED capacity since afvv has o durations//
when ((CapRqmtItmCap._Order.BasicSchedulingType = '3' and CapRqmtItmCap._Order.OrderCategory = '10' and CapRqmtItmCap._OrderOperation.OpPlannedTeardownDurn = 0 ) )
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrres )
// end of "Only capacity Requirement" scheduling type change
else
0
end as TrdwnDurationInSecs,
case
when CapRqmtItmCap._OrderOperation.MinimumWaitDuration > 0
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap._OrderOperation.MinimumWaitDuration,
SOURCE_UNIT => CapRqmtItmCap._OrderOperation.MinimumWaitDurationUnit,
TARGET_UNIT => cast( 'S' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cx_liegen )
else
0
end as WaitDurationInSecs,
cast('00000000' as psttr) as PlndOrderPlannedStartDate,
case CapRqmtItmCap._Order.LastChangeDate
when '00000000'
// then concat( CapRqmtItmCap._Order.CreationDate, CapRqmtItmCap._Order.CreationTime)
then cast( dats_tims_to_tstmp( CapRqmtItmCap._Order.CreationDate, CapRqmtItmCap._Order.CreationTime , OrderTimeZone.TimeZoneID, $session.client, 'NULL' ) as abap.char(17))
// else concat( CapRqmtItmCap._Order.LastChangeDate, CapRqmtItmCap._Order.LastChangeTime)
else cast( dats_tims_to_tstmp( CapRqmtItmCap._Order.LastChangeDate, CapRqmtItmCap._Order.LastChangeTime , OrderTimeZone.TimeZoneID, $session.client, 'NULL' ) as abap.char(17))
end as LastChangeDateTime
}
where
CapRqmtItmCap._Order.ScheduledBasicStartDate <= $parameters.P_EndDate
and CapRqmtItmCap._Order.ScheduledBasicEndDate >= $parameters.P_StartDate
and(
CapRqmtItmCap.OperationIsPhase != '1' //do not pick operation headers having phases
)
and(
CapRqmtItmCap.Operation != ''
or CapRqmtItmCap._Order.OrderCategory = '30'
)
and CapRqmtItmCap.CapacityInternalID = CapRqmtItmCap._WorkCenter.CapacityInternalID
and(
CapRqmtItmCap._Order.OrderCategory = '10' and CapRqmtItmCap._OrderOperation.Sequence = '000000'
or CapRqmtItmCap._Order.OrderCategory = '40' and CapRqmtItmCap._OrderOperation.Sequence = '000000'
or CapRqmtItmCap._Order.OrderCategory = '30'
)
and CapRqmtItmCap._Capacity.CapacityIsFinite = 'X'
/*Prevent loading of confirmed orders to LC*/
and _OrderStatus.StatusCode != 30
union all select from I_CapacityRequirementItemCap as CapRqmtItmCap
left outer join I_CapacityRequirementItemCap as SupCapRqmt on SupCapRqmt.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
and SupCapRqmt.CapacityRequirementItem = CapRqmtItmCap.SuperiorOperationInternalID
and SupCapRqmt.CapacityRqmtItemCapacity = CapRqmtItmCap.CapacityRqmtItemCapacity
left outer join I_BillOfOperationsOpBasic as Routing on Routing.BillOfOperationsType = CapRqmtItmCap.BillOfOperationsType
and Routing.BillOfOperationsGroup = CapRqmtItmCap.BillOfOperationsGroup
and Routing.BOOOperationInternalID = CapRqmtItmCap.BOOOperationInternalID
and Routing.BOOOpInternalVersionCounter = CapRqmtItmCap.BOOOpInternalVersionCounter
{
key cast ( CapRqmtItmCap.PlannedOrder as pph_cfs_ordernumber)