@AbapCatalog.sqlViewName: 'PRSHASGNPDDR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE
/*
CONTRACT**********************************************************************************************************************
Name: Per Day draft or active assignment details
Specification: This View returns Draft assignment if exist else active record
It also adds records for per day assignment create case where no per day active exists
Requires: No
Ensures: Prepares draft assignment data for aggregation and utilization calculation in TBUI
Owners: MY
Unit Test required Y/N: Covered in Hierarchy test of P_RSHAssgmtWithDraftPerMonth & P_RSHResourceUtilznWithDraft
Additional comments None
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHAssignmentWithDraft
as select from I_RSHProjectAssignment as _ProjectAssignment
inner join I_RSHProjectAssignmentPerDay as _PerDay on _ProjectAssignment.ProjAssignmentUUID = _PerDay.ProjAssignmentUUID
// Required in case DRAFT exists for assignment but DOES NOT exist for Per day either duration is reduced or partial delete.
left outer join I_RSHAssignmentDrafts as _DraftFound on _PerDay.ProjAssignmentUUID = _DraftFound.ProjAssignmentUUID
left outer join I_RSHAssignmentDrafts as _PerDayDraft on _PerDay.ProjAssignmentUUID = _PerDayDraft.ProjAssignmentUUID
and _PerDay.ProjAssignmentDate = _PerDayDraft.ProjAssignmentDate
{
key _PerDay.ProjAssignmentPerDayUUID,
_ProjectAssignment.EmploymentInternalID,
_PerDay.ProjAssignmentUUID,
_PerDay.ProjAssignmentDate,
case
when _DraftFound.ProjAssignmentPerDayUUID is null
and _PerDayDraft.ProjAssignmentPerDayUUID is null
then
_PerDay.ProjAssignmentDuration
else
_PerDayDraft.ProjAssignmentDuration
end as ProjAssignmentDuration,
_PerDay.ProjAssignmentDuration as ProjAssignmentActiveDuration,
case
when _DraftFound.ProjAssignmentPerDayUUID is null
and _PerDayDraft.ProjAssignmentPerDayUUID is null
then
0
else
_PerDayDraft.ProjAssignmentDuration
end as ProjAssignmentDraftDuration,
_PerDay.ProjAssignmentDurationUnit,
_PerDay.ProjAssignmentDurationInHours
}
union
// Case where monthly or weekly draft is created as a new entry on the TBUI
// Performance is better with UNION as 2 independent select had time of 140 but union was 127
select from I_RSHProjectAssignment as _ProjectAssignment
inner join I_RSHAssignmentDrafts as _PerDayDraft on _ProjectAssignment.ProjAssignmentUUID = _PerDayDraft.ProjAssignmentUUID
left outer join I_RSHProjectAssignmentPerDay as _PerDayActive on _PerDayDraft.ProjAssignmentUUID = _PerDayActive.ProjAssignmentUUID
and _PerDayDraft.ProjAssignmentDate = _PerDayActive.ProjAssignmentDate
{
key _PerDayDraft.ProjAssignmentPerDayUUID as ProjAssignmentPerDayUUID,
_ProjectAssignment.EmploymentInternalID as EmploymentInternalID,
_PerDayDraft.ProjAssignmentUUID as ProjAssignmentUUID,
_PerDayDraft.ProjAssignmentDate as ProjAssignmentDate,
_PerDayDraft.ProjAssignmentDuration as ProjAssignmentDuration,
_PerDayActive.ProjAssignmentDuration as ProjAssignmentActiveDuration,
_PerDayDraft.ProjAssignmentDuration as ProjAssignmentDraftDuration,
_PerDayDraft.ProjAssignmentDurationUnit as ProjAssignmentDurationUnit,
_PerDayDraft.ProjAssignmentDurationInHours as ProjAssignmentDurationInHours
}
where
_PerDayActive.ProjAssignmentUUID is null