Scripts
How to Get Apps Password From Backend
STEP 1. First login to Database through Command Prompt
sqlplus / as sysdba
STEP 2 : Create Function From Below Script to decrypt the encrypted password
create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,
in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
STEP 3 : Run the Below Query for Retrieving AppsPassword
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;
ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873
AB2FFFA3B302AEE6EBD1
STEP 4: Run the Below Query to get the Password
SELECT apps.decrypt_pin_func(‘GUEST/ORACLE123′,’ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873AB2FFFA3B302AEE6EBD1’) from dual;
APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873AB2FFFA3B302AEE6EBD1′)
——————————————————————————–
APPS
STEP 5: Now Login With the Password Retrieved.
SQL> conn apps/APPS;
Connected.
How to Find Request Group associated for a Program
SELECT rg.application_id
“Request Group Application ID”,
rg.request_group_id
“Request Group – Group ID”,
rg.request_group_name,
rg.description,
rgu.unit_application_id,
rgu.request_group_id
“Request Group Unit – Group ID”,
rgu.request_unit_id,
cp.concurrent_program_id,
cp.concurrent_program_name,
cpt.user_concurrent_program_name,
Decode(rgu.request_unit_type, ‘P’, ‘Program’,
‘S’, ‘Set’,
rgu.request_unit_type) “Unit Type”
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cpt.user_concurrent_program_name = ‘Program Name’;
How to Find Scheduled Concurrent Program
Below Query will Fetch all the details for a scheduled concurrent program.
SELECT a.requested_by,
a.status_code,
a.phase_code,
a.request_id,
b.user_concurrent_program_name,
c.concurrent_program_name,
a.requested_start_date,
c.execution_method_code,
d.execution_file_name,
d.execution_file_path
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs_tl b,
apps.fnd_concurrent_programs c,
apps.fnd_executables d
WHERE a.status_code IN ( ‘Q’, ‘I’ )
AND a.concurrent_program_id = b.concurrent_program_id
AND b.concurrent_program_id = c.concurrent_program_id
AND c.application_id = d.application_id
AND c.executable_id = d.executable_id
AND a.requested_start_date > SYSDATE
AND a.hold_flag = ‘N’
ORDER BY 1;
Query For Finding schedules for a particular program
SELECT a.requested_by,
a.status_code,
a.phase_code,
a.request_id,
b.user_concurrent_program_name,
c.concurrent_program_name,
a.requested_start_date,
c.execution_method_code,
d.execution_file_name,
d.execution_file_path
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs_tl b,
apps.fnd_concurrent_programs c,
apps.fnd_executables d
WHERE a.status_code IN ( ‘Q’, ‘I’ )
AND a.concurrent_program_id = b.concurrent_program_id
AND b.concurrent_program_id = c.concurrent_program_id
AND c.application_id = d.application_id
AND c.executable_id = d.executable_id
AND a.requested_start_date > SYSDATE
AND a.hold_flag = ‘N’
AND b.user_concurrent_program_name = ‘&Program_Full_Name’
ORDER BY 1;
How to Find Database Object and it’s Corresponding Datafiles
SELECT DISTINCT t.ts#,
t.name AS Tablespace_name,
d.name AS DATAFILE,
s.owner,
s.segment_name,
s.segment_type,
s.bytes / 1024 / 1024 AS “MB”
FROM v$tablespace t,
v$datafile d,
dba_segments s
WHERE t.ts# = d.ts#
AND t.name = s.tablespace_name
AND s.owner = ‘APPS’;