Database Software

Hi!

I'm facing the following situation:

There is a "normal" employee called Mr. E.
His supervisor is Ms. S and the Supervisor of Ms. S is Mr. X.

I've got the following select to check if the enrollment requestor is the manager of the delegate:

select xxsony_ota_ame_pkg.is_employee_subordinate(hrv2.number_value,hrv.number_value)
from hr_api_transaction_values hrv
,hr_api_transaction_values hrv2
,hr_api_transaction_steps hrs
, per_all_assignments_f paaf
where hrs.transaction_step_id = hrv.transaction_step_id
and hrs.transaction_id = :transactionId
and hrv.name = 'P_DELEGATE_PERSON_ID'
and hrs.transaction_step_id = hrv2.transaction_step_id
and hrv2.name = 'P_PERSON_ID'
and paaf.person_id = hrv.number_value
and trunc(sysdate) between paaf.effective_start_Date and paaf.effective_end_date


This select works fine if Ms S enroles Mr. E.
But if Mr. X enrolles Ms S, then i get the following error:
oracle.apps.fnd.framework.OAException: java.sql.SQLException: ORA-01403: no data found

The error is not caused by my custom function:

SQL> r
1 select substr(xxsony_ota_ame_pkg.is_employee_subordinate(hrv2.number_value,hrv.number_value), 1, 10) subordinate,
2 hrv2.number_value person,hrv.number_value delegeate_person
3 from hr_api_transaction_values hrv ,hr_api_transaction_values hrv2 ,
4 hr_api_transaction_steps hrs , per_all_assignments_f paaf
5 where hrs.transaction_step_id = hrv.transaction_step_id
6 and hrs.transaction_id = 3075
7 and hrv.name = 'P_DELEGATE_PERSON_ID'
8 and hrs.transaction_step_id = hrv2.transaction_step_id
9 and hrv2.name = 'P_PERSON_ID'
10 and paaf.person_id = hrv.number_value
11* and trunc(sysdate) between paaf.effective_start_Date and paaf.effective_end_date

SUBORDINAT PERSON DELEGEATE_PERSON
---------- ---------- ----------------
true 64 79
true 64 79
true 64 79

After the no_data_found exception occures, the line in hr_api_transaction_values is there.

Also the Select
Select 'true'
from hr_api_transactions
where transaction_id = 3085
works fine in AME.

Could it be, that there is no line in hr_api_transaction_values at that time, where AME queries it?

Do you have any idea, how i can fix this problem?

Thank you for your help!

Best regards,
Thomas