FINOC_RT_SOI_OBJ_STATUS

@AbapCatalog.sqlViewName: 'FINOCV_RTSOIOSTT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@Metadata.ignorePropagatedAnnotations: true
@EndUserText.label: 'Orgl Chg: SDocItms with object status'

// Status and age dependent selection of sales order items works as follows (see also CL_FAGL_R_OBJ_TYPE_001_SO->SELECT_VIA_MATERIAL):

// 1. If VBAK-OBJNR is not initial (it is a CO object) and the sales order item has JEST-status "closed", "deletion flag" or "deletion indicator" -> exclude.

// 2. If VBAK-OBJNR is initial (it is not a CO object) and ( VBAP-GBSTA='C' (completely processed) or the sales order item is older than X months ) -> exclude.

// 3. If the sales order item is assigned to a CPM project (VBAP-AUART = 'SO01'), the status is irrelevant.


define view FINOC_RT_SOI_OBJ_STATUS
  as select from  I_SalesDocumentItem       as SalesDocumentItem
  inner join      I_SalesDocument           as SalesDocument           on  SalesDocumentItem.SalesDocument     =  SalesDocument.SalesDocument
  left outer join FINOC_RT_SOI_MONTHS       as SalesDocumentItemMonths on  SalesDocumentItem.SalesDocument     =  SalesDocumentItemMonths.SalesDocument
                                                                       and SalesDocumentItem.SalesDocumentItem =  SalesDocumentItemMonths.SalesDocumentItem
                                                                       and SalesDocumentItem.ControllingObject =  ''  // status/age handling for SD items, which are no CO objects -> in calling CDS view

                                                                       and ( SalesDocumentItem.SDProcessStatus   = 'C' // completed

                                                                          or SalesDocument.SalesDocumentType     = 'SO01' // or assigned to CPM Cloud Project

                                                                       )
  left outer join jest                      as StatusObject            on  SalesDocumentItem.ControllingObject = StatusObject.objnr
                                                                       and( // checking status of sales order items, which are CO objects

                                                                           StatusObject.inact = ''
                                                                           and(
                                                                                StatusObject.stat  = 'I0046' // closed

                                                                             or StatusObject.stat  = 'I0076' // deletion flag

                                                                             or StatusObject.stat  = 'I0013' // deletion indicator

                                                                          )
                                                                       )
{
  key SalesDocumentItem.SalesDocument     as SalesDocument,
  key SalesDocumentItem.SalesDocumentItem as SalesDocumentItem,
      coalesce(
        SalesDocumentItemMonths.CreationDatePlusMonths,
        cast('99991231' as dats))         as CreationDatePlusMonths
}
where
  (     SalesDocumentItem.ControllingObject            <> ''   // status handling for SD items, which are CO objects

    and StatusObject.stat                              is null // not closed or having deletion flag or indicator

  )
  or    
  (     SalesDocumentItem.ControllingObject            =  ''   // status/age handling for SD items, which are no CO objects -> in calling CDS view

    and (    SalesDocumentItemMonths.CreationDatePlusMonths is not null
          or SalesDocumentItem.SDProcessStatus  <> 'C' // not completed

        )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FINOC_RT_SOI_MONTHS",
"I_SALESDOCUMENT",
"I_SALESDOCUMENTITEM",
"JEST"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/