Search Results psb_data_extract_orgs_u1

Overview

PSB.PSB_DATA_EXTRACT_ORGS is a seed data table within the Oracle E-Business Suite PSB schema, associated with the Human Resources (HR) and data extraction functionality delivered under the FND Design Data reference PSB.PSB_DATA_EXTRACT_ORGS. The table stores the set of organizations that have been selected for a given data extraction run, along with selection and completion flags that drive the incremental extract process. It resides in the APPS_TS_SEED tablespace with a PCT Free of 10, which is consistent with its role as a configuration and control table rather than a high‑volume transactional store.

From a Data Vault modeling perspective, the metadata classifies this table heuristically as standalone, since it is not built from an explicit foreign key hierarchy beyond its reference to PSB_DATA_EXTRACTS. A standalone classification suggests it could be treated as its own hub or, more precisely, as a link-like association recording the participation of an organization in an extraction. In practice, PSB_DATA_EXTRACT_ORGS behaves as a dependent entity anchored to the parent extraction record, recording per-organization selection state.

Key Information Stored

The table contains eleven columns. Its business identity is enforced by the unique index PSB_DATA_EXTRACT_ORGS_U1 on the composite key (DATA_EXTRACT_ID, ORGANIZATION_ID). DATA_EXTRACT_ID is a NUMBER(20) that links each row to the parent extract definition, and ORGANIZATION_ID is a NUMBER(15) holding the organization identifier selected for extraction. There is no separate single surrogate primary key column; the composite unique index functions as the business key.

• DATA_EXTRACT_ID — the extract identifier this organization record belongs to.
• ORGANIZATION_ID — the organization selected for the extract.
• ORGANIZATION_NAME — the organization name, sourced from HR.
• SELECT_FLAG — indicates whether an organization is selected or excluded.
• COMPLETION_STATUS — shows whether the extraction has completed for the organization.
• COMPLETION_TIME — timestamp of the last creation or refresh of the organization’s data.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard Who columns for audit and concurrency.

Common Use Cases and Queries

Typical scenarios include verifying which organizations are flagged for an extract, monitoring incomplete organization extracts, and reconciling the organization list against HR. A common pattern filters by SELECT_FLAG and COMPLETION_STATUS to find pending work:

• Pending organizations for an extract: select DATA_EXTRACT_ID, ORGANIZATION_ID, ORGANIZATION_NAME from PSB.PSB_DATA_EXTRACT_ORGS where SELECT_FLAG = 'Y' and COMPLETION_STATUS is null or COMPLETION_STATUS <> 'COMPLETE';
• Last refresh per organization: order by COMPLETION_TIME descending to identify stale data.
• Full extract listing: the documented query selects all eleven columns ordered by extraction to produce an operator report.

Reporting on this table is frequently joined to PSB_DATA_EXTRACTS to present the extract run alongside its selected organizations.

Related Objects

The principal relationship is the foreign key from DATA_EXTRACT_ID to PSB.PSB_DATA_EXTRACTS, the parent table defining each data extract. The APPS synonym PSB_DATA_EXTRACT_ORGS exposes the object to the application layer. Because the table holds organization references from HR, joins to HR_OPERATING_UNITS or HR_ALL_ORGANIZATION_UNITS are common for resolving organization details, though those relationships are not documented as formal constraints. No other PSB objects are documented as referencing this table, reinforcing its classification as a standalone, extraction‑scoped record.