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;

Advertisements

API to Create Supplier Site

/* Formatted on 2017/04/12 15:54 (Formatter Plus v4.8.8) */
DECLARE
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
lc_return_status VARCHAR2 (10);
ln_msg_count NUMBER;
lc_msg_data VARCHAR2 (4000);
ln_vendor_site_id NUMBER;
ln_party_site_id NUMBER;
ln_location_id NUMBER;
BEGIN
— —————
— Required
— —————
l_vendor_site_rec.vendor_id := 40000;
l_vendor_site_rec.vendor_site_code := ‘Supplier_Site’;
l_vendor_site_rec.address_line1 := ’05 IFFCO Chowk’;
l_vendor_site_rec.city := ‘Gurgaon’;
l_vendor_site_rec.country := ‘IND’;
l_vendor_site_rec.org_id := 182;
— ————–
— Optional
— ————–
l_vendor_site_rec.purchasing_site_flag := ‘N’;
l_vendor_site_rec.pay_site_flag := ‘N’;
l_vendor_site_rec.rfq_only_site_flag := ‘N’;
pos_vendor_pub_pkg.create_vendor_site
(
— ——————————
— Input data elements
— ——————————
p_vendor_site_rec => l_vendor_site_rec,
— ———————————
— Output data elements
— ———————————
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_vendor_site_id => ln_vendor_site_id,
x_party_site_id => ln_party_site_id,
x_location_id => ln_location_id
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;

Create Employee Contact

/* Formatted on 2017/04/12 15:35 (Formatter Plus v4.8.8) */
DECLARE
ln_contact_rel_id per_contact_relationships.contact_relationship_id%TYPE;
ln_ctr_object_ver_num per_contact_relationships.object_version_number%TYPE;
ln_contact_person per_all_people_f.person_id%TYPE;
ln_object_version_number per_contact_relationships.object_version_number%TYPE;
ld_per_effective_start_date DATE;
ld_per_effective_end_date DATE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_per_comment_id per_all_people_f.comment_id%TYPE;
lb_name_comb_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
BEGIN
— Create Employee Contact
— ————————————-
hr_contact_rel_api.create_contact
( — Input data elements
— —————————–
p_start_date => sysdate,
p_business_group_id => fnd_profile.VALUE
(‘PER_BUSINESS_GROUP_ID’),
p_person_id => XX, — Number field
p_contact_type => ‘M’,
p_date_start => TO_DATE (’12-Apr-2017′),
p_last_name => ‘XYZ’,
p_first_name => ‘XX’,
p_personal_flag => ‘Y’,
— Output data elements
— ——————————–
p_contact_relationship_id => ln_contact_rel_id,
p_ctr_object_version_number => ln_ctr_object_ver_num,
p_per_person_id => ln_contact_person,
p_per_object_version_number => ln_object_version_number,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_name_combination_warning => lb_name_comb_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;

Create Employee from Backend API

— Create Employee
— ————————-

/* Formatted on 2017/04/12 15:31 (Formatter Plus v4.8.8) */
DECLARE
lc_employee_number per_all_people_f.employee_number%TYPE
:= ‘XX_01’;
ln_person_id per_all_people_f.person_id%TYPE;
ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
ln_object_ver_number per_all_assignments_f.object_version_number%TYPE;
ln_asg_ovn NUMBER;
ld_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
ld_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_per_comment_id per_all_people_f.comment_id%TYPE;
ln_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
lc_assignment_number per_all_assignments_f.assignment_number%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
BEGIN
hr_employee_api.create_employee
( — Input data elements
— ——————————
p_hire_date => sysdate,
p_business_group_id => fnd_profile.value_specific
(‘PER_BUSINESS_GROUP_ID’),
p_last_name => ‘TEST’,
p_first_name => ‘XX’,
p_middle_names => NULL,
p_sex => ‘M’,
p_national_identifier => ‘183-09-6723′,
p_date_of_birth => TO_DATE (’28-NOV-1989’),
p_known_as => ‘SAJAL’,
— Output data elements
— ——————————–
p_employee_number => lc_employee_number,
p_person_id => ln_person_id,
p_assignment_id => ln_assignment_id,
p_per_object_version_number => ln_object_ver_number,
p_asg_object_version_number => ln_asg_ovn,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_assignment_sequence => ln_assignment_sequence,
p_assignment_number => lc_assignment_number,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (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