Supplier User Gets Error About Access Beyond A Single Supplier Hierarchy / Buyer User Gets SQLStmtException: JBO-27122 and SQLException: ORA-01436: CONNECT BY loop in user data
(Doc ID 1056081.1)
Last updated on AUGUST 20, 2023
Applies to:
Oracle iSupplier Portal - Version 11.5.10 to 12.1.3 [Release 11.5 to 12.1]Information in this document applies to any platform.
Symptoms
Supplier User gets error that their setup allows access beyond a single supplier hierarchy, and supplier user is not able to access iSupplier Portal because of this error. The buyer administrator also gets error with exception ORA-01436 when trying to modify the supplier user setup for this user using the Supplier User Profile Manager responsibility.
ERRORS
Supplier User error
The set-up for the current user allows access beyond a single supplier hierarchy or its sites or contacts. Please contact the system administrator to correct the definition of current user set-up.
Buyer User error
pvs.vendor_site_id,
pvs.vendor_site_code,
pvs.vendor_id,
hou.name, decode( sec_attr.number_value, null, 'N', 'Y') as User_Sec_Attr, decode( sec_attr.number_value, null, 'N', 'Y') as Original_Sec_Attr , pv.vendor_name as supplier_name
from po_vendor_sites_all pvs,hr_all_organization_units_tl hou, ak_web_user_sec_attr_values sec_attr
,po_vendors pv
where pvs.org_id=hou.organization_id
and hou.language (+)= USERENV('LANG')
and
(pvs.inactive_date is null OR pvs.inactive_date >= sysdate)
--and pvs.vendor_id = :1
and pvs.vendor_id in
( select vendor_id from po_vendors pv
start with pv.vendor_id = :1
connect by prior pv.vendor_id = pv.parent_vendor_id
)
and sec_attr.web_user_id(+) = :2
and sec_attr.ATTRIBUTE_CODE (+) = 'ICX_SUPPLIER_SITE_ID'
and sec_attr.ATTRIBUTE_APPLICATION_ID (+) = 177
and sec_attr.number_value(+) = pvs.vendor_site_id
and pvs.vendor_id = pv.vendor_id
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:603)
...
## Detail 0 ##
java.sql.SQLException: ORA-01436: CONNECT BY loop in user data
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
...
STEPS TO REPRODUCE
1. Login as Supplier user
2. Choose iSupplier Portal responsibility
- get error
Error
The set-up for the current user allows access beyond a single supplier hierarchy or its sites or contacts. Please contact the system administrator to correct the definition of current user set-up.
3. Login as Buyer and Navigate to Supplier Management Administrator / Supplier Home / Supplier User Management / Approve and Manage Supplier User Profile
4. Query for the Supplier user
- get a generic unexpected error
- exception details for the error at Supplier User Profile Management
...
and pvs.vendor_id in
( select vendor_id from po_vendors pv
start with pv.vendor_id = :1
connect by prior pv.vendor_id = pv.parent_vendor_id
)
...
## Detail 0 ##
java.sql.SQLException: ORA-01436: CONNECT BY loop in user data
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |