Caching
Framework in oracle apps R12 comes with an administration User
interface and it is available under the Functional Administrator
responsibility. This interface can be used to perform administrative
operations including changing the time-out values for cache components,
looking at cache usage statistics, and clearing caches.
Here are the steps to clear all Global Cache:
1] Login to Functional Administrator responsibility – then choose Home.
2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.
3] Proceed to choose ‘Global Configuration’ from the left hand side menu.
4] In the far right choose ‘Clear all Cache’ button.
5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.
6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.
7] Proceed to test and confirm whatever change was made to the preference, profile, etc….
Please note that clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.
You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code. Select the component and clear the cache.
1] Using FNDLOAD utility:
Here first DOWNLOAD the custom alert from the source instance, copy the ldt file to the new instance and then UPLOAD to the destination instance.
Command to download:
ALR_DISTRIBUTION_LISTS
ALR_LOOKUPS
ALR_MESSAGE_SYSTEMS
ALR_ORACLE_MAIL_ACCOUNTS
ALR_PROFILE_OPTIONS
ALR_PERIODIC_SETS
Command to upload:
You can use the Transfer Alert Definitions window to transfer an alert definition to another database, or make a copy of an existing alert.
1. From the Menu, choose Tools > Transfer Alert.
2. In the Source Alert block of the Transfer Alert Definition window, enter:
* Application name associated with the alert to be transferred
* Alert name to be transferred
* Username and password for the database where the Alert to transfer resides
Include any necessary SQL*Net syntax to indicate where your database resides.
SQL*Net syntax may be used. The different databases need to be defined in the
$TNS_ADMIN/tnsnames.ora file.
Example of: username/ password@ SQL*Net_syntax
sysadmin/friday@T:testing:db2
sysadmin/friday@db2
Note: Database Links are not supported in Applications
3. Similarly, in the Destination Alert block, enter:
* Application name for the new Alert.
* Alert name for the new Alert.
* Username and password for the new database location.
4. Click Transfer button to complete the alert transfer.
NOTE: This process works for NEW Alerts that do not exist. If the Alert exists already in the Destination location, you will receive the error:
APP-ALR-04016: The alert <alert_name> already exists on the destination database account.
Reference:
Click on the ‘Run’ button to execute the Select statement.
3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.
Debugging
plays a very crucial rule when you develop something and in oracle
application also it is no different. Prior to Oracle E-Business Suite
11i, each module used in Oracle EBS had its own debugging methodology.
However, with the Common Debugging Framework initiative, Oracle has
introduced a common set of profile options and tables that can be used
to debug any application across all technologies used in oracle apps..
Starting in 11.5.10, FND has incorporated a debugging technique to enable debug messages to get stored into the table FND_LOG_MESSAGES. This method was introduced in 11.5.10 and it is available in subsequent releases.
There are few profile options to enable and retrieve the debug messages. Here are those profile options.
Sample setting to debug everything:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %
Sample setting to debug ONLY Receivables:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar%
Sample Program:
Debugging an API from SQL*Plus
You can enable FND logging for just one single PL/SQL API. Here is how we can do it from SQL*Plus:
1. From SQL*Plus, issue the following:
2. Call the desired API.
3. Call step 1 again, but this time set AFLOG_ENABLED to N.
Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows:
If you are debugging a concurrent process:
Otherwise use this:
Debugging OA pages
a. Enable the profile option: FND: Debug Log Enabled — Set to Yes
b. Enable the profile option: FND: Debug Log Level – Set to Statement level
c. Add the below piece of code in your OA page
To see log stmt on browser append below string to browser URL and click on enter
Reference MOS Notes:
Here are the steps to clear all Global Cache:
1] Login to Functional Administrator responsibility – then choose Home.
2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.
3] Proceed to choose ‘Global Configuration’ from the left hand side menu.
4] In the far right choose ‘Clear all Cache’ button.
5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.
6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.
7] Proceed to test and confirm whatever change was made to the preference, profile, etc….
Please note that clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.
You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code. Select the component and clear the cache.
API to Load Values into Value Sets
001 | DECLARE |
002 | ----------------------------Local Variables--------------------------- |
003 | l_enabled_flag VARCHAR2 (2); |
004 | l_summary_flag VARCHAR2 (2); |
005 | l_who_type FND_FLEX_LOADER_APIS.WHO_TYPE; |
006 | l_user_id NUMBER := FND_GLOBAL.USER_ID; |
007 | l_login_id NUMBER := FND_GLOBAL.LOGIN_ID; |
008 | l_value_set_name FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE; |
009 | l_value_set_value FND_FLEX_VALUES.FLEX_VALUE%TYPE; |
010 | BEGIN |
011 |
012 | l_value_set_name := 'VALUE_SET_NAME' ; |
013 | l_value_set_value := 'VALUE_SET_VALUE' ; |
014 | l_enabled_flag := 'Y' ; |
015 | l_summary_flag := 'N' ; |
016 | l_who_type.created_by := l_user_id; |
017 | l_who_type.creation_date := SYSDATE; |
018 | l_who_type.last_updated_by := l_user_id; |
019 | l_who_type.last_update_date := SYSDATE; |
020 | l_who_type.last_update_login := l_login_id; |
021 |
022 | fnd_flex_loader_apis.up_value_set_value |
023 | (p_upload_phase => 'BEGIN' , |
024 | p_upload_mode => NULL , |
025 | p_custom_mode => 'FORCE' , |
026 | p_flex_value_set_name => l_value_set_name, |
027 | p_parent_flex_value_low => NULL , |
028 | p_flex_value => l_value_set_value, |
029 | p_owner => NULL , |
030 | p_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ), |
031 | p_enabled_flag => l_enabled_flag, |
032 | p_summary_flag => l_summary_flag, |
033 | p_start_date_active => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS' ), |
034 | p_end_date_active => NULL , |
035 | p_parent_flex_value_high => NULL , |
036 | p_rollup_flex_value_set_name => NULL , |
037 | p_rollup_hierarchy_code => NULL , |
038 | p_hierarchy_level => NULL , |
039 | p_compiled_value_attributes => NULL , |
040 | p_value_category => 'VALUE_SET_NAME' , |
041 | p_attribute1 => '40912' , |
042 | p_attribute2 => NULL , |
043 | p_attribute3 => NULL , |
044 | p_attribute4 => NULL , |
045 | p_attribute5 => NULL , |
046 | p_attribute6 => NULL , |
047 | p_attribute7 => NULL , |
048 | p_attribute8 => NULL , |
049 | p_attribute9 => NULL , |
050 | p_attribute10 => NULL , |
051 | p_attribute11 => NULL , |
052 | p_attribute12 => NULL , |
053 | p_attribute13 => NULL , |
054 | p_attribute14 => NULL , |
055 | p_attribute15 => NULL , |
056 | p_attribute16 => NULL , |
057 | p_attribute17 => NULL , |
058 | p_attribute18 => NULL , |
059 | p_attribute19 => NULL , |
060 | p_attribute20 => NULL , |
061 | p_attribute21 => NULL , |
062 | p_attribute22 => NULL , |
063 | p_attribute23 => NULL , |
064 | p_attribute24 => NULL , |
065 | p_attribute25 => NULL , |
066 | p_attribute26 => NULL , |
067 | p_attribute27 => NULL , |
068 | p_attribute28 => NULL , |
069 | p_attribute29 => NULL , |
070 | p_attribute30 => NULL , |
071 | p_attribute31 => NULL , |
072 | p_attribute32 => NULL , |
073 | p_attribute33 => NULL , |
074 | p_attribute34 => NULL , |
075 | p_attribute35 => NULL , |
076 | p_attribute36 => NULL , |
077 | p_attribute37 => NULL , |
078 | p_attribute38 => NULL , |
079 | p_attribute39 => NULL , |
080 | p_attribute40 => NULL , |
081 | p_attribute41 => NULL , |
082 | p_attribute42 => NULL , |
083 | p_attribute43 => NULL , |
084 | p_attribute44 => NULL , |
085 | p_attribute45 => NULL , |
086 | p_attribute46 => NULL , |
087 | p_attribute47 => NULL , |
088 | p_attribute48 => NULL , |
089 | p_attribute49 => NULL , |
090 | P_ATTRIBUTE50 => NULL , |
091 | p_flex_value_meaning => l_value_set_value, |
092 | p_description => NULL |
093 | ); |
094 | COMMIT ; |
095 |
096 | EXCEPTION |
097 | WHEN OTHERS |
098 | THEN |
099 | DBMS_OUTPUT,PUT_LINE( 'Error is ' || SUBSTR (SQLERRM, 1, 1000)); |
100 | END ; |
How to migrate Oracle Alerts?
Here first DOWNLOAD the custom alert from the source instance, copy the ldt file to the new instance and then UPLOAD to the destination instance.
Command to download:
FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS APPLICATION_SHORT_NAME=’XXCUST’ ALERT_NAME=<Alert name to download>Here are some additional parameters that can also be passed are:
ALR_DISTRIBUTION_LISTS
ALR_LOOKUPS
ALR_MESSAGE_SYSTEMS
ALR_ORACLE_MAIL_ACCOUNTS
ALR_PROFILE_OPTIONS
ALR_PERIODIC_SETS
Command to upload:
FNDLOAD apps_user_name/apps_password 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt – CUSTOM_MODE=FORCE2] Using Alert Manager Responsibility:
You can use the Transfer Alert Definitions window to transfer an alert definition to another database, or make a copy of an existing alert.
1. From the Menu, choose Tools > Transfer Alert.
2. In the Source Alert block of the Transfer Alert Definition window, enter:
* Application name associated with the alert to be transferred
* Alert name to be transferred
* Username and password for the database where the Alert to transfer resides
Include any necessary SQL*Net syntax to indicate where your database resides.
SQL*Net syntax may be used. The different databases need to be defined in the
$TNS_ADMIN/tnsnames.ora file.
Example of: username/ password@ SQL*Net_syntax
sysadmin/friday@T:testing:db2
sysadmin/friday@db2
Note: Database Links are not supported in Applications
3. Similarly, in the Destination Alert block, enter:
* Application name for the new Alert.
* Alert name for the new Alert.
* Username and password for the new database location.
4. Click Transfer button to complete the alert transfer.
NOTE: This process works for NEW Alerts that do not exist. If the Alert exists already in the Destination location, you will receive the error:
APP-ALR-04016: The alert <alert_name> already exists on the destination database account.
Reference:
- Oracle Alert User’s Guide
- How to transfer alert definition from One Instance to another instance using cmdline script [ID 400295.1]
How to design Periodic Alert to send emails?
1] Go to ‘Alert Manager’ responsibility and navigate Alert > Define.- Enter the name of the application that will own the alert
- Enter a suitable Name of the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
- Select a frequency for your periodic alert. You can choose from nine frequency options:
- On Demand
- On Day of the Month
- On Day of the Week
- Every N Calendar Days
- Every Day
- Every Other Day
- Every N Business Days
- Every Business Day
- Every Other Business Day
- Choose ‘On Demand’ frequency when you are developing a periodic alert so that you can test your alert at any time you want. When you will sure that the alert is working fine, then you can change the frequency as per business need.
- Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled. You may also specify the number of times within a 24-hour period that Oracle Alert checks your alert.
- Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.
- Specify a value in the End Date field if you want to disable your alert by a certain date.
- Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.
- Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.
- Identify any inputs with a colon before the name, for example, :INPUT_NAME.
- Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME.
- Do not use set operators in your Select statement.
- You can use PL/SQL functions in your Select statement to fetch complex business logic.
Click on the ‘Run’ button to execute the Select statement.
Once you are satisfied with the SQL statement, save your work.
2] You can view all the input and output column details in ‘Alert
Details’ Tab. The Alert Details window includes information such as
which Application installations you want the alert to run against, what
default values you want your inputs variables to use, and what
additional characteristics you want your output variables to have.3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.
- Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.
- Select a level for your action: Detail, Summary, or No Exception.
- Detail action: performs once for each individual exception found
- Summary action: performs once for all exceptions found
- No exception action: performs when no exceptions are found.
- Select the Action Type field as ‘Message’ if you want to send emails. Other action types are: Concurrent Program, Operating System Script and SQL Script.
- Specify the electronic mail IDs of the recipients you want to send your message to in the To field.
- If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.
- You can enter as many recipients as you want, up to 240 characters.
- You can also enter alert outputs or response variables in any of the alert detail fields. Oracle Alert automatically substitutes the associated output value when checking the alert or the associated response variable value when reading the response.
- Save your changes.
- Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.
- Enter a Sequence number that lets you order the execution of action sets during an alert check.
- Give any suitable name and description.
- Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.
- Go to ‘Members’ tab.
- Find and attach the action that is created in Step 3.
- Save the changes.
- This will fire one concurrent program which you can view by going through the navigation: Request > View
- View the Log and Output files of the concurrent program to find that the alert is fired successfully.
Query to find the Request Group of a Concurrent Program
Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.01 | SELECT |
02 | RG.APPLICATION_ID "Request Group Application ID" , |
03 | RG.REQUEST_GROUP_ID "Request Group - Group ID" , |
04 | RG.REQUEST_GROUP_NAME, |
05 | RG.DESCRIPTION, |
06 | rgu.unit_application_id, |
07 | rgu.request_group_id "Request Group Unit - Group ID" , |
08 | rgu.request_unit_id,cp.concurrent_program_id, |
09 | cp.concurrent_program_name, |
10 | cpt.user_concurrent_program_name, |
11 | DECODE(rgu.request_unit_type, 'P' , 'Program' , 'S' , 'Set' ,rgu.request_unit_type) "Unit Type" |
12 | FROM |
13 | fnd_request_groups rg, |
14 | fnd_request_group_units rgu, |
15 | fnd_concurrent_programs cp, |
16 | FND_CONCURRENT_PROGRAMS_TL CPT |
17 | WHERE rg.request_group_id = rgu.request_group_id |
18 | AND rgu.request_unit_id = cp.concurrent_program_id |
19 | AND cp.concurrent_program_id = cpt.concurrent_program_id |
20 | AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’; |
Common Debugging Framework in Oracle Application
Starting in 11.5.10, FND has incorporated a debugging technique to enable debug messages to get stored into the table FND_LOG_MESSAGES. This method was introduced in 11.5.10 and it is available in subsequent releases.
There are few profile options to enable and retrieve the debug messages. Here are those profile options.
Profile Name | Suggested value | Comments |
FND: Debug Log Enabled | YES | This turns the debugging feature on |
FND: Debug Log Filename | NULL | Use when you want debug messages to get stored to a file |
FND: Debug Log Level | STATEMENT | Following are options listed from least to most detailed debugging : Unexpected, Error, Exception, Event, Procedure, Statement |
FND: Debug Log Module | % | Indicate what modules to debug. You can use something like ‘ar%’ or even ’%arp_rounding%’ to limit modules debugged |
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %
Sample setting to debug ONLY Receivables:
FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar%
Sample Program:
01 | Create or replace PACKAGE BODY xx_debug_pkg |
02 | AS |
03 | g_level_statement CONSTANT NUMBER := fnd_log.level_statement; |
04 | g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure; |
05 | g_level_event CONSTANT NUMBER := fnd_log.level_event; |
06 | g_level_exception CONSTANT NUMBER := fnd_log.level_exception; |
07 | g_level_error CONSTANT NUMBER := fnd_log.level_error; |
08 | g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected; |
09 | g_default_module CONSTANT VARCHAR2 (240) := 'Any_Package_Name' ; |
10 | g_level_log_disabled CONSTANT NUMBER := 99; |
11 | g_log_level NUMBER; |
12 | g_log_enabled BOOLEAN; |
13 |
14 | PROCEDURE DEBUG ( |
15 | p_log_level IN NUMBER, |
16 | p_module IN VARCHAR2, |
17 | p_message IN VARCHAR2, |
18 | p_request_id IN NUMBER |
19 | ) |
20 | IS |
21 | BEGIN |
22 | fnd_profile.put ( 'AFLOG_MODULE' , g_default_module); |
23 |
24 | IF ((p_module IS NULL ) OR (p_message IS NULL )) |
25 | THEN |
26 | fnd_file.put_line |
27 | (fnd_file.LOG, |
28 | 'Error in package:' |
29 | || g_default_module |
30 | || ' , module : debug :Parameters p_module and p_message cant be null' |
31 | ); |
32 | ELSE |
33 | BEGIN |
34 | IF (NVL ((fnd_profile.VALUE ( 'AFLOG_ENABLED' )), 'N' ) = 'Y' ) |
35 | THEN |
36 |
37 | IF (p_message IS NOT NULL AND p_log_level >= g_log_level) |
38 | THEN |
39 | fnd_log_repository.init; |
40 | fnd_log.STRING (p_log_level, g_default_module, |
41 | p_message); |
42 | END IF; |
43 | END IF; |
44 | EXCEPTION |
45 | WHEN OTHERS |
46 | THEN |
47 | fnd_file.put_line (fnd_file.LOG, |
48 | 'Error in package: ' |
49 | || g_default_module |
50 | || ', module : debug, at check point 1 :' |
51 | || SQLERRM |
52 | ); |
53 | END ; |
54 | END IF; |
55 | EXCEPTION |
56 | WHEN OTHERS |
57 | THEN |
58 | fnd_file.put_line (fnd_file.LOG, |
59 | 'Error in package:' |
60 | || g_default_module |
61 | || ' , module : debug, at check point 2 :' |
62 | || SQLERRM |
63 | ); |
64 | END DEBUG; |
65 | END xx_debug_pkg; |
You can enable FND logging for just one single PL/SQL API. Here is how we can do it from SQL*Plus:
1. From SQL*Plus, issue the following:
1 | fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id); |
2 | fnd_profile.put( 'AFLOG_ENABLED' , 'Y' ); |
3 | fnd_profile.put( 'AFLOG_MODULE' , '%' ); |
4 | fnd_profile.put( 'AFLOG_LEVEL' , '1' ); -- Level 1 is Statement Level |
5 | fnd_log_repository.init; |
3. Call step 1 again, but this time set AFLOG_ENABLED to N.
Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows:
1 | SELECT MAX (LOG_SEQUENCE) |
2 | FROM FND_LOG_MESSAGES; |
1 | SELECT log.module , log.message_text message |
2 | FROM fnd_log_messages log, |
3 | fnd_log_transaction_context con |
4 | WHERE con.transaction_id = < request_id > |
5 | AND con.transaction_type = 'REQUEST' |
6 | AND con.transaction_context_id = log.transaction_context_id |
7 | ORDER BY log.log_sequence; |
1 | SELECT module, message_text |
2 | FROM fnd_log_messages |
3 | WHERE log_sequence > &max_log_from_step2 |
4 | ORDER BY log_sequence; |
a. Enable the profile option: FND: Debug Log Enabled — Set to Yes
b. Enable the profile option: FND: Debug Log Level – Set to Statement level
c. Add the below piece of code in your OA page
1 | boolean isLoggingEnabled = pageContext.isLoggingEnabled(OAFwkConstants.STATEMENT); |
2 | if (isLoggingEnabled) |
3 | { |
4 | pageContext.writeDiagnostics( this , "your log statement" , OAFwkConstants.STATEMENT); |
5 | } |
1 | &aflog_level=statement |
- How to enable and retrieve FND debug log messages [ID 433199.1]
- How Can Trace and Debug Be Turned On For A Concurrent Request? [ID 759389.1]
- How to Collect an FND Diagnostics Trace (aka FND: Debug) [ID 372209.1]
- How to Obtain Debug Log in R12 [ID 787727.1]
Awesome
ReplyDelete