ame query [message #517873] |
Fri, 29 July 2011 01:23 |
lokeshsurana
Messages: 212 Registered: April 2010 Location: India
|
Senior Member |
|
|
I have getting error in between in AME approval:
code is as follow:
SELECT 'person_id:' || ppf.person_id
FROM per_all_people_f ppf, per_all_assignments_f paf
WHERE paf.person_id = ppf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.position_id IN
( SELECT ppe.parent_position_id
FROM PER_POS_STRUCTURE_ELEMENTS ppe
WHERE ppe.pos_structure_version_id IN
(SELECT pps.position_structure_id
FROM per_position_structures pps
WHERE pps.primary_position_flag = 'Y')
START WITH ppe.subordinate_position_id =
(SELECT position_id
FROM per_all_assignments_f paaf
WHERE TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND person_id =
(SELECT fu.employee_id
FROM fnd_user fu
WHERE fu.user_id =
(SELECT created_by
FROM pon_auction_headers_all
WHERE award_appr_ame_trans_id =
:transactionId)))
CONNECT BY PRIOR ppe.parent_position_id =
ppe.subordinate_position_id)
error:
ORA-01436: CONNECT BY loop in user data
Please can any one suggest solution?????
|
|
|
Re: ame query [message #517876 is a reply to message #517873] |
Fri, 29 July 2011 02:08 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Hi - you have to fix a problem in your data, you have a circular relationship. It's like this example, against the SCOTT schema:
orcl> select level,ename from emp start with ename='MILLER' connect by prior mgr=empno;
LEVEL ENAME
---------- ----------
1 MILLER
2 CLARK
3 KING
orcl> update emp set mgr=(select empno from emp where ename='MILLER')
2 where ename='KING';
1 row updated.
orcl> select level,ename from emp start with ename='MILLER' connect by prior mgr=empno;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
orcl>
You see what has happened? There are few places in EBS where you can do this, I hit the problem in HR when approving holidays because the users had managed to make one person another's boss and vice versa. You can raise a TAR, and Oracle will provide a script that will check all the dependencies between ppe.parent_position_id and ppe.subordinate_position_id, or you can look at the tables yourself and you'll probably find it more quickly.
|
|
|