Wednesday, November 16, 2011

Disable Right Click in OAF page

Following code in controller will block the Right Click, Context Menu Options for IE and Netscape both:

  pageContext.putJavaScriptFunction("click()",
    "var message=\"Due to security reason, Right Click is not allowed\";"+
      "function right2(){\n"+
           "if (event.button==2){\n"+
                "alert(\"Right Click is not allowed.\");\n"+
                "return false;\n"+
            "}\n"+
       "}\n"+
 "function rightClickTest (e) \n" +
 "{\n" +
      "if (document.layers||document.getElementById&&!document.all){ \n"+
        "if (e.which==2||e.which==3){\n"+
            "alert(\"You do not have permission to right click.\");\n" +
            "return false;\n" +
        "}\n"+
      "}\n"+
  "}\n"+
  "if (document.layers) {" +
      "document.captureEvents(Event.MOUSEDOWN);\n" +
      "document.onmousedown=rightClickTest;\n"+
  "}\n"+
  "else if (document.all&&!document.getElementById){" +
      "document.onmousedown=right2;\n"+
  "}\n"+
  "document.oncontextmenu=new Function(\"alert(message);return false;\")"
 );


Following scenarios have been tested and worked fine:
  • Switching the Buttons (Left-Right) has no impact because it doesn't change the event raised.
  • Context Menu from the keyboard is getting blocked as expected.

Why is it needed?

This is one question that I have not been able to find the answer. I can't think of any valid logical reason for disabling the right click. I have seen it in following places:
  • Bank has disabled right click on pages that shows account information.
  • Companies disable right click on page that displays the payslips.

How to enable the right click?

1.  Copy following javascript code in your browser and hit enter.
javascript:void(document.onmousedown=null);void(document.onmouseup=null);void(document.onclick=null);void(document.oncontextmenu=null)

Above seems to be working for OAF pages that have right click blocked and other websites including my bank's website as well.

2.  Disabling the javascript in browser also enable the Right click in websites.


Whether we should do that or not?

NO, its very annoying, and incomplete solution. We should find better way to restrict and protect information that we need to protect.

Sunday, October 23, 2011

Control Hints in OAF


Main purpose of Control hints is to centralize certain UI settings across clients. For Example, Label Text control hint is used to give same Field Name/Column Name on all pages where an attribute of a View Object is being displayed. It greatly reduces the amount of UI coding in ADF and thus is frequently used there.
But in OAF it greatly increases the amount of coding needed on UI.


Following screenshot gives an example of where we set control hints:



Following is an example how we get the value of control hints:

OAViewObject vo = (OAViewObject)pageContext.getRootApplicationModule().findViewObject("VOInstanceName");
AttributeDef[] attrDef = vo.getAttributeDefs();
attrdef[i].getName();
// This will give the name of the Attribute; Below image shows the Attribute Name along with other properties.
attrDef[i].getUIHelper().getLabel(pageContext.getRootApplicationModule().getSession().getLocaleContext());
// This will give the value of Lable set in the control hints screen.




When you add control hints, a message bundle file gets created.

Saturday, October 22, 2011

DBC File


  • DBC stands for database connect descriptor file.
  • The .dbc file is used to define database parameters used to connect to database, it authenticate users against database in FND_USER table. 
  • It provides runtime database connection, authentication and authorization in Jdeveloper.
  • Default Location for the file is $FND_TOP/secure directory.
  • Profile option "Applications Database ID" contains name of the DBC file.
  • Oracle Apps Login also uses DBC file.
Alternate way to find correct DBC file used in Apps:
  1. Go on About this Page.
  2. Navigate to "Java System Properties" tab.
  3. Check the value of System Property "DBCFILE".
Following image shows Jdev properties where we need to specify the correct DBC file in order to run a page from it:


Important entries in DBC file are:

GUEST_USER_PWD - Guest Userand Its Password
APPL_SERVER_ID - Server ID in FND_NODES table against respective Server
APPS_JDBC_URL - Database connection details
DB_HOST - DB hostname
GWYUID - Gateway User ID and password - database user APPLSYSPUB and password
FNDNAM - Central oracle application schema name, usually its APPS. echo $FNDNAM for the value.
TWO_TASK - It is set to the location where ORACLE server can be found and will normally be the name of the database instance.
By Setting TWO_TASK instead of:
$ sqlplus scott/tiger@some_db

Following will also work:
$ setenv TWO_TASK some_db
$ sqlplus scott/tiger


How DBC files is used  in Login?
  • Self-service login uses Guest password from DBC file to verify the user password.
  • User Login will not work if this password is incorrect.
  • Guest user is used to obtain apps information.
  • Applsyspub schema is responsible for password checking, default password is pub.
  • Oracle applications first connects to this public schema, APPLSYSPUB, public oracle username and password for authentication.
  • This schema has sufficient privileges to perform the authentication of an Applications User (FND user).
  • Once authenticated you get connected to APPS.
DBC file generation, Securing the Apps are DBA responsibility area.
More on Apps Login process security will soon be covered in coming blog.

Note:
DBC is so closely related to Security that going into more details on DBC file requires understanding of Encryption/Decryption of passwords, APPS, APPLSYS, APPLSYSPUB, FND_USER password storage, APPS password storage etc.

Wednesday, October 12, 2011

Multiple Browser Sessions with Oracle Apps Server

A while back we faced an issue with the multiple browser sessions with Oracle Apps Server with same IP Address from Internet Explorer. The sessions were getting merged. It was not allowing us to login to apps with two different users from one computer using IE.

Few days back again I saw the same issue being discussed, so sharing following link on the issue details and resolution:

http://oracleajidba.blogspot.com/2010/04/oracle-apps-multiple-sessions-problem.html

Wednesday, October 5, 2011

Attachments in OAF Pages: A Simple Case study




In Oracle Apps, we enable the attachments at logical entity level. This entity is different that entity in ER diagram or Entity Objects. An entity is an object within Oracle E-Business Suite data, such as an item, an order, or an order line. In the context of attachments, an entity can be considered either a base entity or a related entity.

A base entity is the main entity of the region. A related entity is an entity that is usually related to the region by a foreign-key relationship.
  • Attachments are linked with the VO’s primary key (could be combination of columns).
When an attachment is stored, the values of these primary keys and the Entity ID are stored so the attachments can be uniquely retrieved. (We’ll discuss in details about the storage of this information a little later).

Attachment Styles in a Region

There are four attachment styles that you can render in a region:
  • Display a View List Link and Add Button in a Single Row Region
      
  • Display an Attachments Table on the Page of a Single Row Region













  • Display In-line Links of Attachments in a Single Row Region
      
  • Display an Attachments Column in a Multi-Row Table Region
           


Add Attachment Page:

On Clicking Add Attachment, users are navigated to this page.

It is clearly displaying the three types of attachments supported:
  • URL
  • File
  • Text
Attention: All Text attachments are added as "Short Text" type, with a maximum length of 4000 bytes. If you need to add an attachment that is longer than 4000 bytes, put the text into a text file and add the attachment as a File.


Steps to add Attachments:
  1. Add the UI widget for attachments. Following are the widgets:
    • attachmentTable - for Attachments table
    • attachmentLink - for View List link and Add button
    • MessageInlineAttachment - for inline attachment links
    • attachmentImage - For Attachment column in Table and Advance Table
  1. Specify View Instance property
  1. Select the default entityMap that is created under the attachment region item, set the Entity value to some unique value which will be used to identify all attachments.
  1. Right Click on Entity Map and Add primary keys and specify the primary keys used to identify the attachment uniquely.

Some key SQL Queries:

Query 1: List of All Attachments
--------------------------------------------
select * from fnd_attached_documents where creation_date > sysdate - 1/24 ;/
// List of all Attachments attached in last 1 hr.

Query 2: List of All Attachments
--------------------------------------------
select * from fnd_attached_documents where entity_name = 'XXSL_GROUP' ;/
// List of all Attachments with entity_name = 'XXSL_GROUP'

Query 3: List of All Attachments
--------------------------------------------
select attached_document_id, -- Primary Key
document_id, -- foreign key for FND_DOCUMENTS
entity_name, -- Name of unique entity entered in OAF page's Attachment component
PK1_value, -- primary key attribute specified in OAF page's Attachment component.
-- There are 5 Primary Key columns here to store composite primary keys.
seq_num, -- sequence number of the attachment added.
category_id -- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES
-- In case you are not able to see an attachment in the UI,
-- check the "Show All" property of the Entity Map
from fnd_attached_documents
WHERE ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>'; /

Query 4: Details of the document attached
---------------------------------------------------------

SELECT * FROM FND_DOCUMENTS where document_id in (7922696, 7922698, 7922700, 7922702); /
SELECT document_id, -- Primary Key
datatype_id, -- foreign key to FND_DOCUMENT_DATATYPES, tells about the type of attachment
category_id, -- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES
url, -- URL field is populated in case of URL Type attachment
File_name, -- File name field is populated in case of a file type attachment
media_id -- Foreign key to fnd_lobs for datatype_id = 6
--
FROM FND_DOCUMENTS -- Attachment Title is stored in FND_DOCUMENTS_TL
where document_id in (select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>');
/

Query 5: Types of Attachments
-------------------------------------------
select * from FND_DOCUMENT_DATATYPES;/
Short Text
Long Text
Image
OLE Object
Web Page
File
Document Reference
Oracle Files Folder/Workspace
Oracle File

select * from xxsl_lms_group_headers where group_id = 1722 ;/

Query 6: Types of File Attached
---------------------------------------------
--Record will be in FND_LOB only for datatype_id = 6
select * from fnd_lobs
where file_id in
(select media_id
from FND_DOCUMENTS
where document_id in
(select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>' and datatype_id = 6)
); /
Query 7: for "Short Text" type:
------------------------------------------
-- Record will exist in FND_DOCUMENTS_SHORT_TEXT if datatype_id is 1
select * from FND_DOCUMENTS_SHORT_TEXT
where media_id in
(select media_id
from FND_DOCUMENTS
where document_id in
(select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>' and datatype_id = 1)
); /



API Support:
For Api support refer to following packages - 
1. FND_DOCUMENTS_PKG,
2. FND_ATTACHED_DOCUMENTS_PKG
3. fnd_attached_documents2_pkg.delete_attachments (to delete attachments)


Followings are not covered in this post:
  • Security
  • Virus Scanning
  • Multiple entity support
  • Attachments to be visible on Forms as well as OAF. (FND_ATTACHMENT_FUNCTIONS)
  • Document Catelog

Tuesday, September 13, 2011

OAF Custom Clear Button - Different Approaches


While working on Search Pages more often that not we cannot use the Result-Based or AutoCustomization Criteria, we go for Manual Search Pages. To implement "Clear" button is sometimes not mandatory, but nice to have feature there.

Before discussing various approaches, lets observe some features of standard Clear Button:
  • It doesn’t do form submit.
  • It doesn’t clear the results.
  • It doesn’t clear any other fields on the page apart from Search Criteria fields
  • Standard Clear Button is implemented with JavaScript.

Clear Button Scenarios and Approaches:-

Approach 1: Using Submit Button to clear the search criteria fields:

Following is the code snippet:

        if(pageContext.getParameter("ClearBtn") != null)
       {
              OAMessageTextInputBean searchCriteriaTxt1=
                 (OAMessageTextInputBean) webBean.findChildRecursive("SearchCriteriaTxt1") ;
              searchCriteriaTxt1.setText(pageContext, null);
              // searchCriteriaTxt1.setText(null); will through Developer Mode Exception
              // since it is processFormRequest.
         }

      Advantages:
  • You can achieve same functionality as standard Clear button by standard and supported behaviour.
       Disadvantages:
  • It needs a form submit.

Approach 2: Using Submit button and redirect to same page

Add following snippet in processFormRequest

         if (pageContext.getParameter("ClearBtn") != null)
         {
           pageContext.forwardImmediatelyToCurrentPage(null, false, null);
         }

      Advantages:
  • Usage is specific to a scenario.
       Disadvantages:
  • It needs a form submit.
  • AM is not retained.
  • Its much slower compared to other approaches.

Approach 3: Using Button (not submit button) with JavaScript to achieve exact functionality like the standard clear button:

Write following code in ProcessRequest and it'll do the trick !!

    String clearFunc = "function clearFunction()";
    clearFunc = clearFunc + "{";
    clearFunc = clearFunc + "document.getElementById('SearchItem1').value='';" ;
    clearFunc = clearFunc + "document.getElementById('SearchItem2').value='';" ;
    clearFunc = clearFunc + "}" ;
   
    pageContext.putJavaScriptFunction("clearFunction",clearFunc);
    OAButtonBean clearButton2 = (OAButtonBean) webBean.findChildRecursive("ClearButton2") ;
    String javaSClear = "javascript:clearFunction();";
    clearButton2.setOnClick(javaSClear);

     Advantages:
  • No form submit, so disadvantages from the first two approaches are overcome.
  • Exactly same behaviour as Standard Clear Button.

     Disadvantages:
  • Use of Java Script, which is not recommended by Oracle. But we can’t live with it in Consulting J.
Reset Button: An approach worth ignoring:

A "ResetButton" bean cannot be trusted to perform, Clear button’s job.
A Reset button only resets the values on Page to last submitted/saved values (which needn’t be blank).

For Example: You enter "Prince" in Employee Name Search Criteria on Employee Search Screen and click on Go.
Irrespective of whether any results came or not, these submitted values are saved.

Now if you change the value on Employee Name search criteria to "Arvind".
Clicking on Reset Button will reset the value to ‘Prince’ and not to the desired null value.

Friday, August 19, 2011

Oracle Fusion Was Released in May-2011

Oracle Fusion was released in May 2011. It was released without much publicity.
As of now it is under controlled availability.

Lets all wait for the big bang announcements and updates from Oracle on the NEXT-GEN ERP Suite.

Auto Repeating Layout

This can be used to repeat the content of a region based on the number of rows in VO (data source) attached to the region.

You can implement an auto-repeating layout in one or two levels:

One Level - you create a container web bean and attach a view object to it, which results in the replication of the container children based on the rows in the view object.

Two Levels - you create a master-detail relationship between two lists and create a view link to join them. The outer list replicates its container children based on the master view object and the inner list replicates its container children based on the detail view object.

ARL can be read only or updatable. In case of updatable (with form elements), ARL should be based only on a single view object. A layout with form elements allows you to update any row and submit your changes to update the underlying view object

Limitations:
  • You cannot implement a two-level auto-repeating layout with a list containing Form Elements.
  • OA Framework does not support view objects (for the Child View Usage) with composite primary keys. The view object should have a single-column primary key.
  • The items under an Auto Repeating Layout should not be made hidden/rendered on a Partial Page Refresh (PPR) event.If such a scenario exists, on a PPR event, the page should be re-rendered by calling pageContext.setForwardURL().
  • LOV's are not supported in Auto Repeating Layouts (ARLs).
  • ARL cannot implement in the following container regions directly or indirectly:
    • Table
    • Advanced Table
    • Hide/Show
    • Switcher
    • HGrid
    • SubTabLayout
Properties for Implementing this:
  • Child View Instance
  • Child View Attribute
  • View Link Instance (in case of two levels)
Due to these limitations its use is limited :).

Saturday, August 6, 2011

MDS in Oracle Applications, OAF



Like all popular frameworks available in market today, OAF encourages use of declarative objects. The declarative objects are stored in XML. Following XML files are created in OAF:

  1. UIX Pages and Regions.
  2. OAF Personalizations
  3. BC4J Substitutions (EO, VO Substitutions)
  4. BC4J Components (Eo.xml, VO.xml, AM.xml, AO.xml, VL.xml)
First 3 types of xml files are stored in separate database called MDS.
4th type is directly deployed in BC4J Container.

MDS stores all information about the UI and Personalizations. Substitution is equivalent to Site level Personalization.


Metadata is data about data, data describing data.
Service is unit of work as part of business process written to a specification.

MDS stands for Meta Data Services and it is the declarative metadata repository used with OA Framework applications. It replaced the older AK Repository.

MDS is normal Oracle Database which contains metadata related to all seeded OAF pages in Oracle Applications. By storing Pages and Regions information in database, framework allows page attributes and properties to be changed easily via Personalization.

Why is it called a Service?
MDS is delivery of XML on demand.
OA Framework Page, Personalizations, and BC4J substitutions are stored and managed in MDS.


The meta data for Personalizations and Custom Pages/Regions can either be in the form of XML files on the file system or stored in the MDS repository. All the seeded UI components are present in MDS directory on product top. By exporting meta data into XML files on the file system, you can easily move those files to another system or simply login to a different environment and import those XML files to a new database instance.

Please note: OAF UI and substitution declarative components are retrieved from the MDS. Even though the xml files may exist in the EBS file system, they are not the objects that gets executed.

JDR_UTILS is a PL/SQL package that allows you to evaluate the list of personalization documents that are in your MDS repository.

Following are 4 tables in the MDS Repository:


TableDetails
JDR_PATHSStores document paths, packages and there parent child relationship.
Primary Key: PATH_DOCID
JDR_COMPONENTSStores components on documents and OA Framework pages.
Primary Key: COMP_DOCID, COMP_SEQ
JDR_ATTRIBUTESStores attribute/properties of components on documents and OA Framework pages.
Primary Key: ATT_COMP_DOCID, ATT_COMP_SEQ, ATT_SEQ
JDR_ATTRIBUTES_TRANSStores translated attribute values of document components or OA framework pages.
Primary Key: ATL_COMP_DOCID, ATL_LANG, ATL_COMP_REF, ATL_NAME

Every Metadata definition of Page or Region is called document.



Following are few queries to understand the above tables:

Query1:
select distinct path_type 
from JDR_PATHS;
/
Result:
DOCUMENT (for pages and regions)
PACKAGE (for packages/directories path)

Query2:
select * 
from JDR_PATHS 
where path_name = 'HelloWorldPG' ;
/
-- Get familiar with the table structure, path_type will be 'DOCUMENT' for this record.

Query3:
select * 
from JDR_PATHS 
where path_docid = <path_owner_docid from query2>;
/
-- This record will point to the parent package/directory.

Query4:
select * 
from JDR_COMPONENTS 
where comp_docid = <path_doc_id>;
/
-- This will query components of the DOCUMENT. There are no records for a PACKAGE type.

Query5:
select * from JDR_ATTRIBUTES
where att_comp_docid = <path_doc_id>
and att_comp_seq = <sequence id of component whose properties you wish to check>;
/

Query6:
SELECT   jdr_mds_internal.getdocumentname (paths.path_docid) document_full_path,
               -- Fully Qualified personalized document name
               paths.path_name, -- Actual name of xml file
               --paths.path_docid,
               --paths.path_owner_docid, 

               -- Since All Personalized document reside in a package
               -- path_owner_docid will always point to a PACKAGE.
               --paths.path_type, 
               -- Path type is of two types PACKAGE and DOCUMENT
               -- PACKAGE indicates directory, DOCUMENT indicate xml file.
               -- This query will always retrieve DOCUMENT
               paths.path_seq,
               --attrs.att_comp_docid,
               --attrs.att_comp_seq, -- Always 0 because we are retrieving only page (top level)
               --attrs.att_name, -- Name of Attribute in personalization xml file
               -- Here we are querying for att_name = 'customizes'
               --attrs.att_seq, -- sequence at which attribute is occuring
               attrs.att_value -- value of the attribute here it'll be path of actual page

from        JDR_PATHS paths,
              JDR_ATTRIBUTES attrs
where      paths.path_docid = attrs.att_comp_docid
              -- This query will not fetch path_type= 'PACKAGE'
              -- As there are no jdr_attributes records for packages
              and attrs.att_comp_seq = 0 -- sequence value 0 indicates parent
              and attrs.att_name = 'customizes' -- indicates personalized documents,
              att_name = 'customizes' will always have att_comp_seq = 0
              and paths.last_update_date > to_date('01-JAN-11','DD-MON-RR')
                                                 -- Personalizations done in 2011
              and jdr_mds_internal.getdocumentname (paths.path_docid) like '%/function/%'
                                                 -- All Function Level Personalizations;
/

/*
because ATT_COMP_SEQ = 0; Result will ATT_NAME, ATT_VALUE pairs as mentioned in the first xml tag of the file.
Below is first xml tag from the HelloWorldPG from R12:
<page xmlns="http://xmlns.oracle.com/jrad" xmlns:ui="http://xmlns.oracle.com/uix/ui" xmlns:oa="http://xmlns.oracle.com/oa" xmlns:user="http://xmlns.oracle.com/jrad/user" version="9.0.3.9.1_1573" xml:lang="en-US" file-version="$Header: HelloWorldPG.xml 120.8 2006/05/25 13:15:58 atgops1 noship $">
*/

/*
For att_name = 'customizes' Att_seq is always 3, please check the 4rd attribute (count starts with 0 like arrays in our programming languages) from the below xml tag (This xml tag is from the personalization file of HelloWorldPG) :
<customization xmlns="http://xmlns.oracle.com/jrad" version="9.0.6.0.0_26" xml:lang="en-US" customizes="/oracle/apps/fnd/framework/toolbox/tutorial/webui/HelloWorldPG">
*/