Oracel Apps

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;

How to copy attachment files from One record to another

Test Procedure for Copy Attachments(Short, Long, File, URL)

PROCEDURE copy_attachment (p_sow_number VARCHAR2)
IS
CURSOR c_long (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
dlt.long_text, af.function_name,
det.data_object_code entity_name, ad.pk1_value, d.media_id
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_documents_long_text dlt,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dlt.media_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dcu.enabled_flag = ‘Y’
AND dat.NAME = ‘LONG_TEXT’
AND pk1_value = l_sow_number;

CURSOR c_short (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
dlt.short_text, af.function_name,
det.data_object_code entity_name, ad.pk1_value, d.media_id
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_documents_short_text dlt,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dlt.media_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dcu.enabled_flag = ‘Y’
AND dat.NAME = ‘SHORT_TEXT’
AND pk1_value = l_sow_number;

CURSOR c_file (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
af.function_name, ad.entity_name, ad.pk1_value, d.media_id,
l.file_name
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_lobs l,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND l.file_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dat.NAME = ‘FILE’
AND dcu.enabled_flag = ‘Y’
AND pk1_value = l_sow_number;

CURSOR c_url (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
af.function_name, ad.entity_name, ad.pk1_value, d.media_id,
d.url, d.file_name
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND dat.NAME = ‘WEB_PAGE’
AND dcu.enabled_flag = ‘Y’
AND function_name = ‘xxx_DTL_FUN’
AND pk1_value = l_sow_number;
BEGIN
FOR rec_long IN c_long (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_long.seq_num,
category_id => rec_long.category_id,
document_description => rec_long.description,
datatype_id => rec_long.datatype_id,
text => rec_long.long_text,
file_name => NULL,
url => NULL,
function_name => rec_long.function_name,
entity_name => rec_long.entity_name,
pk1_value => :header_block.sow_number,
–rec_long.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_long.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_short IN c_short (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_short.seq_num,
category_id => rec_short.category_id,
document_description => rec_short.description,
datatype_id => rec_short.datatype_id,
text => rec_short.short_text,
file_name => NULL,
url => NULL,
function_name => rec_short.function_name,
entity_name => rec_short.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_short.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_file IN c_file (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_file.seq_num,
category_id => rec_file.category_id,
document_description => rec_file.description,
datatype_id => rec_file.datatype_id,
text => NULL,
file_name => rec_file.file_name,
url => NULL,
function_name => rec_file.function_name,
entity_name => rec_file.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_file.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_url IN c_url (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_url.seq_num,
category_id => rec_url.category_id,
document_description => rec_url.description,
datatype_id => rec_url.datatype_id,
text => NULL,
file_name => rec_url.file_name,
url => rec_url.url,
function_name => rec_url.function_name,
entity_name => rec_url.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_url.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;
END;

Troubleshoot long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in  Oracle Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6
: Check TKPROF out file to find root cause of slow concurrent request

.

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 2355)

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = ‘R’;

REQUEST_ID        SID    SERIAL# OSUSER           PROCESS    SPID
—————-
2355        514         28 applmgr                        17794   1633.
. 

Step 3.1 : Check and confirm SPID on Database Node
oraclevis11i@onlineappsdba>ps-ef | grep 1633
 ovis11i  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

Step 3.2 : Set OSPID (1633 in my case) for ORADEBUG
SQL> oradebug setospid 1633

Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/oracle/apps/vis11idb/10.2.0/admin/vis11i_onlineappsdba/udump/vis11i_ora_1633.trc

Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
tkprof  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ vis11i_ora_1633.trc’   ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

Step 6 : Check TKPROF file to find root cause of slow concurrent requet

 

Difference between Open Interface and API in Oracle Apps

Open Interfaces-

  • In EBS one Open Interface may run many API calls.
  • Open Interface run asynchronously.
  • The good is that if there is failure of record, they remain in the table until either fixed or purged.
  • They automate the interface into the APIs.
  • This requires less work and less code as few SQL DML would simply .

APIs-

  • When there is no corresponding Open Interface.
  • Normally all Oracle APIs run synchronously, and provide immediate responses, therefore machism to be provided to handle such situation.
  • That requires custom error handling routine.
  • This may requires lot more effort as these need fine grain control approach.

 

Remember, the APIs are also used by the front end screens, and in the same way, will require all the appropriate prerequisites to be implemented.

Important to note, you cannot use APIs as an alternative to implementation.