How to get debug messages from FND_LOG_MESSAGES table

1)Log into system admin responsibility and set the following profile options

FND: Debug Log Enabled - Yes

FND: Debug Log Level - Statement

FND: Debug Log Module - %

[ set at user level ]

2)Run the below select in sqlplus ::

SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq1 in step#5

3)Try to reproduce the issue with above user for which FND profile options are set.

4)Run the below select in sqlplus ::

SELECT MAX(log_sequence) FROM fnd_log_messages;

Take the max of log sequence and use it as log_seq2 in step#5

5)Run following select to get the log messages

SELECT LOG_SEQUENCE, MESSAGE_TEXT, USER_ID FROM fnd_log_messages WHERE

log_sequence > log_seq1-1 and log_sequence <>

Enabling FND Debug Messages

Set the following profile options in respective levels

1) FND: Debug Log Enabled => Yes
2) FND: Debug Log Level => Statement
3) FND: Message Level Threshold =>low level
4) FND: Debug Log Module => %

Creating New User for Oracle Marketing using Forms

1) Responsibility -> US HRMS Manager
People / Enter & Maintain / New
Action Type = Create Employment

2) Responsibility -> System Administrator
Security / User / Define
Select Person as the one created in Step 1
Define Responsibilities for this user

3) Responsibility -> CRM Resource Manager, Vision Enterprises
Maintain Resources / Import Resources
Resource Category = Employee
Name = Name entered in Step 1

find the Locked objects and Kill the sessions

Find the Locked objects

SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program,

s.osuser

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

Query to kill the session

SELECT 'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''''||';'

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'

and S.USERNAME ='SCOTT';

How to find the Package code from SQL Prompt

The following query will get you there , if you have full access to the database

Select *

from all_source

--where owner = ''

where type = 'PACKAGE BODY'

and name = ''

order by line

How to compile all the invalid database objects in a schema in a single shot

Execute the following PL/SQL Block from the SQL prompt/Database

DECLARE

CURSOR invalid_objects

IS

SELECT object_name,

object_type

FROM user_objects

WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')

AND status = 'INVALID'

AND object_name LIKE 'OZF%'

ORDER BY object_type;

BEGIN

FOR rec IN invalid_objects

LOOP

DBMS_DDL.ALTER_COMPILE (rec.object_type ,'APPS' ,rec.object_name);

dbms_output.put_line (rec.object_name || ' has been compiled.');

END LOOP;

END;