P_DDMATERIALSOS
P_DDMATERIALSOS is a CDS View in S/4HANA. 1 CDS views read from this table.
CDS Views using this table (1)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| C_DDMaterialSOS | view | from | CONSUMPTION | Source of Supply for DDR Materials |
@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._Product.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._Product.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._Product.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._Product.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._Product.BaseUnit as UoM
}
where
ValidityStartDate <= $parameters.P_RequirementDate
and ValidityEndDate >= $parameters.P_RequirementDate
and ProductionVersionIsLocked = ' '
and(
_mat.ProcurementType = 'E'
or _mat.ProcurementType = 'X'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MPPURCHASINGSOURCEITEM",
"I_MRPAREA",
"I_PRODUCT",
"I_PRODUCTIONVERSION",
"I_PRODUCTPLANTMRPAREA",
"I_PURCHASINGDOCUMENT",
"I_PURCHASINGDOCUMENTITEM",
"I_PURGINFORECDORGPLANTDATA",
"I_PURGINFORECORDWITHORGDATA",
"I_SUPPLIER",
"T460A"
],
"ASSOCIATED":
[
"I_SUPPLIER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/