Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
k_sood
Active Participant
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
Labels in this area