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

 

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.