Jun 30, 2014

FI-AP Data Extraction to BW using 0FI_AP_4, 0FI_AP_6 and 0FI_AP_7 DataSources. Parked Documents


1      Scope:

From the Functional Specs it was derived that Business required reporting on the AP Line Item Data, Transaction Figures and Payment history along with some Vendor Master Data.
The following standard DataSources were identified which could address the basic extraction need.
  • 0FI_AP_4: Vendors: Line Items with Delta Extrcation
  • 0FI_AP_6: Vendor Sales Figures via Delta Extraction
  • 0FI_AP_7: Vendor SGL Sales Figures using Delta Extraction
  • ZFI_AP_4: Vendors: Line Items (Parked Documents)

Details on the DataSources are available at the following URL Customer / Vendor Analysis

2      R/3 DataSource Customizations:

2.1    Applicable SAP Notes

Customizations were carried out based on the following SAP Notes.
  • Note 410797 - FI line item extraction transfer to new DataSources:
         If using earlier versions of FI-AP Line Item DataSources like 0FI_AP_3, this note has to be applied.
  • Note 410799 - Enhancement of line item extraction FI-GL, FI-CIS, FI-CIS
         Implemented for 0FI_AP_4 DataSource customization.
  • Note 551044 - Detaching the extractors for the InfoSources 0FI_xx_4.pdf
         Already available as part of BI 7.0
  • Note 991429 - Minute Based extraction enhancement for 0FI_xx_4 extractors
         Implem ented to enable Minute Based Extraction. Changes are already available as part of BI 7.0, only need to enable flag and modify table Index.
  • Note 1012874 - FAQ extractors 0FI_AP_* 0FI_AR_* 0FI_GL_* (except 10)
  • Note 411758 - Extracting noted item

2.2    0FI_AP_4 Customization

0FI_AP_4 extracts data from the table BSIK (Open Items) and BSAK (Cleared Items). As per Note 410799, the Structure CI_BSIK was created to include the additional fields. The structure gets automatically included in the main structure DTFIAP_3. Since the source of data for these additional fields were not from BSIK/BSAK, we had to write ABAP code to bring in the data by implementing the BTE 00005121. The function module ZBWFI_FILL_CI_00005021 was created to be invoked by the BTE for this purpose. The module holds the necessary ABAP Code for data extraction for the additional fields. An entry was made in the table TPS31 for the BTE 00005021. The function group ZBWFI4 [BW FI: Functions to fill FI_XX_4 CI flds] was created and used.
Limitations: The following FI Document Types are not extracted by 0FI_AP_4 DataSource – Parked Documents & Noted Items. Noted Items can be extracted by further implementing SAP Note 411758.


2.3    0FI_AP_6 Customization

0FI_AP_6 extracts data from the table LFC1 [Vendor master (transaction figures)]. No customization was requested.


2.4    0FI_AP_7 Customization

0FI_AP_7 extracts data from the table LFC3 [Vendor master (special G/L transaction figures)]. No customization was requested.


2.5    ZFI_AP_4 Creation

For Parked documents extraction from SAP, we have created the DataSource ZFI_AP_4 to extract the data from ECC. This is based on a Database view ZVBSEGK. To accommodate the additional fields an Append Structure ZAZOXDAE0290 was created for the Extract Structure ZOXDAE0290 from the interface of RSA6. The data for the additional fields are sourced from various tables by implementing the code in CMOD.


2.6    Settings in R/3

Following are the settings that are maintained in R/3 for the Extraction to work properly.
Table: TPS31
PROCSLANDAPPLKFUNCT
5010


FBW4_WRITE_CHANGE_QUEUE
5010

FI-CFDM_COLL_WRITE_CHANGE_QUEUE
5010

UKMUKM_EVENT_5010
5011


BWFIP_WRITE_AEDAT_POINTER
5012


BWFI2P_WRITE_AEDA2_POINTER
5013


BWFI3P_WRITE_AEDA3_POINTER
5021


ZBWFI_FILL_CI_00005021
5022


ZBWFI_FILL_CI_00005022

*** If you delete the last initialization selection in the source system for the InfoSources 0FI_*_4 in the BW system Administrator Workbench, the entry from table TPS31 is also removed. This stops recording of chan ged line items in table BWFI_AEDAT. Existing data in table BWFI_AEDAT is also deleted. Before a new data request can be made in the update mode initialization of the delta method, the entry must be added again to table TPS31.
*** Recording of the changed line items must be started a reasonable amount of time before the first data request in update mode initialization of the data method. For this purpose, add the entry in table TPS31 using transaction SM30.
Table: BWOM_SETTINGS
MANDTOLTPSOURCEPARAM_NAMEPARAM_VALUEFormat/Values


BWFILOWLIM19910101YYYYMMDD


BWFINEXTX<space> or X


BWFINSAF3600<seconds>


BWFIOVERLA

<space> or X


BWFISAFETY1<Days>


BWFITIMBOR020000<Time>


DELTIMEST60<Days>


ORGSYSONLY

<space> or X
*** SAP recommends that you do not change the standard value.
More details about the settings are available at the following URL.
Financial Accounting: Procedure for Line Item Extraction


Index was modified for table BWFI_AEDAT as per Note 991429 to enable minute based extraction.
FIAP-img1.jpg

FIAP-img2.jpg

2.7    R/3 Tables used

The following R/3 tables were used for the FI-AP extraction.
Data Tables
BSIKAccounting: Secondary Index for Vendors
BSAKAccounting: Secondary Index for Vendors (Cleared Items)
LFA1Vendor Master (General Section)
LFB1Vendor Master (Company Code)
LFB5Vendor master (dunning data)
LFM1Vendor master record purchasing organization data
LFM2Vendor Master Record: Purchasing Data
LFBKVendor Master (Bank Details)
EKKOPurchasing Document Header
EKPOPurchasing Document Item

Config Tables
BWFI_AEDATList of changed documents with the change date and the document number which helps in the delta extraction. The number of days the data stored here depends on the setting in the bwom_settings table.The 0FI_*_4 extractors use this table for registering change data.
BWFI_AEDA2This is the BW FI: Log Table for Changed Transaction Figures The 0FI_*_6, 0FI_*_7 extractors use this table for registering change data.
BWFI_AEDA3This is the BW FI: Log Table for Changed Credit Management Data The 0FI_AP_8, 0FI _AP_9, 0FI_AP_10 extractors use this table for registering change data.
BWOM_SETTINGSSettings for the delta extraction each entry in the table signifies a special purpose.This table parameters are common to all the datasource extractors.
Parameters: BWFILOWLIM, BWFISAFETY, BWFIOVERLA, ORGSYSONLY, DELTIMEST, BWFIT IMBOR, BWFINEXT, BWFINSAF
BWOM2_TIMESTStores the delta extraction date and timestamp for reference from where the next delta extraction for the specific datasource to begin.
ROOSPRMSFStores the selections for the specific datasources under specific
source and destination systems.
TP S31This table contains the Process BTE: Alternative Function Modules from SAP. The function modules for the various BTEs are mapped in this table.
TPS01This table contains the description of the BTEs.

3      B/W Customization:

3.1    InfoProviders

The following InfoProviders were created i n BW to hold the data for FI-AP
FIAP-img3.jpg

3.2    Data Flows

The various data flows are as below.

0FI_AP_4 & ZFI_AP_4

FIAP-img4.jpg

*** The InfoObject ZFIAPLK has been modeled to have the Long Text loaded across 5 text attributes as in BW the Character limitation is that of 60 characters only while the Long text field is of 255 characters. The data gets loaded from ZFIAP_O3.


0FI_AP_6
FIAP-img6.jpg
0FI_AP_7
FIAP-img7.jpg

3.3    Roles for FI-AP

The following roles have been created to cater to different user groups
  • BW-INFO-PROVIDER-FIAP
  • BW-POWER-USER-FIAP
  • BW-END-USER-FIAP

3.4    Process Chain

To load the FI-AP data from SAP, a process chain has been created for the same which runs as part of the Meta Chains on a daily basis. It is basicall y run twice daily except on Sundays.
FIAP-img8.jpg

4         Appendix

4.1       Routines used in Transformations

  • RSDS 0FI_AP_4 PAE050 -> ODSO ZFIAP_O3
    • 0REF_DOC
    • 0POSTXT
    • ZFIAPLK
    • ZTXTLG_1
    • ZTXTLG_2
    • ZTXTLG_3
    • ZTXTLG_4
    • ZTXTLG_5
  • RSDS ZFI_AP_4 PAE050 -> ODSO ZFIAP_O4
    • 0REF_DOC
    • 0POSTXT

4.2       Source Code for ZBWFI_FILL_CI_00005021

FUNCTION ZBWFI_FILL_CI_00005021.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(I_OLTPSOURCE) TYPE  SBIWA_S_INTERFACE-ISOURCE
*"  CHANGING
*"     REFERENCE(C_STRUCTURE)
*"----------------------------------------------------------------------
DATA: BEGIN OF WA_KNB5,
        BUSAB TYPE BUSAB_MA,
      END OF WA_KNB5,
      BEGIN OF WA_ KNVP,
        PERNR TYPE PERNR_D,
      END OF WA_KNVP,
      BEGIN OF WA_T014,
        WAERS TYPE WAERS,
      END OF WA_T014,
      BEGIN OF WA_T001,
        KTOP2 TYPE KTOP2,
      END OF WA_T001.
DATA: THEAD LIKE THEAD.
DATA: BEGIN OF TLINETAB OCCURS 100.    "table to process comments
        INCLUDE STRUCTURE TLINE.
DATA: END OF TLINETAB.
DATA: C_KEY TYPE THEAD-TDNAME,
      I_LINE TYPE I.

  CASE I_OLTPSOURCE.
    WHEN '0FI_AR_4'. "Customers: Line Items with Delta Extraction
      FIELD-SYMBOLS: <FS_DTFIAR_3> TYPE DTFIAR_3.
      ASSIGN C_STRUCTURE TO <FS_DTFIAR_3>.
      "Details from KNA1:General Data in Customer Master
      SELECT  SINGLE KONZS KUKLA NIELS BRAN1
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_3>
        FROM  KNA1
        WHERE KUNNR = < FS_DTFIAR_3>-KUNNR.
      "Details from KNB1:Customer Master (Company Code)
      SELECT  SINGLE BUSAB
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_3>
        FROM  KNB1
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR
        AND   BUKRS = <FS_DTFIAR_3>-BUKRS.
      "Details from KNB5:Customer master (dunning data)
      SELECT  SINGLE BUSAB
        INTO  CORRESPONDING FIELDS OF WA_KNB5
        FROM  KNB5
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR
        AND   BUKRS = <FS_DTFIAR_3>-BUKRS
        AND   MABER = <FS_DTFIAR_3>-MABER.
      IF SY-SUBRC EQ 0.
        <FS_DTFIAR_3>-BUSAB_D = WA_KNB5-BUSAB.
      ENDIF.
      "Details from KNVV:Customer Master Sales Data
      SELECT  SINGLE VKORG VTWEG SPART BZIRK
        INTO  CORRESPONDING F IELDS OF <FS_DTFIAR_3>
        FROM  KNVV
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR.
      "Sales Engineer from KNVP:Customer Master Partner Functions
      SELECT  SINGLE PERNR
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_3>
        FROM  KNVP
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR
        AND   VKORG = <FS_DTFIAR_3>-VKORG
        AND   VTWEG = <FS_DTFIAR_3>-VTWEG
        AND   SPART = <FS_DTFIAR_3>-SPART
        AND   PARVW = 'SA'.
      "Sales Manager from KNVP:Customer Master Partner Functions
      SELECT  SINGLE PERNR
        INTO  CORRESPONDING FIELDS OF WA_KNVP
        FROM  KNVP
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR
        AND   VKORG = <FS_DTFIAR_3>-VKORG
        AND   VTWEG = <FS_DTFIAR_3>-VTWEG
� �       AND   SPART = <FS_DTFIAR_3>-SPART
        AND   PARVW = 'SM'.
      IF SY-SUBRC EQ 0.
         <FS_DTFIAR_3>-PERNR_M = WA_KNVP-PERNR.
      ENDIF.
      "Details from KNKK:Customer master credit management: Control area data
      SELECT  SINGLE KLIMK SKFOR NXTRV CTLPC CASHD
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_3>
        FROM  KNKK
        WHERE KUNNR = <FS_DTFIAR_3>-KUNNR
        AND   KKBER = <FS_DTFIAR_3>-KKBER.
      "Currency Key for KNKK from T014:Credit control areas
      SELECT  SINGLE WAERS
        INTO  CORRESPONDING FIELDS OF WA_T014
        FROM  T014
        WHERE KKBER = <FS_DTFIAR_3>-KKBER.
      IF SY-SUBRC EQ 0.
         <FS_DTFIAR_3>-WAERS_CM = WA_T014-WAERS.
      ENDIF.
      "Sold-To-Part y from VBRK:Billing Document: Header Data
      SELECT  SINGLE VKORG VTWEG SPART BZIRK KUNAG
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_3>
        FROM  VBRK
        WHERE VBELN = <FS_DTFIAR_3>-VBELN.
    WHEN '0FI_AP_4'. "Vendors: Line Items with Delta Extrcation
      FIELD-SYMBOLS: <FS_DTFIAP_3> TYPE DTFIAP_3.
      ASSIGN C_STRUCTURE TO <FS_DTFIAP_3>.
      "Details from EKPO:Purchasing Document Item
      SELECT  SINGLE MENGE MEINS
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAP_3>
        FROM  EKPO
        WHERE EBELN = <FS_DTFIAP_3>-EBELN
        AND   EBELP = <FS_DTFIAP_3>-EBELP.
      "Details from BKPF:Accounting Document Header
      SELECT  SINGLE PPNAM USNAM
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAP_3>
        FROM  BKPF
         WHERE BUKRS = <FS_DTFIAP_3>-BUKRS
        AND   BELNR = <FS_DTFIAP_3>-BELNR
        AND   GJAHR = <FS_DTFIAP_3>-FISCPER+0(4).
      "Read Long Text
      C_KEY+0(4) = <FS_DTFIAP_3>-BUKRS.
      C_KEY+4(10) = <FS_DTFIAP_3>-BELNR.
      C_KEY+14(4) = <FS_DTFIAP_3>-FISCPER+0(4).
      C_KEY+18(3) = <FS_DTFIAP_3>-BUZEI.
      CALL FUNCTION 'READ_TEXT'
        EXPORTING
          id                      = '0001'
          language                = sy-langu
          object                  = 'DOC_ITEM'
          name                    = C_KEY
        IMPORTING
          header                  = thead
        TABLES
          lines                    = tlinetab
        EXCEPTIONS
          id                      = 1
          language                = 2
          name                    = 3
          not_found               = 4
          object                  = 5
          reference_check         = 6
          wrong_access_to_archive = 7
          others                  = 8.
      IF SY-SUBRC = 0.
        I_LINE = 0.
        LOOP AT TLINETAB.
          IF I_LINE EQ 0.
            <FS_DTFIAP_3>-LGTXT = TLINETAB-TDLINE.
          ELSE.
            CONCATENATE <FS_DTFIAP_3>-LGTXT TLINETAB-TDLINE INTO <FS_DTFIAP_3>-LGTXT SEPARATED BY SPACE.
          ENDIF.
          ADD 1 TO I_LI NE.
        ENDLOOP.
      ENDIF.
    WHEN '0FI_GL_4'. "Customers: Line Items with Delta Extraction
      FIELD-SYMBOLS: <FS_DTFIGL_4> TYPE DTFIGL_4.
      ASSIGN C_STRUCTURE TO <FS_DTFIGL_4>.
      "Details from SKA1:G/L Account Master (Chart of Accounts)
      SELECT  SINGLE XBILK GVTYP
        INTO  CORRESPONDING FIELDS OF <FS_DTFIGL_4>
        FROM  SKA1
        WHERE SAKNR = <FS_DTFIGL_4>-HKONT
        AND   KTOPL = <FS_DTFIGL_4>-KTOPL.
      "Read Alt GL Account from SKB1:G/L account master (company code)
      SELECT  SINGLE ALTKT
        INTO  CORRESPONDING FIELDS OF <FS_DTFIGL_4>
        FROM  SKB1
        WHERE BUKRS = <FS_DTFIGL_4>-BUKRS
        AND   SAKNR = <FS_DTFIGL_4>-HKONT.
      IF <FS_DTFIGL_4>-ALTKT IS NOT INITIAL.
         "Read corresponding Chart of Account for Alt GL Account from T001
        SELECT  SINGLE KTOP2
          INTO  CORRESPONDING FIELDS OF WA_T001
          FROM  T001
          WHERE BUKRS = <FS_DTFIGL_4>-BUKRS.
        IF SY-SUBRC EQ 0.
          <FS_DTFIGL_4>-ZKTOPL = WA_T001-KTOP2.
        ENDIF.
      ENDIF.
    WHEN OTHERS.
  ENDCASE.
  UNASSIGN <FS_DTFIAR_3>.
  UNASSIGN <FS_DTFIAP_3>.
  UNASSIGN <FS_DTFIGL_4>.
ENDFUNCTION.

4.3       Source Code for ZBWFI_FILL_CI_00005022

FUNCTION ZBWFI_FILL_CI_00005022.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(I_OLTPSOURCE) TYPE  SBIW A_S_INTERFACE-ISOURCE
*"  CHANGING
*"     REFERENCE(C_STRUCTURE)
*"----------------------------------------------------------------------
DATA: BEGIN OF WA_T001,
        KTOP2 TYPE KTOP2,
      END OF WA_T001.
  CASE I_OLTPSOURCE.
    WHEN '0FI_AR_6'. "Customer Sales Figures via Delta Extraction
      FIELD-SYMBOLS: <FS_DTFIAR_1> TYPE DTFIAR_1.
      ASSIGN C_STRUCTURE TO <FS_DTFIAR_1>.
      "Details from KNA1:General Data in Customer Master
      SELECT  SINGLE NIELS
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_1>
        FROM  KNA1
        WHERE KUNNR = <FS_DTFIAR_1>-KUNNR.
      "Details from KNB1:Customer Master (Company Code)
      SELECT  SINGLE AKONT
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_1>
        FROM  KNB1
        WHERE KUNNR = <FS_DTFIAR_1>-K UNNR
        AND   BUKRS = <FS_DTFIAR_1>-BUKRS.
      "Details from KNVV:Customer Master Sales Data
      SELECT  SINGLE BZIRK
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_1>
        FROM  KNVV
        WHERE KUNNR = <FS_DTFIAR_1>-KUNNR.
      "Details from T001:Company Codes
      SELECT  SINGLE KTOPL
        INTO  CORRESPONDING FIELDS OF <FS_DTFIAR_1>
        FROM  T001
        WHERE BUKRS = <FS_DTFIAR_1>-BUKRS.
    WHEN '0FI_GL_6'. "General Ledger Sales Figures via Delta Extraction
      FIELD-SYMBOLS: <FS_DTFIGL_1> TYPE DTFIGL_1.
      ASSIGN C_STRUCTURE TO <FS_DTFIGL_1>.
      "Read Alt GL Account from SKB1:G/L account master (company code)
      SELECT  SINGLE ALTKT
        INTO  CORRESPONDING FIELDS OF <FS_DTFIGL_1>
        FROM  SKB1< br>        WHERE BUKRS = <FS_DTFIGL_1>-BUKRS
        AND   SAKNR = <FS_DTFIGL_1>-SAKNR.
      IF <FS_DTFIGL_1>-ALTKT IS NOT INITIAL.
        "Read corresponding Chart of Account for Alt GL Account from T001
        SELECT  SINGLE KTOP2
          INTO  CORRESPONDING FIELDS OF WA_T001
          FROM  T001
          WHERE BUKRS = <FS_DTFIGL_1>-BUKRS.
        IF SY-SUBRC EQ 0.
          <FS_DTFIGL_1>-ZKTOPL = WA_T001-KTOP2.
        ENDIF.
      ENDIF.

    WHEN OTHERS.
  ENDCASE.
  UNASSIGN <FS_DTFIAR_1>.
  UNASSIGN <FS_DTFIGL_1>.
ENDFUNCTION.

4.4       Source Code for ZFI_AP_4 in CMOD

Include: ZXRSAU01
-----------------
  WHEN 'ZFI_AP_4'.
    PERFOR M enhance_ZFI_AP_4 TABLES c_t_data.
Include: ZXRSAF02
-----------------
*...enhance_ZFI_AP_4:Enhancements for FIAP: Parked Documents
FORM enhance_zfi_ap_4 TABLES c_t_data STRUCTURE zoxdae0290.
  DATA: i_zfiap4 TYPE TABLE OF zoxdae0290 WITH HEADER LINE.
  DATA: BEGIN OF ls_faede.
          INCLUDE STRUCTURE faede.
  DATA: END   OF ls_faede.
*  STATICS: R_FISCPER TYPE BWFI_T_FISCPER.
  LOOP AT c_t_data INTO i_zfiap4.
*...check for fiscper selection........................................*
    i_zfiap4-fiscper(4)   = i_zfiap4-gjahr.
    i_zfiap4-fiscper+4(1) = '0'.
    i_zfiap4-fiscper+5(2) = i_zfiap4-monat.
*    CHECK i_ZFIAP4-FISCPER IN R_FISCPER.
*...setting constants........................................*
    i_zfiap4-uposz = '0001'.
    i_zfiap4-statusps = 'D'.
    i_zfiap4-koart = 'K'.
    i_zfiap4-augdt = '00000000'.
*...amount fields........... ...........................................*
    CASE i_zfiap4-shkzg.
      WHEN 'S'.
        i_zfiap4-dmsol = i_zfiap4-dmbtr.
        i_zfiap4-dmshb = i_zfiap4-dmbtr.
        i_zfiap4-wrsol = i_zfiap4-wrbtr.
        i_zfiap4-wrshb = i_zfiap4-wrbtr.
      WHEN 'H'.
        i_zfiap4-dmhab = i_zfiap4-dmbtr.
        i_zfiap4-dmshb = i_zfiap4-dmbtr * -1.
        i_zfiap4-wrhab = i_zfiap4-wrbtr.
        i_zfiap4-wrshb = i_zfiap4-wrbtr * -1.
    ENDCASE.
*...negative posting
    IF i_zfiap4-xnegp = 'X'.
      CASE i_zfiap4-shkzg.
        WHEN 'S'.
          i_zfiap4-dmhab = i_zfiap4-dmbtr * -1.
          i_zfiap4-wrhab = i_zfiap4-wrbtr * -1.
          CLEAR: i_zfiap4-dmsol,
                 i_zfiap4-wrsol.
        WHEN 'H'.
          i_zfiap4-dmsol = i_zfiap4-dmbtr * -1.          i_zfiap4-wrsol = i_zfiap4-wrbtr * -1.
          CLEAR: i_zfiap4-dmhab,
                 i_zfiap4-wrhab.
      ENDCASE.
    ENDIF.

*...Due Date Calculations
    IF ( i_zfiap4-zfbdt IS INITIAL ).
      MOVE i_zfiap4-bldat TO i_zfiap4-zfbdt.
    ENDIF.
    MOVE-CORRESPONDING i_zfiap4 TO ls_faede.
    CALL FUNCTION 'DETERMINE_DUE_DATE'
      EXPORTING
        i_faede = ls_faede
      IMPORTING
        e_faede = ls_faede
      EXCEPTIONS
        OTHERS  = 1.
    MOVE: ls_faede-netdt TO i_zfiap4-netdt,
          ls_faede-sk1dt TO i_zfiap4-sk1dt,
          ls_faede-sk2dt TO i_zfiap4-sk2dt.
*...Details from BKPF:Accounting Document Header
    SELECT  SINGLE usnam
        INTO i_zfiap4-ppnam
        FROM  vbkpf
        WHERE bukrs = i_zfiap4-bukr s
        AND   belnr = i_zfiap4-belnr
        AND   gjahr = i_zfiap4-gjahr.
    IF sy-subrc EQ 0.
      MODIFY c_t_data FROM i_zfiap4.
    ENDIF.
    CLEAR i_zfiap4.
  ENDLOOP.
ENDFORM.                    "enhance_ZFI_AP_4
*...enhance_ZFI_AP_4:Enhancements for FIAP: Parked Documents

Jun 23, 2014

Why master data harmonization is so important when uploading data from external sources


In order to ensure that a cross-data source reporting is possible after uploading all the different data sources like Point-of-Sale data (e.g. from retailers) or Market Research data (e.g. a Retail Panel from syndicated data providers), it needs to be taken care of that the data itself is consistent across the sources.
This is done through data harmonization. It establishes the link between the internal views of products and locations to those of the retailers, distributors and syndicated data providers. Such a linkage comes along with some key benefits when talking about enterprise reporting capabilities:

  1. Consistent, shared data and best practices that can be leveraged across retail accounts and internal departments' lead to more process efficiency… during operations and decision making.
  2. Improved collaboration with the retailers by talking the language of the retailer, using his product or store ID and names e.g. during negotiations.
  3. Reduced costs through better internal and external collaboration that is based on all participants sharing a common view of the business.  

With that, harmonized data creates a 360 degree view of the business.

Easy example:
While a certain product that is produced by the manufacturer "Taste" is called "Chili Chocolate" in-house at the manufacturer, the different retailers and syndicated data providers might have other names, such as "CHILI_CHOC_1254", "Chocolate_Taste_Chilli","03-TAS-chil", etc. How does the manufacturer know which ones represent his internal product so that he can later on be sure to report based on the same data?

Some use cases for cross-data source reporting

  1. Sell-in vs. Sell-Out Comparison:
    e.g. the manufacturer wants to analyze deviations between the quantity that he shipped to his retailers compared to what has been sold to the end consumer. This helps to uncover situations in which the retailer build up stock on purpose e.g. during trade promotions as well as provides answers to the questions "Did the sales to the end consumers increase accordingly?" Or "Did the retailer just increase stock level on their side and the manufacturer can expect a decline in his own sales soon?"
  2. Cross-Country sales performance of acertain retailer
    e.g. the manufacturer wants to get an aggregated view of the retailer's sales performance in U.S., Canada and U.K. He also wants to compare KPIs and trends for the different countries. For this, a harmonized reporting based on the retailer's POS data of the three countries is needed.
    Benefit:  Internally, the manufacturer can view the data in his own view = his own product IDs and descriptions….When talking to retailer, he can also see the data in the language of retailer by using the Product IDs and descriptions of the retailer.
  3. Brand performance in a certain country
    e.g. the manufacturer wants to report the performance of the different products that belong to a certain brand in a certain country but which is split in between different product categories and therefore is split in different Market Research databases.
    Questions like "What is the Market Share of the brand in this country?" or "Who are the main competitors and how are they performing?" can be answered with this reporting.
  4. Sales performance in one country but across retailers
    e.g. the manufacturer wants to analyze the sales performance of the different retailers in Canada and compare their Sell-Out data with his own Sell-In data (e.g. Shipments). For that, a combination and harmonization of the various retailers' POS data with his own ERP sales data in Canada is needed.

Challenges of external master data harmonization
Different master data keys and number ranges are usually used by different external (e.g. Retailers, Syndicated Data Providers) and internal (e.g. ERP) sources. Furthermore, GTINs (Global Trade Item Number) provided by these sources might not always be correct or are even not provided at all. What is missing in such a case is a "golden key" that links all data that is of the same kind (e.g. the same product or the same retailer store) to one single data set which exclusively identifies the product, no matter in which data source it appeared and independent from its description, GTIN or ID that was provided by the different sources.

How it is done in SAP Demand Signal Management
The data harmonization within SAP Demand Signal Management determines which products belong together by creating a GPID (Global Product Identifier) and linking all the different external numbers representing a single manufacturer product together. This enables a holistic mapping of all external master data against internal master data coming from the manufacturers ERP system, for example. Moreover, the manufacturer can now compare different internal and external views on data from different sources based on a single product or loc ation.
More information can be found here: http://help.sap.com/saphelp_dsim100/helpdata/en/c5/361326822249afa7de3f4a12b93439/frameset.htm

Jun 16, 2014

Real-Time Reporting


Real-time reporting is one of the important issues for an organization, especially, online reporting on very sensitive data, for example sales and distributions etc.
There are several standard options in SAP BW for online (real time or almost real time) reporting.
In the first part of the article I will give a briefly explanation of some standard methods, like RDA or Virtual Cube, pointing on disadvantages of these methods.
In the second part of the article I will show another, combined, method which I use for some online reports.

RDA and Virtual Cube
 
There are two general ways, the most popular, for online/near online reporting:
  1. RDA (Real Data Acquisition)
  2. Virtual Cube
Eac h of them has advantages and disadvantages.
Beside the main advantage of these methods, which is online reporting, there are a lot of disadvantages.
I guess that these disadvantages, which will be described later, may "kill" the whole online concept and, actually, have more weight on possible advantages of the methods.

RDA

First, actually, this is not really real time, but almost real time (RDA daemon might have minimum one minute interval). 
In addition, small time intervals between loads may lead (and surely will lead) to overlapping loads.
Therefore loading using RDA with one minute interval in "hot" periods (the end of a quarter) does not seem to be working.

Virtual Cube

One of the main problems of using Virtual Cube is extremely poor performance. I guess most of ABAP and BASIS professionals won't accept an idea of performing direct RFC SELECT from VBAK (for example).
Such model might work on small volume of a data, but RDA as well as standard ETL processes work fine with small volume of data.
In addition, writing complex and performance optimized ABAP code for Virtual Cube based on function modules requires strong ABAP skills.

In conclusion, I guess implementing online reporting model on big volume of a data, sales orders for example, is very complex and even impossible using RDA or Virtual Cube.

Combined Delta Method
 
I n the next part of the article I would like to suggest another, combined method for online reporting.
Suppose we have the next scenario:
  1. There is some standard ETL of sales order items (2LIS_11_VAITM standard extractor) which runs hourly. Each load brings about 5000 lines approx).
  2. Between hourly loads the data (delta) is collected in the delta queue in ECC.
As mentioned early RDA of Virtual Cube won't be useful in this case.
But what if I will bring just deltas from such delta queue using SAP standard function module without resetting delta queue and finally summarize the deltas with the existing records in a cube/DSO?
For example, sales order 123 has booked value 100 EUR and it was loaded to BW at 12:00 using standard hourly ETL.
At 12:15 sales order was updated to 120 EUR.
In this case we have an order with 100 EUR in BW and delta +20 in delta queue (till the next hourly loa d at 13:00).
If I would retrieve such delta value (+20), instead of retrieving whole data from VBAK or running RDA daemon each minute, and add it to the existing  value (100) just at the report level, on demand, so my report give me the most updated value - 120 EUR.
In this case we won't use a complex ABAP code or retrieving a data from huge tables such as VBAK.
In addition, we won't use RDA mechanism, but still getting online values - really online.
Last, but not the least, 2LIS_11_VAITM extractor does not support RDA without adjusting the extractor. In most organizations SD is very sensitive area, and getting an approval of principal level for changing such important extractor won't be easy mission.

Implementation

First of all, I assume that you have a cube (standard or not) which has been loaded every hour with 2LIS_11_VAITM extractor.
The first step is creating the function module for online extractor. 
Such function module should use SAP standard function module for reading delta queue RSFH_GET_DATA_SIMPLE with the following parameters (I mention here only an important part of the parameters): i_osource = '2LIS_11_VAITM' and i_updmode = 'D'.
For detailed implementation of the function module based extractor you suppose to turn to your ABAP team.
The extractor fetches those records which are currently in delta queue.
The next step is creating the cube which has a similar structure as a main cube loaded each hour.
This cube is a virtual cube based on DTP for direct access with an Info Source 3.X.
Finally we create a Multi Provider based on these 2 cubes (hourly and virtual).
A report built on such Multi Provider will show online data.

Conclusion

I've shown several standard options in SAP BW for online (real time or almost real time) reporting such as RDA and Virtual Cube.
Each of the options separately has very serious disadvantages such as RDA may lead to overlapping loads and Virtual Cube may have a performance troubles.
I've suggested another, combined method - using standard hourly loads and, on other hand, adding the records which are in delta queue fetched by SAP standard function module.
The result is online report with a minimal impact on performance, with very simple extractor based on function module.