P_DDMaterialSOS

@AbapCatalog.sqlViewName: 'PDDMATSOS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
//@EndUserText.label: 'Source of Supply for DDR Materials'

@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #XXL, dataClass: #MIXED}
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_DDMaterialSOS
  with parameters
    P_RequirementDate : dats
  as select distinct from I_ProductPlantMRPArea    as _mat
    inner join            I_PurchasingDocumentItem as _ekpo    on  _ekpo.Material = _mat.Material
                                                               and _ekpo.Plant    = _mat.Plant

    inner join            I_PurchasingDocument     as _ekko    on _ekpo.PurchasingDocument = _ekko.PurchasingDocument
    left outer join       t460a                    as _t460a   on  _mat.Plant                      = _t460a.werks
                                                               and _mat.MaterialProcurementProfile = _t460a.sobsl

    left outer join       I_MPPurchasingSourceItem as _eord    on  _eord.Material                        = _mat.Material
                                                               and _eord.Plant                           = _mat.Plant
                                                               and (
                                                                  _eord.Supplier                         = _ekko.Supplier
                                                                  and _eord.PurchasingOrganization       = _ekko.PurchasingOrganization
                                                                  and _eord.PurchaseOutlineAgreement     = _ekpo.PurchasingDocument
                                                                  and _eord.PurchaseOutlineAgreementItem = _ekpo.PurchasingDocumentItem
                                                                )
                                                               and _ekko.ValidityStartDate               <= $parameters.P_RequirementDate
                                                               and _ekko.ValidityEndDate                 >= $parameters.P_RequirementDate
                                                               and (
                                                                  (
                                                                    _eord.ValidityStartDate              <= $parameters.P_RequirementDate
                                                                    and _eord.ValidityEndDate            >= $parameters.P_RequirementDate
                                                                    and _eord.SourceOfSupplyIsBlocked    = 'X'
                                                                  )
                                                                  or _eord.SourceOfSupplyIsBlocked       = ' '
                                                                )
    left outer join       I_MPPurchasingSourceItem as _eordsup on  _eordsup.Material                     = _mat.Material
                                                               and _eordsup.Plant                        = _mat.Plant
                                                               and _eordsup.Supplier                     = _ekko.Supplier
                                                               and _eordsup.PurchaseOutlineAgreement     = ' '
                                                               and _eordsup.PurchasingOrganization       = _ekko.PurchasingOrganization
                                                               and _ekko.ValidityStartDate               <= $parameters.P_RequirementDate
                                                               and _ekko.ValidityEndDate                 >= $parameters.P_RequirementDate
                                                               and (
                                                                  (
                                                                    _eordsup.ValidityStartDate           <= $parameters.P_RequirementDate
                                                                    and _eordsup.ValidityEndDate         >= $parameters.P_RequirementDate
                                                                    and _eordsup.SourceOfSupplyIsBlocked = 'X'
                                                                  )
                                                                  or _eordsup.SourceOfSupplyIsBlocked    = ' '
                                                                )
  association [1..1] to I_Supplier as _lfa1 on _ekko.Supplier = _lfa1.Supplier
{
  key _mat.Material,
  key _mat.Plant,
  key _mat.MRPArea,
  key cast(_ekko.PurchasingDocument as abap.char(12) )         as SourceDocument,
  key _ekpo.PurchasingDocumentItem                             as SourceDocumentItem,
      _ekpo.PurchasingDocumentCategory                         as SourceDocumentType,

      case
      when _eord.SourceListRecord is not null or _eordsup.SourceListRecord is not null
      then 'srclist'
      else
      case _ekko.PurchasingDocumentCategory
      when 'L'
      then 'scag'
      else 'contract'
      end
      end                                                      as SOSTypeDescription,

      _ekko.PurchasingOrganization,

      case
      when _ekko.PurchasingGroup is null or _ekko.PurchasingGroup = ' ' or _ekko.PurchasingGroup = ''
      then _mat.PurchasingGroup
      else _ekko.PurchasingGroup
      end                                                      as PurchasingGroup,

      case
      when _eord.ValidityEndDate is not null
      then _eord.ValidityEndDate
      when _eordsup.ValidityEndDate is not null
      then _eordsup.ValidityEndDate
      else _ekko.ValidityEndDate
      end                                                      as ValidityEndDate,

      case
      when _eord.ValidityStartDate is not null
      then _eord.ValidityStartDate
      when _eordsup.ValidityStartDate is not null
      then _eordsup.ValidityStartDate
      else
      case
      when _ekko.ValidityStartDate <> '00000000'
      then _ekko.ValidityStartDate
      else cast($session.system_date as abap.dats)
      end
      end                                                      as ValidityStartDate,
      @Semantics.quantity.unitOfMeasure: 'UoM'
      cast('0' as abap.dec(13,3))                              as MaterialMinLotSizeQuantity,
      @Semantics.quantity.unitOfMeasure: 'UoM'
      cast('9999999999' as abap.dec(13,3))                     as MaterialMaxLotSizeQuantity,
      case
      when _t460a.sobes = '7'
      then coalesce( _t460a.wrk02, _ekko.SupplyingPlant )
      else cast('' as werks_d)
      end                                                      as SupplyingPlant,
      _ekko.Supplier,
      _lfa1.SupplierName,
      case
      when _t460a.sobes = '7' and _ekko.PurchasingDocumentCategory = 'L' and _t460a.wrk02 is not null
      then 'T'
      else 'B'
      end                                                      as SOSType,
      'BA'                                                     as MRPElementCategory,
      cast('' as abap.char( 1 ))                               as DefaultSOS,
      cast('' as plnty)                                        as BillOfOperationsType,
      _ekpo.StorageLocation                                    as StorageLocation,
      case
      when _ekpo.OrderQuantityUnit <> ''
      then _ekpo.OrderQuantityUnit
      else _mat._Material.BaseUnit
      end                                                      as UoM
}
where
  (
          _ekko.PurchasingDocumentCategory     =  'K'
    or    _ekko.PurchasingDocumentCategory     =  'L'
  )
  and(
    (
          _eord.ValidityStartDate              <= $parameters.P_RequirementDate
      and _eord.SourceListRecord               is not null
      and _eord.SourceOfSupplyIsBlocked        =  ' '
    )
    or(
          _eordsup.ValidityStartDate           <= $parameters.P_RequirementDate
      and _eordsup.SourceListRecord            is not null
      and _eordsup.SourceOfSupplyIsBlocked     =  ' '
      and _eord.SourceListRecord               is null
    )
    or(
          _ekko.ValidityStartDate              <= $parameters.P_RequirementDate
      and _eord.SourceListRecord               is null
      and _eordsup.SourceListRecord            is null
    )
  )
  and(
    (
          _eord.ValidityEndDate                >= $parameters.P_RequirementDate
      and _eord.SourceListRecord               is not null
      and _eord.SourceOfSupplyIsBlocked        =  ' '
    )
    or(
          _eordsup.ValidityEndDate             >= $parameters.P_RequirementDate
      and _eordsup.SourceListRecord            is not null
      and _eordsup.SourceOfSupplyIsBlocked     =  ' '
      and _eord.SourceListRecord               is null
    )
    or(
          _ekko.ValidityEndDate                >= $parameters.P_RequirementDate
      and _eord.SourceListRecord               is null
      and _eordsup.SourceListRecord            is null
    )
  )
  and(
          _ekko.PurchasingProcessingStatus     =  '02'
    or    _ekko.PurchasingProcessingStatus     =  '05'
  )
  and     _ekko.Supplier                       <> ' '
  and(
          _lfa1.PurchasingIsBlocked            is null
    or    _lfa1.PurchasingIsBlocked            =  ''
    or    _lfa1.PurchasingIsBlocked            =  ' '
  )
  and(
          _lfa1.PurchasingIsBlocked            is null
    or    _lfa1.PurchasingIsBlocked            =  ''
    or    _lfa1.PurchasingIsBlocked            =  ' '
  )
  and(
          _lfa1.IsBusinessPurposeCompleted     is null
    or    _lfa1.IsBusinessPurposeCompleted     =  ''
    or    _lfa1.IsBusinessPurposeCompleted     =  ' '
  )
  and(
          _ekpo.PurchasingDocumentDeletionCode =  ''
    or    _ekpo.PurchasingDocumentDeletionCode =  ' '
    or    _ekpo.PurchasingDocumentDeletionCode is null
  )
  and(
          _ekko.PurchasingDocumentDeletionCode =  ''
    or    _ekko.PurchasingDocumentDeletionCode =  ' '
    or    _ekko.PurchasingDocumentDeletionCode is null
  )
  and(
          _eordsup.SourceOfSupplyIsBlocked     =  ''
    or    _eordsup.SourceOfSupplyIsBlocked     =  ' '
    or    _eordsup.SourceOfSupplyIsBlocked     is null
  )
  and(
          _lfa1.DeletionIndicator              is null
    or    _lfa1.DeletionIndicator              =  ''
    or    _lfa1.DeletionIndicator              =  ' '
  )
  and(
          _mat.ProcurementType                 =  'F'
    or    _mat.ProcurementType                 =  'X'
  )
  and not (
          _mat.PurchasingGroup                 is null
    or    _mat.PurchasingGroup                 = ''
    or    _mat.PurchasingGroup                 = ' '
  )
union all select distinct from I_ProductPlantMRPArea       as _mat
  inner join                   I_PurgInfoRecordWithOrgData as _eina on  _mat.Material = _eina.Material
                                                                    and _mat.Plant    = _eina.Plant
  left outer join              I_MPPurchasingSourceItem    as _eord on  _eord.Material                     = _mat.Material
                                                                    and _eord.Plant                        = _mat.Plant
                                                                    and _eord.Supplier                     = _eina.Supplier
                                                                    and _eord.PurchaseOutlineAgreement     = ' '
                                                                    and _eord.PurchasingOrganization       = _eina.PurchasingOrganization
                                                                    and (
                                                                       _eina.AvailabilityStartDate         <= $parameters.P_RequirementDate
                                                                       or _eina.AvailabilityStartDate      = '00000000'
                                                                     )
                                                                    and (
                                                                       _eina.AvailabilityEndDate           >= $parameters.P_RequirementDate
                                                                       or _eina.AvailabilityEndDate        = '00000000'
                                                                     )
                                                                    and (
                                                                       (
                                                                         _eord.ValidityStartDate           <= $parameters.P_RequirementDate
                                                                         and _eord.ValidityEndDate         >= $parameters.P_RequirementDate
                                                                         and _eord.SourceOfSupplyIsBlocked = 'X'
                                                                       )
                                                                       or _eord.SourceOfSupplyIsBlocked    = ' '
                                                                     )
association [0..1] to I_PurgInfoRecdOrgPlantData as _eine on  _eina.PurchasingInfoRecord         = _eine.PurchasingInfoRecord
                                                          and _eina.PurchasingOrganization       = _eine.PurchasingOrganization
                                                          and _eina.PurchasingInfoRecordCategory = _eine.PurchasingInfoRecordCategory
                                                          and _eina.Plant                        = _eine.Plant
association [1..1] to I_Supplier                 as _lfa1 on  _eina.Supplier = _lfa1.Supplier
{
  key _mat.Material,
  key _mat.Plant,
  key _mat.MRPArea,
  key _eina.PurchasingInfoRecord                 as SourceDocument,
  key cast('00000' as abap.numc( 5 ))            as SourceDocumentItem,
      cast('I' as abap.char( 1 ))                as SourceDocumentType,
      'infrec'                                   as SOSTypeDescription,
      _eina.PurchasingOrganization,

      case
      when _eina.PurchasingGroup is null or _eina.PurchasingGroup = ' ' or _eina.PurchasingGroup = ''
      then _mat.PurchasingGroup
      else _eina.PurchasingGroup
      end                                        as PurchasingGroup,

      case
      when _eord.ValidityEndDate is not null
      then _eord.ValidityEndDate
      else
      case
      when _eina.AvailabilityEndDate <> '00000000'
      then _eina.AvailabilityEndDate
      else cast('99991231' as abap.dats)
      end
      end                                        as validityenddate,

      case
      when _eord.ValidityStartDate is not null
      then _eord.ValidityStartDate
      else
      case
      when _eina.AvailabilityStartDate <> '00000000'
      then _eina.AvailabilityStartDate
      else cast($session.system_date as dats)
      end
      end                                        as validitystartdate,

      _eine.MinimumPurchaseOrderQuantity         as MaterialMinLotSizeQuantity,
      case
      when _eine.MaximumOrderQuantity <> 0
      then  _eine.MaximumOrderQuantity
      else cast('9999999999' as abap.dec(13,3))
      end                                        as MaterialMaxLotSizeQuantity,
      ''                                         as SupplyingPlant,
      _eina.Supplier,
      _lfa1.SupplierName,
      'B'                                        as SOSType,
      'BA'                                       as MRPElementCategory,
      cast('' as abap.char( 1 ))                 as DefaultSOS,
      cast('' as plnty)                          as BillOfOperationsType,
      cast('' as lgort_d)                        as StorageLocation,
      case
      when _eina.PurgDocOrderQuantityUnit <> ''
      then _eina.PurgDocOrderQuantityUnit
      else _mat._Material.BaseUnit
      end                                        as UoM
}
where
  (
    (
           _eord.ValidityStartDate          <= $parameters.P_RequirementDate
      and  _eord.SourceListRecord           is not null
    )
    or(
      (
           _eina.AvailabilityStartDate      <= $parameters.P_RequirementDate
        or _eina.AvailabilityStartDate      =  '00000000'
      )
      and  _eord.SourceListRecord           is null
    )
  )
  and(
    (
           _eord.ValidityEndDate            >= $parameters.P_RequirementDate
      and  _eord.SourceListRecord           is not null
    )
    or(
      (
           _eina.AvailabilityEndDate        >= $parameters.P_RequirementDate
        or _eina.AvailabilityEndDate        =  '00000000'
      )
      and  _eord.SourceListRecord           is null
    )
  )
  and(
           _eina.IsDeleted                  is null
    or     _eina.IsDeleted                  =  ''
    or     _eina.IsDeleted                  =  ' '
  )
  and      _eina.Supplier                   <> ' '
  and(
           _lfa1.PurchasingIsBlocked        is null
    or     _lfa1.PurchasingIsBlocked        =  ''
    or     _lfa1.PurchasingIsBlocked        =  ' '
  )
  and(
           _lfa1.PurchasingIsBlocked        is null
    or     _lfa1.PurchasingIsBlocked        =  ''
    or     _lfa1.PurchasingIsBlocked        =  ' '
  )
  and(
           _lfa1.IsBusinessPurposeCompleted is null
    or     _lfa1.IsBusinessPurposeCompleted =  ''
    or     _lfa1.IsBusinessPurposeCompleted =  ' '
  )
  and(
           _eord.SourceOfSupplyIsBlocked    =  ''
    or     _eord.SourceOfSupplyIsBlocked    =  ' '
    or     _eord.SourceOfSupplyIsBlocked    is null
  )
  and(
           _lfa1.DeletionIndicator          is null
    or     _lfa1.DeletionIndicator          =  ''
    or     _lfa1.DeletionIndicator          =  ' '
  )
  and(
           _mat.ProcurementType             =  'F'
    or     _mat.ProcurementType             =  'X'
  )
  and      not(
         _mat.PurchasingGroup               is null
         or     _mat.PurchasingGroup        =  ''
         or     _mat.PurchasingGroup        =  ' '
       )
union all select from I_ProductPlantMRPArea as _mat
  inner join          I_ProductionVersion   as _pv    on  _mat.Material = _pv.Material
                                                      and _mat.Plant    = _pv.Plant
  left outer join     t460a                 as _t460a on  _mat.Plant                      = _t460a.werks
                                                      and _mat.MaterialProcurementProfile = _t460a.sobsl
                                                      and _t460a.sobes                    = 'P'
{
  key _mat.Material,
  key _mat.Plant,
  key _mat.MRPArea,
  key ProductionVersion                           as sourceDocument,
  key cast('00000' as abap.numc( 5 ))             as SourceDocumentItem,
      cast('P' as abap.char( 1 ))                 as SourceDocumentType,
      'prdver'                                    as SOSTypeDescription,
      ''                                          as PurchasingOrganization,
      ''                                          as PurchasingGroup,
      ValidityEndDate,
      ValidityStartDate,
      MaterialMinLotSizeQuantity                  as MaterialMinLotSizeQuantity,
      case
      when MaterialMaxLotSizeQuantity <> 0
      then MaterialMaxLotSizeQuantity
      else cast('9999999999' as abap.dec(13,3))
      end                                         as MaterialMaxLotSizeQuantity,
      ''                                          as SupplyingPlant,
      ''                                          as supplier,
      ''                                          as suppliername,
      'M'                                         as SOSType,
      'PA'                                        as MRPElementCategory,
      cast('' as abap.char( 1 ))                  as DefaultSOS,
      _pv.BillOfOperationsType                    as BillOfOperationsType,
      _pv.ReceivingStorageLocation                as StorageLocation,
      _mat._Material.BaseUnit                      as UoM
}
where
       _t460a.werks              is null
  and  ValidityStartDate         <= $parameters.P_RequirementDate
  and  ValidityEndDate           >= $parameters.P_RequirementDate
  and  ProductionVersionIsLocked = ' '
  and(
       _mat.ProcurementType      = 'E'
    or _mat.ProcurementType      = 'X'
  )
union all select from I_ProductPlantMRPArea    as _mat
  inner join          t460a                    as _t460a   on  _mat.Plant                      = _t460a.werks
                                                           and _mat.MaterialProcurementProfile = _t460a.sobsl
  left outer join     I_MPPurchasingSourceItem as _eord    on  _eord.Material                     = _mat.Material
                                                           and _eord.Plant                        = _mat.Plant
                                                           and _eord.SupplyingPlant               = _t460a.wrk02
                                                           and _eord.PurchaseOutlineAgreement     = ' '
                                                           and (
                                                              (
                                                                _eord.ValidityStartDate           <= $parameters.P_RequirementDate
                                                                and _eord.ValidityEndDate         >= $parameters.P_RequirementDate
                                                                and _eord.SourceOfSupplyIsBlocked = 'X'
                                                              )
                                                              or _eord.SourceOfSupplyIsBlocked    = ' '
                                                            )
  left outer join     I_MRPArea                as _MRPArea on  _mat.MRPArea             = _MRPArea.MRPArea
                                                           and _mat.Plant               = _MRPArea.MRPAreaPlant
                                                           and _MRPArea.MRPAreaCategory = '02'
{
  key    _mat.Material,
  key    _mat.Plant,
  key    _mat.MRPArea,
  key    _t460a.wrk02                                                         as sourceDocument,
  key    cast('00000' as abap.numc( 5 ))                                      as SourceDocumentItem,
         cast('S' as abap.char( 1 ))                                          as SourceDocumentType,
         case
         when _mat.Plant = _t460a.wrk02
         then 'spk45'
         else 'spk40'
         end                                                                  as SOSTypeDescription,
         ''                                                                   as PurchasingOrganization,
         _mat.PurchasingGroup                                                 as PurchasingGroup,
         case
         when _eord.ValidityEndDate is not null
         then _eord.ValidityEndDate
         else
         cast('99991231' as abap.dats)
         end                                                                  as validityenddate,

         case
         when _eord.ValidityStartDate is not null
         then _eord.ValidityStartDate
         else
         cast($session.system_date as abap.dats)
         end                                                                  as validitystartdate,

         0                                                                    as MaterialMinLotSizeQuantity,
         cast('9999999999' as abap.dec(13,3))                                 as MaterialMaxLotSizeQuantity,
         ''                                                                   as SupplyingPlant,
         ''                                                                   as supplier,
         ''                                                                   as suppliername,
         'T'                                                                  as SOSType,
         'UR'                                                                 as MRPElementCategory,
         cast('' as abap.char( 1 ))                                           as DefaultSOS,
         cast('' as plnty)                                                    as BillOfOperationsType,
         case
         when _mat.Plant = _t460a.wrk02
         then _MRPArea.ReceivingStorageLocation
         else cast('' as lgort_d)
         end                                                                  as StorageLocation,
         _mat._Material.BaseUnit                                               as UoM
}
where
          _t460a.sobes                  =  '7'
  and     _t460a.wrk02                  <> ' '

  and(
          _eord.SourceListRecord        is null
    or(
          _eord.SourceListRecord        is not null
      and _eord.ValidityStartDate       <= $parameters.P_RequirementDate
      and _eord.ValidityEndDate         >= $parameters.P_RequirementDate
      and _eord.SourceOfSupplyIsBlocked =  ' '
    )
  )
union all select from I_ProductPlantMRPArea as _mat
  inner join          t460a                 as _t460a on  _mat.Plant                      = _t460a.werks
                                                      and _mat.MaterialProcurementProfile = _t460a.sobsl
                                                      and _t460a.sobes                    = 'P'
  inner join          I_ProductionVersion   as _pv    on  _mat.Material = _pv.Material
                                                      and _t460a.wrk02  = _pv.Plant
{
  key _mat.Material,
  key _mat.Plant,
  key _mat.MRPArea,
  key ProductionVersion                                  as sourceDocument,
  key cast('00000' as abap.numc( 5 ))                    as SourceDocumentItem,
      cast('P' as abap.char( 1 ))                        as SourceDocumentType,
      'spk80'                                            as SOSTypeDescription,
      ''                                                 as PurchasingOrganization,
      ''                                                 as PurchasingGroup,
      ValidityEndDate,
      ValidityStartDate,
      MaterialMinLotSizeQuantity                         as MaterialMinLotSizeQuantity,
      case
      when MaterialMaxLotSizeQuantity <> 0
      then MaterialMaxLotSizeQuantity
      else cast('9999999999' as abap.dec(13,3))
      end                                                as MaterialMaxLotSizeQuantity,
      ''                                                 as SupplyingPlant,
      ''                                                 as supplier,
      ''                                                 as suppliername,
      'M'                                                as SOSType,
      'PA'                                               as MRPElementCategory,
      cast('' as abap.char( 1 ))                         as DefaultSOS,
      _pv.BillOfOperationsType                           as BillOfOperationsType,
      _pv.ReceivingStorageLocation                       as StorageLocation,
      _mat._Material.BaseUnit                             as UoM
}
where
       ValidityStartDate         <= $parameters.P_RequirementDate
  and  ValidityEndDate           >= $parameters.P_RequirementDate
  and  ProductionVersionIsLocked = ' '
  and(
       _mat.ProcurementType      = 'E'
    or _mat.ProcurementType      = 'X'
  )