SUMMARY With SAP BPC NW 10.1 a new data model is introduced that combines the strength of the different planning components SAP BPC NW for end-user friendliness and flexibility; SAP BW-IP for feature richness and integrated data consistency; SAP HANA for blazingly high performance. With this the strategy for planning solutions on SAP BW which covered BPC NW on one side and BW-IP / PAK on the other side. The former was focusing on line-of-business driven scenarios with a strong focus on flexibility and end-user friendliness. The latter covered IT-driven scenarios with a tight integration into the BW architecture and EDW data. Now the combination covers not only these two pillars but every combination thereof. The new data model is called ‘embedded’ to reflect the embedded nature of the solution into BW.This document demonstrates the Extraction and Retraction of Data between ERP and IBP systems. This scenario is implemented in SAP BPC 10.1 Embedded version and uses FOX Script and VBA.

AUTHOR BIO Anila Mohan Anila Mohan is an SAP BPC Consultant in Deloitte Consulting LLP and has around 7.5 years of experience in implementing BPC solutions for Fortune 500 clients across various industries. She has worked on different versions of BPC (5.1 MS till EPM 10.1).

Shoba Vadlamudi SAP certified associate with 5+ years of experience in SAP BPC Net Weaver and Microsoft versions. Worked as a consultant in implementation of consolidation, cost allocations, planning and forecasting for a major bank in US. Gained knowledge of SAP BI, SAP BO. Also, learned business processes when dealing with requirements. Worked as a consultant for implementing consolidation in SAP BPC for a utility major. Also worked upgrade to SAP BPC 7.0 in this assignment.

1. Objective SAP BPC 10.1 Embedded model is an integration of BPC with BW-PAK (HANA accelerated version of IP), it’s mainly about leveraging IT centric EDW model with BPC business oriented features. FOX script is the programming language used in BPC 10.1 – Embedded (Unified) models similar to the BI-IP/PAK. This will help to perform complex calculations in BPC and it is based on key-figure modelling.

2. Business Scenario This document demonstrates the process of Extraction and Retraction of data between the ERP and IBP systems. This is achieved using the standard planning functions and using the input ready form using the VBA macros.

3. Ways to trigger DATA MOVEMENT

3.1 Prompt Selection The below prompt is created for keying data .It’s a part of the Query Y_SFIN_A19_IRQ0003 which is already created in Business Intelligence

Fig1.png

4. Data Extraction Process – ERP to IBP

4.1 The input ready form with Initial Data available on query Y_SFIN_A19_IRQ0003 created in Business Intelligence is shown below. User Selection: Category and version. The logic of pulling data from EPR to IBP is button driven. The Copy is a standard functionality that drives the data movement from ERP to IBP use standard planning function which in turn called by a Planning Sequence.  Click Copy ERP to IBP button to execute the planning function. Column “L” of the below screen shot exhibits the data after successful Copy execution. Column “M” has data that is available in ERP.

Fig2.png

4.2 Successful execution on Copy ERP to IBP displayed to the user who executes the Copy Function.

Fig3.png

4.3 Column “L” is matching with data in Column “M” which is the resultant of the data movement being successful from ERP to IBP.

Fig4.png

5. Data Retraction Process – IBP to ERP 5.1 As this is an input ready template created in IBP, the data is modified in IBP which would reflect and match the same in ERP, due to which the retraction process is being in scope. Modify data in IBP (Input Ready Template) as shown in Column “L19” the data in IBP is increased. Click on to Copy IBP to ERP to execute the in turn planning function.

Fig5.png

5.2 Successful execution on Copy ERP to IBP displayed to the user who executes the Copy Function.

Fig6.png

5.3 Once the template is refreshed the data is matching between ERP and IBP can be seen in Column “L” and Column “M”.

Fig7.png

Hereby  the above steps details how the custom method of data Extraction and Retraction can be carried out in 10.1 Unified modelling using input ready template.

5. Standard “AS-IS” workbook The below screen shot is the standard process given my SAP in a non-input ready template in 10.1 Unified version.

Fig8.png

6. STEPS to view the data moved in the ERP tables: Using SAT Transaction Code in the SAP Logon – A new ABAP Trace Tool has been developed addressing major limitations in SE30.Trace file content is processed and stored in the database. Trace data from all application servers in the system is accessible via SAT. Trace can be kept for a longer time, as trace data on the database is not deleted automatically.

1. Start the ABAP Runtime Analysis (SAT) via System -> Utilities -> Runtime Analysis -> Execute or call the transaction “/NSAT” directly.

2. Type “RSPLAN” into Transaction field. SAP transaction RSPLAN (Modeling BI Integrated Planning) is classified in the Business Warehouse module under application component Planning and runs planning: General Services program SAPLRSPLS_LAUNCH upon execution.

3. Choose Eval. Immediately to start the analysis right after the measurement.

4. Press Execute button to start the measurement

Fig9.png

Display Planning Sequence page is opened and execution of the planning sequence “/ERP/SFIN_A09_PS01” that copies from /ERP/SFIN_R01 to /ERP/COOM_V07 is shown here.

Fig10.png

Now choose the Trace Tab. Trace shows that 193 records are read and copied.

Fig11.png

SAT also shows the DB tables that are hit on retraction – primarily COSS and COSP. This means it helps to identify the source and target transaction tables which is identified by the number of records.

Fig12.png

Entries in COSP table.

Fig13.png

APPENDIX

VBA MACRO TO CALL A PLANNING SEQUENCE

Sub onExecutePS01()

IResult = Application.Run(“SAPExecutePlanningSequence”, “PS_1”)

End Sub

FOX SCRIPT

DATA CATEGORY TYPE / ERP / CATEGORY.

DATA VERSION TYPE / ERP / VERSION.

DATA GLACCT TYPE / ERP / GL_ACCT.

DATA COSTELMT TYPE / ERP / COSTELMT.

DATA KATYP TYPE 0FISCVARNT.

DATA COORDER TYPE / ERP / COORDER.

DATA PCOSTCTR TYPE / ERP / PCOSTCTR.

DATA PACTTYPE TYPE / ERP / PACTTYPE.

DATA PCOORDER TYPE / ERP / PCOORDER.

DATA PWBSELMT TYPE / ERP / PWBSELMT.

DATA UOM TYPE 0UNIT. D

ATA UOM_CL TYPE 0UNIT.

DATA CO_AREA TYPE / ERP / CO_AREA.

DATA FISCYEAR TYPE 0FISCYEAR.

DATA METYPE TYPE / ERP / METYPE.

DATA VTDETAIL TYPE / ERP / VTDETAIL.

DATA IS_WBS_ORDER TYPE / ERP / BAL_FLAG.

DATA IS_MANUAL_PLANNING TYPE / ERP / BAL_FLAG.

DATA IS_OK TYPE / ERP / BAL_FLAG.

*DATA DCIND TYPE /ERP/DCINDCO

*DATA DCIND_OK TYPE /ERP/DCINDCO.

DATA L TYPE I.

BREAK-POINT.

COORDER = OBJV( ).

IF COORDER = ‘ ‘.

EXIT.

ENDIF.

CATEGORY = VARV( ‘/ERP/P_CATEGORY’ ).

CO_AREA = VARV( ‘/ERP/P_CO_AREA01’ ).

FISCYEAR = OBJV( ). GLACCT = OBJV( ).

*DCIND = OBJV().

PCOSTCTR = OBJV( ). PACTTYPE = OBJV( ).

PCOORDER = OBJV( ). PWBSELMT = OBJV( ).

* we are an internal order

IS_WBS_ORDER = ‘X’.

* allow additionally activity allocation for manual planning

IS_MANUAL_PLANNING = ‘A’.

FOREACH VERSION IN SELECTION.

COSTELMT = GLACCT.

CALL FUNCTION FCOM_DETERMINE_BW_VALUE_TYPE

EXPORTING IV_KOKRS = CO_AREA

           IV_GJAHR = FISCYEAR       

          IV_KSTAR = COSTELMT        

          IV_SKOST = PCOSTCTR         

          IV_SLSTA = PACTTYPE          

          IB_IS_PSP_ORDER = IS_WBS_ORDER    

       IB_IS_MANUAL_PLANNING = IS_MANUAL_PLANNING

  IMPORTING EV_METYPE = METYPE     

  EV_VTDETAIL = VTDETAIL   

  EV_KATYP = KATYP.

IF NOT(( KATYP = ’01’ OR KATYP = ’04’ OR KATYP = ’11’ OR KATYP = ’12’ OR KATYP = ’90’ )         

AND NOT( PCOSTCTR IS INITIAL AND PCOORDER IS INITIAL AND PWBSELMT IS INITIAL ) ).

*   ignore primary planning records with sender fields

     IF NOT( KATYP = ’43’ AND( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ).

*     ignore activity allocation records with initial sender activity type (simple finance allows to plan this).

*     (simple finance allows to plan this). 

    IF NOT( KATYP = ’21’ AND( NOT( PCOSTCTR IS INITIAL AND PCOORDER IS INITIAL AND PWBSELMT IS INITIAL )

AND( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ) ).

*        ignore primary order cost records with sender fields and

*        secondary order cost records with with initial sender cost center and sender activity type      

IF( KATYP = ’43’ OR( KATYP = ’21’ AND NOT( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ) ).

*         activity allocation or secondary order costs:

*         check that activity type category is ‘1’ = to be planned manually        

CALL FUNCTION FCOM_CHECK_LATYP_MANUAL        

EXPORTING IV_KOKRS = CO_AREA            

       IV_GJAHR = FISCYEAR            

       IV_VERSN = VERSION              

     IV_SKOST = PCOSTCTR              

     IV_SLSTA = PACTTYPE                  

IV_AUFNR = COORDER        

IMPORTING EV_OK = IS_OK        

           EV_LEINH = UOM_CL.

       ENDIF.    

   IF NOT(( KATYP = ’43’ OR( KATYP = ’21’ AND NOT( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ) ) AND IS_OK IS INITIAL ).

*        copy only activity allocation/secondary order records with category to be planned manually      

   IF KATYP <> ’41’.

*          ignore overhead cost elements because they have in classical CO a deviating D/C indicator CO

*          with value ‘C’ instead of the default value ‘D’ in Simple Finance

*          CALL FUNCTION FCOM_DETERMINE_DC_INDICATOR_CO

*          EXPORTING IV_KOKRS = CO_AREA

*                    IV_GJAHR = FISCYEAR

*                    IV_KSTAR = COSTELMT

*          IMPORTING EV_BELKZ = DCIND_OK.

*          IF DCIND = DCIND_OK

. *    *       copy only records with feasible debit/credit indicator CO

*          !!Special logic for UoM of classical only for primary planning, not activity allocation

*             KATYP = 43, and secondary order costs KATYP = 21 with sender cost center/activity type!!         

  IF NOT( KATYP = ’43’ OR( KATYP = ’21’ AND NOT( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ) ).

*             set amount and quantity of classical to zero          

   CALL FUNCTION FCOM_DETERMINE_PRIMARY_UOM    

         EXPORTING IV_KOKRS = CO_AREA          

             IV_AUFNR = COORDER                    

   IV_GJAHR = FISCYEAR               

        IV_KSTAR = COSTELMT      

       IMPORTING EV_MEINH = UOM_CL.      

     ENDIF.     

      { ‘/ERP/AMOUNT’, #, COSTELMT, 00, METYPE, VERSION, VTDETAIL, 020, ‘/ERP/COOM_V07’, UOM_CL } = 0.         

  { ‘/ERP/QUANTY’, #, COSTELMT, 00, METYPE, VERSION, VTDETAIL, 020, ‘/ERP/COOM_V07’, UOM_CL } = 0.      

     FOREACH UOM IN REFDATA.           

  IF KATYP = ’43’ OR( KATYP = ’21’ AND NOT( PCOSTCTR IS INITIAL OR PACTTYPE IS INITIAL ) ).

*               activity allocation/secondary order costs       

        UOM_CL = UOM.       

      ENDIF.

*             We copy the SFIN Amount to the Classical Amount field to make sure the delta is

*             calculated correctly with respect to the totals.

             { ‘/ERP/AMOUNT’, #, COSTELMT, 00, METYPE, VERSION, VTDETAIL, 020, ‘/ERP/COOM_V07’, UOM_CL }                 =                   { ‘/ERP/AMOUNT’, #, COSTELMT, 00, METYPE, VERSION, VTDETAIL, 020, ‘/ERP/COOM_V07’, UOM_CL }               

  + { ‘/ERP/AMOUNT’, CATEGORY, #, #, #, #, #, #, ‘/ERP/SFIN_R01’, UOM }.

*             We copy the SFIN Quantity to the Classical Quantity field to make sure the delta is

*             calculated correctly with respect to the totals.               IF UOM = UOM_CL

. *               Here, one could do more: Check whether UOM is compatible with UOM_CL.

If yes, do a unit

*               conversion from UOM to UOM_CL,

if not issue an info message            

   { ‘/ERP/QUANTY’, #, COSTELMT, 00, METYPE, VERSION, VTDETAIL, 020, ‘/ERP/COOM_V07’, UOM_CL }             

  = { ‘/ERP/QUANTY’, CATEGORY, #, #, #, #, #, #, ‘/ERP/SFIN_R01’, UOM }.            

ENDIF.    

       ENDFOR

ELSE.

*           overhead cost element (KATYP 41)             MESSAGE I084( FCOM_PLANNING_CORE ).           ENDIF.         ELSE.

*         PCOSTCTR/PACTTYPE with other LATYPE than 1           MESSAGE I083( FCOM_PLANNING_CORE ) WITH PACTTYPE PCOSTCTR.         ENDIF.       ELSE.

*       try to distinguish primary/secondary for error message (KATYP 21)         IF NOT( PCOSTCTR IS INITIAL AND PCOORDER IS INITIAL AND PWBSELMT IS INITIAL ).           IF PACTTYPE IS INITIAL.

*           primary order costs with sender             MESSAGE I086( FCOM_PLANNING_CORE ).           ELSE.

*           secondary order costs without sender cost center             MESSAGE I087( FCOM_PLANNING_CORE ) WITH PACTTYPE.           ENDIF.         ENDIF.       ENDIF.     ELSE.

*     KATYP = ’43’ and PCOSTCTR initial or PACTTYPE initial       MESSAGE I082( FCOM_PLANNING_CORE ).     ENDIF.   ELSE.

*   primary planning with partner -> not manual planning     MESSAGE I085( FCOM_PLANNING_CORE ) WITH KATYP.   ENDIF. ENDFOR.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply