Based on your implementation you might decide that you do not need to use all of the predefined APIs to load external data. Run the Meta-Mapper for all APIs or for each single API that you select. The Meta-Mapper generates a specific PL/SQL package and view for each API.
Note: For APIs with overloaded interfaces, the Meta-Mapper will only generate code for the latest interface. The latest interface is the interface that has the greatest number of mandatory parameters.
Use the following SQL*PLUS command to generate packages and views for a number of APIs. (Not, however, all APIs, as the GENERATEALL name appears to suggest):
sql> execute hr_pump_meta_mapper.generateall;
See also: APIs Supported by the GENERATEALL Command., Oracle HRMS Implementation Guide
Use the following SQL*PLUS command to generate packages and views for one API:
sql> execute hr_pump_meta_mapper.generate( <package_name>,<procedure_name>);
For example:
sql> execute hr_pump_meta_mapper.generate( 'hr_employee_api', 'create_employee' );
The naming convention for the view is hrdpv_<api_module_name> and the naming convention for the PL/SQL package is hrdpp_<api module name>. This applies unless the name would exceed 30 bytes, in which case the name is truncated to 30 bytes. In the example, the name of the view is hrdpv_create_employee, and the name of the package is hrdpp_create_employee.
You can use the view to insert legacy data into the HRMS schema or the batch tables, or to update data already in the batch lines table. The PL/SQL package contains an insert_batch_lines procedure to make it easy to insert data from your external systems into the batch lines table; and a call procedure that executes the API on the rows in the batch lines table.
For each API the Meta-Mapper generates a view on the HR_PUMP_BATCH_LINES table that reflects the parameters of the API. This makes it easier to examine and update row values. The name of the view reflects the API name. For example, HRDPV_CREATE_EMPLOYEE. For a full listing of this view see: Table and View Descriptions, Oracle HRMS Implementation Guide.
In addition to the parameters for the API, the Meta-Mapper always creates the following columns in the view:
Column Description
----------------------------------------------------
BATCH_ID Foreign key to HR_PUMP_BATCH_HEADERS
BATCH_LINE_ID Foreign key to HR_PUMP_BATCH_LINES.
Primary key generated using the hr_pump_batch_lines_s sequence.
API_MODULE_ID Foreign key to HR_API_MODULES. This tells Data Pump which api to call for each row.
LINE_STATUS Load status of this API:
`U' - Unprocessed. This must be the initial value for all lines
'C' - Complete. The API call was successful and the changes have been committed.
'E' - Error.
'V' - Validated The API call was successful but the changes have not been committed.
USER_SEQUENCE Used to control processing order. For example, to make sure that address for an employee is loaded after the employee record has been created.
LINK_VALUE Use a unique link_value to link multiple rows in a single batch. Set this value when using parallel processing to make sure that related rows in a batch are processed together.
BUSINESS_GROUP_NAME Alternative business group name to use for a particular API call. If not null, this overrides the value specified in the batch header
Meta-Mapper also creates other columns for specific APIs. For example, some of the columns on the create employee view are:
P_EFFECTIVE_DATE
P_MANAGER_FLAG
P_ASSIGNMENT_USER_KEY
Other columns are created to reflect the PL/SQL OUT values returned from the API so that you can examine these values. For example:
P_NO_MANAGERS_WARNING
You do not need to know which columns of the batch lines table hold specific parameters for the API.
If you use the view to insert data to the batch lines table then remember that in addition to the data required for the insert batch line procedure you also need :
batch_line_id
Primary key generated using the hr_pump_batch_lines_s sequence.
line_status
Must be set to 'U' (unprocessed).
api_module_id
Foreign key to hr_api_modules.
The following query gets the api_module_id for create employee:
SELECT API_MODULE_ID
FROM HR_API_MODULES
WHERE UPPER(MODULE_NAME) = 'CREATE_EMPLOYEE'
AND UPPER(MODULE_PACKAGE) = 'HR_EMPLOYEE_API';
The Meta-Mapper also generates a separate package for each API to make it easier for you to load data to the batch lines table or to review the content of the table for specific APIs.
For example, the create_employee package hrdpp_create_employee contains two procedures:
insert_batch_lines
call
Use this procedure to simplify loading data into the batch lines table.
See also: Default and Null Values for API Parameters., Oracle HRMS Implementation Guide
A call to this procedure creates one row in the batch lines table, complete with all the parameters. For create employee, some of the parameters are:
p_batch_id number in
p_data_pump_batch_line_id number in default
p_data_pump_business_grp_name varchar2 in default
p_user_sequence number in default
p_link_value number in default
p_hire_date date in
p_last_name varchar2 in
p_sex varchar2 in
p_per_comments varchar2 in default
p_date_employee_data_verified date in default
p_date_of_birth date in default
p_email_address varchar2 in default
p_employee_number varchar2 in
p_expense_check _send_to_addres varchar2 in default
p_first_name varchar2 in default
p_known_as varchar2 in default
p_marital_status varchar2 in default
p_middle_names varchar2 in default
p_nationality varchar2 in default
p_national_identifier varchar2 in default
p_previous_last_name varchar2 in default
p_registered_disabled_flag varchar2 in default
p_title varchar2 in default
p_attribute1 varchar2 in default
p_attribute2 varchar2 in default
p_attribute3 varchar2 in default
p_attribute4 varchar2 in default
p_attribute5 varchar2 in default
p_attribute6 varchar2 in default
p_attribute7 varchar2 in default
p_attribute8 varchar2 in default
...
...
p_resume_exists varchar2 in default
p_resume_last_updated date in default
p_second_passport_exists varchar2 in default
p_student_status varchar2 in default
p_work_schedule varchar2 in default
p_suffix varchar2 in default
p_person_user_key varchar2 in
p_assignment_user_key varchar2 in
p_user_person_type varchar2 in default
p_vendor_name varchar2 in default
p_correspondence_language varchar2 in default
This example does not show all the parameters as there are many more.
The optional p_data_pump_business_grp_name parameter specifies a business group name to override the name specified in the batch header.
The optional p_data_pump_batch_line_id parameter specifies the batch_line_id for the inserted row (if necessary an existing row with this batch_line_id will be deleted).
Note: This procedure requires two user key values p_person_user_key and p_assignment_user_key. You must supply values for these keys. If you use Data Pump to create records in Oracle HRMS then Data Pump automatically inserts your key values and the HRMS key values generated by the APIs into the user keys table. For subsequent actions Data Pump can use these keys to match records from your external system with the Oracle HRMS records. A more detailed explanation and example is included in a later section of this document.
This is the actual 'wrapper' procedure executed by the Data Pump process to call the API and pass in the appropriate parameter values. The procedure takes two arguments: p_business_group_id and p_batch_line_id.
Note: Direct calls to this procedure are NOT supported. You must use the Data Pump concurrent process to execute the procedures.
The Meta-Mapper package also includes a help procedure hr_pump_meta_mapper help that returns information on the generated PL/SQL package and view names, and the batch lines table parameter values for a given API.
The help procedure has two parameters:
p_module_package
The name of API PL/SQL package
p_module_name
The name of API PL/SQL procedure
You must set server output on before calling this procedure.
For example, use the following SQL*PLUS to get help for hr_employee_api.create_employee:
sql> set serveroutput on size 1000000; sql> execute hr_pump_meta_mapper.help( 'hr_employee_api', 'create_employee' );
The output is as follows:
Generated package: hrdpp_create_employee
Generated view: hrdpv_create_employee
Parameter Name Type In/Out Default? Lookup Type
--------------- ----- ------ -------- -----------
P_HIRE_DATE DATE IN
P_LAST_NAME VARCHAR2 IN
P_SEX LOOKUP IN SEX
P_PER_COMMENTS VARCHAR2 IN DEFAULT
P_DATE_EMPLOYEE
_DATA_VERIFIED DATE IN DEFAULT
P_DATE_OF_BIRTH DATE IN DEFAULT
P_EMAIL_ADDRESS VARCHAR2 IN DEFAULT
P_EMPLOYEE_NUMBER VARCHAR2 IN
P_EXPENSE_CHECK _SEND_TO_ADDRES LOOKUP IN DEFAULT HOME_OFFICE
P_FIRST_NAME VARCHAR2 IN DEFAULT
P_KNOWN_AS VARCHAR2 IN DEFAULT
P_MARITAL_STATUS LOOKUP IN DEFAULT MAR_STATUS
P_MIDDLE_NAMES VARCHAR2 IN DEFAULT
P_NATIONALITY LOOKUP IN DEFAULT NATIONALITY
P_NATIONAL_IDENTIFIER VARCHAR2 IN DEFAULT
P_PREVIOUS_LAST_NAME VARCHAR2 IN DEFAULT
P_REGISTERED_DISABLED_FLAG LOOKUP IN DEFAULT YES_NO
P_TITLE LOOKUP IN DEFAULT TITLE
P_WORK_TELEPHONE VARCHAR2 IN DEFAULT
P_ATTRIBUTE_CATEGORY VARCHAR2 IN DEFAULT
P_ATTRIBUTE1 VARCHAR2 IN DEFAULT
P_ATTRIBUTE2 VARCHAR2 IN DEFAULT
P_ATTRIBUTE3 VARCHAR2 IN DEFAULT
P_ATTRIBUTE4 VARCHAR2 IN DEFAULT
P_ATTRIBUTE5 VARCHAR2 IN DEFAULT
P_ATTRIBUTE6 VARCHAR2 IN DEFAULT
...
P_ASSIGNMENT_SEQUENCE NUMBER OUT
P_ASSIGNMENT_NUMBER VARCHAR2 OUT
P_NAME_COMBINATION_WARNING BOOLEAN OUT
P_ASSIGN_PAYROLL_WARNING BOOLEAN OUT
P_USER_PERSON_TYPE VARCHAR2 IN DEFAULT
P_VENDOR_NAME VARCHAR2 IN DEFAULT
P_CORRESPONDENCE_LANGUAGE VARCHAR2 IN DEFAULT
...
The following is an explanation of the help output:
In the above example, the insert_batch_lines procedure is: hrdpp_create_employee.insert_batch_lines.
The Parameter Name column shows the name of the parameter as it appears in the insert_batch_lines procedure and generated view.
A parameter can have type USER_KEY which means that it is a user key (see the section User Key Values, Oracle HRMS Implementation Guide for more details). For example, P_SUPERVISOR_USER_KEY USER_KEY IN DEFAULT. User key parameters are implicitly of type VARCHAR2.
DATE parameter values are passed to the insert_batch_lines procedure as VARCHAR2 strings in the appropriate date format.
Data Pump makes use of two different date formats:
Unsigned Date Format: This can be either YYYY/MM/DD, or for dates in Oracle HRMS, the internal date format is YYYY/MM/DD HH24:MM:SS
Signed Date Format: This can be either SYYYY/MM/DD, or for dates in Oracle HRMS, the internal date format is SYYYY/MM/DD HH24:MM:SS. The signed date format has been introduced for HR_API_G_SYSDATE which must be capable of holding a negative date.
BOOLEAN parameter values are passed to the insert_batch_lines procedure as VARCHAR2 strings with the values TRUE or FALSE'.
The In/Out column has the value IN for parameters that are PL/SQL IN or IN/OUT when passed to the API, or are user key parameters. If the parameter is an API PL/SQL OUT parameter, then the In/Out column value is OUT.
Only IN parameters are arguments to the insert_batch_lines procedure. OUT parameters appear in the generated view.
The Default column has the value DEFAULT if the parameter's value is not required in the batch lines table. For mandatory parameters this column is empty.
Mandatory parameter values must be passed to the insert_batch_lines procedure.
If the parameter is a lookup parameter, the Lookup Type column contains the name of the parameter's lookup type.