Query for Custom Alerts

SELECT aa.alert_name, aa.creation_date, aa.last_update_date,
aa.alert_condition_type, aa.enabled_flag, aa.start_date_active,
aa.end_date_active, aa.description, aa.date_last_checked,
aa.maintain_history_days, aa.table_name, aa.sql_statement_text,
fa.application_name
FROM alr_alerts aa, fnd_application_tl fa
WHERE aa.alert_name LIKE 'XX%' AND aa.application_id = fa.application_id

Query for Application and assigned responsiblity

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (a.application_name, 1, 50)
application,
SUBSTR (r.responsibility_name, 1, 60) responsiblity, ppf.full_name
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
per_people_f ppf
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND ppf.person_id(+) = u.employee_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (A.application_name, 1, 50),
SUBSTR (R.responsibility_name, 1, 60)

Query for Customized Reports

SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (
fcpv.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',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
AND fcpv.user_concurrent_program_name LIKE '%DSO%'
ORDER BY 1

Query for Status Of Report Request

SELECT r.request_id, r.status_code, r.request_date,
TO_CHAR (r.request_date, 'DD-MON-YYYY')
reqdate,
TRUNC (
AVG (
( TO_NUMBER (TO_CHAR (r.actual_completion_date, 'SSSSS'))
- TO_NUMBER (TO_CHAR (r.actual_start_date, 'SSSSS'))
)
/ 60
),
2
)
"Time in Min.",
r.requested_start_date, r.hold_flag, r.parent_request_id,
r.last_update_date, u1.user_name updated_by_name, r.actual_start_date,
r.completion_text, r.actual_completion_date, u2.user_name requestor,
fa.application_name
application_name,
DECODE (
r.description,
NULL,
cp.user_concurrent_program_name,
r.description || ' (' || cp.user_concurrent_program_name || ')'
)
program_name
FROM fnd_concurrent_requests r,
fnd_concurrent_programs_vl cp,
fnd_user u1,
fnd_user u2,
fnd_application_vl fa
WHERE r.program_application_id = fa.application_id
AND r.program_application_id = cp.application_id(+)
AND r.concurrent_program_id = cp.concurrent_program_id(+)
AND r.last_updated_by = u1.user_id(+)
AND r.requested_by = u2.user_id(+)
GROUP BY r.request_id,
r.status_code,
r.request_date,
r.requested_start_date,
r.hold_flag,
r.parent_request_id,
r.last_update_date,
u1.user_name,
r.actual_start_date,
r.completion_text,
r.actual_completion_date,
u2.user_name,
fa.application_name,
DECODE (
r.description,
NULL,
cp.user_concurrent_program_name,
r.description || ' (' || cp.user_concurrent_program_name || ')'
)

Query for Getting Request Group name Associated With Application

SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
AND c.user_concurrent_program_name=:P_CONCURRENT_PROGRAM_NAME
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id

Query for Form personalization

SELECT (SELECT application_name
FROM fnd_application_tl fa
WHERE fa.application_id = ff.application_id) application, ffcr.SEQUENCE,
ffcr.function_name,
REPLACE (ffcr.description, CHR (39), CHR (39) || CHR (39)) description,
ffcr.trigger_event, ffcr.trigger_object,
REPLACE (
REPLACE (ffcr.condition, CHR (10), CHR (32)),
CHR (39),
CHR (39) || CHR (39)
) condition,
ffcr.enabled, ffcr.fire_in_enter_query, ffcr.form_name
FROM apps.fnd_form_custom_rules ffcr, applsys.fnd_form ff
WHERE (ff.form_name) = (ffcr.form_name)

Create function

CREATE OR REPLACE FUNCTION XX_CHECKRUNNAME
RETURN VARCHAR2
IS
XX_CHECKRUN VARCHAR2(240);
BEGIN
SELECT CHECKRUN_NAME INTO XX_CHECKRUN FROM APPS.AP_INV_SELECTION_CRITERIA_ALL WHERE STATUS='CONFIRMED'
AND LAST_UPDATE_DATE IN(SELECT MAX(LAST_UPDATE_DATE)FROM APPS.AP_INV_SELECTION_CRITERIA_ALL
WHERE STATUS='CONFIRMED');
RETURN XX_CHECKRUN;
EXCEPTION WHEN OTHERS THEN
RETURN 'A';
END XX_CHECKRUNNAME;