Oracle Business Intelligence 10.1.3.4.1

Oracle released OBIEE 10.1.3.4.1 & BI Apps 7.9.6 and can be downloaded from OTN

R12.1.1 is available to download

Oracle EBS Release 12.1.1 is available to download from edelivery.

The changes in the 12.1.1 technology stack

1. Database 11gR1 (11.1.0.7)
2. Oracle Application Server 10g Release 2 (OracleAS 10.1.2.3)
3. Oracle Application Server 10g Release 3 (OracleAS 10.1.3.4)

Note: The database tier technology stack for a new Release 12.1.1 installation is Oracle 11g Release 1 (11.1.0.7).

Upgrades from Release 11i with Oracle 10g Release 2 and Oracle 11g Release 1 are both supported

Custom reports exit with APP:FND-362 error

Customized Reports exit with error

====================================
APP-FND-00362: Routine &ROUTINE cannot execute request &REQUEST for program &PROGRAM, because theenvironment variable &BASEPATH is not set for the application to which the concurrent program executable &EXECUTABLE belongs. Shut down the concurrent managers. Set the basepath environment variable for theapplication. Restart the concurrent managers.
====================================

Customized reports registered under the customization Application directory structure were created under APPL_TOP directory and then were copied into it or that the concurrent program definition was incorrect. Rather, the custom top under which this program was registered was not defined correctly. the value of custom application registration within apps (applications -> register) should be the same value as in the shell level.

To implement the solution, execute the following steps:

1. Define customization _TOP directory in your Applications environment file (e.g: CUST_TOP=".../cust/xx.x.x")

2. If step 1 is executed already, login to forms, select system administrator responsibility and navigate to applications -> register. query up the Custom application and change the value of it with the same name as in the shell level.

3. Bounce the concurrent Managers

How to rename the access log file of the Oracle HTTP Server to include the date of the day it is created in?

We can rename the access log file of the Oracle HTTP Server to include the date of the day it is created in. Follow the below step to do it.

- For the 9iAS 1.0.2.x
1- Stop the HTTP Server2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file6 - Start the HTTPServer again

II - For the 9iAS 9.0.2.x
1- Stop the HTTP Server:$ORACLE_HOME/dcm/bin/dcmctl stop -ct ohs -v -d
2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:
3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file
6- Apply the changes to the DCM Repository:$ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs -v -d
7- Start the HTTPServer again:$ORACLE_HOME/dcm/bin/dcmctl start -ct ohs -v -d

III For AS 10g , 9.0.4.x - 10.1.x
1- Stop the HTTP Server2- Make a backup of $ORACLE_HOME/Apache/Apache/conf/httpd.conf:
3- Open the file httpd.conf and search for something like the following line:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
where ORACLE_HOME=D:\oracle\FRM_REP_904
4 - Modify the httpd.conf file like this example; this will create a new access_log every 24 hours- 86400 seconds:
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log 43200" common
CustomLog "D:\oracle\FRM_REP_904\Apache\Apache\bin\rotatelogs logs/access_log.%d%m%Y 86400"common
5 - Save the httpd.conf file
6- Apply the changes to the DCM Repository:$ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct ohs -v -d
7- Start the HTTPServer again

Note: For the 9.0.2.x - 10.1.x , you can use the EM WebPage to change the httpd.conf file then you will no longer need to run the "dcmctl updateconfig" command.

How to Configure the Account Analysis Report in Release 12 for Large Reports

A typical problem for application user's of these reports is the fact that it is likely to fail when generating the report for very large data sets so the environment needs to be configured properly to handle that.

1. Set the Scalable Option to on for these programs.
--> This prevents the following error in the Subledger Accounting program's log:

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***
The scalability option is set by performing these steps:
1. As System Administrator: Navigate to Concurrent->Program->Define
2. Query up the report: Account Analysis Report
3. Add a parameter named ScalableFlag:

* Value Set: yes_no
* Default Value: Y
* Select checkboxes Enable and Required
* Do not select the check box Displayed, or users could turn this off at runtime.
* Token needs to be ScalableFlag (this is a case sensitive value).

Complete these steps for both the General Ledger and the Subledger Accounting concurrent program definitions.

2. Configure the XML Publisher Administrator Configuration settings. This prevents "java.lang.OutOfMemoryError" errors in the Output Post Processor log associated to the Subledger Accounting program.

* As XML Publisher Administrator navigate to Administration->Configuration.
* Under Temporary Directory pick a temporary file location on your concurrent processing node. This should be at least 5GB or 20x larger than largest XML data file you generate
* Under FO Processing, set:

o Use XML Publisher's XSLT processor set to True
o Enable scalable feature of XSLT processor set to False
o Enable XSLT runtime optimization set to True

3. Configure the Output Post Processor's JVM. These steps set the JVM to 2GB, depending upon your server's size you might find 3 GB (-mx3072m), 4GB (-mx4096m) or even 5GB (-mx5120m) is a better value. This setting prevents the error "java.lang.OutOfMemoryError: Java heap space" in the Output Post Processor's log associated to the Subledger Accounting Program.

* Login to SQL*Plus as APPS.
* SQL>update FND_CP_SERVICES set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m' where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP'); * Bounce the concurrent managers.

4. Test the reports

Creating the Database link

Navigation for defining database link is Setup > System > Database Link.

To define a database link in General Ledger perform the following steps
on the Target instance (the one you are copying to):

1. Enter the Database Name and optional Description for the linked database.
(You must also supply the Database Name to the FSG Transfer Program).

NOTE: You can find the database name by executing the
following sql query in the source database:

sql> SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_NAME';

2. Enter the following additional information for the database:

Connect String = The network connect string to point to the
General Ledger database.

Domain Name = The domain of the remote General Leder database
to which this link connects.

NOTE: You can find the domain name by executing the
following query in the source database:

sql> SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_DOMAIN';

APPS Username = The name of the Oracle Applications account that has
read access to the General Ledger database tables.

NOTE: This is NOT the username you enter to login to Oracle
Applications.

APPS Password = The passwrod for the above username.

NOTE: For security reasons, when you enter the
password, General Ledger will display asterisks
Instead of actual characters in the password.

3. Save your changes before exiting the form.

Useful Queries Related to Concurrent Requests in 11i Applications

Scheduled concurrent requests:

The below query will return all the concurrent requests which are scheduled using any of the above methods:
==========================================================
SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ) schedule_type, DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ), 'PERIODICALLY', 'EVERY ' cr.resubmit_interval ' ' cr.resubmit_interval_unit_code ' FROM ' cr.resubmit_interval_type_code ' OF PREV RUN', 'ONCE', 'AT :' TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 'EVERY: ' fcr.class_info ) schedule, fu.user_name, requested_start_date FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu, apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fcr.release_class_id(+) = cr.release_class_id AND fcr.application_id(+) = cr.release_class_app_id;
==========================================================

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week. Positions 1 through 31: Specific day of the month. Position 32: Last day of the month Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments:

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules. Note: This query will return even though the request was submitted using a different responsibility.
==========================================================
SELECT request_id, NAME, argument_text, user_name FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t1 WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t2 WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text AND t1.user_name = t2.user_name GROUP BY NAME, argument_text, user_name HAVING COUNT (*) > 1) ORDER BY user_name, NAME
==========================================================

Average pending time per request:

This is a very useful query to check the performance of the concurrent managers. Average pending time for a request is calculated like below: ("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc) We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

==========================================================
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, concurrent_queue_name, (SUM ( ( actual_start_date - (CASE WHEN requested_start_date > request_date THEN requested_start_date ELSE request_date END ) ) * 24 * 60 * 60 ) ) / COUNT (*) "Wait_Time_per_Req_in_Secs" FROM apps.fnd_concurrent_requests cr, apps.fnd_concurrent_processes fcp, apps.fnd_concurrent_queues fcq WHERE cr.phase_code = 'C' AND cr.actual_start_date IS NOT NULL AND cr.requested_start_date IS NOT NULL AND cr.controlling_manager = fcp.concurrent_process_id AND fcp.queue_application_id = fcq.application_id AND fcp.concurrent_queue_id = fcq.concurrent_queue_id GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name ORDER BY 2
==========================================================

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program:

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
==========================================================
SELECT user_concurrent_program_name, user_concurrent_queue_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc, apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = 'P' AND cqc.include_flag(+) = 'I' AND cp.LANGUAGE = 'US' AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = 'US'
==========================================================

To see all the pending / Running requests per each manager wise:

==========================================================
SELECT request_id, phase_code, status_code, user_name, user_concurrent_queue_name FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id AND cq.LANGUAGE = 'US' AND cwr.requested_by = fu.user_id ORDER BY 5
==========================================================
Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs:

The below query can be used to find all incompatibilities in an application instance.
==========================================================
SELECT a2.application_name, a1.user_concurrent_program_name, DECODE (running_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) "Type", b2.application_name "Incompatible App", b1.user_concurrent_program_name "Incompatible_Prog", DECODE (to_run_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) incompatible_type FROM apps.fnd_concurrent_program_serial cps, apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1, apps.fnd_application_tl a2, apps.fnd_application_tl b2 WHERE a1.application_id = cps.running_application_id AND a1.concurrent_program_id = cps.running_concurrent_program_id AND a2.application_id = cps.running_application_id AND b1.application_id = cps.to_run_application_id AND b1.concurrent_program_id = cps.to_run_concurrent_program_id AND b2.application_id = cps.to_run_application_id AND a1.language = 'US' AND a2.language = 'US' AND b1.language = 'US' AND b2.language = 'US'
==========================================================

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.