Recently we where doing a test at our customer site and part of that test was changing an existing Classification Ruleset. This had been done before, but for some reasons when we tried it then the Synchronization of the Rulesets completed successfully but the Ruleset itself stayed in status Install Pending. With a Ruleset in this status you can’t do any calculations in Oracle Incentive Compensation.
We looked at metalink but couldn’t find anything. We issued a SR with Oracle, they couldn’t find anything either and kept us waiting for over 2 month. In the meanwhile another project also required us to change the Ruleset, but we couldn’t wait much longer. So I started working my way to the trace files for the Synchronize Rulesets myself. A big file with a large number of queries. It was the first time I’ve really looked at a trace file so maybe I was a bit lucky when I stumbled upon this step:
select oracle_username from APPS.fnd_oracle_userid where install_group_num = 1 and read_only_flag = 'U'
It returned 0 rows.
Reading the trace file a bit more I found that this is the user that should be installing the Classification Ruleset as a package on the database. But when it can’t find any user the synchronization stops without an error message and the Ruleset stays in status isntall pending.
These installations are normally done by the APPS user. But the APPS user had install_group_num = 0 on our databases and therefore the query couldn’t find any user. Probably the install_group_num was set to 0 as part of a Oracle patch, explaining why we had been able to change Rulsets in the past.
Changing the install_group_num back to 1 fixed the problem:
update fnd_oracle_userid set install_group_num=1 where oracle_username ='APPS'; commit;
Oracle metalink Note:369186.1 describes a similar problem where a Custom Transactions source needs to be created as a package on the database.
So we had been stuggeling with an issue for more then 2 months which was a simple as changing a 0 to an 1.
