P_SlsOrdConfAnalytics

@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
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_SALESDOCUMENTITEM",
"I_SALESDOCUMENTSCHEDULELINE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/