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;
