I_DFS_UniversalAssignmentUnion

@AbapCatalog.sqlViewName: 'IDFSASSGMTUNION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Intf. View for Assignment  Union'

@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL


@VDM.viewType: #COMPOSITE

define view I_DFS_UniversalAssignmentUnion
  with parameters
    P_DfsAssgmtType       : dfs_assignment_type,
    P_DfsAssgmtSourceType : dfs_assignment_type_source
  //Structural Authorization

  as select distinct from  I_DFS_UniversalAssignmentBasic as ActiveDocument
  //  as select from           I_DFS_UniversalAssignmentBasic as ActiveDocument

  //Structural Authorization

    left outer to one join I_DFS_UniversalAssignmentDraft as DraftDocument on ActiveDocument.DfsAssgmtUUID = DraftDocument.ActiveDfsAssgmtUUID

  //  left outer to one join idfsassgmttp_d                 as DraftDocument on ActiveDocument.DfsAssgmtUUID = DraftDocument.ActiveDfsAssgmtUUID

  // association [0..1] to E_DFS_UniversalAssignmentBasic as _Extension on $projection.DfsAssgmtUUID = _Extension.DfsAssgmtUUID

  //Structural Authorization

  //  association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznForAssgmt on $projection.ForceElementOrgSrceID   = _FrcElmntWithAuthznForAssgmt.ForceElementOrgID

  //                                                                                      or $projection.ForceElementOrgTargetID = _FrcElmntWithAuthznForAssgmt.ForceElementOrgID

  association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznAssgmtSrc  on $projection.ForceElementOrgSrceID = _FrcElmntWithAuthznAssgmtSrc.ForceElementOrgID
  association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznAssgmtTrgt on $projection.ForceElementOrgTargetID = _FrcElmntWithAuthznAssgmtTrgt.ForceElementOrgID
  //Structural Authorization

{
        // Active

  key   ActiveDocument.DfsAssgmtUUID                                                       as DfsAssgmtUUID,
  key   cast ( hextobin('00000000000000000000000000000000') as sysuuid_x preserving type ) as DraftUUID,
        '1'                                                                                as DfsAssgmtActiveDraft,
        ActiveDocument.ForceElementOrgSrceUUID                                             as ForceElementOrgSrceUUID,
        ActiveDocument.ForceElementOrgTargetUUID                                           as ForceElementOrgTargetUUID,
        ActiveDocument.FlxblMatlPlngObjectUUID                                             as FlxblMatlPlngObjectUUID,
        ActiveDocument.FlxblMatlPlngObjectID                                               as FlxblMatlPlngObjectID,
        ActiveDocument.FlxblMatlPlngObjVariantUUID                                         as FlxblMatlPlngObjVariantUUID,
        ActiveDocument.FlxblMatlPlngObjVariantID                                           as FlxblMatlPlngObjVariantID,
        ActiveDocument.ForceElementOrgSrceID                                               as ForceElementOrgSrceID,
        ActiveDocument.ForceElementOrgTargetID                                             as ForceElementOrgTargetID,
        ActiveDocument.DfsCapabilityObjectUUID                                             as DfsCapabilityObjectUUID,
        ActiveDocument.DfsCapabilityObjectID                                               as DfsCapabilityObjectID,
        ActiveDocument.DfsCapabilityStatementUUID                                          as DfsCapabilityStatementUUID,
        ActiveDocument.DfsCapabilityStatementID                                            as DfsCapabilityStatementID,
        ActiveDocument.MissionEssentialTaskUUID                                            as MissionEssentialTaskUUID,
        ActiveDocument.MissionEssentialTaskID                                              as MissionEssentialTaskID,
        ActiveDocument.DfsIsAccreditingMsnEsnlTsk                                          as DfsIsAccreditingMsnEsnlTsk,
        ActiveDocument.DfsCapabilityObjectTargetUUID                                       as DfsCapabilityObjectTargetUUID,
        ActiveDocument.DfsCapabilityObjectTargetID                                         as DfsCapabilityObjectTargetID,
        ActiveDocument.Product                                                             as Product,
        ActiveDocument.FrcElmntOrgMaintCapblt                                              as FrcElmntOrgMaintCapblt,
        ActiveDocument.DfsAssgmtUsageType                                                  as DfsAssgmtUsageType,
        ActiveDocument.DfsAssgmtSourceType                                                 as DfsAssgmtSourceType,
        ActiveDocument.DfsAssgmtType                                                       as DfsAssgmtType,
        ActiveDocument.DfsAssgmtValdtyStrtDate                                             as DfsAssgmtValdtyStrtDate,
        ActiveDocument.DfsAssgmtValdtyStrtTime                                             as DfsAssgmtValdtyStrtTime,
        ActiveDocument.DfsAssgmtValdtyEndDate                                              as DfsAssgmtValdtyEndDate,
        ActiveDocument.DfsAssgmtValdtyEndTime                                              as DfsAssgmtValdtyEndTime,
        ActiveDocument.DfsAssgmtValdtyTimeZone                                             as DfsAssgmtValdtyTimeZone,
        ActiveDocument.DfsAssgmtStatusObject                                               as DfsAssgmtStatusObject,
        ActiveDocument.DfsAssgmtStatusCode                                                 as DfsAssgmtStatusCode,
        ActiveDocument.DfsAssgmtLogicalSystem                                              as DfsAssgmtLogicalSystem,
        ActiveDocument.DfsAssgmtComment                                                    as DfsAssgmtComment,
        ActiveDocument.DfsAssgmtPriority                                                   as DfsAssgmtPriority,
        ActiveDocument.DfsAssgmtCreationDateTime                                           as DfsAssgmtCreationDateTime,
        ActiveDocument.DfsAssgmtCreatedByUser                                              as DfsAssgmtCreatedByUser,
        ActiveDocument.DfsAssgmtChangeDateTime                                             as DfsAssgmtChangeDateTime,
        ActiveDocument.DfsAssgmtLastChgdByUser                                             as DfsAssgmtLastChgdByUser,
        ActiveDocument.DfsAssgmtNmbrOfAssgdMatl                                            as DfsAssgmtNmbrOfAssgdMatl,
        ActiveDocument.DfsAssgmtMinNmbrOfAssgdMatl                                         as DfsAssgmtMinNmbrOfAssgdMatl,
        ActiveDocument.DfsAssgmtMaxNmbrOfAssgdMatl                                         as DfsAssgmtMaxNmbrOfAssgdMatl,
        ActiveDocument.DfsAssgmtMaterialIndicatorCode                                      as DfsAssgmtMaterialIndicatorCode,
        ActiveDocument.DfsAssgmtCorrectionValue                                            as DfsAssgmtCorrectionValue,
        ActiveDocument.DfsAssgmtCorrectionReason                                           as DfsAssgmtCorrectionReason,
        ActiveDocument.DfsAssgmtMaterialWeightingRate                                      as DfsAssgmtMaterialWeightingRate,
        ActiveDocument.DfsAssgmtIsMandForRdnssCalc                                         as DfsAssgmtIsMandForRdnssCalc,
        ActiveDocument.DfsAssgmtIsUsedForMassMatlPlng                                      as DfsAssgmtIsUsedForMassMatlPlng,
        ActiveDocument.DfsAssgmtMaterialCategory                                           as DfsAssgmtMaterialCategory,
        ActiveDocument.DfsAssgmtInitialSupplyStatus                                        as DfsAssgmtInitialSupplyStatus,
        ActiveDocument.DfsAssgmtIsOwnProvision                                             as DfsAssgmtIsOwnProvision,
        ActiveDocument.DfsAssgmtIsDefaultAssignment                                        as DfsAssgmtIsDefaultAssignment,
        ActiveDocument.DfsAssgmtDamageType                                                 as DfsAssgmtDamageType,
        ActiveDocument.DfsAssgmtExternalProductGroup                                       as DfsAssgmtExternalProductGroup,
        ActiveDocument.DfsCapbltStmntSqncNmbr                                              as DfsCapbltStmntSqncNmbr,
        ActiveDocument.DfsCapbltStmntType                                                  as DfsCapbltStmntType,
        ActiveDocument.DfsPositionSourceUUID                                               as DfsPositionSourceUUID,
        ActiveDocument.DfsPositionSourceID                                                 as DfsPositionSourceID,
        ActiveDocument.DfsJobID                                                            as DfsJobID,
        ActiveDocument.DfsAssgmtFrcElmntTrngAssmtDte                                       as DfsAssgmtFrcElmntTrngAssmtDte,
        ActiveDocument.DfsAssgmtFrcElmntTrngAssmtBy                                        as DfsAssgmtFrcElmntTrngAssmtBy,
        ActiveDocument.DfsAssgmtSourceObjectUUID                                           as DfsAssgmtSourceObjectUUID,
        ActiveDocument.DfsAssgmtSourceObjectType                                           as DfsAssgmtSourceObjectType,
        ActiveDocument.Equipment                                                           as Equipment,
        ActiveDocument.FrcElmntOrgRotationKey                                              as FrcElmntOrgRotationKey,
        ActiveDocument.FrcElmntOrgOpExerciseID                                             as FrcElmntOrgOpExerciseID,
        ActiveDocument.FrcElmntOrgForceProviderID                                          as FrcElmntOrgForceProviderID,
        ActiveDocument.FrcElmntOrgInitialMatlSuplrID                                       as FrcElmntOrgInitialMatlSuplrID,
        ActiveDocument.FrcElmntOrgInitialPersSuplrID                                       as FrcElmntOrgInitialPersSuplrID,
        //Structural Authorization

        //        _FrcElmntWithAuthznForAssgmt.DfsIsStrucAuthznEnabled                               as DfsIsStrucAuthznEnabled,

        _FrcElmntWithAuthznAssgmtSrc.DfsIsStrucAuthznEnabled                               as DfsFrcElmntIsChangeableSrce,
        _FrcElmntWithAuthznAssgmtTrgt.DfsIsStrucAuthznEnabled                              as DfsFrcElmntIsChangeableTgt,
        //Structural Authorization

        //  Active & Draft Handling

        cast( 'X' as boole_d preserving type )                                             as IsActiveEntity,
        cast( ' ' as boole_d preserving type )                                             as HasActiveEntity,
        case
        //   when( DraftDocument.DfsAssgmtUUID is null ) then cast( ' ' as boole_d preserving type )

          when( DraftDocument.ActiveDfsAssgmtUUID is null ) then cast( ' ' as boole_d preserving type )
          else cast( 'X' as boole_d preserving type )
        end                                                                                as HasDraftEntity

}
where
      ActiveDocument.DfsAssgmtType                          = :P_DfsAssgmtType
  and ActiveDocument.DfsAssgmtSourceType                    = :P_DfsAssgmtSourceType
  //Structural Authorization

  //  and(

  //       _FrcElmntWithAuthznForAssgmt.DfsIsStrucAuthznEnabled = 'X'

  //    or ActiveDocument.ForceElementOrgSrceID                 = '00000000'

  //  )

  and(
      _FrcElmntWithAuthznAssgmtSrc.DfsIsStrucAuthznEnabled  = 'X'
    or(
      ActiveDocument.ForceElementOrgSrceID                  = '00000000'
    )
  )

  and(
      _FrcElmntWithAuthznAssgmtTrgt.DfsIsStrucAuthznEnabled = 'X'
    or(
      ActiveDocument.ForceElementOrgTargetID                = '00000000'
    )
  )
//Structural Authorization

union all select from I_DFS_UniversalAssignmentDraft as DraftDocument
  inner join          I_DraftAdministrativeData on I_DraftAdministrativeData.DraftUUID = DraftDocument.DraftUUID
//association [0..1] to E_DFS_UniversalAssignmentBasic as _Extension on $projection.DfsAssgmtUUID = _Extension.DfsAssgmtUUID

//Structural Authorization

//association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznForAssgmt on $projection.ForceElementOrgSrceID   = _FrcElmntWithAuthznForAssgmt.ForceElementOrgID

//                                                                                    or $projection.ForceElementOrgTargetID = _FrcElmntWithAuthznForAssgmt.ForceElementOrgID

association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznAssgmtSrc  on $projection.ForceElementOrgSrceID = _FrcElmntWithAuthznAssgmtSrc.ForceElementOrgID
association [1..1] to I_FrcElmntWithAuthznForAssgmt as _FrcElmntWithAuthznAssgmtTrgt on $projection.ForceElementOrgTargetID = _FrcElmntWithAuthznAssgmtTrgt.ForceElementOrgID
//Structural Authorization

{

       //         key  DraftDocument.DfsAssgmtUUID,

  key  DraftDocument.ActiveDfsAssgmtUUID                     as DfsAssgmtUUID,
  key  DraftDocument.DraftUUID                               as DraftUUID,
       case
       // Locked by somebody else

       when(I_DraftAdministrativeData.DraftIsProcessedByMe <> 'X'   and
            I_DraftAdministrativeData.DraftIsCreatedByMe   <> 'X'   and
            I_DraftAdministrativeData.InProcessByUser      <> ' ')  then '2' //2 = Locked

       // Locked by somebody else

       when (I_DraftAdministrativeData.DraftIsProcessedByMe <> 'X'  and
             I_DraftAdministrativeData.DraftIsCreatedByMe    = 'X'  and
             I_DraftAdministrativeData.InProcessByUser      <> ' ') then '2'

       when (I_DraftAdministrativeData.DraftIsProcessedByMe = 'X'   and
             I_DraftAdministrativeData.DraftIsCreatedByMe   = 'X'   and
             I_DraftAdministrativeData.InProcessByUser      = ' ' ) then '3' //3 = Draft


       when (I_DraftAdministrativeData.DraftIsProcessedByMe = ' '   and
             I_DraftAdministrativeData.DraftIsCreatedByMe   = 'X'   and
             I_DraftAdministrativeData.InProcessByUser      = ' ' ) then '3'

       when (I_DraftAdministrativeData.DraftIsProcessedByMe = 'X'   and
             I_DraftAdministrativeData.DraftIsCreatedByMe  <> 'X'   and
             I_DraftAdministrativeData.InProcessByUser     <> ' ' ) then '3'

       when (I_DraftAdministrativeData.DraftIsProcessedByMe = 'X'   and
             I_DraftAdministrativeData.DraftIsCreatedByMe   = 'X'   and
             I_DraftAdministrativeData.InProcessByUser     <> ' ' ) then '3'

        when (I_DraftAdministrativeData.DraftIsProcessedByMe <> 'X' and
              I_DraftAdministrativeData.DraftIsCreatedByMe   <> 'X' and
              I_DraftAdministrativeData.InProcessByUser      = ' ' ) then '4' //4 = Draft by Other

        else  '0'
        end                                                  as DfsAssgmtActiveDraft,
       ForceElementOrgSrceUUID                               as ForceElementOrgSrceUUID,
       ForceElementOrgTargetUUID                             as ForceElementOrgTargetUUID,
       FlxblMatlPlngObjectUUID                               as FlxblMatlPlngObjectUUID,
       FlxblMatlPlngObjectID                                 as FlxblMatlPlngObjectID,
       FlxblMatlPlngObjVariantUUID                           as FlxblMatlPlngObjVariantUUID,
       FlxblMatlPlngObjVariantID                             as FlxblMatlPlngObjVariantID,
       ForceElementOrgSrceID                                 as ForceElementOrgSrceID,
       ForceElementOrgTargetID                               as ForceElementOrgTargetID,
       DfsCapabilityObjectUUID                               as DfsCapabilityObjectUUID,
       DfsCapabilityObjectID                                 as DfsCapabilityObjectID,
       DfsCapabilityStatementUUID                            as DfsCapabilityStatementUUID,
       DfsCapabilityStatementID                              as DfsCapabilityStatementID,
       MissionEssentialTaskUUID                              as MissionEssentialTaskUUID,
       MissionEssentialTaskID                                as MissionEssentialTaskID,
       DfsIsAccreditingMsnEsnlTsk                            as DfsIsAccreditingMsnEsnlTsk,
       DfsCapabilityObjectTargetUUID                         as DfsCapabilityObjectTargetUUID,
       DfsCapabilityObjectTargetID                           as DfsCapabilityObjectTargetID,
       Product                                               as Product,
       FrcElmntOrgMaintCapblt                                as FrcElmntOrgMaintCapblt,
       DfsAssgmtUsageType                                    as DfsAssgmtUsageType,
       DfsAssgmtSourceType                                   as DfsAssgmtSourceType,
       DfsAssgmtType                                         as DfsAssgmtType,
       DfsAssgmtValdtyStrtDate                               as DfsAssgmtValdtyStrtDate,
       DfsAssgmtValdtyStrtTime                               as DfsAssgmtValdtyStrtTime,
       DfsAssgmtValdtyEndDate                                as DfsAssgmtValdtyEndDate,
       DfsAssgmtValdtyEndTime                                as DfsAssgmtValdtyEndTime,
       DfsAssgmtValdtyTimeZone                               as DfsAssgmtValdtyTimeZone,
       DfsAssgmtStatusObject                                 as DfsAssgmtStatusObject,
       DfsAssgmtStatusCode                                   as DfsAssgmtStatusCode,
       DfsAssgmtLogicalSystem                                as DfsAssgmtLogicalSystem,
       DfsAssgmtComment                                      as DfsAssgmtComment,
       DfsAssgmtPriority                                     as DfsAssgmtPriority,
       DfsAssgmtCreationDateTime                             as DfsAssgmtCreationDateTime,
       DfsAssgmtCreatedByUser                                as DfsAssgmtCreatedByUser,
       DfsAssgmtChangeDateTime                               as DfsAssgmtChangeDateTime,
       DfsAssgmtLastChgdByUser                               as DfsAssgmtLastChgdByUser,
       DfsAssgmtNmbrOfAssgdMatl                              as DfsAssgmtNmbrOfAssgdMatl,
       DfsAssgmtMinNmbrOfAssgdMatl                           as DfsAssgmtMinNmbrOfAssgdMatl,
       DfsAssgmtMaxNmbrOfAssgdMatl                           as DfsAssgmtMaxNmbrOfAssgdMatl,
       DfsAssgmtMaterialIndicatorCode                        as DfsAssgmtMaterialIndicatorCode,
       DfsAssgmtCorrectionValue                              as DfsAssgmtCorrectionValue,
       DfsAssgmtCorrectionReason                             as DfsAssgmtCorrectionReason,
       DfsAssgmtMaterialWeightingRate                        as DfsAssgmtMaterialWeightingRate,
       DfsAssgmtIsMandForRdnssCalc                           as DfsAssgmtIsMandForRdnssCalc,
       DfsAssgmtIsUsedForMassMatlPlng                        as DfsAssgmtIsUsedForMassMatlPlng,
       DfsAssgmtMaterialCategory                             as DfsAssgmtMaterialCategory,
       DfsAssgmtInitialSupplyStatus                          as DfsAssgmtInitialSupplyStatus,
       DfsAssgmtIsOwnProvision                               as DfsAssgmtIsOwnProvision,
       DfsAssgmtIsDefaultAssignment                          as DfsAssgmtIsDefaultAssignment,
       DfsAssgmtDamageType                                   as DfsAssgmtDamageType,
       DfsAssgmtExternalProductGroup                         as DfsAssgmtExternalProductGroup,
       DfsCapbltStmntSqncNmbr                                as DfsCapbltStmntSqncNmbr,
       DfsCapbltStmntType                                    as DfsCapbltStmntType,
       DfsPositionSourceUUID                                 as DfsPositionSourceUUID,
       DfsPositionSourceID                                   as DfsPositionSourceID,
       DfsJobID                                              as DfsJobID,
       DfsAssgmtFrcElmntTrngAssmtDte                         as DfsAssgmtFrcElmntTrngAssmtDte,
       DfsAssgmtFrcElmntTrngAssmtBy                          as DfsAssgmtFrcElmntTrngAssmtBy,
       DfsAssgmtSourceObjectUUID                             as DfsAssgmtSourceObjectUUID,
       DfsAssgmtSourceObjectType                             as DfsAssgmtSourceObjectType,
       Equipment                                             as Equipment,
       FrcElmntOrgRotationKey                                as FrcElmntOrgRotationKey,
       FrcElmntOrgOpExerciseID                               as FrcElmntOrgOpExerciseID,
       FrcElmntOrgForceProviderID                            as FrcElmntOrgForceProviderID,
       FrcElmntOrgInitialMatlSuplrID                         as FrcElmntOrgInitialMatlSuplrID,
       FrcElmntOrgInitialPersSuplrID                         as FrcElmntOrgInitialPersSuplrID,
       //Structural Authorization

       //       _FrcElmntWithAuthznForAssgmt.DfsIsStrucAuthznEnabled as DfsIsStrucAuthznEnabled,

       _FrcElmntWithAuthznAssgmtSrc.DfsIsStrucAuthznEnabled  as DfsFrcElmntIsChangeableSrce,
       _FrcElmntWithAuthznAssgmtTrgt.DfsIsStrucAuthznEnabled as DfsFrcElmntIsChangeableTgt,
       //Structural Authorization


       cast( ' ' as boole_d preserving type )                as IsActiveEntity,
       DraftDocument.HasActiveEntity,
       cast( ' ' as boole_d preserving type)                 as HasDraftEntity
}
where
        DraftDocument.DfsAssgmtType                           = :P_DfsAssgmtType
  and   DraftDocument.DfsAssgmtSourceType                     = :P_DfsAssgmtSourceType
  and   not(
      DraftDocument.DfsAssgmtCreatedByUser                    <> $session.user
      and DraftDocument.ActiveDfsAssgmtUUID                   = hextobin(
        '00000000000000000000000000000000'
      )
    )
  //Structural Authorization

  //  and(

  //        _FrcElmntWithAuthznForAssgmt.DfsIsStrucAuthznEnabled = 'X'

  //    or  ForceElementOrgSrceID                                = '00000000'

  //  )

  and(
        _FrcElmntWithAuthznAssgmtSrc.DfsIsStrucAuthznEnabled  = 'X'
    or(
        ForceElementOrgSrceID                                 = '00000000'
    )
  )

  and(
        _FrcElmntWithAuthznAssgmtTrgt.DfsIsStrucAuthznEnabled = 'X'
    or(
        ForceElementOrgTargetID                               = '00000000'
    )
  )
//Structural Authorization

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DFS_UNIVERSALASSIGNMENTBASIC",
"I_DFS_UNIVERSALASSIGNMENTDRAFT",
"I_DRAFTADMINISTRATIVEDATA",
"I_FRCELMNTWITHAUTHZNFORASSGMT"
],
"ASSOCIATED":
[
"I_FRCELMNTWITHAUTHZNFORASSGMT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/