Oracle Apps Knowledge Sharing

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;   

            In this post, we have given a query which helps to link iSupplier user and respective vendors.

Instance: R12.2.4

Query:

SELECT fu.user_name isupplier_user_name,
       fu.start_date  user_start_date,
       fu.end_date    user_end_date,   
       NVL(fu.email_address,
	       user_party.email_address
		  ) email_address,
       pv.vendor_name,
       user_party.person_last_name contact_last_name,
       user_party.person_first_name contact_first_name,
       hzr.end_date user_vend_relation_enddate,
       fu.person_party_id,
       pv.party_id  vendor_party_id,
       pv.vendor_id,
       hzr.relationship_id,
       hzr.party_id rel_party_id,
       fu.user_id
  FROM po_vendors pv,
       hz_parties user_party,
       fnd_user   fu,
       hz_relationships hzr,
       ak_web_user_sec_attr_values  sec
 WHERE fu.person_party_id = user_party.party_id
   AND hzr.relationship_type = 'CONTACT'
   AND hzr.relationship_code = 'CONTACT_OF'
   AND hzr.subject_id = fu.person_party_id
   AND hzr.subject_type = 'PERSON'
   AND hzr.subject_table_name = 'HZ_PARTIES'
   AND hzr.object_type = 'ORGANIZATION'
   AND hzr.object_table_name = 'HZ_PARTIES'
   AND hzr.object_id = pv.party_id
   AND hzr.status = 'A'
   AND sec.web_user_id = fu.user_id
   AND sec.attribute_code = 'ICX_SUPPLIER_ORG_ID'
   AND sec.number_value = pv.vendor_id
   AND sec.attribute_application_id = 177;