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
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.
4.2 Successful execution on Copy ERP to IBP displayed to the user who executes the Copy Function.
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.
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.
5.2 Successful execution on Copy ERP to IBP displayed to the user who executes the Copy Function.
5.3 Once the template is refreshed the data is matching between ERP and IBP can be seen in Column “L” and Column “M”.
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.
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
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.
Now choose the Trace Tab. Trace shows that 193 records are read and copied.
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.
Entries in COSP table.
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.
- SAP Managed Tags:
2 Comments
