Implementing minus operation with null and left outer join in cds view

Introduction

I once had the requirement to select the PO positions which i implemented with CDS view using left outer join and Null as an alternative to the minus operation on the selection of records which is directly not available in CDS view. Hope it might be helpful for others.

Business Scenario Selection Criteria  :

1. Positions for which confirmation has been received but Inbound delivery has not yet created. i.e. there is a an EKES Entry for this position with confirmation type AB and the corresponding LA entry with the same amount and date does not exist .
2. Positions for which no Confirmation has been received and no Inbound delivery has been created. This case has to be checked since its possible to create the Inbound delivery without getting an acknowledgement also. In this case those Eket records have to be selected for which there are no Ekes entries with AB and LA exists.

In the conventional way, this selection can be achieved by looping on the Ekpo, eket and ekes tables, which is obviously not very performant.

Implementation with CDS view using null and left outer Join

CDS view directly does not support the minus operation on the selected records but there is an alternate way to achieve that .i.e. with the ”Null and left outer Join”

Here is the CDS view.

@AbapCatalog.sqlViewName: 'myview'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Selection of PO items for Inb Delivery creation Dim. Priv.'
define view myviews 

// select the AB ekes entries for which no LA records exists.
as select from I_PurchasingDocumentItem as poitem
inner join ekes on ekes.ebeln = poitem.PurchasingDocument     and
                   ekes.ebelp = poitem.PurchasingDocumentItem and
                   ekes.ebtyp = 'AB'      
left outer join ekes as ekesla on ekesla.ebeln = ekes.ebeln and
                                  ekesla.ebelp = ekes.ebelp and
                                  ekesla.ebtyp = 'LA'       and
                                  ekesla.eindt = ekes.eindt and
                                  ekesla.menge = ekes.menge
{   
   ekes.ebeln as ebeln,
   ekes.ebelp as ebelp,
   ekes.menge as menge,
   ekes.eindt as eindt,
   poitem.Plant as werks,
   poitem.StorageLocation as lgort,
   poitem.SupplierConfirmationControlKey as bstae, 
   poitem.PurchasingDocumentDeletionCode as loekz,
   poitem.IsCompletelyDelivered as elikz,
   poitem.IsReturnsItem as retpo,
   poitem.PurchasingDocumentItemCategory as pstyp,
   poitem.OrderQuantityUnit as meins,
   poitem.Material as matnr ,
   poitem.PurchasingDocumentItemText as txz01,
   poitem.GoodsReceiptIsExpected as wepos 
}
where  
       ekesla.ebelp is null
   and ekesla.ebeln is null  
   and
  ( 
    poitem.SupplierConfirmationControlKey = '0001' or 
    poitem.SupplierConfirmationControlKey = '0004' or 
    poitem.SupplierConfirmationControlKey = '0005' 
   )
   and 
    poitem.GoodsReceiptIsExpected = 'X'  //wepos
   
 union 

 
// Further select eket entries for which no ab entries exists and no LA entries exists.

select from I_PurchasingDocumentItem as poitem   
inner join eket on  eket.ebeln = poitem.PurchasingDocument                       and
                    eket.ebelp = poitem.PurchasingDocumentItem  
left outer join ekes as ekesab on ekesab.ebeln = eket.ebeln                      and
                                  ekesab.ebelp = eket.ebelp                      and
                                 (ekesab.ebtyp = 'AB' or ekesab.ebtyp = 'LA')                                  
{    
   eket.ebeln as ebeln,
   eket.ebelp as ebelp,
   eket.menge as menge,
   eket.eindt as eindt,
   poitem.Plant as werks,
   poitem.StorageLocation as lgort,
   poitem.SupplierConfirmationControlKey as bstae, 
   poitem.PurchasingDocumentDeletionCode as loekz,
   poitem.IsCompletelyDelivered as elikz,
   poitem.IsReturnsItem as retpo,
   poitem.PurchasingDocumentItemCategory as pstyp,
   poitem.OrderQuantityUnit as meins,
   poitem.Material as matnr ,
   poitem.PurchasingDocumentItemText as txz01,
   poitem.GoodsReceiptIsExpected as wepos 
}

 where  
     ekesab.ebelp is null                         and 
     ekesab.ebeln is null                         and
   ( 
    poitem.SupplierConfirmationControlKey = '0001' or 
    poitem.SupplierConfirmationControlKey = '0004' or 
    poitem.SupplierConfirmationControlKey = '0005' 
   )                                              and
    poitem.GoodsReceiptIsExpected = 'X'   //wepos
   

In the first selection, putting the condition on ekesla.ebeln = null and ekesla.ebelp = null , you select only those entries which are having the AB positions and no LA positions.

Similarly, In the second selection after the Union operator , those entries will be selected which are niether having any AB entries nor having any LA entries and the corresponding Eket entry will be selected.

Basically, the output of left outer join needs to be filtered using where in a way that you get only those records which you need.

If there are any questions,  please comment below.

Best regards

Ketan