C_MaterialCoverageIndivSegment

@AbapCatalog.sqlViewName: 'CMATCOVINDSEG'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@EndUserText.label: 'MRP Material Coverage Individual Segment'
@Search.searchable: true
@AccessControl.privilegedAssociations: ['_MRPController', '_Supplier' ]
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel: {
    usageType : { serviceQuality: #X, sizeCategory: #XL, dataClass: #MIXED }
}
@VDM.viewType: #CONSUMPTION
@Metadata.allowExtensions: true

/*+[hideWarning] { "IDS" : [ "CARDINALITY_CHECK" ]  } */
define view C_MaterialCoverageIndivSegment
  as select from I_MRPMaterialCoverage as Material

  ----------------------------------------------------------------------------------------------------------------
  -- inner join also as filter for area of responsibility. Is needed for every selection                        --
  ----------------------------------------------------------------------------------------------------------------
    inner join            I_SDMAreaOfResponsibility as SDMAreaOfResponsibility on  Material.MRPController         = SDMAreaOfResponsibility.MRPController
                                                                               and Material.MRPPlant              = SDMAreaOfResponsibility.Plant
                                                                               and SDMAreaOfResponsibility.UserID = $session.user

  association [0..*] to C_CacheSupplier                as _Supplier                     on  $projection.MRPPlant               = _Supplier.MRPPlant
                                                                                        and $projection.Material               = _Supplier.Material
                                                                                        and $projection.MRPArea                = _Supplier.MRPArea
                                                                                        and $projection.MRPPlanningSegmentType = _Supplier.MRPPlanningSegmentType
                                                                                        and $projection.MRPPlanningSegment     = _Supplier.MRPPlanningSegment

  association [0..*] to C_MRPAvailability              as _MRPAvailability              on  $projection.MRPPlant                     = _MRPAvailability.MRPPlant
                                                                                        and $projection.Material                     = _MRPAvailability.Material
                                                                                        and $projection.MRPArea                      = _MRPAvailability.MRPArea
                                                                                        and $projection.MRPPlanningSegmentType       = _MRPAvailability.MRPPlanningSegmentType
                                                                                        and $projection.MRPPlanningSegment           = _MRPAvailability.MRPPlanningSegment
                                                                                        and $projection.MaterialShortageProfile      = _MRPAvailability.MaterialShortageProfile
                                                                                        and $projection.MaterialShortageProfileCount = _MRPAvailability.MaterialShortageProfileCount

  association [0..1] to C_MRPController                as _MRPController                on  $projection.MRPPlant      = _MRPController.Plant
                                                                                        and $projection.MRPController = _MRPController.MRPController

  association [1..1] to I_MRPType                      as _MRPType                      on  $projection.MRPType = _MRPType.MRPType

  ----------------------------------------------------------------------------------------------------------------
  -- Text Fallback Associations                                                                                 --
  ----------------------------------------------------------------------------------------------------------------
  association [0..*] to I_MRPTypeText                  as _MRPTypeText                  on  $projection.MRPType = _MRPTypeText.MRPType
  association [0..1] to I_MRPTypeText                  as _MRPTypeTextPlant             on  $projection.MRPType       = _MRPTypeTextPlant.MRPType
                                                                                        and $projection.PlantLanguage = _MRPTypeTextPlant.Language

  association [0..1] to I_MatlProcurementCategoryText  as _ProcurementCategoryTextPlant on  $projection.MaterialProcurementCategory = _ProcurementCategoryTextPlant.MaterialProcurementCategory
                                                                                        and $projection.PlantLanguage               = _ProcurementCategoryTextPlant.Language

  association [0..1] to I_MatlProcurementProfileText   as _ProcurementProfileTextPlant  on  $projection.MaterialProcurementProfile = _ProcurementProfileTextPlant.MaterialProcurementProfile
                                                                                        and $projection.MRPPlant                   = _ProcurementProfileTextPlant.Plant
                                                                                        and $projection.PlantLanguage              = _ProcurementProfileTextPlant.Language

  association [0..1] to I_MRPGroup                     as _MRPGroup                     on  $projection.MRPGroup = _MRPGroup.MRPGroup
                                                                                        and $projection.MRPPlant = _MRPGroup.MRPPlant

  association [0..1] to I_MRPGroupText                 as _MRPGroupTextPlant            on  $projection.MRPGroup      = _MRPGroupTextPlant.MRPGroup
                                                                                        and $projection.MRPPlant      = _MRPGroupTextPlant.MRPPlant
                                                                                        and $projection.PlantLanguage = _MRPGroupTextPlant.Language

  association [1..1] to I_MatlProcurementCategory      as _MatlProcurementCategory      on  $projection.MaterialProcurementCategory = _MatlProcurementCategory.MaterialProcurementCategory
  association [1..1] to I_MatlProcurementProfile       as _MatlProcurementProfile       on  $projection.MaterialProcurementProfile = _MatlProcurementProfile.MaterialProcurementProfile
                                                                                        and $projection.MRPPlant                   = _MatlProcurementProfile.Plant
  association [1..1] to C_PlantVH                      as _PlantVH                      on  $projection.MRPPlant = _PlantVH.Plant
  association [0..1] to C_MRPAreaValHelp               as _MRPAreaVH                    on  $projection.MRPArea  = _MRPAreaVH.MRPArea
                                                                                        and $projection.MRPPlant = _MRPAreaVH.Plant
  association [0..1] to C_PurchasingGroupValueHelp     as _PurchasingGroupVH            on  $projection.PurchasingGroup = _PurchasingGroupVH.PurchasingGroup

  association [0..1] to I_ProdUniversalHierarchyText_2 as _HierText                     on  $projection.ProductionProductHierarchy = _HierText.ProdUnivHierarchy
                                                                                        and _HierText.ProdHierarchyValidityEndDate >= $session.user_date
                                                                                        and _HierText.Language                     = $session.system_language

  association [0..1] to I_ProductHierarchyNormalNodeT  as _HierNodeText                 on  $projection.ProductionProductHierarchyNode = _HierNodeText.ProductHierarchyNode
                                                                                        and _HierNodeText.Language                     = $session.system_language

  association [0..1] to I_MRPMaterialStatusText        as _MaterialStatusText           on  $projection.MRPValidMaterialStatus = _MaterialStatusText.MRPValidMaterialStatus
                                                                                        and _MaterialStatusText.Language       = $session.system_language

  association [0..1] to I_SafetyStockMethodVH          as _SafetyStockMethodVH          on  $projection.MRPSafetyStockMethod = _SafetyStockMethodVH.SafetyStockMethod
  // successor for I_Project

  association [0..1] to I_ProjectBasicData             as _Project                      on  $projection.ProjectInternalID = _Project.ProjectInternalID

  association [0..1] to I_MRPAreaPlanningFileEntry     as _LastMRPRun                   on  $projection.Material            = _LastMRPRun.Material
                                                                                        and $projection.MRPPlant            = _LastMRPRun.Plant
                                                                                        and $projection.MRPArea             = _LastMRPRun.MRPArea
                                                                                        and _LastMRPRun.MRPPlanningScenario = '000'

  ----------------------------------------------------------------------------------------------------------------
  -- Extension Associations                                                                                     --
  ----------------------------------------------------------------------------------------------------------------
  association [0..1] to E_Product                      as _MatExtension                 on  $projection.Material = _MatExtension.Product
  association [0..1] to E_Productplant                 as _PlantExtension               on  $projection.Material = _PlantExtension.Product
                                                                                        and $projection.MRPPlant = _PlantExtension.Plant


{
  key  Material.MRPPlant,
  key  Material.MRPArea,
  key  Material.Material,
  key  cast(Material.MRPPlanningSegmentType as plaab)                                                                                                               as MRPPlanningSegmentType,
  key  Material.MRPPlanningSegment,
  key  Material.MaterialShortageProfile,
  key  Material.MaterialShortageProfileCount,
  key  Material.FactoryCalendar, // needed as keyfield to be sure that it is filled

       concat(Material.Material,concat(Material.MRPPlant,concat(Material.MRPArea,concat(Material.MRPPlanningSegmentType,Material.MRPPlanningSegmentExternalCode)))) as MaterialSelectionIdentifier,

       _PlantVH.PlantName,
       _MRPAreaVH.MRPAreaText,
       Material.MaterialName,
       Material.PlantLanguage,
       Material.MaterialABCClassification,
       _MaterialStatusText.MRPValidMaterialStatusName,
       Material.MRPValidMaterialStatus,
       @ObjectModel: {
        virtualElement,
        readOnly: true,
        virtualElementCalculatedBy: 'ABAP:CL_PP_MRP_MAT_COV_SUP_TRA_EXIT',
        filter.transformedBy: 'ABAP:CL_MRP_CDS_SUP_FILTER_EXIT'
       }
       cast( '' as md_supplier)                                                                                                                                     as Supplier,
       @ObjectModel: {
        virtualElement,
        readOnly: true,
        virtualElementCalculatedBy: 'ABAP:CL_PP_MRP_MAT_COV_SUP_TRA_EXIT',
        filter.transformedBy: 'ABAP:CL_MRP_CDS_SUP_FILTER_EXIT'
       }
       cast ('' as suppliername_2)                                                                                                                                  as SupplierName,

       Material.NumberOfSupplier,
       Material.Customer,
       @Semantics.text: true
       Material.CustomerName,

       ---------------------------------------------------------------------------------------------------------
       -- Individual Segment                                                                             --
       ---------------------------------------------------------------------------------------------------------
       Material.MRPPlanningSegmentExternalCode,

       Material.MaterialCompIsProcuredDirectly,
       //cast(Material.ProjectNetwork as ps_pspid_edit)                                                                                                               as MatlPlngProjectNetwork,

       Material.MatlPlngProjectNetwork,
       Material.ProjectInternalID,
       case Material.MRPPlanningSegmentType
        when '22' then _Project.ProjectExternalID else ''
       end                                                                                                                                                          as Project,
       case Material.MRPPlanningSegmentType
        when '22' then _Project.ProjectDescription else ''
       end                                                                                                                                                          as ProjectDescription,
       cast( case Material.MRPPlanningSegmentType
        when '22' then Material._ProjectRespPersn.PersonWorkAgreement else ''
       end as ps_s4_vernr )                                                                                                                                         as ProjectResponsiblePerson,

       case Material.MRPPlanningSegmentType
        when '22' then Material._ProjectRespPersn._WorkforcePerson.PersonFullName else ''
       end                                                                                                                                                          as ProjectResponsiblePersonName,

       Material.WBSElementInternalID,
       Material.WBSElement,
       Material.WBSDescription,
       Material.ResponsiblePerson, -- WBSElement
       Material.ResponsiblePersonName, -- WBSElement
       Material.PlanningSegmentDocument,
       Material.PlanningSegmentDocumentItem,

       Material.UnitOfMeasureDspNmbrOfDcmls,

       ---------------------------------------------------------------------------------------------------------
       -- Availability Cart Data                                                                              --
       ---------------------------------------------------------------------------------------------------------
       Material.AvailabilityChart,
       Material.WeeklyAvailabilityChart,

       ---------------------------------------------------------------------------------------------------------
       -- Text Fields                                                                                         --
       ---------------------------------------------------------------------------------------------------------
       Material.PurchasingGroup,
       _PurchasingGroupVH.PurchasingGroupName,


       Material.MaterialType,
       Material.MaterialTypeName,
       Material.MaterialGroup,
       Material.MaterialGroupName,

       cast(concat(Material.MaterialShortageProfile, Material.MaterialShortageProfileCount) as materialshortagedefinition)                                          as MaterialShortageDefinition,
       Material.MaterialShortageDefinitionName,

       Material.MRPController,
       _MRPController.MRPControllerName,
       Material.IsMarkedForDeletion,
       Material.LowLevelCode,
       Material.HasAcceptedShortage,

       ---------------------------------------------------------------------------------------------------------
       -- Dates                                                                                               --
       ---------------------------------------------------------------------------------------------------------
       Material.MaterialReplnmtLeadDurnEndDate,
       Material.TotalReplnmtLeadDurnEndDate,
       Material.ManualPlanningHorizonEndDate,
       Material.MRPSafetyDurationEndDate,
       Material.MaterialShortageStartDate,
       Material.MaterialShortageEndDate,

       ---------------------------------------------------------------------------------------------------------
       -- Durations                                                                                           --
       ---------------------------------------------------------------------------------------------------------
       Material.MaterialPlannedDeliveryDurn,
       Material.MaterialPlannedProductionDurn,
       Material.MRPSafetyDuration,
       Material.TotalReplenishmentLeadDuration,
       Material.MaterialGoodsReceiptDuration,
       Material.PlanningTimeFenceInDays,
       Material.EvaluationHorizonInDays,
       Material.DaysOfSupplyDuration,
       Material.IntraDaySupplyDuration,
       Material.TimeTillShortageInCalendarDays,
       Material.MaterialShortageDuration,
       Material.MaterialShortageDurnInWorkdays,
       Material.DaysOfSupplyCriticality,
       Material.StockDaysSupplyDuration,
       Material.StockDaysSupplyCriticality,
       Material.StockIntraDaySupplyDuration,

       ---------------------------------------------------------------------------------------------------------
       -- Quantities                                                                                          --
       ---------------------------------------------------------------------------------------------------------
       @Semantics.unitOfMeasure: true
       @ObjectModel.foreignKey.association: '_UnitOfMeasure'
       Material.MaterialBaseUnit,
       @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
       case
         when Material.MaterialSafetyStockQty is null
          then cast ( 0 as materialsafetystockqty ) else Material.MaterialSafetyStockQty
       end                                                                                                                                                          as MaterialSafetyStockQty,
       @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
       Material.MRPAvailableStockQty,
       @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
       @UI.dataPoint.valueFormat.numberOfFractionalDigits: '_UnitOfMeasure.UnitOfMeasureDspNmbrOfDcmls'
       cast (Material.MaterialShortageQuantity as materialshortageqty)                                                                                              as MaterialShortageQuantity,
       @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
       case
          when  Material.MaterialReorderPointQty is null
            then cast( 0 as materialreorderpointqty ) else  Material.MaterialReorderPointQty
       end                                                                                                                                                          as MaterialReorderPointQty,

       ---------------------------------------------------------------------------------------------------------
       -- Time Till Shortage Filter Values                                                                    --
       ---------------------------------------------------------------------------------------------------------
       Material.HasMaterialShortageReplnmtLead,
       Material.HasShortageTotalReplnmtLead,
       Material.HasShortageManualPlanHorizon,
       Material.HasShortageMRPSafetyDuration,

       ---------------------------------------------------------------------------------------------------------
       -- Language Fallbacks                                                                                  --
       ---------------------------------------------------------------------------------------------------------
       //@ObjectModel.text.element:  [ 'MRPTypeName' ]

       Material.MRPType,
       case
         when _MRPTypeText[1: Language = $session.system_language ].MRPTypeName != ''
             then _MRPTypeText[1: Language = $session.system_language ].MRPTypeName
         when _MRPTypeTextPlant.MRPTypeName != ''
             then _MRPTypeTextPlant.MRPTypeName
         when _MRPTypeText[1: Language = 'E' ].MRPTypeName != ''
             then _MRPTypeText[1: Language = 'E' ].MRPTypeName
         else Material.MRPType
       end                                                                                                                                                          as MRPTypeName,

       Material.MaterialProcurementProfile,
       case
         when _MatlProcurementProfile._Text[1: Language = $session.system_language ].MaterialProcurementProfileName != ''
             then _MatlProcurementProfile._Text[1: Language = $session.system_language ].MaterialProcurementProfileName
         when _ProcurementProfileTextPlant.MaterialProcurementProfileName != ''
             then _ProcurementProfileTextPlant.MaterialProcurementProfileName
         when _MatlProcurementProfile._Text[1: Language = 'E' ].MaterialProcurementProfileName != ''
             then _MatlProcurementProfile._Text[1: Language = 'E' ].MaterialProcurementProfileName
         else Material.MaterialProcurementProfile
       end                                                                                                                                                          as MaterialProcurementProfileName,

       Material.MaterialProcurementCategory,
       case
         when _MatlProcurementCategory._Text[1: Language = $session.system_language ].MaterialProcurementCatName != ''
             then _MatlProcurementCategory._Text[1: Language = $session.system_language ].MaterialProcurementCatName
         when _ProcurementCategoryTextPlant.MaterialProcurementCatName != ''
             then _ProcurementCategoryTextPlant.MaterialProcurementCatName
         when _MatlProcurementCategory._Text[1: Language = 'E' ].MaterialProcurementCatName != ''
             then _MatlProcurementCategory._Text[1: Language = 'E' ].MaterialProcurementCatName
         else Material.MaterialProcurementCategory
       end                                                                                                                                                          as MaterialProcurementCatName,

       Material.MRPGroup,
       case
         when _MRPGroup._Text[1: Language = $session.system_language ].MRPGroupName != ''
             then _MRPGroup._Text[1: Language = $session.system_language ].MRPGroupName
         when _MRPGroupTextPlant.MRPGroupName != ''
             then _MRPGroupTextPlant.MRPGroupName
         when _MRPGroup._Text[1: Language = 'E' ].MRPGroupName != ''
             then _MRPGroup._Text[1: Language = 'E' ].MRPGroupName
         else Material.MRPGroup
       end                                                                                                                                                          as MRPGroupName,

       @ObjectModel.text.element:  [ 'MRPSafetyStockMethodText' ]
       Material.MRPSafetyStockMethod,
       @Semantics.text:true
       _SafetyStockMethodVH.SafetyStockMethodName                                                                                                                   as MRPSafetyStockMethodText,

       @Feature: 'BF:API_ESSENTIALSCOPE' //to hide column for onPremise, API is only active for cloud

       Material.ProductionProductHierarchy,
       @Feature: 'BF:API_ESSENTIALSCOPE' //to hide column for onPremise, API is only active for cloud

       _HierText.ProdUnivHierarchyText                                                                                                                              as ProductHierarchyText,
       @Feature: 'BF:API_ESSENTIALSCOPE' //to hide column for onPremise, API is only active for cloud

       Material.ProductionProductHierarchyNode,
       @Feature: 'BF:API_ESSENTIALSCOPE' //to hide column for onPremise, API is only active for cloud

       _HierNodeText.ProductHierarchyNodeText,

       Material.ScheduleAgreementHasReleaseDoc,
       Material.MatlMaxCoverageDurnInWorkdays,
       Material.MaterialFirstShortageDate,
       Material.MaterialFirstShortageDateQty,

       Material.IssueCount,

       ---------------------------------------------------------------------------------------------------------
       -- Number of past due elements                                                                         --
       ---------------------------------------------------------------------------------------------------------
       Material.NmbrOfPastDueOpeningDateElmnts,
       Material.NumberOfPastDueStartDateElmnts,
       Material.NumberOfPastDueEndDateElements,
       Material.NmbrOfPastDueOpeningDateElmnts + Material.NumberOfPastDueStartDateElmnts + Material.NumberOfPastDueEndDateElements                                  as NumberOfPastDueElements,

       ---------------------------------------------------------------------------------------------------------
       -- MRP Notes for Material                                                                              --
       ---------------------------------------------------------------------------------------------------------
       Material.MaterialHasMRPNote,
       Material.MRPMaterialNoteText,

       ---------------------------------------------------------------------------------------------------------
       -- Number of Rescheduling                                                                       --
       ---------------------------------------------------------------------------------------------------------
       Material.NumberOfRescheduleInElements,
       Material.NumberOfRescheduleOutElements,
       Material.NumberOfCancelProcesses,
       Material.NumberOfRescheduleInElements + Material.NumberOfRescheduleOutElements + Material.NumberOfCancelProcesses                                            as NumberOfRescheduleElements,

       ----- Multi Adress Handling -----
       @ObjectModel.readOnly:true
       @ObjectModel.virtualElement
       @ObjectModel.virtualElementCalculatedBy: 'ABAP:CL_BP_MULTI_ADDR_BF_CALC_EXIT'
       cast(' ' as abap_boolean preserving type)                                                                                                                    as BPCustMultiAddrIsActive,

       _LastMRPRun.MaterialLastMRPDateTime,

       ---------------------------------------------------------------------------------------------------------
       -- Associations                                                                                        --
       ---------------------------------------------------------------------------------------------------------
       _MRPAvailability, // association for avilibility chart popover

       _Supplier, // association for supplier popover

       _MRPController,
       Material._UnitOfMeasure

}
where
  _MRPType.MRPProcedure <> 'C' // exclude DDMRP materials