Building a generic upload page in OA Framework

Let’s built a very simple page in OAF to enable end-users to upload files to the FND_LOBS table.
After they have been inserted in the table you have all kinds of options, use them as attachments or save them on the filesystem of the server, I use them as input for interfaces for example.

First of all we need some standard stuff from Oracle (actually the VO for FND_LOBS),
so zip the contents of the $JAVA_TOP/oracle/apps/fnd/server and unzip in your JDEV_USER_HOME/myclasses directory.

Create a new OA Workspace and project…

 

 

 

 

 

Set the default package to something like xx.oracle.apps.fnd.upload.webui
I use webui as default package as i plan to create a custom page and controller which reside in the webui package.

 

 

 

 

 

 

 

 

 

 

Click next twice to get to step 3…

 

 

 

 

 

 

 

 

 

 

First the DBC file is needed, you can find this file on the server or via a URL, see this article for details.
Enter a EBS username and password and a valid application (short name) and responsibility (key).
Off course the EBS user you just entered should have access to this responsibility.

Create a new business component.

 

 

 

 

 

 

 

 

Leave the package name as is…

 

 

 

 

 

 

 

 

 

Press next and press finish on page 2.

Now we can import the files you copied from the server to myclasses.

 

 

 

 

 

 

 

 

Find your myclasses directory and goto myclasses/oracle/apps/fnd/server, there should be a server.xml file over there which we will import.

 

 

 

 

 

 

 

 

 

 

You can get following message, answer with Yes.

 

 

 

 

 

Your project should now look like below:

 

 

 

 

When you open oracle.apps.fnd.server you should see all standard FND objects, we are interested in FndLobsVO, which should be there as well.

Now let’s create the page from which we will upload the files.
Click File – New – Web Tier – OA Components and choose Page, name it something like xxUploadPG.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You will see the default Page Layout Region which i renamed to pageLayoutRegion.
Set the following properties:

• AM Definition to oracle.apps.fnd.server.OAAttachmentsAM
• Window title to something like: xx Web Upload
• Title to something like: Web Upload

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Add an item to the pageLayout region with Item Style messageFileUpload.

 

 

 

 

Enter properties as follows:

• View Instance: FndLobsVO
• Data Type: BLOB
• View Attribute: FileData (This is the BLOB column FILE_DATA in FND_LOBS)
• Prompt to something like: File Name

 

 

 

 

 

 

 

 

 

 

 

 

Add a region of type pageButtonBar to hold the buttons and add a Submit and Cancel button (item style submitButton) to this region so we are able to add code later on to submit the upload of the file.
If you run your page, it will look like this now:

To be able to cancel without any validation and return to the homepage we have to change 2 properties of the cancel button first, set Disable Server Side Validation and Disable Client Side Validation to True.

Add a controller to the pageLayout region.

NOTE: For this example we will add all code to the controller, in real situations you should consider moving parts of the code to the AM and/or EO to get cleaner code in the controller.

This it the complete code of the controller xxUploadCO.java, i hope the inline comments will do all the explanation:

/*===========================================================================+
|   Copyright (c) 2001, 2005 Oracle Corporation, Redwood Shores, CA, USA    |
|                         All rights reserved.                              |
+===========================================================================+
|  HISTORY                                                                  |
|  1.0  RJUNGERIUS   07-APR-11 Initial creation                             |
+===========================================================================*/
package xx.oracle.apps.fnd.upload.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.OAWebBeanConstants;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.OAException;

import oracle.jbo.Row;

import oracle.apps.fnd.framework.webui.beans.message.OAMessageFileUploadBean;
import oracle.apps.fnd.framework.webui.OADataBoundValueViewObject;

/**
* Controller for …
*/
public class xxUploadCO extends OAControllerImpl
{
public static final String RCS_ID=”$Header$”;
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, “%packagename%”);

/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
OAApplicationModule am = pageContext.getApplicationModule(webBean);
OAViewObject vo = (OAViewObject)am.findViewObject(“FndLobsVO”);
// Per the coding standards, this is the proper way to initialize a
// VO that is used for both inserts and queries.  See View Objects
// in Detail in the Developer’s Guide for additional information.
if (!vo.isPreparedForExecution())
{
vo.executeQuery();
}
Row row = vo.createRow();
vo.insertRow(row);
// Required per OA Framework Model Coding Standard M69
row.setNewRowState(Row.STATUS_INITIALIZED);

// Initialize columns
// Set column FILE_FORMAT to IGNORE, this column is used for the online help index
row.setAttribute(“FileFormat”,”IGNORE”);
// set upload date to current date and time
row.setAttribute(“UploadDate”, am.getOADBTransaction().getCurrentDBDate());
// set program name to the name of this module, i.e xxUpload
// This way it is easy to identify that the record was inserted by this customization
row.setAttribute(“ProgramName”, “xxUpload”);
// get a handle to the uploadbean
OAMessageFileUploadBean uploadBean = (OAMessageFileUploadBean)webBean.findChildRecursive(“fileName”);
// set file name display
OADataBoundValueViewObject displayNameBoundValue = new OADataBoundValueViewObject(uploadBean, “FileName”);
uploadBean.setAttributeValue(DOWNLOAD_FILE_NAME,displayNameBoundValue);
// set content type (MIME)
OADataBoundValueViewObject contentBoundValue = new OADataBoundValueViewObject(uploadBean, “FileContentType”);
uploadBean.setAttributeValue(FILE_CONTENT_TYPE, contentBoundValue);
}

/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
if (pageContext.getParameter(“cancelButton”) != null)
{
// Perform a rollback on the database
OAApplicationModule am = pageContext.getApplicationModule(webBean);
am.getTransaction().rollback();
// cancel button pressed, return to homepage
pageContext.forwardImmediately(“OA.jsp?OAFunc=OAHOMEPAGE”,
null,
OAWebBeanConstants.KEEP_MENU_CONTEXT,
null,
null,
false, // do not retain AM
OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
}
else if (pageContext.getParameter(“submitButton”) != null)
{
// Perform a commit on the database
OAApplicationModule am = pageContext.getApplicationModule(webBean);
am.getTransaction().commit();
// Display upload confirmation message
OAViewObject vo = (OAViewObject)am.findViewObject(“FndLobsVO”);
oracle.jbo.domain.Number fileId = (oracle.jbo.domain.Number)vo.getCurrentRow().getAttribute(“FileId”);
String fileName = (String)vo.getCurrentRow().getAttribute(“FileName”);
OAException confirmMessage = new OAException(“File “+fileName+” uploaded succesfully with ID “+fileId+”.”,OAException.CONFIRMATION);
pageContext.putDialogMessage(confirmMessage);
// return to upload page
pageContext.forwardImmediately(“OA.jsp?page=/xx/oracle/apps/fnd/upload/webui/xxUploadPG”,
null,
OAWebBeanConstants.KEEP_MENU_CONTEXT,
null,
null,
true, // retain AM
OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
}

}

}

Make and rebuild the complete project and run the page from within JDeveloper.
If you followed all steps then you should be able to select a file and after pressing Submit it will be inserted in FND_LOBS and the confirmation message is shown as below:

To deploy copy the controller java file and the page xml file to $JAVA_TOP/xx/oracle/apps/fnd/upload/webui.
Compile the java file.
Import the page with the following command:

java oracle.jrad.tools.xml.importer.XMLImporter \
$JAVA_TOP/xx/oracle/apps/fnd/upload/webui/xxUploadPG.xml \
-username apps -password -rootdir $JAVA_TOP/xx/oracle/apps/fnd/upload/webui \
-rootPackage /xx/oracle/apps/fnd/upload/webui \
-dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=))(CONNECT_DATA=(SID=)))”

Define a function via Functional Administrator responsibility like below:

That’s all folks, assign the function to an appropiate menu and users have access to it.
This has been done in 11i but it works exactly the same on R12 environments.

Customize the #HDR header attributes of notifications in a workflow

Oracle uses a standard way to display header information and as such it is very easy to customize this part.
Let’s take the Purchase Requisition approval notification and replace the header attribute “Estimated Tax” with our own information or any other information already present in the workflow.

Open your workflow and open a message, in this case we use “Requisition Approved JRAD”.

If you click “Description”, “Requisition Total” or “Estimated Tax” then you will see that these are marked as #HDR_1, #HDR2, #HDR3, etc.
These internal names have special meaning and will be used to build up the right part of the notification header.
There are some more special internal names like #ATTACHMENTS, but in this blog we will focus on customizing the #HDR attributes.

You can find the definition of all the attributes, including #HDR, (per language) in the message as follows:

SELECT *
FROM   wf_message_attributes_tl
WHERE  message_type = ‘REQAPPRV’
AND    LANGUAGE=’US’
AND    message_name = ‘PO_REQ_APPROVED_JRAD’;

Furthermore you can find the values of these attributes per notification with this SQL:

SELECT *
FROM   wf_notification_attributes
WHERE  notification_id = <ntf_id>
AND    NAME = ‘#HDR_1’;

This is the default #HDR_3, next we will replace it with another value.
For this example we will use a standard attribute “Emergency PO Number”, but you can also use a custom attribute off course.

 
#HDR3 replaced with our own information.

In all new notifications of this type you will now see the Emergency PO Number instead of the Estimated Tax.

That’s all there is to it, this way you can easily customize the header section of any notification.

Using SPEL in OA Framework to overcome “context” issue in notifications

When you extend a VO used in a notification and you want to make sure that the custom columns are only visible for certain operating units then you will quickly notice that this is not possible using personalization’s on organization level.
Easiest way to see this in action is to open a notification from the homepage worklist (without opening/selecting any responsibility/function). In that case oracle has not set any context yet, so OA Framework will also not apply personalization’s on any level and will default to site level. If any custom columns have been added to the VO and you would expect them to only show up for a certain organization then you will notice that it is not working and other organizations will also see the custom columns.

So how can we make sure that certain personalization’s within notifications only show up for certain organizations without relying on oracle to set the context correctly ?

we can achieve this by using SPEL and adding a new attribute to the VO.

Add a new column to the VO query that will return a boolean that returns True/1 when you want the custom columns to render and False/0 when you don’t want them to render.

For example you can add a case statement to the VO query that will do the trick on org_id:
NOTE: I would not use this in a real situation but always use a PLSQL function to return the flag, that way you can easily change the requirements in PLSQL without touching the VO again.

CASE org_id
WHEN 65 THEN 1
WHEN 67 THEN 1
ELSE 0
END AS xx_display_flag

So in above case we want to make sure that the columns will display only for org_id 65 and 67.

We can now use this xx_display_flag column in SPEL to determine the rendered property of the custom columns.
Go to the Personalize Region screen again and select the custom column you want to apply this SPEL to.

In the Rendered property (on site level) set the value to SPEL and enter “${oa.<View Name>.<View Attribute>}”.
Apply the change and you will immediately see that the custom column(s) will now only render when the data returned by the VO is for a org_id that makes the flag return True/1.
There is no need anymore to rely on oracle to set the context correctly.

 

Extending a VO: ReqLinesNotificationsVO in ReqLinesNotificationsRN

The standard requisition approval notification is based on OA Framework regions and shows basic information for requisition lines. A common change request is to extend this line with additional information/columns.
So let’s find out how this is done.

First let’s find out which VO is behind the requisition lines information.
Make you sure you have the “About this page” link available and open up a requisition approval notification.

Click “About this page” and Click the Expand All link for the Page Definition.

Find the section where the Requisition Lines are defined.

As you can see the VO to extend will be ReqLinesNotificationsVO.

Click Collapse All on the Page Definition and open the Business Component References section.

 

Here you can see the location of the ReqLinesNotificationsVO, i.e. $JAVA_TOP/oracle/apps/icx/por/wf/server.
Get all the directories and files from this location and place them on your pc in the myclasses directory.

Define a new OA workspace and OA project to host the extension we will develop.
Set the default package in the OA Project definition to xx.oracle.apps.icx.por.wf.server

Create a new Business Components Package to host the extended VO

Make sure the package mentions xx.oracle.apps.icx.por.wf.server

Click Next and click Finish in Step 2 of 3.

Import the standard Business Components you copied from the server to your myclasses directory.
Go to myclasses and click the server.xml file in myclasses\oracle\apps\icx\por\wf\server.

   If all is ok then your project will now like this:

Before we extend the VO we first need the original SQL so we can add additional columns to it.
Double-click the ReqLinesNotificationsVO.xml file and copy the complete SQL to the clipboard.

 

 

If all is ok then we can now define the new VO.

 

Make sure that you populate the Extends field with the original VO.
Proceed to step 5 and paste the SQL in the Query Statement field.
Now we can alter the SQL to include extra columns, for this example i will add the item category.
It is best to define PLSQL functions for the columns so that you can easily change the logic behind it later on without touching the VO again.

The function we will use to get this category is as follows:

CREATE OR REPLACE FUNCTION xx_category_fnc(p_requisition_line_id IN NUMBER)
  RETURN VARCHAR2 IS
  v_category mtl_categories_kfv.concatenated_segments%TYPE;
BEGIN
  SELECT mcat.concatenated_segments
  INTO   v_category
  FROM   po_requisition_lines_all prl
        ,mtl_categories_kfv       mcat
  WHERE  prl.requisition_line_id = p_requisition_line_id
  AND    prl.category_id = mcat.category_id;
  RETURN v_category;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

Add the new column “xx_category_fnd(requisition_line_id) AS XX_CATEGORY” as the last column of the query.
Click Test to check if the query is valid.

Continue to step 7 and make sure you generate java files for the view row class, this is to comply with OA Framework coding guidelines. You can uncheck the view object class as we will not add any custom code.

We are almost there, now we need to register the substitution of the original ReqLinesNotificationsVO with the newly created xxReqLinesNotificationsVO, double-click the xxReqLinesNotifications.jpx file and select the Substitutions.

On the left side select the ReqLinesNotificationsVO and on the right side select xxReqLinesNotificationsVO, click Add to add the substitution and click Ok to close the dialog.

Done (Click Save All to make sure everything is saved)!
Make and build the project and close JDeveloper.

Go to your myclasses directory and move the complete directory “xx\oracle\apps\icx\por\wf\server” to the $JAVA_TOP on the server. Also copy the xxReqLinesNotifications.jpx to the $JAVA_TOP/xx/oracle/apps/icx/por/wf/server directory on the server so we can run jpximport from the server.

Execute following command to import the jpx file (substitute the $variables with your own values):

java oracle.jrad.tools.xml.importer.JPXImporter \
  $JAVA_TOP/xx/oracle/apps/icx/por/wf/server/xxReqLinesNotifications.jpx \
-username $APPS_NAME -password $APPS_PASSWORD \
-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$IP_ADDRESS)(PORT=$PORT_NUMBER))(CONNECT_DATA=(SID=$DATABASE_SID)))"

This should return a message:

Imported document : /oracle/apps/icx/por/wf/server/customizations/site/0/ReqLinesNotificationsVO
Import completed successfully

DONE!

Bounce the Apache server or the complete mid-tier and we can continue to define the new column in the EBS environment so it will be displayed.
To be able to personalize the page make sure the following profiles have been enabled on your account:
FND: Personalization Region Link Enabled
Personalize Self-Service Defn

Login to EBS and select a Requisition notification, click the “About this page” link so we can found out the attribute name of our newly defined column. You should be able to find your xxReqLinesNotificationsVO in the View Objects list now, click this one so we can see all attributes defined.

If all is ok you will see the xx_category column defined as attribute XxCategory:

Return to the notification and now click the Personalize link of the requisition lines table.

Click the Create Item link on the Table:

Select Site as level and choose Message Styled Text as item style, populate the ID with a unique name:

Populate the Prompt  and the View Attribute and you are done, press Apply to finish.

Return to the notification and you should now see the newly defined column at the end of the line.

NOTE:
It could well be that your requirements are to only show the columns for specific organizations. You would assume you can do this by setting the Rendered property False on site level and only set it to True on specific organization levels.
Unfortunately this will not work when opening notifications from the homepage worklist as at that point oracle has not set any context or organization (it is actually unaware at that point which org_id needs to be set in the context).
There is quick and easy way to solve this yourself and not rely on context switching anymore.

See “Using SPEL in OA Framework to overcome “context” issue in notifications” for detailed information.

Get contents of DBC file without access to server

During OA Framework development you need to get the DBC file of the environment you want to work on.
This file is located in $FND_TOP/secure for 11i and $FND_SECURE for R12.

But if you don’t have access to the server it is also possible to get the DBC file in a convenient way.
Open up a webbrowser and go to the homepage URL and append “/OA_HTML/jsp/fnd/aoljtest.jsp” to it.
(so for example: http://vis11.server.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp)

Fill in the details and click “Test”.
If all your details are ok you will get an overview page with details, click on the link “Enter AOL/J Setup Test”.

From this point find the menu entry “Locate DBC File” and click it…

On the right side you will get the contents of the DBC file.
Copy/paste this to a <sid>.dbc file and place it in your JDeveloper JDEV_USER_HOME/dbc_files/secure.

Use a sequence value when saving a record in OA Framework

Most tables in EBS have a unique identifier which can be generated using a database sequence.
For custom tables i also use the same approach.
In Oracle Forms this is relatively easy to achieve, how about OA Framework ?

If you use an EO (Entity Object) then it is also very easy to achieve, just put the code in the setter method of the required column:

 

public void setFileId(Number value) {
   if (value == null) {
        OADBTransaction t = getOADBTransaction();
        value = t.getSequenceValue("<sequence_name>");
    }

    setAttributeInternal(FILEID, value);
}

 

If you need to do it in the PR of PFR then use:

OAApplicationModule ap = pageContext.getApplicationModule(webBean);
// Assign a new sequence value to a row attribute for example
row.setAttribute("FileId",ap.getSequenceValue("<sequence_name>"));

Extending a controller in OA Framework

As an example i will take the change request i received to make the Oracle Internet Expense Audit page “security rule aware”.
Currently you will get all the expense reports in there but we would like to create different copies of the responsibility to which we assign different (GL) security rules on the cost centre segment:

• XX OIE Internal Audit (CC 0300-0399), will only see expense reports from cost centre 0300 – 0399
• XX OIE Internal Audit (CC 0400-0499), will only see expense reports from cost centre 0400 – 0499
• etc

To achieve this functionality the where clause of the query behind the results table in this page needs to be modified so it will check if the flex_concatenated column of ap_expense_report_headers_all table is between the assigned flex value security rules ranges.
To extend the OA Framework page you have 2 options, extend the VO or extend/create the controller behind the results table in the page.
Extending a VO is on a global level and you pick this option if you want to add extra columns.
Extending/creating a controller can be assigned on all levels (function/site/organization/responsibility/etc) and you can change the where clause in the ProcessRequest method, so extending the controller is definitely the way to go…

Open up the standard Internet Expenses Internal Audit screen and click “About this page” in the lower left corner.
You can already see that it is the “Search Results” table we are after…

Click “Expand All” below Page Definition

Now find the Search Results Table in the page definition list…

As you see there is already a controller assigned to the Search Results table, i.e. SearchTableCO.
So we need to extend this controller to be able to add a custom where clause to the query.

Extending a controller is merely creating a new java file and as such could be done using any editor if you know all the details. I always rely on JDeveloper.
We only need the source file of SearchTableCO, so that will be $JAVA_TOP/oracle/apps/ap/oie/audit/webui/SearchTableCO.class.
So login on your server and ftp the file over to JDEV_USER_HOME\myclasses\oracle\apps\ap\oie\audit\webui\SearchTableCO.class.

Open up JDeveloper and create a new OA Workspace and project, follow below screenshots:

Extended objects need to be placed in a custom package, usually it will be the original package prefixed with xx, i.e. xx.oracle.apps.ap.oie.audit.webui

Move on to step 3 and specify a valid DBC file for the environment you are testing on (get this one from the server at $FND_TOP/secure and place it in JDEV_USER_HOME\dbc_files\secure).
Furthermore specify a valid applications user/password which has the correct responsibility assigned
and enter the correct application (SQLAP) and responsibility key.

Right-click the newly created project and choose “New Class…”
Enter a name for the new class, i.e. xxSearchTableCO and check that the package defaults to the custom package of the project.
Use the browse button from “Extends” to lookup up the class SearchTableCO under oracle/apps/ap/oie/audit/webui as we want
to extend the original controller class from the Search Table. Click OK to create the new class file.

Now that we have created a new class file that extends the original controller we can override the processRequest method.
In green you can see what needs to be added to the original file JDeveloper created for you:

package xx.oracle.apps.ap.oie.audit.webui;
import oracle.apps.ap.oie.audit.webui.SearchTableCO;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;

public class xxSearchTableCO extends SearchTableCO
{
    public void processRequest(OAPageContext pageContext, OAWebBean webBean)
    {
        super.processRequest(pageContext, webBean);
          // Get a handle to the AuditReportHeadersVO VO
          OAApplicationModule ap = pageContext.getApplicationModule(webBean);
          OAViewObject vo = (OAViewObject)ap.findViewObject("AuditReportHeadersVO");
          // extend where clause
          vo.setWhereClause("((SELECT COUNT(1) FROM   fnd_responsibility fr,fnd_flex_value_rule_usages ffvru,fnd_flex_value_rules ffvr"+
                            " WHERE  fr.responsibility_id = ffvru.responsibility_id AND ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id) = 0) OR"+
                            " (((SELECT COUNT(1) FROM fnd_responsibility fr, fnd_flex_value_rule_usages ffvru, fnd_flex_value_rules ffvr,"+
                            " fnd_flex_value_rule_lines ffvrl WHERE fr.responsibility_id = ffvru.responsibility_id AND"+
                            " ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id AND"+
                            " ffvrl.include_exclude_indicator = ‘E’ AND"+
                            " flex_concatenated BETWEEN ffvrl.flex_value_low AND ffvrl.flex_value_high) = 0) AND"+
                            " ((SELECT COUNT(1) FROM fnd_responsibility fr, fnd_flex_value_rule_usages ffvru, fnd_flex_value_rules ffvr,"+
                            " fnd_flex_value_rule_lines ffvrl WHERE fr.responsibility_id = ffvru.responsibility_id AND"+
                            " ffvru.flex_value_rule_id = ffvr.flex_value_rule_id AND ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id AND"+
                            " fr.responsibility_id = fnd_global.resp_id AND"+
                            " ffvrl.include_exclude_indicator = ‘I’ AND"+
                            " flex_concatenated BETWEEN ffvrl.flex_value_low AND ffvrl.flex_value_high) > 0))");
    }
}

NOTE: In above where clause below checks are done:
(maybe it can be done in a smarter way, let me know!)

1. Check if there are NO security rules assigned to the current responsibility (count = 0)
   OR
2. Check if there are NO EXCLUDE lines in the security rules assigned to the current responsibility (count = 0) for the cost centre of the expense report
   AND
3. Check if there ARE INCLUDE lines in the security rules assigned to the current responsibility (count > 0) for the cost centre report

So the expense report is shown in the search table if check 1 is true OR (check 2 AND check 3 are true).

Try if the class file compiles correctly, right-click on the project and Make and Rebuild the project.

 

To deploy it is very simple, copy the xxSearchTableCO.java file to your server in directory $JAVA_TOP/xx/oracle/apps/ap/oie/audit/webui.
Open a terminal session on your server, set your environment and compile the java file.

 

Last step to take is to assign the new controller class to the Search Results table.
Login into Oracle E-business Suite and click the Expenses Internal Audit responsibility. On the main page click “Personalize Search Results” link.

Click the Personalize Pencil on Table Search Results.

We want the extension to be applied on organization level so we enter the new controller class xx.oracle.apps.ap.oie.audit.webui.xxSearchTableCO in the organization entry field. After you applied this change you can return to the personalization and you should see that the Result/Source column now shows your new controller class to be the active one on Organization level.

 

Bounce the Apache  server and you are done!
Create copies of the Internal Audit responsibility and assign different security rules to them to get the desired results.

Making sure (some) workflow notifications do not get sent via email

When you define a workflow mailer on a test instance and/or on production you usually don’t want the complete history of pending notifications to be emailed.
To avoid this you need to set the mail status of the notifications in WF_NOTIFICATIONS to SENT
and rebuild the WF_NOTIFICATION_OUT queue/table (wfntfqup.sql).
What i found more convenient is to run a script to only dequeue the messages that you don’t want to sent out, so no need to do a complete rebuild.

— Step 1:

— Clear mail status for ALL pending HR (item_type is HRSSA) notifications older then 1 day.
— This will make sure they will not end up in the WF_NOTIFICATION_OUT queue in case of a rebuild.

UPDATE wf_notifications
SET    mail_status = ‘SENT’
WHERE  message_type = ‘HRSSA’
AND    TRUNC(begin_date) < TRUNC(SYSDATE)
AND    mail_status IN (‘MAIL’, ‘FAILED’);

COMMIT;

— Step 2:

— Run following pl/sql code from SQL*Plus on the server (logged in as APPS).
— This will dequeue all pending HRSSA notifications older then 1 day from the mailer queue
— NOTE: Enable serveroutput as it will display the number of notifications it will dequeue.

SET SERVEROUTPUT ON

DECLARE
  q_version VARCHAR2(200);
  event sys.aq$_jms_text_message;
  dequeue_options dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;

  TYPE msgidtab IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;

  msg_id msgidtab;
  dq_msg_id RAW(16);
  q_name VARCHAR2(60);

  CURSOR target_msgids IS
    SELECT wfno.msg_id
    FROM applsys.aq$wf_notification_out wfno
    WHERE wfno.corr_id LIKE ‘APPS:HRSSA:%’
    AND TRUNC(wfno.enq_time) < TRUNC(SYSDATE)
    AND wfno.msg_state = ‘READY’
    ;

BEGIN
  q_name := ‘APPLSYS.WF_NOTIFICATION_OUT’;
  dequeue_options.dequeue_mode := dbms_aq.remove_nodata;
  dequeue_options.wait := dbms_aq.no_wait;
  dequeue_options.navigation := dbms_aq.first_message;
  dequeue_options.consumer_name := ‘WF_NOTIFICATION_OUT’;

  OPEN target_msgids;
  FETCH target_msgids BULK COLLECT
  INTO msg_id;
  CLOSE target_msgids;
  —
  dbms_output.put_line(‘Dequeue of ‘||msg_id.COUNT||’ messages.’);
  —
  IF (msg_id.COUNT > 0) THEN
    FOR i IN msg_id.FIRST .. msg_id.LAST LOOP dequeue_options.msgid := msg_id(i);
      dbms_aq.dequeue(queue_name         => q_name
                       ,dequeue_options    => dequeue_options
                       ,message_properties => message_properties
                       ,payload            => event
                       ,msgid              => dq_msg_id);
      dequeue_options.navigation := dbms_aq.next_message;
    END LOOP;
    COMMIT;
  END IF;
EXCEPTION
–timeout will fall to here
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
  IF (target_msgids%ISOPEN) THEN
    CLOSE target_msgids;
  END IF;
END;
/

NOTE: Dequeuing can take a few minutes to be processed so check the wf_notification_out table to make sure no messages are pending before starting the workflow mailer (select count(1) from wf_notification_out where corrid like ‘APPS:HRSSA%’ and state=0;)

SQL to list profile values on all levels

SELECT fpot.user_profile_option_name “Profile”
,fpov.profile_option_value “Value”
,decode(fpov.level_id
,10001
,’Site’
,10002
,’Application’
,10003
,’Responsibility’
,10004
,’User’
,’UNKNOWN’) “Level”
,decode(fpov.level_id
,10002
,fat.application_name
,10003
,frt.responsibility_name
,10004
,fu.user_name
,’n/a’) “Level value”
FROM   applsys.fnd_application_tl        fat
,applsys.fnd_user                  fu
,applsys.fnd_responsibility_tl     frt
,applsys.fnd_profile_option_values fpov
,applsys.fnd_profile_options       fpo
,applsys.fnd_profile_options_tl    fpot
WHERE  upper(fpot.user_profile_option_name) LIKE upper(‘%&v_profile%’)
AND    fpot.profile_option_name = fpo.profile_option_name
AND    fpot.LANGUAGE = ‘US’
AND    fpo.application_id = fpov.application_id(+)
AND    fpo.profile_option_id = fpov.profile_option_id(+)
AND    fpov.level_value = frt.responsibility_id(+)
AND    frt.LANGUAGE(+) = ‘US’
AND    fpov.level_value = fu.user_id(+)
AND    fpov.level_value = fat.application_id(+)
AND    fat.LANGUAGE(+) = ‘US’
ORDER  BY “Profile”
,”Level”
,”Level value”
,”Value”;

 

Flexfield security rule assignments to responsibility

The following query will list all the security rules assigned to a responsibility

SELECT fr.responsibility_id
      ,fr.responsibility_key
      ,fr.responsibility_name
      ,ffvr.flex_value_rule_name
      ,ffvrl.include_exclude_indicator
      ,ffvrl.flex_value_low
      ,ffvrl.flex_value_high
FROM   fnd_responsibility_vl      fr
      ,fnd_flex_value_rule_usages ffvru
      ,fnd_flex_value_rules_vl    ffvr
      ,fnd_flex_value_rule_lines  ffvrl
WHERE  fr.responsibility_id = ffvru.responsibility_id
AND    ffvru.flex_value_rule_id = ffvr.flex_value_rule_id
AND    ffvr.flex_value_rule_id = ffvrl.flex_value_rule_id
AND    fr.responsibility_name = ‘&resp_name’
ORDER  BY fr.responsibility_id
         ,ffvr.flex_value_rule_name
         ,ffvrl.include_exclude_indicator DESC
         ,ffvrl.flex_value_low
         ,ffvrl.flex_value_high;