@AbapCatalog.sqlViewName: 'PRSHPASTOPKPIDET'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Past Order Operation KPI Details'
/*
CONTRACT**********************************************************************************************************************
Name: Operations from relevant work center for the supplied time frame
Specification: This view will retrieve the operations from relevant work centers and timeframe.
(Earlierst/Latest Execution End Date is used for timeframe comparison)
Operations with all status are determined. The processing status is determined as well.
Requires: Work Center assignment in I_RSHAssignedWorkCenters.
Ensures: 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 views where operation dates are convered to user timezone
(This view is used in Past Order KPI Cards logic of OVP)
Owners: PP
Contributors: DJ
Abap Unit: Yes
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHPastOperForKPIDetails
with parameters
P_StartDate : datum,
P_EndDate : datum
as select from I_RSHOrder as _MaintOrder
inner join I_OrderOperationBasic as _OrderOpBasic on _MaintOrder.MaintOrderRoutingNumber = _OrderOpBasic.OrderInternalID
inner join I_MaintOrderOperPlanningValues as _MaintOrderOpPlanValue on _OrderOpBasic.OrderInternalID = _MaintOrderOpPlanValue.MaintOrderRoutingNumber
and _OrderOpBasic.OrderOperationInternalID = _MaintOrderOpPlanValue.MaintOrderOperationCounter
// Work Center
inner join I_RSHAssignedWorkCenters as _AssignedWorkCenter on _OrderOpBasic.WorkCenterInternalID = _AssignedWorkCenter.WorkCenterInternalID
and _OrderOpBasic.WorkCenterTypeCode = _AssignedWorkCenter.WorkCenterTypeCode
// Operation Control Profile
inner join I_OperationControlProfile as _OperControlProfile on _OrderOpBasic.OperationControlProfile = _OperControlProfile.OperationControlProfile
and _OperControlProfile.OperationIsScheduled = 'X'
// Operation Status
inner join I_RSHOperationStatusObject as _OperationStatusObj on _OrderOpBasic.ObjectInternalID = _OperationStatusObj.StatusObject
// Scheduling Type
inner join I_SchedulingType as _SchedulingType on _MaintOrder.BasicSchedulingType = _SchedulingType.SchedulingType
{
key _MaintOrder.MaintOrderRoutingNumber as MaintOrderRoutingNumber,
key _OrderOpBasic.OrderOperationInternalID as OrderOperationInternalID,
_MaintOrder.MaintenanceOrder as MaintenanceOrder,
_OrderOpBasic.WorkCenterInternalID as WorkCenterInternalID,
_OrderOpBasic.Plant as Plant,
_OrderOpBasic.OperationControlProfile as OperationControlProfile,
_MaintOrder.Equipment as Equipment,
_MaintOrder.MaintObjectLocAcctAssgmtNmbr as MaintObjectLocAcctAssgmtNmbr,
// --- If scheduling backward is not on, then use earliest date otherwise latest for the remaining fields ---
case when _SchedulingType.SchedulingIsPerformedBackward = ''
then _MaintOrderOpPlanValue.OpErlstSchedldExecStrtDte
else _MaintOrderOpPlanValue.OpLtstSchedldExecStrtDte end as PlannedStartDate,
case when _SchedulingType.SchedulingIsPerformedBackward = ''
then _MaintOrderOpPlanValue.OpErlstSchedldExecStrtTme
else _MaintOrderOpPlanValue.OpLtstSchedldExecStrtTme end as PlannedStartTime,
case when _SchedulingType.SchedulingIsPerformedBackward = ''
then _MaintOrderOpPlanValue.OpErlstSchedldExecEndDte
else _MaintOrderOpPlanValue.OpLtstSchedldExecEndDte end as PlannedEndDate,
case when _SchedulingType.SchedulingIsPerformedBackward = ''
then _MaintOrderOpPlanValue.OpErlstSchedldExecEndTme
else _MaintOrderOpPlanValue.OpLtstSchedldExecEndTme end as PlannedEndTime,
max( case _OperationStatusObj.StatusCode
when 'I0010' then 40 --PART. CONFIRMED
when 'I0117' then 30 --DISPATCHED
when 'I0002' then 20 --RELEASED
when 'I0001' then 20 --CREATED
when 'I0045' then 50 --CONFIRMED
when 'I0009' then 50 --CONFIRMED
when 'I0046' then 50 --Business Closed
when 'I0013' then 60 --Deleted
when 'I0043' then 70 --Locked
end ) as ProcessingStatus,
_OrderOpBasic.OperationPersonResponsible as OperationPersonResponsible
}
where
( ( _MaintOrderOpPlanValue.OpErlstSchedldExecEndDte <= dats_add_days( $parameters.P_EndDate, 1, 'FAIL' ) and
_MaintOrderOpPlanValue.OpErlstSchedldExecEndDte >= dats_add_days( $parameters.P_StartDate, -1, 'FAIL' ) and
_SchedulingType.SchedulingIsPerformedBackward = '' )
or
( _MaintOrderOpPlanValue.OpLtstSchedldExecEndDte <= dats_add_days($parameters.P_EndDate, 1, 'FAIL' ) and
_MaintOrderOpPlanValue.OpLtstSchedldExecEndDte >= dats_add_days($parameters.P_StartDate, -1, 'FAIL' ) and
_SchedulingType.SchedulingIsPerformedBackward = 'X' ) )
group by
_MaintOrder.MaintenanceOrder,
_MaintOrder.MaintOrderRoutingNumber,
_OrderOpBasic.OrderOperationInternalID,
_OrderOpBasic.WorkCenterInternalID,
_OrderOpBasic.Plant,
_OrderOpBasic.OperationControlProfile,
_MaintOrder.Equipment,
_MaintOrder.MaintObjectLocAcctAssgmtNmbr,
_MaintOrderOpPlanValue.OpErlstSchedldExecStrtDte,
_MaintOrderOpPlanValue.OpErlstSchedldExecStrtTme,
_MaintOrderOpPlanValue.OpErlstSchedldExecEndDte,
_MaintOrderOpPlanValue.OpErlstSchedldExecEndTme,
_MaintOrderOpPlanValue.OpLtstSchedldExecStrtDte,
_MaintOrderOpPlanValue.OpLtstSchedldExecStrtTme,
_MaintOrderOpPlanValue.OpLtstSchedldExecEndDte,
_MaintOrderOpPlanValue.OpLtstSchedldExecEndTme,
_SchedulingType.SchedulingIsPerformedBackward,
_OrderOpBasic.OperationPersonResponsible