12.2.4 Remove custom application registered using the Application>Register screen

[IKV]

We are configuring a R12.2.4 environment and in the process registered the custom application using the Application>Register screen. We were not aware that in 12.2.4 adsplice is to be used for setting up custom application and registration on the screen is not to be done.
Now we do not want to create a new custom top with a different name.

1. Is there a way to delete the already created custom application and start setting up the custom application afresh?

2. If not, then is it possible to somehow make adsplice use the same custom application name and proceed with setting up the custom application?

I'd appreciate any help regarding this issue.

[ErmanArslansOracleBlog]

Hi,

Looking back the steps for adding custom top in 12.2.
The document is :Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)

There is a known issues section. It says;

If you are an upgrade customer with a custom product top in the context file but have not run adsplice, you must remove the relevant entry from the FND_OAM_CONTEXT_CUSTOM table and then run AutoConfig on all nodes.
The same approach needs to be taken if you have any custom context variables with more than eight characters, as a custom schema abbreviation cannot exceed eight charcters.

After performing either of the above steps, you should run adsplice to add/register the custom product (after applying the latest AD-TXK RUP).


So, in my opinion you can delete the relevant line from FND_OAM_CONTEXT_CUSTOM table + some other tables and rerun adsplice to accomplish your goal
 
Also check this thread:
This issue in this tread looks like yours...

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Custom-TOP-Creation-Error-tp49.html

[IKV]

Emran,

Thanks a lot for the quick response.
I had seen that comment on the note earlier.
However for us the FND_OAM_CONTEXT_CUSTOM table is empty. Also there are no records for the custom application in FND_PRODUCT_INSTALLATIONS and FND_ORACLE_USER_ID.

The custom application seems to be present in only the FND_APPLICATION table, which must have been created when the application was registered in the Application>Register screen.

Would you suppose running the steps as mentioned in the note (reusing the already created custom application name and application ID) would help setup the custom application? Will adsplice run into any conflict with the existing record in FND_APPLICATION?

[ErmanArslansOracleBlog]

I suggest you to try running adsplice with the same name and application id ...

In document Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1);

It says;
**Note: In Release 12.2, you should use adsplice to register your application in order to ensure that the application is set up for online patching. Do not use the Applications window to register applications in this release.

**In Oracle E-Business Suite Release 12.2, adsplice performs the following steps:
Makes the new user edition-enabled.
Enables Edition-Based Redefinition (EBR) for the custom objects.
When you start the next online patching cycle, the prepare phase will run fs_clone to synchronize the two file systems.


***Note: If you upgraded your environment from an earlier release to Release 12.2, then you should run adsplice for your custom application again after the upgrade, using the same application ID and application name as when you originally added your custom application. Running the Release 12.2 version of adsplice after the upgrade helps ensure that the custom top folder for your application will be included when the two file systems are synchronized during online patching.


-> So run adsplice with the same name and application id ...  IF you get any errors; then errors must be resolved, some deletes may be necessary, as I suggested in my previous post.. But first try running it with the same name and app id.

[IKV]

Thank you again for your inputs. I'll execute the steps as per the note and use the same application name and ID. Will post the results here soon - hope to have this completed in next couple of days.

[ErmanArslansOracleBlog]

Okay.
It s better to do this kind of things firstly on test instance.

[IKV]

Thanks, was able to successfully execute the steps (per the note) and create the custom application - used existing custom app name and ID.
Appreciate all your help.

[IKV]

Hi Erman,

We are encountering an issue which might be related to the way the custom application was registered, so posting this here.

Some of the custom programs that are run via the custom application are completing in error.
They run for a while and complete in error with the completion text "Concurrent program returned no reason for failure".
The concurrent manger log shows the below error:

"Call to establish_mgr failed
When reconnecting for request id (524808)
APP-FND-01564: ORACLE error 942 in establish_mgr

Cause: establish_mgr failed due to ORA-00942: table or view does not exist"

I noticed that the custom application is added to the Standard Data Group with the custom Oracle ID (custom schema user) and not APPS.
We recently setup a new testing instance where the custom application was registered the correct way (using adsplice) and there the Oracle ID (for custom app under Standard Data Group) is APPS.

Could this be the reason for the programs not running? And if so, then is there a way to change the Oracle ID to APPS? The Data Group setup screen is not editable.

[ErmanArslansOracleBlog]

Seems so.. Setting it to apps will probably resolve..
It must be that ; when the ebs connected to the database with the custom schema, it can not see an apps system table..

(ALTER system SET EVENTS='00942 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';)

Lets first enable ORA-00942 event trace in the database,reproduce the problem and send me the log file.. Lets see the table in question..

[ErmanArslansOracleBlog]

Also note down the data group related tables..
FND_DATA_GROUPS
FND_DATA_GROUP_UNITS

[ErmanArslansOracleBlog]

Also in note Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1);

it says;


Database objects such as tables, indexes, and sequences should be created in the XXMZ schema. You then need to:

a) Grant all privilege from each custom data object to the APPS schema.

For example, logged in as the XXMZ user:

SQL>grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object

For example, logged in as the APPS user:

SQL>create synonym myTable for XXMZ.myTable;>



Please check the note above.

[IKV]

Erman, thanks for the inputs. I did a trace and below is an excerpt from the trace file.
The program is very basic - it is only creating a log and an output file. There are no custom objects involved so grants and synonyms are out of picture.

----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=4d8w3n5u1mgp3) -----
LOCK TABLE FND_CONCURRENT_PROCESSES IN SHARE UPDATE MODE NOWAIT

----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=4d8w3n5u1mgp3) -----
LOCK TABLE FND_CONCURRENT_PROCESSES IN SHARE UPDATE MODE NOWAIT

----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=fmfdkztk8vx23) -----
SELECT CATEGORY, SEVERITY, FND_LOG_SEVERITY FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME = :B1 AND APPLICATION_ID = :B2 AND LANGUAGE_CODE  = :B3 AND ROWNUM  = 1

----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=fmfdkztk8vx23) -----
SELECT CATEGORY, SEVERITY, FND_LOG_SEVERITY FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME = :B1 AND APPLICATION_ID = :B2 AND LANGUAGE_CODE  = :B3 AND ROWNUM  = 1

[ErmanArslansOracleBlog]

Okay...
So ORa-00942 shows that your custom database user which is associated with the data group, does not have necessary privileges to operate on table such as : FND_CONCURRENT_PROCESSES and FND_NEW_MESSAGES... There may be other tables too..

So, that s why the standart way is to use apps as oracle_id  when adding a custom schema to a data
group.

You can grant those privileges, but then you will end up a custom schema with bunch of privileges.. You may not want this.. That 's my go with APPS as oracle_id

[IKV]

Thanks Erman, that was our guess too - the custom app needs to have APPS id in the Data Group. The question now is how to change the Oracle ID to APPS - the fields are non-editable on the Data Group form.

Does this require only an update on FND_DATA_GROUP_UNITS table (ORACLE_ID column)? Or is there a script to update all and any other tables involved?

[ErmanArslansOracleBlog]

Updating FND_DATA_GROUP_UNITS will be not a supported action, but it can be tried in a test instance.