@AbapCatalog.sqlViewName: 'PRSHOPUTIL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Operations and Sub Operations for utilization'
/*
CONTRACT**********************************************************************************************************************
Name: Operations and Suboperations from relevant work center and timeframe
Specification: This view will retrieve the operations and sub operations from relevant work centers and timeframe.
Operations with status Confirmed and Deleted are excluded. The processing status is determined.
The planned dates and times will also be retrieved, based on the scheduling type.
Requires: Work Center assignment in I_RSHAssignedWorkCenters.
Ensures: Operations and sub operations from relevant work centers, timeframe are returned
Owners: CK
Contributors: AN,PRP
Unit Test required Y/N: Yes
Additional comments 480ms to return 5000 records / 46400 records in a 4 week period.
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHOperationsForUtilization
with parameters
P_StartDate : datum,
P_EndDate : datum
as select from I_MaintOrderOperAndSubOper as iop
inner join I_MaintenanceOrder as iorder on iorder.MaintenanceOrder = iop.MaintenanceOrder
inner join I_MaintOrderOperPlanningValues on iop.MaintOrderRoutingNumber = I_MaintOrderOperPlanningValues.MaintOrderRoutingNumber
and iop.MaintOrderOperationCounter = I_MaintOrderOperPlanningValues.MaintOrderOperationCounter
inner join I_RSHAssignedWorkCenters as user_var on iop.OperationWorkCenterInternalID = user_var.WorkCenterInternalID
and iop.OperationWorkCenterTypeCode = user_var.WorkCenterTypeCode
//Filter out the operations with control key which is not relevant for scheduling
inner join I_OperationControlProfile as operation_controlprofile on operation_controlprofile.OperationControlProfile = iop.OperationControlKey
and operation_controlprofile.OperationIsScheduled = 'X'
--- Filter Due, Dispatched and Partially Confirmed Operation Status
inner join I_RSHOperationStatusObject as incl_status on incl_status.StatusObject = iop.MaintOrderOperationInternalID
and(
incl_status.StatusCode = 'I0010' //PART. CONFIRMED
or incl_status.StatusCode = 'I0117' //DISPATCHED
or incl_status.StatusCode = 'I0002' //RELEASED
or incl_status.StatusCode = 'I0001' //CREATED
)
--- Filter Out Operation Status: Delete or Confirmed Status
left outer join I_RSHOperationStatusObject as excl_status on excl_status.StatusObject = iop.MaintOrderOperationInternalID
and(
excl_status.StatusCode = 'I0013' //DELETED
or excl_status.StatusCode = 'I0045' //TECHN. COMPLETED
or excl_status.StatusCode = 'I0009' //CONFIRMED
or excl_status.StatusCode = 'I0043' //LOCKED
or excl_status.StatusCode = 'I0046' //CLOSED (Business Closed)
)
association [1..1] to I_SchedulingType on iorder.BasicSchedulingType = I_SchedulingType.SchedulingType
{
key iop.MaintenanceOrder,
key iop.MaintenanceOrderOperation,
key iop.MaintenanceOrderSubOperation,
// util chart grouping attributes
iop.OperationControlKey,
iorder.MaintPriority,
iorder.MaintPriorityType,
iorder._MaintenancePriority.MaintPriorityColorCode,
iorder.MaintenanceActivityType,
iorder.MaintenanceOrderType,
user_var.WorkCenter,
iop.OperationWorkCenterInternalID as WorkCenterInternalID,
iop.OperationWorkCenterTypeCode as WorkCenterTypeCode,
user_var.WorkCenterCategoryCode as WorkCenterCategoryCode,
user_var.CapacityInternalID,
iop.Plant as Plant,
I_MaintOrderOperPlanningValues.OperationPlannedWork,
I_MaintOrderOperPlanningValues.OperationPlannedWorkUnit,
// --- If scheduling backward is not on, then use earliest date otherwise latest for the remaining fields ---
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtDte
else I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte end as PlannedStartDate,
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtTme
else I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtTme end as PlannedStartTime,
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte
else I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte end as PlannedEndDate,
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndTme
else I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndTme end as PlannedEndTime,
iorder.LatestAcceptableCompletionDate,
max( case incl_status.StatusCode
when 'I0010' then 40 //In Process
when 'I0117' then 30 //Dispatched
when 'I0002' then 20 //Due
when 'I0001' then 20 //Due
end ) as ProcessingStatus,
iop.OperationPersonResponsible as OperationPersonResponsible,
iop.MaintOperationExecStageCode as MaintOperationExecStageCode,
iop.MaintOrdOpProcessSubPhaseCode as MaintOrdOpProcessSubPhaseCode
}
where
excl_status.StatusObject is null
and
// only look at operations that are in the current timeframe either with earliest or latest dates depending on backward scheduling or not.
(
(
I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtDte <= $parameters.P_EndDate
and I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte >= $parameters.P_StartDate
and I_SchedulingType.SchedulingIsPerformedBackward = ''
)
or(
I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte <= $parameters.P_EndDate
and I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte >= $parameters.P_StartDate
and I_SchedulingType.SchedulingIsPerformedBackward = 'X'
)
)
group by
iop.MaintenanceOrder,
iop.MaintenanceOrderOperation,
iop.MaintenanceOrderSubOperation,
iop.OperationControlKey,
iorder.MaintPriority,
iorder.MaintPriorityType,
iorder._MaintenancePriority.MaintPriorityColorCode,
iorder.MaintenanceActivityType,
iorder.MaintenanceOrderType,
iop.OperationWorkCenterInternalID,
iop.OperationWorkCenterTypeCode,
user_var.WorkCenter,
user_var.WorkCenterCategoryCode,
user_var.CapacityInternalID,
iop.Plant,
I_MaintOrderOperPlanningValues.OperationPlannedWork,
I_MaintOrderOperPlanningValues.OperationPlannedWorkUnit,
I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtDte,
I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtTme,
I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte,
I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndTme,
I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte,
I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtTme,
I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte,
I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndTme,
iorder.LatestAcceptableCompletionDate,
I_SchedulingType.SchedulingIsPerformedBackward,
iop.OperationPersonResponsible,
iop.MaintOperationExecStageCode,
iop.MaintOrdOpProcessSubPhaseCode