Search Results bic_relationship_types_u1

Overview

BIC.BIC_RELATIONSHIP_TYPES is a reference (lookup) table within the Oracle EBS Business Intelligence / Customer (BIC) schema. It stores the set of valid party relationship types used to classify how one party relates to another — for example, whether a customer is a parent organization, a subsidiary, a contact, or another defined role. The table functions as a controlled vocabulary: rather than allowing free-form relationship descriptors, applications validate against this list and then use the type code to drive downstream processing, display, and reporting.

The object is documented as VALID, owned by BIC, with FND Design Data registration BIC.BIC_RELATIONSHIP_TYPES, and is stored in the APPS_TS_ARCHIVE tablespace. The metadata labels the object with an Oracle Internal Use Only warning, meaning supported access is expected through standard Oracle Applications programs rather than direct SQL.

The heuristic Data Vault classification mined from the constraint structure is standalone. As a modeling suggestion, this table is best treated as an independent reference/dimension hub rather than a link or satellite: it holds a single unique business key and no significant external dependencies.

Key Information Stored

The table exposes eight documented columns. The most significant are:

• PARTY_RELATIONSHIP_TYPE (VARCHAR2(30)) — the core descriptor and the column backing the unique index BIC_RELATIONSHIP_TYPES_U1. This is the natural business key that uniquely identifies each relationship type. Note that the metadata does not document a separate surrogate numeric primary key column; the unique index on PARTY_RELATIONSHIP_TYPE is the business-key candidate.
• DISPLAY_FLAG (VARCHAR2) — controls whether the relationship type is exposed in application user interfaces and pick lists. This enables a type to be retained for historical data while being retired from active selection.
• SECURITY_GROUP_ID (NUMBER(15)) — the multi-organization/security partition identifier, with a foreign key to FND_SECURITY_GROUPS. It scopes which security group may see the row.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. These provide audit lineage: who created or last modified each relationship type, when, and from which operating system login.

Common Use Cases and Queries

Typical scenarios include validating a relationship code during data conversion, populating LOVs for customer hierarchy maintenance, and reporting on active versus inactive relationship types.

List active relationship types:

SELECT PARTY_RELATIONSHIP_TYPE, DISPLAY_FLAG
FROM   BIC.BIC_RELATIONSHIP_TYPES
WHERE  DISPLAY_FLAG = 'Y';

Confirm the business key is unique and detect duplicates or casing issues before loading:

SELECT PARTY_RELATIONSHIP_TYPE, COUNT(*)
FROM   BIC.BIC_RELATIONSHIP_TYPES
GROUP  BY PARTY_RELATIONSHIP_TYPE
HAVING COUNT(*) > 1;

Validate a source value against the reference list:

SELECT 1
FROM   BIC.BIC_RELATIONSHIP_TYPES
WHERE  PARTY_RELATIONSHIP_TYPE = :p_type;

Audit recent maintenance activity using the WHO columns:

SELECT PARTY_RELATIONSHIP_TYPE, LAST_UPDATED_BY, LAST_UPDATE_DATE
FROM   BIC.BIC_RELATIONSHIP_TYPES
ORDER  BY LAST_UPDATE_DATE DESC;

Related Objects

The metadata documents limited direct relationships. The principal documented dependencies are:

• FND_SECURITY_GROUPS — referenced through BIC_RELATIONSHIP_TYPES.SECURITY_GROUP_ID, providing row-level security scoping.
• BIC_RELATIONSHIP_TYPES# — the internal editioning/underlying object listed as referencing this table.
• FND_USER — implicit relationship via CREATED_BY and LAST_UPDATED_BY (foreign keys to FND_USER.USER_ID).
• FND_LOGINS — implicit relationship via LAST_UPDATE_LOGIN (foreign key to FND_LOGINS.LOGIN_ID).

The table itself references no other database objects, confirming its standalone reference nature. Consumers of the relationship type values are application-level rather than schema-enforced, so joins to party or customer hierarchy tables rely on the shared PARTY_RELATIONSHIP_TYPE code value.