Script to print the Oracle Apps Version Number

SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
  FROM fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
 WHERE a.application_id = p.application_id
   AND a.application_id = t.application_id
   AND t.language = USERENV('LANG')

Query to extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Query that shows all the repsonsibilities and what functions are attached to these responsibilities.

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

Query to link a Responsibility to a Set of Books in Oracle

SELECT fr.responsibility_name, fpov.profile_option_value set_of_books_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr
WHERE fpo.user_profile_option_name = 'GL Set of Books Name'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id

API to Purge an person from Oracle HRMS

DECLARE
   l_person_org_manager_warning VARCHAR2 (200);
BEGIN
   hr_person_api.delete_person(p_validate => FALSE,
                               p_effective_date => SYSDATE,
                               p_person_id => :person_id,
                               p_perform_predel_validation => FALSE,
                               p_person_org_manager_warning => l_person_org_manager_warning
                              );
   COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from 'Employee' to 'Applicant' and then use the above API again to purge the record.

Thanks & Regards,
Anto Joe Natesh

Reset Application Password

This is the query to reset your application password through backend.

DECLARE
v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword('ENERGY','123456');
END;

COMMIT;

Concurrent Request Status

SELECT   fcr.phase_code,
         DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code) phase,
         fcr.status_code,
         DECODE (fcr.status_code,'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',
                                fcr.status_code) status,
         request_date,
         fat.description, frt.responsibility_name, fu.user_name,
         fu.description, fcpt.user_concurrent_program_name, fcpt.description,
         fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
         fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
         fcr.printer, fcr.parent_request_id, fcr.description,
         fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
         fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
         fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
         fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
         fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
         fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
         fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
         fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
         fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
         fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
         fcr.outfile_node_name
    FROM fnd_concurrent_requests fcr,
         fnd_user fu,
         fnd_responsibility_tl frt,
         fnd_application_tl fat,
         fnd_concurrent_programs_tl fcpt
   WHERE (fu.user_id = fcr.requested_by)
     AND (fat.application_id = fcr.program_application_id)
     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
     AND (fcr.responsibility_id = frt.responsibility_id)
     AND fat.LANGUAGE = 'US'
     AND frt.LANGUAGE = 'US'
     AND fcpt.LANGUAGE = 'US'
     AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC