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

[k_sood]

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