Frequently Used API's In Oracle Apps R12
1 : API to Create Supplier
===============================
DECLARE
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
BEGIN
-- Required
l_vendor_rec.segment1 := '0000235916';
--* If the segment1 is auto generated than this no will not be
-- updated and the auto generated will come.
l_vendor_rec.vendor_name := 'TEST_SUPP';
-- Optional
l_vendor_rec.match_option := 'R';
pos_vendor_pub_pkg.create_vendor(
-- Input Parameters
p_vendor_rec => l_vendor_rec,
-- Output Parameters
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
x_vendor_id => l_vendor_id ,
x_party_id => l_party_id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line(sqlerrm);
END;
2 : API to Update Salary - PER_PAY_PROPOSALS
==============================================
DECLARE
l_inv_next_sal_date_warning BOOLEAN;
DBMS_OUTPUT.put_line (i.pay_proposal_id || ' has been Updated !!!!');
CURSOR c1 IS
SELECT
*
FROM
per_pay_proposals;
l_proposed_salary_warning BOOLEAN;
l_approved_warning BOOLEAN;
l_payroll_warning BOOLEAN;
BEGIN
FOR i IN c1
LOOP
l_inv_next_sal_date_warning := NULL;
l_proposed_salary_warning := NULL;
l_approved_warning := NULL;
l_payroll_warning := NULL;
hr_maintain_proposal_api.update_salary_proposal ( p_pay_proposal_id => i.pay_proposal_id ,
p_change_date => i.change_date ,
p_proposed_salary_n => 10000 ,
p_object_version_number => i.object_version_number ,
p_multiple_components => 'N' ,
p_approved => 'Y' ,
p_validate => FALSE ,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning ,
p_approved_warning => l_approved_warning ,
p_payroll_warning => l_payroll_warning );
End Loop;
Exception
When Others then
Rollback;
Dbms_output.put_line(Sqlerrm);
End;
3 : API to CREATE FND User
===============================
Declare
lc_user_name VARCHAR2(100) := 'Mrahman';
lc_user_password VARCHAR2(100) := 'Oracle123';
ld_user_start_date DATE := TO_DATE('23-JUN-2012');
ld_user_end_date VARCHAR2(100) := NULL;
ld_password_date VARCHAR2(100) := TO_DATE('23-JUN-2012');
ld_password_lifespan_days NUMBER := 90;
ln_person_id NUMBER := 32979;
lc_email_address VARCHAR2(100) := 'mrahman@test.com';
BEGIN
fnd_user_pkg.createuser ( x_user_name => lc_user_name ,
x_owner => NULL ,
x_unencrypted_password => lc_user_password ,
x_start_date => ld_user_start_date ,
x_end_date => ld_user_end_date ,
x_password_date => ld_password_date ,
x_password_lifespan_days => ld_password_lifespan_days,
x_employee_id => ln_person_id ,
x_email_address => lc_email_address );
Commit;
Exception
When Others then
Rollback;
Dbms_output.put_line(Sqlerrm);
End;
4 : API to Create Bank
===============================
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 := 'US';
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;
5 : Oracle HRMS API – Create Employee API - hr_employee_api.create_employee
=========================================================================
Create Employee
-------------------------
DECLARE
lc_employee_number PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE := 'TestEmp';
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 => TO_DATE('08-JUN-2011') ,
p_business_group_id => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
p_last_name => 'TEST' ,
p_first_name => 'Rahman' ,
p_middle_names => NULL ,
p_sex => 'M' ,
p_national_identifier => '183-09-6723' ,
p_date_of_birth => TO_DATE('03-DEC-1988') ,
p_known_as => 'PRAJ' ,
-- 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;
/
SHOW ERR;
6 : To reset application password.
========================================
Declare
v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘Test’,’abcd′);
END;
7 : To get the description of GL Account.
Parameters: 1. Chart of Account id
2. Number of the Segment
3. Segment value for which you need the description
============================================================================================================================================
SELECT
gcc.code_combination_id ,
apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1) Segment1_desc,
apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2) Segment2_desc,
apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3) Segment3_desc,
apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4) Segment4_desc,
apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5) Segment5_desc
from
gl_code_combinations gcc
where
gcc.code_combination_id = 12854 -- code combination id
;
8 : API to initiate Apps session from Toad or sql.
===========================================================
begin
FND_GLOBAL.apps_initialize(user_id =>1654 ,
resp_id =>20434,
resp_appl_id =>101);
end;
9 : API to register Executable
======================================
BEGIN
FND_PROGRAM.executable ('XXREQNOTIFTOBUYER' -- executable name
, 'Payables' -- application
, 'XX_REQNOTBUY_API' -- short_name
, 'Executable for Approved requisition notif to Buyer' -- description
, 'PL/SQL Stored Procedure' -- execution_method
, 'XXREQNOTIFTOBUYER' -- execution_file_name
, '' -- subroutine_name
, '' -- Execution File Path
, 'US' -- language_code
,'');
COMMIT;
END;
10 : API to register Concurrent Program
==========================================
BEGIN
FND_PROGRAM.register('XXREQNOTIFTOBUYER' -- program
, 'Payables' -- application
, 'Y' -- enabled
, 'XX_REQNOTBUY_API' -- short_name
, 'Approved requisition notif to Buyer' -- description
, 'XX_REQNOTBUY_API' -- executable_short_name
, 'Payables' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
COMMIT;
END;
11 : API to attach Concurrent Porgram to Request Group
==========================================================
BEGIN
FND_PROGRAM.add_to_group('XX_REQNOTBUY_API' -- program_short_name
, 'Payables' -- application
, 'All Reports' -- Report Group Name
, 'SQLAP'); -- Report Group Application
COMMIT;
END;
12 : API to create inventory item
==========================================
DECLARE
l_inventory_item_id number;
l_organization_id number;
l_return_status varchar2(4000);
l_msg_data varchar2(4000);
l_msg_count number;
l_msg_index number;
API_ERROR
EXCEPTION;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID =>1873 ,
RESP_ID =>20634,
RESP_APPL_ID =>401);
EGO_ITEM_PUB.PROCESS_ITEM ( p_api_version => 1.0 ,
p_init_msg_list => 'T' ,
p_commit => 'T' ,
p_transaction_type =>'CREATE' ,
p_segment1 =>'xx_testing' ,
p_description =>'xx_testing_detail' ,
p_long_description =>'same as before' ,
p_organization_id =>121 ,
p_template_id =>19 ,
p_inventory_item_status_code =>'Active' ,
p_approval_status =>'A' ,
x_inventory_item_id =>l_inventory_item_id ,
x_organization_id =>l_organization_id ,
x_return_status =>l_return_status ,
x_msg_count =>l_msg_count ,
x_msg_data =>l_msg_data );
if
l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.PUT_LINE('Item is created successfully, Inventory Item Id : '||l_inventory_item_id);
commit;
else
DBMS_OUTPUT.PUT_LINE('Item creatION is failed');
RAISE API_ERROR;
ROLLBACK;
END IF;
-- HANDLE EXCEPTION
EXCEPTION
WHEN API_ERROR THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS :'||l_msg_data);
end loop;
when others THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS : '||l_msg_data);
end loop;
end;
13 : API to update inventory item' s template
================================================
DECLARE
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_return_status VARCHAR2(1);
x_msg_count NUMBER(10);
x_msg_data VARCHAR2(1000);
x_message_list error_handler.error_tbl_type;
BEGIN
l_item_table(1).transaction_type := 'UPDATE';
l_item_table(1).inventory_item_id := 242008; --INVENTORY_ITEM_ID;
l_item_table(1).organization_id := 127; -- I.ORGANIZATION_ID;
l_item_table(1).template_id := 19;-- I.NEW_TEMPLATE_ID;
ego_item_pub.process_items(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_item_tbl => l_item_table,
x_item_tbl => x_item_table,
x_return_status => x_return_status,
x_msg_count => x_msg_count);
dbms_output.put_line('Return Status ==>' || x_return_status);
dbms_output.put_line('Error Messages : ');
error_handler.get_message_list(x_message_list => x_message_list);
FOR i IN 1..x_message_list.count LOOP
dbms_output.put_line(x_message_list(i).message_text);
END LOOP;
END;
No comments:
Post a Comment