hr_assignment_api.delete assignment example

• About Me: Shailender Thallam
• Ask a Question

API to Purge/Delete Employee from HRMS application

• by Shailender Thallam
• July 3, 2013 December 23, 2014

You can use API HR_PERSON_API.DELETE_PERSON to delete an employee(person) and the associated person related records.

DECLARE -- Input Variables l_validate BOOLEAN := false; l_effective_date DATE := sysdate; l_person_id NUMBER := 123048; l_perform_predel_validation BOOLEAN := false; -- Output Variables l_person_org_manager_warning VARCHAR2(2000); BEGIN -- Calling API HR_PERSON_API.DELETE_PERSON hr_person_api.delete_person(p_validate => l_validate , p_effective_date => l_effective_date , p_person_id => l_person_id , p_perform_predel_validation => l_perform_predel_validation , p_person_org_manager_warning => l_person_org_manager_warning ); -- dbms_output.put_line('Employee deleted successfully'); -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error : ' || sqlerrm); end; /

Some more information on the API parameters

p_perform_predel_validation : When this parameter is set to TRUE, the API performs a check to see if any data in addition to that set up by default, e.g. data in per_all_people_f, per_all_assignments_f, per_periods_of_service exists for the person. If no additional data exists, then the API cascade deletes the person data. If any additional data exists for this person in any non-HRMS tables, then this person will not be deleted irrespective of whether only default data exists. When this parameter is set to FALSE, the API cascade deletes all data held in HRMS tables for this person, provided no additional data exists in any non-HRMS tables.

p_person_org_manager_warning : If the person being deleted is an organization manager, then a warning message will be returned otherwise no value will be returned.

Prerequisites:

• Employee and fnd_user link must be removed by removing the person in users form
• No Payroll should be processed on employee till-date

Note: If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.

Source: Arun Rathod’s blog

For Further Reading

• How to Delete XML Publisher Definition and Template
• Setup to enable Effective Date Tracking popup in Oracle HRMS
• List of Profile Options in Oracle HRMS R12

OracleAppsDNA.com

Oracle Apps Technical

Thursday 13 march 2014, oracle hrms api – update employee assignment & supervisor adding.

3 comments:

Very much Helpful! Thanks :)

Thank you so much, very useful :-)

what if we have multiple assignment then how should we tell the api that we need supervisor on active one. becasue the active assignment flag of both assignments are Y.

Doyen System Pvt. Ltd.

Tuesday 27 September 2016

Oracle hrms- person, assignment , job , location conversion using api, no comments:, post a comment.

Oracle Applications Knowledge Repository

Monday, september 3, 2012, api to delete assignment, no comments:, post a comment.

Search This Blog

Oracle apps technical, hr_assignment_api.update_emp_asg in hrms, popular posts from this blog, query for item onhand quantity & lot wise query, customer site creation api ( hz_cust_account_site_v2pub.create_cust_acct_site).

hr_assignment_api.update_emp_asg_criteria error

Hi all, I am trying to update the emp assignment using the api. But i get the error, ORA-06550: line 18, column 4: PLS-00306: wrong number or types of arguments in call to ‘UPDATE_EMP_ASG_CRITERIA’ ORA-06550: line 18, column 4: PL/SQL: Statement ignored This is my code, what is wrong i couldnt find what is wrong in these, Please help. DECLARE l_people_group_id number; l_object_version_number number; l_special_ceiling_step_id number; l_group_name varchar2(50); l_effective_start_date date; l_effective_end_date date; l_org_now_no_manager_warning boolean; l_other_manager_warning boolean; l_spp_delete_warning boolean; l_entries_changed_warning varchar2(50); l_tax_district_changed_warning boolean; l_called_from_mass_update boolean; l_grade_id number; l_payroll_id number; l_pay_basis_id number; BEGIN HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA ( p_effective_date =>trunc(sysdate) ,p_datetrack_update_mode =>‘CORRECTION’ ,p_assignment_id =>10574 ,p_validate => FALSE ,p_position_id =>32 ,p_job_id =>12 ,p_location_id =>207 ,p_organization_id =>207 ,p_people_group_id =>L_PEOPLE_GROUP_ID ,p_object_version_number =>l_object_version_number ,p_special_ceiling_step_id =>l_special_ceiling_step_id ,p_group_name =>l_group_name ,p_effective_start_date =>l_effective_start_date ,p_effective_end_date =>l_effective_end_date ,p_org_now_no_manager_warning =>l_org_now_no_manager_warning ,p_other_manager_warning =>l_other_manager_warning ,p_spp_delete_warning =>l_spp_delete_warning ,p_entries_changed_warning =>l_entries_changed_warning ,p_tax_district_changed_warning =>l_tax_district_changed_warning); end; Thanks in advance, The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/

I found something this error will occur because of wrong type declaration of parameter. first check this parameter declare as a Boolean l_called_from_mass_update Boolean ; if you declare as a varchar parameter it will throw the above error Cheers Diyoni

Hi All I tested u code in my instance and change the following things and it work fine with me . So plz have a look into the code Declare l_people_group_id number; l_object_version_number number:=10; --Added By me while updating u have to pass the Correct object version number l_special_ceiling_step_id number; l_group_name varchar2(500); --just Increase the size l_effective_start_date date; l_effective_end_date date; l_org_now_no_manager_warning boolean; l_other_manager_warning boolean; l_spp_delete_warning boolean; l_entries_changed_warning varchar2(500); --just Increase the size l_tax_district_changed_warning boolean; l_called_from_mass_update boolean; l_grade_id number; l_payroll_id number; l_pay_basis_id number; BEGIN HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA ( p_effective_date =>sysdate ,p_datetrack_update_mode =>‘CORRECTION’ ,p_assignment_id =>11281 ,p_validate => FALSE ,p_called_from_mass_update => FALSE–Added By me because there are 3 API with same name but current one using this ,p_grade_id =>20001 ,p_position_id =>23664 ,p_job_id =>2113 ,p_location_id =>121 ,p_organization_id =>607 ,p_people_group_id =>L_PEOPLE_GROUP_ID ,p_object_version_number =>l_object_version_number ,p_special_ceiling_step_id =>l_special_ceiling_step_id ,p_group_name =>l_group_name ,p_effective_start_date =>l_effective_start_date ,p_effective_end_date =>l_effective_end_date ,p_org_now_no_manager_warning =>l_org_now_no_manager_warning ,p_other_manager_warning =>l_other_manager_warning ,p_spp_delete_warning =>l_spp_delete_warning ,p_entries_changed_warning =>l_entries_changed_warning ,p_tax_district_changed_warning =>l_tax_district_changed_warning); end;

I hope it will work for u and if u have any concern just let me know Regards Asif Ali Khan Technical Consultant Evolutionary Systems Pvt. Ltd. India Cell : +91 9776047348 KSA Cell : +966 566511273 UAE Cell : +971 553877671 Email : [email protected]

I know. What I meant was the call to update_emp_asg_criteria was through. There was no problem with the parameter types etc and there was no issue with the custom code as reported.

Hi Sridhar,

This code is sample only, so you have to change the values assignment_id, position_id, etc as per your application values before running this script

Muthu From: sridharmorti via oracle-apps-l [ mailto:[email protected] ] Sent: 16 November 2009 16:44 To: Periyanan, Muthu {PI} Subject: Re: [oracle-apps-l] hr_assignment_api.update_emp_asg_criteria error

Posted by sridharmorti (Consultant) on Nov 16 at 11:33 AM Mark this reply as helpfulMark as helpful

Thank You very much!!! The code you have given works fine.

It is working now and able to update the assignment.

Thanks again for your gr8 help!!!

Thanks All!

When I execute the same piece of code, I am getting the folLowing error which is being thrown from HR_ASSIGNMENT_API. DECLARE * ERROR at line 1: ORA-20001: The primary key specified is invalid Cause: The primary key values specified are invalid and do not exist in the schema. Action: Check the primary key values before attempting to carry out the operation again. ORA-06512: at “APPS.HR_ASSIGNMENT_API”, line 16009 ORA-06512: at “APPS.HR_ASSIGNMENT_API”, line 14644 ORA-06512: at “APPS.HR_ASSIGNMENT_API”, line 14429 ORA-06512: at line 18

So be assured, there is nothing wrong in your code. Please check the declaration of the function in your database by doing the following from SQL prompt : DESC HR_ASSIGNMENT_API. and check how UPDATE_EMP_ASG_CRITERIA function has been defined in your database.

I found the problem, it is to do with the datatype of the out variables, 4 of them have to be declared as boolean. Try the code below.

DECLARE l_effective_date DATE := SYSDATE; l_datetrack_update_mod VARCHAR2 (100) := ‘U’; l_assignment_id NUMBER := 32160; l_grade_id NUMBER := NULL; l_position_id NUMBER := 59473; l_job_id NUMBER := 227; l_payroll_id NUMBER := NULL; l_location_id NUMBER := 204; l_organization_id NUMBER := 626; l_pay_basis_id NUMBER := NULL; l_segment1 VARCHAR2 (100); l_segment2 VARCHAR2 (100); l_segment3 VARCHAR2 (100); l_old_obj_ver_number NUMBER; l_special_ceiling_step_id NUMBER; l_people_group_id NUMBER; l_soft_coding_keyflex_id NUMBER; l_group_name VARCHAR2 (100); l_effective_start_date DATE; l_effective_end_date DATE; l_org_now_no_manager_warning BOOLEAN ; l_other_manager_warning BOOLEAN ; l_spp_delete_warning BOOLEAN ; l_entries_changed VARCHAR2 (100); l_tax_district_changed_warning BOOLEAN ; l_concatenated_segments VARCHAR2 (100); l_gsp_post_process_warning VARCHAR2 (100); BEGIN hr_assignment_api.update_emp_asg_criteria (p_effective_date => l_effective_date, p_datetrack_update_mode => l_datetrack_update_mod, p_assignment_id => l_assignment_id, p_validate => NULL, p_called_from_mass_update => NULL, p_grade_id => l_grade_id, p_position_id => l_position_id, p_job_id => l_job_id, p_payroll_id => l_payroll_id, p_location_id => l_location_id, p_organization_id => l_organization_id, p_pay_basis_id => l_pay_basis_id, p_segment1 => l_segment1, p_segment2 => l_segment2, p_segment3 => l_segment3, p_segment4 => NULL, p_segment5 => NULL, p_segment6 => NULL, p_segment7 => NULL, p_segment8 => NULL, p_segment9 => NULL, p_segment10 => NULL, p_segment11 => NULL, p_segment12 => NULL, p_segment13 => NULL, p_segment14 => NULL, p_segment15 => NULL, p_segment16 => NULL, p_segment17 => NULL, p_segment18 => NULL, p_segment19 => NULL, p_segment20 => NULL, p_segment21 => NULL, p_segment22 => NULL, p_segment23 => NULL, p_segment24 => NULL, p_segment25 => NULL, p_segment26 => NULL, p_segment27 => NULL, p_segment28 => NULL, p_segment29 => NULL, p_segment30 => NULL, p_employment_category => NULL, p_concat_segments => NULL, p_contract_id => NULL, p_establishment_id => NULL, p_scl_segment1 => NULL, p_grade_ladder_pgm_id => NULL, p_supervisor_assignment_id => NULL, p_object_version_number => l_old_obj_ver_number, p_special_ceiling_step_id => l_special_ceiling_step_id, p_people_group_id => l_people_group_id, p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, p_group_name => l_group_name, p_effective_start_date => l_effective_start_date, p_effective_end_date => l_effective_end_date, p_org_now_no_manager_warning => l_org_now_no_manager_warning, p_other_manager_warning => l_other_manager_warning, p_spp_delete_warning => l_spp_delete_warning, p_entries_changed_warning => l_entries_changed, p_tax_district_changed_warning => l_tax_district_changed_warning, p_concatenated_segments => l_concatenated_segments, p_gsp_post_process_warning => l_gsp_post_process_warning ); END;

Muthu From: DURANCE via oracle-apps-l [ mailto:[email protected] ] Sent: 16 November 2009 13:06 To: Periyanan, Muthu {PI} Subject: RE: [oracle-apps-l] hr_assignment_api.update_emp_asg_criteria error

Posted by DURANCE on Nov 16 at 7:51 AM Mark this reply as helpfulMark as helpful

Hi can u explain u requirement. What i understand from u request is that u want update the assignment of an existing employee . If u check at the time of creation it will two API !st one will do the assignment creation and the 2nd one will do the PPG and assignment position and job creation .So try to do the same from the application and chk what are mandatory field . Asif Ali Khan Technical Consultant Evolutionary Systems Pvt. Ltd. India Cell : +91 9776047348 KSA Cell : +966 566511273 UAE Cell : +971 553877671 Email : [email protected]

Hi, Still same error Don’t know what the error is- ORA-06550: line 31, column 4: PLS-00306: wrong number or types of arguments in call to ‘UPDATE_EMP_ASG_CRITERIA’

Hi, Try this, hope it works. You were still missing some parameters, which I’ve added in

DECLARE l_effective_date DATE := SYSDATE; l_datetrack_update_mod VARCHAR2 (100) := TRUE; l_assignment_id NUMBER := 32160; l_grade_id NUMBER := NULL; l_position_id NUMBER := 59473; l_job_id NUMBER := 227; l_payroll_id NUMBER := NULL; l_location_id NUMBER := 204; l_organization_id NUMBER := 626; l_pay_basis_id NUMBER := NULL; l_segment1 VARCHAR2 (100); l_segment2 VARCHAR2 (100); l_segment3 VARCHAR2 (100); l_old_obj_ver_number NUMBER; l_special_ceiling_step_id NUMBER; l_people_group_id NUMBER; l_soft_coding_keyflex_id NUMBER; l_group_name VARCHAR2 (100); l_effective_start_date DATE; l_effective_end_date DATE; l_org_now_no_manager_warning VARCHAR2 (100); l_other_manager_warning VARCHAR2 (100); l_spp_delete_warning VARCHAR2 (100); l_entries_changed VARCHAR2 (100); l_tax_district_changed_warning VARCHAR2 (100); l_concatenated_segments VARCHAR2 (100); l_gsp_post_process_warning VARCHAR2 (100); BEGIN hr_assignment_api.update_emp_asg_criteria (p_effective_date => l_effective_date, p_datetrack_update_mode => l_datetrack_update_mod, p_assignment_id => l_assignment_id, p_validate => NULL, p_called_from_mass_update => NULL, p_grade_id => l_grade_id, p_position_id => l_position_id, p_job_id => l_job_id, p_payroll_id => l_payroll_id, p_location_id => l_location_id, p_organization_id => l_organization_id, p_pay_basis_id => l_pay_basis_id, p_segment1 => l_segment1, p_segment2 => l_segment2, p_segment3 => l_segment3, p_segment4 => NULL, p_segment5 => NULL, p_segment6 => NULL, p_segment7 => NULL, p_segment8 => NULL, p_segment9 => NULL, p_segment10 => NULL, p_segment11 => NULL, p_segment12 => NULL, p_segment13 => NULL, p_segment14 => NULL, p_segment15 => NULL, p_segment16 => NULL, p_segment17 => NULL, p_segment18 => NULL, p_segment19 => NULL, p_segment20 => NULL, p_segment21 => NULL, p_segment22 => NULL, p_segment23 => NULL, p_segment24 => NULL, p_segment25 => NULL, p_segment26 => NULL, p_segment27 => NULL, p_segment28 => NULL, p_segment29 => NULL, p_segment30 => NULL, p_employment_category => NULL, p_concat_segments => NULL, p_contract_id => NULL, p_establishment_id => NULL, p_scl_segment1 => NULL, p_grade_ladder_pgm_id => NULL, p_supervisor_assignment_id => NULL, p_object_version_number => l_old_obj_ver_number, p_special_ceiling_step_id => l_special_ceiling_step_id, p_people_group_id => l_people_group_id, p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, p_group_name => l_group_name, p_effective_start_date => l_effective_start_date, p_effective_end_date => l_effective_end_date, p_org_now_no_manager_warning => l_org_now_no_manager_warning, p_other_manager_warning => l_other_manager_warning, p_spp_delete_warning => l_spp_delete_warning, p_entries_changed_warning => l_entries_changed, p_tax_district_changed_warning => l_tax_district_changed_warning, p_concatenated_segments => l_concatenated_segments, p_gsp_post_process_warning => l_gsp_post_process_warning ); END;

Muthu From: DURANCE via oracle-apps-l [ mailto:[email protected] ] Sent: 16 November 2009 12:24 To: Periyanan, Muthu {PI} Subject: RE: [oracle-apps-l] hr_assignment_api.update_emp_asg_criteria error

Posted by DURANCE on Nov 16 at 7:12 AM Mark this reply as helpfulMark as helpful

Hi, Here my code /* Formatted on 2009/11/16 17:47 (Formatter Plus v4.8.8) */DECLARE l_effective_date DATE := SYSDATE; l_datetrack_update_mod VARCHAR2 (100) := TRUE; l_assignment_id NUMBER := 32160; l_grade_id NUMBER := NULL; l_position_id NUMBER := 59473; l_job_id NUMBER := 227; l_payroll_id NUMBER := NULL; l_location_id NUMBER := 204; l_organization_id NUMBER := 626; l_pay_basis_id NUMBER := NULL; l_segment1 VARCHAR2 (100); l_segment2 VARCHAR2 (100); l_segment3 VARCHAR2 (100); l_old_obj_ver_number NUMBER; l_special_ceiling_step_id NUMBER; l_people_group_id NUMBER; l_soft_coding_keyflex_id NUMBER; l_group_name VARCHAR2 (100); l_effective_start_date DATE; l_effective_end_date DATE; l_org_now_no_manager_warning VARCHAR2 (100); l_other_manager_warning VARCHAR2 (100); l_spp_delete_warning VARCHAR2 (100); l_entries_changed VARCHAR2 (100); l_tax_district_changed_warning VARCHAR2 (100); l_concatenated_segments VARCHAR2 (100); l_gsp_post_process_warning VARCHAR2 (100);BEGIN hr_assignment_api.update_emp_asg_criteria (p_effective_date => l_effective_date, p_datetrack_update_mode => l_datetrack_update_mod, p_assignment_id => l_assignment_id, p_called_from_mass_update => NULL, p_grade_id => l_grade_id, p_position_id => l_position_id, p_job_id => l_job_id, p_payroll_id => l_payroll_id, p_location_id => l_location_id, p_organization_id => l_organization_id, p_pay_basis_id => l_pay_basis_id, p_segment1 => l_segment1, p_segment2 => l_segment2, p_segment3 => l_segment3, p_employment_category => NULL, p_concat_segments => NULL, p_contract_id => NULL, p_establishment_id => NULL, p_scl_segment1 => NULL, p_grade_ladder_pgm_id => NULL, p_supervisor_assignment_id => NULL, p_object_version_number => l_old_obj_ver_number, p_special_ceiling_step_id => l_special_ceiling_step_id, p_people_group_id => l_people_group_id, p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, p_group_name => l_group_name, p_effective_start_date => l_effective_start_date, p_effective_end_date => l_effective_end_date, p_org_now_no_manager_warning => l_org_now_no_manager_warning, p_other_manager_warning => l_other_manager_warning, p_spp_delete_warning => l_spp_delete_warning, p_entries_changed_warning => l_entries_changed, p_tax_district_changed_warning => l_tax_district_changed_warning, p_concatenated_segments => l_concatenated_segments, p_gsp_post_process_warning => l_gsp_post_process_warning );END;

Send me your script, I’ll check and let you know

Muthu From: DURANCE via oracle-apps-l [ mailto:[email protected] ] Sent: 16 November 2009 12:02 To: Periyanan, Muthu {PI} Subject: RE: [oracle-apps-l] hr_assignment_api.update_emp_asg_criteria error

Posted by DURANCE on Nov 16 at 6:51 AM Mark this reply as helpfulMark as helpful

Hi, I gave all the parameters and assigned null for no values, but still i get the same error. Whats wrong, today i have to get this fixed. Please help!

Hi there, All the arguments in this API are mandatory, so you have to specify each argument with NULL as parameter if you don’t have any values. For Eg.

HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA ( p_effective_date =>trunc(sysdate) ,p_datetrack_update_mode =>‘CORRECTION’ ,p_assignment_id =>10574 ,p_validate => FALSE ,p_called_from_mass_update=>null ,p_grade_id=>null

Muthu From: DURANCE via oracle-apps-l [ mailto:[email protected] ] Sent: 14 November 2009 07:37 To: Periyanan, Muthu {PI} Subject: [oracle-apps-l] hr_assignment_api.update_emp_asg_criteria error

Posted by DURANCE on Nov 14 at 9:36 AM

Hi all, I am trying to update the emp assignment using the api. But i get the error, ORA-06550: line 18, column 4: PLS-00306: wrong number or types of arguments in call to ‘UPDATE_EMP_ASG_CRITERIA’ ORA-06550: line 18, column 4: PL/SQL: Statement ignored This is my code, what is wrong i couldnt find what is wrong in these, Please help.

DECLARE l_people_group_id number; l_object_version_number number; l_special_ceiling_step_id number; l_group_name varchar2(50); l_effective_start_date date; l_effective_end_date date; l_org_now_no_manager_warning boolean; l_other_manager_warning boolean; l_spp_delete_warning boolean; l_entries_changed_warning varchar2(50); l_tax_district_changed_warning boolean; l_called_from_mass_update boolean; l_grade_id number; l_payroll_id number; l_pay_basis_id number; BEGIN HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA ( p_effective_date =>trunc(sysdate) ,p_datetrack_update_mode =>‘CORRECTION’ ,p_assignment_id =>10574 ,p_validate => FALSE ,p_position_id =>32 ,p_job_id =>12 ,p_location_id =>207 ,p_organization_id =>207 ,p_people_group_id =>L_PEOPLE_GROUP_ID ,p_object_version_number =>l_object_version_number ,p_special_ceiling_step_id =>l_special_ceiling_step_id ,p_group_name =>l_group_name ,p_effective_start_date =>l_effective_start_date ,p_effective_end_date =>l_effective_end_date ,p_org_now_no_manager_warning =>l_org_now_no_manager_warning ,p_other_manager_warning =>l_other_manager_warning ,p_spp_delete_warning =>l_spp_delete_warning ,p_entries_changed_warning =>l_entries_changed_warning ,p_tax_district_changed_warning =>l_tax_district_changed_warning); end; Thanks in advance, The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.

Related Topics

Oracle Apps Solutions

Tuesday, september 2, 2014, assignment extra info create/update - hrms apis, no comments:, post a comment.