How to display both Vertical and Horizontal Scroll Bars in Detail Block in Oracle Forms 6i?

Problem
A Detail Block in Oracle Forms 6i with many fields in tabular format needs not only a Vertical Scroll Bar, but also a Horizontal Scroll Bar so that we can see all the information we need for a Record.
But the problem is that a Data Block supports only one Scroll Bar at a time. We can choose to use or not a Scroll Bar, setting the Show Scroll Bar property to Yes or No at the Data Block level. If the Scroll Bar will be displayed, we can establish its orientation setting the Scroll Bar Orientation property to Horizontal or Vertical. So how to display both Vertical and Horizontal Scroll Bars in Detail Block in Oracle Forms 6i?

Solution
Create the Detail Data Block setting Show Scroll Bar property to Yes and Scroll Bar Orientation property to Vertical. Add some fields from the Detail Block on the Content Canvas.
Create a Stacked Canvas and put the other needed fields from the Detail Block on it. For the Stacked Canvas set Show Horizontal Scroll Bar property to Yes.

Solution description with an example
This example uses the HR schema. The Master Block contains the departments and the Detail Block contains all the employees from the department displayed in the Master Block. Suppose you already have created the Master Block based on departments table.

Step 1
Create the Detail Block based on employees table. Choose to display only a few items from this Data Block (for example, only first_name and last_name). Set the Number of Records Displayed property to 10 (or other value grater than 1). Set the Show Scroll Bar property to Yes and set the Scroll Bar Orientation property to Vertical. Then move the Scroll Bar object in the right side of the frame, like in the next image:

Step 2
Associated with the Content Canvas make a Stacked Canvas and place it on the Content Canvas in the space between Last Name Item Text and the Scroll Bar object (the green rectangle in the previous image). For Stacked Canvas set the Show Horizontal Scroll Bar to Yes.

Step 3
Place in the Stacked Canvas all the fields that you need from Detail Block, like in the next images:

Content Canvas

Stacked Canvas

The Text Items from the Detail Block placed in the Content Canvas will be synchronized with the Text Items placed in the Stacked Canvas.

How to alternate row colors using Conditional Formatting property?

It is useful to highlight some parts of a report to get the attention when some conditions are met: some sum is greater then a specific value, something is on minus etc.

Oracle Report Builder offers two ways of doing this in a report:

- With Conditional Formatting, attributes like font, text color, border, fill color can be modified when some condition is true without programming;

- Format Trigger property offers more flexibility because you write your own code to perform conditional formatting.

The next report is showing all the responsibilities from Oracle Applications that starts with ‘Oracle’. The report is based on this query:

select frt.responsibility_id, frt.responsibility_name,

fr.start_date responsability_start_date, fr.end_date responsability_end_date

from fnd_responsibility fr, fnd_responsibility_tl frt

where frt.responsibility_id = fr.responsibility_id

and frt.responsibility_name like 'Oracle%'

order by frt.responsibility_name;

The colors for the rows of the report must be white and light blue. For this, a Summary Column (in this case it has the name row_no) must be created for counting and keeping the row number (count the responsibility_id because it is never null):

We want odd rows to be light blue and the even rows to be white. A Formula Column named odd_no must be created to check for each row if row_no has an odd or an even value:

In Layout Model, for each field from the row in the repeating frame, create a new formatting rule with the Conditional Formatting property: if odd_no equals with 1 then Fill Color will be light blue.

The report will look like this:

How to load data from a text file into database table using a SQL*Loader Request in Oracle Applications?

---

Create the text file containing the data and the control file (with ‘.ctl’ extension).
Then create a request in Oracle Application. This request must use as Execution Method SQL*Loader and must have a parameter for the text file path.

---

Solution description with an example
Step 1
The table test which will be populated with the data from the text file has the following columns:
Column Name--Nullable?---Data Type
X---------------No----------NUMBER
Y---------------Yes---------VARCHAR2(20 BYTE)
Z---------------Yes---------DATE

The sequence X_S is defined for column X:
CREATE SEQUENCE X_S
MINVALUE 1
INCREMENT BY 1
START WITH 1
NOCACHE
NOORDER
NOCYCLE ;

Name the text file with data to insert test.txt.
Place this file in $APPL_TOP\app_name\version\txt\ folder.
Suppose text file contains something like this:
,"aaa",10-APR-2008
,"bbb",11-APR-2008
,"ccc",12-APR-2008
,"ddd",13-APR-2008
,"eee",14-APR-2008
,"fff",15-APR-2008
"aaa", "bbb" ,"ccc" ,"ddd" ,"eee" ,"fff" are for column Y and 10-APR-2008,
11-APR-2008, …, 15-APR-2008 are for column Z. There are no values specified for
column X because for this values it will be used the sequence X_S.

Step 2
Name the control file test.ctl.
Place it in $APPL_TOP\app_name\x.y.z\bin\ folder.
The control file looks like this:
LOAD DATA
APPEND
INTO TABLE eco.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(X "eco.x_s.nextval",
Y,
Z)

Step 3
Define the Request in Oracle Application:
The Concurrent Program Executable has:
- Execution Method = SQL*Loader
- Execution File Name = test

The Request must have a parameter for the full path of the text file
( for example: C:\\oracle\visappl\eco\11.x.x\txt\test.txt ).

Step 4
Assign the request to some Request Group.

Step 5
Run the request and check if the data was loaded successfully into the database
table.

---

Troubleshooting
If the log file shows the error SQL*Loader-524: partial record found at end of
datafile, the last line in the text file and the control file must end with an “Enter”.

---

Enterprise and its functions-Part 1

Years back ,Myself and couple of my friends joined a company ,A company that manufactures piston rings.

On the first day ,we were being introduced to different department heads Production,Stores,Purchasing,HR,Quality and Inspection,New product development,Sales and Marketing and planning.

So you got it ? A large Enterprise has many department/functions.We were very curious about our department allocation.I got allocated to shop-floor ,My friend Prakash got allocated to purchasing (Remember P for Purchase/Prakash)..

Though every department has it own functions/roles..For the success of the enterprise ,It is important the individual department effectively communicates with other department and this is what we call in the software packaged applications as seamless integration

Now ,Let us explore each and every department and its corresponding functions and a co-relation to the oracle eBusiness suite.

Taking first the purchasing department.
Unlike me ,Prakash enjoyed a white collar job in the purchase department and he is being approached by all department people to procure one or other thing.

For example..
HR department needs Uniform clothes,Stationery's,Gift coupons,Chairs etc etc.
Production department requires Raw materials,Machines
Quality department requires gauges,scales,specialised equipments used in Labs..etc
Stores requires items as monthly replenishment ..etc..etc and
Many more ...

How does a purchasing department fulfill these needs ? What processes they need to maintain for an effective procurement and how does the performance of the purchasing department is measured ?..Let us explore

A typical day-to-day activities(some) of prakash(P for prakash/purchase) includes ..
1.Collect all the Purchase Requisitions from the drop box
2.Call up vendors ,check availability,check price ,chose vendor and raise purchase
order
3.get approval for the purchase orders
4.Inform Vendors about the purchase orders ,send the purchase orders via
email/Post/collect in person
Just imagine how difficult,painstaking,cumbersome the task will be if you maintain everything manually/excel sheets or in a stand lone applications.

Let us explore the challenges/solutions/department sub functions in the next part
and co-relate to the oracle applications

How To - General Part 3

---

How to find a form name and version?
Having the Oracle Application started, a lot of useful information can be found starting from the Help menu of the application.
To find the current form name and its version, go to Help->About Oracle Applications…
Here, scroll down to Current Form section. Bellow is an example for Sales Orders form:
----------------------------------------
Current Form
----------------------------------------
Form Application : Order Management
Form Name : OEXOEORD
Form Path : /opt/oracle/apps/appl/ont/11.5.0/forms/US/OEXOEORD.fmx
Form Version : 11.5.555.1159.2
Form Last Modified : $Date: 2003/11/20 13:02 $

---

How to find a patch set version of an application?
Details about the patch level of the applications can be found querying the fnd_product_installations table.
The example below is for the Order Management module:

SELECT fat.application_name,
fpi.status,
fpi.patch_level
FROM fnd_application_tl fat,
fnd_product_installations fpi
WHERE fat.language = 'US'
AND fat.application_id = fpi.application_id
AND(fpi.status != 'N' OR fpi.patch_level IS NOT NULL)
AND fat.application_name IN
(SELECT fat1.application_name
FROM fnd_application_tl fat1,
fnd_application fa
WHERE fa.application_id = fat1.application_id
AND fa.application_short_name IN ('ONT'))

---

How to search for a Form Function in a responsibility?
Once logged on in Oracle Applications under a specific responsibility, you must have the Navigator in focus and then press Ctrl+L.

---

Ways to get profile values
Once logged on into Oracle Applications, go to Help->Diagnostics->Examine…
Enter the apps password.
In the ‘Examine Field and Variable Values’ form, choose as Block ‘$PROFILES$’ and as Field you can select the needed information like ORG_ID, USER_ID, etc.

Or, you can use the procedure fnd_profile.value:
select fnd_profile.value('ORG_ID')
from dual;

Or, having the Navigator in focus, pres Ctrl+L to search after 'Profile User Values'.

Or, from System Administrator responsibility, having the Navigator in focus, you can search after 'Profile System Values' pressing Ctrl+L.

---

How to find the last query executed in a form?
Once logged on into Oracle Applications, go to
Help->Diagnostics->Examine…
Enter the apps password.
In the ‘Examine Field and Variable Values’ form, choose as Block ‘SYSTEM’ and as Field ‘LAST_QUERY’.
The Value field will be populated with the last query executed.

---

How to - General Part 2

---

How to switch off the custom code?

In the Oracle Forms [under any responsibility]
Go to Help --> Diagnostics --> Custom Code --> Off

---

How to find the invalid objects?

Select object_name, owner from dba_objects where status like ‘INVALID’;

---

How to enable the HTTP Trace for HTML Applications ?

ieHTTPHeaders Trace : Gives information on the HTTP request that is being sent to the server from the browser.

Usage : Can be used to debug the HTML based error message.


Please follow the below instructions to get the ieHTTPHeaders trace.

You can download the free tool from this url: - http://www.blunck.se/iehttpheaders/iehttpheaders.html

This tool provides a plugin to Internet Explorer so that the http calls can be logged.

After installing the http headers trace,
Enable the trace exactly before the issue occurs
Trace can be enabled by View -> Explorer Bar -> ieHTTPHeaders.
Trace can be disabled by View -> Explorer Bar -> ieHTTPHeaders.

---

How to get the instance details like SID, Host Name, Apps Version and Database version from the back end?

select i.instance_name, i.host_name, f.release_name release, i.version from v$instance
i,fnd_product_groups f where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));

---

How to find the file version from the back end?
Use the below command to get the version of the file that resides in the APPS server, this makes things easier without using the UNIX command

select FILENAME , VERSION from AD_FILE_VERSIONS a, AD_FILES b where a.FILE_ID=b.FILE_ID and FILENAME like '';

It lists the various versions of Oracle Applications files (real files, not "pseudo-files"), that have ever been patched or executed in the Oracle Applications installation.
So The query result will be having more than one record, the highest version in that will be the correct file version of the file that resides in the APPS Server.
For more info, search in eTrm for the tables AD_FILE_VERSIONS , AD_FILES.

---

How To -General Part 1

How to enter in to Oracle Application using AppsLocalLogin.jsp by leaving password field blank?



Step no 1
In the Username field enter UserName/Password, then clcik on Login button, this works fine till R12.
-------------------------------------------------------------------------------------------------

How to get the instance details like SID, Host Name, Apps Version and Database version from the back end?

Step no 1
select i.instance_name, i.host_name, f.release_name release, i.version from v$instancei,fnd_product_groups f where upper(substr(i.instance_name,1,4)) =upper(substr(f.applications_system_name,1,4));

Sell and Cross sell more

How many times you wanted to shop Item A and ended up in buying A + items related to A.(Happily..!)

This happens to everyone ,and now with the technological advance,most of the sales solutions applications has the capability of cross-selling .Oracle is no exception to this ,Order management, istore ,Tele-sales ,contact center has an ability to cross-sell products to the customers.

What is this cross selling ?

By Definition,Cross selling is an ability to sell related items to a customer.(Example .If a customerbuys a mobile ,suggesting him to get a mobile case is a cross selling).

Cross selling should be an win-win situation for both the buyer and seller ,Effective cross selling requires not only an adequete information about the products ,but also an intelligence engine to enchance the cross selling to add an value to the customers.

Say for example ebay suggesting like "People who bought this ,also bought ...." can be quite value adding for a buyer.

Though oracle inventory maintains the inventory masters and other related items set up etc to support the cross-selling .It does has a limitation with the information storage and cross selling functionality in OM is not built around a sales intelligence logic.

Though this concept is not restricted in selling products ,Now-a-days this is widely used to promote contents ,web ..You should have many times seen this ....Readers who like this article also liked ...

PIP -Shop and Connect

With increasing acquisitions of products by oracle ,it is necessary to built integration between the products to align with the business process to achieve the oracle mantra of "BRINGING IT ALL TOGETHER"

Let us see an example of where these process integration pack will help.

Company A which has a global presence which is using SIEBEL as their CRM applications and uses oracle business suite for financials bookkeeping and Oracle transportation management (Previously G-LOG )Now it is vital to have a integration between peoplesoft and oracle ebusiness suite and GLOG and oracle.In this case a integration pack will help to integrate cross product applications to support the business processes

Who will be largely benefited from these PIP?

Customers who uses combination of applications like peoplesoft ,SIEBEL,Oracle E-business suite ,Agile PLM..will be benefited from these pre-built integrations.

Do we have PIP only for oracle E-biz and other oracle acquired products(SIEBEL,Peoplesoft ,AGILE ,GLOG)

No ,Oracle gives PIP even for AGILE to SAP

It is good to see oracle creating these PIP's to get a seamless integration