Understanding the Workflow Definition (Set up) Tables

Oracle workflow is also a database application as many other application of Oracle, which means that it also utilizes database tables as the basis of its operation. Behind its very pleasant and user-friendly GUI, It’s the database tables which store every piece of information regarding the attributes, functions, process, messages you create while designing a workflow. If you really want to know Workflow and discover how it works, you have to understand its table structures.

In this article, I have covered the tables which got affected, when you create or modify a workflow process. However it doesn’t include the tables which capture information at run time when you run a workflow. I have taken the ‘PO Approval Workflow’ (POAPPRV) for example purpose.


The wf_item_types table contains one record for each item_type created. The eight character name of the item_type represents the “Internal Name” of the item. It also functions as the primary key for this table. Some key columns are:

  • NAME: It is a mandatory field. It represents the internal name of the item type.
  • PROTECT_LEVEL: Level at which the data is protected. A mandatory field.
  • CUSTOM_LEVEL: Level of user who last updated the row. Again a mandatory field.
  • WF_SELECTOR: It stores the name of the PL/SQL procedure which implements selector function. This is an optional field.
  • PERSISTENCE_TYPE: Indicates whether item type is temporary or permanent.
  • PERSISTENCE_DAYS: Number of days until purge if persistence is temporary.

Workflow Item Type Display Name and description can be found in WF_ITEM_TYPES _TL table. Also check the view WF_ITEM_TYPES_VL.




This table stores definitions of attributes associated with a process. The entries in this table correspond to the “Attributes” subheading in the Workflow Builder. An item attribute works like a variable which can hold values that are specific to the process instance or which may change at run time. Some key columns are:

  • ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.
  • NAME: Internal name of the attribute. A mandatory field.
  • SEQUENCE: Order of the attribute within the message
  • TYPE: Each item attribute is assigned a datatype, such as “Character”, “Number”, or “Date”.

There are three fields to hold a default value, but only one of them will be populated for any item attribute, depending upon the datatype. For example, if you create an item attribute with a datatype of “Number”, and then supply a default value, that value would be stored in the “number_default” field.

The “format” field stores information about a format mask that should be applied to number or date values, and the “subtype” field contains “SEND” or “RECEIVE”. The Translation table is WF_ITEM_ATTRIBUTES_TL and the related view is WF_ITEM_ATTRIBUTES_VL.




This table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub-process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, but it provides a means of grouping activities. Some key columns are:

  • ITEM_TYPE: Internal name for the Item Type that owns the message.
  • NAME: Internal name for the activity.
  • VERSION: It is used to support multiple versions of the same process running at the same time. The version number works in concert with the “begin_date” and “end_date” fields, to ensure that only one version of any activity is active at any given time. By versioning, the previously launched processes retain the process definition that was in force at the time they were launched.
  • TYPE: The “type” field is the way that the individual types of activities can be distinguished. There are five valid values found in the “type” field: “FUNCTION”, “NOTICE”, “EVENT”, “PROCESS”, and “FOLDER”.
  • RERUN: Determines if activity is rerun during looping.
  • EXPAND_ROLE: Determines how many roles are required to respond to a notification activity.
  • FUNCTION: For function activities only, the field is used to store the name of the PLSQL procedure that the Workflow Engine should call to implement the function.
  • RESULT_TYPE: If you intend to model transitions in a process based upon values returned by an activity node, then the expected results must be predefined by supplying a lookup type, which is stored in this field.
  • ICON_NAME: Name of activity icon used in process window.
  • MESSAGE: For notification activities only, the field called “message” will be populated. In these cases, it will contain the internal name of the message that the notification will deliver.
  • ERROR_PROCESS: Workflow process to run in case of an error.
  • ERROR_ITEM_TYPE: Name of item type to execute in case of error.
  • RUNNABLE_FLAG: Flag (Y or N) to indicate if activity is runnable.
  • FUNCTION_TYPE: Indicates whether function type is pl/sql or internal.

The Translation table is WF_ACTIVITIES_TL and the related view is WF_ACTIVITIES_VL.





This table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities. Each row includes the associated activity, type of attribute, and the format used by the activity. Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2. Notice that the table requires three fields just to identify to which activity the attribute is attached: the item_type, name, and version of the activity. To join this table to the wf_activities tables you must join all three of these fields to their corresponding fields in that table. Some key columns are:

  • ACTIVITY_ITEM_TYPE: Item type the activity is associated with
  • ACTIVITY_NAME: Internal name of the activity
  • ACTIVITY_VERSION: Version of the activity
  • NAME: Internal name of the attribute
  • SEQUENCE: Order of the attribute within the message
  • TYPE: This field refers to the datatype of the values that the attribute will contain.
  • VALUE_TYPE: Defines if the default is a constant or a reference to an item attribute.

The Translation table is WF_ACTIVITY_ATTRIBUTES_TL and the related view is WF_ACTIVITY_ATTRIBUTES_VL.



This table used to track values contained in activity attributes. This table is identical in purpose to wf_item_attribute_values except it holds values for activity attributes instead of item attributes. Each row includes the process activity id and the associated value for the attribute. The interesting thing about this table is that it uses the process_activity_id to identify the activity to which the attribute is attached. The same activity can be inserted into a process more than one time, so the only way to uniquely identify the node to which this attribute is attached is to use the process_activity_id.



The messages that are associated with notifications are stored in this table. Each message, which is uniquely identified by the combination of item_type and message_name (stored in the fields “type” and “name”) receives a single record in the wf_messages table. The actual text of the message is stored only in its localization table (wf_messages_tl). They can found in the “body” and “html_body” fields.




This table contains message attribute definitions. Each message may have zero or more message attributes. Message attributes define additional information that is to be sent to, or received from the user. These attributes can be used as tokens in the subject or body of a message template to place variables values into the message at runtime.



A process is a sequence of activities performed in a pre-determined order. When you create a process definition in the Workflow Builder by dragging various notifications and functions into the process window, the records created by the Builder are stored into this table.



The flow of a process from node to node as indicated by the transition arrows is not saved in the wf_process_activities table. Instead this information is stored in this table.

A transition is defined by three discrete pieces of information: the node where the arrow begins, the node toward which the arrow points, and the result which, when returned by the beginning node, causes the transition to be followed. Not surprisingly, it is those three fields which are the most important fields in this table: “from_process_activity”, “to_process_activity”, and “result_code”. The values stored in “from_process_activity” and “to_process_activity” are numbers which represent the instance_id of the records from wf_process_activities from which and to which the transition is moving.



Wf_lookup_types_tl is the table used to set up the types of results expected from Workflow activities like functions and notifications. This table does not contain the actual result values, it holds the groupings of the result_codes – the names you see in the Workflow Builder as the names of the Lookups. Wf_lookups_tl is the table that stores the component values that comprise a lookup_type.



Happy New Year & 2011 in blogging!

Wish you all a very happy new year……

Here is what WordPress says about this Blog. Just thought of sharing with you all.

A Visit to Aga Khan Palace, Pune

Couple of days back, I visited the Aga Khan Palace, which is located at the very heart of the pune city. It is a national monument of India’s freedom movement. Following the launch of Quit India movement in 1942, Gandhiji, his wife Kasturba, his secretary Mahadevbhai Desai were interned at the palace from August 9, 1942 to May 6, 1944. Mahadevbhai and Kasturba passed away while in captivity at the Agakhan palace and their samadhis are located in the campus. Situated near the River Mula, the palace is a simple memorial to Gandhi and his life.

Here are some nice pictures of the palace, which is so well maintained and covered by beautiful green trees all around.

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:
  1. On Demand
  2. On Day of the Month
  3. On Day of the Week
  4. Every N Calendar Days
  5. Every Day
  6. Every Other Day
  7. Every N Business Days
  8. Every Business Day
  9. 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 ‘Verify’ button to check the select statement is correct.

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.
  1. Detail action: performs once for each individual exception found
  2. Summary action: performs once for all exceptions found
  3. No exception action: performs when no exceptions are found.

4] Click on ‘Action Details’ tab to display the Action Details window.

  • 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.

5] Click on ‘Action Sets’ tab in the main Alert Window.

  • 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.

6] Click on ‘Action Set Details’ tab.

  • Go to ‘Members’ tab.
  • Find and attach the action that is created in Step 3.
  • Save the changes. 

7] Since it is an ‘On Demand’ periodic alert, we can run the alert at any time we want. For that go to Request > Check and enter the alert details. Then click on ‘Submit Request’.

  • 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.

Done…check your mailbox and you should get emails that are sent from Oracle Alerts.

How to customize a LOV in OAF (VO Extension)?

OA Framework provides robust support for personalizing and extending the E-Business Suite user interface and underlying business logic. The capabilities are largely achieved by leveraging the OA Framework’s declarative architecture, and the object-oriented features of Java.

This post describes how to customize a LOV in an OAF Page and also make the LOV dependent on a parent LOV. It can be done through a VO Extension and Personalization in R12. View Objects are the queries that provide the data seen on a Self Service web page. Oracle offers many attributes in each View Object, but often an additional attribute is needed. Oracle allows developers to extend the delivered View Objects to meet this need. 

The following iProcurement Requisition Page will be used for demonstration, but any page developed using the OAF architecture is a candidate for this exercise.

Here we are about to customize the Expenditure Type LOV so that when creating an iProcurement requisition and entering project related information, the Expenditure Types list of values will present the user with a subset of expenditure types, instead of all expenditure types.


1] Check ‘About this page’:

To find all dependent objects of the base Oracle Application page you wish to extend, use the About this page link shown at the bottom-left corner of the base page. The link renders the About page which displays page definition, current session, and technology stack information for that base page. (If you do not see the About this page link, verify that the FND_DIAGNOSTICS profile option is enabled.)

2] Find the VO Object Name:

3] Fnd the SQL query behind the VO:


Now that we have identified the VO we need to extend, ExpenditureTypeNoAwardLovVO. we need to setup our JDeveloper environment so that we can create a new View Object that contains our extended code.

1] Setup Jdeveloper:

You can refer the below articles for this:

Initial Setup in JDeveloper for OAF Development

2] Create a New OA Workspace:

In the Applications Navigator right click the Applications Node and select “New OA Workspace”.

Give a suitable name. Leave the directory path as your default myprojects folder in your JDev Home, ensure that the “Add a New OA Project” check box is selected. Select “OK”. 

3] Create a New Project:

Give a suitable project name. Leave the default directory as your myprojects folder in the JDev Home. For a VO substitution the default package name will need to represent the VO file path on JAVA_TOP with the exception that it must be prefixed with your custom application shortname, the prefix can actually be anything you like however it is recommended that the custom application shortname is used.

Click “Next”  and complete the remaining steps.

4] Copy the required files from JAVA_TOP:

Next we need to copy the existing VO component from the apps server and import it into our local file system. In order to do this we must setup a file structure on our local machine in our project folder so we can copy the VO into it. Log onto the applications server and navigate to $JAVA_TOP, change into the oracle/apps/icx/ directory (Or which ever product top you are working with).

For that do the following:

  1. cd $JAVA_TOP/oracle/apps
  2. tar -cvf icx_top.tar icx .. repeat for as many modules as you might need.
  3. FTP each .tar file to \jdevhome\jdev\myclasses.
  4. Extract each .tar file in the \jdevhome\jdev\myclasses directory
  5. Also extract each .tar file in the \jdevhome\jdev\myprojects directory.

Click the “Refresh” button on the Applications Navigator tab of JDeveloper, you should notice that a new business components package under oracle.apps.pa has now appeared. 

5] Create a new VO Object:
Now that we have the existing projects business components in our environment we need to create our new VO object that will be used in place of the existing one.
Please note- We do not customize existing components in OA Framework. We extend the existing ones which means creating a new object that extends the original and we notify the framework that we want to use the new object rather than the seeded one. 
5.1] Test the seeded VO:
We now need to test that the VO we want to substitute does not contain any java errors following the download. Navigate to the VO in the applications navigator window right click the VO and click edit. 
If the VO opens without error then we are ready to move onto the next step. If you get an error message saying that java errors exist in either the voNameImpl.java file or the voNameRowImpl.java file then do the following:
  1. Get the .class files.
  2. Download a java deconpiler (ex:’CAVAJ’) and decompile the files to .java files and put it in myprojects folder.
  3. Remake the project.
5.2] Lunch the create new VO Object wizard:

Right click on the project node and select “New”. Select “View Object” under Business Tier > ADF Business Components and click “OK”.

5.3] Give the details:

Specify the package as xxscm.oracle.apps.icx.lov.server (This component package will hold our extended object so we need the custom application prefix). Specify the VO name as the name of the custom application concatenated with the orginal VO name i.e. XxScmExpenditureTypeNoAwardLovVO. Select the original VO in the “extends” box using the browse button and select the original VO i.e. oracle.apps.icx.lov.server.ExpenditureTypeNoAwardLovVO.

5.4] Modify the SQL Query:
On step 2 of the VO wizard you can see the SQL statement that we first saw when we were analysing the page. Here add your modified query.Please note that you should add your additional columns at the end.
In our example the initial query was:
SELECT et.expenditure_type,
  1 AS dummy_number
FROM pa_expenditure_types_expend_v et
WHERE et.system_linkage_function = 'VI'
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_active, TRUNC(sysdate  +1)))

Now the modified query is:

1 AS dummy_number,
AND (TRUNC(sysdate) BETWEEN et.sys_link_start_date_active AND NVL(et.sys_link_end_date_active, TRUNC(sysdate  +1)))

5.5] Generate the Java files:

Once we have clicked through to the end of the wizard the final step is to create the relevant java files.

Now click “Finish” and the new business components package will be created containing our new extended VO. Right click on the custom business components package and select “Make”, this will compile the Impl and RowImpl java files. 

6] Create a Substitution:

Now that we have our newly extended VO, we need to create a substitution file. This substitution file will be an xml based file that will contain a mapping from the old VO to the new VO, we will use this file to tell Oracle Application to use our newly extended VO rather than the old one by uploading it to the MDS repository using the JPX Import tool. 

Right click on your project node and select “Project Properties”, click “Substitutions” under the “Business Components” menu. In the “Available” pane select the original VO , and in the right hand “Substitute” pane select the new VO. Once you have done this click the “Add” button and select “OK”. 

In your JDev home in the myprojects folder you will see a file called projectName.jpx. This is your substitution file and we will use this later when we deploy the substitution.


1] Deploy the the relevant java and xml files to java top:

Right click on the project node and select “New”, select “Jar File” under the General > Deployment Profiles menu. Name the profile as something meaningful. Leave the directory set as the default myprojects folder and click “OK”. 

In the deployment profile properties deselect the “Include Manifest File” option. On the filters menu deselect the root folder and navigate to the xxscm.oracle.apps.icx.lov.server directory, select all the files in this directory and click “OK”.

You will see in JDeveloper that you now have a deployment profile listed under you application sources node, right click the .deploy file and select “Deploy to JAR file”, You will see a deploy tab appear next to the compile log and this will confirm that the deployment was successful. If you experience issues with compilation i.e. there are issues with some of the files in your project that are not connected to your custom files then simply remove them from the project by selecting the top level component package i.e. oracle.app.pa and click the “Exclude Project Content” button (Little file symbol with a red cross on it) and re-try the deployment. 

Inspect your myprojects folder and you will see you now have a “Deploy” directory, in this directory will be your jar file ready for deployment to the apps server. FTP the jar file in binary format to a convienient directory on the apps server. Then run the below command:

jar -xvf /$JAVA_TOP/XxScmRestrictExpTypes_Deploy.jar

2] Deploy the Substitution file:

You can run a script something like below to deploy the substitution file:

echo  "--------------------------------------------"
echo  "Please enter the following details :"
echo  "--------------------------------------------"
echo Enter APPS Password :
read apps_pw

echo "Enter Host TNS DATABASE Server name:>"
read v_host

echo "Enter Host Database TNS Port Number:"
read v_port

echo "Enter Host Database SID name:"
read v_sid

echo "Importing VO Extension..."

java oracle.jrad.tools.xml.importer.JPXImporter \
$XXSCM_TOP/install/XxScmRestrictExpTypes.jpx -username apps -password $apps_pw \
-dbconnection "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=$v_host)(PORT=$v_port)) (CONNECT_DATA= (SID=$v_sid) ) )"

echo "Importing VO Extension successful!!!!..."

Finally restart the webserver (bounce Apache) so that you can see your changes in the application.

Creating Personalizations:

1] Add a new attribute:

Go to ‘Functional Administrator’ responsibility and go to the path ‘/oracle/apps/icx/lov/webui/ExpenditureTypeLovRN’ 

Create a new Item and Give the below details:

Item Style Message Styled Text
Id XxScmProjectId
Datatype Number
Rendered False
View Attribute ProjectId
View Instance ExpenditureTypeNoAwardLovVO

2] Add a LOV Mapping to make the LOV dependent ( Here Expenditure Type LOV is dependent to the Projects LOV):

Go to ‘Functional Administrator’ responsibility and go to the path ‘/oracle/apps/icx/por/req/webui/CheckoutSummaryPG’

Create a new Item and Give the below details:

Item Style Lov Map
Id XxScmProjectIdLovMap
Criteria Item ProjectIdExpense
* LOV Region Item XxScmProjectId

Great!!…Its over now. Go to iProcurement Requisition Page and check the new Expenditure Type LOV.

Reference Article: oaf-modifying-sql-behind-framework-pages (keithturley.wordpress.com)

Beautiful flowers of Assam!

Assam is a land where one can see unseen beauty of nature. You can find beautiful flowers in every corner of Assam. Here is a small collection collected by me during my recent visit to my native.

© Copyright for all images remains with the photographer.

Please DO NOT distribute, copy, publish or use the images or any part of the images in any way without express permission of the copyright holder.

Capturing the drops of monsoon!

These photos are captured during my visit to Assam…..

© Copyright for all images remains with the photographer.

Please DO NOT distribute, copy, publish or use the images or any part of the images in any way without express permission of the copyright holder.

Advantages of using the XML Publisher Report

Advantages of using the XML Publisher Report


Choose Your Design Tool:

Xml Publisher Report formats can be designed using Microsoft Word or Adobe Acrobat – tools most users are already familiar with. There is no proprietary design studio component required, meaning no extra cost and no extra learning curve.

Data from Anywhere:

Xml Publisher can accept and format any well-formed Xml data, as well as generate the Data. It can therefore be integrated with any database. Xml Publisher also allows you to bring data In from Multiple Data Sources into a Single Output Document.


Deliver To Anywhere:

Deliver your content via Printer, E-Mail, Fax, Webdav, or publish your report to a portal. The Delivery Manager’s open architecture allows for easy implementation of custom delivery channels, as well.

Communicate With The World:

Report Layouts can be created for any combination of 185 languages and 244 territories. These Layouts are converted to Industry-Standard files for easy In-House translation or delivery to third party translation providers. Because each translation is a separate file, modifications can still be made to the Layout without impacting translations. Xml Publisher also offers Advanced Bidirectional and Font Handling Support.

Unsurpassed Performance:

Xml Publisher is based on the W3c Xsl-Fo Standard and it is the Fastest, Most Scalable Implementation in the world today. It can handle very large data inputs and generate output in less time, using very low levels of CPU time and memory.

Open Standards. Easy Integration:

Xml Publisher is built on Open Standard technologies. It is a J2ee application that can be deployed to any J2ee container. Data is handled as Xml and the Layout Templates created in the desktop applications are internally converted to Xsl-Fo, another W3c standard. Outputs generated by the application are also industry standards such as Pdf, Rtf and Html. The delivery protocols are Internet Printing Protocol (Ipp), Webdav, Ftp, As2.

Advanced Table Functionalities in OAF!

1] Add A Row:

To add an ‘Add a Row’ button, in the advanced table, go to

Advanced Table > Advanced Table Components > Footer > Table Footer > New > Add A Row.

In the Structure pane, select the addTableRow item that is newly created, as shown in the figure above, and use the Property Inspector to set its following properties (* Required):

ID* – specify an identifier that uniquely identifies the addTableRow item in the page.

Add Rows Label – specify text to override the “Add Another Row” default text that appears in the Add Another Row button.

Rows to Add – specify the number of rows to add each time a user chooses the Add Another Row button. The value for this property must not be greater than the value set for the Records Displayed property on the advanced table. The default is 1. Note that this property is valid only if the Insert Rows Automatically property is set to True.

Insert Rows Automatically – specify True to indicate rows are to be added automatically when the Add Another Row button is chosen. Specify False if you want to respond to the event in your controller, so that you can, for example, set the primary keys or set some default row attributes. Note: The Insert Rows Automatically property is equivalent to the ‘autoInsertion’ property that was set in your controller using the setAutoInsertion API for classic tables.

Add a Row in OAF Advanced Table

If you want to handle the Add a Row Event in the Controller, First set Insert Rows Automatically to False and then in controller, processFormRequest method, call your AM method when user click on the ‘Add a Row’ in your advanced table – ‘AdvTblRN’.

if  ("addRows".equals(pageContext.getParameter("event")) &&
"AdvTblRN".equals(pageContext.getParameter("source")) )
  System.out.println("The source is :"+pageContext.getParameter("source"));
  String p_header_id = (String)pageContext.getTransactionValue("p_tx_header_id");
  Serializable[] param = {p_header_id};
  am.invokeMethod("createRow", param);

In AMImpl wrote the method:

public void createRow( String p_header_id)
  OAViewObject pervo = getPriceLinesVO1();    // Advanced Table VO
  pervo.last();   // Go to the last Row of the VO
  String line_number =  pervo.getCurrentRow().getAttribute("LineNumber").toString();
  String description =  pervo.getCurrentRow().getAttribute("Description").toString();
  Number line_num = new Number(Integer.parseInt(line_number) + 1);
  Row row = pervo.createRow(); // Create a New Row
  // Insert the values in the row.
  Number header_id = new Number(Integer.parseInt(p_header_id));
  pervo.insertRow(row);  // Insert the row in DB
  System.out.println("Commit Done");

2] Delete Multiple Rows:

Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.

To add a Delete button, in the advanced table, go to

Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.

In the MultipleSelection, add the view attribute defined above.

Multiple delete in OAF Advanced Table

In the controller, add logic as below:

if (pageContext.getParameter("DelBtn") != null)
  System.out.println("In Delete Button");
  System.out.println("The source is:"+pageContext.getParameter("source"));
  OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
  Row[] row=linesVO.getFilteredRows("SelectFlag","Y");
  System.out.println("Total Rows: "+linesVO.getRowCount());
  System.out.println("Selected Rows: "+row.length);
  Row row[] = linesVO.getAllRowsInRange();
  for (int i=0;i<row.length;i++)
       PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
       if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))

3] Update Multiple Rows:

Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.

To add a Update button, in the advanced table, go to

Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.

In the MultipleSelection, add the view attribute defined above.

In the controller, add logic as below:

if (pageContext.getParameter("UpdateBtn") != null)
  System.out.println("In Update Button");
  Number user_id = new Number(am.getOADBTransaction().getUserId());
  String updated_by = user_id.toString();
  OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
  Row row[] = linesVO.getAllRowsInRange();
  for (int i=0;i<row.length;i++)
      PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
      if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))
          String Bucket_low = rowi.getAttribute("BucketLow").toString();
          String Bucket_high = rowi.getAttribute("BucketHigh").toString();
          Number Price = (Number) rowi.getAttribute("Price");
          Serializable[] param = {rowi.getAttribute("LineId").toString(),rowi.getAttribute("BucketLow").toString(),rowi.getAttribute("BucketHigh").toString(),rowi.getAttribute("Price").toString(),updated_by};
  OAViewObject volns = (OAViewObject)am.findViewObject("PriceLinesVO1");

In AM method, you can call PLSQL Procedure to update the data.

public void UpdateLinesRecord(String LineId, String BucketLow, String BucketHigh, String Price, String updated_by)
try {
     CallableStatement cs=this.getOADBTransaction().
     getJdbcConnection().prepareCall("{call APPS.XXTEST_LINES_PKG.UPDATE_LINES(?,?,?,?,?)}");
catch(SQLException e) {
System.out.println("the exception is"+e);

4] Delete or Update Single Row at one time.

If you want to add update and delete icons per rows do the followings:

  1. Create Columns in the advanced table say ‘Delete’ and ‘Update’.
  2. In the Delete Column, add an image item (deleteicon_enabled.gif). For all image names go to $OA_MEDIA folder.
  3. Set Action type to fire action.
  4. Give any name to Event (Say – ‘deleteLine’)
  5. In the parameter, pass the identifier of the row which user wants to delete. You can use SPEL for that.

Example>> p_header_id: ${oa.QotDtlVO1.HeaderId}

In the controller, handle the event – ‘deleteLine’.

if ("deleteLine".equals(pageContext.getParameter(EVENT_PARAM)))
//Handle delete logic here. You can call AM methods which eventually calls PLSQL Procedures to delete the data.

Similarly you can add image link for update also.

Do not forget to reset the VO after operation to reflect the new data.

Naming Standards of Commonly Used OAF Components

1] Naming Standards for a Custom Package:

If you want to create new pages, business logic, or whole applications and deploy your code with existing Oracle E-Business Suite applications, you must define a new application and use its short name for any related package and seed data definitions. For example, if you want to create a new, custom Procurement application, you might create a product with the short name XXPO (the “XX” prefix ensures that your selected product short name never conflicts with any future Oracle E-Business Suite product names).

Any objects that you create — either by extending (subclassing) existing Oracle E-Business Suite objects or by creating new objects from scratch — must be added to a package that starts with your company’s identifier:


  • If you are creating new objects from scratch, you should put them in a package that also includes your custom application short name as follows:


For example, assuming your company is called ABC Corporation and your custom product short code is XXPO, any new classes and OA Components that you create should be added to the following package:


  • If you are extending existing Oracle-Business Suite objects, you may add your files to a package that corresponds directly to the original Oracle package (in this case, you don’t need to add your files to a package including a custom application short code).

2] Naming Standards for a Page (File Extension: .xml):

The page name should convey the object it presents (an employee, a supplier, an item, a purchase order, an applicant, and so on), and the function being performed (search, promote, hire, approve, view). For some pages, the object is sufficient.

<Object><Function>PG or <Object>PG


  • SuppliersPG.xml (Supplier update)
  • SupplierCreatePG.xml (differentiated only if update and create are separate tasks)
  • SupplierViewPG.xml (view supplier info)
  • SupplierSearchPG.xml (search for supplier)
  • SupplierHomePG.xml (Supplier home page)

3] Naming Standards for a Region (File Extension: .xml):

The region name should convey the object it presents (an employee, a supplier, an item, a purchase order, an applicant, and so on), and the function being performed or the structure (search, promote, hire, approve, view, table, HGrid, Tree and so on).

<Object><Function-Structure>RN or <Object>RN


  • SupplierDetailsRN.xml
  • PoApproveRN.xml
  • CustomerContactsRN.xml
  • ItemTypeHGridRN.xml 

4] Naming Standards for an Entity Object (File Extension: .xml, .java):

The EO should be named for the objects stored in its underlying entity. For example, the entity FWK_TBX_PO_HEADERS stores purchase order headers, so the associated EO name is PurchaseOrderHeaderEO.



  • EmployeeEO.xml
  • EmployeeEOImpl.java
  • SupplierEO.xml
  • SupplierEOImpl.java
  • SupplierSiteEO.xml
  • SupplierSiteEOImpl.java
  • PurchaseOrderHeaderEO.xml
  • PurchaseOrderHeaderEOImpl.java
  • PurchaseOrderLineEO.xml
  • PurchaseOrderLineEOImpl.java

5] Naming Standards for an Entity Association Object (File Extension: .xml):

The AO name should convey the relationship between a parent and its child entities.



  • PoHeaderToLinesAO.xml
  • SupplierToSitesAO.xml
  • EmployeeToContactsAO.xml

6] Naming Standards for a View Object (File Extension: .xml, .java):

The VO name should convey the nature of the query. VO names are always plural as they model a collection of rows.



  • AllEmployeesVO.xml
  • AllEmployeesVOImpl.java
  • AllEmployeesVORowImpl.java
  • NewCustomersVO.xml
  • NewCustomersVOImpl.java
  • NewCustomersVORowImpl.java

7] Naming Standards for a View Link (File Extension: .xml):

The VL name should convey the relationship between the master and detail VOs.



  • EmployeeToDepartmenstVL.xml
  • PoHeaderToLinesVL.xml
  • ItemToApprovedSuppliersVL.xml

8] Naming Standards for an Application Module (File Extension: .xml, .java):

The AM name should convey the purpose of the UI module it services.



  • EmployeesAM.xml
  • EmployeesAM.java (optional interface)
  • EmployeesAMImpl.java
  • ItemCatalogAM.xml
  • ItemCatalogAMImpl.java
  • PoSummaryAM.xml
  • PoSummaryAMImpl.java
  • ApprovedSupplierListAM.xml
  • ApprovedSupplierListAMImpl.java

WFLOAD Command for Oracle Workflow Migration

We use the Workflow Definitions Loader to save or load process definitions from a database or flat file. We can also define as it is a utility that moves workflow data between a file and a database and it is also used to upgrade, upload and download the workflow data.

Following is the syntax and the parameter details of WFLOAD.

WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]


WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXSCM_TOP/install/POAPPRV.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE Honors both protection and customization levels of data
UPLOAD Honors only protection level of data [No respect of Customization Level]
FORCE Force upload regardless of protection or customization level

Use the following command to download workflow definition:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>


WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD POAPPRV.wft

As a standard practice, Oracle’s upgrade patches mostly run in UPGRADE mode, hence your customizations can protected if best practices for development are followed.

One can find the seeded workflow definition files under$<PRODUCT_TOP>/patch/115/import/<LANG> directory.

For example, .wft files related to Purchasing can be located in the following directory:-

Oracle Workflow Access Protection: A Technical Note

Oracle Workflow assigns a protection and customization level to every workflow object definition stored in the database and requires every user of Oracle Workflow to operate at a certain access level.

The combination of protection, customization, and access levels build the access protection feature and determine whether a user can modify a given workflow object. The level, in all three cases, is a numeric value ranging from 0 to 1000 that indicates the relationship between different organizations as providers and consumers of seed data.

The following ranges of levels are there in Oracle Workflow:

0-9 Oracle Workflow
10-19 Oracle Application Object Library
20-99 Oracle Applications development
100-999 Customer organization. You can determine how you want this range to be interpreted. For example, 100 can represent headquarters, while 101 can represent a regional office, and so on.
1000 Public

Access Level:

Each user of Oracle Workflow operates the system at a certain access level according to the range of levels listed above. A “user of Oracle Workflow” in this case, represents someone who is operating Oracle Workflow Builder, or the Workflow Definitions Loader program, which loads workflow process definitions from a file into a database.

Navigation to view your access level:

Oracle Workflow Builder > Help > About Workflow

Workflow Access Level

Note: If you are going to run the Workflow Definitions Loader program to download workflow process definitions from the database to a file, check the value for the environment variable WF_ACCESS_LEVEL on your workflow server.

The Access tab lets you define whether:

  • Future customizations to the object are preserved during a workflow definition upgrade.
  • The object can be edited by users operating at a higher access level.

Protection Level:

Whenever you create a workflow object in Oracle Workflow Builder, you have the option of protecting the object at a certain level. An object’s protection level helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or lower than the object’s protection level to modify the object.

Navigation to set the protection level of an object:

Oracle Workflow Builder > object’s property page > Access tab


  • If you protect an object against customization, you effectively assign the object a protection level equal to your current access level.
  • Objects protected against customizations are considered “seed data.”
  • Only users operating at an access level equal to or lower than the protection level of the object can modify the object.
  • Users operating at an access level greater than the protection level of the object will see a small lock on the icon for the object in the navigator tree, indicating that the object is read-only.

Customization Level:

An object’s customization level helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or higher than the object’s customization level to modify the object.

Navigation to set the customization level of an object:

Oracle Workflow Builder > object’s property page > Access tab

Setting the customization level ensures that a customizable object that has been customized never gets overwritten during a seed data upgrade, because the upgrade always occurs with the Workflow Definitions Loader operating at an access level below the customized object’s customization level.

Use the Options region to set the protection and customization levels of an object.

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized by higher access levels

Preserve Customizations: Prevents customized objects from being overwritten during a workflow definition upgrade.

Access Level
Lock at this Access Level
Preserve Customization
Level Summary
1 Set as 100



A: 100, P: 1000, C: 0
2 Set as 100



A: 100, P: 100, C: 0
3 Set as 100



A: 100, P: 1000, C: 100
4 Set as 100



A: 100, P: 100, C: 100

The indicator bar provides a visual range of access levels that can edit the object:

Black vertical line: Current access level

White range: Can’t edit the object

Solid green: Can edit the object

Cross-hatch green: Usually cannot modify the object because it has been customized, but can now do so because Oracle Workflow Builder is set to upload mode so that customized objects can be modified.

1] Allow access to everyone

Workflow Protection Level-1

None: Object can be updated at any time by any access level.

2] Limit access to users with access levels equal to your own or lower

Workflow Protection Level-2

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized, except by access levels 0-100.

3] Limit access to users with access levels equal to your own or higher

Workflow Protection Level-3

Preserve Customizations: Disallows customized objects from being overwritten during a workflow upgrade.

  • Object may be updated by access levels 100-1000.
  • If the Allow modifications of customized objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

4] Limit access to users with access levels equal to your own

Workflow Protection Level-4

Both: Object can only be updated by the access level at which the object is protected.

  • Object may only be updated by access level 100.
  • If the Allow Modifications of Customized Objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

Oracle E-Business Suite customers should select both the Preserve Customizations and Lock at this Access Level check boxes to protect your workflow objects during upgrades.

During an Oracle Workflow seed data upgrade, the Workflow Definitions Loader is always run in Upgrade mode at an access level less than 100. As a result, the upgrade will not overwrite any object with a customization level of 100 or higher.




Create XML Publisher Report using Data Templates

The data templates are useful when you need to create a XML Publisher report without using the RDF. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.

The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML.

The Data Template Definition:

The data template is an XML document that consists of four basic sections:

 Sections of Data Templates

  • define parameters,
  • define triggers,
  • define data query,
  • define data structure

 Here is a sample data template:

Sample Data Template

1] Parameters Section:

A parameter is a variable whose value you can set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. However, the Parameters section of the data template is optional.

How to Define Parameters:

<parameter name="P_PERIOD_FROM" dataType="character" />
<parameter name="P_PERIOD_TO" dataType="character" />

How to Pass Parameters:

To pass parameters, (for example, to restrict the query), use bind variables in your query. For example:


FROM apps.gl_balances glb


2] Data Query Section:

The <dataQuery> section of the data template is required.

How to Define SQL Queries

The <sqlStatement> element is placed between the open and close dataQuery tags. The <sqlStatement> element has a related attribute, name. It is expressed within the <sqlStatment> tag. The query is entered in the CDATA section.

<sqlStatement name="Q1">
FROM apps.gl_balances glb

Lexical References:

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.

Create a lexical reference using the following syntax:


Define the lexical parameters as follows:

• Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.

• Create your query containing lexical references.

<sqlStatement name="Q_Lexical">
<![CDATA[ &p_query ]]>

<dataTrigger name="beforeReport" source="xxfin_test_pkg.beforeReport" />

In the function xxfin_test_pkg.beforeReport, you can design your query at runtime as below:

p_query VARCHAR2(2000) :=

‘SELECT * apps.gl_balances glb


How to Define a Data Link between Queries:

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.

The following example shows a query link using a bind variable:

<sqlStatement name="Q1">

<sqlStatement name="Q2">

<link name="TEST_LINK" parentQuery="Q1" parentColumn="PARENT_COL"
childQuery="Q2" childColumn="CHILD_COL"/>

The <link> element has a set of attributes. Use these attributes to specify the required link information. You can specify any number of links.

3] Using Data Triggers:

Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.

Data triggers are optional, and you can have as many <dataTrigger> elements as necessary. The <dataTrigger> element has a set of related attributes. These are expressed within the <dataTrigger> tag.

For example:

<dataTrigger name="beforeReport1" source=" xxfin_test_pkg.beforeReport()"/>
<dataTrigger name="beforeReport2" source=" xxfin_test_pkg.beforeReport(:Parameter)"/>
  • Name: The event name to fire this trigger.
  • Source: The PL/SQL <package name>.<function name> where the executable code resides.

4] Data Structure Section:

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups.

Sample Data Structure:

<group name="GROUP_1" source="Q1">
  <element name="LEDGER_ID" value="LEDGER_ID" />
  <element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME" />
  <element name="LEDGER_NAME" value="LEDGER_NAME" />
  <element name="LEDGER_SUM_BAL_DR" value="ACCT_SUM_BAL_DR" function="SUM()" />
  <element name="LEDGER_SUM_BAL_CR" value="ACCT_SUM_BAL_CR" function="SUM()" />
<group name=" GROUP_2" source="Q1">
  <element name="CODE_COMBINATION_ID" value="CODE_COMBINATION_ID" />
  <element name="ACCT_SUM_PR_DR" value="PERIOD_NET_DR" function="SUM()" />
  <element name="ACCT_SUM_PR_CR" value="PERIOD_NET_CR" function="SUM()" />
  <element name="ACCT_SUM_BAL_DR" value="BEGIN_BALANCE_DR" function="SUM()" />
  <element name="ACCT_SUM_BAL_CR" value="BEGIN_BALANCE_CR" function="SUM()" />
<group name=" GROUP_3" source="Q1">
  <element name="PERIOD_YEAR" value="PERIOD_YEAR" />
  <element name="PERIOD_NUMBER" value="PERIOD_NUMBER" />
  <element name="PERIOD_NAME" value="PERIOD_NAME" />

How to Call a Data Template:

There are two methods for calling the data engine to process your data template:

• Concurrent Manager

• Data Engine Java APIs

Before you can use either of these methods, you must first register your data template in the Template Manager as a Data Definition. After that you attached the RTF template to that data definition.

Data def for data template

Calling a Data Template from the Concurrent Manager:

To use the concurrent manager to execute your data template, you must register a Concurrent Program, using the define Concurrent Programs form:

CP Def for data template

Executable Name Enter the XML Publisher data engine executable: XDODTEXE

Output Format Select “XML” as the output format.

Note: We do not need to create a concurrent executable as we use a seeded executable XDODTEXE

Concurrent Program – Parameters:

For each parameter in the Data Template, define a parameter in the concurrent program.  The Data Template parameter name should match the concurrent program parameter token

Few tips for best practices:

Performing operations in SQL is faster than performing them in the data template or PL/SQL. It is recommended that you use SQL for the following operations:

  • Use a WHERE clause instead of a group filter to exclude records.
  • Perform calculations directly in your query rather than in the template.

 To maximize performance when building data queries in the data template: XML Publisher tests have shown that using bind variables is more efficient than using the link tag.

The dataStructure section is required for multiple queries and optional for single queries. If omitted for a single query, the data engine will generate flat XML.

How to create concurrent programs from database?

Here are couple of APIs useful for creating any concurrent programs from the backend database.

1)    Registering the Executable from back-end:

Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

  FND_PROGRAM.executable(executable => 'XXFIN TEST EXECUTABLE' , -- Executable Name
  application=>'XXFIN' , -- Application Short Name
  short_name=>'XXFINTSTEXE' , -- Executable Short Name
  description=>'Test Executable created from Backend' ,     -- Description,DEFAULT NULL
  execution_method=>'PL/SQL Stored Procedure',              -- Execution Method
  execution_file_name=>'XXFIN_TEST_PROC' ,                  -- Execution File Name,DEFAULT NULL
  subroutine_name=>NULL ,                                   -- Subroutine Name,DEFAULT NULL
  icon_name=>NULL ,                                         -- Icon Name,DEFAULT NULL
  language_code=>'US' ,                                     -- Language Code,DEFAULT 'US'
  execution_file_path=>NULL                                 -- Execution File Path, DEFAULT NULL

View from Frontend:

Creating Executable


1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.

2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”

FROM fnd_lookup_values


AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:     

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

  FND_PROGRAM.register(program =>'Test CP from DB', -- CP Name
  application =>'XXFIN' , -- Application Short Name
  enabled =>'Y',                                    -- Flag to Enable/Disable a CP
  short_name =>'XXFINTSTCPDB', -- CP Short Name
  description =>'Test CP created from Backend' ,    -- Description,DEFAULT NULL
  executable_short_name =>'XXFINTSTEXE', -- Executable Short Name
  executable_application =>'XXFIN' , -- Executable Application Short Name
  execution_options => NULL,                        -- Execution Options,DEFAULT NULL,
  priority => NULL,                                 -- Priority,DEFAULT NULL,
  save_output =>'Y',                                -- Save Output,DEFAULT 'Y',
  PRINT =>'Y' ,                                     -- Print,DEFAULT 'Y',
  cols => NULL, -- DEFAULT NULL,
  rows => NULL, -- DEFAULT NULL,
  style => NULL,                                    -- DEFAULT NULL,
  style_required =>'N' ,                            -- DEFAULT 'N',
  printer => NULL,                                  -- DEFAULT NULL,
  request_type => NULL,                             -- DEFAULT NULL,
  request_type_application => NULL,                 -- DEFAULT NULL,
  use_in_srs =>'N' ,                                -- DEFAULT 'N',
  allow_disabled_values =>'N' ,                     -- DEFAULT 'N',
  run_alone =>'N' ,                                 -- DEFAULT 'N',
  output_type =>'TEXT',                             -- DEFAULT 'TEXT'
  enable_trace =>'N' ,                              -- DEFAULT 'N',
  restart =>'Y' ,                                   -- DEFAULT 'Y',
  nls_compliant =>'Y' ,                             -- DEFAULT 'Y',
  icon_name => NULL,                                -- DEFAULT NULL,
  language_code => 'US',                            -- DEFAULT 'US',
  mls_function_short_name => NULL,                  -- DEFAULT NULL,
  mls_function_application => NULL,                 -- DEFAULT NULL,
  incrementor => NULL, -- DEFAULT NULL,
  refresh_portlet => NULL                           -- DEFAULT NULL,

View from Frontend:

Creating CP


1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.

2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

  FND_PROGRAM.add_to_group('XXFINTSTCPDB', -- Concurrent Program Short Name
  'XXFIN' , -- Application Short Name
  'All Reports',                           -- Report Group Name
  'SQLAP'); -- Report Group Application

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all.

How to design An Accounting Flexfield Parameter for your Report?

If you want to design similar functionality as below in your report parameter, do the below steps.

Concurrent Program

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

CP Parameters

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD

2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:











Special Value set

How to split a string in oracle into variables based on the separator?

Say to split the below string into 4 variables based on the separator  :

l_string : = ‘AAAA:BBBB:CCCC:DDDD’;

  • l_var1:= ‘AAAA’;
  • l_var2:= ’BBBB’;
  • l_var3:= ‘CCCC’;
  • l_var4:= ’DDDD’;


  l_string VARCHAR2(100) := 'AAAA:BBBB:CCCC:DDDD';
  l_var1   VARCHAR2(240);
  l_var2   VARCHAR2(240);
  l_var3   VARCHAR2(240);
  l_var4   VARCHAR2(240);
  SELECT trim('"'
  FROM regexp_substr(l_string,'".*?"|[^:]+',1,1)) Col1,
  FROM regexp_substr(l_string,'".*?"|[^:]+',1,2)) Col2,
  FROM regexp_substr(l_string,'".*?"|[^:]+',1,3)) Col3,
  FROM regexp_substr(l_string,'".*?"|[^:]+',1,4)) Col4
  INTO l_var1 ,
    l_var2 ,
    l_var3 ,
  FROM dual ;
  dbms_output.put_line('Var1 : ' ||l_var1);
  dbms_output.put_line('Var2 : ' ||l_var2);
  dbms_output.put_line('Var3 : ' ||l_var3);
  dbms_output.put_line('Var4 : ' ||l_var4);

How to make parameters optional in Discoverer?

When you create a parameter for your discoverer report, sometimes we need to make the parameter as optional. You cannot make parameters as optional in Discoverer Desktop. This feature is only available in Discoverer Plus.

Discoverer Parameter in desktop

Discoverer Parameter in plus

Working with REF Cursors – With Examples

A ref cursor is a variable, defined as a cursor type, which points to, or references a cursor result. A cursor variable which is based on REF CURSOR data type can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).



  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  en employee.ename%TYPE;
  OPEN c_employee FOR select ename from employee;
      FETCH c_employee INTO en;
      EXIT WHEN c_employee%NOTFOUND;
  CLOSE c_employee;


  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  er employee%ROWTYPE;
  OPEN c_employee FOR select * from employee;
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.ename || ' - ' || er.position);
  CLOSE c_employee;


  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  er rec_emp;
  OPEN c_employee FOR select ename,position from employee;
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  CLOSE c_employee;


  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  er rec_emp;
  OPEN c_employee FOR select ename,position from employee where deptname = ‘IT’;
  dbms_output.put_line('Department: IT');
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  CLOSE c_employee;
  OPEN c_employee FOR select ename,position from employee where deptname = ‘Finance’;
  dbms_output.put_line('Department: Finance');
      fetch c_employee into er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  CLOSE c_employee;

Normal Cursor Vs. Ref Cursor:

Normal Cursor REF Cursor
It is static in definition. It may be dynamically opened or opened based on logic.
It can’t be returned to a client. It can be returned to a client.
A normal cursor can be global. A ref cursor cannot. (you cannot define them OUTSIDE of a procedure / function)
A normal cursor can’t be passed from subroutine to subroutine. A ref cursor can be passed from subroutine to subroutine.

The performance of normal cursors is much more efficient than ref cursors and hence you should use ref cursor only when you absolutely have to.