@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #CONSUMPTION
@AbapCatalog.sqlViewName: 'PCUSTRETPROCF10'
define view P_CustRetProcFlow10
as select from P_CustRetProcFlow0 as Level0 --> Level 0
inner join I_SDDocumentProcessFlow as Level1 --> Level 1
on Level0.CustomerReturn = Level1.PrecedingDocument
and(
Level1.SubsequentDocumentCategory = 'T' --> Returns Delivery
or(
Level1.SubsequentDocumentCategory = 'I'
and Level1.SubsequentDocumentItem <> '000000'
) --> Order w/o charge as Replacement Order
or(
Level1.SubsequentDocumentCategory = 'K'
and Level1.SubsequentDocumentItem <> '000000'
) --> Credit Memo Request
or Level1.SubsequentDocumentCategory = 'J' --> Outbound Delivery
or Level1.SubsequentDocumentCategory = 'O' --> Credit Memo
or Level1.SubsequentDocumentCategory = 'M' --> Customer Invoice
or Level1.SubsequentDocumentCategory = 'U' --> Pro Forma Invoice
or Level1.SubsequentDocumentCategory = 'P' --> Debit Memo
)
left outer to one join I_DeliveryDocument as DeliveryDoc --> Delivery Status
on Level1.SubsequentDocument = DeliveryDoc.DeliveryDocument
and Level1.SubsequentDocumentCategory = DeliveryDoc.SDDocumentCategory
left outer to one join I_SalesDocumentBasic as SalesDoc on Level1.SubsequentDocument = SalesDoc.SalesDocument
and Level1.SubsequentDocumentCategory = SalesDoc.SDDocumentCategory
left outer to one join I_BillingDocument as BillingDoc on Level1.SubsequentDocument = BillingDoc.BillingDocument
{
//Key
key Level0.CustomerReturn as Level0Document,
key Level0.SDDocumentCategory as Level0DocumentCategory,
key Level1.SubsequentDocument as Level1Document,
key Level1.SubsequentDocumentItem as Level1DocumentItem,
//Category
key case
when
( Level1.SubsequentDocumentCategory = 'K' or --> Credit Memo Request
Level1.SubsequentDocumentCategory = 'L' ) --> Debit Memo Request
--- category taken directly from Sales Document instead of from SDDocumentProcessFlow
then
coalesce( SalesDoc.SDDocumentCategory, Level1.SubsequentDocumentCategory )
when
( Level1.SubsequentDocumentCategory = 'O' or --> Credit Memo
Level1.SubsequentDocumentCategory = 'P' ) --> Debit Memo
--- category taken directly from Billing Document instead of from SDDocumentProcessFlow
then
coalesce( BillingDoc.SDDocumentCategory, Level1.SubsequentDocumentCategory )
else
Level1.SubsequentDocumentCategory
end as Level1DocumentCategory,
//Customer Return
Level0.CustomerReturn,
Level0.CustomerReturnType,
//Organization
Level0.SalesOrganization,
Level0.DistributionChannel,
Level0.OrganizationDivision,
Level1.PrecedingDocumentItem,
//Relevance for planned subsequent processing
case
when
( SalesDoc.OverallTotalDeliveryStatus = 'A' or
SalesDoc.OverallTotalDeliveryStatus = 'B' )
then
'X'
else
''
end as DeliveryDocumentIsRequired,
case
when
( DeliveryDoc.OverallDelivReltdBillgStatus = 'A' or
DeliveryDoc.OverallDelivReltdBillgStatus = 'B' or
SalesDoc.OverallOrdReltdBillgStatus = 'A' or
SalesDoc.OverallOrdReltdBillgStatus = 'B' )
then
'X'
else
''
end as BillingDocumentIsRequired
}
union all select from P_CustRetProcFlow0 as Level0
left outer to many join P_CustRetProcFlow04 as Level1 on Level0.CustomerReturn = Level1.CustomerReturn
and Level1.ReturnsDocumentType = '13' --> Order w/o charge as Replacement Order
left outer to one join I_SalesDocumentBasic as SalesDoc on Level1.ReturnsDocument = SalesDoc.SalesDocument
and SalesDoc.SDDocumentCategory = 'I' --> Order w/o charge as Replacement Order
{
//Key
key Level0.CustomerReturn as Level0Document,
key Level0.SDDocumentCategory as Level0DocumentCategory,
key Level1.ReturnsDocument as Level1Document,
key Level1.ReturnsDocumentItem as Level1DocumentItem,
//Category
key SalesDoc.SDDocumentCategory as Level1DocumentCategory,
//Customer Return
Level0.CustomerReturn,
Level0.CustomerReturnType,
//Organization
Level0.SalesOrganization,
Level0.DistributionChannel,
Level0.OrganizationDivision,
Level1.CustomerReturnItem as PrecedingDocumentItem,
//Relevance for planned subsequent processing
case
when
( SalesDoc.OverallTotalDeliveryStatus = 'A' or
SalesDoc.OverallTotalDeliveryStatus = 'B' )
then
'X'
else
''
end as DeliveryDocumentIsRequired,
case
when
( SalesDoc.OverallOrdReltdBillgStatus = 'A' or
SalesDoc.OverallOrdReltdBillgStatus = 'B' )
then
'X'
else
''
end as BillingDocumentIsRequired
}
where
Level1.ReturnsDocument is not initial
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLINGDOCUMENT",
"I_DELIVERYDOCUMENT",
"I_SALESDOCUMENTBASIC",
"I_SDDOCUMENTPROCESSFLOW",
"P_CUSTRETPROCFLOW0",
"P_CUSTRETPROCFLOW04"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/