SQL rule in SAP FC 7.5

[Former Member]

Hello, coleagues!

I have a task now, which implies SQL rule implementation in sap fc 7.5.

Is anybody has  any materials how to implement it? (How to's , examples and so on)

How to start learn it? ( i was unable to find any links with any explanations)

Thank you a lot!

[olga_vasilieva]

May this post would be useful:

Useful SQL query for SAPBusinessObjects Financial consolidation - Enterprise Performance Management ...

[Former Member]

Olga, thank you for your response, but this is not what i'm looking for.

I need to do some reclassification between accounts with some IF logic:

I try to explaine with following example

Lets assume that we have 2 Asset accounts A1, A2

I need to analyze SUM of 2 accounts as a trigger (SUM <0) for reclassification.

EXAMPLE 1: (Trigger logic = true, so we need to post new value for AC3)

AC   PA    AMOUNT

A1   PA1   -700

A2   PA2   500

Result: because sum of 2 accounts < 0 then A3  = A1 + A2 = -700 + 500 = -200

EXAMPLE 2: (Trigger logic = false, so we don't need to post new value for AC3)

AC   PA    AMOUNT

A1   PA1   -700

A2   PA2   900

Result: because sum of 2 accounts > 0  then don't any result for rule.

This is what i need to implement.

[olga_vasilieva]

Aleksander,

the business case behind your example is not clear to me.

Usage of SQL rules is advised to be limited as SQL rules have to be tested manually and don’t leave a clear audit trail like other rules. Consider using other options depending on your business case:

1. If you need a simple one-time check, use a report with cell formulas.
2. If you need to do a pre-consolidation check for packages sent by subsidiaries, define a control (in Category Builder).
3. If you need some accounts to be automatically populated, create a formula (in Category Builder).
4. If you need an automatic rule for preconsolidation or consolidation phase, create a corresponding automatic rule. For your example you will have 2 steps: first fill AC3 with amounts of AC1 and AC2; then clear all negative amounts on AC3.
5. If nothing else works for you, write an SQL rule. SQL rule uses SQL syntax specific to your database with system variables (that can be found by clicking on button ‘Variables’ on ’SQL’ tab of SQL rule creation window). Some information on it is provided in BOF230 (former FM212) training. You can also find some info and SQL rule code examples for MS, HANA and Oracle databases in FC 10 Windows User Guide http://help.sap.com/businessobject/product_guides/bofc10/en/fc_10_win_user_en.pdf Since SQL rules are rarely used I have yet to find better tutorials.

[Former Member]

Olga! Thanks a lot! Nothing of 1-4 fits for me, so i started to implement my SQL Rule.

First, i decided to write smth very simple just for check, how it works

I am on Oracle db

1) Through PL/SQL Developer on my dev system i figure out that table generated for new test consolidation is  "CT_CO0074"

2) I wright this sample code to insert some data for this [WORKTABLE] table (just for test smth simple and understand how it works)

BEGIN

insert into [WORKTABLE] (CT_0000_AN, PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, PARTNER, CTSHARE, CURNCY, TECHORIG,GLOBORIG, JOURNAL, ENUMBER, ROLLUP_PARTNER, ROLLUP, CT_0000_CO, CT_0000_CFO, CT_0000_PCFO, CT_0000_FNC, CT_0000_FNR, CT_0000_CF, CT_0000_PRJ, CT_0000_MVZ, CT_0000_DMVZ, CT_0000_COS, CT_0000_CUR, CT_0000_FS, CT_0000_FSLBU, AMOUNT, CONVAMOUNT, CONSAMOUNT, DATA_COMMENT)

VALUES ('0', '30416896','1196', '1196','29075','328','549','434','0','134', '-524274','1', '-524285', '1', '434', '196552', '0','0','0','0','0','0','0','0','0','351','0','0','0','1111', '111','111','0' )\;

END\; ;

3) Error in log

I can't understand what is "SYSTEM", "UNDV....." "CONVAMOUNT"? there is no any info about this fields in table definition in PL/SQL developer.

Thanks a lot.

[olga_vasilieva]

Aleksander,

I believe SYSTEM.UNDVHKTMOCYRW.CONVAMOUNT2 - is a system path to your WORKTABLE table (with UNDVHKTMOCYRW being automatically generated name) and its required non-null column CONVAMOUNT2. Since you didn't mention it (or mentioned smth else?) system error appeared.

You can find description of FC10 technical tables with their columns and properties (including [WORKTABLE]) here:

SAP BusinessObjects Financial Consolidation Physical Database Schema Guide

https://websmp208.sap-ag.de/~sapidb/011000358700000238172011E/fc10_database_structure_en.pdf

The difference from FC7.5 is described here:

SAP BusinessObjects Financial Consolidation 10.0 - Comparing the Database Schemas of 7.5 and 10.0

http://service.sap.com/~sapidb/011000358700000238182011E/fc75_db_structure_dif10_en.pdf

If links above won't work, you can find these documents here: http://help.sap.com/boall_en/

Hope it helps.

[Former Member]

Hello,

SQL rules define the SQL queries which are run during "consolidation" processing. They can be run

on the package or during consolidations and preconsolidations.

SQL rules should only be defined by experienced users.

An SQL rule is defined in two stages:

• Enter the SQL query
• Define the trigger condition to run the rule.

The size of an SQL rule is limited to 1MB.

For more information ; please consult the user guide document :

http://help.sap.com/businessobject/product_guides/bofc75/en/fc_75_win_user_en.pdf

Regards,

Soumaya

[Former Member]

Hello! Thank you, but this is a general definition only (i hade already found it user guide before post this thread). I mean some mope "practical and deeper" information about it.