P_SlsOrdNotDelivdDueDte

@ClientHandling.algorithm: #SESSION_VARIABLE 
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #S
@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck:#NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PSODUEDATEREUSE1'

/*

 .........Description "SO/notYetDelivered":...............  
   To a given SalesOrder the possible DueDate is selected from min(DeliveryDocumentDate): 
    first choice: from related confirmed open SLs  or 
    second choice: related requested SLs. 
    third choice: If both choices are missing, the RequestedDeliveryDate is taken.
   The calculated attribute DueDate contains the final choice.
   
 Result: 
   SO   (SalesOrg,Distibution,Division)    with DueDate

 Note:  in case a given sales order  ABC has both a billing and a delivery block formulated as user status, 
        there are two entries to this sales order ABC in this model. However this is unproblematic, since in all 
        use scenarios it is ensured in the reusing interface models that only one entry is selected during runtime.
        

*/


define view P_SlsOrdNotDelivdDueDte
as select from I_SalesDocumentBasic as SOH

left outer to many join I_SalesDocumentScheduleLine   as ConfirmedSL --to get first preference of DueDate
on SOH.SalesDocument = ConfirmedSL.SalesDocument and
ConfirmedSL.IsConfirmedDelivSchedLine = 'X' and ConfirmedSL.OpenConfdDelivQtyInOrdQtyUnit > 0

left outer to many join I_SalesDocumentScheduleLine   as RequestedSL --to get second preference of DueDate
on SOH.SalesDocument = RequestedSL.SalesDocument and
RequestedSL.IsRequestedDelivSchedLine = 'X' and RequestedSL.OpenReqdDelivQtyInOrdQtyUnit > 0

left outer to one join I_SDDocControllingObjectSts as COS  -- "to one" should lead to "branch pruning" in use scenarios where ControllingObjectStatus is not relevant
on SOH.ControllingObject = COS.ControllingObject and
   not COS.StatusIsInactive = 'X' and
   COS.ControllingObjectCategory = 'VBK' and
   ( COS.BusinessTransactionType = 'SDDN' or COS.BusinessTransactionType = 'SDIN' ) and
   COS.BusTransacExecutionAllowance = '3'

{
  //Key

  key SOH.SalesDocument,
  
  //Organization

  SOH.SalesOrganization,
  SOH.DistributionChannel,
  SOH.OrganizationDivision,
  SOH.SalesDocumentType,
  
  //DueDate

  min( case when ConfirmedSL.DeliveryCreationDate > '00000000' 
             then ConfirmedSL.DeliveryCreationDate 
             else
                case when RequestedSL.DeliveryCreationDate > '00000000' 
                then RequestedSL.DeliveryCreationDate 
                else SOH.RequestedDeliveryDate 
                end
       end 
      ) 
    as DueDate,
  
  //Status

  SOH.OverallBillingBlockStatus,    --needed for BillingBlock
  SOH.DeliveryBlockReason,          --needed for DeliveryBlock
  SOH.HeaderBillingBlockReason,     --needed for BillingBlock
  SOH.OverallTotalDeliveryStatus,   --needed for DeliveryBlock, BillingBlock
  SOH.OverallOrdReltdBillgStatus,   --needed for BillingBlock, Incompletion
  SOH.HdrGeneralIncompletionStatus, --needed for SOIncomplete 
  SOH.OverallPricingIncompletionSts,--needed for SOIncomplete
  SOH.HeaderDelivIncompletionStatus,--needed for SOIncomplete
  SOH.HeaderBillgIncompletionStatus, --needed for SOIncomplete
  SOH.TotalCreditCheckStatus,
  COS.BusinessTransactionType,       --needed for ControllingObjectStatus BillingBlocked & DeliveryBlock 
  COS.BusTransacExecutionAllowance   --needed for ControllingObjectStatus BillingBlocked & DeliveryBlock

  
}
where -- all "sales order like" categories/vbtyps
  SOH.SDDocumentCategory = 'C' or
  SOH.SDDocumentCategory = 'I' or
  SOH.SDDocumentCategory = 'L' 

group by SOH.SalesDocument, 
         SOH.SalesOrganization, 
         SOH.DistributionChannel, 
         SOH.OrganizationDivision, 
         SOH.SalesDocumentType, 
         SOH.OverallBillingBlockStatus,
         SOH.DeliveryBlockReason, 
         SOH.HeaderBillingBlockReason, 
         SOH.OverallTotalDeliveryStatus, 
         SOH.OverallOrdReltdBillgStatus,
         SOH.HdrGeneralIncompletionStatus, 
         SOH.OverallPricingIncompletionSts,
         SOH.HeaderDelivIncompletionStatus,
         SOH.HeaderBillgIncompletionStatus,
         SOH.TotalCreditCheckStatus,
         COS.BusinessTransactionType,
         COS.BusTransacExecutionAllowance  

                                   
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_SALESDOCUMENTBASIC",
"I_SALESDOCUMENTSCHEDULELINE",
"I_SDDOCCONTROLLINGOBJECTSTS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/