Instrument to Ledger Stat Allocation ID Not Writing Results to the LEDGER_STAT Table (Doc ID 421544.1)

Last updated on MARCH 29, 2011

Applies to:

Oracle Profitability Manager - Version: 4.5.39 and later   [Release: 4.5 and later ]
Information in this document applies to any platform.
Oracle Financial Services Applications (OFSA) 4.5.39
Oracle Performance Analyzer (PA) - Version: 4.5 or higher

Symptoms

A simple Allocation ID that takes records from an instrument table and writes them to the Ledger Stat table is not generating any results.  While the Allocation's "Filter On" does retrieve records from the input table, records are not being written to Ledger Stat.  The insert or update on LEDGER_STAT is not working.

An example of the Allocation setup is as follows:

Filter On: FEM_CHECKING
Debit: LEDGER_STAT

No calculation is performed on the Filter On records.

A SQL Trace and tkprof of the Allocation shows a full table scan on the update of LEDGER_STAT:

update ledger_stat set ytd_09 = ytd_09 + :ytd_09, month_09 = month_09 +
:month_09, ytd_10 = ytd_10 + :ytd_10, month_10 = month_10 + :month_10,
ytd_11 = ytd_11 + :ytd_11, month_11 = month_11 + :month_11, ytd_12 = ytd_12
+ :ytd_12, month_12 = month_12 + :month_12
where
ledger_stat.identity_code = :identity_code and ledger_stat.year_s =
:year_s and ledger_stat.accum_type_cd = :accum_type_cd and
ledger_stat.consolidation_cd = :consolidation_cd and
ledger_stat.currency_type_cd = :currency_type_cd and
ledger_stat.set_of_books_cd = :set_of_books_cd and
ledger_stat.FINANCIAL_ELEM_ID = :FINANCIAL_ELEM_ID and
ledger_stat.ORG_UNIT_ID = :ORG_UNIT_ID and ledger_stat.GL_ACCOUNT_ID =
:GL_ACCOUNT_ID and ledger_stat.COMMON_COA_ID = :COMMON_COA_ID


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.01       0.00          0          0          0          0
Execute     35   587.35     588.15    1318551    1318590        210          0
Fetch        0     0.00       0.00          0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       36   587.36     588.15    1318551    1318590        210          0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 201 (A511001)

Rows    Execution Plan
------- ---------------------------------------------------
0       UPDATE STATEMENT GOAL: CHOOSE
0        UPDATE OF 'LEDGER_STAT'
0         TABLE ACCESS (FULL) OF 'LEDGER_STAT'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms