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.

HRMS APIs with hard coded syntax

Hardcoded HRMS API Examples (Doc ID 1505063.1)

API to cancel an AP invoice in R12

Declare
v_boolean               BOOLEAN;
v_error_code            VARCHAR2(100);
v_debug_info            VARCHAR2(1000);
begin

API procedure for creating a assignment

The below API copies the data from a staging table named xx_assg_staging_tbl into per_all_assignments_f. This API is executed once the create employee API is executed. Since in oracle apps the assignment is automatically created when a employee is created, the values of assignment_id, object_version_number,assignment_number and other fields are automatically generated. Hence we have to fetch these values as well from per_all_assignments_fwhen executing this API to avoid any execution errors. The values of supervisor_id, employee_category and other assignment related data can be stored in the staging table which is then copied into per_all_assignments_f after the execution of API.

CREATE PROCEDURE create_assignment

IS

   CURSOR csr_asg

   IS

      SELECT xast.*, papf.person_id

        FROM xx_assg_staging_tbl xast,

             per_all_assignments_f paaf,

             per_all_people_f

       WHERE xast.emp_num = papf.employee_number

         AND papf.person_id = paaf.person_id;

--IN parametes

   l_effective_date                 DATE;

   l_assignment_id                  NUMBER;

   l_assignment_number              VARCHAR2 (20);

   l_change_reason                  VARCHAR2 (80);

   l_assignment_status_type_id      NUMBER;

   l_comments                       VARCHAR2 (80);

   l_date_probation_end             DATE;

   l_default_code_comb_id           NUMBER;

   l_frequency                      VARCHAR2 (80);

   l_internal_address_line          VARCHAR2 (80);

   l_manager_flag                   VARCHAR2;

   l_normal_hours                   NUMBER;

   l_perf_review_period             NUMBER;

   l_perf_review_period_frequency   VARCHAR2 (80);

   l_probation_period               NUMBER;

   l_probation_unit                 VARCHAR2 (80);

   l_projected_assignment_end       VARCHAR2 (80);

   l_sal_review_period              NUMBER;

   l_sal_review_period_frequency    VARCHAR2 (80);

   l_set_of_books_id                NUMBER;

   l_source_type                    VARCHAR2 (80);

   l_time_normal_finish             VARCHAR2 (80);

   l_time_normal_start              VARCHAR2 (80);

   l_bargaining_unit_code           VARCHAR2 (80);

   l_labour_union_member_flag       VARCHAR2 (80);

   l_hourly_salaried_code           VARCHAR2 (80);

   l_ass_attribute_category         VARCHAR2 (80);

   l_ass_attribute1                 VARCHAR2 (80);

   l_ass_attribute2                 VARCHAR2 (80);

   l_ass_attribute3                 VARCHAR2 (80);

   l_ass_attribute4                 VARCHAR2 (80);

   l_ass_attribute5                 VARCHAR2 (80);

   l_ass_attribute6                 VARCHAR2 (80);

   l_ass_attribute7                 VARCHAR2 (80);

   l_ass_attribute8                 VARCHAR2 (80);

   l_ass_attribute9                 VARCHAR2 (80);

   l_ass_attribute10                VARCHAR2 (80);

   l_ass_attribute11                VARCHAR2 (80);

   l_ass_attribute12                VARCHAR2 (80);

   l_ass_attribute13                VARCHAR2 (80);

   l_ass_attribute14                VARCHAR2 (80);

   l_ass_attribute15                VARCHAR2 (80);

   l_ass_attribute16                VARCHAR2 (80);

   l_ass_attribute17                VARCHAR2 (80);

   l_ass_attribute18                VARCHAR2 (80);

   l_ass_attribute19                VARCHAR2 (80);

   l_ass_attribute20                VARCHAR2 (80);

   l_ass_attribute21                VARCHAR2 (80);

   l_ass_attribute22                VARCHAR2 (80);

   l_ass_attribute23                VARCHAR2 (80);

   l_ass_attribute24                VARCHAR2 (80);

   l_ass_attribute25                VARCHAR2 (80);

   l_ass_attribute26                VARCHAR2 (80);

   l_ass_attribute27                VARCHAR2 (80);

   l_ass_attribute28                VARCHAR2 (80);

   l_ass_attribute29                VARCHAR2 (80);

   l_ass_attribute30                VARCHAR2 (80);

   l_title                          VARCHAR2 (80);

   l_segment1                       VARCHAR2 (80);

   l_segment2                       VARCHAR2 (80);

   l_segment3                       VARCHAR2 (80);

   l_segment4                       VARCHAR2 (80);

   l_segment5                       VARCHAR2 (80);

   l_segment6                       VARCHAR2 (80);

   l_segment7                       VARCHAR2 (80);

   l_segment8                       VARCHAR2 (80);

   l_segment9                       VARCHAR2 (80);

   l_segment10                      VARCHAR2 (80);

   l_segment11                      VARCHAR2 (80);

   l_segment12                      VARCHAR2 (80);

   l_segment13                      VARCHAR2 (80);

   l_segment14                      VARCHAR2 (80);

   l_segment15                      VARCHAR2 (80);

   l_segment16                      VARCHAR2 (80);

   l_segment17                      VARCHAR2 (80);

   l_segment18                      VARCHAR2 (80);

   l_segment19                      VARCHAR2 (80);

   l_segment20                      VARCHAR2 (80);

   l_segment21                      VARCHAR2 (80);

   l_segment22                      VARCHAR2 (80);

   l_segment23                      VARCHAR2 (80);

   l_segment24                      VARCHAR2 (80);

   l_segment25                      VARCHAR2 (80);

   l_segment26                      VARCHAR2 (80);

   l_segment27                      VARCHAR2 (80);

   l_segment28                      VARCHAR2 (80);

   l_segment29                      VARCHAR2 (80);

   l_segment30                      VARCHAR2 (80);

   l_contract_id                    NUMBER;

   l_establishment_id               NUMBER;

   l_collective_agreement_id        NUMBER;

   l_cagr_id_flex_num               NUMBER;

   l_cag_segment1                   VARCHAR2 (80);

   l_cag_segment2                   VARCHAR2 (80);

   l_cag_segment3                   VARCHAR2 (80);

   l_cag_segment4                   VARCHAR2 (80);

   l_cag_segment5                   VARCHAR2 (80);

   l_cag_segment6                   VARCHAR2 (80);

   l_cag_segment7                   VARCHAR2 (80);

   l_cag_segment8                   VARCHAR2 (80);

   l_cag_segment9                   VARCHAR2 (80);

   l_cag_segment10                  VARCHAR2 (80);

   l_cag_segment11                  VARCHAR2 (80);

   l_cag_segment12                  VARCHAR2 (80);

   l_cag_segment13                  VARCHAR2 (80);

   l_cag_segment14                  VARCHAR2 (80);

   l_cag_segment15                  VARCHAR2 (80);

   l_cag_segment16                  VARCHAR2 (80);

   l_cag_segment17                  VARCHAR2 (80);

   l_cag_segment18                  VARCHAR2 (80);

   l_cag_segment19                  VARCHAR2 (80);

   l_cag_segment20                  VARCHAR2 (80);

   l_notice_period                  NUMBER (80);

   l_notice_period_uom              VARCHAR2 (80);

   l_employee_category              VARCHAR2 (80);

   l_work_at_home                   VARCHAR2 (80);

   l_job_post_source_name           VARCHAR2 (80);

   l_supervisor_assignment_id       NUMBER;

--out parameters

   l_cagr_concatenated_segments     VARCHAR2 (80);

   l_concatenated_segments          VARCHAR2 (80);

   l_comment_id                     NUMBER;

   l_effective_start_date           DATE;

   l_effective_end_date             DATE;

   l_no_managers_warning            BOOLEAN;

   l_other_manager_warning          BOOLEAN;

   l_hourly_salaried_warning        BOOLEAN;

   l_gsp_post_process_warning       VARCHAR2 (80);

--IN OUT parameters

   l_cagr_grade_def_id              NUMBER;

   l_soft_coding_keyflex_id         NUMBER;

   l_object_version_number          NUMBER;

BEGIN

   OPEN csr_asg;

   FOR i IN csr_asg

   LOOP

      BEGIN

         SELECT effective_start_date, assignment_id, object_version_number,

                assignment_number

           INTO l_effective_date, l_assignment_id, l_object_version_number,

                l_assignment_number

           FROM per_all_assignments_f

          WHERE person_id = i.person_id;

      END;

      l_change_reason := NULL;

      l_assignment_status_type_id := NULL;

      l_comments := NULL;

      l_date_probation_end := NULL;

      l_default_code_comb_id := NULL;

      l_frequency := NULL;

      l_internal_address_line := NULL;

      l_manager_flag := NULL;

      l_normal_hours := NULL;

      l_perf_review_period := NULL;

      l_perf_review_period_frequency := NULL;

      l_probation_period := NULL;

      l_probation_unit := NULL;

      l_projected_assignment_end := NULL;

      l_sal_review_period := NULL;

      l_sal_review_period_frequency := NULL;

      l_set_of_books_id := NULL;

      l_source_type := NULL;

      l_time_normal_finish := NULL;

      l_time_normal_start := NULL;

      l_bargaining_unit_code := NULL;

      l_labour_union_member_flag := NULL;

      l_hourly_salaried_code := NULL;

      l_ass_attribute_category := NULL;

      l_ass_attribute1 := NULL;

      l_ass_attribute2 := NULL;

      l_ass_attribute3 := NULL;

      l_ass_attribute4 := NULL;

      l_ass_attribute5 := NULL;

      l_ass_attribute6 := NULL;

      l_ass_attribute7 := NULL;

      l_ass_attribute8 := NULL;

      l_ass_attribute9 := NULL;

      l_ass_attribute10 := NULL;

      l_ass_attribute11 := NULL;

      l_ass_attribute12 := NULL;

      l_ass_attribute13 := NULL;

      l_ass_attribute14 := NULL;

      l_ass_attribute15 := NULL;

      l_ass_attribute16 := NULL;

      l_ass_attribute17 := NULL;

      l_ass_attribute18 := NULL;

      l_ass_attribute19 := NULL;

      l_ass_attribute20 := NULL;

      l_ass_attribute21 := NULL;

      l_ass_attribute22 := NULL;

      l_ass_attribute23 := NULL;

      l_ass_attribute24 := NULL;

      l_ass_attribute25 := NULL;

      l_ass_attribute26 := NULL;

      l_ass_attribute27 := NULL;

      l_ass_attribute28 := NULL;

      l_ass_attribute29 := NULL;

      l_ass_attribute30 := NULL;

      l_title := NULL;

      l_segment1 := NULL;

      l_segment2 := NULL;

      l_segment3 := NULL;

      l_segment4 := NULL;

      l_segment5 := NULL;

      l_segment6 := NULL;

      l_segment7 := NULL;

      l_segment8 := NULL;

      l_segment9 := NULL;

      l_segment10 := NULL;

      l_segment11 := NULL;

      l_segment12 := NULL;

      l_segment13 := NULL;

      l_segment14 := NULL;

      l_segment15 := NULL;

      l_segment16 := NULL;

      l_segment17 := NULL;

      l_segment18 := NULL;

      l_segment19 := NULL;

      l_segment20 := NULL;

      l_segment21 := NULL;

      l_segment22 := NULL;

      l_segment23 := NULL;

      l_segment24 := NULL;

      l_segment25 := NULL;

      l_segment26 := NULL;

      l_segment27 := NULL;

      l_segment28 := NULL;

      l_segment29 := NULL;

      l_segment30 := NULL;

      l_contract_id := NULL;

      l_establishment_id := NULL;

      l_collective_agreement_id := NULL;

      l_cagr_id_flex_num := NULL;

      l_cag_segment1 := NULL;

      l_cag_segment2 := NULL;

      l_cag_segment3 := NULL;

      l_cag_segment4 := NULL;

      l_cag_segment5 := NULL;

      l_cag_segment6 := NULL;

      l_cag_segment7 := NULL;

      l_cag_segment8 := NULL;

      l_cag_segment9 := NULL;

      l_cag_segment10 := NULL;

      l_cag_segment11 := NULL;

      l_cag_segment12 := NULL;

      l_cag_segment13 := NULL;

      l_cag_segment14 := NULL;

      l_cag_segment15 := NULL;

      l_cag_segment16 := NULL;

      l_cag_segment17 := NULL;

      l_cag_segment18 := NULL;

      l_cag_segment19 := NULL;

      l_cag_segment20 := NULL;

      l_notice_period := NULL;

      l_notice_period_uom := NULL;

      l_employee_category := NULL;

      l_work_at_home := NULL;

      l_job_post_source_name := NULL;

      l_supervisor_assignment_id := NULL;

      l_cagr_concatenated_segments := NULL;

      l_concatenated_segments := NULL;

      l_comment_id := NULL;

      l_effective_start_date := NULL;

      l_effective_end_date := NULL;

      l_no_managers_warning := NULL;

      l_other_manager_warning := NULL;

      l_hourly_salaried_warning := NULL;

      l_gsp_post_process_warning := NULL;

      l_soft_coding_keyflex_id := NULL;

      l_cagr_grade_def_id := NULL;

      l_object_version_number := NULL;

      hr_assignment_api.update_emp_asg

            (p_validate                          => FALSE,

             p_effective_date                    => l_effective_date,

             p_datetrack_update_mode             => 'CORRECTION',

             p_assignment_id                     => l_assignment_id,

             p_supervisor_id                     => l_supervisor_id,

             p_assignment_number                 => l_assignment_number,

             p_change_reason                     => l_change_reason,

             p_assignment_status_type_id         => l_assignment_status_type_id,

             p_comments                          => l_comments,

             p_date_probation_end                => l_date_probation_end,

             p_default_code_comb_id              => l_default_code_comb_id,

             p_frequency                         => l_frequency,

             p_internal_address_line             => l_internal_address_line,

             p_manager_flag                      => l_manager_flag,

             p_normal_hours                      => l_normal_hours,

             p_perf_review_period                => l_perf_review_period,

             p_perf_review_period_frequency      => l_perf_review_period_frequency,

             p_probation_period                  => l_probation_period,

             p_probation_unit                    => l_probation_unit,

             p_projected_assignment_end          => l_projected_assignment_end,

             p_sal_review_period                 => l_sal_review_period,

             p_sal_review_period_frequency       => l_sal_review_period_frequency,

             p_set_of_books_id                   => l_set_of_books_id,

             p_source_type                       => l_source_type,

             p_time_normal_finish                => l_time_normal_finish,

             p_time_normal_start                 => l_time_normal_start,

             p_bargaining_unit_code              => l_bargaining_unit_code,

             p_labour_union_member_flag          => l_labour_union_member_flag,

             p_hourly_salaried_code              => l_hourly_salaried_code,

             p_ass_attribute_category            => l_ass_attribute_category,

             p_ass_attribute1                    => l_ass_attribute1,

             p_ass_attribute2                    => l_ass_attribute2,

             p_ass_attribute3                    => l_ass_attribute3,

             p_ass_attribute4                    => l_ass_attribute4,

             p_ass_attribute5                    => l_ass_attribute5,

             p_ass_attribute6                    => l_ass_attribute6,

             p_ass_attribute7                    => l_ass_attribute7,

             p_ass_attribute8                    => l_ass_attribute8,

             p_ass_attribute9                    => l_ass_attribute9,

             p_ass_attribute10                   => l_ass_attribute10,

             p_ass_attribute11                   => l_ass_attribute11,

             p_ass_attribute12                   => l_ass_attribute12,

             p_ass_attribute13                   => l_ass_attribute13,

             p_ass_attribute14                   => l_ass_attribute14,

             p_ass_attribute15                   => l_ass_attribute15,

             p_ass_attribute16                   => l_ass_attribute16,

             p_ass_attribute17                   => l_ass_attribute17,

             p_ass_attribute18                   => l_ass_attribute18,

             p_ass_attribute19                   => l_ass_attribute19,

             p_ass_attribute20                   => l_ass_attribute20,

             p_ass_attribute21                   => l_ass_attribute21,

             p_ass_attribute22                   => l_ass_attribute22,

             p_ass_attribute23                   => l_ass_attribute23,

             p_ass_attribute24                   => l_ass_attribute24,

             p_ass_attribute25                   => l_ass_attribute25,

             p_ass_attribute26                   => l_ass_attribute26,

             p_ass_attribute27                   => l_ass_attribute27,

             p_ass_attribute28                   => l_ass_attribute28,

             p_ass_attribute29                   => l_ass_attribute29,

             p_ass_attribute30                   => l_ass_attribute30,

             p_title                             => l_title,

             p_segment1                          => l_segment1,

             p_segment2                          => l_segment2,

             p_segment3                          => l_segment3,

             p_segment4                          => l_segment4,

             p_segment5                          => l_segment5,

             p_segment6                          => l_segment6,

             p_segment7                          => l_segment7,

             p_segment8                          => l_segment8,

             p_segment9                          => l_segment9,

             p_segment10                         => l_segment10,

             p_segment11                         => l_segment11,

             p_segment12                         => l_segment12,

             p_segment13                         => l_segment13,

             p_segment14                         => l_segment14,

             p_segment15                         => l_segment15,

             p_segment16                         => l_segment16,

             p_segment17                         => l_segment17,

             p_segment18                         => l_segment18,

             p_segment19                         => l_segment19,

             p_segment20                         => l_segment20,

             p_segment21                         => l_segment21,

             p_segment22                         => l_segment22,

             p_segment23                         => l_segment23,

             p_segment24                         => l_segment24,

             p_segment25                         => l_segment25,

             p_segment26                         => l_segment26,

             p_segment27                         => l_segment27,

             p_segment28                         => l_segment28,

             p_segment29                         => l_segment29,

             p_segment30                         => l_segment30,

             p_contract_id                       => l_contract_id,

             p_establishment_id                  => l_establishment_id,

             p_collective_agreement_id           => l_collective_agreement_id,

             p_cagr_id_flex_num                  => l_cagr_id_flex_num,

             p_cag_segment1                      => l_cag_segment1,

             p_cag_segment2                      => l_cag_segment2,

             p_cag_segment3                      => l_cag_segment3,

             p_cag_segment4                      => l_cag_segment4,

             p_cag_segment5                      => l_cag_segment5,

             p_cag_segment6                      => l_cag_segment6,

             p_cag_segment7                      => l_cag_segment7,

             p_cag_segment8                      => l_cag_segment8,

             p_cag_segment9                      => l_cag_segment9,

             p_cag_segment10                     => l_cag_segment10,

             p_cag_segment11                     => l_cag_segment11,

             p_cag_segment12                     => l_cag_segment12,

             p_cag_segment13                     => l_cag_segment13,

             p_cag_segment14                     => l_cag_segment14,

             p_cag_segment15                     => l_cag_segment15,

             p_cag_segment16                     => l_cag_segment16,

             p_cag_segment17                     => l_cag_segment17,

             p_cag_segment18                     => l_cag_segment18,

             p_cag_segment19                     => l_cag_segment19,

             p_cag_segment20                     => l_cag_segment20,

             p_notice_period                     => l_notice_period,

             p_notice_period_uom                 => l_notice_period_uom,

             p_employee_category                 => l_employee_category,

             p_work_at_home                      => l_work_at_home,

             p_job_post_source_name              => l_job_post_source_name,

             p_supervisor_assignment_id          => l_supervisor_assignment_id,

             p_cagr_concatenated_segments        => l_cagr_concatenated_segments,

             p_concatenated_segments             => l_concatenated_segments,

             p_comment_id                        => l_comment_id,

             p_effective_start_date              => l_effective_start_date,

             p_effective_end_date                => l_effective_end_date,

             p_no_managers_warning               => l_no_managers_warning,

             p_other_manager_warning             => l_other_manager_warning,

             p_hourly_salaried_warning           => l_hourly_salaried_warning,

             p_gsp_post_process_warning          => l_gsp_post_process_warning,

             p_soft_coding_keyflex_id            => l_soft_coding_keyflex_id,

             p_cagr_grade_def_id                 => l_cagr_grade_def_id,

             p_object_version_number             => l_object_version_number

            );

   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line ('NO DATA FOUND');

   WHEN OTHERS

   THEN

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

END create_assignment;