P_SlsOrdConfAnalytics

DDL: P_SLSORDCONFANALYTICS SQL: PSDSLSORDCONF Type: view COMPOSITE Package: VDM_SD_ANALYTICS

Aggregated Schedule Lines for Confirmation Info

P_SlsOrdConfAnalytics is a Composite CDS View that provides data about "Aggregated Schedule Lines for Confirmation Info" in SAP S/4HANA. It reads from 2 data sources (I_SalesDocumentItem, I_SalesDocumentScheduleLine) and exposes 10 fields with key fields SalesDocument, SalesDocumentItem. Part of development package VDM_SD_ANALYTICS.

2 data source(s) 10 fields

Data Sources (2)

SourceAliasJoin Type
I_SalesDocumentItem SDI inner
I_SalesDocumentScheduleLine SL from

Annotations (10)

NameValueLevelField
ClientHandling.algorithm #SESSION_VARIABLE view
ObjectModel.usageType.dataClass #MIXED view
ObjectModel.usageType.serviceQuality #D view
ObjectModel.usageType.sizeCategory #L view
VDM.private true view
VDM.viewType #COMPOSITE view
AccessControl.authorizationCheck #NOT_REQUIRED view
AbapCatalog.sqlViewName PSDSLSORDCONF view
AbapCatalog.preserveKey true view
AbapCatalog.compiler.compareFilter true view

Fields (10)

KeyFieldSource TableSource FieldDescription
KEY SalesDocument I_SalesDocumentScheduleLine SalesDocument
KEY SalesDocumentItem I_SalesDocumentScheduleLine SalesDocumentItem
BaseUnit
AvgDelay
SumDelay
MaxDelay
2018DownportOP1709CM177172
Plant I_SalesDocumentItem Plant
CreationDate I_SalesDocumentItem CreationDate
MaterialGroup I_SalesDocumentItem MaterialGroup 
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PSDSLSORDCONF'
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true

define view P_SlsOrdConfAnalytics
as select from I_SalesDocumentScheduleLine as SL 
inner join     I_SalesDocumentItem as SDI on SL.SalesDocument = SDI.SalesDocument and SL.SalesDocumentItem = SDI.SalesDocumentItem   --AT 9.5.2018 & Downport OP1709 CM 177172
{
   key SL.SalesDocument,
    
   key SL.SalesDocumentItem,

   cast(
    sum(
     case when   SL.IsRequestedDelivSchedLine = 'X' and SL.IsConfirmedDelivSchedLine = 'X' and SL.OrderToBaseQuantityDnmntr != 0
          then   cast(division(SL.ConfdOrderQtyByMatlAvailCheck * SL.OrderToBaseQuantityNmrtr,  SL.OrderToBaseQuantityDnmntr ,3 ) as ovr_conf_so_sl_qty)
          else   cast(0 as ovr_conf_so_sl_qty) 
          end 
       ) as  ovr_conf_so_sl_qty )  as SlsDocItmSchdLnConfdQty,   --Quantity Confirmed in Time (CiTQ)
      
    SL.BaseUnit,

    cast(
     avg(
     case  when  SL.IsRequestedDelivSchedLine = '' and SL.IsConfirmedDelivSchedLine = 'X' 
           then Dats_Days_Between( SL.ConfdSchedLineReqdDelivDate, SL.ConfirmedDeliveryDate) 
           else  0 
           end
        ) as  avg_delay_so_itm )  as AvgDelayOfSchdLnConfdToReqdDte, --AvgDelay,
    
    cast(
     sum(
      case  when  SL.IsRequestedDelivSchedLine = '' and SL.IsConfirmedDelivSchedLine = 'X' 
            then Dats_Days_Between( SL.ConfdSchedLineReqdDelivDate, SL.ConfirmedDeliveryDate)  
            else 0 
            end
         ) as    sum_delay_so_itm )  as TotDelayOfSchdLnConfdToReqdDte, --SumDelay ,
      
    cast( 
     max(
      case  when  SL.IsRequestedDelivSchedLine = '' and SL.IsConfirmedDelivSchedLine = 'X' 
            then Dats_Days_Between( SL.ConfdSchedLineReqdDelivDate, SL.ConfirmedDeliveryDate)  
            else 0 
            end
        ) as max_delay_so_itm )  as MaxDelayOfSchdLnConfdToReqdDte, --MaxDelay ,
         
    cast( 
     min(
      case  when SL.IsRequestedDelivSchedLine = '' and SL.IsConfirmedDelivSchedLine = 'X' 
            then Dats_Days_Between( SL.ConfdSchedLineReqdDelivDate, SL.ConfirmedDeliveryDate )  
            else 0
            end
         ) as min_delay_so_itm )     as MinDelayOfSchdLnConfdToReqdDte --MINDelay
         
     , SDI.TotalDeliveryStatus                                            --AT 9.5.2018 & Downport OP1709 CM 177172
     , SDI.Plant                                            
     , SDI.CreationDate
     , SDI.MaterialGroup
     
}
where  SL.IsConfirmedDelivSchedLine = 'X' -- for our analysis here, only confirmed ones are relevant ! Ex. for AvgDelay we don't consider 10 0, would result in 1 too much denominator                                        --                                                           Ex. for MinDelay we don't consider 10 0, would always result in 0           
group by SL.SalesDocument, SL.SalesDocumentItem, SL.BaseUnit
       , SDI.TotalDeliveryStatus, SDI.Plant, SDI.CreationDate, SDI.MaterialGroup  --AT 9.5.2018 & Downport OP1709 CM 177172

Learn More