CDS to calculate Invoice Receipt for Purchase Order Item

[guillaume_bouzebra]

Hi,

Trying to implement the following scenario with CDS: select purchase order items with their respective invoice receipt quantity:

define view Z_P_POItemIRCalc1
  as select from I_PurchaseOrderHistory        // EKBE
{
  key PurchaseOrder,
  key PurchaseOrderItem,
  
  case when DebitCreditCode = 'S'
          then Quantity
       else (-1 * Quantity)
  end as InvoiceReceiptQty
}
where PurchaseOrderTransactionType = '2'     //EKBE-VGABE = 2

define view Z_P_POItemIRQty
  as select from Z_P_POItemIRCalc1
{
  key PurchaseOrder,
  key PurchaseOrderItem,
      sum (InvoiceReceiptQty)    as InvoiceReceiptQty
}
group by
  PurchaseOrder,
  PurchaseOrderItem

define view Z_C_ConsumptionView
  as select from I_PurchaseOrderItem  as POItem         //EKPO
       association [1..1] to Z_P_POItemIRQty as _POItemIR   
                    on  POItem.PurchaseOrder = _POItemIR.PurchaseOrder
                    and POItem.PurchaseOrderItem = _POItemIR.PurchaseOrderItem
{
  key PurchaseOrder,
  key PurchaseOrderItem,
  InvoiceReceiptQty,
 
  .... + several other PO fields
}

The issue is that when filtering on the purchase orders, like for example:

SELECT [fields....] FROM Z_C_ConsumptionView
WHERE PurchasingGroup = 'XXX' 
AND   PurchaseOrderDate BETWEEN '20180101' AND '20180401'

all the entries in EKBE are selected, which is 5.000.000+ records in our system and makes the query unusable. This seems to be because of the Not NULL-preserving CASE statement in XXXXXX.

Any idea how to replace this CASE statement and still provides the correct sign for the SUM to be correct?

[thomasgauweiler]

avoid the -1 (which causes the Not NULL preserving effect) by aggregating positive and negative values separately (which also make aggregation much faster):

define view Z_P_POITEMIRCALC2a
  as select from I_PurchaseOrderHistory
{
  PurchaseOrder,
  PurchaseOrderItem,
  Quantity as InvoiceReceiptQty
}
where PurchaseOrderTransactionType = '2'
  and DebitCreditCode              = 'S'

and

define view Z_P_POITEMIRCALC2b
  as select from I_PurchaseOrderHistory
{
  PurchaseOrder,
  PurchaseOrderItem,
  Quantity as InvoiceReceiptQty
}
where PurchaseOrderTransactionType = '2'
  and DebitCreditCode              <> 'S'

and

define view Z_P_POITEMIRQTY2
  as select from I_PurchaseOrderItem as h
    left outer to one join Z_P_POITEMIRCALC2a as p 
       on p.PurchaseOrder = h.PurchaseOrder 
      and p.PurchaseOrderItem = h.PurchaseOrderItem 
    left outer to one join Z_P_POITEMIRCALC2b as n 
       on n.PurchaseOrder = h.PurchaseOrder 
      and n.PurchaseOrderItem = h.PurchaseOrderItem
{
  key h.PurchaseOrder,
  key h.PurchaseOrderItem,
  sum(p.InvoiceReceiptQty) as InvoiceReceiptQtyPlus, -- can become NULL
  sum(n.InvoiceReceiptQty) as InvoiceReceiptQtyMinus -- can become NULL
}
group by
  h.PurchaseOrder,
  h.PurchaseOrderItem

Be careful, as the sums might be null:

define view Z_C_CONSUMPTIONVIEW2
  as select from I_PurchaseOrderItem as POItem //EKPO
  association [1..1] to ZTG_P_POITEMIRQTY2 as _POItemIR on  POItem.PurchaseOrder     = _POItemIR.PurchaseOrder
                                                       and POItem.PurchaseOrderItem = _POItemIR.PurchaseOrderItem
{
  key PurchaseOrder,
  key PurchaseOrderItem,
      coalesce( _POItemIR.InvoiceReceiptQtyPlus, 0 ) 
    - coalesce( _POItemIR.InvoiceReceiptQtyMinus, 0 ) as  InvoiceReceiptQty
}

Also be sure to tag your keys correctly: Your view Z_P_POItemIRCalc1 probably does not have a unique key. So better omit the keys completely there.

Regards, Thomas