@AbapCatalog.sqlViewName: 'PRSHEXOPFRSCHD'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey: true
/*
CONTRACT**********************************************************************************************************************
Name: In process and dispatched operations from relevant work center and timeframe for scheduling
Specification: This view will retrieve the in process and dispatched operations and sub operations
from relevant work centers and timeframe.
Operations with status Confirmed,Deleted and Due are excluded. Also, opertaions with scheduling
non-relevant control key 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 for scheduling, timeframe are returned
Owners: CK
Contributors: AN,PRP
Unit Test required Y/N: Yes
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHExOpsForScheduling
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
--- Filter out the operations with control key which is not relevant for scheduling
inner join I_OperationControlProfile as OperationControlProfile on OperationControlProfile.OperationControlProfile = iop.OperationControlKey
and OperationControlProfile.OperationIsScheduled = 'X'
inner join I_RSHAssignedWorkCenters as user_var on iop.OperationWorkCenterInternalID = user_var.WorkCenterInternalID
and iop.OperationWorkCenterTypeCode = user_var.WorkCenterTypeCode
--- Filter Dispatched and Partially Confirmed Operation Status
inner join I_StatusObjectStatusBasic as incl_status on incl_status.StatusObject = iop.MaintOrderOperationInternalID
and incl_status.StatusIsInactive = ''
and (
incl_status.StatusCode = 'I0010' //PART. CONFIRMED
or incl_status.StatusCode = 'I0117' //DISPATCHED
)
--- Filter Out Operation Status: Delete or Confirmed Status
left outer join I_StatusObjectStatusBasic as excl_status on excl_status.StatusObject = iop.MaintOrderOperationInternalID
and excl_status.StatusIsInactive = ''
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,
@Semantics.quantity.unitOfMeasure: 'OperationPlannedWorkUnit'
I_MaintOrderOperPlanningValues.OperationPlannedWork as OperationPlannedWork,
@Semantics.unitOfMeasure:true
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,
// Convert planned start and end of operations to UTC
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then
case when I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtTme = '240000'
then dats_tims_to_tstmp( dats_add_days(I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtDte,1,'FAIL'), '000000',abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
else
dats_tims_to_tstmp( I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtDte, I_MaintOrderOperPlanningValues.OpErlstSchedldExecStrtTme,
abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
end
else
case when I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtTme = '240000'
then dats_tims_to_tstmp( dats_add_days(I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte,1,'FAIL'), '000000',abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
else
dats_tims_to_tstmp( I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte, I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtTme,
abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
end
end as PlannedStartDateTime,
case when I_SchedulingType.SchedulingIsPerformedBackward = ''
then
case when I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndTme = '240000'
then dats_tims_to_tstmp( dats_add_days(I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte,1,'FAIL'), '000000',abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
else
dats_tims_to_tstmp( I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte, I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndTme,
abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
end
else
case when I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndTme = '240000'
then dats_tims_to_tstmp( dats_add_days(I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte,1,'FAIL'), '000000',abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
else
dats_tims_to_tstmp( I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte, I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndTme,
abap_system_timezone( $session.client,'NULL'),$session.client,'NULL')
end
end as PlannedEndDateTime,
max( case incl_status.StatusCode
when 'I0010' then 40 //In Process
when 'I0117' then 30 //Dispatched
end ) as ProcessingStatus,
iop.OperationPersonResponsible as OperationPersonResponsible
}
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
// MaintSchedSimlnUUID,
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,
I_SchedulingType.SchedulingIsPerformedBackward,
OperationPersonResponsible