Search Results pos_supplier_users_v




Overview
Explore in AI Chat

Overview

The POS_SUPPLIER_USERS_V view in the APPS schema is a reporting and integration object within the POS – iSupplier Portal product family of Oracle E-Business Suite. It is defined with a status of VALID in both the 12.1.1 and 12.2.2 releases and is documented in ETRM as a view that "queries the list of supplier users." Functionally, the view answers a single, well-scoped question: which Oracle EBS application users are registered as contacts of a given supplier (vendor) organization, and through which trading relationship that association exists.

This is a critical question in iSupplier Portal implementations because supplier-side access is not granted directly on the vendor record. Instead, a named person is created in the Trading Community Architecture (HZ) as a person party, linked to the supplier organization party through a relationship, and then provisioned with an FND_USER account whose web-user security attributes bind that account to a specific supplier organization. POS_SUPPLIER_USERS_V flattens that multi-table chain into a single denormalized row per supplier user, making it suitable for concurrent programs, Oracle Reports, OA Framework-based administration pages, and custom SQL against the APPS schema. Because the view is owned by APPS and references only standard, shipped objects, it can be queried by any application user granted SELECT on the view, without needing direct privileges on the underlying HZ and FND tables.

Underlying Base Objects

The documented base objects referenced by the view are:

  • FND_USER (synonym) — supplies the application user account, aliased as FU.
  • HZ_PARTIES (synonym) — appears twice conceptually: once as the user's person party (USER_PARTY) and once as the supplier organization party via PO_VENDORS.PARTY_ID.
  • HZ_RELATIONSHIPS (synonym) — aliased HZR, supplies the contact-of relationship record between the person and the organization.
  • AK_WEB_USER_SEC_ATTR_VALUES (synonym) — aliased SEC, holds the web-user security attribute that ties the FND user to a supplier organization ID.
  • PO_VENDORS (view) — supplies the vendor/supplier record and its associated party ID.

The joins enforce a strict semantic filter. HZ_RELATIONSHIPS is restricted to RELATIONSHIP_TYPE = 'CONTACT', RELATIONSHIP_CODE = 'CONTACT_OF', SUBJECT_TYPE = 'PERSON', SUBJECT_TABLE_NAME = 'HZ_PARTIES', OBJECT_TYPE = 'ORGANIZATION', OBJECT_TABLE_NAME = 'HZ_PARTIES', and STATUS = 'A'. The web-user security join additionally requires ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID' and ATTRIBUTE_APPLICATION_ID = 177, with SEC.NUMBER_VALUE matching PO_VENDORS.VENDOR_ID. Together these predicates guarantee that only active, portal-enabled supplier contacts are returned.

Key Columns

  • USER_ID — the FND_USER primary key of the supplier's application user.
  • USER_NAME — the login name of that user.
  • USER_END_DATE — the effective end date of the user account (aliased from FU.END_DATE).
  • PERSON_PARTY_ID — the HZ_PARTIES party ID for the person, sourced from FU.PERSON_PARTY_ID.
  • VENDOR_PARTY_ID — the party ID of the supplier organization, exposed as PV.PARTY_ID.
  • VENDOR_ID — the PO_VENDORS vendor identifier of the supplier organization.
  • RELATIONSHIP_ID — the HZ_RELATIONSHIPS primary key of the contact-of relationship.
  • REL_PARTY_ID — aliased from HZR.PARTY_ID; this is the party ID recorded on the relationship record itself. User searches for "rel_party_id" typically target this column, since it is the relationship-side party reference rather than the person's own party ID.
  • REL_END_DATE — aliased from HZR.END_DATE, the end date of the contact relationship.

Common Use Cases and Queries

Typical scenarios include auditing which portal users belong to a supplier, validating that a new supplier contact was provisioned correctly, and listing active relationships for a vendor party during data migration or integration with an external identity store.

List all supplier users for a given vendor number:

  • SELECT user_id, user_name, person_party_id, rel_party_id, rel_end_date FROM apps.pos_supplier_users_v WHERE vendor_id = :p_vendor_id;

Locate the supplier associated with a specific login (a common support query):

  • SELECT vendor_id, vendor_party_id, relationship_id, rel_party_id FROM apps.pos_supplier_users_v WHERE user_name = :p_user_name;

Filter to users whose account and relationship are still open:

  • SELECT user_id, user_name, rel_end_date FROM apps.pos_supplier_users_v WHERE (user_end_date IS NULL OR user_end_date > SYSDATE) AND (rel_end_date IS NULL OR rel_end_date > SYSDATE);

Because the view is a simple SELECT (no DISTINCT or aggregate), it performs well for point queries on USER_NAME or VENDOR_ID, particularly as those columns map to indexed keys in FND_USER and PO_VENDORS. All queries must be issued with the APPS schema context, typically through a synonym or the fully qualified APPS.POS_SUPPLIER_USERS_V name.