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 JUNE 14, 2017

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.
Verified currency on 04-FEB-2016


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

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

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select
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

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select
...
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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms