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));