@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PMRPMATRL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.representativeKey: 'Material'
@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #XL, dataClass: #MASTER}
@VDM.viewType: #BASIC
@VDM.private:true
define view P_MRPMATERIAL
as select from pph_dbvm
inner join marc on marc.matnr = pph_dbvm.matnr
and marc.werks = pph_dbvm.werks
left outer to one join mdfd on pph_dbvm.matnr = mdfd.matnr
and pph_dbvm.werks = mdfd.werks
and pph_dbvm.plscn = mdfd.plscn
left outer to one join mdfddb on pph_dbvm.matnr = mdfddb.matnr
and pph_dbvm.berid = mdfddb.berid
and pph_dbvm.plscn = mdfddb.plscn
association [0..1] to P_T460AMAXBANF as _t460a_def_b on $projection.MRPPlant = _t460a_def_b.werks
association [0..1] to P_T460AMAX as _t460a_def_u on $projection.MRPPlant = _t460a_def_u.werks
association [1..1] to I_MRPArea as _MRPArea on $projection.MRPArea = _MRPArea.MRPArea
association [1..1] to I_Product as _Product on $projection.Material = _Product.Product
association [0..1] to P_PRODUCTPLANTMRP as _ProductPlantMRP on $projection.Material = _ProductPlantMRP.Product
and $projection.MRPPlant = _ProductPlantMRP.Plant
and $projection.MRPArea = _ProductPlantMRP.MRPArea
and (
(
_ProductPlantMRP.IsMarkedForDeletion = ''
and _ProductPlantMRP.MRPAreaCategory = '03'
) -- if deleted in berty 03, do not consider
or _ProductPlantMRP.MRPAreaCategory = '02' -- if deleted in berty 02, consider, but eliminate in WHERE condition below
)
association [1..1] to I_TimeDependentStockLevel as _TimeDependentStockLevel on $projection.Material = _TimeDependentStockLevel.Product
and $projection.MRPArea = _TimeDependentStockLevel.MRPArea
and _TimeDependentStockLevel.StockLevelValidityStartDate <= $session.system_date
and _TimeDependentStockLevel.StockLevelValidityEndDate >= $session.system_date
{
key pph_dbvm.matnr as Material,
key pph_dbvm.werks as MRPPlant,
key pph_dbvm.berid as MRPArea,
_MRPArea.MRPAreaCategory,
_Product.ProductGroup as MaterialGroup,
_Product.IsMarkedForDeletion as MaterialIsMarkedForDeletion,
marc.lvorm as PlntMatlIsMarkedForDeletion,
_Product.CrossPlantStatus,
_Product.BaseUnit,
_Product.LowLevelCode,
_Product.ProductIsConfigurable as MaterialIsConfigurable,
marc.mmsta as MaterialUsabilityProfile,
marc.maabc as MaterialABCClassification,
marc.ekgrp as PurchasingGroup,
marc.beskz as MaterialProcurementCategory,
marc.sbdkz as DependentRequirementsType,
case
when (marc.kzaus = '1' and marc.nfmat <> '') or marc.kzaus = '3' then marc.kzaus
else ''
end as MatlCompDiscontinuationType,
cast(case
when (marc.kzaus = '1' and marc.nfmat <> '') then marc.ausdt
else '00000000'
end as ausdt) as EffectiveOutDate,
_Product.ProductType as MaterialType,
case -- disgr_marc special logic
when marc.disgr = '' then _Product.ProductType
else marc.disgr
end as MRPGroup,
case when _ProductPlantMRP.MRPResponsible is null or _ProductPlantMRP.MRPResponsible = '' then marc.dispo
else _ProductPlantMRP.MRPResponsible
end as MRPController,
marc.rwpro as RangeOfCvrgPrflCode,
marc.fhori as SchedulingFloatProfile,
marc.stdpd as ConfigurableProduct,
marc.bearz as ProcessingTime,
marc.ruezt as SetupAndTeardownTime,
marc.tranz as TransitionTime,
marc.webaz as GoodsReceiptDuration,
case -- special logic FOR plifz
when _ProductPlantMRP.IsPlannedDeliveryTime = 'X' then _ProductPlantMRP.PlannedDeliveryDurationInDays
else marc.plifz
end as MaterialPlannedDeliveryDurn,
marc.dzeit as MaterialPlannedProductionDurn,
marc.wzeit as TotalReplenishmentLeadDuration,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.IsSafetyTime,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.IsSafetyTime
else marc.shflg
end as IsSafetyTime,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.SafetyDuration,'00')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.SafetyDuration
else marc.shzet
end as MRPSafetyDuration,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.PerdPrflForSftyTme,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.PerdPrflForSftyTme
else marc.shpro
end as PerdPrflForSftyTme,
case when marc.sfty_stk_meth = 'MB' then _TimeDependentStockLevel.ReorderThresholdQuantity
when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.ReorderThresholdQuantity ,cast(0 as minbe))
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.ReorderThresholdQuantity
else marc.minbe
end as ReorderThresholdQuantity,
case when marc.sfty_stk_meth = 'MB' then cast( _TimeDependentStockLevel.ProductSafetyStockLvlQuantity as abap.dec( 13, 3))
when _MRPArea.MRPAreaCategory = '03' then coalesce(cast(_ProductPlantMRP.SafetyStockQuantity as abap.dec( 13, 3)),cast(0 as abap.dec( 13, 3)))
when _MRPArea.MRPAreaCategory = '02' then cast(_ProductPlantMRP.SafetyStockQuantity as abap.dec( 13, 3))
else cast(marc.eisbe as abap.dec( 13, 3))
end as SafetyStockQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.LotSizingProcedure,'EX')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.LotSizingProcedure
else marc.disls
end as LotSizingProcedure,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.MinimumLotSizeQuantity,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.MinimumLotSizeQuantity
else marc.bstmi
end as MinimumLotSizeQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.MaximumLotSizeQuantity,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.MaximumLotSizeQuantity
else marc.bstma
end as MaximumLotSizeQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.FixedLotSizeQuantity,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.FixedLotSizeQuantity
else marc.bstfe
end as FixedLotSizeQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.MaximumStockQuantity,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.MaximumStockQuantity
else marc.mabst
end as MaterialMaxStockLevelQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.LotSizeIndependentCosts,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.LotSizeIndependentCosts
else marc.losfx
end as LotSizeIndependentCosts,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.IsStorageCosts,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.IsStorageCosts
else marc.lagpr
end as StorageCostsPercentageCode,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.AssemblyScrapPercent,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.AssemblyScrapPercent
else marc.ausss
end as AssemblyScrapPercent,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.TaktTime,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.TaktTime
else marc.takzt
end as TaktTime,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.LotSizeRoundingQuantity,0)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.LotSizeRoundingQuantity
else marc.bstrf
end as PurOrderQtyRoundingQty,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.RoundingProfile,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.RoundingProfile
else marc.rdprf
end as RoundingProfile,
marc.strgr as PlanningStrategyGroup,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.DfltStorageLocationExtProcmt,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.DfltStorageLocationExtProcmt
else marc.lgfsb
end as DfltStorageLocationExtProcmt,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.MRPPlanningCalendar,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.MRPPlanningCalendar
else marc.mrppp
end as MRPPlanningCalendar,
marc.sauft as RepetitiveManufacturingIsAllwd,
marc.sfepr as RepetitiveManufacturingProfile,
cast( case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.PlanningTimeFence,'000')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.PlanningTimeFence
else marc.fxhor
end as fxhor) as PlanningTimeFenceInDays,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.MRPType,marc.dismm)
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.MRPType
else marc.dismm
end as MRPType,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(coalesce(coalesce(_ProductPlantMRP.ProcurementSubType,_t460a_def_u.sobsl),_t460a_def_b.sobsl),'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.ProcurementSubType
else marc.sobsl
end as MaterialProcurementProfile,
marc.fevor as ProductionSupervisor,
marc.basmg as MaterialBaseQuantity,
case when _MRPArea.MRPAreaCategory = '03' then coalesce(_ProductPlantMRP.StorageLocation,'')
when _MRPArea.MRPAreaCategory = '02' then _ProductPlantMRP.StorageLocation
else marc.lgpro
end as ProductionInvtryManagedLoc,
case when _ProductPlantMRP.IsMarkedForDeletion = 'X' then '1'
else '0'
end as IsMarkedForDeletion,
marc.sfty_stk_meth as MRPSafetyStockMethod,
_Product.AuthorizationGroup,
_ProductPlantMRP._MRPType.MRPProcedure as MRPProcedure,
marc.mmsta as PlantSpecificMaterialStatus
}
where
pph_dbvm.plscn = '000' -- Nothing else supported in S4
and pph_dbvm.sc_empty = '' -- Omit empty MRP Areas
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MRPAREA",
"I_MRPTYPE",
"I_PRODUCT",
"I_TIMEDEPENDENTSTOCKLEVEL",
"P_PRODUCTPLANTMRP",
"P_T460AMAX",
"P_T460AMAXBANF",
"MARC",
"MDFD",
"MDFDDB",
"PPH_DBVM"
],
"ASSOCIATED":
[
"I_MRPAREA",
"I_PRODUCT",
"I_TIMEDEPENDENTSTOCKLEVEL",
"P_PRODUCTPLANTMRP",
"P_T460AMAX",
"P_T460AMAXBANF"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/