P_CUSTRETPROCFLOW10

@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