How to make comments field mandatory for an approver in workflow notification

Add below function in Approval Notification in Oracle Workflow.

PROCEDURE comment_reject (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
)
IS
l_notfication_result VARCHAR2 (1000);
l_sup_comments VARCHAR2 (10000);
BEGIN
IF (funcmode = ‘RESPOND’)
THEN
l_notfication_result :=
wf_notification.getattrtext (wf_engine.context_nid, ‘RESULT’);
l_comments :=
wf_notification.getattrtext (nid => wf_engine.context_nid,
aname => ‘COMMENTS’
);

IF (l_notfication_result = ‘REJECTED’)
THEN
IF l_comments IS NULL
THEN
raise_application_error
(‘-20002’,
‘Comments are mandatory while rejecting request’
);
ELSE
resultout := ‘COMPLETE:REJECT’;
END IF;
ELSIF (l_notfication_result = ‘APPROVED’)
THEN
resultout := ‘COMPLETE:APPROVE’;
END IF;

wf_engine.setitemattrtext (itemtype, itemkey, ‘COMMENTS’, l_comments);
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.CONTEXT (‘check_comment_reject’,
itemtype,
itemkey,
actid,
funcmode
);
END;

Enable/Disable Oracle Application Login User by API

How to Disable(end date) large number of Users in Oracle Apps
How to end date large number of Users – fnd_users in Oracle Apps

To Disable/Enable bulk number of users in oracle Applications, we have a API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users,

Below is the syntax which can used to Huge number of Users in one GO !!
————————————————————————————-

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN (‘username’,’username’, …….);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End;

——————————————————————————————-

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN (‘username’,’username’, …….);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.DisableUser(all_user.user_name);
commit;
end loop;
End;

Delete Responsibility From Backend in Oracle Apps

/* Formatted on 2017/04/20 12:15 (Formatter Plus v4.8.8) */
DECLARE
v_user_name VARCHAR2 (100) := ‘SAJAL’;
v_responsibility_name VARCHAR2 (100) := ‘Application Developer’;
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name, fr.responsibility_key,
frg.security_group_key
INTO v_application_name, v_responsibility_key,
v_security_group
FROM fnd_responsibility fr,
fnd_application fa,
fnd_security_groups frg,
fnd_responsibility_tl frt
WHERE fr.application_id = fa.application_id
AND fr.data_group_id = frg.security_group_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV (‘LANG’)
AND frt.responsibility_name = v_responsibility_name;

fnd_user_pkg.delresp (username => v_user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group
);
COMMIT;
DBMS_OUTPUT.put_line ( ‘Responsiblity ‘
|| v_responsibility_name
|| ‘ is removed from the user ‘
|| v_user_name
|| ‘ Successfully’
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘Error encountered while deleting responsibilty from the user and the error is ‘
|| SQLERRM
);
END;
/

Delete Concurrent Program and Executable from Backend in Oracle Apps

DECLARE
l_prog_short_name VARCHAR2 (240);
l_exec_short_name VARCHAR2 (240);
l_appl_full_name VARCHAR2 (240);
l_appl_short_name VARCHAR2 (240);
l_del_prog_flag VARCHAR2 (1) := ‘Y’; –Set flag whether to delete Concurrent program or not
l_del_exec_flag VARCHAR2 (1) := ‘Y’; –Set flag whether to delete executable or not
BEGIN

— set concurrent program and executable short name

l_prog_short_name := ‘XX_TEST_CP’; — Concurrent program short name
l_exec_short_name := ‘XX_TEST_EXEC’; — Executable short name
l_appl_full_name := ‘XX Custom Application’; — Application full name
l_appl_short_name := ‘XXCUST’; — Application Short name


— Check if the program exists. if found, delete the program

IF fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
THEN

IF l_del_prog_flag = ‘Y’
THEN

–API call to delete Concurrent Program

fnd_program.delete_program (l_prog_short_name, l_appl_full_name);

END IF;

IF l_del_exec_flag = ‘Y’
THEN

–API call to delete Executable

fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);

END IF;
COMMIT;

DBMS_OUTPUT.put_line (‘Concurrent Program ‘||l_prog_short_name || ‘ deleted successfully’);
DBMS_OUTPUT.put_line (‘Executable ‘||l_exec_short_name || ‘ deleted successfully’);

— if the program does not exist in the system

ELSE
DBMS_OUTPUT.put_line (l_prog_short_name ||’ or ‘||l_exec_short_name|| ‘ not found’);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Error while deleting: ‘ || SQLERRM);
END;

API to Create External Bank Branch

/* Formatted on 2017/04/12 15:59 (Formatter Plus v4.8.8) */
DECLARE
p_api_version NUMBER := 1.0;
p_init_msg_list VARCHAR2 (1) := ‘F’;
v_bank_id NUMBER := 530705;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER (5);
x_msg_data VARCHAR2 (2000);
x_branch_id NUMBER;
p_count NUMBER;
x_response iby_fndcpt_common_pub.result_rec_type;
p_ext_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;
BEGIN
DBMS_OUTPUT.put_line (‘BEFORE BANK BRANCH API’);
p_ext_bank_branch_rec.bch_object_version_number := 1.0;
p_ext_bank_branch_rec.branch_name := ‘TEST BANK BRANCH’;
p_ext_bank_branch_rec.branch_type := ‘ABA’;
p_ext_bank_branch_rec.bank_party_id := v_bank_id;
iby_ext_bankacct_pub.create_ext_bank_branch
( — —————————–
— Input data elements
— —————————–
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_ext_bank_branch_rec => p_ext_bank_branch_rec,
— ——————————–
— Output data elements
— ——————————–
x_branch_id => x_branch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
DBMS_OUTPUT.put_line (‘x_branch_id = ‘ || x_branch_id);

IF (x_msg_count = 1)
THEN
DBMS_OUTPUT.put_line (‘x_msg_data ‘ || x_msg_data);
ELSIF (x_msg_count > 1)
THEN
LOOP
p_count := p_count + 1;
x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF (x_msg_data IS NULL)
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line (‘Message’ || p_count || ‘ —‘ || x_msg_data);
END LOOP;
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Bank

/* Formatted on 2017/04/12 15:59 (Formatter Plus v4.8.8) */
DECLARE
lc_output VARCHAR2 (3000);
lc_msg_dummy VARCHAR2 (3000);
lc_return_status VARCHAR2 (3000);
lc_msg_data VARCHAR2 (3000);
ln_bank_id NUMBER;
ln_msg_count NUMBER;
lr_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type;
lr_response_rec apps.iby_fndcpt_common_pub.result_rec_type;
BEGIN
lc_return_status := ”;
ln_msg_count := ”;
lc_msg_data := ”;
lr_extbank_rec.bank_name := ‘Test Supp Bank’;
lr_extbank_rec.bank_number := ‘TSB0000001’;
lr_extbank_rec.country_code := ‘IN’;
apps.fnd_msg_pub.delete_msg (NULL);
apps.fnd_msg_pub.initialize ();
iby_ext_bankacct_pub.create_ext_bank ( — ——————————
— Input data elements
— ——————————
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => lr_extbank_rec,
— ——————————–
— Output data elements
— ——————————–
x_bank_id => ln_bank_id,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_response => lr_response_rec
);
lc_output := ‘ ‘;

IF (lc_return_status ‘S’)
THEN
FOR i IN 1 .. ln_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
lc_msg_data,
lc_msg_dummy
);
lc_output :=
lc_output
|| (TO_CHAR (i) || ‘: ‘ || SUBSTR (lc_msg_data, 1, 250));
END LOOP;

apps.fnd_file.put_line (apps.fnd_file.output,
‘Error Occured while Creating Bank: ‘
);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;