Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Friday, February 15, 2013

SQL Scripts


List of DFF

List of Scheduled Concurrent Programs


User Creation API (FND_USER_PKG)
Below is the code to create multiple users and adding responsibilities to them through API
Other way around, you can load users in table and fetch cursor table data in Loop to create users.

declare
v_user_name := 'euser' ;
v_session_id := sid; 
/* Pass here sid from this query- SELECT username, sid, serial#, status FROM v$session WHERE username = 'APPS'; */
BEGIN
for i in 1..1000 loop
fnd_user_pkg.createuser
(x_user_name => v_user_name||i
,x_owner => ''
,x_unencrypted_password => 'welcome1'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'EBS user creation'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_email_address => 'user@myemail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');

fnd_user_pkg.addresp
(username => v_user_name||i
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
end loop;
END;
/

How to get Profile option values using SQL?

SELECT 
po.profile_option_name as name
, po.user_profile_option_name
, decode(to_char(pov.level_id),'10001','SITE','10002','APP','10003','RESP','10005','SERVER','10006','ORG','10004','USER', '???') as "LEVEL"
, decode(to_char(pov.level_id),'10001','','10002', app.application_short_name,'10003', rsp.responsibility_key,'10005', svr.node_name,'10006', org.name,'10004', usr.user_name,'???') as context
, pov.profile_option_value as value
FROM 
fnd_profile_options_vl po
, fnd_profile_option_values pov
, fnd_user usr
, fnd_application app
, fnd_responsibility rsp
, fnd_nodes svr
, hr_operating_units org
WHERE 1=1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id = pov.level_value
AND rsp.application_id = pov.level_value_application_id
AND rsp.responsibility_id = pov.level_value
AND app.application_id = pov.level_value
AND svr.node_id = pov.level_value
AND org.organization_id = pov.level_value
ORDER BY 1, pov.level_id, 5

Note- Initialize your SQL session before running above query


CCID Creation Code Thru API

Call get_code_combination_id_f function by passing required segment values and ledger name and hold error message returned in variable. This function will check for existing CCID and return the value. If CCID does'nt exists, it calls create_a_combination function and generate new CCID using API

FUNCTION create_a_combination 
(
p_segment1 IN VARCHAR2,
p_segment2 IN VARCHAR2,
p_segment3 IN VARCHAR2,
p_segment4 IN VARCHAR2,
p_segment5 IN VARCHAR2,
p_segment6 IN VARCHAR2,
p_segment7 IN VARCHAR2,
p_segment8 IN VARCHAR2,
p_flex_delimiter IN VARCHAR2,
p_chart_of_accounts_id IN NUMBER,
p_ccid OUT NUMBER
)
RETURN VARCHAR2
IS
ccid NUMBER := 0;
allsegments VARCHAR2 (256) := NULL;
flexerror VARCHAR2 (2560) := NULL;
BEGIN
allsegments :=
p_segment1
|| p_flex_delimiter
|| p_segment2
|| p_flex_delimiter
|| p_segment3
|| p_flex_delimiter
|| p_segment4
|| p_flex_delimiter
|| p_segment5
|| p_flex_delimiter
|| p_segment6
|| p_flex_delimiter
|| p_segment7
|| p_flex_delimiter
|| p_segment8;

DBMS_OUTPUT.put_line ('Inside create_a_combination-' || allsegments);

ccid :=
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
validation_date => TO_CHAR (SYSDATE, fnd_flex_ext.DATE_FORMAT), concatenated_segments => allsegments);

p_ccid := ccid;

IF ccid <= 0
THEN
flexerror := fnd_message.get;
END IF;

RETURN flexerror;

EXCEPTION
WHEN OTHERS THEN
p_ccid := 0;
flexerror := SQLERRM || ' ' || fnd_message.get;
RETURN flexerror;

END create_a_combination;

-------------------------------------------------------------------------------------

FUNCTION get_code_combination_id_f 
(
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE,
p_set_of_books_name IN gl_ledgers.NAME%TYPE,
p_error_msg OUT VARCHAR2
)
RETURN gl_code_combinations.code_combination_id%TYPE
IS
CURSOR c_ccid_validate (
p_segment1 IN gl_code_combinations.segment1%TYPE,
p_segment2 IN gl_code_combinations.segment2%TYPE,
p_segment3 IN gl_code_combinations.segment3%TYPE,
p_segment4 IN gl_code_combinations.segment4%TYPE,
p_segment5 IN gl_code_combinations.segment5%TYPE,
p_segment6 IN gl_code_combinations.segment6%TYPE,
p_segment7 IN gl_code_combinations.segment7%TYPE,
p_segment8 IN gl_code_combinations.segment8%TYPE
)
IS
SELECT code_combination_id
FROM gl_code_combinations
WHERE segment1 = p_segment1
AND segment2 = p_segment2
AND segment3 = p_segment3
AND segment4 = p_segment4
AND segment5 = p_segment5
AND segment6 = p_segment6
AND segment7 = p_segment7
AND segment8 = p_segment8;

CURSOR c_flex_details
IS
SELECT id_flex_num
FROM fnd_id_flex_segments
WHERE id_flex_num =
(SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = (SELECT ledger_id
FROM gl_ledgers
WHERE NAME = p_set_of_books_name))
AND application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL')
AND enabled_flag = 'Y'
AND ROWNUM = 1;

l_flex_details c_flex_details%ROWTYPE;
l_flexerror VARCHAR2 (2560) := NULL;
l_acc_segment1 gl_code_combinations.segment1%TYPE;
l_acc_segment2 gl_code_combinations.segment2%TYPE;
l_acc_segment3 gl_code_combinations.segment3%TYPE;
l_acc_segment4 gl_code_combinations.segment4%TYPE;
l_acc_segment5 gl_code_combinations.segment5%TYPE;
l_acc_segment6 gl_code_combinations.segment6%TYPE;
l_acc_segment7 gl_code_combinations.segment7%TYPE;
l_acc_segment8 gl_code_combinations.segment8%TYPE;

l_ccid gl_code_combinations.code_combination_id%TYPE := NULL;
l_user_name fnd_user.user_name%TYPE := fnd_profile.VALUE ('USERNAME');
x_delimiter VARCHAR2 (10);

BEGIN
FOR c_ccid_validate_rec IN c_ccid_validate 
(p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8
)

LOOP
l_ccid := c_ccid_validate_rec.code_combination_id;
RETURN (l_ccid);
END LOOP;

IF l_ccid IS NULL
THEN
fnd_file.put_line (fnd_file.LOG, 'CCID Not Found in gl_code_combinations');
fnd_file.put_line (fnd_file.LOG, 'CCID Not Found..Creating New CCID...');

-------- Cursor for getting the SOB details---------------------------

OPEN c_flex_details;
FETCH c_flex_details INTO l_flex_details;
CLOSE c_flex_details;

fnd_file.put_line (fnd_file.LOG, 'chart of account :-' || l_flex_details.id_flex_num);

x_delimiter := 
fnd_flex_ext.get_delimiter
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_flex_details.id_flex_num
);

--Create CCID

l_flexerror :=
create_a_combination 
(p_segment1,
p_segment2,
p_segment3,
p_segment4,
p_segment5,
p_segment6,
p_segment7,
p_segment8,
x_delimiter,
l_flex_details.id_flex_num,
l_ccid
);

fnd_file.put_line (fnd_file.LOG, 'Newly Created CCID-' || l_ccid);

IF l_ccid <= 0
THEN
fnd_file.put_line (fnd_file.LOG, 'l_flexerror' || l_flexerror);
p_error_msg := l_flexerror;
RETURN (0);
ELSE
p_error_msg := NULL;
RETURN (l_ccid);
END IF;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;

WHEN OTHERS
THEN
l_flexerror := SQLERRM;
p_error_msg := l_flexerror;
RETURN 0;
END get_code_combination_id_f;


FND_CONCURRENT_REQUESTS (Table)

1) FND_CONCURRENT_REQUESTS stores information about individual concurrent requests.


2) ARGUMENT1 through ARGUMENT25 contain any arguments the application passes to the concurrent program. If the concurrent program needs more than 25 arguments to run, the first 25 arguments are stored in this table, ARGUMENT26 throughARGUMENT100 are stored in table FND_CONC_REQUEST_ARGUMENTS.


3) REQ_INFORMATION is used with report sets to remember the status of the request between runs.


4) When the request is set to use automatic resubmission, RESUBMITTED is a flag to indicate whether the request has been resubmitted or not.


5) IS_SUB_REQUEST is a flag that identifies a child request and HAS_SUB_REQUEST is a flag that identifies a parent request.


STATUS_CODE Column Values:
A- Waiting
B- Resuming
C- Normal
D- Cancelled
E- Error
F- Scheduled
G- Warning
H- On Hold
I- Normal
M- No Manager
Q- Standby
R- Normal
S- Suspended
T- Terminating
U- Disabled
W- Paused
X- Terminated
Z- Waiting


PHASE_CODE Column Values:
C- Completed
I- Inactive
P- Pending
R- Running


Detailed Query for Parameter Request ID

SELECT DISTINCT fcr.request_id, fcr.actual_start_date,
fcr.actual_completion_date,
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600) HOURS,

floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60) MINUTES,
round((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60) -
floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) SECS,
DECODE (fcr.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
) phase_code,
DECODE (fcr.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
) status_code,
fcr.outfile_name, fcr.number_of_arguments, fcr.argument_text,
frt.responsibility_name, fav.application_name, fav.application_short_name appl_short_name, fu.user_name,
fu.description user_description, fu.start_date user_start_date,
fcp.user_concurrent_program_name,
fcp.concurrent_program_name short_name, fe.executable_name,
DECODE (fe.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
) execution_method,
fe.execution_file_name
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_application_vl fav,
fnd_responsibility_tl frt,
fnd_concurrent_programs_vl fcp,
fnd_executables fe
WHERE fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.responsibility_application_id = fav.application_id
AND fcp.executable_id = fe.executable_id
AND fcr.request_id = :request_id
-- AND fu.user_name= ''

DBA Queries

-- Database Details
SELECT * FROM v$database

-- Instance Details
SELECT * FROM v$instance

-- License Details 

SELECT * FROM v$license

-- Version Details
SELECT * FROM v$version

--Release Details
SELECT * FROM apps.fnd_product_groups

-- Patch Details
SELECT * FROM ad_applied_patches
SELECT * FROM ad_bugs


-- Partitioning Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') partitioning
FROM (SELECT 1
FROM dba_part_tables
WHERE owner NOT IN ('SYSMAN', 'SH', 'SYS', 'SYSTEM') AND ROWNUM = 1);

-- Spatial Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') spatial
FROM (SELECT 1
FROM all_sdo_geom_metadata
WHERE ROWNUM = 1);

-- RAC Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') rac
FROM (SELECT 1
FROM v$active_instances
WHERE ROWNUM = 1);

-- Unix Product Top Value
SELECT variable_name, value
FROM apps.fnd_env_context
WHERE variable_name = 'AP_TOP'
AND concurrent_process_id = 
(SELECT MAX (concurrent_process_id) FROM apps.fnd_env_context);

-- List of Installed Applications
SELECT fat.application_id, fat.application_name, fpi.creation_date, fpi.product_version, fpi.status, fpi.TABLESPACE, fpi.install_group_num, fpi.db_status, fpi.patch_level, fpi.industry
FROM fnd_product_installations fpi, fnd_application_tl fat
WHERE fpi.application_id = fat.application_id AND fpi.status = 'I'

Different Column Values are as below:
STATUS
S- Shared Install
I- Installed Product
L- Custom Product
N- Not Installed
INSTALL_GROUP_NUM
0- SOA Products
1- MOA (Multiple oracle account) Products. It needs multiple oracle account to support multiple set of books
INDUSTRY
C- Commercial
G- Educational or Non-Profit Use

B- Project Billing
P- Project Costing

-- Query to Find list of peoples Locking AP Object 
SELECT c.owner, c.object_name, c.object_type, fu.user_name locking_user_name,
fl.start_time, vs.module, vs.machine, vs.osuser, vs.SID, vp.pid,
vp.spid AS os_process_to_kill, vs.serial#, vs.status, vs.saddr,
vs.audsid, vs.process
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE 'AP%';

-- Command to Kill Session for Releasing Lock 
ALTER SYSTEM KILL SESSION '(sid, serial#)';

-- Check Users who are Online
SELECT
p.spid -- The UNIX PID
,s.sid ,s.serial#
,p.username as os_user
,s.username ,s.status
,p.terminal ,p.program
FROM v$session s ,v$process p
WHERE p.addr = s.paddr
ORDER BY s.username ,p.spid ,s.sid ,s.serial# ;

Sysadmin Queries

1 comment:


  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete