Oracle Apps Knowledge Sharing

iSupplier Portal: Query to fetch iSupplier user's "Modify Sites" access details in Oracle Applications R12

Background: An iSupplier user will be able to view, acknowledge purchase order only when the respective user was given “Modify site access” to respective vendor site of Purchase order. This access can be granted or revoked from supplier master maintenance screens. 

In this post, we have given a query which helps to list vendor site details for which isupplier user have “Modify Site Access”. This helps in debugging issues related to access for purchase order.

Instance: R12.2.4

Query:

SELECT psu.user_name,
       aps.vendor_name,
       pvs.vendor_site_code,  
       hou.short_code ou_short_code,  
       psu.user_end_date,
       aps.end_date_active vendor_end_date,
       pvs.inactive_date vendor_site_inactive_date,      
       hou.organization_id org_id,     
       pvs.vendor_site_id vendor_site_id,
       psu.user_id,       
       psu.vendor_id,      
       psu.person_party_id,
       aps.party_id
  FROM ap_supplier_sites_all       pvs,
       hr_operating_units          hou,
       ak_web_user_sec_attr_values sec_attr,
       pos_supplier_users_v psu, 
       ap_suppliers aps
 WHERE pvs.org_id = hou.organization_id
   AND sec_attr.web_user_id = psu.user_id
   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         = psu.vendor_id 
   AND psu.vendor_id         = aps.vendor_id;