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,
Thanks for sharing!
Could u tell me, pllease - is it possible to generate Calculation View on BEX with non-cumulative key figures ?
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.