OFSAA Dimension Reverse Population Procedure Failing for Org and Product (Doc ID 1338597.1)

Last updated on JULY 11, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.2.8 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.2
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.x

Symptoms

On Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) 7.2.8 when attempting to run Reverse Population on the Org dimension, the following error occurs:

ERROR
Error. While inserting into OFSA members table insert into OFSA_DETAIL_ORG_UNIT(LEAF_NODE, O_ORG_ID)

select aa.ORG_UNIT_ID,max(case when cc.attribute_id='5030' then DIM_ATTRIBUTE_NUMERIC_MEMBER else -99100 end) from
DIM_ORG_UNIT_B aa
join DIM_ORG_UNIT_TL bb
on aa.ORG_UNIT_ID =
bb.ORG_UNIT_ID
left outer join DIM_ORG_UNIT_ATTR cc
on aa.ORG_UNIT_ID = cc.ORG_UNIT_ID
where leaf_only_flag = 'Y' group by aa.ORG_UNIT_ID


Also receiving similar errors when attempting to run reverse population on the Product dimension:

Error. While inserting in OFSA members table insert into OFSA_DETAIL_OTHER_COA(leaf_num_id,
leaf_node,
common_coa_id,
tax_rate,
o_coa_id,
o_org_id)

select 4,aa.PRODUCT_ID,max(case when cc.attribute_id = '5011' then DIM_ATTRIBUTE_NUMERIC_MEMBER else -99100 end),0,-99100,max(case when cc.attribute_id = '5013' then DIM_ATTRIBUTE_NUMERIC_MEMBER else -99100 end) from DIM_PRODUCTS_B aa
join DIM_PRODUCTS_TL bb
on aa.PRODUCT_ID =
bb.PRODUCT_ID
left outer join DIM_PRODUCTS_ATTR cc
on aa.PRODUCT_ID = cc.PRODUCT_ID
where leaf_only_flag = 'Y' group by aa.PRODUCT_ID


The issue can be reproduced at will with the following steps:

1. Run the following for Org dimension:

Declare
num number;
Begin
num := fsi_batchmemberload ('MYINFODOM_20110710','20110710',1,null,1 );
End;


2. Run the following for Product dimension:

Declare
num number;
Begin
num := fsi_batchmemberload ('MYINFODOM_20110710','20110710',4,null,1 );
End;

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