Script to view all Form personalization by responsibility

  SELECT fpt.application_name,
         ff.form_name source_form_name,
         fft.user_form_name,
         fft.description form_description,
         fff.function_name,
         ffft.user_function_name,
         ffft.description function_description,
         ffcr.sequence personalize_rule_sequence,
         ffcr.description personalize_rule_description,
         DECODE (ffcr.rule_type,  'F', 'Form',  'A', 'Function')
            personalize_rule_level,
         ffcr.enabled personalize_rule_enabled,
         ffcr.trigger_event personalize_rule_event,
         ffcr.trigger_object,
         ffcr.condition personalize_rule_condition,
         DECODE (ffcs.level_id,
                 10, 'Industry',
                 20, 'Site',
                 30, 'Responsibility',
                 40, 'User')
            context_level,
         DECODE (ffcs.level_id,
                 10, '',
                 20, '',
                 30, frt.responsibility_name,
                 40, fu.user_name)
            context_level_value,
         ffca.sequence action_sequence,
         DECODE (ffca.action_type,
                 'P', 'Property',
                 'M', 'Message',
                 'B', 'Builtin',
                 'S', 'Menu',
                 '')
            action_type,
         ffca.summary action_description,
         ffca.enabled action_enabled,
         DECODE (ffca.language,
                 '*', 'All',
                 'US', 'American English',
                 'AR', 'Arabic')
            action_language,
         DECODE (ffca.action_type, 'P', ffca.object_type, NULL)
            action_object_type,
         DECODE (ffca.action_type, 'P', ffca.target_object, NULL)
            action_target_object,
         DECODE (ffca.action_type, 'P', ffcpl.property_name, NULL)
            action_property_name,
         DECODE (
            ffca.action_type,
            'P', DECODE (
                    ffca.argument_type,
                    'B', DECODE (ffca.property_value,  '5', 'FALSE',  '4', 'TRUE'),
                    ffca.property_value),
            NULL)
            action_property_value,
         DECODE (
            ffca.action_type,
            'M', DECODE (ffca.MESSAGE_TYPE,
                         'S', 'Show',
                         'E', 'Error',
                         'W', 'Warning',
                         'H', 'Hint',
                         'D', 'Debug'),
            NULL)
            action_message_type,
         DECODE (ffca.action_type, 'M', ffca.MESSAGE_TEXT, NULL)
            action_message_text,
         DECODE (ffca.action_type, 'B', ffca.builtin_type, NULL)
            action_builtin_type,
         DECODE (ffca.action_type, 'B', ffca.builtin_arguments, NULL)
            action_builtin_arguments,
         DECODE (ffca.action_type, 'B', ffca.menu_argument_long, NULL)
            action_builtin_parameters,
         DECODE (ffca.action_type, 'B', ffca.menu_argument_short, NULL)
            action_builtin_code,
         DECODE (ffca.action_type, 'S', ffca.menu_entry, NULL)
            action_menu_entry,
         DECODE (ffca.action_type, 'S', ffca.menu_label, NULL)
            action_menu_label,
         DECODE (ffca.action_type, 'S', ffca.menu_seperator, NULL)
            action_menu_seperator,
         DECODE (ffca.action_type, 'S', ffca.menu_enabled_in, NULL)
            action_menu_blocks_enabled,
         DECODE (ffca.action_type, 'S', ffca.menu_argument_short, NULL)
            action_menu_icon_name
    FROM fnd_application fp,
         fnd_application_tl fpt,
         fnd_form ff,
         fnd_form_tl fft,
         fnd_form_functions fff,
         fnd_form_functions_tl ffft,
         fnd_form_custom_rules ffcr,
         fnd_form_custom_scopes ffcs,
         fnd_responsibility_tl frt,
         fnd_user fu,
         fnd_form_custom_actions ffca,
         fnd_form_custom_prop_list ffcpl
   WHERE                                           ----------------APPLICATION
        fp   .application_id = fpt.application_id
         AND fpt.language = 'US'
         ------------------------ FORM
         AND fpt.application_id = ff.application_id
         AND ff.form_id = fft.form_id
         AND fft.language = 'US'
         ------------------------ FUNCTION
         AND ff.form_id = fff.form_id
         AND fff.function_id = ffft.function_id
         AND ffft.language = 'US'
         ------------------------ Custom Rule
         AND ff.form_name = ffcr.form_name
         AND ffcr.function_name = fff.function_name
         ------------------------ Custom Scope
         AND ffcr.id = ffcs.rule_id
         AND ffcs.level_value = frt.responsibility_id(+)
         AND frt.language(+) = 'US'
         AND frt.responsibility_name =
         AND ffcs.level_value = fu.user_id(+)
         ------------------------ Custom Actions
         AND ffcr.id = ffca.rule_id
         AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =
                ffcpl.property_id
         AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =
                ffcpl.field_type
ORDER BY fft.application_id,
         ff.form_name,
         ffcr.function_name,
         ffcr.sequence,
         ffcs.level_id,
         ffcs.level_value,
         ffca.sequence

Online Users

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# ;

Users Locking 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_CUSTOM_PKG%';

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

Custom List

List of Custom Oracle Reports

SELECT DISTINCT FAV.application_name, FCP.user_concurrent_program_name, FCP.concurrent_program_name, FCP.description
FROM fnd_concurrent_programs_vl fcp, FND_EXECUTABLES_FORM_V fef, fnd_application_vl FAV
WHERE fcp.executable_id = fef.executable_id AND fcp.application_id = FAV.application_id AND fcp.concurrent_program_name like 'XX%' AND fef.execution_method_code = 'P'

List of Custom Profile Options

SELECT DISTINCT fav.application_name, fpo.profile_option_name, fpo.creation_date FROM fnd_profile_options fpo, fnd_application_vl FAV
WHERE FPO.application_id = FAV.application_id AND fpo.profile_option_name LIKE 'XX%'

List of Custom Request Groups

SELECT DISTINCT fav.application_name, frg.request_group_name, frg.description, frg.request_group_code FROM fnd_request_groups FRG, FND_APPLICATION_VL fav
WHERE frg.application_id = fav.application_id AND frg.request_group_code LIKE 'XX%'

List of Custom Responsibilities

SELECT DISTINCT fav.application_name, frv.responsibility_key, frv.responsibility_name, frv.description FROM fnd_responsibility_vl FRV, fnd_application_vl FAV
WHERE FRV.application_id = FAV.application_id AND FRV.responsibility_key LIKE 'XX%'

List of Custom Forms

SELECT DISTINCT fav.application_name, ffv.form_name, ffv.user_form_name, ffv.description FROM fnd_form_vl ffv, FND_APPLICATION_VL fav
WHERE ffv.application_id = fav.application_id AND form_name LIKE 'XX%'

List of Custom Applications

SELECT APPLICATION_ID, APPLICATION_SHORT_NAME, BASEPATH, APPLICATION_NAME, DESCRIPTION FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME LIKE 'XX%'

Validate Accounting Segment

SELECT COUNT (1)
INTO lc_segment1
FROM gl_sets_of_books GSOB, fnd_id_flex_structures_vl FIFST, fnd_id_flex_segments_vl FIFSE, fnd_flex_value_sets FFVS, fnd_flex_values_vl FFV WHERE GSOB.chart_of_accounts_id = FIFST.id_flex_num
AND FIFST.id_flex_num = FIFSE.id_flex_num
AND FIFST.id_flex_code = FIFSE.id_flex_code
AND FIFSE.flex_value_set_id = FFVS.flex_value_set_id
AND FFVS.flex_value_set_id = FFV.flex_value_set_id
AND FIFSE.application_column_name = 'SEGMENT1'
AND FFV.flex_value = rec_cur_new.company -- Company Segment Value
AND GSOB.set_of_books_id = lc_sob_id; -- Ledger ID

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;

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


-- Concurrent Manager

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       description,
       enabled_flag
  FROM apps.fnd_concurrent_queues_vl fcq
 WHERE user_concurrent_queue_name LIKE 'AA%'

-- 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);

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