ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 66758. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 84 ORA-06512: at line 1

Changed Workflow Administrator role from SYSADMIN to a user ABC. After this change the users started getting the following error in workflow notification emails.

ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 66758. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 84 ORA-06512: at line 1

• To resolve the issue I changed the Workflow Administrator role from ABC to 'Workflow Administrator Web (New)' responsibility.
• Assingned this responsibility to SYSADMIN and user ABC.
• Then run the request “Synchronize WF LOCAL tables” with “ALL” in parameter.

FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oXXXXXXXX.tmp in the directory, /usr/tmp

A TEST instance was already up and running and I created another production clone on the same server/machine. Once the services of both the instances  

The concurrent programs were completing in error and when checked the log files, the following error message was there:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o0031866.tmp in the directory, /usr/tmp.

You will find more information in the request log.

ORA-06512: at "APPS.FND_FILE", line 417

When investigated, I found that both the instnaces were creating .tmp files in /usr/tmp directory with the same name. This error was being thrown when one instance was trying to create .tmp file and a file with the same name was already created by the other instance.

• To resolve the issue I shutdown both the apps and db services of one instance.
• Created a directory 'temp' in '/usr/tmp' and changed the ownership of this dir to user owner of this instance 
• Logon to database as sysdba
• Create pfile from spfile
• modified UTL_FILE_DIR parameter's first entry from '/usr/tmp' to '/usr/tmp/temp'
• Created spfile from pfile
• Brought up the db and listener
• Now modified the $APPLPTMP variable in TEST_oratest.xml file from '/usr/tmp' to '/usr/tmp/temp'
• Run the autoconfig on apps tier/node
• Brought up the apps services
• Retested the issue and it was resolved 

R12 Web ADI: Make it work with MS Office 2010

In Excel 2010

Go to File > Options. Following screen will open

Select Trust center

Click on Trust center settings

Select ActiveX Settings and make sure setting match following screen

Then click on Macro Settings and make sure settings match as following

Click on Protected View and make sure setting match as following

Click on External Contents and make sure setting match as following

Press Ok. Then again Press OK.

Now in Internet explorer

Go to Tools > Internet Options

Click on Security

Select Internet and then on Custom Level

Scroll Down to Download portion and match settings with following

Then Scroll down to miscellaneous section and match setting with following

Then Scroll Down to Scripting section and match setting with following

Press OK. Then again press OK.

APP-FND-204 Concurrent Manager encountered an error while running the spawned concurrent program Receiving Transaction Manager - RCVOLTM for your concurrent request XXXXXX. TM-SVC LOCK HANDLE FAILED

This is happening because of large number of receiving processes being spawned at the same time.

Navigation:

• System Administrator (or comparable) Responsibility > Concurrent > Manager > Define
• Query for Manager='Receiving Transaction Manager'
• Click Work Shifts button and review value in Processes field (Increase the number of process)
• Go to: Concurrent -> Manager -> Administer
• Restart and Activate the Receiving Transaction Manager
• Refresh to check to make sure the target and actual processes = desired number of processes
• Retest the issue

If steps above still does not work, please perform the ADRELINK utility on the executable
as follows:

At UNIX prompt:
cd $PO_TOP/bin
adrelink.sh force=y ranlib=y "po RCVOLTM"
This relinks the executable for the Receiving Transaction Manager which is RCVOLTM. Now shut down and restart the Receiving Transaction Manager, you may have to click on RESTART, then
REFRESH and then click on ACTIVATE.

ORA-00257: archiver error. Connect internal only, until freed

Today I faced this issue. The users were unable to login to Oracle Apps. When I checked the system the archive logs SAN volume was full. The database was not able to write the online logs to destination  path. I tried to connect to database using SQL Plus and it gave me the following error:

ORA-00257: archiver error. Connect internal only, until freed

I took the following steps to resolve the issue.

• Tried to delete old archive logs using RMAN but rman was also not able to connect
• Then I deleted the old logs using OS command and freed some space.
• Switch the log as

              SQL> alter system switch logfile;

• Check the current status of archive logs as

             SQL> archive log list;

• Then finally I bounced the Apps and DB services.

How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)

GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)

GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)

GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)

XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id)

XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id)

XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)

The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.

The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

Force termination of a concurrent request

SQL> update fnd_concurrent_requests
set status_code='X', phase_code='C'
where request_id=xxxxxx;

SQL> commit;

TO CHECK WORKFLOW VERSIONS, PENDING JOBS AND USER RESPONSIBILITIES ISSUES

Run the below sql script and check the output.

$FND_TOP/sql/wfver.sql

Check the file version

To check the file version use any of the following methods.

a) $ ident path/file_name

b) $ strings -a path/file_name |grep '$Header'

All Receiving Transactions Fail with RVTTH-445: Subroutine ORA-20001: -: XLA-95103

Error:

RVTTH-445: Subroutine ORA-20001: -: XLA-95103: An internal error occurred. Please inform your
system administrator or support representative that: An internal error has occurred in the program
xla_e

Solution 1:

1. Manually re-compile following database packages even if they are valid to ensure dependent called objects are compiled :

RCV_ACCRUALACCOUNTING_GRP
RCV_ACCEVENTS_PVT
RCV_SEEDEVENTS_PVT

SQL> alter package RCV_ACCRUALACCOUNTING_GRP compile;
SQL> alter package RCV_ACCRUALACCOUNTING_GRP compile body;

SQL> alter package RCV_ACCEVENTS_PVT compile;
SQL> alter package RCV_ACCEVENTS_PVT compile body;

SQL> alter package RCV_SEEDEVENTS_PVT compile;
SQL> alter package RCV_SEEDEVENTS_PVT compile body;
2. Next, relink receiving executables - logged into the application tier with the environment is set

cd $PO_TOP/bin
$ adrelink.sh force=y ranlib=y "PO RCVOLTM"
$ adrelink.sh force=y ranlib=y "PO RVCTP"
3. Stop and Start the Receiving Transaction Managers to call the newly recompiled executables.
(System Administrator > Concurrent > Manager > Administer (Deactivate and then Activate))

4. Test and confirm the error is no longer present.

Solution 2:

1. Resolve INVALID Objects
2. Relink receiving executables
   cd $PO_TOP/bin
   $ adrelink.sh force=y ranlib=y "PO RCVOLTM"
   $ adrelink.sh force=y ranlib=y "PO RVCTP"
3. Bounce Receiving Transaction Manager:
   - $ ps -ef | grep RCVOLTM (to see how many processes are running)
   -Deactivate Receiving Transaction Manager
   (System Administrator > Concurrent > Manager > Administer)
   -$ ps -ef | grep RCVOLTM (repeat until no processes are running)
   -Restart Receiving Transaction Manager
4. Perform transaction.

OPP Log Shows: Unable to determine SMTP server to use: set FND_SMTP_HOST

You have run a concurrent request and you want its output to be emailed on spefied email addrees specified email address on submit request form > delivery options.
But the request in completed in warning. When OPP log was checked there was following exception:

oracle.apps.fnd.cp.opp.PostProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST
 at oracle.apps.fnd.cp.opp.EmailDeliveryProcessor.deliver(EmailDeliveryProcessor.java:66)
 at oracle.apps.fnd.cp.opp.DeliveryProcessor.process(DeliveryProcessor.java:91)
 at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)

To resolve the issue:
1. Go to System Administrator responsibility
2. Navigate to Profile -> System
3. Query the %fnd%smtp% profiles
4. Set the following profile values
    FND: SMTP Host (host name or IP)
    FND: SMTP Port (port number, default 25)

Check which generic users are active in your Apps 12.1.x instance

/* Formatted on 28-Mar-12 3:22:03 PM (QP5 v5.163.1008.3004) */
SELECT a.user_name,
   a.description,
   a.email_address email_address,
   a.start_date,
   a.employee_id,
   a.password_lifespan_days
  FROM fnd_user a
 WHERE a.end_date IS NULL
   AND a.user_name IN
      ('GUEST',
     'AME_INVALID_APPROVER',
     'ANONYMOUS',
     'APPSMGR',
     'ASGADM',
     'ASGUEST',
     'AUTOINSTALL',
     'BOL-OPS',
     'BOL-SETUP',
     'BOL-SUPPORT',
     'CONCURRENT MANAGER',
     'FEEDER SYSTEM',
     'IBE_ADMIN',
     'IBE_GUEST',
     'IBEGUEST',
     'IEXADMIN',
     'INITIAL SETUP',
     'IRC_EMP_GUEST',
     'IRC_EXT_GUEST',
     'MOBILEADM',
     'MOBADM',
     'MOBDEV',
     'OP_CUST_CARE_ADMIN',
     'OP_SYSADMIN',
     'PORTAL30',
     'PORTAL30_SSO',
     'STANDALONE BATCH PROCESS',
     'SYSADMIN',
     'WIZARD',
     'XML_USER',
     'ORACLE12.0.0',
     'ORACLE12.1.0',
     'ORACLE12.2.0',
     'ORACLE12.3.0',
     'ORACLE12.4.0',
     'ORACLE12.5.0',
     'ORACLE12.6.0',
     'ORACLE12.7.0',
     'ORACLE12.8.0',
     'ORACLE12.9.0',
     'INDUSTRY DATA',
     'ASADMIN',
     'OA_IMPLEMENTER',
     'PASYSADMIN',
     'MFG',
     'PRESETUP',
     'SETUP',
     'CONVERSION',
     'CONVERSIONS')

Concurrent Manager Status Shows 'System Hold Fix Manager before resetting counters'

To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
    Please make sure that no FNDLIBR, FNDSM, or any dead process is running.

2. Stop the database.

3. Start the database.

4. cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"
$ adrelink.sh force=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit). Article- ID : 134007.1
Title: CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
    select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
    CONCURRENT_QUEUE_NAME like 'FNDSM%';
7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

Oracle Applications and Database Monitoring Tools

Database Monitoring Tools

Database management involves the monitoring, administration, and maintenance of the databases and database groups in your enterprise. Enterprise Manager is the premier tool for managing your database environment.

With Enterprise Manager, you receive:

·         A complete set of integrated features for managing Oracle Databases

·         Unparalleled scalability that lets you manage a single database or thousands of instances

·         An intuitive management product that leads the industry in ease of deployment and use

There are two flavors of Enterprise Manager

1.       Oracle Enterprise Manager DBCosole or DBControl

2.       Oracle Enterprise Manager Grid Control

OEM DBConsole

·         Database Control is the Enterprise Manager Web-based application for managing Oracle Database 11g Release 1 (11.1) and later

·         Database Control is installed and available with every Oracle Database 11g installation

·         From Database Control, you can monitor and administer a single Oracle Database instance

·         There is no extra hardware, software or licensing cost to use it

OEM Grid Control

·         Grid Control is the Enterprise Manager console you use to centrally manage your entire Oracle environment

·         Within Grid Control, you access the multiple database targets using the Targets tab, then Databases

·         It also provide GUI interface for Oracle Data Guard

·         Separate server is required to install OEM 11g Grid Control

·         Additional software installation and licensing of the following is required to use OEM Grid Control

1.       Oracle WebLogic Server 11g Release 1

2.       Oracle Enterprise Manager Grid Control 11g Release 1

3.       Oracle Database 11g Release 1 or 2

Oracle E-Business Suite Monitoring Tools

The Oracle E-Business Suite Plug-in 4.0 can be used to manage Oracle E-Business Suite systems.

• The Oracle E-Business Suite Plug-in 4.0 includes functionality that was available in previous releases of the Oracle Application Management Pack for E-Business Suite (AMP) and the Oracle Application Change Management Pack for E-Business Suite (ACMP).
• All of the functionality that was previously available for AMP is now classified as "System Management for E-Business Suite".
• All of the functionality that was previously available for ACMP is now classified as "Change Management for E-Business Suite".

System Management for Oracle E-Business Suite

Oracle E-Business Suite Plug-in extends Enterprise Manager Grid Control to monitor and manage Oracle E-Business Suite systems.

This new release of Oracle E-Business Suite Plug-in offers the following key capabilities:

• Oracle Enterprise Manager 11g Grid Control Support: All components of the management suite are certified with Oracle Enterprise Manager 11g Grid Control.
• Built-in Diagnostic Ability: Release 4.0 has numerous major enhancements that provide the necessary intelligence to determine if the product has been installed and configured correctly. There are diagnostics for Discovery, Cloning, and User Monitoring that will validate if the appropriate patches, privileges, setups, and profile options have been configured. This feature improves the setup and configuration time to be up and operational.

Change Management for Oracle E-Business Suite

Oracle E-Business Suite Plug-in is Oracle's offering for customers to monitor and manage Oracle E-Business Suite changes. Application Change Management Pack Plug-in provides a centralized view to monitor and orchestrate changes (both functional and technical) across multiple Oracle E-Business Suite systems.

In Release 4.0 Oracle E-Business Suite Plug-in has been enhanced to provide more control and flexibility in managing Oracle E-Business Suite changes.

Signon Profile Options

Profile Option	Description	Default Value	My Recommendation
Signon Password Failure Limit	The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.	NULL	5
Signon Password Hard to Guess	The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules: ·         The password contains at least one letter and at least one number. ·         The password does not contain the username. ·         The password does not contain repeating characters	No	Yes
Signon Password Length	Signon Password Length sets the minimum length of an Applications signon password.	5	7
Signon Password No Reuse	This profile option specifies the number of days that a user must wait before being allowed to reuse a password.	NULL	90
Signon Password Case	Oracle Applications gives you the ability to control case sensitivity in user passwords through this profile option.	Insensitive	Sensitive
ICX: Session Timeout	This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.	30 Minutes	30 Minutes

Limit concurrent connections?

Doc 375403.1 - "How Can I Restrict Applications Users To Be

Signed In Only Once At Any Time"

HOW TO PASS PARAMETERS TO A PROCEDURE IN FORM PERSONALIZATION

On the personalization form select the "Action" tab and choose the type "Builtin".
Select the "Execute a Procedure" and you can call your database procedure as follows:

='declare
v_field_value VARCHAR2(200);
begin
xx_procedure_name ('''||${item.block_name.fieled_name1.value}||''','''||${item.block_name2.fieled_name.value}||''');
end'

By using this example you can pass as many parameters as you want to you procedure.

How to Clear Application Cache

Login to application with the user who have access to Functional Administrator responsibility.

1- Navigate to Functional Administrator Responsibility

2- Choose the " Core Services" Tab

3- Choose "Caching Framework"

4- Click on "Global Configuration"

5- Click on "Clear All Cache"

6- The Click on yes on the display which will appear.