@AbapCatalog: {
sqlViewName: 'PMBACKLOGSERVUNI',
compiler.compareFilter: true,
preserveKey: true
}
@AccessControl: {
authorizationCheck: #CHECK
}
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM: {
viewType: #CONSUMPTION,
private: true
}
@ObjectModel: {
usageType: {
serviceQuality: #D,
dataClass: #TRANSACTIONAL,
sizeCategory: #XL
}
}
define view P_MaintBacklogServiceUnion
as select from I_MaintenanceOrderComponent_2 as _MaintenanceOrderComponent
left outer to one join I_PurchaseRequisitionItemBasic as _PurgRequisitionItem on _PurgRequisitionItem.SettlementReservation = _MaintenanceOrderComponent.Reservation
and _PurgRequisitionItem.SettlementReservationItem = _MaintenanceOrderComponent.ReservationItem
association [0..1] to I_MaintOrdProcurementMilestone as _MaintOrdProcurementMilestone on _MaintOrdProcurementMilestone.MaintenanceOrder = _MaintenanceOrderComponent.MaintenanceOrder
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocument = _MaintenanceOrderComponent.MaintOrderComponentInternalID
and _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnIsCurrent = 'X'
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocType = 'OKP'
association [0..1] to I_PurchasingDocumentItem as _PurchaseOrderItem on _PurchaseOrderItem.PurchaseRequisition = _MaintenanceOrderComponent.PurchaseRequisition
and _PurchaseOrderItem.PurchaseRequisitionItem = _MaintenanceOrderComponent.PurchaseRequisitionItem
association [0..1] to I_PurchasingDocument as _PurchaseOrder on _PurchaseOrder.PurchasingDocument = _PurgRequisitionItem.PurchasingDocument
association [1..1] to I_MaintenanceOrder as _MaintenanceOrder on _MaintenanceOrder.MaintenanceOrder = _MaintenanceOrderComponent.MaintenanceOrder
association [0..1] to I_MaintenanceOrderOperation as _MaintenanceOrderOperation on _MaintenanceOrderOperation.MaintOrderRoutingNumber = _MaintenanceOrderComponent.MaintOrderRoutingNumber
and _MaintenanceOrderOperation.MaintOrderOperationCounter = _MaintenanceOrderComponent.MaintOrderOperationCounter
association [0..1] to I_Supplier as _PreferredSupplier on _PreferredSupplier.Supplier = $projection.PreferredSupplier
association [0..1] to I_Supplier as _PurchaseOrderSupplier on _PurchaseOrderSupplier.Supplier = $projection.PurchaseOrderSupplier
association [0..1] to I_Supplier as _OperationSupplier on _OperationSupplier.Supplier = $projection.operationsupplier
association [0..1] to I_Supplier as _FixedSupplier on _FixedSupplier.Supplier = $projection.FixedSupplier
association [0..1] to I_FunctionalLocation as _FunctionalLocation on _FunctionalLocation.FunctionalLocation = $projection.functionallocation
association [0..*] to I_FunclocationLabelInternal as _FuncnlLocationLabel on _FuncnlLocationLabel.FunctionalLocation = $projection.functionallocation
{
key _MaintenanceOrderComponent.MaintenanceOrder,
key _MaintenanceOrderComponent.MaintOrderComponentInternalID as MaintOrdProcurementDocument,
_MaintenanceOrderComponent.MaintenanceOrderOperation,
_MaintenanceOrderComponent.MaintenanceOrderComponent,
_MaintenanceOrderComponent.PurchaseRequisition,
_MaintenanceOrderComponent.PurchaseRequisitionItem,
_PurgRequisitionItem.PurchasingDocument as PurchaseOrder,
_PurgRequisitionItem.PurchasingDocumentItem as PurchaseOrderItem,
_MaintenanceOrderComponent._MaintenanceOrder.MaintenanceOrderDesc,
_MaintenanceOrderComponent.MaintenanceOrderSubOperation,
_MaintenanceOrderOperation.OperationDescription,
_MaintenanceOrderOperation.MaintOperationExecStageCode,
_MaintenanceOrderOperation._MaintOperationExecStageCode._MaintOpExecStageCodeText[1: LanguageCode = $session.system_language ].MaintOpExecStageShortText,
_MaintenanceOrderOperation._MaintOperationExecStageCode._MaintOpExecStageCodeText[1: LanguageCode = $session.system_language ].MaintOpExecutionStageName,
_MaintenanceOrderComponent.Material as Product,
_MaintenanceOrderComponent.Material,
_MaintenanceOrderComponent._Material.MaterialType as ProductType,
_MaintenanceOrderComponent.MaterialGroup as ProductGroup,
_MaintenanceOrderComponent.ProductTypeCode,
_MaintenanceOrderComponent.ComponentDescription,
_MaintenanceOrderComponent.RequirementQuantityInBaseUnit,
_MaintenanceOrderComponent.BaseUnit,
_PurchaseOrder.Supplier as PurchaseOrderSupplier,
_PurgRequisitionItem.FixedSupplier,
_MaintenanceOrderComponent.Supplier as PreferredSupplier,
_MaintenanceOrderOperation.OperationSupplier,
case when _PurgRequisitionItem.PurchasingDocument != '' then _PurchaseOrder.Supplier
when _PurgRequisitionItem.FixedSupplier != '' then _PurgRequisitionItem.FixedSupplier
when _MaintenanceOrderComponent.Supplier != '' then _MaintenanceOrderComponent.Supplier
else _MaintenanceOrderOperation.OperationSupplier end as Supplier,
_MaintenanceOrderComponent.Reservation,
_MaintenanceOrderComponent.ReservationItem,
_MaintenanceOrderComponent.ReservationType,
_MaintenanceOrderComponent.MaintComponentItemCategory,
_MaintenanceOrderComponent.GoodsMovementType,
_MaintenanceOrderComponent.WithdrawnQuantity,
cast (0 as pph_enmng ) as ReceivedQuantity,
_MaintenanceOrderComponent.MaintOrderComponentInternalID,
_MaintenanceOrderComponent.RequirementDate,
cast( case when _MaintenanceOrderComponent.RequirementTime = '240000' and _MaintenanceOrderComponent.RequirementDate <> '00000000'
then dats_tims_to_tstmp(dats_add_days( _MaintenanceOrderComponent.RequirementDate, 1, 'NULL'), cast( '000000' as abap.tims ),
abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
else dats_tims_to_tstmp(_MaintenanceOrderComponent.RequirementDate, _MaintenanceOrderComponent.RequirementTime,
abap_system_timezone($session.client,'NULL'), $session.client, 'NULL' )
end as timestamp preserving type ) as RequirementDateTime,
// case when _MaintenanceOrderComponent.PurchaseRequisition is null then 1
// when I_MaintOrdCompResvnPurgDoc.PurchaseOrder is null then 2
// when _MaintenanceOrderComponent.PurchaseRequisition != '' and I_MaintOrdCompResvnPurgDoc.PurchaseOrder != '' then 3 end as MaintOrdCompAvailyCritltyCode,
case when _MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate <= $session.system_date then 1
when _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate <= $session.system_date then 2
else 3 end as MaintOrdCompAvailyCritltyCode,
_MaintenanceOrderComponent.PerformancePeriodStartDate,
_MaintenanceOrderComponent.PerformancePeriodEndDate,
_MaintenanceOrderComponent.SrvcSchedgIsAlignedWthOpWrkCtr,
_MaintenanceOrderComponent.ServicePerformer,
_MaintenanceOrderComponent._MaintenanceOrder.MaintenanceOrderType,
_MaintenanceOrderComponent._MaintenanceOrder.MaintOrdProcessPhaseCode,
_MaintenanceOrderComponent._MaintenanceOrder.ScheduledBasicStartDate,
_MaintenanceOrderComponent._MaintenanceOrder.ScheduledBasicStartTime,
_MaintenanceOrderComponent._MaintenanceOrder.ScheduledBasicEndDate,
_MaintenanceOrderComponent._MaintenanceOrder.ScheduledBasicEndTime,
_MaintenanceOrderComponent._MaintenanceOrder.MaintenancePlanningPlant,
_MaintenanceOrderComponent._MaintenanceOrder.MaintenancePlant,
_MaintenanceOrderComponent._MaintenanceOrder._LocationAccountAssignment.PlantSection,
_MaintenanceOrderComponent._MaintenanceOrder.MaintenancePlannerGroup,
_MaintenanceOrderComponent._MaintenanceOrder.WorkCenterInternalID,
_MaintenanceOrderComponent._MaintenanceOrder.MainWorkCenter,
_MaintenanceOrderComponent._MaintenanceOrder.Equipment,
_MaintenanceOrderComponent._MaintenanceOrder._LocationAccountAssignment.FunctionalLocation,
_MaintenanceOrderComponent._MaintenanceOrder.MaintObjectLocAcctAssgmtNmbr,
_MaintOrdProcurementMilestone.MaintProcurementMilestone,
_MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate,
_MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate,
_MaintOrdProcurementMilestone.MaintOrdProcurementDocType,
_MaintenanceOrderComponent.ReservationIsFinallyIssued as IsCompletelyDelivered,
_FunctionalLocation,
_FuncnlLocationLabel,
_MaintenanceOrder,
_PreferredSupplier,
_OperationSupplier,
_FixedSupplier,
_PurchaseOrderSupplier,
_MaintenanceOrderComponent._MaintenanceOrder._Equipment,
_MaintenanceOrderComponent._MaintenanceOrder._LocationAccountAssignment,
_MaintenanceOrderComponent.OverallLimitAmount,
_MaintenanceOrderComponent.ExpectedOverallLimitAmount,
_MaintenanceOrderComponent.MaintOrdOpComponentCurrency
}
where
_MaintenanceOrderComponent.ProductTypeCode = '2'
and(
_MaintenanceOrder.MaintenanceProcessingPhase < '4'
or(
_MaintenanceOrder.MaintenanceProcessingPhase = '6'
and _MaintenanceOrder.TechnicalCompletionDate != '00000000'
)
)
union all
select from I_MaintenanceOrder
inner join I_OrderOperation on I_OrderOperation.OrderInternalID = I_MaintenanceOrder.MaintOrderRoutingNumber
left outer to one join I_PurchaseRequisitionItemBasic as _PurgRequisitionItem on _PurgRequisitionItem.PurchaseRequisition = I_OrderOperation.PurchaseRequisition
and _PurgRequisitionItem.PurchaseRequisitionItem = I_OrderOperation.PurchaseRequisitionItem
left outer to one join I_OperationControlProfile on I_OperationControlProfile.OperationControlProfile = I_OrderOperation.OperationControlProfile
left outer to one join I_OrderOperationBasic on I_OrderOperationBasic.OrderInternalID = I_OrderOperation.OrderInternalID
and I_OrderOperationBasic.OrderOperationInternalID = I_OrderOperation.SuperiorOperationInternalID
left outer to one join I_PurchasingDocumentItem as _PurchaseOrderItem on _PurchaseOrderItem.PurchaseRequisition = _PurgRequisitionItem.PurchaseRequisition
and _PurchaseOrderItem.PurchaseRequisitionItem = _PurgRequisitionItem.PurchaseRequisitionItem
association [0..1] to I_PurchasingDocument as _PurchaseOrder on _PurchaseOrder.PurchasingDocument = _PurgRequisitionItem.PurchasingDocument
association [0..1] to I_MaintOrdProcurementMilestone as _MaintOrdProcurementMilestone on _MaintOrdProcurementMilestone.MaintOrdProcurementDocument = I_OrderOperation.ObjectInternalID
and _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnIsCurrent = 'X'
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocType = 'OVG' //????
association [0..1] to I_Purchaserequisitionitem as _PurchaseRequisitionItem on _PurchaseRequisitionItem.PurchaseRequisition = I_OrderOperation.PurchaseRequisition
and _PurchaseRequisitionItem.PurchaseRequisitionItem = I_OrderOperation.PurchaseRequisitionItem
association [1..1] to I_MaintenanceOrder as _MaintenanceOrder on _MaintenanceOrder.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
association [0..1] to I_MaintOpExecStageCodeText as _MaintOpExecStageCodeText on _MaintOpExecStageCodeText.MaintOperationExecStageCode = I_OrderOperation.MaintOperationExecStageCode
and _MaintOpExecStageCodeText.LanguageCode = $session.system_language
association [0..1] to I_FunctionalLocation as _FunctionalLocation on _FunctionalLocation.FunctionalLocation = $projection.functionallocation
association [0..*] to I_FunclocationLabelInternal as _FuncnlLocationLabel on _FuncnlLocationLabel.FunctionalLocation = $projection.functionallocation
association [0..1] to I_Supplier as _PreferredSupplier on _PreferredSupplier.Supplier = $projection.PreferredSupplier
association [0..1] to I_Supplier as _PurchaseOrderSupplier on _PurchaseOrderSupplier.Supplier = $projection.PurchaseOrderSupplier
association [0..1] to I_Supplier as _OperationSupplier on _OperationSupplier.Supplier = $projection.OperationSupplier
association [0..1] to I_Supplier as _FixedSupplier on _FixedSupplier.Supplier = $projection.FixedSupplier
{
key I_MaintenanceOrder.MaintenanceOrder,
key I_OrderOperation.ObjectInternalID as MaintOrdProcurementDocument,
case when I_OrderOperation.SuperiorOperationInternalID <> '00000000' then I_OrderOperationBasic.Operation
else I_OrderOperation.Operation end as MaintenanceOrderOperation,
cast ('' as aposn ) as MaintenanceOrderComponent,
I_OrderOperation.PurchaseRequisition,
I_OrderOperation.PurchaseRequisitionItem,
_PurchaseOrderItem.PurchasingDocument as PurchaseOrder,
_PurchaseOrderItem.PurchasingDocumentItem as PurchaseOrderItem,
I_MaintenanceOrder.MaintenanceOrderDesc,
case when I_OrderOperation.SuperiorOperationInternalID <> '00000000' then I_OrderOperation.Operation
else '0000' end as MaintenanceOrderSubOperation,
I_OrderOperation.OperationText as OperationDescription,
I_OrderOperation.MaintOperationExecStageCode,
_MaintOpExecStageCodeText.MaintOpExecStageShortText,
_MaintOpExecStageCodeText.MaintOpExecutionStageName,
'' as Product,
'' as Material,
'' as ProductType,
I_OrderOperation.MaterialGroup as ProductGroup,
'' as ProductTypeCode,
I_OrderOperation.OperationText as ComponentDescription,
I_OrderOperation.OpPlannedTotalQuantity as RequirementQuantityInBaseUnit,
I_OrderOperation.OperationUnit as BaseUnit,
_PurchaseOrderItem._PurchasingDocument.Supplier as PurchaseOrderSupplier,
_PurgRequisitionItem.FixedSupplier,
I_OrderOperation.Supplier as PreferredSupplier,
I_OrderOperation.Supplier as OperationSupplier,
case when _PurchaseOrderItem.PurchasingDocument != '' then _PurchaseOrderItem._PurchasingDocument.Supplier
when _PurgRequisitionItem.FixedSupplier != '' then _PurgRequisitionItem.FixedSupplier
else I_OrderOperation.Supplier end as Supplier,
cast( '' as rsnum ) as Reservation,
cast( '' as rspos ) as ReservationItem,
cast( '' as rsart ) as ReservationType,
cast ('' as postp ) as MaintComponentItemCategory,
cast ('' as bwart ) as GoodsMovementType,
cast (0 as pph_enmng ) as WithdrawnQuantity,
cast (0 as pph_enmng ) as ReceivedQuantity,
cast ('' as objnr ) as MaintOrderComponentInternalID,
I_OrderOperation.OpErlstSchedldExecStrtDte as RequirementDate,
cast( '00000000000000' as timestamp ) as RequirementDateTime,
//case when I_OrderOperation.PurchaseRequisition = '' then 1
// when _PurchaseOrderItem.PurchasingDocument is null then 2
// when I_OrderOperation.PurchaseRequisition != '' and _PurchaseOrderItem.PurchasingDocument != '' then 3 end as MaintOrdCompAvailyCritltyCode,
case when _MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate <= $session.system_date then 1
when _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate <= $session.system_date then 2
else 3 end as MaintOrdCompAvailyCritltyCode,
'00000000' as PerformancePeriodStartDate,
'00000000' as PerformancePeriodEndDate,
'' as SrvcSchedgIsAlignedWthOpWrkCtr,
'' as ServicePerformer,
I_MaintenanceOrder.MaintenanceOrderType,
I_MaintenanceOrder.MaintOrdProcessPhaseCode,
I_MaintenanceOrder.ScheduledBasicStartDate,
I_MaintenanceOrder.ScheduledBasicStartTime,
I_MaintenanceOrder.ScheduledBasicEndDate,
I_MaintenanceOrder.ScheduledBasicEndTime,
I_MaintenanceOrder.MaintenancePlanningPlant,
I_MaintenanceOrder.MaintenancePlant,
I_MaintenanceOrder._LocationAccountAssignment.PlantSection,
I_MaintenanceOrder.MaintenancePlannerGroup,
I_MaintenanceOrder.WorkCenterInternalID,
I_MaintenanceOrder.MainWorkCenter,
I_MaintenanceOrder.Equipment,
I_MaintenanceOrder._LocationAccountAssignment.FunctionalLocation,
I_MaintenanceOrder.MaintObjectLocAcctAssgmtNmbr,
_MaintOrdProcurementMilestone.MaintProcurementMilestone,
_MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate,
_MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate,
_MaintOrdProcurementMilestone.MaintOrdProcurementDocType,
_PurchaseOrderItem.IsCompletelyDelivered,
_FunctionalLocation,
_FuncnlLocationLabel,
_MaintenanceOrder,
_PreferredSupplier,
_OperationSupplier,
_FixedSupplier,
_PurchaseOrderSupplier,
I_MaintenanceOrder._Equipment,
I_MaintenanceOrder._LocationAccountAssignment,
_PurgRequisitionItem.OverallLimitAmount,
_PurgRequisitionItem.ExpectedOverallLimitAmount,
_PurgRequisitionItem.PurReqnItemCurrency as MaintOrdOpComponentCurrency
}
where
(
_PurgRequisitionItem.ProductTypeCode = '2'
or _PurgRequisitionItem.ProductTypeCode = '1'
or _PurgRequisitionItem.ProductTypeCode = ''
)
and(
I_OperationControlProfile.OperationExternalProcessing = '+'
or I_OperationControlProfile.OperationExternalProcessing = 'X'
)
and(
_MaintenanceOrder.MaintenanceProcessingPhase < '4'
or(
_MaintenanceOrder.MaintenanceProcessingPhase = '6'
and _MaintenanceOrder.TechnicalCompletionDate != '00000000'
)
)
union all
select from I_MaintenanceOrder
inner join I_PurReqnAcctAssgmt on I_MaintenanceOrder.MaintenanceOrder = I_PurReqnAcctAssgmt.OrderID
left outer to one join I_PurchaseRequisitionItemBasic as _PurchaseRequisitionItem on _PurchaseRequisitionItem.PurchaseRequisition = I_PurReqnAcctAssgmt.PurchaseRequisition
and _PurchaseRequisitionItem.PurchaseRequisitionItem = I_PurReqnAcctAssgmt.PurchaseRequisitionItem
left outer to one join I_PurchasingDocumentItem as _PurchaseOrderItem on _PurchaseOrderItem.PurchaseRequisition = I_PurReqnAcctAssgmt.PurchaseRequisition
and _PurchaseOrderItem.PurchaseRequisitionItem = I_PurReqnAcctAssgmt.PurchaseRequisitionItem
association [0..1] to I_PurchasingDocument as _PurchaseOrder on _PurchaseOrder.PurchasingDocument = _PurchaseRequisitionItem.PurchasingDocument
association [0..1] to I_MaintOrdProcurementMilestone as _MaintOrdProcurementMilestone on _MaintOrdProcurementMilestone.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocument = _PurchaseRequisitionItem.PurchaseReqnItemUniqueID
and _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnIsCurrent = 'X'
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocType = 'PR'
association [1..1] to I_MaintenanceOrder as _MaintenanceOrder on _MaintenanceOrder.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
association [0..1] to I_Supplier as _PreferredSupplier on _PreferredSupplier.Supplier = $projection.PreferredSupplier
association [0..1] to I_Supplier as _PurchaseOrderSupplier on _PurchaseOrderSupplier.Supplier = $projection.PurchaseOrderSupplier
association [0..1] to I_Supplier as _OperationSupplier on _OperationSupplier.Supplier = $projection.OperationSupplier
association [0..1] to I_Supplier as _FixedSupplier on _FixedSupplier.Supplier = $projection.FixedSupplier
association [0..1] to I_FunctionalLocation as _FunctionalLocation on _FunctionalLocation.FunctionalLocation = $projection.functionallocation
association [0..*] to I_FunclocationLabelInternal as _FuncnlLocationLabel on _FuncnlLocationLabel.FunctionalLocation = $projection.functionallocation
{
key I_MaintenanceOrder.MaintenanceOrder,
key _PurchaseRequisitionItem.PurchaseReqnItemUniqueID as MaintOrdProcurementDocument,
'' as MaintenanceOrderOperation,
'' as MaintenanceOrderComponent,
I_PurReqnAcctAssgmt.PurchaseRequisition,
I_PurReqnAcctAssgmt.PurchaseRequisitionItem,
_PurchaseRequisitionItem.PurchasingDocument as PurchaseOrder,
_PurchaseRequisitionItem.PurchasingDocumentItem as PurchaseOrderItem,
I_MaintenanceOrder.MaintenanceOrderDesc,
' ' as MaintenanceOrderSubOperation,
'' as OperationDescription,
'' as MaintOperationExecStageCode,
'' as MaintOpExecStageShortText,
'' as MaintOpExecutionStageName,
_PurchaseRequisitionItem.Material as Product,
_PurchaseRequisitionItem.Material,
'' as ProductType,
_PurchaseRequisitionItem.MaterialGroup as ProductGroup,
_PurchaseRequisitionItem.ProductType as ProductTypeCode,
_PurchaseRequisitionItem.PurchaseRequisitionItemText as ComponentDescription,
_PurchaseRequisitionItem.RequestedQuantity as RequirementQuantityInBaseUnit,
_PurchaseRequisitionItem.BaseUnit,
_PurchaseOrder.Supplier as PurchaseOrderSupplier,
_PurchaseRequisitionItem.FixedSupplier,
_PurchaseRequisitionItem.Supplier as PreferredSupplier,
'' as OperationSupplier,
case when _PurchaseRequisitionItem.PurchasingDocument != '' then _PurchaseOrder.Supplier
when _PurchaseRequisitionItem.FixedSupplier != '' then _PurchaseRequisitionItem.FixedSupplier
else _PurchaseRequisitionItem.Supplier end as Supplier,
cast( '' as rsnum ) as Reservation,
cast( '' as rspos ) as ReservationItem,
cast( '' as rsart ) as ReservationType,
cast ('' as postp ) as MaintComponentItemCategory,
cast ('' as bwart ) as GoodsMovementType,
cast (0 as pph_enmng ) as WithdrawnQuantity,
cast (0 as pph_enmng ) as ReceivedQuantity,
cast ('' as objnr ) as MaintOrderComponentInternalID,
'00000000' as RequirementDate,
cast( '00000000000000' as timestamp ) as RequirementDateTime,
//case when _PurchaseRequisitionItem.PurchasingDocument = '' then 2
// when _PurchaseRequisitionItem.PurchasingDocument != '' then 3 end as MaintOrdCompAvailyCritltyCode,
case when _MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate <= $session.system_date then 1
when _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate <= $session.system_date then 2
else 3 end as MaintOrdCompAvailyCritltyCode,
_PurchaseRequisitionItem.PerformancePeriodStartDate,
_PurchaseRequisitionItem.PerformancePeriodEndDate,
'' as SrvcSchedgIsAlignedWthOpWrkCtr,
_PurchaseRequisitionItem.ServicePerformer,
I_MaintenanceOrder.MaintenanceOrderType,
I_MaintenanceOrder.MaintOrdProcessPhaseCode,
I_MaintenanceOrder.ScheduledBasicStartDate,
I_MaintenanceOrder.ScheduledBasicStartTime,
I_MaintenanceOrder.ScheduledBasicEndDate,
I_MaintenanceOrder.ScheduledBasicEndTime,
I_MaintenanceOrder.MaintenancePlanningPlant,
I_MaintenanceOrder.MaintenancePlant,
I_MaintenanceOrder._LocationAccountAssignment.PlantSection,
I_MaintenanceOrder.MaintenancePlannerGroup,
I_MaintenanceOrder.WorkCenterInternalID,
I_MaintenanceOrder.MainWorkCenter,
I_MaintenanceOrder.Equipment,
I_MaintenanceOrder._LocationAccountAssignment.FunctionalLocation,
I_MaintenanceOrder.MaintObjectLocAcctAssgmtNmbr,
_MaintOrdProcurementMilestone.MaintProcurementMilestone,
_MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate,
_MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate,
_MaintOrdProcurementMilestone.MaintOrdProcurementDocType,
_PurchaseOrderItem.IsCompletelyDelivered,
_FunctionalLocation,
_FuncnlLocationLabel,
_MaintenanceOrder,
_PreferredSupplier,
_OperationSupplier,
_FixedSupplier,
_PurchaseOrderSupplier,
I_MaintenanceOrder._Equipment,
I_MaintenanceOrder._LocationAccountAssignment,
_PurchaseRequisitionItem.OverallLimitAmount,
_PurchaseRequisitionItem.ExpectedOverallLimitAmount,
_PurchaseRequisitionItem.PurReqnItemCurrency as MaintOrdOpComponentCurrency
}
where
I_PurReqnAcctAssgmt.IsDeleted = ''
and _PurchaseRequisitionItem.ProductTypeCode = '2'
and I_PurReqnAcctAssgmt.NetworkActivityInternalID = '00000000'
and _PurchaseRequisitionItem.SettlementReservation = '0000000000'
and(
_MaintenanceOrder.MaintenanceProcessingPhase < '4'
or(
_MaintenanceOrder.MaintenanceProcessingPhase = '6'
and _MaintenanceOrder.TechnicalCompletionDate != '00000000'
)
)
union all
select from I_MaintenanceOrder
inner join I_PurgDocAccountAssignment on I_MaintenanceOrder.MaintenanceOrder = I_PurgDocAccountAssignment.OrderID
left outer to one join I_PurchasingDocumentItem on I_PurchasingDocumentItem.PurchasingDocument = I_PurgDocAccountAssignment.PurchasingDocument
and I_PurchasingDocumentItem.PurchasingDocumentItem = I_PurgDocAccountAssignment.PurchasingDocumentItem
association [1..1] to I_PurchasingDocument as _PurchaseOrder on I_PurgDocAccountAssignment.PurchasingDocument = _PurchaseOrder.PurchasingDocument
//association [1..1] to I_MaintenanceOrder as _MaintenanceOrder on _MaintenanceOrder.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
association [0..1] to I_MaintOrdProcurementMilestone as _MaintOrdProcurementMilestone on _MaintOrdProcurementMilestone.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocument = I_PurchasingDocumentItem.PurchasingDocumentItemUniqueID
and _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnIsCurrent = 'X'
and _MaintOrdProcurementMilestone.MaintOrdProcurementDocType = 'PO'
association [1..1] to I_MaintenanceOrder as _MaintenanceOrder on _MaintenanceOrder.MaintenanceOrder = I_MaintenanceOrder.MaintenanceOrder
association [0..1] to I_Supplier as _PreferredSupplier on _PreferredSupplier.Supplier = $projection.PreferredSupplier
association [0..1] to I_Supplier as _PurchaseOrderSupplier on _PurchaseOrderSupplier.Supplier = $projection.PurchaseOrderSupplier
association [0..1] to I_Supplier as _OperationSupplier on _OperationSupplier.Supplier = $projection.OperationSupplier
association [0..1] to I_Supplier as _FixedSupplier on _FixedSupplier.Supplier = $projection.fixedsupplier
association [0..1] to I_FunctionalLocation as _FunctionalLocation on _FunctionalLocation.FunctionalLocation = $projection.functionallocation
association [0..*] to I_FunclocationLabelInternal as _FuncnlLocationLabel on _FuncnlLocationLabel.FunctionalLocation = $projection.functionallocation
{
key I_MaintenanceOrder.MaintenanceOrder,
key I_PurchasingDocumentItem.PurchasingDocumentItemUniqueID as MaintOrdProcurementDocument,
'' as MaintenanceOrderOperation,
'' as MaintenanceOrderComponent,
I_PurchasingDocumentItem.PurchaseRequisition,
I_PurchasingDocumentItem.PurchaseRequisitionItem,
I_PurchasingDocumentItem.PurchasingDocument as PurchaseOrder,
I_PurchasingDocumentItem.PurchasingDocumentItem as PurchaseOrderItem,
I_MaintenanceOrder.MaintenanceOrderDesc,
' ' as MaintenanceOrderSubOperation,
'' as OperationDescription,
'' as MaintOperationExecStageCode,
'' as MaintOpExecStageShortText,
'' as MaintOpExecutionStageName,
I_PurchasingDocumentItem.Material as Product,
I_PurchasingDocumentItem.Material,
'' as ProductType,
I_PurchasingDocumentItem.MaterialGroup as ProductGroup,
I_PurchasingDocumentItem.ProductType as ProductTypeCode,
I_PurchasingDocumentItem.PurchasingDocumentItemText as ComponentDescription,
I_PurchasingDocumentItem.OrderQuantity as RequirementQuantityInBaseUnit,
I_PurchasingDocumentItem.BaseUnit,
_PurchasingDocument.Supplier as PurchaseOrderSupplier,
I_PurchasingDocumentItem._PurchaseRequisitionItem.FixedSupplier,
I_PurchasingDocumentItem._PurchaseRequisitionItem.Supplier as PreferredSupplier,
'' as OperationSupplier,
case when _PurchasingDocument.Supplier != '' then _PurchasingDocument.Supplier
when I_PurchasingDocumentItem._PurchaseRequisitionItem.FixedSupplier != '' then I_PurchasingDocumentItem._PurchaseRequisitionItem.FixedSupplier
else I_PurchasingDocumentItem._PurchaseRequisitionItem.Supplier end as Supplier,
cast( '' as rsnum ) as Reservation,
cast( '' as rspos ) as ReservationItem,
cast( '' as rsart ) as ReservationType,
cast ('' as postp ) as MaintComponentItemCategory,
cast ('' as bwart ) as GoodsMovementType,
cast (0 as pph_enmng ) as WithdrawnQuantity,
cast (0 as pph_enmng ) as ReceivedQuantity,
cast ('' as objnr ) as MaintOrderComponentInternalID,
'00000000' as RequirementDate,
cast( '00000000000000' as timestamp ) as RequirementDateTime,
//case when _PurchaseRequisitionItem.PurchasingDocument = '' then 2
// when _PurchaseRequisitionItem.PurchasingDocument != '' then 3 end as MaintOrdCompAvailyCritltyCode,
case when _MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate <= $session.system_date then 1
when _MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate <= $session.system_date then 2
else 3 end as MaintOrdCompAvailyCritltyCode,
I_PurchasingDocumentItem._PurchaseRequisitionItem.PerformancePeriodStartDate,
I_PurchasingDocumentItem._PurchaseRequisitionItem.PerformancePeriodEndDate,
'' as SrvcSchedgIsAlignedWthOpWrkCtr,
I_PurchasingDocumentItem._PurchaseRequisitionItem.ServicePerformer,
I_MaintenanceOrder.MaintenanceOrderType,
I_MaintenanceOrder.MaintOrdProcessPhaseCode,
I_MaintenanceOrder.ScheduledBasicStartDate,
I_MaintenanceOrder.ScheduledBasicStartTime,
I_MaintenanceOrder.ScheduledBasicEndDate,
I_MaintenanceOrder.ScheduledBasicEndTime,
I_MaintenanceOrder.MaintenancePlanningPlant,
I_MaintenanceOrder.MaintenancePlant,
I_MaintenanceOrder._LocationAccountAssignment.PlantSection,
I_MaintenanceOrder.MaintenancePlannerGroup,
I_MaintenanceOrder.WorkCenterInternalID,
I_MaintenanceOrder.MainWorkCenter,
I_MaintenanceOrder.Equipment,
I_MaintenanceOrder._LocationAccountAssignment.FunctionalLocation,
I_MaintenanceOrder.MaintObjectLocAcctAssgmtNmbr,
_MaintOrdProcurementMilestone.MaintProcurementMilestone,
_MaintOrdProcurementMilestone.MaintOrdProcmtMilestoneDate,
_MaintOrdProcurementMilestone.MaintOrdProcmtMlstnWarningDate,
_MaintOrdProcurementMilestone.MaintOrdProcurementDocType,
I_PurchasingDocumentItem.IsCompletelyDelivered,
_FunctionalLocation,
_FuncnlLocationLabel,
_MaintenanceOrder,
_PreferredSupplier,
_OperationSupplier,
_FixedSupplier,
_PurchaseOrderSupplier,
I_MaintenanceOrder._Equipment,
I_MaintenanceOrder._LocationAccountAssignment,
I_PurchasingDocumentItem.OverallLimitAmount,
I_PurchasingDocumentItem.ExpectedOverallLimitAmount,
I_PurchasingDocumentItem.DocumentCurrency as MaintOrdOpComponentCurrency
}
where
I_PurgDocAccountAssignment.IsDeleted = ''
and(
I_PurchasingDocumentItem.ProductType = '2'
or I_PurchasingDocumentItem.ProductType = ''
)
and I_MaintenanceOrder.MaintenanceOrder is not null
and I_MaintenanceOrder.MaintenanceOrderCategory = '30'
and(
_MaintenanceOrder.MaintenanceProcessingPhase < '4'
or(
_MaintenanceOrder.MaintenanceProcessingPhase = '6'
and _MaintenanceOrder.TechnicalCompletionDate != '00000000'
)
)
and I_PurchasingDocumentItem.PurchaseRequisition is initial //Those POs, which are assigned into Purchase Requisition, are covered in the P_MaintOrdNonStkCompFrmPurReqn select statement.