Business has a common requirement to get supplier accounts using iSupplier Portal. Below is the SQL to get iSupplier Portal Active Users. This is needed from procurement and Oracle licensing prospective.
SELECT hou.name Operating_Unit,pv.segment1 Supplier_Number, pv.vendor_name Supplier_Name, pvs.vendor_site_code Supplier_Site,
fu.user_name Supplier_UserName, fu.EMAIL_ADDRESS Supplier_Email,fu.end_date User_Effective_End_Date
FROM apps.AK_WEB_USER_SEC_ATTR_VALUES attr, apps.fnd_user fu, apps.ap_suppliers pv, apps.ap_supplier_sites_all pvs, apps.hr_operating_units hou
WHERE attr.attribute_code = ‘ICX_SUPPLIER_SITE_ID’
AND attr.number_value IS NOT NULL
AND attr.web_user_id = fu.user_id
AND pvs.vendor_site_id = attr.number_value
AND pv.vendor_id = pvs.vendor_id
AND pvs.org_id = hou.organization_id
AND NVL(fu.end_date, SYSDATE+1) > SYSDATE
— AND hou.ORGANIZATION_ID = <Operating Unit Id>
ORDER BY 3,4,5,7