cdstable-functionss4hana

Table Function to get first row of MSEG in CDS view

I'm trying to build a CDS view that uses various fields from invoices in VBRK and VBRP. Another requirement is to display the price listed in the original purchase order (for example, I'm selling kiwis to someone and I want to display the original purchase price I paid). I'm supposed to use the connection to MSEG with parameter batch (MSEG-CHARG). The assumption here is that for every batch there is only one purchase order. I'm not sure how to make that connection from the invoices, though.

This is my basic CDS view:

@AbapCatalog.sqlViewName: <view_name>
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: '<text>'
@VDM.viewType: #BASIC

define view <view_name> as select distinct from I_BillingDocumentItemCube( P_ExchangeRateType: 'M', P_DisplayCurrency: 'EUR' )

{
  key BillingDocument,
  key BillingDocumentItem,
      BillingDocumentType,
      _BillingDocument._Item._PricingElement[ConditionType = 'XXX1'].ConditionRateValue as cost1,
      _BillingDocument._Item._PricingElement[ConditionType = 'XXX2'].ConditionRateValue  as cost2,
      SoldToParty,
      SoldToPartyName,
      Material,
      BillingDocumentItemText,
      Batch,
      BillingDocumentDate,
      BillingQuantity,
      BillingQuantityUnit,
      SalesDocumentItemCategory
};

I tried using a table function to select the responding batch from MSEG but I'm not sure how to connect it to the CDS view.

@EndUserText.label: '<name>'
define table function <table_function>
with parameters @Environment.systemField: #CLIENT
                clnt: abap.clnt,
                charg: charg_d
    returns {
      clnt      : abap.clnt;
      charg_exp : charg_d;
      dmbtr     : dmbtr_cs;
      menge     : menge_d;
    }
    implemented by method <class> => <method>;

class:

CLASS <class_name> DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    CLASS-METHODS <method_name> FOR TABLE FUNCTION <table_function>.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS <class_name> IMPLEMENTATION.
    METHOD <method_name>
    BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING nsdm_e_mseg.
    RETURN select top 1 mandt as clnt, charg as charg_exp, dmbtr, menge
           from nsdm_e_mseg
           where mandt = :clnt
             and charg = :charg
             and dmbtr > 0
             and menge > 0
             order by mblnr;
    ENDMETHOD.
ENDCLASS.

How can I use this table function in my basic CDS view to connect the position in VBRP to the position in MSEG?

Solution

  • Generally the linking between MSEG and VBRP is following:

    VBRK-vbeln -> VBRP-vbeln    VBFA-vbeln -> MSEG-mblnr
...           VBRP-posnr    VBFA-posnn -> MSEG-zeile
...           ...           ...
...           ...           vbtyp_n='R'
...           VBRP-aubel -> VBFA-vbelv
...           VBRP-aupos -> VBFA-posnv

    However, I do not exactly now how I_BillingDocumentItemCube is built as I am not very familiar with S4HANA analytical cubes, does it really project to VBRK/VBRP?

    Anyway MSEG table has a MBLNR/MJAHR/ZEILE key so passing batch (CHARG) is definitely not sufficient, because it does not uniquely define Purchase Order. Even though you assume it is a 1:1 relation of PO:Billing, it can be also multiple similar material (MATNR) positions within the same Batch and the same Purchase Order with different prices.

    Look at the MCHB table, and please note that only Plant/StorageLoc/Batch (MATNR/WERKS/LGORT) combination unambiguously defines material doc.

    To conclude, at the very least you should pass into the method these 4 parameters which you will derive from I_BillingDocumentItemCube:

    METHOD <method_name>
BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING nsdm_e_mseg.
RETURN select top 1 mandt as clnt, charg as charg_exp, dmbtr, menge
       from nsdm_e_mseg
       where mandt = :clnt
         and mblnr = :mblnr
         and mjahr = :mjahr
         and zeile = :zeile
         and charg = :charg
         and dmbtr > 0
         and menge > 0
         order by mblnr;
ENDMETHOD.

    Last but not least, to conform to the new S4HANA data model, why don't you read MATDOC table directly? NSDM_E_MSEG is nothing more than legacy wrapper, so-called compatibility view that redirects to MATDOC table with some parameters, check 2206980 note enter image description here

    It may be a nice start to use new tables for a smoother shift to S4HANA model.