7 Ways for a Better LOG--My View..Suggestions Welcomed

Hi All,
we all use the log files for debugging issues by writting text into them when ever a exception occurs or to know the process flow of our code...Here are few steps that will make us to have better log and make our life easy during supporting phase..

7 ways for writing a Better Log
1.Always Display the Name of the package/procedure in the LOG File.
The main advantage of doing this is no need query the concurrent program name and executable to find the procedure of
concurrent program

2.It is always better to have a common package having the log function.So that every time we want to write into a log
there is no need to type entire fnd_file.put_line(fnd_file.log,'message'
it can be easily done like
xxab_util_pkg.log('message');
it is better to have out also in the same way...

3.If the code is running very long number of lines define a stage variables and set it at different levels..and the dispaly the stage completed value when ever the exception occurs...This helps to debug the issue faster...

4.Display all the derived values in the log

5.When ever you are displaying statements like no data found for any sqlstatments in the code display all the values passed to the SQL along with nodatafound or too many row exception.This inturn will reduce a lot of time during support...

6.Always make sure you attach the SQLERRM variable with the when other exceptions handled...and diplay the section name which is causing this exception
exception
when others then
fnd_File.put_line('Unhandled exception occured in XX_GL_INTF_PKG.Check_PERIOD:'||SQLERRM);

7.Always End the concurrent program either in ERROR or warning when ever a exception occurs in the programs unless it is a business requirement.

Entering Query Conditions in Standard Forms

Recently i want to query on a stadard form for records having reversal date as null
i remember that i need to give is null but it didn't work...some how i manager to do it from backend..Even i searched developer guide with no use...So how i get this from the form guide and i am publishing for easy reference..these things will be very useful..and makes our life easy..

1. to enter query by example
2. to recall the last query parameters
3. to Execute Query
4. Query : Count Matching Records (after putting in the query parameters)

Query Wild Card Characters
% one or more characters
_ exactly one character

Query Operators
#between and --need to give #
#is null
#is not null
> Greater Than
>= Greater Than or Equal
< Less Than
<= Less Than or Equal
= Equal
!= Not Equal

RETCODE & ERRBUFF

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....

Shell script for File Movement between Servers

From very long i used to get a common requirement that some system will place the files in a server.
the system should get the files and process them
for achieving this we need to have shell script which will connect to the server and ftp the files to the source server..
Plz find the sample script for doing this..
after writing the script we have to schedule the script in unix using a cronjob...

--Shell script starting
#! /usr/bin/ksh
HOST=egltest.test.co.in
USER=applprod
PASSWD=applprod

chmod -R 777 /egltestarch1/oracle/EGLTEST/test/coa/*.txt

exec 4>&1
ftp -nv >&4 2>&4 |&

print -p open $HOST
print -p user $USER $PASSWD
print -p bin
print -p lcd /egltestarch1/oracle/EGLTEST/test/coa
print -p cd /egltestapps1/home/test/coa
print -p mget *.txt
print -p bye

exit 0
--Shell script ending name it as ftpcode.sh

this shell script is saved in /egltestarch1/oracle/EGLTEST/dba/sh/ftpcode.sh
After writing the script we need to a cronjon entry..this entry will run the script on the schedule we have given..

for this
crontab -e
using vi editior commands to make this entry below..this will run the script every minute and log is the written to the file specified on the right side...



# ftp files from application server to database server
* * * * * /eglprodarch1/oracle/EGLPROD/dba/sh/ftpcode.sh > /egltestarch1/oracle/EGLtest/dba/log/ftpcode.log 2>&1

once the files are dowloaded make sure to delete or move to other folders to automate the FTP process...

Tracing techniques in oracle applications

Hi,This is one of the good documents from metalink..good reference document when you want to do tracing....

WHAT TO SET UP BEFORE GENERATING THE TRACE

These steps must be performed by the DBA on the database server.

1. Set TIMED_STATISTICS to TRUE.
For performance issues, make sure TIMED_STATISTICS is turned on,
before attempting to generate the trace.
Set the following in the init.ora file:
TIMED_STATISTICS=TRUE
OR
in SQL*Plus:
ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

2. Set the location of the trace output.
Set the following in the init.ora file:
USER_DUMP_DEST = <preferred directory for the trace output>

3. Create the PLAN_TABLE to hold the output of the explain plan. Run
the SQL script called UTLXPLAN.SQL to create this in the apps schema.
This script is usually in $ORACLE_HOME/rdbms/admin.

4. If the init.ora file has been updated, you must shut down and restart the database before the changes will take effect.
TYPES OF TRACE - HOW TO TURN TRACE ON
Regardless of the type of trace file you create, make sure you note the time
that you create it.

1. Form Trace

Toggle trace on/off on the form, to trace specific application functions.
Make sure you go in fresh (sign off/on to the application), since somequeries are cached and may not be executed on subsequent visits to theform.

From the menu, select Help..Diagnostics..Trace (Release 11i) to turn
trace on (when checked, it is on).
Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace
Perform the action to be traced.
From the menu, turn trace off, by selecting Help..Diagnostics..Trace

(it should now be unchecked).Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace

2. Concurrent Program Trace

A. This will turn trace on for each execution of this program.
In Release 11.0 and lli, check the Enable Trace checkbox for the
concurrent program on the Concurrent Programs form. After running the
program to be traced, make sure you uncheck the Enable Trace checkbox.
Select the System Administrator responsibility.
Navigation =
Concurrent -> Programs -> Define. Query the concurrent program you
want to trace. Check the Enable Trace checkbox and save.
OR
B. How to generate a raw trace file with binds and/or waits
for 11.5.10:
1. Log into applications as System Administrator and Navigate to the
System Profile Values Form. Select the profile called
Concurrent: Allow Debugging and change the value to Yes at the appropriate level.
2. Allow pop-ups on your browser.
3. Navigate to the Submit a New Request form and select a job and
enter all parameters for that job
4. Select the Debug Options Button and this will take you to Create
Debug Rule in Oracle Application Manager
5. Select the appropriate Debug Option Value for SQL Trace only and then check the box
6. Hit OK twice and then Submit the job
7. Raw trace file with options selected will be located in the
appropriate directory.

3. Database Level Trace
This will turn trace on for all processes that are running in the
instance and should only have to be used in Release 10.7, for
concurrent programs. (This has to be done by the DBA.)
Set the following in the init.ora file:SQL_TRACE=TRUE

Shut down and restart the database.After generating the trace file, shut down and restart the database with the original init.ora.

.
4. Report Trace
If you are on 10.7 and need to trace an Oracle Report, you can modify the report to turn trace on for that session.
a. Convert the report from rdf to rex:
$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd>> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
b. Edit the rexfile and search for the beforerep trigger in the report.
Locate the following code:
IF (:p_trace_switch = 'Y') THEN
SRW.DO_SQL('alter session set sql_trace TRUE');
END IF;
Comment out the IF and END IF lines.
c. Save the report.
d. Convert the report from rex to rdf:

$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd> \
> stype=rexfile source=REPORT_NAME.rex dtype=rdffile overwrite=yes

5. Self Service page (like a Forms trace, but for self service web apps)
a. Set the FND:Diagnostics profile:
Responsibility = System Administrator
Navigation: Profile > System
User: Enter User name
Query the Profile: 'FND:Diagnostics'
Set the 'FND:Diagnostics' profile to Yes at User level

b. Login to Self Service under the same user the profile was set for.
c. Turn Trace on:
Click the Diagnostic link at the top of the page
It shows two options: Show Log and Set Trace Level
Select 'Set Trace Level'
Click Go.
Select one of the following options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues
Trace with binds and waits - combines both of the above
Click Save.
d. Perform the action to be traced in Self Service.
Multiple trace files may be generated in the usual trace directory.
e. Turn trace off:
Select the Diagnostic link
Click on option: Set Trace Level
Note all of the trace numbers listed
Click Go
Select: Disable Trace
Click Save

WHAT TO DO AFTER GENERATING THE TRACE FILE

These steps should be performed by the DBA, on the database server.

1. Find the trace directory.
Get the location of user_dump_dest.
Log into SQL*Plus as the apps user.

select value from V$PARAMETER where name = 'user_dump_dest'
2. Find the trace file for your process.
Go to the directory you found in step 1 (in UNIX, use cd).
Look for a file (.trc) that was created at the time you started
your process (in UNIX, use ls -ltr).

3. Run tkprof with explain plan.
Go to a directory in which you have write privilege (in UNIX, use cd).

Run tkprof:

tkprof <full path to trace file> <output file name> explain=<apps username/apps password>

HOW TO GET AN EXPLAIN PLAN FOR A SQL STATEMENT

Sometimes you may have a need to get an explain plan for a specific SQL
statement. If you have the sql statement, you can get the explain plan for
it.
This should be run on the same instance that the sql statement came from.
In your apps account ---
Run the following script:
delete from plan_table
where statement_id = 'tmp'

explain plan

set statement_id = 'tmp'
for
<put sql statement here>
/
set pages 100
col operation format a36
col options format a11
col object_name format a30

select lpad(' ',2*(level-1))operation operation,

options, object_name
from plan_table
where statement_id = 'tmp'
connect by prior id = parent_id
and statement_id = 'tmp'
start with id = 1
and statement_id = 'tmp'
order by id
/
delete from plan_table
where statement_id = 'tmp'
/
commit;

HRMS Interface -Employee Creation and updation

Hi All,
this is basic to sink employee information between two instances....


CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTF_PROC
(retcode VARCHAR2
, errbuff VARCHAR2)
AS l_emp_num VARCHAR2 (30);
l_person_id NUMBER;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (30);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_employee_number VARCHAR2 (20);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_comment_id NUMBER;
lv_rec_exixsts VARCHAR2 (1) := 'N';
l_last_std_process_date_out DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (200);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_alu_change_warning VARCHAR2 (200);
l_acutal_temination_date_ewgl timestamp;
l_business_group_id number:=101;
l_ser_object_version_number number;
l_last_run_date timestamp ;
l_service_id number;
CURSOR c_emp_cur_hrms(c_last_run_Date date) IS
SELECT * FROM xx_hrms_intf_v@uat_new
where last_update_date>=c_last_run_Date or creation_Date>=c_last_run_Date;
BEGIN
select max(actual_completion_date)
into l_last_run_date from FND_CONCURRENT_REQUESTS fcr, fnd_concurrent_programs fcp where fcr.concurrent_program_id=fcp.concurrent_program_id
and concurrent_program_name='XX_HRMS_INTF'
and fcr.STATUS_CODE='C';
fnd_file.PUT_LINE(fnd_file.log,'last run successful date:'l_last_run_date); --Creating New Employee
FOR emp_rec IN c_emp_cur_hrms(l_last_run_date) LOOP
fnd_file.put_line (fnd_file.LOG, 'HRMS employee NO:' emp_rec.employee_number);
BEGIN
SELECT 'Y' INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = emp_rec.employee_number
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and business_group_id=l_business_group_id;
EXCEPTION
WHEN OTHERS THEN
lv_rec_exixsts := 'N';
END;
IF lv_rec_exixsts = 'N' THEN
hr_employee_api.create_employee
(p_hire_date => emp_rec.start_date ,
p_business_group_id => l_business_group_id,
p_last_name => emp_rec.last_name ,
p_first_name => emp_rec.first_name ,
p_title => emp_rec.title, p_sex => emp_rec.sex ,
p_employee_number => emp_rec.employee_number ,
p_date_of_birth => emp_rec.date_of_birth ,
p_person_id => l_person_id ,
p_assignment_id => l_assignment_id ,
p_per_object_version_number => l_per_object_version_number ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_full_name => l_full_name ,
p_per_comment_id => l_per_comment_id ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_name_combination_warning => l_name_combination_warning ,
p_assign_payroll_warning => l_assign_payroll_warning ,
p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'Creating New employee:' emp_rec.employee_number); ELSIF lv_rec_exixsts = 'Y' THEN
SELECT papf.person_id ,
papf.object_version_number ,
ppos.actual_termination_date ,
ppos.OBJECT_VERSION_NUMBER ,
ppos.PERIOD_OF_SERVICE_ID INTO l_person_id ,
l_per_object_version_number ,
l_acutal_temination_date_ewgl ,
l_ser_object_version_number ,
l_service_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE employee_number = emp_rec.employee_number
AND papf.person_id = ppos.person_id
and papf.business_group_id=l_business_group_id
AND SYSDATE BETWEEN EFFECTIVE_START_dATE AND EFFECTIVE_END_DATE;
l_employee_number := emp_rec.employee_number;
fnd_file.put_line(fnd_file.log,'person_id:'l_person_id);
fnd_file.put_line(fnd_file.log,'Object Version Number:'l_per_object_version_number); fnd_file.put_line(fnd_file.log,'EWGL Termination date:'l_acutal_temination_date_ewgl); fnd_file.put_line(fnd_file.log,'Last Update date:'emp_rec.last_update_date); fnd_file.put_line(fnd_file.log,'HRMS Employee Actual Termination Date:'emp_rec.actual_termination_date); f
nd_file.put_line(fnd_file.log,'Service Object Version:'l_ser_object_version_number);
IF emp_rec.last_update_date>=l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL and emp_rec.actual_termination_date is null THEN
hr_person_api.update_person (p_effective_date => emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_last_name => emp_rec.last_name
,p_first_name => emp_rec.first_name
,p_date_of_birth => emp_rec.date_of_birth
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'UPDATING New employee:' emp_rec.employee_number);
END IF;
IF emp_rec.actual_termination_date IS NOT NULL and emp_rec.last_update_date>=l_last_run_date THEN
l_last_std_process_date_out := emp_rec.final_process_date; hr_ex_employee_api.actual_termination_emp
(p_effective_date => emp_rec.effective_start_date
,p_period_of_service_id => l_service_id
,p_object_version_number => l_ser_object_version_number
,p_actual_termination_date => emp_rec.actual_termination_date
,p_person_type_id=>null
,p_last_std_process_date_out => l_last_std_process_date_out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning ,
p_pay_proposal_warning => l_pay_proposal_warning ,
p_dod_warning => l_dod_warning ,
p_alu_change_warning => l_alu_change_warning);
fnd_file.put_line (fnd_file.LOG, 'Terminating New employee:' emp_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND emp_rec.actual_termination_date IS NULL and emp_rec.last_update_date>=l_last_run_date THEN hr_employee_api.re_hire_ex_employee
(p_hire_date => emp_rec.effective_start_date ,
p_person_id => l_person_id ,
p_per_object_version_number => l_per_object_version_number ,
p_rehire_reason => ' ', p_assignment_id => l_assignment_id ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_assign_payroll_warning => l_assign_payroll_warning);
fnd_file.put_line (fnd_file.LOG, 'RE Hiring New employee:' emp_rec.employee_number); END IF; END IF; END LOOP;
commit;
END;

Copying a request Group Content to another request group

Hi All,
Recently i got a requirement to create request and attach all the standard reports of GL to these request groups...it seems a lot tedious task to do manually...so i developed this script to copy the request group content to other request groups..THis is very useful if you have to create multiple requestgroups and attach reports to all,....

declare
cursor z isselect 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='GL Concurrent Program Group';
begin
FOR REC IN Z LOOP
FND_PROGRAM.ADD_TO_GROUP(program_short_name =>REC.PROGRAMNAME
,program_application=>REC.APPNAME
,request_group=>'XX_GL_MOCA'
, group_application=>'Custom Application');
END LOOP;
END;