Edi Yanto (何 萬 新)

You can raise exception with this below scripts:

DECLARE
ey_custom EXCEPTION;
PRAGMA EXCEPTION_INIT (ey_custom, -20288);
BEGIN
raise_application_error (-20288, ‘EY – This is a custom error’);
EXCEPTION
WHEN ey_custom
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

–OR

DECLARE
ey_custom EXCEPTION;
BEGIN
RAISE ey_custom;
EXCEPTION
WHEN ey_custom
THEN
RAISE_APPLICATION_ERROR (-20288, ‘EY – This is a custom error’);
END;

Scenario: We want to disable “Sample No” parameter if “Type” parameter value is “Receipt“, otherwise disable “Receipt No” parameter if “Type” parameter value is “Sample” and the parameter must be mandatory if enable.

We have to define concurrent program with the following parameters:

1. Type (value are Sample or Receipt, using value set independent (ex: “EY_TYPE”))
2. Run_Mode_S (use SQL Statement with value “select ‘S’ from dual where :$FLEX$.EY_TYPE:null in (‘Sample’)“, and the parameter is  not display)
3. Run_Mode_R (use SQL Statement with value “select ‘R’ from dual where :$FLEX$.EY_TYPE:null in (‘Receipt’)“, and the parameter is  not display)
4. Sampe_No (value set “EY_SAMPLE” and set parameter required)
5. Receipt_No (value set “EY_RECEIPT” and set parameter required)

Read the rest of this entry »

SELECT p.profile_option_name short_name, n.user_profile_option_name NAME,
DECODE (v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Org’,
10007, DECODE (TO_CHAR (v.level_value2), ‘-1’, ‘Responsibility’, DECODE (TO_CHAR (v.level_value), ‘-1’, ‘Server’, ‘Server+Resp’)),
‘UnDef’
) level_set,
DECODE (TO_CHAR (v.level_id),
‘10001’, ”,
‘10002’, app.application_short_name,
‘10003’, rsp.responsibility_key,
‘10004’, usr.user_name,
‘10005’, svr.node_name,
‘10006’, org.NAME,
‘10007’, DECODE (TO_CHAR (v.level_value2),
‘-1’, rsp.responsibility_key,
DECODE (TO_CHAR (v.level_value),
‘-1’, (SELECT node_name
FROM fnd_nodes
WHERE node_id = v.level_value2),
(SELECT node_name
FROM fnd_nodes
WHERE node_id = v.level_value2) || ‘-‘ || rsp.responsibility_key
)
),
‘UnDef’
) “CONTEXT”,
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND UPPER (p.profile_option_name) IN (SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE UPPER (user_profile_option_name) LIKE UPPER (‘%&user_profile_name%’))
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY short_name, user_profile_option_name, level_id, level_set;