Basic API to create employee

The below API is used to create a new employee and loads all the basic data required to create a employee.
emp_details_staging_tbl contains the data of employees to be loaded to per_all_people_f table. When p_validateis TRUE the API execution does not copy the data into per_all_people_f table. So it should be validated to FALSE. It is validated to TRUE only when testing the API to check it satisfies all the validations.

CREATE OR REPLACE PROCEDURE create_employee

IS

   CURSOR emp_csr

   IS

      SELECT *

        FROM emp_details_staging_tbl;

--IN PARAMETERS

   l_business_group_id           NUMBER;

   l_last_name                   VARCHAR2 (60);

   l_sex                         VARCHAR2 (10);

   l_date_of_birth               DATE;

   l_email_address               VARCHAR2 (60);

   l_first_name                  VARCHAR2 (60);

   l_known_as                    VARCHAR2 (60);

   l_marital_status              VARCHAR2 (10);

   l_nationality                 VARCHAR2 (60);

   l_title                       VARCHAR2 (10);

   l_country_of_birth            VARCHAR2 (60);

--INOUT PARAMETERS

   l_employee_number             NUMBER        DEFAULT NULL;

--OUT PARAMETERS

   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 (60);

   l_per_comment_id              NUMBER;

   l_assignment_sequence         NUMBER;

   l_assignment_number           VARCHAR2 (60);

   l_name_combination_warning    BOOLEAN;

   l_assign_payroll_warning      BOOLEAN;

   l_orig_hire_warning           BOOLEAN;

BEGIN

   FOR i IN emp_csr

   LOOP

      EXIT WHEN emp_csr%NOTFOUND;

      l_business_group_id := i.business_group_id;

      l_last_name := i.last_name;

      l_sex := i.sex;

      l_date_of_birth := i.date_of_birth;

      l_email_address := i.email_address;

      l_first_name := i.first_name;

      l_known_as := i.known_as;

      l_marital_status := i.marital_status;

      l_nationality := i.nationality;

      l_title := i.title;

      l_country_of_birth := i.country_of_birth;

      hr_employee_api.create_employee

                 (p_validate                       => FALSE,

                  p_hire_date                      => l_hiredate,

                  p_business_group_id              => l_business_group_id,

                  p_last_name                      => l_last_name,

                  p_sex                            => l_sex,

                  p_date_of_birth                  => l_date_of_birth,

                  p_email_address                  => l_email_address,

                  p_first_name                     => l_first_name,

                  p_known_as                       => l_known_as,

                  p_marital_status                 => l_marital_status,

                  p_nationality                    => l_nationality,

                  p_title                          => l_title,

                  p_attribute_category             => 'EMP',

                  p_country_of_birth               => l_country_of_birth,

                  p_employee_number                => l_employee_number,

                  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 LOOP;

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('ERROR : ' || sqleerm);

END create_employee;