Create form with Cancel Query option

Set the form level property Interaction Mode to Blocking or non-Blocking.
When set to non-Blocking, a dialog box appears that contains the following prompt:

Press cancel to end this database operation

When a long running query is executed in forms, this enables the option to interrupt the query by pressing the cancel button. However, this only works for Forms blocks which are based on standard tables or views.
It does not work for blocks based on stored procedures, where the data is returned by a ref cursor or a table of records.

Enable Cancel Query option on Oracle Forms

Sometimes when we query form with higher data results the forms hangs and leaves us with option to wait until data is displayed or close it forcefully.
Using profile option FND: Enable Cancel Query, the cancel button with message "Press cancel to end this database operation" can be displayed that allows canceling query. The profile option may not support all forms but still you can have this option for most of the oracle provided forms.

Colors in Concurrent Request Manager Screen

On concurrent manager screen normally along with default form color we see Red, Yellow and Green color for Error, Warning and Pending respectively. Recently I came across a profile that would change this normal behavior and hide these colors.

The profile name is FND: Indicator Colors. The profile also affects the behavior of required parameter which is normally displayed in yellow.
Another point to note is that by setting this profile to No only concurrent manager screen is affected and other forms behave normally.

This is just for your information.

Enhancing 11i/12 Homepage Menu via Firefox and Greasemonkey

Source: Gareth Robert Blog
Here is something I found very interesting and useful and thought of sharing it with others.

In oracle E-Business suite the Oracle homepage menus is not in an organized manner and hence many times we have to make use of favourite option and for easy access save frequently used ones there.

By Default the home page is displayed as


After the enhancement the menu will be displayed as


Now lets discuss how this can be achieved.
Following things should be installed
1) Firefox
2) GreaseMonkey (Firefox Addon)
3) Install the Script

Once the Add-On is installed a Monkey icon will be displayed in status bar on the right side of firefox. Right click on that and click on Manage User Scripts and add the applications URL.

Many thanks to the Author for sharing this with us.

Wish you all a Merry Christmas and Happy new year

Increase Session Timeout Time

The session when kept inactive for certain period gets inactive and hence we need to relogin again.
It sometimes become frustating when working on a test instance and makes us login again and again. This can be avoided by setting a profile option.

Profile "ICX_SESSION_TIMEOUT" can be used to increase the session timeout time.

FNDLOAD to create value sets

Download a Value Set:

FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'

Download Value Set Values:

FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXXX'

Upload a Value Set:

FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt 

Related Post: Create Concurrent Program using FNDLOAD

FNDLOAD to create concurrent programs

The FNDLOAD can be used to create several oracle objects like Menu, Concurrent program, request sets, DFF, Responsibility, Messages, forms personaliztion etc. from one instance to another.
Here I will discuss how to create concurrent program using FNDLOADS

Download the program definition

FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME='XXXX' CONCURRENT_PROGRAM_NAME='CONC_PRG_NAME'

FNDLOAD_CONC_PRG_NAME.ldt is the file where concurrent program information is extracted. This file will be used to create same definition in any other instance.

Upload the Program definition

FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt

Note: Any value set that needs to be created along with concurrent program is downloaded and created automatically by FNDLOAD.

Date List of Value(LOV) for Concurrent Request Parameter

We are aware that it is not possible to display calendar window for concurrent request parameter. Here I am discussing on an alternative to create Date List of values.
This is addition to my earlier post Calender in Concurrent program.
Create a table value set as shown in the screenshot below.


click on Edit Information and Enter following in the table name field

(SELECT (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
           , (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word 
      FROM DUAL CONNECT BY LEVEL <= 1000)

This is how concurrent program looks when a value is attached to it.


Related Post: Calender in Concurrent program.

Basics of Value Sets in Oracle Applications

Explained below is the basics of Value Set.

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

None:

* There is no validation done for this type of value set, hence allows user to enter any value.

Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.

Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.

Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.

Special & Pair:

Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.

Translatable Independent & Translatable Dependent:

* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES

Enable/Disable Concurrent program parameter based on Other Parameter

Based on a request, here is the details on how parameter can be enabled/disabled based another parameter value.
Below is the requirement
There are 2 valuesets parameter. If for parameter 1 user selects Yes then the other paramter should be enabled whereas if No is selected then the parameter should remain disabled. This can be achieved by using a hidden parameter as explained below.

Step1: Need 3 value sets for 3 parameter.
Value set1 = BOM_SRS_YES_NO (Oracle Defined)
Value Set2 = AMS_SRS_CHAR1 (Oracle Defined)
Value Set3 = SV_DEPENDENT_VS (User Defined)




Step2: Create Concurrent program as displayed in the screenshot below
Parameter1: Main Parameter

Parameter2: Hidden PArameter

Parameter3: Dependent Parameter

Step3: Assign concurrent program to a request group and test your program.

FAQ: Common Tracing Techniques for Oracle Apps

1) How to enable trace in the Oracle Application screens / forms?
Below is the navigation to enable trace for forms
Help Menu --> Diagnostics --> Trace(Select appropriate trace as needed)

Most commonly if debugging an error, you may select trace with binds.
When debugging a performance issue, you may consider using trace with binds and waits.

Remember to disable trace as this will keep generating the file.

2) How to enable trace for a concurrent program?
Refer to post Create Trace File for Concurrent Program

3) How to enable trace for a concurrent program INCLUDING bind variables and waits?
The above method can be used to generate a trace file, but this does not includes bind variables and wait times.
Download and review the script, bde_system_event_10046.sql, from Metalink Note 179848.1.
The above script is used to turn on the trace with binds and wait(Level 12).
Run Script and press enter when prompted.
Return to application and submit your concurrent program.
The moment status changes to running, switch to SQL*PLUS and press enter again to turn tracing off.
Important: Note that the tracing is set at global level and any program that runs during time when trace is kept on will be traced. Hence it is very important to immediately turn trace off after your programs status changes to running.

4) How to enable trace for all actions that occur for a user?
Use profile option Initialization SQL Statement - Custom
Set the value at user level

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'4269824.999' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Reference: Metalink Note 296559.1

Related Post:
SQL Trace and TKPROF
Various options with TKPROF
Create Trace File for Concurrent Program

Various options with TKPROF

The post is in continuation to SQL Trace and TKPROF

Syntax of TKPROF

tkprof [filename1] [filename2] [WAITS] [SORT] [PRINT]
There are few more but these are more commonly used.

Argument Description
filename1 : The trace file name
filename2 : Filename to which TKPROF writes formatted output
WAITS : Flag to record summary for any wait events found in the trace file. Values are YES or NO
SORTS : Sorts traced SQL statements in descending order of specified sort option before listing them into the output file

PRSCNT Number of times parsed.
PRSCPU CPU time spent parsing.
PRSELA Elapsed time spent parsing.
PRSDSK Number of physical reads from disk during parse.
PRSQRY Number of consistent mode block reads during parse.
PRSCU Number of current mode block reads during parse.
PRSMIS Number of library cache misses during parse.
EXECNT Number of executes.
EXECPU CPU time spent executing.
EXEELA Elapsed time spent executing.
EXEDSK Number of physical reads from disk during execute.
EXEDSK Number of physical reads from disk during execute.
EXEQRY Number of consistent mode block reads during execute.
EXECU Number of current mode block reads during execute.
EXEROW Number of rows processed during execute.
EXEMIS Number of library cache misses during execute.
FCHCNT Number of fetches.
FCHCPU CPU time spent fetching.
FCHELA Elapsed time spent fetching.
FCHDSK Number of physical reads from disk during fetch.
FCHQRY Number of consistent mode block reads during fetch.
FCHCU Number of current mode block reads during fetch.
FCHROW Number of rows fetched.

PRINT : Number of SQL statements to be displayed

Example 1
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

tkprof abcd53269.trc abcd53269.txt SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

Example 2
Print the SQL Query taking maximum CPU Usage

tkprof abcd53269.trc abcd53269.txt SORT = (EXECPU,FCHCPU) PRINT = 1

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) --> Concurrent --> Program --> Define

Query for the concurrent program and check enable trace button.


Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.

select * from v$parameter
where name like '%user_dump_dest%'

The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Click for more info on TKPROF

Different Types of Table in Oracle Apps

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B	The Main base tables
_ALL	Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V	View created on base table. Mostly forms are created based on this views
_TL	Tables that support multi language.
_VL	View created on multi language tables. The view generally uses the base table and _tl table
_F	This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S	sequence related tables
_DFV /_KFV	The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF. Also using this table the values can be queried based on the DFF/KFF name and not attributes column.

See Parameters for the Submitted Request

A concurrent program is submitted which has many parameters. After the program is submitted I need to map the values with the parameter.
Click "Find Request" -> "View Details...", click on "Parameters" field, the parameters form will popup.




Sometimes you may feel that the forms have gone crazy and the parameters form does not pop up.This is because you are not in the correct responsibility that enable you to launch that request. The parameters form popup only from the responsibility where reports are registered.

Proposed Reverse Life Cycle

Found something interesting and thought of sharing with all of you. Hope you like it :-)

Display Numbers in Figures (words)

It happens very often that there is a requirement to display amounts in words, but in most of the forms we have amounts displayed in numbers. Here I will discuss on various options/methods to meet this.

1) Using Julian method

select to_char(to_date(11221873,'J'),'JSP') from dual  -- For upper-case letters

select to_char(to_date(11221873,'J'),'Jsp') from dual  -- For mixed-case letters

select to_char(to_date(11221873,'J'),'jsp') from dual  -- For lower-case letters 

The Julian Method is limited to display text only till million, so 9,999,999 is the maximum value that can be displayed in words.

2) Using Data and Time Suffixes. Listed is the option for Data and Time Suffix
TH - converts numbers to ordinal numbers
SP - converts numbers to words
SPTH - converts numbers to ordinal words

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
  FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'Ffsp') AS amt_in_words
  FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'ffsp') AS amt_in_words
  FROM DUAL -- For Lower Case Letters

The maximum value that can be displayed is 99,999,999. So we added another number as against the Julian function.

3) This is the best option but limited only to Oracle Application users.
Oracle Apps has provided an inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL  -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL -- For Lower Case Letters

Well as there is an end to everything, even this function has a limitation but it can display big enough as it goes upto hundred billions. 999,999,999,999 is the maximum allowed value.

Send Email through PL/SQL

The short procedure can be used to send a mail through PL/SQL.
Although there is a limitation to this as the code below does not allow you to send attachments. This can only be used to send text messages.

CREATE OR REPLACE PROCEDURE email_message (
   from_name   VARCHAR2
 , to_name     VARCHAR2
 , subject     VARCHAR2
 , MESSAGE     VARCHAR2
)
IS
   l_mailhost    VARCHAR2 (64)       := 'server.com';
   l_mail_conn   UTL_SMTP.connection;
BEGIN
   l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
   UTL_SMTP.helo (l_mail_conn, l_mailhost);
   UTL_SMTP.mail (l_mail_conn, from_name);
   UTL_SMTP.rcpt (l_mail_conn, to_name);
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn
                      ,    'Date: '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                        || CHR (13)
                       );
   UTL_SMTP.write_data (l_mail_conn, 'From: ' || from_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || subject || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || to_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, Message );

   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
END;
/

Emails with attachment can be done using Java as a wrapper. The metalink Note:120994.1 has a very good explanation on how to create a program that facilates sending emails with attachment.