FND_ATTACHED_DOCUMENTS

FND_ATTACHED_DOCUMENTS is one of the largest tables in terms of number of rows. It contains reference of all attachments from all the iModules like iRecruitment, iSupplier, iSourcing etc. Here's the structure of the table

SQL> desc fnd_attached_documents
Name Null? Type
----------------------------------------- --------
ATTACHED_DOCUMENT_ID NOT NULL NUMBER
DOCUMENT_ID NOT NULL NUMBER
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_LOGIN NUMBER
SEQ_NUM NOT NULL NUMBER
ENTITY_NAME NOT NULL VARCHAR2(40)
PK1_VALUE VARCHAR2(100)
PK2_VALUE VARCHAR2(100)
PK3_VALUE VARCHAR2(100)
PK4_VALUE VARCHAR2(150)
PK5_VALUE VARCHAR2(150)
AUTOMATICALLY_ADDED_FLAG NOT NULL VARCHAR2(1)
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
ATTRIBUTE_CATEGORY VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(150)
ATTRIBUTE2 VARCHAR2(150)
ATTRIBUTE3 VARCHAR2(150)
ATTRIBUTE4 VARCHAR2(150)
ATTRIBUTE5 VARCHAR2(150)
ATTRIBUTE6 VARCHAR2(150)
ATTRIBUTE7 VARCHAR2(150)
ATTRIBUTE8 VARCHAR2(150)
ATTRIBUTE9 VARCHAR2(150)
ATTRIBUTE10 VARCHAR2(150)
ATTRIBUTE11 VARCHAR2(150)
ATTRIBUTE12 VARCHAR2(150)
ATTRIBUTE13 VARCHAR2(150)
ATTRIBUTE14 VARCHAR2(150)
ATTRIBUTE15 VARCHAR2(150)
COLUMN1 VARCHAR2(30)
APP_SOURCE_VERSION VARCHAR2(255)
CATEGORY_ID NUMBER
STATUS VARCHAR2(30)

In our largest environment, this table has 12752217 rows. We have had performance issues with patches which try to update this table. A datafix patch specifically developed by Oracle for our environment hangs while executing this statement:


UPDATE fnd_attached_documents fad
SET (created_by, last_updated_by) =
(SELECT nvl(paha.created_by,fad.created_by), nvl(paha.created_by,fad.last_updated_by)
FROM pon_auction_headers_all paha
WHERE fad.pk1_value = paha.auction_header_id
)
WHERE fad.entity_name = 'PON_AUCTION_ITEM_PRICES_ALL'
AND fad.created_by = 1
AND exists
(SELECT '1'
FROM pon_auction_item_prices_all pai,
pon_auction_headers_all paha1
WHERE fad.pk1_value = to_char(pai.auction_header_id)
AND fad.pk2_value = to_char(pai.line_number)
AND paha1.auction_header_id = pai.auction_header_id
AND pai.rowid BETWEEN l_start_rowid AND l_end_rowid);

We have waited for 24 hours before aborting the patch. Oracle is yet to give us a good reason why this query hangs.

Forms doesn't launch from self service

This is one of my favorite problems. I have posted about this problem before. The problem exhibited is same but the cause is always different. Here's the latest one I found. In one of the releases last year, I had tried to push Sun JRE client and replace Jinitiator. At that time, Sun JRE client was an early adopter program and was not certified. During testing, the team faced the lost focus problem and there was so much hue and cry that it was abandoned. In 2008, a new release and a new clone, I am told about a problem where Jinitiator doesn't launch on clicking any of the forms based options. Strangely it works on some PCs and doesn't work on others. It works on all DBA's PCs but doesn't work on the PCs of the people who have to do the actual testing. One common thing I noticed is that on the PCs where this was working, it was working with IE but not with Firefox. Launching with Firefox would give the message, you require to install Sun JRE 1.5.0_07. After almost half a day of trying out different stuff, Akhilesh suggested that we check the appsweb.cfg file and found that it had the plugin=jdk instead of plugin=jinit. Once this was changed to plugin=jinit and Apache was bounced, it started working for everyone. And yes it started working in Firefox browser too. A quick comparison of the context files revealed the variable s_sun_plugin whose value was set to jinit in a working instance and set to jdk in case of this instance.

Sametime meeting doesn't launch with browser error code -11

Sametime is an instant messenger from IBM/Lotus which is used in many organizations as the chat application on the intranet.

Akhilesh pinged me today as he was unable to launch sametime meeting. Sametime would error out with browser error code -11. I googled for this and found the answer in Sametime forums. The problem occurs if you have changed your default browser from Internet Explorer to something else, say Firefox. Since older versions of sametime do not work with firefox browser, they complain about it by throwing browser error code -11. If you reset your default browser as Internet Explorer, then the problem is resolved. To do this, open Internet Explorer - Tools menu - Internet Options - Programs tab. Here you'll find options to make IE the default browser. In IE7 you have a Make Default button and in IE6 there is a checkbox for checking whether IE is the default browser every time it is started. Use these options to make IE the default browser. Try launching the sametime meeting after you have made IE the default browser.

Workflow mails not moving after a fresh clone

After a fresh clone, notification mails sent through Workflow Notification Mailer were not going out. These were the steps taken to fix this issue:

a. Shut down the workflow listeners.

b. Follow the Note 372933.1 and recreate the wf_notification_out queue as follows:

sqlplus apps/ @$FND_TOP/patch/115/sql/wfntfqup.sql APPS
APPLSYS

c. Start up the workflow listeners.

The note 372933.1 describes How to purge e-mail notifications from the workflow queue so the e-mail is not sent.

I found another metalink note 432663.1 (Best Practice for Enabling Email Events in an Oracle EBS Testing Environment) authored by an Oracle customer which recommends:

Infrastructure Setups (One-time)

1. Created a generic email account TestOracle@yourdomain.com. In our case, we use Microsoft Exchange/Outlook with Active Directory.

2. Set the permissions of the INBOX folder of the generic email account to allow a development team distribution list (DL) to have OWNER privilege.

a. It's easier to use a distribution list to open up and manage access to the INBOX folder. This is an added benefit as it exposes the whole team to email activity generated during testing.

b. The OWNER privilege will allow any test team member of the distribution list to read and reply to Oracle generated email notifications.

3. If running Purchasing, we take the additional steps of setting up these default usernames in the system. By doing this in production, we establish default testing accounts. In production these accounts are disabled and re-enabled only in our testing environments. How to structure these accounts as people within the Purchasing Hierarchy is beyond the scope of this document. Listed below are some suggested people and levels:

TEST_APPROVER I
TEST_APPROVER II
TEST_APPROVER III
TEST_BUYER
TEST_REQ

Post Cloning Setups (After Each Clone)

There are numerous post cloning activities that are performed on an instance taken from the production copy. Below are the specific steps we used to enable email events and the use of a generic email account.

1. Update wf_notifications

set status = 'CLOSED',
mail_status = 'SENT',
end_date = sysdate;

2. Update fnd_user

set email_address = 'TestOracle@yourdomain.com ';

3. Update per_people_f

set email_address = 'TestOracle@yourdomain.com ';

4. Update po_vendor_sites_all =

email_address = 'TestOracle@yourdomain.com',
remittance_email = 'TestOracle@yourdomain.com';

5. Purge Workflow data :

Run the "Purge Obsolete Workflow Runtime Data" concurrent program

6. If test usernames are implemented, re-enable these accounts with the following code snippet:

 UPDATE apps.fnd_user
        SET last_update_date = SYSDATE,     -- set update date
            last_updated_by = -1,           -- set to ANONYMOUS
            password_accesses_left = NULL,           
            password_lifespan_accesses = NULL,       
            password_lifespan_days = NULL,
            end_date = NULL                 -- activate user account
      WHERE user_name = 'TEST_APPROVER I'
         OR user_name = 'TEST_APPROVER II'
         OR user_name = 'TEST_APPROVER III'
         OR user_name = 'TEST_BUYER'
         OR user_name = 'TEST_REQ'

7. Alerts can be selectively disabled or changed based on your preferences and the type of alert.

Using the generic email account

Based on the version of Microsoft Outlook, the command to open another user's folder might be different.

File > Open > Other User's Folder

Summary

These simple steps allow us full use of email services in our testing environments and provide our developers with testing accounts for use in Requisition/PO creation, approval and generation.

I am pretty sure if the DBAs had purged the workflow data and cleaned up workflow tables as suggested above, this problem would not have occurred.

Reverse patch 5307530

Patch 5307530 is a Receivables patch which is recommended in April 2006 Critical Patch Update. This patch can be applied on an instance where you have already applied Financials Family pack G. As an oversite, this patch was applied on an instance where Financials Family pack F was installed. As a result this patch had to be reversed. Now we all know that there is no official way to undo a patch other than restoring from backup. However in the case of this patch, we found that it had only two file which were being executed. The first file was a .sql which was creating a view. The second file was a .pls file which was creating a package body. The easiest way to reverse this patch is to run the .sql and .pls file it has kept in the patch/backup directory. However there was no .sql file in backup directory. Only the .pls file was present.

This means that the view could be dropped. We ran the .pls and it failed complaining about a procedure init which was present in the package specification but not in the package body. So we compared the versions of specification and body with a fresh 11.5.10.2 instance. The package body version in the backup directory matched with the fresh instance. So we took the package specification from the fresh 11.5.10 instance and the package and its body compiled ok. This way the patch was reversed.

The incorrect extranet site mystery

Many enterprise applications encounter a major obstacle when they deploy multiple servers for scalability: the challenge of maintaining session persistence. A transaction typically consists of several TCP connections between the client browser and the servers. Once multiple servers are deployed, connections for a given transaction could go to any of the servers. While many load balancers solve the problem of balancing load across multiple servers, not every one supports the different persistence needs. Because persistence by definition requires the load balancer to ignore the load conditions on the servers and select a server based on persistence rules, the trick for the load balancing products is to ensure that the required level of persistence is met without breaking load balancing as much as possible.

For the last few days, Akhilesh contacted me regularly for an issue in one extranet environment. In this environment, we have iSupplier running in extranet with the URL pon.justanexample.com which connects to the E-Business Suite. This has two app tiers on extranet which are running Oracle Apps configured for DMZ. There is another Java application which is called supplier.justanexample.com. This is also load balanced on two servers. The BigIP box is same for both but their webs (pool) are different. The following steps can be taken to reproduce the problem:

From your home machine which is connected to internet, if you access pon.justanexample.com the E-Business Suite login page appears. After this if you access supplier.justanexample.com, then also you are directed to pon.justanexample.com

Go to another machine with a different public IP and try to access supplier.justanexample.com. The site opens with its login page. Now try accessing pon.justanexample.com, you'll get directed to supplier.justanexample.com

This was a consistent behaviour which could be reproduced easily. We had network guys, DNS guys scratching their heads, trying to figure this one out. Finally we reached out to a network expert who had previously solved such tricky problems. He did a trace of the network calls and found that the global IP was resolving correctly and requests were coming in on the correct IP address, but it was opening the incorrect application. He said maybe it is being done by the Application itself. Application teams denied this. So he checked from the other side, that is from the load balancer inside the DMZ and found that it was indeed going to the incorrect server. It was very strange. He checked the persistence setting in the load balancer and saw that it was set to source IP based persistence. Since all other environments had cookie based persistence, he changed the persistence to cookie based on a hunch. Voila, the problem was solved. After this, if you typed http://pon.justanexample.com, it would take you to E-Business Suite and if you typed http://supplier.justanexample.com, it would take you to that application's username/password page.

He said that it is possible that source IP based persistence was not taking the URL into account and was routing traffic solely on the IP. The BigIp load balancer would check the source IP from which the your first request came for a particular application, and would always send any subsequent request coming from your IP to the same application disregarding the URL you were trying to actually reach. This could be a bug in the load balancer. We are not really sure about this. Changing the persistence method to cookie based, fixed the issue.

There are two ways to do cookie-based persistence: cookie based switching and cookie hashing. In the first approach, the real server sets a cookie value that indicates to the load-balancing switch which real server a connection must be directed to. In the second approach, the load balancer can hash on the entire cookie string to select a real server. Once the load balancer selects a real server for a given hashing value, it will stick with that real server for all such traffic.

I am not sure which method is used by BigIP to do the cookie based persistence, but it sure avoided the problem we had with source ip based persistence.