Skip to Content
Author's profile photo Ramakrishna Kotha

Inventory Value at a given date range Including Serial number BW 7.4 on HANA

 

Purpose of this Document:
This approach helps
1. Tracking of material price at plant, valuation type which can be leveraged for any other reporting purpose
2. Splitting 0IC_C03 into serial number will help the IM reporting at detail level.
3. These reports should tie with Inventory G/L; these can be used to support FI Audits to show the corresponding value at detailed Inventory movements

Prerequisites:
From our POC we can see lot of performance improvement by BW upgrade from BW 7.4 SP4 to BW 7.4 SP12.
For example, in BW 7.4 SP4, the NCUM_REFP_UPD in table RSDCUBELOC is blank, this will not fully support standard marker concept of Inventory cube BW 7.4 on HANA.

 

After upgrade BW 7.4 SP12, the flag set to Y, it will take care of marker on Inventory cube on compression which will be useful in improving performance at report execution.

 

 

I strongly recommend to upgrade the system minimum to BW 7.4 SP5 to take the advantage of performance on Inventory reporting

If you are in lower version than BW 7.4 SP5 on HANA and still on BW 7.4 on HANA, then apply note 128398 , execute the program RSDD_SWITCH_NCUM_HANDLING to update NCUM_REFP_UPD to X.

 

Step 1

We have to create a data source on table MBEW to capture material price changes on daily basis.

Joins on Table:

 

Fields in the data source:

 

 

Step 2

Create a time dependent master data object in BW to store the material prices on daily basis with compounded to plant and valuation type (Usually Material price is based on the Valuation type and Plant)

 

 

 

Map the datasource to master data object.

 

 

After load, the data looks like below, I hide the material number for security reasons.

 

 

From Step1 and Step 2, we completed creating model to capture Material prices in BW, which will be used in Bex to derive Inventory Value ($) on a selected date.

 

Step 3

 

The standard cube 0IC_C03 is inventory movements posted at material document, but for our requirement we need inventory movement at more detail level that is at serial number.

 

For example:

 

Material Material Document Material Document Item Quantity
M1 MD1 1 10

 

The above document MD1 quantity 5 is for 5 serial numbers, so in our design this has to be split into 5 serial numbers

 

Material Material Document Material Document Item Serial number Quantity
M1 MD1 1 S1 1
M1 MD1 1 S2 1
M1 MD1 1 S3 1
M1 MD1 1 S4 1
M1 MD1 1 S5 1

 

Let’s have a look how to achieve this ……

Created DSO, to capture the serial number at material, material document, Material document item,

This DSO will be used in lookup, while loading data to Inventory cube from 2LIS_03_BF to split the transactions into Serial number.

Create DSO as below.

 

 

Create the Datasource in ECC based on the table OBJK, SER03 to extract the serial numbers at each material document.

Join on tables:

 

Fields on the datasource.

 

 

The mapping between datasource to DSO

 

 

In Step 3 , we have completed modeling to store the serial numbers in a DSO.

 

Step 4.

 

As we discussed the standard cube 0IC_C03 is the transactions posted at material documents, so create a new custom cube similar to 0IC_C03 and add serial number to the characteristics and also time dependent material price object which was created in Step 1.

 

 

Write a logic in the transformation to split each material document into serial number. Also the logic involves to capture the quantity for Receipts, Shipments, Scrap, Adjustments.

 

 

The logic in the end routine.

 

PROGRAM trans_routine.


*---------------------------------------------------------------------*
*       CLASS routine DEFINITION
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
CLASS lcl_transform DEFINITION.
  PUBLIC SECTION.

*  Attributs
    DATA:
      p_check_master_data_exist
            TYPE RSODSOCHECKONLY READ-ONLY,
*-    Instance for getting request runtime attributs;
*     Available information: Refer to methods of
*     interface 'if_rsbk_request_admintab_view'
      p_r_request
            TYPE REF TO if_rsbk_request_admintab_view READ-ONLY.

  PRIVATE SECTION.

    TYPE-POOLS: rsd, rstr.

*   Rule specific types
    TYPES:
      BEGIN OF _ty_s_TG_1,
*      InfoObject: 0CHNGID Change Run ID.
        CHNGID           TYPE /BI0/OICHNGID,
*      InfoObject: 0RECORDTP Record type.
        RECORDTP           TYPE /BI0/OIRECORDTP,
*      InfoObject: 0REQUID Request ID.
        REQUID           TYPE /BI0/OIREQUID,
*      InfoObject: 0CALDAY Calendar day.
        CALDAY           TYPE /BI0/OICALDAY,
*      InfoObject: 0CALMONTH Calendar year/month.
        CALMONTH           TYPE /BI0/OICALMONTH,
*      InfoObject: 0CALWEEK Calendar year / week.
        CALWEEK           TYPE /BI0/OICALWEEK,
*      InfoObject: 0CALYEAR Calendar year.
        CALYEAR           TYPE /BI0/OICALYEAR,
*      InfoObject: 0MATERIAL Material.
        MATERIAL           TYPE /BI0/OIMATERIAL,
*      InfoObject: 0PLANT Plant.
        PLANT           TYPE /BI0/OIPLANT,
*      InfoObject: 0STOR_LOC Storage location.
        STOR_LOC           TYPE /BI0/OISTOR_LOC,
*      InfoObject: 0STOCKTYPE Stock type.
        STOCKTYPE           TYPE /BI0/OISTOCKTYPE,
*      InfoObject: 0RECTOTSTCK Receipt Quantity Total Stock.
        RECTOTSTCK           TYPE /BI0/OIRECTOTSTCK,
*      InfoObject: 0ISSTOTSTCK Issue Quantity Total Stock.
        ISSTOTSTCK           TYPE /BI0/OIISSTOTSTCK,
*      InfoObject: 0LOC_CURRCY Local currency.
        LOC_CURRCY           TYPE /BI0/OILOC_CURRCY,
*      InfoObject: 0BASE_UOM Base Unit of Measure.
        BASE_UOM           TYPE /BI0/OIBASE_UOM,
*      InfoObject: 0GL_ACCOUNT G/L Account.
        GL_ACCOUNT           TYPE /BI0/OIGL_ACCOUNT,
*      InfoObject: 0CHRT_ACCTS Chart of accounts.
        CHRT_ACCTS           TYPE /BI0/OICHRT_ACCTS,
*      InfoObject: 0MOVE_TYPE Movement Type.
        MOVE_TYPE           TYPE /BI0/OIMOVE_TYPE,
*      InfoObject: 0COMP_CODE Company code.
        COMP_CODE           TYPE /BI0/OICOMP_CODE,
*      InfoObject: 0ACDOCNO_IR Document Number of Invoice to which Trans
*action Relates.
        ACDOCNO_IR           TYPE /BI0/OIACDOCNO_IR,
*      InfoObject: 0DOC_DATE Document Date.
        DOC_DATE           TYPE /BI0/OIDOC_DATE,
*      InfoObject: 0AMOUNT Amount.
        AMOUNT           TYPE /BI0/OIAMOUNT,
*      InfoObject: 0CURRENCY Currency key.
        CURRENCY           TYPE /BI0/OICURRENCY,
*      InfoObject: 0DOC_NUM BW: Document Number.
        DOC_NUM           TYPE /BI0/OIDOC_NUM,
*      InfoObject: 0DOC_ITEM BW: Document Item Number.
        DOC_ITEM           TYPE /BI0/OIDOC_ITEM,
*      InfoObject: 0QUANT_B Quantity in base units of measure.
        QUANT_B           TYPE /BI0/OIQUANT_B,
*      InfoObject: 0NETPRICE Net price.
        NETPRICE           TYPE /BI0/OINETPRICE,
*      InfoObject: 0CALQUARTER Calendar year/quarter.
        CALQUARTER           TYPE /BI0/OICALQUARTER,
*      InfoObject: P_ZEILE Material Doc item.
        /BIC/P_ZEILE           TYPE /BIC/OIP_ZEILE,
*      InfoObject: 0VAL_TYPE Valuation type.
        VAL_TYPE           TYPE /BI0/OIVAL_TYPE,
*      InfoObject: PFROM_PC From Plant code.
        /BIC/PFROM_PC           TYPE /BIC/OIPFROM_PC,
*      InfoObject: PFROM_STL From Storage location.
        /BIC/PFROM_STL           TYPE /BIC/OIPFROM_STL,
*      InfoObject: PTO_PC To Plant Code.
        /BIC/PTO_PC           TYPE /BIC/OIPTO_PC,
*      InfoObject: PTO_STL To Storage Location.
        /BIC/PTO_STL           TYPE /BIC/OIPTO_STL,
*      InfoObject: P_ZEILEJ Material documnet item for join.
        /BIC/P_ZEILEJ           TYPE /BIC/OIP_ZEILEJ,
*      InfoObject: PCOGS_AMT COGS AMOUNT.
        /BIC/PCOGS_AMT           TYPE /BIC/OIPCOGS_AMT,
*      InfoObject: POTHR_QTY Others Quantity.
        /BIC/POTHR_QTY           TYPE /BIC/OIPOTHR_QTY,
*      InfoObject: PRECP_QTY Receipt Quantity.
        /BIC/PRECP_QTY           TYPE /BIC/OIPRECP_QTY,
*      InfoObject: PSCRP_QTY Scrap Quantity.
        /BIC/PSCRP_QTY           TYPE /BIC/OIPSCRP_QTY,
*      InfoObject: PSHIP_QTY Shipment Quantity.
        /BIC/PSHIP_QTY           TYPE /BIC/OIPSHIP_QTY,
*      InfoObject: 0ISSTRANSST Issue Quantity: Stock in Transit.
        ISSTRANSST           TYPE /BI0/OIISSTRANSST,
*      InfoObject: 0RECTRANSST Receipt Quantity: Stock in Transit.
        RECTRANSST           TYPE /BI0/OIRECTRANSST,
*      InfoObject: PAMT_INFL Amount Inflow.
        /BIC/PAMT_INFL           TYPE /BIC/OIPAMT_INFL,
*      InfoObject: PAMT_OUTF Amount Outflow.
        /BIC/PAMT_OUTF           TYPE /BIC/OIPAMT_OUTF,
*      InfoObject: POTHR_AMT Adjustment Amount.
        /BIC/POTHR_AMT           TYPE /BIC/OIPOTHR_AMT,
*      InfoObject: PRECP_AMT Receipt Amount.
        /BIC/PRECP_AMT           TYPE /BIC/OIPRECP_AMT,
*      InfoObject: PSCRP_AMT Scrap Amount.
        /BIC/PSCRP_AMT           TYPE /BIC/OIPSCRP_AMT,
*      InfoObject: PSHIP_AMT Ship Amount.
        /BIC/PSHIP_AMT           TYPE /BIC/OIPSHIP_AMT,
*      InfoObject: PSER_NUM Serial Number.
        /BIC/PSER_NUM           TYPE /BIC/OIPSER_NUM,
*      InfoObject: 0STORNO Reversal indicator.
        STORNO           TYPE /BI0/OISTORNO,
*      InfoObject: 0DB_MATCH Match recommended flag.
        DB_MATCH           TYPE /BI0/OIDB_MATCH,
*      InfoObject: 0COSTCENTER Cost Center.
        COSTCENTER           TYPE /BI0/OICOSTCENTER,
*      InfoObject: 0CO_AREA Controlling area.
        CO_AREA           TYPE /BI0/OICO_AREA,
*      InfoObject: 0BWAPPLNM Application component.
        BWAPPLNM           TYPE /BI0/OIBWAPPLNM,
*      InfoObject: 0MATMREA Reason for Goods Movement.
        MATMREA           TYPE /BI0/OIMATMREA,
*      InfoObject: 0ME_ORDER Sales Order Number.
        ME_ORDER           TYPE /BI0/OIME_ORDER,
*      InfoObject: 0ORD_TYP Order Type.
        ORD_TYP           TYPE /BI0/OIORD_TYP,
*      InfoObject: 0PROCESSKEY BW: Transaction Key.
        PROCESSKEY           TYPE /BI0/OIPROCESSKEY,
*      InfoObject: 0SORD_ITEM Item Number in Sales Order.
        SORD_ITEM           TYPE /BI0/OISORD_ITEM,
*      InfoObject: 0STOCKCAT Stock Categories.
        STOCKCAT           TYPE /BI0/OISTOCKCAT,
*      InfoObject: 0STOCKRELEV BW: Relevance to Stock.
        STOCKRELEV           TYPE /BI0/OISTOCKRELEV,
*      InfoObject: 0USERNAME User Name.
        USERNAME           TYPE /BI0/OIUSERNAME,
*      InfoObject: 0VAL_CLASS Valuation class.
        VAL_CLASS           TYPE /BI0/OIVAL_CLASS,
*      InfoObject: PMAT_ITM Material Item No.
        /BIC/PMAT_ITM           TYPE /BIC/OIPMAT_ITM,
*      InfoObject: PSTKINC Stock indicator.
        /BIC/PSTKINC           TYPE /BIC/OIPSTKINC,
*      InfoObject: PTYPEPOST Type of posting.
        /BIC/PTYPEPOST           TYPE /BIC/OIPTYPEPOST,
*      InfoObject: 0BATCH Batch number.
        BATCH           TYPE /BI0/OIBATCH,
*      InfoObject: 0RT_PROMO Promotion.
        RT_PROMO           TYPE /BI0/OIRT_PROMO,
*      InfoObject: 0BUS_AREA Business area.
        BUS_AREA           TYPE /BI0/OIBUS_AREA,
*      InfoObject: 0WHSE_NUM Warehouse number / warehouse complex.
        WHSE_NUM           TYPE /BI0/OIWHSE_NUM,
*      InfoObject: 0STRGE_BIN Storage bin.
        STRGE_BIN           TYPE /BI0/OISTRGE_BIN,
*      InfoObject: 0STRGE_TYPE Storage type.
        STRGE_TYPE           TYPE /BI0/OISTRGE_TYPE,
*      InfoObject: 0PROFIT_CTR Profit Center.
        PROFIT_CTR           TYPE /BI0/OIPROFIT_CTR,
*      InfoObject: 0DCINDIC Debit/credit indicator.
        DCINDIC           TYPE /BI0/OIDCINDIC,
*      InfoObject: 0COSTOBJ Cost Object.
        COSTOBJ           TYPE /BI0/OICOSTOBJ,
*      InfoObject: 0COORDER Order Number.
        COORDER           TYPE /BI0/OICOORDER,
*      InfoObject: 0MOVE_PLANT Receiving Plant/Issuing Plant.
        MOVE_PLANT           TYPE /BI0/OIMOVE_PLANT,
*      InfoObject: 0INDSPECSTK Indicator: Valuation of Special Stock.
        INDSPECSTK           TYPE /BI0/OIINDSPECSTK,
*      InfoObject: 0SOLD_TO Sold-to party.
        SOLD_TO           TYPE /BI0/OISOLD_TO,
*      InfoObject: PO_TYPE PO Document Type.
        /BIC/PO_TYPE           TYPE /BIC/OIPO_TYPE,
*      InfoObject: GCU_PON Customer PO Number.
        /BIC/GCU_PON           TYPE /BIC/OIGCU_PON,
*      InfoObject: PPUR_DATE Purchase Doc Date.
        /BIC/PPUR_DATE           TYPE /BIC/OIPPUR_DATE,
*      InfoObject: P_DELLINE Deleivery Item.
        /BIC/P_DELLINE           TYPE /BIC/OIP_DELLINE,
*      InfoObject: P_DELVERY Delivery No.
        /BIC/P_DELVERY           TYPE /BIC/OIP_DELVERY,
*      InfoObject: 0CPQUABU BW: Amount in base unit of measure.
        CPQUABU           TYPE /BI0/OICPQUABU,
*      InfoObject: 0CPPVLC BW: Purchase Value in Local Currency.
        CPPVLC           TYPE /BI0/OICPPVLC,
*      InfoObject: 0CPSTLC BW: Sales Value in Local Currency.
        CPSTLC           TYPE /BI0/OICPSTLC,
*      InfoObject: 0CPSVLC BW: Sales Value in Local Currency.
        CPSVLC           TYPE /BI0/OICPSVLC,
*      InfoObject: 0VALUE_LC Amount in local currency.
        VALUE_LC           TYPE /BI0/OIVALUE_LC,
*      InfoObject: 0BWCOUNTER Additional Key Field Revaluation Document
*Record.
        BWCOUNTER           TYPE /BI0/OIBWCOUNTER,
*      InfoObject: PREV_FLAG Revaluation data flag.
        /BIC/PREV_FLAG           TYPE /BIC/OIPREV_FLAG,
*      InfoObject: PMAT_TIME Material time dependent.
        /BIC/PMAT_TIME           TYPE /BIC/OIPMAT_TIME,
*      InfoObject: 0PSTNG_DATE Posting date in the document.
        PSTNG_DATE           TYPE /BI0/OIPSTNG_DATE,
*      Field: RECORD.
        RECORD           TYPE RSARECORD,
      END   OF _ty_s_TG_1.
    TYPES:
      _ty_t_TG_1        TYPE STANDARD TABLE OF _ty_s_TG_1
                        WITH NON-UNIQUE DEFAULT KEY.

*$*$ begin of global - insert your declaration only below this line  *-*
    ... "insert your code here
*$*$ end of global - insert your declaration only before this line   *-*
    METHODS
      new_record__end_routine
        IMPORTING
          source_segid             type rstran_segid
          source_record            type sytabix
        EXPORTING
          record_new               type sytabix.

    METHODS
      end_routine
        IMPORTING
          request                  type rsrequest
          datapackid               type rsdatapid
          segid                    type rsbk_segid
        EXPORTING
          monitor                  type rstr_ty_t_monitors
        CHANGING
          RESULT_PACKAGE              type _ty_t_TG_1
        RAISING
          cx_rsrout_abort
          cx_rsbk_errorcount.
    METHODS
      inverse_end_routine
        IMPORTING
          i_th_fields_outbound         TYPE rstran_t_field_inv
          I_R_SELSET_OUTBOUND          TYPE REF TO CL_RSMDS_SET
          i_is_main_selection          TYPE rs_bool
          i_r_selset_outbound_complete TYPE REF TO cl_rsmds_set
          i_r_universe_inbound         TYPE REF TO CL_RSMDS_UNIVERSE
        CHANGING
          c_th_fields_inbound          TYPE rstran_t_field_inv
          c_r_selset_inbound           TYPE REF TO CL_RSMDS_SET
          c_exact                      TYPE rs_bool.
ENDCLASS.                    "routine DEFINITION

*$*$ begin of 2nd part global - insert your code only below this line  *
... "insert your code here
*$*$ end of 2nd part global - insert your code only before this line   *

*---------------------------------------------------------------------*
*       CLASS routine IMPLEMENTATION
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
CLASS lcl_transform IMPLEMENTATION.

*----------------------------------------------------------------------*
*       Method end_routine
*----------------------------------------------------------------------*
*       Calculation of result package via end routine.
*       Note: Update of target fields depends on rule assignment in
*       transformation editor. Only fields that have a rule assigned,
*       are updated to the data target.
*----------------------------------------------------------------------*
*   <-> result package
*----------------------------------------------------------------------*
  METHOD end_routine.
*=== Segments ===

    FIELD-SYMBOLS:
      <RESULT_FIELDS>    TYPE _ty_s_TG_1.

    DATA:
      MONITOR_REC     TYPE rstmonitor.

*$*$ begin of routine - insert your code only below this line        *-*
    ... "insert your code here
    DATA:it_result_package TYPE STANDARD TABLE OF _ty_s_tg_1,
         wa_source         TYPE                   _ty_s_tg_1.

    REFRESH it_result_package[].

*    BREAK-POINT.

    LOOP AT RESULT_PACKAGE INTO wa_source.

      IF wa_source-move_type = '101' AND wa_source-bwcounter =
         '0000000002'
        AND wa_source-stocktype = 'A'  AND wa_source-dcindic =
        'S'.
        wa_source-/bic/p_zeilej = wa_source-/bic/p_zeile - 1.
      ELSE.
        wa_source-/bic/p_zeilej = wa_source-/bic/p_zeile.
      ENDIF.

      APPEND wa_source TO it_result_package.

      CLEAR wa_source.
    ENDLOOP.
    REFRESH RESULT_PACKAGE[].
    RESULT_PACKAGE[] = it_result_package[].

    REFRESH it_result_package[].

*** START NEW CODE FROM HERE.......
****data declarations
    TYPES: BEGIN OF ty_serial_nu,
             /bic/pser_num TYPE /bic/oipser_num,
             material      TYPE /bi0/oimaterial,
             mat_doc       TYPE /bi0/oimat_doc,
             mat_item      TYPE /bi0/oimat_item,
           END OF ty_serial_nu.

    DATA : lt_serial_nu       TYPE TABLE OF          ty_serial_nu,
           lw_serial_nu       TYPE                   ty_serial_nu,
           lv_serial_nu       TYPE                   string,
           it_result_package1 TYPE STANDARD TABLE OF _ty_s_tg_1,
           wa_source1         TYPE                   _ty_s_tg_1,
           lv_tabix           TYPE                   sy-tabix,
           lv_amount          TYPE                   /BI0/OICPPVLC,
           lv_quant           TYPE                   /BI0/OICPQUABU,
*           lv_quant1          TYPE                   string,
           count              TYPE                   string,
           it_count           TYPE STANDARD TABLE OF _ty_s_tg_1,
           lt_serial_nu_temp  TYPE STANDARD TABLE OF _ty_s_tg_1,
           lv_lines          TYPE                    int4.

    CONSTANTS : lv_match  TYPE /bi0/oidb_match VALUE 'Y',
                lv_match1 TYPE /bi0/oidb_match VALUE 'N',
                lv_symbol type string value '-'.


*    IF sy-uname = 'MANALIP'.
*      BREAK-POINT.
*    ENDIF.

*check the result_package initial or not
    IF RESULT_PACKAGE IS NOT INITIAL.
*fetch the serial number data from table /bic/aaim_o0500
      SELECT /bic/pser_num
             material
             mat_doc
             mat_item FROM /bic/aaim_o0500 INTO TABLE lt_serial_nu
             FOR ALL ENTRIES IN RESULT_PACKAGE
             WHERE
             material  = RESULT_PACKAGE-material AND
             mat_doc   = RESULT_PACKAGE-acdocno_ir AND
             mat_item  = RESULT_PACKAGE-/bic/p_zeilej.

      IF sy-subrc = 0.
*        DESCRIBE TABLE lt_serial_nu LINES lv_serial_nu.
      ENDIF.

      SORT RESULT_PACKAGE BY material  acdocno_ir /bic/p_zeilej.
      SORT lt_serial_nu BY  material mat_doc mat_item.

* loop the result package data one by one
      LOOP AT RESULT_PACKAGE INTO wa_source.

        READ TABLE lt_serial_nu INTO lw_serial_nu
                    WITH KEY material =  wa_source-material
                             mat_doc = wa_source-acdocno_ir
                             mat_item = wa_source-/bic/p_zeilej
                             BINARY SEARCH.

        IF sy-subrc = 0.
          clear :  lv_quant.
          lv_quant = wa_source-cpquabu.
*          lv_quant1 = lv_quant.

          CLEAR : lt_serial_nu_temp,lv_lines.
*decribe the lines for line item material each
          LOOP AT lt_serial_nu INTO lw_serial_nu WHERE
                     material EQ wa_source-material AND
                     mat_doc  EQ wa_source-acdocno_ir AND
                     mat_item EQ wa_source-/bic/p_zeilej.
            APPEND lw_serial_nu TO lt_serial_nu_temp.
            CLEAR : lw_serial_nu.
          ENDLOOP.

          DESCRIBE TABLE lt_serial_nu_temp LINES lv_lines.
          lv_tabix = sy-tabix.
*use parallel curssor technichs read the details serial numbers from
*result package
          LOOP AT lt_serial_nu INTO lw_serial_nu FROM lv_tabix.

            IF wa_source-material EQ lw_serial_nu-material AND
               wa_source-acdocno_ir EQ lw_serial_nu-mat_doc AND
               wa_source-/bic/p_zeilej EQ lw_serial_nu-mat_item.
              IF sy-subrc = 0.

                MOVE-CORRESPONDING wa_source TO wa_source1.

                wa_source1-/bic/pser_num = lw_serial_nu-/bic/pser_num.
*                wa_source1-db_match = lv_match.
*case 1 check the line item serial numner with quantity value and pass
*the db_match value
                IF lv_quant le 0.
                  lv_lines = ( -1 * lv_lines ).
*                  CONCATENATE lv_symbol lv_lines into lv_lines.
*                  CONDENSE lv_lines.
                ENDIF.

                IF lv_quant = lv_lines.
                  wa_source1-db_match = lv_match.
                ELSE.
**case 4 check the line item serial numner with quantity value and pass
**the db_match value
                  wa_source1-db_match = lv_match1.
                ENDIF.
*case 3 check the material quantty -ve value
                IF wa_source-cppvlc > 0.
                  wa_source1-cppvlc  = ( wa_source-cppvlc /
                  wa_source-cpquabu ).
                  wa_source1-cpquabu       = '1'.
                ELSEIF wa_source-cppvlc = 0 AND wa_source-cpquabu > 0.
                  wa_source1-cppvlc = ( wa_source-cppvlc /
                  wa_source-cpquabu ).
                  wa_source1-cpquabu = '1'.
                ELSEIF wa_source-cppvlc = 0 AND wa_source-cpquabu < 0.
                   wa_source1-cppvlc = ( wa_source-cppvlc /
                   wa_source-cpquabu ).
                   wa_source1-cpquabu = '-1'.
                ELSE.
                  wa_source1-cppvlc        = ( wa_source-cppvlc /
                  wa_source-cpquabu ).
                  lv_amount    =   wa_source1-cppvlc.
                     lv_amount = ( -1 * lv_amount ).
*                  CONCATENATE '-' lv_amount INTO lv_amount SEPARATED BY
*                  space.
*                  CONDENSE lv_amount.
                  wa_source1-cppvlc = lv_amount.
                  wa_source1-cpquabu       = '-1'.

                ENDIF.

                IF wa_source-value_lc > 0.
                  wa_source1-value_lc        = ( wa_source-value_lc /
                  wa_source-quant_b ).
                  wa_source1-quant_b       = '1'.
                ELSEIF wa_source-value_lc = 0 AND wa_source-quant_b > 0.
                  wa_source1-value_lc        = ( wa_source-value_lc /
                  wa_source-quant_b ).
                  wa_source1-quant_b       = '1'.
                ELSEIF  wa_source-value_lc = 0 AND wa_source-quant_b < 0
                .
                  wa_source1-value_lc        = ( wa_source-value_lc /
                  wa_source-quant_b ).
                  wa_source1-quant_b       = '-1'.
                ELSE.
                  wa_source1-value_lc       = ( wa_source-value_lc /
                  wa_source-quant_b ).
                  wa_source1-quant_b       = '-1'.
                ENDIF.
**** Ram start....
                IF ( wa_source1-processkey EQ '000'
                         OR wa_source1-processkey EQ '001'
                         OR wa_source1-processkey EQ '004'
                         OR wa_source1-processkey EQ '005'
                         OR wa_source1-processkey EQ '006'
                         OR wa_source1-processkey EQ '010' )
                         AND wa_source1-bwapplnm EQ 'MM'
                         AND wa_source1-stockrelev EQ '1'
                         AND wa_source1-cpquabu <> 0.
                  wa_source1-rectotstck = wa_source1-cpquabu.
                ENDIF.

                IF ( wa_source1-processkey EQ '100'
                  OR wa_source1-processkey EQ '101'
                  OR wa_source1-processkey EQ '104'
                  OR wa_source1-processkey EQ '105'
                  OR wa_source1-processkey EQ '106'
                  OR wa_source1-processkey EQ '110' )
                  AND wa_source1-bwapplnm EQ 'MM'
                  AND wa_source1-stockrelev EQ '1'
                  AND wa_source1-cpquabu <> 0.
                  wa_source1-isstotstck = wa_source1-cpquabu.
                ENDIF.

               IF wa_source1-/BIC/PO_TYPE <> 'ZRBC'.
                   IF wa_source1-/BIC/PO_TYPE <> 'ZRB'.
                       IF wa_source1-gl_account = '0000541000'.
                          if wa_source1-DCINDIC = 'S' and
                          wa_source1-cppvlc > 0
                   .
                            wa_source1-/bic/pcogs_amt =
                            wa_source1-cppvlc * -1
                      .
                          else.
                            wa_source1-/bic/pcogs_amt =
                            wa_source1-cppvlc.
                         endif.
                      ENDIF.
                   ENDIF.
              ENDIF.

                IF  wa_source1-processkey EQ '005'
                   AND wa_source1-bwapplnm EQ 'MM'
                   AND wa_source1-stockrelev EQ '1'
                   AND wa_source1-cpquabu <> 0.
                  wa_source1-/bic/pothr_qty = wa_source1-cpquabu.
                ENDIF.

                IF wa_source1-processkey EQ '105'
                  AND wa_source1-bwapplnm EQ 'MM'
                  AND wa_source1-stockrelev EQ '1'
                  AND wa_source1-cpquabu <> 0.
                  wa_source1-/bic/pothr_qty = wa_source1-cpquabu * -1.
                ENDIF.

                IF ( wa_source1-processkey EQ '000'
                 OR wa_source1-processkey EQ '001'
                 OR wa_source1-processkey EQ '004'
                 OR wa_source1-processkey EQ '006'
                 OR wa_source1-processkey EQ '010' )
                 AND wa_source1-bwapplnm EQ 'MM'
                 AND wa_source1-stockrelev EQ '1'
                 AND wa_source1-cpquabu <> 0.
                  wa_source1-/bic/precp_qty = wa_source1-cpquabu.
                ENDIF.

                IF  wa_source1-processkey EQ '106'
                    AND wa_source1-bwapplnm EQ 'MM'
                    AND wa_source1-stockrelev EQ '1'
                    AND wa_source1-cpquabu <> 0.
                  wa_source1-/bic/pscrp_qty = wa_source1-cpquabu.
                ENDIF.

                IF ( wa_source1-processkey EQ '100'
                 OR wa_source1-processkey EQ '101'
                 OR wa_source1-processkey EQ '104'
                 OR wa_source1-processkey EQ '110' )
                 AND wa_source1-bwapplnm EQ 'MM'
                 AND wa_source1-stockrelev EQ '1'
                 AND wa_source1-cpquabu <> 0.
                  wa_source1-/bic/pship_qty = wa_source1-cpquabu.
                ENDIF.

                IF ( wa_source1-processkey EQ '100'
                  OR wa_source1-processkey EQ '101'
                  OR wa_source1-processkey EQ '104'
                  OR wa_source1-processkey EQ '105'
                  OR wa_source1-processkey EQ '106'
                  OR wa_source1-processkey EQ '110' )
                  AND wa_source1-bwapplnm EQ 'MM'
                  AND wa_source1-stocktype CA 'FH789W'
                  AND wa_source1-stockrelev EQ '1'
                  AND wa_source1-cpquabu <> 0.
                  wa_source1-isstransst = wa_source1-cpquabu.
                ENDIF.

                IF ( wa_source1-processkey EQ '000'
                  OR wa_source1-processkey EQ '001'
                  OR wa_source1-processkey EQ '004'
                  OR wa_source1-processkey EQ '005'
                  OR wa_source1-processkey EQ '006'
                  OR wa_source1-processkey EQ '010' )
                  AND wa_source1-bwapplnm EQ 'MM'
                  AND  wa_source1-stocktype CA 'FH789W'
                  AND wa_source1-stockrelev EQ '1'
                  AND wa_source1-cpquabu <> 0.
                  wa_source1-rectransst = wa_source1-cpquabu.
                ENDIF.

                IF ( wa_source1-processkey EQ '000'
                    OR wa_source1-processkey EQ '001'
                    OR wa_source1-processkey EQ '004'
                    OR wa_source1-processkey EQ '005'
                    OR wa_source1-processkey EQ '006'
                    OR wa_source1-processkey EQ '010' )
                    AND wa_source1-bwapplnm EQ 'MM'
                    AND wa_source1-stockrelev EQ '1'
                    AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pamt_infl = wa_source1-cppvlc.
                ENDIF.

                IF ( wa_source1-processkey EQ '100'
                    OR wa_source1-processkey EQ '101'
                    OR wa_source1-processkey EQ '104'
                    OR wa_source1-processkey EQ '105'
                    OR wa_source1-processkey EQ '106'
                    OR wa_source1-processkey EQ '110' )
                    AND wa_source1-bwapplnm EQ 'MM'
                    AND wa_source1-stockrelev EQ '1'
                    AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pamt_outf = wa_source1-cppvlc.
                ENDIF.

                IF  wa_source1-processkey EQ '005'
                   AND wa_source1-bwapplnm EQ 'MM'
                   AND wa_source1-stockrelev EQ '1'
                  AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pothr_amt = wa_source1-cppvlc.
                ENDIF.

                IF  wa_source1-processkey EQ '105'
                     AND wa_source1-bwapplnm EQ 'MM'
                    AND wa_source1-stockrelev EQ '1'
                    AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pothr_amt = wa_source1-cppvlc * -1.
                ENDIF.

                IF ( wa_source1-processkey EQ '000'
                 OR wa_source1-processkey EQ '001'
                 OR wa_source1-processkey EQ '004'
                 OR wa_source1-processkey EQ '006'
                 OR wa_source1-processkey EQ '010' )
                 AND wa_source1-bwapplnm EQ 'MM'
                 AND wa_source1-stockrelev EQ '1'
                 AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/precp_amt = wa_source1-cppvlc.
                ENDIF.

                IF  wa_source1-processkey EQ '106'
                    AND wa_source1-bwapplnm EQ 'MM'
                    AND wa_source1-stockrelev EQ '1'
                    AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pscrp_amt = wa_source1-cppvlc.
                ENDIF.

                IF ( wa_source1-processkey EQ '100'
                  OR wa_source1-processkey EQ '101'
                  OR wa_source1-processkey EQ '104'
                  OR wa_source1-processkey EQ '110' )
                 AND wa_source1-bwapplnm EQ 'MM'
                 AND wa_source1-stockrelev EQ '1'
                 AND wa_source1-cppvlc <> 0.
                  wa_source1-/bic/pship_amt = wa_source1-cppvlc.
                ENDIF.

*Ram end......
                APPEND wa_source1 TO it_result_package.
                CLEAR : wa_source1,lv_amount.

              ELSE.
*                EXIT.
              ENDIF.
            ENDIF.
          ENDLOOP.

        ELSE.
*case 4  material don't have serial numbers pass the value
          MOVE-CORRESPONDING wa_source TO wa_source1.
          wa_source1-db_match = lv_match1.
          wa_source1-/bic/pser_num = ' '.


****         loop at lt_serial_nu into lw_serial_nu.
****           read TABLE  RESULT_PACKAGE INTO wa_source
****           with key material = lw_serial_nu-material..
****           count = count + 1.
****           at END OF material.
****             if count = wa_source-cpquabu.
****
****               else.
****                 endif.
****
****             clear count.
****             ENDAT.


* ***          endloop.
*loop at result_package into wa_sourse.
*
*  endloop.

*** Ram start....

          IF ( wa_source1-processkey EQ '000'
             OR wa_source1-processkey EQ '001'
             OR wa_source1-processkey EQ '004'
             OR wa_source1-processkey EQ '005'
             OR wa_source1-processkey EQ '006'
             OR wa_source1-processkey EQ '010' )
             AND wa_source1-bwapplnm EQ 'MM'
             AND wa_source1-stockrelev EQ '1'
             AND wa_source1-cpquabu <> 0.
            wa_source1-rectotstck = wa_source1-cpquabu.
          ENDIF.

          IF ( wa_source1-processkey EQ '100'
            OR wa_source1-processkey EQ '101'
            OR wa_source1-processkey EQ '104'
            OR wa_source1-processkey EQ '105'
            OR wa_source1-processkey EQ '106'
            OR wa_source1-processkey EQ '110' )
            AND wa_source1-bwapplnm EQ 'MM'
            AND wa_source1-stockrelev EQ '1'
            AND wa_source1-cpquabu <> 0.
            wa_source1-isstotstck = wa_source1-cpquabu.
          ENDIF.

          IF wa_source1-/BIC/PO_TYPE <> 'ZRBC'.
            IF wa_source1-/BIC/PO_TYPE <> 'ZRB'.
              IF wa_source1-gl_account = '0000541000'.
                 wa_source1-/bic/pcogs_amt = wa_source1-cppvlc.
              ENDIF.
            ENDIF.
          ENDIF.

          IF  wa_source1-processkey EQ '005'
             AND wa_source1-bwapplnm EQ 'MM'
             AND wa_source1-stockrelev EQ '1'
             AND wa_source1-cpquabu <> 0.
            wa_source1-/bic/pothr_qty = wa_source1-cpquabu.
          ENDIF.

          IF wa_source1-processkey EQ '105'
            AND wa_source1-bwapplnm EQ 'MM'
            AND wa_source1-stockrelev EQ '1'
            AND wa_source1-cpquabu <> 0.
            wa_source1-/bic/pothr_qty = wa_source1-cpquabu * -1.
          ENDIF.

          IF ( wa_source1-processkey EQ '000'
           OR wa_source1-processkey EQ '001'
           OR wa_source1-processkey EQ '004'
           OR wa_source1-processkey EQ '006'
           OR wa_source1-processkey EQ '010' )
           AND wa_source1-bwapplnm EQ 'MM'
           AND wa_source1-stockrelev EQ '1'
           AND wa_source1-cpquabu <> 0.
            wa_source1-/bic/precp_qty = wa_source1-cpquabu.
          ENDIF.

          IF  wa_source1-processkey EQ '106'
              AND wa_source1-bwapplnm EQ 'MM'
              AND wa_source1-stockrelev EQ '1'
              AND wa_source1-cpquabu <> 0.
            wa_source1-/bic/pscrp_qty = wa_source1-cpquabu.
          ENDIF.

          IF ( wa_source1-processkey EQ '100'
           OR wa_source1-processkey EQ '101'
           OR wa_source1-processkey EQ '104'
           OR wa_source1-processkey EQ '110' )
           AND wa_source1-bwapplnm EQ 'MM'
           AND wa_source1-stockrelev EQ '1'
           AND wa_source1-cpquabu <> 0.
            wa_source1-/bic/pship_qty = wa_source1-cpquabu.
          ENDIF.

          IF ( wa_source1-processkey EQ '100'
            OR wa_source1-processkey EQ '101'
            OR wa_source1-processkey EQ '104'
            OR wa_source1-processkey EQ '105'
            OR wa_source1-processkey EQ '106'
            OR wa_source1-processkey EQ '110' )
            AND wa_source1-bwapplnm EQ 'MM'
            AND wa_source1-stocktype CA 'FH789W'
            AND wa_source1-stockrelev EQ '1'
            AND wa_source1-cpquabu <> 0.
            wa_source1-isstransst = wa_source1-cpquabu.
          ENDIF.

          IF ( wa_source1-processkey EQ '000'
            OR wa_source1-processkey EQ '001'
            OR wa_source1-processkey EQ '004'
            OR wa_source1-processkey EQ '005'
            OR wa_source1-processkey EQ '006'
            OR wa_source1-processkey EQ '010' )
            AND wa_source1-bwapplnm EQ 'MM'
            AND  wa_source1-stocktype CA 'FH789W'
            AND wa_source1-stockrelev EQ '1'
            AND wa_source1-cpquabu <> 0.
            wa_source1-rectransst = wa_source1-cpquabu.
          ENDIF.

          IF ( wa_source1-processkey EQ '000'
              OR wa_source1-processkey EQ '001'
              OR wa_source1-processkey EQ '004'
              OR wa_source1-processkey EQ '005'
              OR wa_source1-processkey EQ '006'
              OR wa_source1-processkey EQ '010' )
              AND wa_source1-bwapplnm EQ 'MM'
              AND wa_source1-stockrelev EQ '1'
              AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pamt_infl = wa_source1-cppvlc.
          ENDIF.

          IF ( wa_source1-processkey EQ '100'
              OR wa_source1-processkey EQ '101'
              OR wa_source1-processkey EQ '104'
              OR wa_source1-processkey EQ '105'
              OR wa_source1-processkey EQ '106'
              OR wa_source1-processkey EQ '110' )
              AND wa_source1-bwapplnm EQ 'MM'
              AND wa_source1-stockrelev EQ '1'
              AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pamt_outf = wa_source1-cppvlc.
          ENDIF.

          IF  wa_source1-processkey EQ '005'
             AND wa_source1-bwapplnm EQ 'MM'
             AND wa_source1-stockrelev EQ '1'
            AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pothr_amt = wa_source1-cppvlc.
          ENDIF.

          IF  wa_source1-processkey EQ '105'
               AND wa_source1-bwapplnm EQ 'MM'
              AND wa_source1-stockrelev EQ '1'
              AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pothr_amt = wa_source1-cppvlc * -1.
          ENDIF.

          IF ( wa_source1-processkey EQ '000'
           OR wa_source1-processkey EQ '001'
           OR wa_source1-processkey EQ '004'
           OR wa_source1-processkey EQ '006'
           OR wa_source1-processkey EQ '010' )
           AND wa_source1-bwapplnm EQ 'MM'
           AND wa_source1-stockrelev EQ '1'
           AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/precp_amt = wa_source1-cppvlc.
          ENDIF.

          IF  wa_source1-processkey EQ '106'
              AND wa_source1-bwapplnm EQ 'MM'
              AND wa_source1-stockrelev EQ '1'
              AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pscrp_amt = wa_source1-cppvlc.
          ENDIF.

          IF ( wa_source1-processkey EQ '100'
            OR wa_source1-processkey EQ '101'
            OR wa_source1-processkey EQ '104'
            OR wa_source1-processkey EQ '110' )
           AND wa_source1-bwapplnm EQ 'MM'
           AND wa_source1-stockrelev EQ '1'
           AND wa_source1-cppvlc <> 0.
            wa_source1-/bic/pship_amt = wa_source1-cppvlc.
          ENDIF.

*Ram end......

          APPEND wa_source1 TO it_result_package.
          CLEAR : wa_source,wa_source1.
        ENDIF.
      ENDLOOP.
    ENDIF.

    REFRESH RESULT_PACKAGE[].
    RESULT_PACKAGE[] = it_result_package[].
    REFRESH it_result_package[].

*      ENDIF.
*  ENDIF.





*--  fill table "MONITOR" with values of structure "MONITOR_REC"
*-   to make monitor entries
    ... "to cancel the update process
*    raise exception type CX_RSROUT_ABORT.



*$*$ end of routine - insert your code only before this line         *-*
  ENDMETHOD.                    "end_routine
*----------------------------------------------------------------------*
*       Inverse method inverse_end_routine
*----------------------------------------------------------------------*
*       This subroutine needs to be implemented only for direct access
*       (for better performance) and for the Report/Report Interface
*       (drill through).
*       The inverse routine should transform a projection and
*       a selection for the target to a projection and a selection
*       for the source, respectively.
*       If the implementation remains empty all fields are filled and
*       all values are selected.
*----------------------------------------------------------------------*
*       Customer comment:
*----------------------------------------------------------------------*
  METHOD inverse_end_routine.

*   IMPORTING
*     i_r_selset_outbound          TYPE REF TO cl_rsmds_set
*     i_th_fields_outbound         TYPE HASHED TABLE
*     i_r_selset_outbound_complete TYPE REF TO cl_rsmds_set
*     i_r_universe_inbound         TYPE REF TO cl_rsmds_universe
*   CHANGING
*     c_r_selset_inbound           TYPE REF TO cl_rsmds_set
*     c_th_fields_inbound          TYPE HASHED TABLE
*     c_exact                      TYPE rs_bool

*$*$ begin of inverse routine - insert your code only below this line*-*
    ... "insert your code here
*$*$ end of inverse routine - insert your code only before this line *-*

  ENDMETHOD.                    "inverse_end_routine

  METHOD new_record__end_routine.

***** IMPLEMENTATION  is only visible in generated program *****

  ENDMETHOD.
ENDCLASS.                    "routine IMPLEMENTATION

 

 

In Step 4, we have completed modeling to split the inventory transactions into serial number while posting data from 2LIS_03_BF to cube.

 

 

Step 5.

Create a Bex query to get the ending value depending on the “to date” of selected date range on the report execution.

 

Create a formula variable on the price object of time dependent material master data.

 

 

 

 

 

 

 

 

 

to get the material price based on the selected date, use key data as shown below to pass the “to date”.

 

 

 

The program for variable &ZVAR_END_DATE& is below. Based on the selected date range, pass the end date to the variable to get the price at ending date.

 

 

*&---------------------------------------------------------------------*
*& Report  ZBWVAR_ZDCRMA_CCINO_01
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZBWVAR_ZVAR_END_DATE.

DATA: i_date_from type sy-datum,
      i_date_to   type sy-datum,
      P_I_CURDT TYPE sy-datum VALUE IS INITIAL,
      VAR_RANGE     TYPE RRRANGEEXIT,
      l_s_range     type RSR_S_RANGESID,
      l_s_range_c   TYPE rrs0_s_range_c,
      var_range_c   TYPE rrs0_s_var_range_c,
      date_typ      TYPE C LENGTH 5,
      date_range_typ    TYPE C LENGTH 5.

FORM COMPUTE_ZVAR_END_DATE TABLES   P_I_T_VAR_RANGE    STRUCTURE VAR_RANGE
                                    P_I_T_VAR_RANGE_C  STRUCTURE var_range_c
                                    P_E_T_RANGE        STRUCTURE L_S_RANGE
                                    P_E_T_RANGE_C      STRUCTURE l_s_range_c
                             USING  P_I_VNAM
                                    P_I_VARTYP
                                    P_I_IOBJNM
                                    P_I_S_COB_PRO
                                    P_I_S_RKB1D
                                    P_I_PERIV
                                    P_I_STEP
                           CHANGING P_E_MEEHT
                                    P_E_MEFAC
                                    P_E_WAERS
                                    P_E_WHFAC.

  IF p_i_step = 2.

*    BREAK-POINT.

    LOOP AT P_I_T_VAR_RANGE INTO VAR_RANGE "Read Date object Value
    WHERE VNAM = '0REDAY1'. "Here write the technical Name of Date Selection Object variable
    ENDLOOP.

      CLEAR L_S_RANGE.
      if var_range-high = ' '.
         var_range-high = sy-datum.
      endif.

      l_s_range-low = var_range-high.
      L_S_RANGE-SIGN = 'I'.
      L_S_RANGE-OPT  = 'EQ'.
      APPEND L_S_RANGE TO P_E_T_RANGE.
*     ENDIF.
   ENDIF.
ENDFORM.

 

Ending Value:

 

 

Step 6.

 

Similar as shown in Step 4, we can create a one more Bex query to get the beginning value depending on the selected date range “from date”.

Merge the two Bex queries in Webi to generate a report with beginning value, ending value as shown below.

 

 

 

 

 

Right side corner of the Webi report follows …

 

 

 

Reconcile with ECC:

Total ending value from BI report for 07/2016 is 92644421.65

 

 

G/L 14400 is the inventory GL,

 

 

 

 

 

 

 

 

 

 

 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alexander Zlobin
      Alexander Zlobin

      Thanks for sharing!

      Could u tell me, pllease   - is it possible to generate Calculation View on BEX with non-cumulative key figures ?

      Author's profile photo Ramakrishna Kotha
      Ramakrishna Kotha
      Blog Post Author

       

      Alexander,

      I used to built this model BW 7.4 on HANA ==> Bex ==> Webi, I have not tried with calculation view, so at this point I am not sure how the non cumulative key figures works with calculation view, if I know more on this, I will update you.