Oracle APIs and Interfaces
Oracle EBS R12/R11
Oracle APIs and Interfaces
Q: What is API? and what is open interface?what is the difference between two?
The term Open Interfaces actually refers to table driven interfaces, where you put data in a table that sits between your external application and the Oracle module you are interfacing with.
The term API refers to stored procedure driven interfaces, where you call a stored procedure to perform an action within an Oracle Module, and the data from your external application is passed through the stored procedure’s parameters.
Historically, there were only table driven interfaces and they were called Open Interfaces. Later, stored procedures were added and were called APIs.
API – it’s validating the record(s) and inserting directly into base tables. (if any error occurs, it’s just thrown an error at run time.)
Interface – Loads the data through interface table(s) and error-out record(s) histories’ available in the same interface tables or provided error tables.
———————————————————————————————————-
Q: What are Interfaces?
- Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
- The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
- Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
- Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
- Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.
Types of Interfaces
- Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
- Outbound Interface : These interfaces are used to transfer data from Oracle Applications to external systems.
oracle apps Interface Table Names
Oracle APIs:
Oracle APIs are a logical grouping of all external process routines. The Oracle HRMS API sets delivers a set of PL/SQL packages procedures and functions that provide an open interface to the database.
There are three options for you to find the details:
- Oracle Documents
To find a current list of publicly callable business process APIs in Oracle HRMS; you need to go into your On Line Help from within applications and use the search field to find ‘Publicly Callable Business’. In the Search Results frame you will see a link to ‘Publicly Callable Business Process APIs in Oracle HRMS (Oracle HRMS)’ listed.
- Irep
To find correct information on APIs you check Oracle Integration Repository (irep) which can accessed via MetaLink by selecting the Knowledge tab – Knowledge Browser sub-tab, then in the Online Documentation box select – Oracle Integration Repository. - Search the database
You may find official documentation on Publicaly Callable APIs as in options 1.
You can search in the HR Schema for the stored procedures with name ‘HR%API’. Open the Package spec and then read the inline comment about the parameters. This is very useful to understand what is meant by each paramater and what the API does.
For Oracle release 11 ,11i release 12, the APIs are located in the operating system directories: $PER_TOP/patch/xxx/sql and $PAY_TOP/patch/xxx/sql,
- Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules
WHERE u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by a.owner, a.name;
TRM helpful in selecting the appropriate API call.
——————————————————————————————————————-
Important APIs Available in HR Module
Add a New Employee:
- Insert person : hr_employee_API.create_employee
- Update people group : hr_assignment_api.update_emp_asg_criteria
- Update assignment : hr_assignment_api.update_us_emp_asg
- Insert base salary : insert into per_pay_proposals
- Insert check as the payment method : hr_entry_api. insert_element_entry
Example……
An example call to the create_employee API where the business group method of employee number generation is manual, the default employee person type is required and the e-mail attributes do not need to be set.
declare
l_emp_num varchar2(30);
l_person_id number;
l_assignment_id number;
l_per_object_version_number number;
l_asg_object_version_number number;
l_per_effective_start_date date;
l_per_effective_end_date date;
l_full_name varchar2(240);
l_per_comment_id number;
l_assignment_sequence number;
l_assignment_number varchar2(30);
l_name_combination_warning boolean;
l_assign_payroll_warning boolean;
l_orig_hire_warning boolean;
begin
--
-- Set variable with the employee number value,
-- which is going to be passed into the API.
--
l_emp_num := 4532;
--
-- Put the new employee details in the database
-- by calling the create_employee API
--
hr_employee_api.create_employee
(p_hire_date =>
to_date('06-06-1996','DD-MM-YYYY')
,p_business_group_id => 23
,p_last_name => 'Bloggs'
,p_sex => 'M'
,p_employee_number => l_emp_num
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_per_comment_id => l_per_comment_id
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning
);
end;
Note: The database column for employee_number is defined as varchar2 to allow for when the business group method of employee_number generation is set to National Identifier.
Update Employee Information:
- Update personal information if it changed: per_per_upd.upd
- Update assignment and people group information if it exists, set mode to correct or update : hr_assignment_api.update_emp_asg_criteria
Example……
DECLARE
— Local Variables
— ———————–
lc_dt_ud_mode VARCHAR2(100) := NULL;
ln_assignment_id NUMBER := 33561;
ln_supervisor_id NUMBER := 2;
ln_object_number NUMBER := 1;
ln_people_group_id NUMBER := 1;
— Out Variables for Find Date Track Mode API
— —————————————————————–
lb_correction BOOLEAN;
lb_update BOOLEAN;
lb_update_override BOOLEAN;
lb_update_change_insert BOOLEAN;
— Out Variables for Update Employee Assignment API
— —————————————————————————-
ln_soft_coding_keyflex_id HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
lc_concatenated_segments VARCHAR2(2000);
ln_comment_id PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
lb_no_managers_warning BOOLEAN;
— Out Variables for Update Employee Assgment Criteria
— ——————————————————————————-
ln_special_ceiling_step_id PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
lc_group_name VARCHAR2(30);
ld_effective_start_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
ld_effective_end_date PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
lb_org_now_no_manager_warning BOOLEAN;
lb_other_manager_warning BOOLEAN;
lb_spp_delete_warning BOOLEAN;
lc_entries_changed_warning VARCHAR2(30);
lb_tax_district_changed_warn BOOLEAN;
BEGIN
— Find Date Track Mode
— ——————————–
dt_api.find_dt_upd_modes
( p_effective_date => TO_DATE(’12-JUN-2011′),
p_base_table_name => ‘PER_ALL_ASSIGNMENTS_F’,
p_base_key_column => ‘ASSIGNMENT_ID’,
p_base_key_value => ln_assignment_id,
— Output data elements
— ——————————–
p_correction => lb_correction,
p_update => lb_update,
p_update_override => lb_update_override,
p_update_change_insert => lb_update_change_insert
);
IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
THEN
— UPDATE_OVERRIDE
— ———————————
lc_dt_ud_mode := ‘UPDATE_OVERRIDE’;
END IF;
IF ( lb_correction = TRUE )
THEN
— CORRECTION
— ———————-
lc_dt_ud_mode := ‘CORRECTION’;
END IF;
IF ( lb_update = TRUE )
THEN
— UPDATE
— ————–
lc_dt_ud_mode := ‘UPDATE’;
END IF;
— Update Employee Assignment
— ———————————————
hr_assignment_api.update_emp_asg
( — Input data elements
— ——————————
p_effective_date => TO_DATE(’12-JUN-2011′),
p_datetrack_update_mode => lc_dt_ud_mode,
p_assignment_id => ln_assignment_id,
p_supervisor_id => NULL,
p_change_reason => NULL,
p_manager_flag => ‘N’,
p_bargaining_unit_code => NULL,
p_labour_union_member_flag => NULL,
p_segment1 => 204,
p_segment3 => ‘N’,
p_normal_hours => 10,
p_frequency => ‘W’,
— Output data elements
— ——————————-
p_object_version_number => ln_object_number,
p_soft_coding_keyflex_id => ln_soft_coding_keyflex_id,
p_concatenated_segments => lc_concatenated_segments,
p_comment_id => ln_comment_id,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_no_managers_warning => lb_no_managers_warning,
p_other_manager_warning => lb_other_manager_warning
);
— Find Date Track Mode for Second API
— ——————————————————
dt_api.find_dt_upd_modes
( p_effective_date => TO_DATE(’12-JUN-2011′),
p_base_table_name => ‘PER_ALL_ASSIGNMENTS_F’,
p_base_key_column => ‘ASSIGNMENT_ID’,
p_base_key_value => ln_assignment_id,
— Output data elements
— ——————————-
p_correction => lb_correction,
p_update => lb_update,
p_update_override => lb_update_override,
p_update_change_insert => lb_update_change_insert
);
IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
THEN
— UPDATE_OVERRIDE
— ——————————–
lc_dt_ud_mode := ‘UPDATE_OVERRIDE’;
END IF;
IF ( lb_correction = TRUE )
THEN
— CORRECTION
— ———————-
lc_dt_ud_mode := ‘CORRECTION’;
END IF;
IF ( lb_update = TRUE )
THEN
— UPDATE
— ————–
lc_dt_ud_mode := ‘UPDATE’;
END IF;
— Update Employee Assgment Criteria
— —————————————————–
hr_assignment_api.update_emp_asg_criteria
( — Input data elements
— ——————————
p_effective_date => TO_DATE(’12-JUN-2011′),
p_datetrack_update_mode => lc_dt_ud_mode,
p_assignment_id => ln_assignment_id,
p_location_id => 204,
p_grade_id => 29,
p_job_id => 16,
p_payroll_id => 52,
p_organization_id => 239,
p_employment_category => ‘FR’,
— Output data elements
— ——————————-
p_people_group_id => ln_people_group_id,
p_object_version_number => ln_object_number,
p_special_ceiling_step_id => ln_special_ceiling_step_id,
p_group_name => lc_group_name,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_org_now_no_manager_warning => lb_org_now_no_manager_warning,
p_other_manager_warning => lb_other_manager_warning,
p_spp_delete_warning => lb_spp_delete_warning,
p_entries_changed_warning => lc_entries_changed_warning,
p_tax_district_changed_warning => lb_tax_district_changed_warn
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line(SQLERRM);
END;
/
SHOW ERR;
Add terminating employees:
Hr_ex_employee_api (File name: peexeapi.pkb) This file contains three (3) procedures.....
hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp
Note: The API's have to be called in that order.
Address Insert or Update:
- Insert or Update Addresses : hr_person_address_api.create_person_address
- Update existing address records : hr_person_address_api.update_person_address
- Insert or create a phone : hr_phone_api.create_phone
- update existing phone records : hr_person_address_api.update_person_address
- Insert or create a schol and college information : per_esa_ins.ins
- update existing records : per_esa_upd.upd
- If deduction is ended, use the delete API to set end date : hr_entry_api.delete_element_entry
- Update deduction if exists, else insert it: hr_entry_api.insert_element_entry
Qualifications:
- Insert or create a qualifications information : per_qualifications_api.create_qualification
- update qualifications records : per_qualifications_api.update_qualification
End Date /delete Element Enties:
End Date /delete Element Enties :py_element_entry_api.delete_element_entry There are 4 date track delete modes available (variable p_datetrack_delete_mode)
- DELETE end date element entry
- DELETE_NEXT_CHANGE delete next changes
- FUTURE_CHANGE delete future changes
- ZAP completely remove element entry from the database
Insert or Update Direct Deposits:
- If direct deposit is ended, use the delete API to set end date: hr_personal_pay_method_api.delete_personal_pay_method
- Insert bank account if it does not exist : insert into pay_external_accounts
- Update direct deposit if exists, else insert it : hr_personal_pay_method_api.create_personal_pay_method
- Use this if required inactivate employee credit card process : hr_ex_employee_api.actual_termination_emp
Jobs:
- Create new Job : hr_job_api.create_job
- Updating the job details : hr_job_api.update_job
Positions
- Create new position : hr_position_api.create_position
- Updating the position details : hr_position_api.update_position
This post is very useful for me, much appreciated! :)
Great Post ..
Great Site….Good work faisal.