@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'IPURORDPROCFLOW'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Purchase Order Business Process Flow'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.lifecycle.contract.type: #SAP_INTERNAL_API
@ObjectModel: {
usageType.serviceQuality: #D,
usageType.sizeCategory: #XL,
usageType.dataClass: #MIXED
}
define view I_PurchaseOrderProcessFlow as
//PO
select distinct from I_PurchaseOrderItem
association [1..*] to I_PurchaseOrderHistoryBasic as PurDocHistory on PurDocHistory.PurchaseOrder = $projection.PurchaseOrder
and PurDocHistory.PurchaseOrderItem = $projection.PurchaseOrderItem
{
key PurchaseOrder as PrecedingDocument,
key cast (PurchaseOrderItem as char10) as PrecedingDocumentItem,
key cast (' ' as vdm_purchaseorder) as SubsequentDocument,
key cast (cast (' ' as vdm_purchaseorderitem) as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
_PurchaseOrder._PurchaseOrderType.PurchasingDocumentCategory as PrecedingDocumentCategory,
cast (' ' as bstyp) as SubsequentDocumentCategory,
PurchaseOrder,
PurchaseOrderItem,
_PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
CompanyCode,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrder.PurchasingOrganization
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Ext. Reference Link -> PO
union all select distinct from I_PurchaseOrderItem as POItem
inner join R_PurOrdItemExternalReference as ExtRef on ExtRef.PurchaseOrder = POItem.PurchaseOrder
and ExtRef.PurchaseOrderItem = POItem.PurchaseOrderItem
association [1..*] to I_PurchaseOrderHistoryBasic as PurDocHistory on PurDocHistory.PurchaseOrder = POItem.PurchaseOrder
and PurDocHistory.PurchaseOrderItem = POItem.PurchaseOrderItem
{
key cast (ExtRef.PurgDocExternalReference as char10) as PrecedingDocument,
key cast (ExtRef.PurgDocExternalReferenceItem as char10) as PrecedingDocumentItem,
key POItem.PurchaseOrder as SubsequentDocument,
key cast (POItem.PurchaseOrderItem as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
cast ('E' as bstyp) as PrecedingDocumentCategory,
POItem.PurchaseOrderCategory as SubsequentDocumentCategory,
POItem.PurchaseOrder,
POItem.PurchaseOrderItem,
POItem._PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
cast('' as fis_bukrs) as CompanyCode,
POItem._PurchaseOrder.PurchasingGroup,
POItem._PurchaseOrder.PurchasingOrganization
}
where ExtRef.LinkType = 'PD'
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// PR -> PO
union all select distinct from I_PurchaseOrderItem as POItem
inner join I_Purchaserequisitionitem as PR on PR.PurchaseRequisition = POItem.PurchaseRequisition
and PR.PurchaseRequisitionItem = POItem.PurchaseRequisitionItem
association [1..*] to I_PurchaseOrderHistoryBasic as PurDocHistory on PurDocHistory.PurchaseOrder = POItem.PurchaseOrder
and PurDocHistory.PurchaseOrderItem = POItem.PurchaseOrderItem
{
key PR.PurchaseRequisition as PrecedingDocument,
key cast (PR.PurchaseRequisitionItem as char10) as PrecedingDocumentItem,
key POItem.PurchaseOrder as SubsequentDocument,
key cast (POItem.PurchaseOrderItem as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
cast ('B' as bstyp) as PrecedingDocumentCategory,
POItem.PurchaseOrderCategory as SubsequentDocumentCategory,
POItem.PurchaseOrder,
POItem.PurchaseOrderItem,
POItem._PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
cast('' as fis_bukrs) as CompanyCode,
POItem._PurchaseOrder.PurchasingGroup,
POItem._PurchaseOrder.PurchasingOrganization
}
where PR.PurchaseRequisition != ''
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// PO -> SES
union all select distinct from I_PurchaseOrderItem as POItem
inner join I_PurchaseOrderHistoryBasic as PurDocHistory on POItem.PurchaseOrder = PurDocHistory.PurchaseOrder
and POItem.PurchaseOrderItem = PurDocHistory.PurchaseOrderItem
association to I_PurchaseOrder as _PurchaseOrder on POItem.PurchaseOrder = _PurchaseOrder.PurchaseOrder
{
key POItem.PurchaseOrder as PrecedingDocument,
key cast (POItem.PurchaseOrderItem as char10) as PrecedingDocumentItem,
key PurDocHistory.PurchasingHistoryDocument as SubsequentDocument,
key cast (PurDocHistory.PurchasingHistoryDocumentItem as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
cast ('F' as bstyp) as PrecedingDocumentCategory,
cast ('S' as bstyp) as SubsequentDocumentCategory,
POItem.PurchaseOrder,
POItem.PurchaseOrderItem,
_PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
cast('' as fis_bukrs) as CompanyCode,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrder.PurchasingOrganization
}
where ( PurDocHistory.PurchasingHistoryDocumentType = 'S' or PurDocHistory.PurchasingHistoryDocumentType = '9' ) and
// !!! filter BEWTP from table t163b
(PurDocHistory.PurchasingHistoryCategory = '0' or PurDocHistory.PurchasingHistoryCategory = 'V' or PurDocHistory.PurchasingHistoryCategory = 'D' /* ?????????????????? */)
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// PO/SES -> GR
union all select distinct from I_PurchaseOrderItem as POItem
inner join I_PurchaseOrderHistoryBasic as PurDocHistory on POItem.PurchaseOrder = PurDocHistory.PurchaseOrder
and POItem.PurchaseOrderItem = PurDocHistory.PurchaseOrderItem
association [1..1] to I_PurchaseOrder as _PurchaseOrder on POItem.PurchaseOrder = _PurchaseOrder.PurchaseOrder
{
key case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and PurDocHistory.ReferenceDocument not like '5%'
and PurDocHistory.IsCompletelyDelivered = ''
and POItem.ProductType = '2'
//Case SES-> GR
then PurDocHistory.ReferenceDocument
//Case PO -> GR
else POItem.PurchaseOrder end as PrecedingDocument,
key case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and PurDocHistory.ReferenceDocument not like '5%'
and PurDocHistory.IsCompletelyDelivered = ''
and POItem.ProductType = '2'
then cast (PurDocHistory.ReferenceDocumentItem as char10)
else cast (POItem.PurchaseOrderItem as char10) end as PrecedingDocumentItem,
key PurDocHistory.PurchasingHistoryDocument as SubsequentDocument,
key cast (PurDocHistory.PurchasingHistoryDocumentItem as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and PurDocHistory.ReferenceDocument not like '5%'
and PurDocHistory.IsCompletelyDelivered = ''
and POItem.ProductType = '2'
then cast ('S' as bstyp)
else cast ('F' as bstyp) end as PrecedingDocumentCategory,
cast ('G' as bstyp) as SubsequentDocumentCategory,
POItem.PurchaseOrder,
POItem.PurchaseOrderItem,
_PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
cast('' as fis_bukrs) as CompanyCode,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrder.PurchasingOrganization
}
where PurDocHistory.PurchasingHistoryDocumentType = '1'
and (PurDocHistory.PurchasingHistoryCategory = '9' or PurDocHistory.PurchasingHistoryCategory = 'E')
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// PO/(SES-GR) -> IV
union all select distinct from I_PurchaseOrderItem as POItem
inner join I_PurchaseOrderHistoryBasic as PurDocHistory on POItem.PurchaseOrder = PurDocHistory.PurchaseOrder
and POItem.PurchaseOrderItem = PurDocHistory.PurchaseOrderItem
association to I_PurchaseOrder as _PurchaseOrder on POItem.PurchaseOrder = _PurchaseOrder.PurchaseOrder
{
case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '2'
then PurDocHistory.ReferenceDocument
when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '1'
then PurDocHistory.ReferenceDocument
else POItem.PurchaseOrder end as PrecedingDocument,
case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '2'
then cast (PurDocHistory.ReferenceDocumentItem as char10)
when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '1'
then cast (PurDocHistory.ReferenceDocumentItem as char10)
else cast (POItem.PurchaseOrderItem as char10) end as PrecedingDocumentItem,
key PurDocHistory.PurchasingHistoryDocument as SubsequentDocument,
key cast (PurDocHistory.PurchasingHistoryDocumentItem as char10) as SubsequentDocumentItem,
key PurDocHistory.PurchasingHistoryDocumentYear as MaterialDocumentYear,
//Case (SES)-GR -> IV
case when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '2'
then cast ('S' as bstyp)
//Case GR -> IV
when PurDocHistory.ReferenceDocument != ''
and PurDocHistory.ReferenceDocument != PurDocHistory.PurchasingHistoryDocument
and POItem.ProductType = '1'
then cast ('G' as bstyp)
//Case PO -> IV
else cast ('F' as bstyp) end as PrecedingDocumentCategory,
cast ('2' as bstyp) as SubsequentDocumentCategory,
POItem.PurchaseOrder,
POItem.PurchaseOrderItem,
_PurchaseOrder.PurchaseOrderType,
PurDocHistory.PurchasingHistoryDocumentYear as FiscalYear,
cast('' as fis_bukrs) as CompanyCode,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrder.PurchasingOrganization
}
where (( PurDocHistory.PurchasingHistoryDocumentType = '2' or PurDocHistory.PurchasingHistoryDocumentType = 'P' ) and
//filter BEWTP from table t163b
(PurDocHistory.PurchasingHistoryCategory = '4' or PurDocHistory.PurchasingHistoryCategory = '5' or PurDocHistory.PurchasingHistoryCategory = '6'
or PurDocHistory.PurchasingHistoryCategory = 'Q' or PurDocHistory.PurchasingHistoryCategory = 'R' or PurDocHistory.PurchasingHistoryCategory = 'T' ))
//note 3032130 - start
or (PurDocHistory.PurchasingHistoryDocumentType = '3' and
(PurDocHistory.PurchasingHistoryCategory = 'B' or PurDocHistory.PurchasingHistoryCategory = 'N' or
PurDocHistory.PurchasingHistoryCategory = 'P' or PurDocHistory.PurchasingHistoryCategory = 'S' or
PurDocHistory.PurchasingHistoryCategory = 'X'))
//note 3032130 - end