@AbapCatalog.sqlViewName: 'PRSHOPSUTIL'
@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 Sub Operations for utilization
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.
Planned Start Date Time and End Date Time (in UTC) is prepared and returned.
Operations are fetched for one additional day as Start Date and End Date is in usertimezone and
all the operations might not be included as operation dates are in system timezone.
These are filtered correctly in the higher utilization calculation views where operation dates
are convered to user timezone.
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 This view is similar to P_RSHOperationsForUtilization but leaner with few fields excluded compared
n to the view P_RSHOperationsForUtilization. This is used in the table function P_RSHWrkCtrUtilization
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHOpsForUtilization
with parameters
P_StartDate : datum,
P_EndDate : datum
as select from I_OrderOperationBasic as iop
//to filter out PP-Orders etc.
inner join I_RSHOrder as iorder on iop.OrderInternalID = iorder.MaintOrderRoutingNumber
inner join I_MaintOrderOperPlanningValues on iop.OrderInternalID = I_MaintOrderOperPlanningValues.MaintOrderRoutingNumber
and iop.OrderOperationInternalID = I_MaintOrderOperPlanningValues.MaintOrderOperationCounter
inner join I_RSHAssignedWorkCenters as user_var on iop.WorkCenterInternalID = user_var.WorkCenterInternalID
and iop.WorkCenterTypeCode = 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.OperationControlProfile
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.ObjectInternalID
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 to many join I_RSHOperationStatusObject as excl_status on excl_status.StatusObject = iop.ObjectInternalID
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
// association [0..1] to I_OrderOperationBasic as _SuperiorOperation on $projection.MaintOrderRoutingNumber = _SuperiorOperation.OrderInternalID
// and $projection.SuperiorOperationInternalID = _SuperiorOperation.OrderOperationInternalID
{
key iop.OrderInternalID as MaintOrderRoutingNumber,
key iop.OrderOperationInternalID as MaintOrderOperationCounter,
// util chart grouping attributes
iop.OperationControlProfile as OperationControlKey,
iorder.MaintPriority,
iorder.MaintPriorityType,
iorder._MaintenancePriority.MaintPriorityColorCode,
iorder.MaintenanceActivityType,
iorder.MaintenanceOrderType,
user_var.WorkCenter,
iop.WorkCenterInternalID,
iop.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,
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
when 'I0002' then 20 //Due
when 'I0001' then 20 //Due
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 <= dats_add_days( $parameters.P_EndDate, 1, 'FAIL' )
// and I_MaintOrderOperPlanningValues.OpErlstSchedldExecEndDte >= dats_add_days( $parameters.P_StartDate, -1, 'FAIL' )
// and I_SchedulingType.SchedulingIsPerformedBackward = ''
// )
// or(
// I_MaintOrderOperPlanningValues.OpLtstSchedldExecStrtDte <= dats_add_days($parameters.P_EndDate, 1, 'FAIL' )
// and I_MaintOrderOperPlanningValues.OpLtstSchedldExecEndDte >= dats_add_days($parameters.P_StartDate, -1, 'FAIL' )
// and I_SchedulingType.SchedulingIsPerformedBackward = 'X'
// )
// )
(
(
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
iorder.MaintenanceOrder,
iop.Operation,
iop.OperationControlProfile,
iorder.MaintPriority,
iorder.MaintPriorityType,
iorder._MaintenancePriority.MaintPriorityColorCode,
iorder.MaintenanceActivityType,
iorder.MaintenanceOrderType,
iop.WorkCenterInternalID,
iop.WorkCenterTypeCode,
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,
iop.OperationPersonResponsible,
iop.SuperiorOperationInternalID,
iop.OrderInternalID,
iop.OrderOperationInternalID