Cursor Focus on OA page

Initial Cursor Focus is a highly under rated requirement in OA framework and mostly it is put on the back burner during design/coding. And there is no clear documentation for this feature and may be OA dev guide avoided it because we need to use Java Script for it; which is considered sacrilegious in OAF. We designed and deployed around 50 pages to production and realized the importance of cursor focus only when the users started whining about it. Hey, but they had a valid case, some times they open a page and scans a lot number immediately. But if the focus was on URL browser field, it will take them to different page. And worse, some users started complaining about carpal tunnel symptoms due to excessive usage of mouse. So when we started fixing the pages for initial cursor focus we found ourselves in a difficult situation for some pages like the one which have tab region or one with table region.

Simple messageText field.

Initial cursor focus on single row region is simple and straightforward.

String id = "myId"; // ID of the field
OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
bB.setInitialFocusId(id);

Tab region

We need to find the current active tab and then focus on the field for that tab.

OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
OASubTabLayoutBean tBean = (OASubTabLayoutBean)webBean.findIndexedChildRecursive("tabRegionId");
    if(tBean != null)
    {
      int tabIndex = tBean.getSelectedIndex(pageContext);
       if(tabIndex == 0)
         bB.setInitialFocusId("id1");
       else if (tabIndex == 1)
         bB.setInitialFocusId("id2");

Table region

This is tricky and there is no direct OA supported way of doing this. OA framework (UIX) suffixes N: to the table field id while creating the final HTML page. Based on this fact following javascript function will do the trick.

 // id is ID of your bean
 // rowIndex is the row number you want to focus.
 function setTableFocus(id,rowIndex)
 {
   for (i = 0; i< 100 ; i++)
   {
    var bean = document.getElementById('N' + i+ ':' + id + ':' + rowIndex);
    if(bean != null)
    {
       bean.focus();
       break;
    }
   }
 }

 
   OABodyBean bB = (OABodyBean)pageContext.getRootWebBean();
   pageContext.putJavaScriptFunction("focusTag",);
   bB.setOnLoad("setTableFocus('" + id + "'," + 1 + ")");  // focus on first row.

We created an utility class to handle all these scenarios. FocusMap.java. Read javadoc for its usage.

Sunday Night Cricket

Cricket and Election are the two most popular past time of India and when these two collide the world is ready to move heaven and earth to limit the damage. The previous sentence is a pastiche of news bytes I read from various sites which sums up the current situation of IPL. The money and glamor that IPL brings is much debated and scorned topic. But what is mind boggling is organizers insistence on keeping the same time slot as previous year so that viewers can watch it. Or so that media house can get maximum viewership and maximum advertising revenue. IPL is no doubt turning into a media staged event like American Football. In NFL, media has managed to create an aura of staged event and induced addiction so that viewers tune in not for the actual football match but for the program that is aired on particular time. And the extreme of that is Super Bowl where some people watch it just for Super Bowl ads and half time shows. Indian channels' obsession of filling in screen real estate with ads and 5th ball advertisement break has already pi**ed off many viewers. As IPL is drifting more towards Sunday Night Cricket mode, I wont be surprised to see when Punjabs' Brett Lee charges in full speed to bowl and suddenly umpire pops his left hand out, stops him and says "Sorry mate ..we are not on air yet ...you have to do it again ..".

Sagar Alias Jacky

I learned from my sister today that Mohanlals' much awaited Sagar Alias Jacky will be released this Thursday. Last time I was this eager and excited for a movie was for Rajnis' Sivaji. Hype and expectations usually doesn't bodes well for a movie. But Sivaji was an exception and I hope the same for SAJ. I came to know about SAJ when I saw the huge poster near Srikumar theatre in Trivandrum last December. Then I thought SAJ would a

Retake

An embellished modern retake of Iruthapatham Nutandu; in the same line as Don and Billa. See, no one would have complained such a venture, obvious I may add, because Irupataham Nutandu was easily the best candidate for such a much over-due remake from Malayalam stream.

Return/Revenge

A sequel with maybe Sekharan Kuttis' illegal son seeking revenge, more corrupt and cunning political characters, Lal surrounded with dozen obsequious croonies, winding dialogues while camera rotate around Lal. Boy! it would have been a train wreck which might even make Chenkol a better sequel.

Reloaded

Something like main characters are carried over with a new backdrop and supporting casts. Indian audience seems to have stomach for such super hero flicks as they made Lage Raho a bigger hit than MBBS.

And the promoters are not leaving any stones unturned to make it point that it is indeed reloaded. Yes Sagar Alias Jacky - Reloaded.

My expectations from the movie are

• Intro of Mohanlal. It must pull even the most skeptical of Lal fan out of his seat and scream. It should set the tune for the rest of movie and catapult me (an imbecile) to the new world of J.A.C.K.Y and stay me put there for the rest of movie.

• Indelible and goose pimple moments. If you will, I must add like the one from Irupatham Nootandu

• In the lift with Ambika were Lal non chalantly delivers the dialogue "Sagar alias Jacky" and walks away with signature background music.

• In customs' uniform after busting Sekharankuttys' cheap trick. "Eda nayinte moneay ...Sekharan kutty nee onnu orapicho nee ee nasraniyeye kanda divasam muthal ninte andhyam arambichu kazhinju ..now you start your count down COUNT DOWN





• Bust up with police after parking car in no parking zone (in front of AGs' office).

• Mario Puzzo style twists and a better climax. I was impressed with turn of events in the last scene of Big B and no doubt Neerad is from the staple of RGB.

I am planning to travel to Dallas to watch the movie first week. Fingers crossed. Hoping to be back with a rave review.

Watch full movie: Irupatham Nutandu.

Trailers:

Printing Word doc FND attachments

We recently came across an unique requirement of printing the Word document attachments along with a report. I googled and metalinked. But found no direct solution and there were few open Enhancement Requests open with Oracle. We finally found a way. The idea is very simple. Get the attachments BLOB stored in database to apps server using JDBC and print the file using soffice. But lotsa work must be done and most of them need an APPS DBA's help. This article outlines the steps but not in detail. You should spend some time based on your apps configuration to get it work.

Technical Overview of FND attachments.

All file attachments are stored in FND_LOBS table in file_data column. You can link this back to your attachment entity using the following query.

SELECT fl.file_data,
fdt.media_id,
fad.entity_name,
fdt.description
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
WHERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = 183
AND fad.pk2_value = 164217


Here we are getting the attachment details of Discrete Item Master (Entity_Name: MTL_SYSTEM_ITEMS) and primary key values PK1_VALUE(Organization_ID) and PK2_VALUE (Inventory_Item_id).

For your entity you can get the details by navigating to Responsibility: Application Developer and Menu Navigaton: Attachments > Document Entity and Attachments > Attachment Functions.

Steps to print the attachments

Step 1: Java program to bring attachment BLOB to apps tier. Link.
This program gets media id and brings the BLOB from fnd_lob to apps tier.

Parameters : 1:User name 2:Password 3:Host 4:Port 5:SID 6:MediaId 7:FileSuffix

Step 2: Download Open Office and configure it in apps tier. I used my Apps DBA's help for it and it is a linux expert job.

Vaguely we have to do the following.
a.Install open office on the server
b.Log in as the oraowner, start a X session and launch open office writer
c.From root run the following: (This will need to be run after reboot or added as a service)
$ unset DISPLAY
$ soffice –headless –accept="socket,host=127.0.0.1,port=8100;urp;" –nofirststarwizard


Step 3: Create a simple shell script to use Java program in Step 1 and Open Office in Step 2 to print the file.

#!/bin/ksh
# $Header: XXAttachmentPrint.ksh$
#
# Purpose:
# Copy a file from the db to a temp location and print it.
#
# $5 MediaId
# $6 FileName
# $7 PrinterName
#
# Set Application Environment Variables
export PATH=${PATH}:/usr/local/bin
export XXENVPREF=`echo ${TWO_TASK}|awk ' { print tolower($1) } '`

# intialize the apps context in linux. You must replace this with your logic for executing APPSORA.env from $APPL_TOP
. XXsetenv${XXENVPREF}.ksh
. XXSetAppsEnv.ksh


export MEDIAID=${5}
echo "MediaId=" ${MEDIAID}
export FILENAME=${6}
echo "FileName=" ${FILENAME}
export PRINTERNAME=${7}
echo "PrinterName=" ${PRINTERNAME}

# Change Directory to temp location
cd ${COMMON_TOP}/temp

# Execute the java code to extract file. You can read db user name, passwd host etc from concurrent program params 1-4 or read it from another
# file/ exported variables in instance.
java tempel.oracle.apps.xxtmg.crm.AttachmentReader apps `cat ~/APPSpswd${XXENVPREF}.txt` ${XXDBHOST} ${XXDBPORT} ${XXENV} ${MEDIAID} ${FILENAME}.doc

# Open Office print command
soffice -headless -pt ${PRINTERNAME} ${FILENAME}.doc

# Clean up extracted document
rm ${FILENAME}.doc

#
# End of File



Step 4: Seed the above shell script as a concurrent program in APPS.

Application Developer > Concurrent > Programs/Executable

Execution file name should be same as the script name in Step 3.

Step 5: Write a simple PLSQL program which based on entity name and PK value launches the Step 4 program by passing parameters for media id, printer and file name.
PROCEDURE print_mtl_attachments(p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER) IS

CURSOR c IS
SELECT fdt.media_id
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
HERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = p_organization_id
AND fad.pk2_value = p_inventory_item_id;

l_request_id NUMBER;
BEGIN

FOR c_rec IN c LOOP
x_request_id :=
fnd_request.submit_request (<app short name>,
<prog short name from step 4>,
<some desc>,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
FALSE,
c_rec.media_id,
'F' || c_rec._media_id || '-' || to_char(SYSDATE,'MMDDYYYYHH24MISS', -- unique file name,
<printer name>
);
END LOOP;

END print_mtl_attachments;

Step 6: Seed another concurrent program for the PLSQL in step 5.

Step 7: Add concurrent program in Step 6 to a request set containing your report.

When To Fuse

Oracle, by announcing Application Unlimited are suggesting customers to take an evolutionary path to Oracle Fusion applications. In layman terms, they are asking Customers to take their own time to move to Fusion Apps. Which certainly has failed to enthuse most of the customers and most of them will hesitate to adopt the Apps in the foreseeable future. There is a hope that Larry Ellison might pull a Steve Jobs in terms of time to market and convincing customers to embrace the new technology. But ERP is not a fancy utility device and they might even miss the oppurtunity. As far as I (Oracle Apps Technical Consultant), am concerned, the big buck question is when is the right time to start learning Fusion? Or with my current skill sets (read Apps 11i and OA Framework) how fast I will get out dated?

One thing is for certain. Fusion is for real. I met few of my old colleagues at Oracle who are currently part of Fusion Apps development. And based on it, insider information or rumor or whatever you want to call, but certianly from reliable resources

• Coding is almost over and it might hit the market (may be to privileged few) around October 2009.
• It is toned down version with minimum features.
• It is developed almost from scratch. And not using the database design of EBS 11i or 12.
  
• Lots of RAD and less coding.
• They faced huge number of issues with Fusion Tech stack and believes it is still evolving and might take some releases to stabilize.
• Entirey different from OAF. There are some similarities.
• Dont worry about learning Fusion tecnology now.
  

Oracle is also recommending most Customers/Oracle Apps Consulting Organizations to start learning Fustion middleware and related technologies like SOA, BPEL, BIEE etc. And also promises that future releases of EBS will be using these techologies.

I will try to make a very silly guess how long OAF will last and when we should start embracing the Fusion technologies?

Oracle EBS Timeline

1987 - Oracle started its application division
1993 - First client server application release
1997 - Oracle announces native Java support and promises new set of RAD tools in Java. Genesis of Jdeveloper.
1998 - Oracle announces EBS 10.7 NCA
1999 - Releases Jdeveloper 2.0
EBS - Releases 11.0.3
2000 - First release of EBS 11i (11i1 and 11i2)
First release of Oracle 9i Application server
2001 - Release 11i3, 11i4 and 11i5
Release of Oracle 9i JDeveloper
2002 - Release of 11i6, 11i7 and 11i8.
Used JTF-the mother of OAF*
2003 - Release of 11i9
Started using OAF in its products.*
2004 - Release of 11i10
Maybe first stable and ironically last release of OAF as after 11.5.10 Oracle internally stopped providing enhancements or additions to OAF.*
2005 - Announces Project Fusion after acquiring host of companies.
2007 - Release of 12
2008 - Release of Fusion middle ware
Announcement of Application Unlimited and Evolutionary path to Fusion.


Timeline reference:

http://www.oracle.com/oraclemagazine/20/o20timeline.html

http://www.oracle.com/support/library/brochure/lifetime-support-applications.pdf

http://apps2fusion.com/apps/oa-framework/197-brief-history-of-oa-framework-and-future

* No references available.


Some observations based on the above timelines.
- It took seven years (97-04) for OAF or to be precise web based ERP application technology stack to become stable internally. OAF started percolating to outside consultancy world only after 11i10.
- It took six years and seven major releases for Oracle to release a relatively stable first application (11i7).

And my silly guess.

2009 - First cut privileged release of Fusion Apps.
2010 - EBS new release with more features from Fusion middleware.
2011 - First market release of Fusion Apps. Stable Fusion middleware.
2012 - Migration path to EBS to Fusion Apps clear.
2013/14 - Few more release of EBS slowly migrating towards Fusion and few more Fusion Apps releases.
2015 - No more new EBS releases and customers slowly start embracing Fusion Apps releases.

So,

- We got another two years for first stable release of Fusion Apps
- We got three years before EBS start looking like Fusion Apps.
- We got a good six year before Oracle might stop making EBS releases or OAF is completely out of the scene.

And a big question mark looms around the success of Fusion Middleware.

I don't want to make any conclusions and leave the readers to make chocies.

SET DEFINE OFF

In the last post I mentioned about shell script for converting excel to insert statements. I came across one more problem with that task. The values in insert statement had "&" (ampersand) and the sqlplus was prompting to enter a value. There are slick ways to encode ampersand using sed, but the simplest one is to turn that behavior off in sqlplus. The following command will do the trick.

SET DEFINE OFF.

My first SED-AWK script

I have heard a lot about SED-AWK scripts but never had a chance to work on it. Recently I was assigned a mundane task of importing excel sheet to Oracle database table. Of course there are plenty of tools available in market but my case is little complex as the data I had was denormalized. So I must find ID value for some column and also some formatting/calculation was required for other columns. There are many ways to solve this problem but this time I took an approach of solving it using SED-AWK.

AWK Script to create insert statements.

/a*/ {
print "insert into <TABLE> (<COLUMNLIST>) values (";
for (i=1; i< NF; ++i)
{
    if ($i=="SYSDATE")
    {
         print $i",";
    }
    else
    {
        if (i >= 2 && i <= 7)
        {
            print $i"*3600,";
        }
   else
      {
           print "'"$i"',";
   }
 }
}
print "(select item_id from ic_item_mst where item_no = "$i"))";
print "/";}

Shell script to converts CSV to Insert SQL.

#
# CSV to INSERT SQL
# param 1 TABLE NAME
# param 2 COMMA SEPERATED COLUMN LIST
# param 3 awk script file
# parma 4 CSV values
#

USAGE="Usage: 0-ScriptName 1-Table Name 2- Comma seperated column list 3- awk script 4- values csv file";

if test $# != 4
then
  echo "$0: Error: Incorrect Arguments.."
  echo $USAGE
  exit 1
fi

cp $3 temp.awk

#
# Encode spaces with @@ token
#
sed 's/ /@@/g' $4 > temp1

#
# Replace , with spaces so that awk can read it
#
sed 's/,/ /g' temp1 > tempvalues.txt

#
# Replace table name in AWK script
#
sed 's/<TABLE>/'$1'/g' temp.awk > test.awk
rm temp.awk

#
# Replace column list in AWK script
#
sed 's/<COLUMNLIST>/'$2'/g' test.awk > final.awk
rm test.awk

#
# AWK and generate the insert stmt
#
awk -f final.awk tempvalues.txt > temp.sql
rm final.awk
rm tempvalues.txt
rm temp1

#
# Decode spaces back
#
sed 's/@@/ /g' temp.sql > insertstmt.sql
rm temp.sql

In the AWK script I have written code to multiply columns 2 to 7 with 3600 and I am getting ID value for the last column.

Example:

./ci.sh xxtmg_sl_std_maintenance "RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID" 
xsm.awk
final.csv

Sample insert statement from the output:

insert into xxtmg_sl_std_maintenance (RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID)
values (
'800',
0.5*3600,
0.75*3600,
1*3600,
0.2*3600,
0.25*3600,
0.33*3600,
'1913',
SYSDATE,
'1913',
SYSDATE,
'-1',
'LIB',
'SLITTER',
(select item_id
from ic_item_mst
where item_no = '0315MS600 000'))