吐个槽,去年公司做了BPC项目,跟项目的同事跑路了,只能留我这个ABAP工具人来天天找BW的BUG,问题不少,但其实都是类似的问题,该项目的开发,所有数据的抽取都没有考虑过取消、删除、冲销等情况,导致抽取数据后,在HANA模型处理得出的数据各种问题,不知道是不是因为乙方开发顾问对数据没那么熟,感觉做过的每个项目顾问在开发时都不太会考虑这些情况。
以存货未实现损益的模型举个例子,目前公司的采用的是集中制造模式,制造公司A生成完产品,然后通过内部交易销售给各个销售公司,销售公司再卖给客户。如果销售公司产品没有卖出去,那制造公司卖给销售公司时产生的这部分利润,站在集团层面上实际上是虚增的,目前的HANA模型计算逻辑是销售公司采购成本 - 制造公司产品成本,采购成本取物料凭证,由于未考虑冲销情况,也不是按累计算,而是直接取了最近日期的凭证,导致数据计算错误
之前的数据源采用的是视图抽取模式,物料凭证中有没有直接的冲销标识,不太好改,于是重新建了数据源,通过函数的方式抽取,抽取结构中增加冲销标,然后在HANA模型处理时排除带冲销标识的数据
RSO2创建数据源,提取结构
创建function,复制RSAX_BIW_GET_DATA_SIMPLE做调整,主要调整点:筛选条件的设置、数据源名称、SQL取数逻辑,代码如下
FUNCTION zbw_mseg.
*"----------------------------------------------------------------------
*"*"Local interface:
*" IMPORTING
*" VALUE(I_REQUNR) TYPE SRSC_S_IF_SIMPLE-REQUNR
*" VALUE(I_DSOURCE) TYPE SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL
*" VALUE(I_MAXSIZE) TYPE SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL
*" VALUE(I_INITFLAG) TYPE SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL
*" VALUE(I_READ_ONLY) TYPE SRSC_S_IF_SIMPLE-READONLY OPTIONAL
*" VALUE(I_REMOTE_CALL) TYPE SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF
*" TABLES
*" I_T_SELECT TYPE SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL
*" I_T_FIELDS TYPE SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL
*" E_T_DATA STRUCTURE ZOXDEV0121 OPTIONAL
*" EXCEPTIONS
*" NO_MORE_DATA
*" ERROR_PASSED_TO_MESS_HANDLER
*"----------------------------------------------------------------------
* Example: DataSource for table SFLIGHT
TABLES: sflight.
* Auxiliary Selection criteria structure
DATA: l_s_select TYPE srsc_s_select.
* Maximum number of lines for DB table
STATICS: s_s_if TYPE srsc_s_if_simple,
* counter
s_counter_datapakid LIKE sy-tabix,
* cursor
s_cursor TYPE cursor.
* Select ranges
RANGES: l_r_mblnr FOR mseg-mblnr,
l_r_mjahr FOR mseg-mjahr,
l_r_bwart FOR mseg-bwart,
l_r_matnr FOR mseg-matnr,
l_r_werks FOR mseg-werks,
l_r_shkzg FOR mseg-shkzg,
l_r_sobkz FOR mseg-sobkz,
l_r_kdauf FOR mseg-kdauf,
l_r_ebeln FOR mseg-ebeln,
l_r_bukrs FOR mseg-bukrs,
l_r_mat_kdauf FOR mseg-mat_kdauf,
l_r_budat_mkpf FOR mseg-budat_mkpf.
* Initialization mode (first call by SAPI) or data transfer mode
* (following calls) ?
IF i_initflag = sbiwa_c_flag_on.
************************************************************************
* Initialization: check input parameters
* buffer input parameters
* prepare data selection
************************************************************************
* Check DataSource validity
CASE i_dsource.
WHEN 'ZBW_MSEG_FM'.
WHEN OTHERS.
IF 1 = 2. MESSAGE e009(r3). ENDIF.
* this is a typical log call. Please write every error message like this
log_write 'E' "message type
'R3' "message class
'009' "message number
i_dsource "message variable 1
' '. "message variable 2
RAISE error_passed_to_mess_handler.
ENDCASE.
APPEND LINES OF i_t_select TO s_s_if-t_select.
* Fill parameter buffer for data extraction calls
s_s_if-requnr = i_requnr.
s_s_if-dsource = i_dsource.
s_s_if-maxsize = i_maxsize.
* Fill field list table for an optimized select statement
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
APPEND LINES OF i_t_fields TO s_s_if-t_fields.
ELSE. "Initialization mode or data extraction ?
************************************************************************
* Data transfer: First Call OPEN CURSOR + FETCH
* Following Calls FETCH only
************************************************************************
* First data package -> OPEN CURSOR
IF s_counter_datapakid = 0.
* Fill range tables BW will only pass down simple selection criteria
* of the type SIGN = 'I' and OPTION = 'EQ' or OPTION = 'BT'.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MBLNR'.
MOVE-CORRESPONDING l_s_select TO l_r_mblnr.
APPEND l_r_mblnr.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MJAHR'.
MOVE-CORRESPONDING l_s_select TO l_r_mjahr.
APPEND l_r_mjahr.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'BWART'.
MOVE-CORRESPONDING l_s_select TO l_r_bwart.
APPEND l_r_bwart.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MATNR'.
MOVE-CORRESPONDING l_s_select TO l_r_matnr.
APPEND l_r_matnr.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'WERKS'.
MOVE-CORRESPONDING l_s_select TO l_r_werks.
APPEND l_r_werks.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'SHKZG'.
MOVE-CORRESPONDING l_s_select TO l_r_shkzg.
APPEND l_r_shkzg.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'SOBKZ'.
MOVE-CORRESPONDING l_s_select TO l_r_sobkz.
APPEND l_r_sobkz.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'KDAUF'.
MOVE-CORRESPONDING l_s_select TO l_r_kdauf.
APPEND l_r_kdauf.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'EBELN'.
MOVE-CORRESPONDING l_s_select TO l_r_ebeln.
APPEND l_r_ebeln.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'MAT_KDAUF'.
MOVE-CORRESPONDING l_s_select TO l_r_mat_kdauf.
APPEND l_r_mat_kdauf.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'BUKRS'.
MOVE-CORRESPONDING l_s_select TO l_r_bukrs.
APPEND l_r_bukrs.
ENDLOOP.
LOOP AT s_s_if-t_select INTO l_s_select WHERE fieldnm = 'BUDAT_MKPF'.
MOVE-CORRESPONDING l_s_select TO l_r_budat_mkpf.
APPEND l_r_budat_mkpf.
ENDLOOP.
* Determine number of database records to be read per FETCH statement
* from input parameter I_MAXSIZE. If there is a one to one relation
* between DataSource table lines and database entries, this is trivial.
* In other cases, it may be impossible and some estimated value has to
* be determined.
OPEN CURSOR WITH HOLD s_cursor FOR
SELECT
mandt
mblnr
mjahr
zeile
bwart
matnr
werks
sobkz
shkzg
kdauf
dmbtr
waers
menge
meins
ebeln
bukrs
mat_kdauf
mat_kdpos
budat_mkpf
kzbew
aufnr
kunnr
lifnr
smbln
FROM mseg
WHERE mblnr IN l_r_mblnr
AND mjahr IN l_r_mjahr
AND bwart IN l_r_bwart
AND matnr IN l_r_matnr
AND werks IN l_r_werks
AND shkzg IN l_r_shkzg
AND sobkz IN l_r_sobkz
AND kdauf IN l_r_kdauf
AND ebeln IN l_r_ebeln
AND bukrs IN l_r_bukrs
AND mat_kdauf IN l_r_mat_kdauf
AND budat_mkpf IN l_r_budat_mkpf.
ENDIF. "First data package ?
* Fetch records into interface table.
* named E_T_'Name of extract structure'.
FETCH NEXT CURSOR s_cursor
APPENDING CORRESPONDING FIELDS
OF TABLE e_t_data
PACKAGE SIZE s_s_if-maxsize.
IF sy-subrc <> 0.
CLOSE CURSOR s_cursor.
RAISE no_more_data.
ENDIF.
DATA:lt_mbmps TYPE TABLE OF m_mbmps WITH HEADER LINE.
FIELD-SYMBOLS:<fs_data> LIKE e_t_data.
IF e_t_data[] IS NOT INITIAL.
SELECT *
INTO CORRESPONDING FIELDS OF TABLE lt_mbmps
FROM m_mbmps
FOR ALL ENTRIES IN e_t_data
WHERE m_mbmps~sjahr = e_t_data-mjahr
AND m_mbmps~smbln = e_t_data-mblnr
AND m_mbmps~smblp = e_t_data-zeile.
SORT lt_mbmps BY smbln sjahr smblp.
ENDIF.
LOOP AT e_t_data ASSIGNING <fs_data>.
READ TABLE lt_mbmps WITH KEY smbln = <fs_data>-mblnr sjahr = <fs_data>-mjahr smblp = <fs_data>-zeile BINARY SEARCH.
IF sy-subrc = 0.
<fs_data>-zflag_mblnr = 'R'. "冲销
ENDIF.
IF <fs_data>-smbln IS NOT INITIAL.
<fs_data>-zflag_mblnr = 'R'. "冲销
ENDIF.
IF <fs_data>-zflag_mblnr IS INITIAL AND ( <fs_data>-bwart = '161' or <fs_data>-bwart = '122' ).
<fs_data>-zflag_mblnr = 'T'. "采购退货
ENDIF.
ENDLOOP.
s_counter_datapakid = s_counter_datapakid + 1.
ENDIF. "Initialization mode or data extraction ?
ENDFUNCTION.
SE38中执行程序RODPS_OS_EXPOSE激活数据源,BW中的处理参考上一章视图抽取数据源,不在赘述