List of Tables for Forms Personalization

Below is the list of tables that are populated when any Forms Personalization is done

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

You can FNDLOAD utility to move one instance to another.
eg :

FNDLOAD apps/pwd Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct test_file.ldt FND_FORM_CUSTOM_RULES function_name=RCV_RCVRCERC

FNDLOAD apps/pwd 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct test_file.ldt

Or export and import this records from one instance to other. But make sure the sequences are altered :)

Query to find out Request set details

SELECT rs.user_request_set_name "Request Set"
, rss.display_sequence Seq
, cp.user_concurrent_program_name "Concurrent Program"
, e.EXECUTABLE_NAME
, e.execution_file_name
, lv.meaning file_type
,fat.application_name "Application Name"
-- ,get_appl_name(e.application_id) "Application Name"
FROM apps.fnd_request_sets_vl rs
, apps.fnd_req_set_stages_form_v rss
, applsys.fnd_request_set_programs rsp
, apps.fnd_concurrent_programs_vl cp
, apps.fnd_executables e
, apps.fnd_lookup_values lv
, apps.fnd_application_tl fat
WHERE 1=1
--and rs.application_id IN ( 20006 )
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rs.user_request_set_name = :p_request_set_name
AND e.APPLICATION_ID =FAT.APPLICATION_ID
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
and lv.language='US'
and fat.language='US'
AND rs.end_date_active IS NULL
ORDER BY 1,2

Query to find out concurrent program details

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where exe.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND appl.language='US'
AND prog.user_concurrent_program_name =:p_conc_prog_name

How to tune/trace sql statements ?

Trace Commands

alter system set timed_statistics=true; -- Enable trace
alter session set sql_trace=true;
alter session set tracefile_identifier = testtrace;

run sql query
eg:select * from emp;--statements

alter session set sql_trace=false; -- Disable trace

find trace file name from current session :

select
u_dump.value || '/' ||
db_name.value || '_ora_' ||
v$process.spid ||
nvl2(v$process.traceid, '_' || v$process.traceid, null )
|| '.trc' "Trace File"
from
v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
u_dump.name = 'user_dump_dest' and
db_name.name = 'db_name' and
v$session.audsid=sys_context('userenv','sessionid');

tkprof commands :

tkprof testtrace.trc testtrace.trcprf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n sort=execpu

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

After getting .trc file we change into readable format by using following command :

tkprof file_name_time_stamp.trc file_name_time_stamp.prf sort=(PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)

Note : We can read by many options , above is one option.

RETCODE & ERRBUFF

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--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.....

How to stop the PO Receipt Traveler report for specific organization using Form personalization:

Stop Receipt Traveler – FLG Form Personalization

FORM NAME: RCVRCERC

Trigger Event : WHEN-NEW-BLOCK-INSTANCE

Condition : fnd_profile.value(‘MFG_ORGANIZATION_ID’)=306

Target object: PO_STARTUP_VALUES.PRINT_TRAVELLER_VALUE

Property Name: Value

Value: N

Note : Here if flag value is reset to 'N' then Receipt Traveler Report program never call .