Session Values - putSessionValue vs putSessionValueDirect

• Values set with putSessionValue function are cleared when redirected to homepage, but values set by putSessionValueDirect are cleared by logout, session invalidation and manual removal only.

• putSessionValue can store only String, Number or Date but putSessionValueDirect can store any object.

• Use of putSessionValueDirect is not recommended.

• putSessionValueDirect is typically for passing information between self-service pages and non self-service pages.

• Puts a value on the servlet session directly (through HttpSession.putValue(String name, Object value)) without associating it with a specific transaction ID.

Note: putSessionValueDirect has been used in Multple Transactions in One OAF Page.

Note: Session is not preferred place to store and pass values between pages. Refer to OAF: Passing Parameters, Encryption, Encoding.

OAF: Passing Parameters, encryption, encoding

OAF: Passing Parameters between pages

Following are three common means of passing parameters between pages:

• Request
• Transaction
• Session

In addition the values set in the View Attributes are available to all pages participating in the transaction.

Request :

• Short-lived object created for each HTTP request.
• It contains following application state:
• URL Parameters
• Updatable Field values (Message Text Input etc) and Hidden Field Values (form values) in case of post
• Event trigggering bean and the action in case of post.
• Request values are accessed using OAPageContext.getParameter() methods.
• In OAF, we don't interact with Request directly, we interact with Request through PageContext.

Ways to pass parameters in Request:

• Adding in URL: We can specify parameters as literal values or token substituted values (mapped to VO Attributes). Following are the examples:
• OA.jsp?page=/xxabc/oracle/apps/xxabc/custommodule/webui/CustomPG&order={@OrderNum}
• OA.jsp?OAFunc=XXABC_ADM_SUPP_ENGR&asset=123

• OAPageContext.putParameter()
• Values are not technically added to request, but are stored in a special page cache.
• Equivalent to HttpServletRequest.setAttribute()

• Hidden fields (form values)

• Passing parameters in Hashmap using setForwardUrl().

Passing parameters in Hashmap is the better approach for following reasons:

1. URL has size restrictions.
2. URL is visible to users.
3. putParamater() and hidden fields are not stored in Request as parameters, thus if we navigate with Breadcrumbs or for other reasons in case the webBean hierarchy needs to be reconstructed, these will not be available.

Transaction:

Transaction has wider scope than Request thus its not preferred approach.

• OAPageContext.putTransactionValue()
• OAPageContext.getTransactionValue()
• ((OADBTransactionImpl)getTransaction()).putValue()
• ((OADBTransactionImpl)getTransaction()).getValue()

Session:

Again, a wider scope than Transaction and not recommended.

• pageContext.putSessionValue();
• pageContext.getSessionValue();

Using VO Attributes for passing values

Values stored in VO attributes are available in all pages within the transaction with same Root AM.

URL Parameters : Encryption and Encoding

When we are passing parameters in URL, following need to be considered:

{@Attr} - encodes. Changes Prince Kapoor to Prince%20Kapoor
{!Attr} - encrypts. Encrypts sensitive information.
{$Attr} - plain token substitution (no encoding or encryption)
{@@RETURN_TO_MENU} - Used for E-Business Suite Personal Home Page. Same as OAWebBeanConstants.RETURN_TO_MENU_URL.
{@@RETURN_TO_PORTAL} - Return the user to a launching Portal page. Same as OAWebBeanConstants.RETURN_TO_PORTAL_URL.

Show OA References

OAF Tip:

Right click on any page layout region in structure window, and click on "Show OA References".
Details of all OA Components being used in the page will be shown including the VO attributes, and Attribute sets.

ValidateEntity, NewRowState and Commit

Below is stated from Developer's Guide:

 

Entity Object Initial / New State

By default, entity objects are created with the row state of STATUS_NEW, and BC4J adds them to its validation and post listener lists. In this case, any event that triggers a validation or database post sequence includes these entity objects.

As stated in the OA Framework Model Coding Standards, always circumvent this behavior by explicitly calling the setNewRowState(STATUS_INITIALIZED) method on its containing ViewRowImpl immediately after you insert the newly created row. This sets the state of any associated entity objects to STATUS_INITIALIZED.

When you do this, BC4J removes the corresponding entity objects from the transaction and validation listener lists, so they will not be validated or posted to the database. As soon as the user makes a change (an attribute "setter" is called), the entity object's state changes to STATUS_NEW, and BC4J returns it to the validation/post lists. You can also call setNewRowState(STATUS_NEW) on the ViewRowImpl to change the state manually at any time.

Code Behaviour:

Case 1: Row has been modified in the UI before submit. Row State could be anything. 

• The RowState becomes New.
• ValidateEntity will get called before processFormRequest().

Case 2: Called row.setNewRowState(Row.STATUS_NEW) after inserting a new row. Row has not been modified from UI.

ValidateEntity will not get called before processFormRequest() but will get called on commit and postChanges.

Case 3: Called row.setNewRowState(Row.STATUS_INITIALISED) after inserting a new row. Row has not been modified from UI.

• ValidateEntity will not get called before processFormRequest() or on commit or postChanges.
• The Row will be ignored by the Framework and removed from the Cache.
• Even the mandatory fields in the Entity Object are not checked.

Please note setting the row state (row.setNewRowState) should be done after adding the row to VO as recommended in the OAF Standard.

Though if you set the row state before adding it to the VO, it'll also work fine as long as you don't call any setter. The moment any setter is called row state changes to NEW.

Common Mistakes in OAF

I have conducted a few corporate trainings, it was a good experience. I received positive feedback, which encouraged me further. On introspection I remembered a saying, “A poor student has good chance of becoming a good teacher”.

Poor students have to struggle hard to learn things, and excel. They can easily relate to participant's problems. They have less chances of taking things for granted and assuming that one would at least know this much. They can ensure that one feels comfortable sharing less intelligent thoughts, they may even relate to many of them. He can understand its not uncommon to do common and silly mistakes while on the learning curve.

Debatable !! Certainly when one of the best teachers I know is my dear friend Deepak Srivastava, who is a topper in School, College, and University. There are exceptions

We are diverting here.

So, following are some of the mistakes I have done over the years and I am totally cool about it. Given a chance I don't mind making more mistakes and learn from them:


1. Calling ProcessRequest from ProcessFormRequest.

This was during my first OAF search page was around 7.5 yrs back. Search page was different from the traditional search pages. This was done in an attempt to write less code. Later realized that significance and semantics of these methods and took corrective measures.


2. Traversing VO Rows directly without any iterator.

Ideally one should prefer using a RowSetIterator for navigating the rows.
If we choose to directly use the VO for navigating the rows using vo.first(), vo.hasNext(), vo.next(), etc methods, this would result in current row getting changed. If same VO is being used to display the result on the webpage, the change in current row will result in different rows being displayed in result table or form.


3. Using of getRowCount()

Worst reason to use getRowCount() could be
if(getRowCount() > 0)
getRowCount() always executes the VO.

4. Calling setters from getters.

Get Methods are not expected to change the value of the property and state of the object.


5. Coding PPR without CO and AM code

This might be a surprise to few but there is a terrible way to code PPR, following are the steps:
a) Enable PPR on the UI widget.
b) Don't handle anything in CO, no capturing the event.
c) Don't write any code for PPR in AM
d) All code in VO getters. Whenever a PPR event is fired, getters of all the fields being displayed will be fired.

Please don't follow above steps for the PPR.


6. Went overboard with generic APIs.

A good programmer writes the logic with KISS principle in mind. Too many generic APIs will make more slightly difficult to read. They are important but one shouldn't write an API for every piece of logic needed.

7. Keeping constants in a separate utility java file (for using it at many places in multiple java files) Compiling only this file after changing value of constant.

Java replaces the reference of constants to actual value of the constants in the .class file.

When you change the value of the constant in the utility java file, and compile, it doesn't replace the value of constant in existing compiled class files where this constant is being used. Existing class files will continue to use the old value of the constants.

8. Creating more transient attributes in VORowImpl than using calculated attributes and keeping code in SQL Queries.

Many times you have a choice to either create a transient variable in VO and calculate the value in VORowImpl or modify the SQL of the VO and calculated the value in SQL.

Both approaches are fine but doing it in SQL is better practice, makes code more readable and easy to maintain.

9. Ignoring the sequence of Form Value and other fields 

At times we have form values and other fields on a page, all binded to same VO Instance and VO Attribute.

Read Only widgets like MessageStyleText don't submit their data, we need Form Values to submit the data to BC4J. 

We need form values in case of dependent LOVs or other dependent widgets. 
We also need form values to submit the data shown to users as readonly which is returned by LOVs.

Now what is more important is to know how and in what order the values in the UI widgets gets transferred to BC4J. The sequence is from top to bottom. In case if there are more than one field in UI for a VO Attribute, then the last UI widget that can submit the value will set the value the last time and thus this will get saved in DB.
I ignored this and in a rare but a badly coded page of mine, this was cause of a bug.

10. Not enabling Passivation on AM

Please do not forget to enable Passivation on your AM.

11. Ignoring the Tuning Tab in VO

In VO properties, there is tuning tab, setting the properties helps improve the performance, and shouldn't be ignored.

12. Not writing re-enterable processRequest Code

The processRequest() method may be re-entered to synchronize UIX with BC4J. Any initialization logic (VO execution, session variables, transaction variables based logic) must keep this in mind.

13. Prefer Oracle-style binding (:1, :2) over JDBC Style(?)

      Oracle Style binding avoids parsing SQL at runtime to do String replacement.

OAF Page Processing Part #1 - Key Terms

There are lot of terms that you'll encounter while learning about the OAF Page Processing, Request Handling etc. 

Following are some key terms that should help one understand OAF and Web development in quick time:

PAGE:

• A hierarchy of regions that is accessible using a URL. Pages generally represent a cohesive unit of functionality that may stand on its own, or work in concert with other pages.

REGION:

• Rectangular area that determines layout and UI component presentation in the user interface. Regions are containers which can contain both regions and items. Common region examples include tables, flow layouts, headers and so on.

PAGE LAYOUT REGION:

• A region that is a high-level layout element that is a template for the entire page. It supports several navigation and content areas for the creation of pages.

RENDER:

• To furnish; to contribute, to make available, to interpret, represent 
• When web beans are "rendered," UIX includes them in the web bean hierarchy. Furthermore, HTML is generated for the component and sent to the browser.

ORACLE E-BUSINESS SUITE USER SESSION

• A mechanism for keeping track of key Oracle E-Business Suite context information for a login.
• When the user logs in to an OA Framework application, the OA Framework creates an AOL/J oracle.apps.fnd.comon.WebAppsContext object and a browser session-based cookie that together keep track of key Oracle E-Business Suite context information like the current responsibility, organization id and various user attributes (user name, user id, employee id and so on). The Oracle E-Business Suite user session is associated with a servlet session, however, it has its own life cycle and time-out characteristics. 

CONTROLLER:

• In the context of a Model-View-Controller application (MVC), the controller responds to user actions and directs application flow.
• In an OA Framework application, this term is often used to specifically refer to the Java UI Controller associated with one or more regions in a page.

 PAGE CONTEXT:

• Each time a request is received for a page, OA Framework creates an OAPageContext that persists until a new page finishes processing. 
• This object contains parameters from the requesting page
• It also contains form fields if the request is a POST
• The OAPageContext object provides:
• Access to the server Application Module Class
• Methods to perform JSP forwards and client redirects\
• Access to session level Application context for
• User name
• Id
• Current responsibility and so on

WEBBEAN HIERARCHY:

• At page development time, we specify the bean hierarchy. Structure window shows the bean hierarchy at development time.
• The sequence in which region and items appear in structure window determines their position in web bean hierarchy.
• OA Framework reads the page's declarative metadata definition to create the web bean hierarchy.
• At page rendering time, the UIX framework processes the web bean hierarchy to generate the page HTML
  

PASSIVATION:

• The process of saving client state to a secondary medium (in the case of the OA Framework, database tables).
  

ACTIVATION:

• The process of restoring client state from a secondary medium (in the case of the OA Framework, database tables).

Passivation and activation are two phases of a resource management technique that reduces the number of bean instances needed to service all clients.

SERIALIZABLE:

• Process of saving an object's state to a sequence of bytes, as well as the process of rebuilding those bytes into a live object at some future time

Serialization is one way to save data, Passivation is a more generic concept.

PAGE BOUNDARY:

• The boundary between the completion of page processing for one page, and the start of page processing for another. When OAPageBean finishes processing a page.

REQUEST BOUNDARY:

• Once a response is returned, it is the end of the request processing or the boundary between one request/response pair and the next.

A page boundary is equal to request boundary except for JSP forward case where a single request can span multiple pages. 

Request parameters exists throughout the life span of a request, which can span across multiple page boundaries. Request parameters will be hanging around in case of JSP Forward.

JBO:

• Java Business Object
• BC4J was originally named JBO.

JRAD:

• Java Rapid Application Development
• Originally UI Level was called JRAD.

Multple Transactions in One OAF Page

Date: 20-May-2010  (I am little late for posting this article)

Background:

One very respected gentleman (who's blog we all would have visited while working on OAF.. keep guessing !!) asked a question in interview to a dear friend of mine.

How can we have two regions in a page in such a way that if you commit data in one region it shouldn't commit the data in other regions of the page?

In other words the requirement is to have two transactions in one OAF page.

First Reaction:

Shock!!

Root Application Module provides transaction context.

We can have only one Root Application Module in a page, then how can we have two transactions?

Why we need it?

I don't think we need it at all.

We can handle scenarios like this using combinations of Read-Only and Updatable VOs.

My strict recommendation is you try this only at home and never at work. You'll not be always available for your code's maintenance.

An observation that led to answer:

Application Behavior:

After some brainstorming, I remembered an observation while navigating between pages with retainAM=Y and with different Root AM, state of the page is getting retained.

Learning:

Somewhere system was retaining both Root AMs.

Two Root AMs means two transactions.

Test Case Tried:

On a relatively free day at work. I discussed this testcase with my colleague, one of the better technical resources I have ever worked with.

Tried passing AM object of one page to next page using Request and Session scope. Tried out things with both AMs in second page. 

It worked like magic.

Snippet:
------------

Page 1: Empty Page

Controller 1:

pageContext.putSessionValueDirect("xxfirstAM", pageContext.getApplicationModule(webBean));

//You can try passing it in Request also.

//We'll get this value in second CO and update data in VO's in this AM, call APIs by getting OADBTransaction

pageContext.forwardImmediately(<url of second page>);

Page 2: Transaction Page

Controller 2:

OAApplicationModule secondPageAM = pageContext.getRootApplicationModule();

OAApplicationModule firstPageAM =             (OAApplicationModule)pageContext.getSessionValueDirect("xxfirstAM");

// This way we'll be having two AMs in our Controller.

// Both these AMs will have their own transactions.

// Changes commited from one of them will not commit changes in other. 

OABodyBean

oracle.apps.fnd.framework.webui.beans.OABodyBean

public class OABodyBean

extends oracle.cabo.ui.beans.BodyBean

implements OAWebBeanContainer, OAWebBeanConstants

This bean is a container for the body of a document. It provides many supporting APIs for OAF.

Some Key APIs supported by this:

1. Blocking on Submit (Refer Dev Guide)

Whenever a submit action takes place on a page, subsequent submits can be blocked. When using a blocking on submit technique, when the submit action takes place, the cursor becomes busy and prevents any other submit action until the current submit event has been handled. The block on submit behavior is not enabled by default on a page. However, For Partial Page Refresh (PPR) events alone, it is enabled by default.

OABodyBean bodyBean = (OABodyBean)pageContext.getRootWebBean();  

bodyBean.setBlockOnEverySubmit(true);

2. PPR Event Queuing (Refer Dev Guide)

By default, when a PPR event fires, all subsequent events on the page are queued and processed. For example, lets say you have a search page and one of the item in search panel is a textbox with the PPR. If you enter some information in the textbox and click on Go. It'll first execute the PPR and then it'll process the Go button click.

To disable this feature at site level, set the value of the profile FND: Disable PPR Event Queuing (FND_PPR_EVENT_QUEUE_DISABLED) to Y. In this case, when a PPR event fires, all subsequent events on the page are ignored.

To implement event queuing on a specific page or to update the behavior of event queuing on a specific page, add the following code to the processRequest of that page.


This will enable the event queuing.

bodyBean.setFirstClickPassed(true); 

 

3. Setting Initial Focus on a Field

Setting the initial focus removes the use of mouse to start typing and saves time in certain pages.

bodyBean.setInitialFocusId("FirstName");

4. Setting Page Dirty

bodyBean.setDirty(true);

To mark a page as Dirty.

bodyBean.setDirty(false);

To mark a page as non-Dirty.

bodyBean.isDirty(false);

To get if a page is dirty or non-Dirty.

5. Setting Javascript

body.setOnLoad("<JS>"); (To call JS function on page load.)
body.setOnKeyPress("<JS>"); (Sets an onkeypress JS handler.)

There are more similar APIs that support Javascript.

bodyBean.setFirstClickPassed(true); This will disable the event queuing.

UIX Bean and OA Webbean hierarchy

Behind every OAWebBean, there is a UIX webbean working behind the scene. 

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.

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.

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.

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

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

2. Specify View Instance property

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

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