How to add DBlink from Oracle EBS GL Responsibility

This post assumed that you have known terms such as FSG Report (Financial Statement Generator),  DbLink, tnsnames.ora

You want to create an FSG report from Database instance “UAT” to instance “PROD” (import the report) . The COA setup of both instance should be the same.

Only one step needed :

from GL Super User, navigate to (N)>Setup>System>Database Links

Fill like this :

Database : UAT

Connect String :(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.10.139) (PORT=1522)) (CONNECT_DATA=(SERVICE_NAME=UAT)))

Domain Name : WORLD

APPS username : APPS

APPS password : apps (use your password)

Done, You could try to import the FSG report from UAT instance to PROD instance

Note:

The connect string is the same connection string taken from client’s (e.g. your PC)  tnsnames.ora file

crontab file location

With Linux, when issuing command like crontab-e   or    crontab -l,  in some case it is beneficial for us if we had known their location.

well I tell just directly, they are located in /var/spool/cron  

those “crontab” files in /etc/cron* aren’t what I was looking for (it is not the same file as crontab -e)

In my case,  my  environment OS is under a VM machine and somehow, I don’t know why yet, each time I issued crontab -l   or crontab -e the CPU usage will go to 100% in 3 minutes right from a fresh boot.  I’ve tried to vi the file, but cpu becoming gradually unresponsive.. also,  I’ve tried to replace the file via FTP.  It still can’t be changed altough the file permission is 600 (I’m as root). 

So,

1. I cat the file (the filename is the same as the user who created the file)

2. make a new file named root2, which I changed the content according to my needs

3. rm -rf root

4. mv root2 root

5. chmod 600 root

 

Hope this post could be helpful under some circumstances..

Copy Request Group

When you want to make a new Concurrent Program that almost same with one of your exist concurrent program, you can use “Copy to” function to make it simple.

But when you want to make a new Request Group that almost same with one of your exist request group, there is no “Copy To” function. It’s a weary task when we must do it manually.

I can give you the script to copy the request group content to other request group. Before you run this script you have to make a new empty Request Group.

declare
cursor z is
select b.CONCURRENT_PROGRAM_NAME PROGRAMNAME,c.APPLICATION_NAME APPNAME from FND_REQUEST_GROUP_UNITS a
,fnd_concurrent_programs b
,fnd_application_tl c
,FND_REQUEST_GROUPS d
where a.request_Group_id=d.request_Group_id and a.REQUEST_UNIT_ID=b.CONCURRENT_PROGRAM_ID
and b.APPLICATION_ID=c.APPLICATION_ID
and d.request_Group_name=’All Reports’ — The Exist Request Group
and d.application_id=’140′
and d.request_group_id=’175′;
begin
FOR REC IN Z LOOP
FND_PROGRAM.ADD_TO_GROUP(program_short_name =>REC.PROGRAMNAME
,program_application=>REC.APPNAME
,request_group=>’All Reports (NEW)’ — The New Request Group
, group_application=>’Oracle Assets’);
END LOOP;
END;

Note
This script just do not copy Request Set,Application. or Function types.

Can’t Run Concurrent Program

When you run concurrent program,the program never complete with status pending or stand by. And it is the non ordinary situation in your oracle EBS.

First you must check in Administer Concurrent Program, the value of Internal Manager and Standard Manager.
If the actual of Standard Manager or Internal Manager =0, it’s mean something problem with your Concurrent Manager. You can chek in the log file why this happen. The log file location is in $COMMON_TOP/admin/log.

To Solve this problem you must restart concurrent program manager with adcmctl.sh script. The location of this script is in $COMMON_TOP/admin/scripts.
1. Shutdwon the concurrent manager with this command:
sh adcmctl.sh stop apps/appspswd
2. Make sure all service in Administer Concurrent Program is off.
In oracle form you can check ini Administer Concurrent Program Menu.
In server you can check LIBR process, that you have to kill.
3. After all service shutdown, you can start concurrent manager with this command:
sh adcmctl.sh stop apps/appspswd

After restart concurrent manager, you can check with run a concurrent program until completed normal. Or you can check the value of Standard Manager pending request must be turn down until 0.

Troubleshooting s_display value in $CONTEXT_FILE while running Accelerator Config Tool

OBA (Oracle Business Accelerator) is a relatively new product from Oracle Corp. It aim to boost up the setup process time of Oracle Applications via online questionnaire within a timeframe called Project Work Space (PWS). Once all question is submitted, then you can download the Setup Configuration Tool from Oracle Application that connect directly, wth the authetication system using approved CSI number, etc.

After you download the file, then you must continue the automated setup process. During the setup process, this tool will warn user about setup that need to be fixed, and you can choose to fix or to skip it. This process relatively take some workingdays. Once the automated Setup Configuration Tool is finished, the tools will provide you links about statistics in hours & minutes; what happened during the process; Errorred process; setup that you need to investigate and check again or setup that failed and need manual setup.

One of the error during running the config tool is s_display value in $CONTEXT_FILE doesn’t match the vnc or xmanager display value.
Once corrected, you’ll be told to run AUTOCONFIG and resume the Setup Configuration Tool. This error cannot be skipped as the Setup Config Tool will prompt you the same error message each time you resuming.

I checked inside the context file (Application context file, NOT the Database context file since display should have nothing related with the database). The value of s_display is already correct =[hostname]:1.0 ( Read metalink note 467549.1 ) the hostname is the server’s IP address.

You could do the trick using a broadcast and Xstart (from Xmanager) or from VNCviewer these application type is different from application like putty, WinSCP or secureCRT. Using Putty for example, when you close the window, your session is “over” in the server, but using VNCviewer, eventhough you have shutdown your PC, then if you login again, your server session is still “alive” unless you have issued command such as logout or reboot.

The trick is simple,  using VNC viewer, open a terminal then login using root. type :

$ export DISPLAY=[your_server_hostname]:1.0

$ xhost +

or

$ xclock

if the clock is displayed then you have done right, just shutdown apps services, run autoconfig and resume the Configuration Setup Tool.

note : to detemine your_server_hostname value, just type hostname in the terminal, the result is what should be set as s_display environment variable value in the $CONTEXT_FILE.

Reducing Excessive I\O for Large Joins via In-Line Functions

We often have to scan very large tables to produce simple, aggregated reports. A table could be several million rows in size. There is nothing we can do about having to read the table, but overheads associated with joining to other “description” tables can be substantially reduced. For example:

SELECT h.emp_no, E.emp_name, h.hist_type, T.type_desc, COUNT (*)
FROM history_type T, emp E, emp_history h
WHERE h.emp_no = E.emp_no AND h.hist_type = T.hist_type
GROUP BY h.emp_no, E.emp_name, h.hist_type, T.type_desc;

This statement will return the correct result and in a fairly efficient manner. If the EMP_HISTORY table is 5 million rows, the optimizer has to join both the HIST_TYPE and EMP tables 5 million times to finally return only a few hundred aggregated rows. On top of that, the descriptive columns are included in the output data set that then needs to be written to the temporary tablespace, sorted and aggregated. What can be done? Here is a suggestion:

FUNCTION lookup_hist_type (typ IN NUMBER)
RETURN VARCHAR2
AS
tdesc VARCHAR2 (30);
CURSOR c1
IS SELECT type_desc
FROM history_type
WHERE hist_type = typ;
BEGIN
OPEN c1;
FETCH c1
INTO tdesc;
CLOSE c1;
RETURN (NVL (tdesc, ‘?’));
END;

FUNCTION lookup_emp (emp IN NUMBER)
RETURN VARCHAR2
AS
ename VARCHAR2 (30);
CURSOR c1
IS SELECT emp_name
FROM emp
WHERE emp_no = emp;
BEGIN
OPEN c1;
FETCH c1
INTO ename;
CLOSE c1;
RETURN (NVL (ename, ‘?’));
END;

SELECT h.emp_no, lookup_emp (h.emp_no), h.hist_type,
lookup_hist_type (h.hist_type), COUNT (*)
FROM emp_history h
GROUP BY h.emp_no, h.hist_type;

The real beauty of this particular approach is that the lookup procedure calls happen only after the EMP_HISTORY table has been scanned and sorted. Only a few hundred, rather than a few million, lookups need to be executed. The amount of data written to the temporary tablespace and sorted is also substantially reduced. When all else fails and sheer table size is your worst enemy, try coding a few well placed inline functions.

Note: Inline SQL functions are not the answer to all problems and should be used carefully. If performance is being affected, investigate database views or stored procedures using PL/SQL cursor variables.

First step to setup employee is to set a system profile value

Before we go in this case I’m using a fresh-haven’t been setup yet R12.0.5.

In fresh-clean installed Oracle Apps (Not Vision Demo),  the default provided application user is sysadmin and oa_implementer.

Then if you look how these user are defined from System Administrator responsibility (N) > Security > User > Define, they don’t have employee yet (I mean those fields in the upper right :  Person, Customer,Supplier,E-mail,Fax) by default you should enter them in order to assign a user to an employee or position e.g. (for approval hierarchies,etc)

This is must be done by setup per module (Order Management, Purchasing, etc), but beforehand you must assign value to a system profile in responsibility level. This profile name is HR:User Type set its value to any value in the LOV, it is must not empty.

After you saved this profile,  you can setup employee in each module and then you can use your employee setup assigned to application user, just put the employee in the Person, Customer, etc fields (via System Administrator — Define User)

Troubleshooting Oracle Apps OC4J down with exit status 204

This is happened on Saturday around 11:30 AM and we resolve this until 03:00 PM..

Occurance : in the middle of training, connection to Oracle apps server is down.

We looked the server, someone has plug out the RJ45 cable.

The Solution came in two part : first, its about the wrong slot to plug the cable in. Second is about OC4J services down..

First, we plug the RJ cable to the server..

we try to ping from client to server : host unreachable.. we even try to use another cables, even a cross cable

the server itself cannot ping its gateway.

Lately we found out that we’ve plug in the RJ45 cable to the wrong NIC card slot after some one plug it out..

there are 2 slot : eth0 (active) and  eth1 (inactive), we use only the eth0

after plugged in the cable to the eth0 NIC we’re able to ping from server to client, back and forth.

Beforehand we’ve changed the eth0 IP address, Gateway, with DB tier services  is up and Apps tier services  is down,  restart network services, changed /etc/hosts file

we set the IP address and gateway to the state of before changes are made and reboot the machine.

We startup the DB tier service, its running well but took a long time to mount the Oracle 10G database.

2nd trouble occured, that we cannot startup all Apps tier services. We’ve tried torun  autoconfig both the apps tier and DB tier but these attempts haven’t solved the problem.

looking into the log file,  we found that something is wrong, OC4J cannot start..

we run” adocorectl.sh status” to check the OC4J status, yes from OC4J total of 4 services, 1 is down.

OC4J oafm N/A Down
OC4J forms N/A Down
OC4J oacore N/A Down
HTTP_Server HTTP_Server 29760Down

we googling a little and found this steps is solving the problem :

1.Stop Oracle Apps Tier (Stop Oracle Apps Servies)

2. cd $INST_TOP/ora/10.1.3/j2ee

rm -fr oacore/persistence/*
rm -fr oafm/persistence/*
rm -fr forms/persistence/*

3.Start Oracle Apps Tier (Start Oracle Apps Servies)

Yes ! Apps Tier is all running now. yipee.. but 3 hours (too long) become an instant DBA with Iman Abdulah Siswosapoetro..

Thanks to Iman and Joben the Master..

VNC viewer troubleshooting, cannot pop up TWM menu

This case is intended that you have installed vncviewer in your PC and after perform sucessful login, you cannot pop up the TWM menu

e.g. 192.168.10.195:1 type your password, and log in, the vnc window will appears with grey background

When you encounter this problem, restarting the vncserver service only wouldn’t solve the problem, or if you haven’t know the way vncviewer works, you try as I’ve try, installing (fresh) vnc viewer in another PC the vncviewer display the same behaviour.

[root@apps sysconfig]# vncserver -kill :1
Killing Xvnc process ID 19955
[root@apps sysconfig]# vncserver :1

Then you can restart the vnc service by

[root@apps sysconfig]# service vncserver restart

now you can try again to login from vnc viewer from your PC, the TWM menu should appears

How to check applied Patches in a running Oracle Apps with query

When you caught bugs in oracle Apps, you would want to lookup metalink.oracle.com

and, if necessary you would like to create SR (Service Request) via metalink too.

Usually Oracle’s Customer Support Officer will ask you the patches that have been applied to your current APPS before he or she give you workarounds to be tried.

Here is the easiest way to identify those installed patches in your APPS :

login to APPS schema and perform query :

select distinct patch_name from ad_applied_patches;

select distinct patch_name from ad_applied_patches;