Hi

I am getting below error,
here hard coded values are same as i entered in front end.

********************************************************************************
Business Group ID :-> 81
Error Occured in API Block :-> ORA-01403: no data found
********************************************************************************

here is the my code

CREATE OR REPLACE PROCEDURE APPS.XXPER_EMP_API1(errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
)
IS

ln_person_id NUMBER;
ln_assignment_id NUMBER;
ln_per_object_version_number NUMBER;
ln_asg_object_version_number NUMBER;
ln_per_comment_id NUMBER;
ln_assignment_sequence NUMBER;
gn_business_group_id NUMBER:=apps.fnd_global.per_business_group_id;

ld_per_effective_start_date DATE;
ld_per_effective_end_date DATE;

lc_registered_disabled VARCHAR2(30);
lc_title VARCHAR2(30);
lc_assignment_number VARCHAR2(300);

lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;

lc_full_name apps.per_all_people_f.full_name%TYPE;
lc_employee_number apps.per_all_people_f.employee_number%TYPE;


BEGIN
fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));

fnd_file.put_line(fnd_file.log,'Business Group ID :-> '||gn_business_group_id);
--l_gender Get from hr_lookups where lookup_type = 'SEX' AND enabled_flag='Y'

BEGIN

APPS.Hr_General.G_DATA_MIGRATOR_MODE := 'Y';


APPS.Hr_Employee_Api.Create_Employee
(p_validate => FALSE
,p_hire_date => SYSDATE-100
,p_business_group_id => 81
,p_last_name => 'G1LTest'
,p_sex => 'M'
,p_person_type_id => 1120
,p_per_comments => NULL
,p_date_employee_data_verified => NULL
,p_date_of_birth => TO_DATE('20-OCT-1982','DD-MON-
,p_email_address => NULL
,p_employee_number => lc_employee_number
,p_expense_check_send_to_addres => NULL
,p_first_name => 'G1FTest'
,p_known_as => NULL
--,p_marital_status => 'S'
,p_middle_names => NULL
,p_nationality => NULL
,p_national_identifier => NULL
,p_previous_last_name => NULL
,p_registered_disabled_flag => NULL
,p_title => 'MR.'
,p_vendor_id => NULL
,p_work_telephone => NULL
,p_attribute_category => 81
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_attribute21 => NULL
,p_attribute22 => NULL
,p_attribute23 => NULL
,p_attribute24 => NULL
,p_attribute25 => NULL
,p_attribute26 => NULL
,p_attribute27 => NULL
,p_attribute28 => NULL
,p_attribute29 => NULL
,p_attribute30 => NULL
,p_per_information_category => 'AE'
-- p_per_information_category - Obsolete parameter, do not use
,p_per_information1 => NULL
,p_per_information2 => NULL
,p_per_information3 => NULL
,p_per_information4 => NULL
,p_per_information5 => NULL
,p_per_information6 => NULL
,p_per_information7 => NULL
,p_per_information8 => NULL
,p_per_information9 => NULL
,p_per_information10 => NULL
,p_per_information11 => NULL
,p_per_information12 => NULL
,p_per_information13 => NULL
,p_per_information14 => NULL
,p_per_information15 => NULL
,p_per_information16 => NULL
,p_per_information17 => NULL
,p_per_information18 => 205
,p_per_information19 => NULL
,p_per_information20 => NULL
,p_per_information21 => NULL
,p_per_information22 => NULL
,p_per_information23 => NULL
,p_per_information24 => NULL
,p_per_information25 => NULL
,p_per_information26 => NULL
,p_per_information27 => NULL
,p_per_information28 => NULL
,p_per_information29 => NULL
,p_per_information30 => NULL
,p_date_of_death => NULL
,p_background_check_status => 'N'
,p_background_date_check => NULL
,p_blood_type => NULL
,p_correspondence_language => NULL
,p_fast_path_employee => NULL
,p_fte_capacity => NULL
,p_honors => NULL
,p_internal_location => NULL
,p_last_medical_test_by => NULL
,p_last_medical_test_date => NULL
,p_mailstop => NULL
,p_office_number => NULL
,p_on_military_service => 'N'
,p_pre_name_adjunct => NULL
,p_rehire_recommendation => NULL
,p_projected_start_date => NULL
,p_resume_exists => 'N'
,p_resume_last_updated => NULL
,p_second_passport_exists => 'N'
,p_student_status => NULL
,p_work_schedule => NULL
,p_suffix => NULL
,p_benefit_group_id => NULL
,p_receipt_of_death_cert_date => NULL
,p_coord_ben_med_pln_no => NULL
,p_coord_ben_no_cvg_flag => 'N'
,p_coord_ben_med_ext_er => NULL
,p_coord_ben_med_pl_name => NULL
,p_coord_ben_med_insr_crr_name => NULL
,p_coord_ben_med_insr_crr_ident => NULL
,p_coord_ben_med_cvg_strt_dt => NULL
,p_coord_ben_med_cvg_end_dt => NULL
,p_uses_tobacco_flag => NULL
,p_dpdnt_adoption_date => NULL
,p_dpdnt_vlntry_svce_flag => 'N'
,p_original_date_of_hire => SYSDATE-100
,p_adjusted_svc_date => NULL
,p_town_of_birth => NULL
,p_region_of_birth => NULL
,p_country_of_birth => NULL
,p_global_person_id => NULL
,p_party_id => NULL
,p_person_id => ln_person_id
,p_assignment_id => ln_assignment_id
,p_per_object_version_number => ln_per_object_version_number
,p_asg_object_version_number => ln_asg_object_version_number
,p_per_effective_start_date => ld_per_effective_start_date
,p_per_effective_end_date => ld_per_effective_end_date
,p_full_name => lc_full_name
,p_per_comment_id => ln_per_comment_id
,p_assignment_sequence => ln_assignment_sequence
,p_assignment_number => lc_assignment_number
,p_name_combination_warning => lb_name_combination_warning
,p_assign_payroll_warning => lb_assign_payroll_warning
,p_orig_hire_warning => lb_orig_hire_warning
);

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error Occured in API Block :-> '||SQLERRM);

END;


fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));

EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'Error Message :-> '||SQLERRM);
fnd_file.put_line(fnd_file.log,RPAD('*',80,'*'));
END XXPER_EMP_API1;


Please let me know if any body faced this situation & how to solve it.

Thanks & Rgeards
Gireesh

Hello Gireesh

I would suggest taking things one step at a time here:

1. You don't need to explicitly reference any IN parameter where there is a default that you're happy with. Most 'create' apis will have IN parameters with a "DEFAULT NULL" clause on them, so you can take all of those out of your api call. That will simplify things greatly, even if it doesn't actually cure your problem.

2. I am unsure as to why you are setting 'data_migrator_mode' to 'Y'. That is not something one would normally do. I suggest commenting that line out if you don't wish to remove it completely.

3. If you're going to use SYSDATE for anything, be aware that there is a time component there, so best to use TRUNC(SYSDATE)

4. Is that a valid person_type_id you are refering to there, ie valid for the business group?

That's all I can think of for a start.

Hi Clive,

Many Many thanks for ur reply.
The code is working fine with the use of TRUNC(SYSDATE) as
u said.
Once again thanks for ur kind help.

Thanks & Regards
Gireesh

No worries, happy to have helped.

I've got a similar problem with the same API. The error is
ORA-01403: no data found

Sure I'm missing something obvious but can't see what. I've done a PKG that will be called VIA Web ADI, but just using an exec in toad at the moment.

I've attached the PKG body and exec I'm using.

Any help would be much appreciated

As for the previous poster I would suggest taking it back to the bare essentials to start with (i.e. only the minimum parameters needed for the api to be able to do something). Still using your wrapper, but comment out all the parameter references for in parameters with a DEFAULT associated (keep p_validate as TRUE for now). Assuming you are successful, then reintroduce them gradually until you hit the point at which the error occurs.

If you get to that point, and you still can't work out what's wrong with it, then post back on here the parameter that's causing the problem.

I've stripped it right back as attached
and passing it
p_validate => TRUE
,p_hire_date => TRUNC(SYSDATE)
,p_business_group_id => 8052
,p_last_name => 'XXtest1'
,p_gender => 'M'

But still get the no data found error???

School boy error, recently had laptop rebuilt and since re-installing toad haven't checked nls_language.

Altered session and now everying is ok in the world again, laptop almost became a frisbee but got there in end

Ah, that old chestnut... Good spot though! And schoolboy errors are the best kind!

For the benefit of anyone wondering what exactly the issue is, it's to do with the language setting on the client where you are running the query/process.

If you do

SELECT userenv('LANG') FROM dual;

then you should get your base language (typically 'US')

A lot of time it shows as 'GB' which although sort of logical for a UK laptop, means that querying any 'translation' tables/views (ending in _TL or _VL) that have 'US' in the language column means no rows will be found. In this case, it was in all probability the view hr_lookups that was the issue. Shame we couldn't have a more meaningful message in there though, eh? e.g. an explicit check of the language setting as part of the validation strategy...

Anyway, how to set it back to 'US'? Just do this:

ALTER SESSION SET nls_language = 'American'

Simples!

As another foot note to this issue. If you want to change the NLS_LANG in toad for all sessions, you can edit the language in the oracle home editor. For more details on oracle home editor use toad help.