@AbapCatalog.sqlViewName: 'CSESPROCESSFLOW'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #MANDATORY
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Process Flow For Service Entry Sheet'
define view C_ServiceEntrySheetProcessFlow
as
select from I_PurchaseOrderHistory as PurchasingDocument
inner join I_ServiceEntrySheetBasic as ServiceEntrySheet
on(
PurchasingDocument.MaterialDocument = ServiceEntrySheet.ServiceEntrySheet
and PurchasingDocument.PurchaseOrder = ServiceEntrySheet.PurchaseOrder
)
right outer join I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem
on(
ServiceEntrySheet.ServiceEntrySheet = ServiceEntrySheetItem.ServiceEntrySheet
and PurchasingDocument.MaterialDocumentItem = substring(
ServiceEntrySheetItem.ServiceEntrySheetItem, 2, 5
) //NUMC5 compared with NUMC4
and(
PurchasingDocument.PurchaseOrderTransactionType = 'S'
or PurchasingDocument.PurchaseOrderTransactionType = '1'
or PurchasingDocument.PurchaseOrderTransactionType = '2'
or PurchasingDocument.PurchaseOrderTransactionType = '3'
)
)
left outer join C_PlantVH as Plant on Plant.Plant = PurchasingDocument.Plant
{
key
case
when PurchasingDocument.ReferenceDocument = ''
or PurchasingDocument.ReferenceDocument = PurchasingDocument.MaterialDocument //ServiceEntrySheet.ServiceEntrySheet
then ltrim(cast(PurchasingDocument.PurchaseOrder as abap.char( 12 )),'0')
else ltrim(cast(PurchasingDocument.ReferenceDocument as abap.char( 12 )),'0')
end as PrecedingDocument,
key
case
//when PurchasingDocument.ReferenceDocumentItem = '0000'
when PurchasingDocument.ReferenceDocument = ''
or PurchasingDocument.ReferenceDocument = PurchasingDocument.MaterialDocument //ServiceEntrySheet.ServiceEntrySheet
then lpad(cast(ltrim(PurchasingDocument.PurchaseOrderItem,'0') as abap.char( 5 )),5, '0')
else lpad(cast(ltrim(PurchasingDocument.ReferenceDocumentItem,'0') as abap.char( 4 )),4, '0')
end as PrecedingDocumentItem,
key
case
when PurchasingDocument.ReferenceDocument = ''
or PurchasingDocument.ReferenceDocument = PurchasingDocument.MaterialDocument
then 'F'
else 'S'
end as PrecedingDocumentCategory,
key
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
and PurchasingDocument.PurchaseOrderTransactionType = 'S'
then ltrim(ServiceEntrySheetItem.OriginObject, '0')
else ltrim(cast(PurchasingDocument.MaterialDocument as abap.char( 12 )),'0')
end as SubsequentDocument,
key
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
and PurchasingDocument.PurchaseOrderTransactionType = 'S'
then '00000'
else lpad(cast(ltrim(PurchasingDocument.MaterialDocumentItem,'0') as abap.char( 4 )),4, '0') // Good receipt
end as SubsequentDocumentItem,
key case when PurchasingDocument.PurchaseOrderTransactionType = '1'
then PurchasingDocument.MaterialDocumentYear
else '0000'
end as MaterialDocumentYear,
PurchasingDocument.AccountAssignmentNumber,
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
//and PurchasingDocument.ReferenceDocument = '' !
then 'T'
else PurchasingDocument.PurchaseOrderTransactionType
end as SubsequentDocumentCategory,
case
when PurchasingDocument.ReferenceDocument = ''
then ltrim(PurchasingDocument.MaterialDocument,'0')
else ltrim(PurchasingDocument.ReferenceDocument,'0')
end as ServiceEntrySheet,
case
when PurchasingDocument.ReferenceDocumentItem = '0000'
then lpad(cast(ltrim(PurchasingDocument.MaterialDocumentItem,'0') as abap.char( 4 )),4, '0')
else lpad(cast(ltrim(PurchasingDocument.ReferenceDocumentItem,'0') as abap.char( 4 )),4, '0')
end as ServiceEntrySheetItem,
Plant.PlantName as PlantName,
case
when PurchasingDocument.DebitCreditCode = 'S'
then PurchasingDocument.Quantity
else PurchasingDocument.Quantity * (-1)
end as Quantity,
case when PurchasingDocument.PurchaseOrderTransactionType = '2'
then PurchasingDocument.MaterialDocumentYear
else '0000'
end as FiscalYear,
PurchasingDocument.PostingDate,
@Semantics.amount.currencyCode: 'Currency'
case
when PurchasingDocument.DebitCreditCode = 'S'
then PurchasingDocument.PurchaseOrderAmount
else PurchasingDocument.PurchaseOrderAmount * (-1)
end as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
PurchasingDocument.Currency as Currency,
ServiceEntrySheet.PurchasingGroup,
ServiceEntrySheet.PurchasingOrganization,
ServiceEntrySheet.IsEndOfPurposeBlocked,
ServiceEntrySheet.ApprovalStatus
}
where
ServiceEntrySheetItem.IsDeleted is initial
and PurchasingDocument.PurchaseOrder is not initial
and PurchasingDocument.PurchaseOrderHistCategory <> 'K'
union all
select from I_PurchaseOrderHistory as PurchasingDocument
inner join I_ServiceEntrySheetBasic as ServiceEntrySheet
on(
PurchasingDocument.ReferenceDocument = ServiceEntrySheet.ServiceEntrySheet
and PurchasingDocument.ReferenceDocument <> PurchasingDocument.MaterialDocument
and PurchasingDocument.PurchaseOrder = ServiceEntrySheet.PurchaseOrder
)
right outer join I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem
on(
ServiceEntrySheet.ServiceEntrySheet = ServiceEntrySheetItem.ServiceEntrySheet
and PurchasingDocument.ReferenceDocumentItem = substring(
ServiceEntrySheetItem.ServiceEntrySheetItem, 2, 5
) //NUMC5 compared with NUMC4
and(
PurchasingDocument.PurchaseOrderTransactionType = 'S'
or PurchasingDocument.PurchaseOrderTransactionType = '1'
or PurchasingDocument.PurchaseOrderTransactionType = '2'
or PurchasingDocument.PurchaseOrderTransactionType = '3'
)
)
left outer join C_PlantVH as Plant on Plant.Plant = PurchasingDocument.Plant
{
key
case
when PurchasingDocument.ReferenceDocument <> ''
then ltrim(cast(PurchasingDocument.ReferenceDocument as abap.char( 12 )),'0')
else ltrim(cast(PurchasingDocument.PurchaseOrder as abap.char( 12 )),'0')
end as PrecedingDocument,
key
case
when PurchasingDocument.ReferenceDocumentItem = '0000'
then lpad(cast(ltrim(PurchasingDocument.PurchaseOrderItem,'0') as abap.char( 5 )),5, '0')
else lpad(cast(ltrim(PurchasingDocument.ReferenceDocumentItem,'0') as abap.char( 4 )),4, '0')
end as PrecedingDocumentItem,
key
case
when PurchasingDocument.ReferenceDocument = ''
then 'F'
else 'S'
end as PrecedingDocumentCategory,
key
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
and PurchasingDocument.PurchaseOrderTransactionType = 'S'
then ltrim(ServiceEntrySheetItem.OriginObject, '0')
else ltrim(cast(PurchasingDocument.MaterialDocument as abap.char( 12 )),'0')
end as SubsequentDocument,
key
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
and PurchasingDocument.PurchaseOrderTransactionType = 'S'
then '00000'
else lpad(cast(ltrim(PurchasingDocument.MaterialDocumentItem,'0') as abap.char( 4 )),4, '0')
end as SubsequentDocumentItem,
key case when PurchasingDocument.PurchaseOrderTransactionType = '1'
then PurchasingDocument.MaterialDocumentYear
else '0000'
end as MaterialDocumentYear,
PurchasingDocument.AccountAssignmentNumber,
case
when ServiceEntrySheet.ServiceEntrySheet is not null
and ServiceEntrySheet.OriginObjectType = 'TS'
and PurchasingDocument.ReferenceDocument = ''
then 'T'
else PurchasingDocument.PurchaseOrderTransactionType
end as SubsequentDocumentCategory,
case
when PurchasingDocument.ReferenceDocument = ''
then ltrim(PurchasingDocument.MaterialDocument,'0')
else ltrim(PurchasingDocument.ReferenceDocument,'0')
end as ServiceEntrySheet,
case
when PurchasingDocument.ReferenceDocumentItem = '0000'
then lpad(cast(ltrim(PurchasingDocument.MaterialDocumentItem,'0') as abap.char( 4 )),4, '0')
else lpad(cast(ltrim(PurchasingDocument.ReferenceDocumentItem,'0') as abap.char( 4 )),4, '0')
end as ServiceEntrySheetItem,
Plant.PlantName as PlantName,
case
when PurchasingDocument.DebitCreditCode = 'S'
then PurchasingDocument.Quantity
else PurchasingDocument.Quantity * (-1)
end as Quantity,
case when PurchasingDocument.PurchaseOrderTransactionType = '2'
then PurchasingDocument.MaterialDocumentYear
else '0000'
end as FiscalYear,
PurchasingDocument.PostingDate,
@Semantics.amount.currencyCode: 'Currency'
case
when PurchasingDocument.DebitCreditCode = 'S'
then PurchasingDocument.PurchaseOrderAmount
else PurchasingDocument.PurchaseOrderAmount * (-1)
end as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
PurchasingDocument.Currency as Currency,
ServiceEntrySheet.PurchasingGroup,
ServiceEntrySheet.PurchasingOrganization,
ServiceEntrySheet.IsEndOfPurposeBlocked,
ServiceEntrySheet.ApprovalStatus
}
where
ServiceEntrySheetItem.IsDeleted is initial
and PurchasingDocument.PurchaseOrder is not initial
and PurchasingDocument.PurchaseOrderHistCategory <> 'K'
union all
select from I_ServiceEntrySheetBasic as ServiceEntrySheet
inner join I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem on ServiceEntrySheet.ServiceEntrySheet = ServiceEntrySheetItem.ServiceEntrySheet
and ServiceEntrySheetItem.IsDeleted is initial
{
key ltrim(cast(ServiceEntrySheetItem.OriginObject as abap.char( 12 )),'0') as PrecedingDocument,
key '00000' as PrecedingDocumentItem,
key 'T' as PrecedingDocumentCategory,
key ltrim(ServiceEntrySheetItem.ServiceEntrySheet,'0') as SubsequentDocument,
//key lpad(cast(ltrim(substring(ServiceEntrySheetItem.ServiceEntrySheetItem, 2, 5), '0') as abap.char( 5 )), 5, '0') as SubsequentDocumentItem,
key lpad(cast(ltrim(ServiceEntrySheetItem.ServiceEntrySheetItem, '0') as abap.char( 4 )), 4, '0') as SubsequentDocumentItem,
key '0000' as MaterialDocumentYear,
'00' as AccountAssignmentNumber,
'S' as SubsequentDocumentCategory,
ltrim(ServiceEntrySheetItem.ServiceEntrySheet,'0') as ServiceEntrySheet,
lpad(cast(ltrim(substring(ServiceEntrySheetItem.ServiceEntrySheetItem, 2, 5), '0') as abap.char( 4 )), 4, '0') as ServiceEntrySheetItem,
'' as PlantName,
cast( 0 as abap.dec( 13, 3 ) ) as Quantity,
'0000' as FiscalYear,
'00000000' as PostingDate,
@Semantics.amount.currencyCode: 'Currency'
0 as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
ServiceEntrySheetItem.Currency as Currency,
ServiceEntrySheet.PurchasingGroup,
ServiceEntrySheet.PurchasingOrganization,
ServiceEntrySheet.IsEndOfPurposeBlocked,
ServiceEntrySheet.ApprovalStatus
}
where
ServiceEntrySheet.OriginObjectType = 'TS'
union all
select from I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem
inner join R_PurchaseOrderItem as PO on PO.PurchaseOrder = ServiceEntrySheetItem.PurchaseOrder
and PO.PurchaseOrderItem = ServiceEntrySheetItem.PurchaseOrderItem
{
key case
when ServiceEntrySheetItem.PurchaseContract is not initial
then ltrim(cast(ServiceEntrySheetItem.PurchaseContract as abap.char( 12 )),'0')
when PO.PurchaseContract is not initial
then ltrim(PO.PurchaseContract, '0')
end as PrecedingDocument,
key case
when ServiceEntrySheetItem.PurchaseContract is not initial
then lpad(cast(ltrim(ServiceEntrySheetItem.PurchaseContractItem,'0') as abap.char( 5 )),5,'0')
when PO.PurchaseContract is not initial
then lpad(cast(ltrim(PO.PurchaseContractItem,'0') as abap.char( 5 )),5, '0')
end as PrecedingDocumentItem,
key 'H' as PrecedingDocumentCategory,
key ltrim(ServiceEntrySheetItem.PurchaseOrder,'0') as SubsequentDocument,
key lpad(cast(ltrim(ServiceEntrySheetItem.PurchaseOrderItem,'0') as abap.char( 5 )),5, '0') as SubsequentDocumentItem,
key '0000' as MaterialDocumentYear,
'00' as AccountAssignmentNumber,
'F' as SubsequentDocumentCategory,
ltrim(ServiceEntrySheetItem.ServiceEntrySheet,'0') as ServiceEntrySheet,
lpad(cast(ltrim(ServiceEntrySheetItem.ServiceEntrySheetItem, '0') as abap.char( 4 )), 4, '0') as ServiceEntrySheetItem,
'' as PlantName,
cast( 0 as abap.dec( 13, 3 ) ) as Quantity,
'0000' as FiscalYear,
'00000000' as PostingDate,
@Semantics.amount.currencyCode: 'Currency'
0 as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
ServiceEntrySheetItem.Currency as Currency,
ServiceEntrySheetItem.PurchasingGroup,
ServiceEntrySheetItem.PurchasingOrganization,
ServiceEntrySheetItem.IsEndOfPurposeBlocked,
'' as ApprovalStatus
}
where
ServiceEntrySheetItem.PurchaseContract is not initial
or PO.PurchaseContract is not initial
union all
select from I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem
inner join R_PurchaseOrderItem
as PO on PO.PurchaseOrder = ServiceEntrySheetItem.PurchaseOrder
and PO.PurchaseOrderItem = ServiceEntrySheetItem.PurchaseOrderItem
inner join I_PurchaseContractItem as PC on PC.PurchaseContract = PO.PurchaseContract
and PC.PurchaseContractItem = PO.PurchaseContractItem
{
// key lpad(cast(ltrim(PC.PurchaseRequisition,'0') as abap.char( 12 )),12, '0') as PrecedingDocument,
key ltrim(cast(PC.PurchaseRequisition as abap.char( 12 )),'0') as PrecedingDocument,
// key lpad(cast(ltrim(PC.PurchaseRequisitionItem,'0') as abap.char( 5 )),5, '0') as PrecedingDocumentItem,
key lpad(cast(ltrim(PC.PurchaseRequisitionItem ,'0') as abap.char( 5 )),5, '0') as PrecedingDocumentItem,
key 'G' as PrecedingDocumentCategory,
key ltrim(PO.PurchaseContract,'0') as SubsequentDocument,
key lpad(cast(ltrim(PO.PurchaseContractItem,'0') as abap.char( 5 )),5, '0') as SubsequentDocumentItem,
key '0000' as MaterialDocumentYear,
'00' as AccountAssignmentNumber,
'H' as SubsequentDocumentCategory,
ltrim(ServiceEntrySheetItem.ServiceEntrySheet, '0') as ServiceEntrySheet,
lpad(cast(ltrim(ServiceEntrySheetItem.ServiceEntrySheetItem, '0') as abap.char( 4 )), 4, '0') as ServiceEntrySheetItem,
'' as PlantName,
cast( 0 as abap.dec( 13, 3 ) ) as Quantity,
'0000' as FiscalYear,
'00000000' as PostingDate,
@Semantics.amount.currencyCode: 'Currency'
0 as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
ServiceEntrySheetItem.Currency as Currency,
ServiceEntrySheetItem.PurchasingGroup,
ServiceEntrySheetItem.PurchasingOrganization,
ServiceEntrySheetItem.IsEndOfPurposeBlocked,
'' as ApprovalStatus
}
where
PO.PurchaseContract is not initial
and PC.PurchaseRequisition is not initial
union all
select from C_PurReqnProcessFlow as PurchaseRequisitionFlow
inner join I_PurchaseOrder as PO on(
PO.PurchaseOrder = PurchaseRequisitionFlow.PrecedingDocument
)
inner join I_ServiceEntrySheetItemBasic as ServiceEntrySheetItem on(
(
ServiceEntrySheetItem.ServiceEntrySheet = PurchaseRequisitionFlow.SubsequentDocument
)
and(
ServiceEntrySheetItem.ServiceEntrySheetItem = lpad(
PurchaseRequisitionFlow.SubsequentDocumentItem, 5, '0'
)
)
and(
ServiceEntrySheetItem.PurchaseOrder = PurchaseRequisitionFlow.PrecedingDocument
)
and(
ServiceEntrySheetItem.PurchaseOrderItem = PurchaseRequisitionFlow.PrecedingDocumentItem
)
)
{
key ltrim(cast(PurchaseRequisitionFlow.PurchaseRequisition as abap.char( 12 )),'0') as PrecedingDocument,
key lpad(cast(ltrim(PurchaseRequisitionFlow.PurchaseRequisitionItem,'0') as abap.char( 5 )),5, '0') as PrecedingDocumentItem,
key 'G' as PrecedingDocumentCategory,
key ltrim(PurchaseRequisitionFlow.PrecedingDocument, '0' ) as SubsequentDocument,
key lpad(cast(ltrim(PurchaseRequisitionFlow.PrecedingDocumentItem ,'0') as abap.char( 5 )),5, '0') as SubsequentDocumentItem,
key '0000' as MaterialDocumentYear,
'00' as AccountAssignmentNumber,
PurchaseRequisitionFlow.PrecedingDocumentCategory as SubsequentDocumentCategory,
ltrim(PurchaseRequisitionFlow.SubsequentDocument,'0') as ServiceEntrySheet,
lpad(cast(ltrim(PurchaseRequisitionFlow.SubsequentDocumentItem,'0') as abap.char( 4 )),4, '0') as ServiceEntrySheetItem,
//cast(PurchaseRequisitionFlow.SubsequentDocumentItem as abap.char( 5 )) as ServiceEntrySheetItem,
'' as PlantName,
cast( 0 as abap.dec( 13, 3 ) ) as Quantity,
'0000' as FiscalYear,
'00000000' as PostingDate,
@Semantics.amount.currencyCode: 'Currency'
0 as SupplierInvoiceItemAmount,
@Semantics.currencyCode:true
ServiceEntrySheetItem.Currency as Currency,
ServiceEntrySheetItem.PurchasingGroup,
ServiceEntrySheetItem.PurchasingOrganization,
ServiceEntrySheetItem.IsEndOfPurposeBlocked,
'' as ApprovalStatus
}
where
(
PurchaseRequisitionFlow.SubsequentDocumentCategory = 'S'
)
and(
PurchaseRequisitionFlow.SubsequentDocument is not initial
)