How to Audit Changes in Profile Options

By using the following query we can get number of profile option changed along with new values in given number of days

Connect as apps user to instance

SELECT '***Profile Option Name ***'
|| a.user_profile_option_name
|| '*** Was Updated with value '
|| '"'
|| b.profile_option_value
|| '"'
|| ' In The Last '
|| &p_no_of_days
|| ' days'
|| ' by '
|| (SELECT user_name
FROM apps.fnd_user u
WHERE u.user_id = b.last_updated_by) mesg
FROM apps.fnd_profile_options_vl a,
apps.fnd_profile_option_values b,
apps.fnd_user c
WHERE a.profile_option_id = b.profile_option_id
AND b.last_updated_by = c.user_id
AND ( b.last_update_date > SYSDATE - &p_no_of_days
OR b.creation_date > SYSDATE - &p_no_of_days
)

How to get JV more then a specific Amount

Connect by Apps user name and just enter Amount when Prompted.We can use this query in Oracle Alerts to Notify GL Manager

SELECT gjh.NAME, gjh.running_total_cr, gjh.running_total_dr,
gjh.currency_code, gjlv.description, gsnv.NAME,
DECODE (NVL (gjlv.entered_dr, 1),
1, 'CREDIT',
gjlv.entered_dr, gjlv.entered_dr
) debit,
DECODE (NVL (gjlv.entered_cr, 1),
1, 'DEBIT',
gjlv.entered_cr, gjlv.entered_cr
) credit,
gjlv.period_name,
( gccv.segment1
|| ' '
|| gccv.segment2
|| ' '
|| gccv.segment3
|| ' '
|| gccv.segment4
|| ' '
|| gccv.segment5
|| ' '
|| gccv.segment6
|| ' '
|| gccv.segment7
|| ' '
|| gccv.segment8
) ACCOUNT,
gjlv.entered_cr, gjlv.entered_dr
FROM gl_je_headers gjh,
gl_je_lines_v gjlv,
gl_sob_names_v gsnv,
gl_code_combinations_v gccv
WHERE ( (gjlv.je_header_id = gjh.je_header_id)
AND (gsnv.set_of_books_id = gjlv.set_of_books_id)
AND (gjlv.code_combination_id = gccv.code_combination_id)
)
AND (gjh.running_total_dr > :amount OR gjh.running_total_cr > :amount)

How to Get Single Employee with Multiple Rules in WorkFlow

Use Following Query to Get Multiple Rules Attached to a Single Employee
Connect to instance By Using APPS User Name

SELECT orig_system, description, orig_system_id, NAME "Login Name",
display_name "Employee Name"
FROM wf_local_roles wfr1
WHERE (wfr1.orig_system <> 'WF_LOCAL_ROLES' AND wfr1.orig_system_id <> 0)
AND EXISTS (
SELECT 'row found'
FROM wf_local_roles wfr2
WHERE ( wfr2.orig_system <> 'WF_LOCAL_ROLES'
AND wfr2.orig_system_id <> 0
)
AND wfr2.orig_system_id = wfr1.orig_system_id
AND wfr2.orig_system = wfr1.orig_system
AND wfr2.NAME <> wfr1.NAME)
ORDER BY orig_system

Get Report List With Parameters

Use this query to Get Report List along With Parameters

Connect with APPS Password

SELECT a.concurrent_program_name AS concurrent_program_name,
a.user_concurrent_program_name AS user_concurrent_program_name,
c.application_short_name AS application_short_name,
b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
b.form_left_prompt AS prompt,
d.flex_value_set_name AS values_set_name
FROM fnd_concurrent_programs_vl a,
fnd_descr_flex_col_usage_vl b,
fnd_application c,
fnd_flex_value_sets d
WHERE a.enabled_flag = 'Y'
AND a.concurrent_program_name =
SUBSTR (b.descriptive_flexfield_name, 7, 100)
AND a.application_id = c.application_id
AND b.enabled_flag = 'Y'
AND b.flex_value_set_id = d.flex_value_set_id
ORDER BY a.concurrent_program_id, b.column_seq_num

How to Get User's Status in the ICX_SESSIONS Table

Connect to Application by using apps user and run the following SQL Query

SELECT (SELECT user_function_name
FROM fnd_form_functions_vl fffv
WHERE (fffv.function_id = a.function_id)) "Current Function",
TO_CHAR (first_connect, 'MM/DD/YYYY HH:MI:SS') start_time,
TO_CHAR (last_connect,
'MM/DD/YYYY HH:MI:SS'
) "Date and time of last hit",
TO_CHAR (SYSDATE, 'HH:MI:SS') current_time, user_name, session_id,
(SYSDATE - last_connect) * 24 * 60 mins_idle,
fnd_profile.value_specific ('ICX_SESSION_TIMEOUT',
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TIMEOUT,
counter "How many hits a User has made",
a.limit_connects "No of hits allowed in session"
FROM icx_sessions a, fnd_user b
WHERE a.user_id = b.user_id AND last_connect > SYSDATE - 1 / 24;

Display database links with remote passwords

Connect to instance by using sys login (as sysdba)

Execute the following query

SELECT u.NAME owner, l.NAME "Dblink Name", l.HOST "Host Name",

l.userid || '/' || l.PASSWORD userpass

FROM SYS.user$ u, SYS.link$ l

WHERE l.owner# = u.user#;

How to Restrict Access of Some Users or Program in Oracle Prodcution Database

CREATE OR REPLACE TRIGGER programe_restrication

AFTER LOGON ON DATABASE

BEGIN

FOR x IN (SELECT username, program

FROM SYS.v_$session

WHERE audsid = USERENV ('sessionid'))

LOOP

IF LTRIM (RTRIM (x.username)) = 'AHMADBILAL'

AND LTRIM (RTRIM (x.program)) IN ('sqlplusw.exe', 'TOAD.exe')

THEN

raise_application_error

(-20999,

'Not authorized to use in the Production environment!'

);

END IF;

END LOOP;

END programe_restrication;

/

Check Work Flow Activity In Error

I have used This Query to check Error in Purchase Order Work flow(Mean How Much PO stuck in work flow)

SELECT DISTINCT wi.item_type item_type, wi.item_key item_key,

wpa.process_name

|| ':'

|| wpa.instance_label error_process_activity_label,

wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_TYPE'

) errant_item_type,

wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_KEY'

) errant_item_key

FROM wf_items wi,

wf_item_activity_statuses wias,

wf_item_activity_statuses wias1,

wf_process_activities wpa

WHERE wi.item_type = 'WFERROR'

AND wi.end_date IS NULL

AND TO_NUMBER (wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ACTIVITY_ID'

)

) = wias.process_activity

AND wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_TYPE'

) = wias.item_type(+)

AND wf_engine.getitemattrtext ('WFERROR',

wi.item_key,

'ERROR_ITEM_KEY'

) = wias.item_key(+)

AND wias.activity_status(+) <> 'ERROR'

AND wias1.item_type = wi.item_type

AND wias1.item_key = wi.item_key

AND wias1.end_date IS NULL

AND wias1.notification_id IS NOT NULL

AND wias1.process_activity = wpa.instance_id;

Check Profile and Value with level

SELECT (SELECT user_profile_option_name

FROM fnd_profile_options_tl fpot

WHERE TRIM (fpot.profile_option_name) =

TRIM (fpo.profile_option_name))

PROFILE,

fpov.profile_option_value VALUE,

DECODE (fpov.level_id,

10001, 'SITE',

10002, 'APPLICATION',

10003, 'RESPONSIBILITY',

10004, 'USER'

) "Apply On",

fa.application_name application,

fr.responsibility_name responsibility, fu.user_name "USER"

FROM fnd_profile_option_values fpov,

fnd_profile_options fpo,

fnd_application_tl fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

WHERE fpo.profile_option_id = fpov.profile_option_id

AND fa.application_id(+) = fpov.level_value

AND fr.application_id(+) = fpov.level_value_application_id

AND fr.responsibility_id(+) = fpov.level_value

AND fu.user_id(+) = fpov.level_value

AND fl.login_id(+) = fpov.last_update_login

ORDER BY 1, 3

How to recover Control file in oracle

/* I have already backup control file to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

/* modify and run your trace file and your control file is up to date */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;

Up the database

Change the font and font size in SQLPLUS

You can change the font in SQL*Plus for Windows NT/2000.

In regedit, go to
HKEY_LOCAL_MACHINE
-> SOFTWARE
-> ORACLE
-> HOME0

Create a new registry value called SQLPLUS_FONT of type REG_EXPAND_SZ and set it to your favourite fixed-width font, Eg. Courier New
Create a new registry value called SQLPLUS_FONT_SIZE of type REG_EXPAND_SZ and set it to the size you want (13 is a good size).

Formated Query For Auto Month Addition of Given period with all tabs etc for Data Loader

By Using this query you can generate your sequence information in data Loader Format

SELECT SUBSTR (sa.method_code, 1, 1) ty, fap.application_name, '\{TAB}' tb1,

dsc.NAME, '\{TAB}' tb2, sob.NAME, '\{TAB}' tb3,

DECODE (SUBSTR (sa.method_code, 1, 1), 'A', '\{LEFT}') lf1,

'\{LEFT}' lf2, '\{TAB}' tb4,

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'DD-MON-YYYY') start_dt,

'\{TAB}' tb5,

TO_CHAR (ADD_MONTHS (sa.end_date, 1), 'DD-MON-YYYY') date_ed,

'\{TAB}' tb6,

REPLACE (REPLACE (UPPER (ds.NAME),

TO_CHAR (sa.start_date, 'MON'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'MON')

),

TO_CHAR (sa.start_date, 'YY'),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), 'YY')

) seq_na,

'\{TAB}' tb7

FROM fnd_doc_sequence_assignments sa,

fnd_application_vl fap,

gl_sets_of_books sob,

fnd_document_sequences ds,

fnd_doc_sequence_categories dsc

WHERE (sa.start_date >= TO_DATE ('01-01-2007', 'DD-MM-YYYY'))

AND (sa.end_date <= TO_DATE ('31-01-2007', 'DD-MM-YYYY'))

AND sa.application_id = fap.application_id

AND sa.set_of_books_id = sob.set_of_books_id

AND sa.doc_sequence_id = ds.doc_sequence_id

AND sa.category_code = dsc.code

ORDER BY sob.NAME, sa.category_code, sa.method_code, sa.application_id

Just need to Input Start_date and end_date in my case this is '01-01-2007 and 31-01-2007'

How Get Trace of a database user activity

Connect to your Sys Schema by using SQL or Toad create following trigger

Create or replace trigger APPSTRACE
after logon on apps.schema
begin
execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

This will generate Trace for that specific session.

This is some sort of database customization that’s y I never suggest to do it with your production environment hmmm u can do it with your vision / test environment

How to remove this check from your APPS schema

alter trigger appstrace disable;

How to determine XML Publisher and Business Intelligence Publisher Version in oracle eBusiness Suite

SQL to get the version of XML Publisher / BI Publisher that you are running in the Oracle eBusiness Suite:

SELECT DECODE (bug_number,

'3554613', '4.5.0',

'3263588', 'XDO.H',

'3822219', '5.0.0',

'4236958', '5.0.1',

'4206181', '5.5.0',

'4561451', '5.6.0',

'4905678', '5.6.1',

'5097966', '5.6.2',

'5472959', '5.6.3'

) patch,

bug_number

FROM ad_bugs

WHERE bug_number IN

('3554613',

'3263588',

'3822219',

'4236958',

'4206181',

'4905678',

'5097966',

'5472959',

'4561451'

);

How to move a datafile from one Disk To Other

Connect to SQL as Sysdba then Follow the following steps

1. alter tablespace users offline;


2. copy c:\Oracle\users01.dbf e:\Oracle\users01.dbf


3. alter database rename file 'c:\oracle\users01.dbf' to 'e:\oracle\users01.dbf';


4. alter tablespace users online;

Trace the Password Change History of Database Accounts

Oracle only tracks the date that the password will expire based on when it was latest changed. So by looking at the DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. The last password change time can also directly be seen from the PTIME column in dictionary table USER$ (on which DBA_USERS view is based).

If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account. This will maintain any password which still falls with in the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX limits.

Must Run this Query after connecting by Sys user

SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#

How to generate Serial Number By using SQL

SELECT *
FROM (SELECT LEVEL as "SR Number"
FROM DUAL
CONNECT BY LEVEL <= 15)
this will generate sequence from 1 to 15

How to Kill Oracle Session

Get All Lock Objects by Using Following Query

SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id


Get Session id From below Query by Passing session _id (get From Above query)

For example i got 67 from above query

SELECT SID, serial#,SID||','||serial# "session id", username, command, schemaname, osuser, machine, terminal
FROM v$session
WHERE SID = 67;

I got 93,10383 from above query
Pass Session Id In below query

ALTER system kill session '93,10383'

Oracle Base Tables Detail

General Ledger

Ø Base Tables

· gl_je_batches

· gl_je_headers

· gl_je_lines

· gl_je_sources

· gl_je_categories_tl

· gl_set_of_books

· gl_daily_rates

· gl_balances

· gl_periods

· gl_period_sets

· gl_code_combinations

Ø Interface Tables

· gl_interface

· gl_budget_interface

Inventory

Ø Base Tables

· mtl_system_items_b

· mtl_system_items_tl

· mtl_item_locations

· mtl_item_categories

· mtl_item_revisions_b

· mtl_parameters

· hr_all_organization_units

· cst_item_costs

Ø Interface Tables

· mtl_system_items_interface

· mtl_item_categories_interface

· mtl_item_revisions_interface

· mtl_interface_errors

Order Management

Ø Base Tables

· oe_order_headers_all

· oe_order_lines_all

· oe_order_sources

· oe_transaction_types_all

· oe_transaction_types_tl

Ø Interface Tables

· oe_headers_iface_all

· oe_lines_iface_all

· oe_actions_iface_all

Tables Used by Concurrent Request Concurrent Program

FND_CONCURRENT_REQUESTS

Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).

FND_RUN_REQUESTS

Stores information about the reports in a report set that a user submits including the report set's parameter values.

FND_CONC_REQUEST_ARGUMENTS

Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.

FND_DUAL

Records when a request does not update any database tables.

FND_CONCURRENT_PROCESSES

Records information about Oracle Applications processes and OS processes.

FND_CONC_STAT_LIST

Collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics।