Search Results psb_data_extract_orgs

Overview

PSB.PSB_DATA_EXTRACT_ORGS is a table within the Public Sector Budgeting (PSB) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the set of organizations that have been selected for a given data extraction run. Where PSB_DATA_EXTRACTS defines the extraction job header, PSB_DATA_EXTRACT_ORGS acts as the line-level detail, recording each organization included in that run together with its selection state and processing status. This makes it the primary reference point for determining which organizations participated in a budget data extraction and how far each one progressed.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. It references only the extraction header and is not itself referenced by downstream tables in the documented model, so it is best treated as a detail or link-style table rather than a hub or satellite. The classification is a modeling suggestion derived from the FK topology, not an ETRM-defined property.

Key Information Stored

The table is documented with 11 columns. The most significant are:

• DATA_EXTRACT_ID — the foreign key to PSB_DATA_EXTRACTS, identifying the parent extraction run.
• ORGANIZATION_ID — the identifier of the organization included in the extraction.
• ORGANIZATION_NAME — the descriptive name of the organization, denormalized for reporting convenience.
• SELECT_FLAG — indicates whether the organization was selected for extraction.
• COMPLETION_STATUS — the processing state of the extraction for this organization.
• COMPLETION_TIME — the timestamp at which processing for the organization completed.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns recording the most recent modification.
• CREATED_BY, CREATION_DATE — the standard audit columns recording initial row creation.

No single-column surrogate primary key is documented. The business-key candidate is defined by the unique index PSB_DATA_EXTRACT_ORGS_U1 on the composite of DATA_EXTRACT_ID and ORGANIZATION_ID, which guarantees that a given organization appears at most once per extraction run. Any query that resolves a single row for reporting or updates should key on that pair rather than on a synthetic identifier.

Common Use Cases and Queries

Typical uses center on auditing extraction coverage and monitoring per-organization progress. A common pattern lists all organizations in an extraction with their completion state:

• SELECT organization_id, organization_name, select_flag, completion_status FROM psb_data_extract_orgs WHERE data_extract_id = :extract_id;
• Identify organizations still pending in a run: filter WHERE completion_status <> 'COMPLETE'.
• Measure extraction duration by comparing COMPLETION_TIME across DATA_EXTRACT_ID values.
• Trace which user last touched a row via LAST_UPDATED_BY joined to FND_USER.
• Detect duplicate or re-selected organizations using the PSB_DATA_EXTRACT_ORGS_U1 key.

Because organizations are keyed by ORGANIZATION_ID, joining to HR_ALL_ORGANIZATION_UNITS validates the extracted set against the organization hierarchy. Reporting extracts should always carry DATA_EXTRACT_ID as a filter to bound result sets.

Related Objects

Relationship metadata identifies one foreign key path, though the table logically participates in a wider join network:

• PSB_DATA_EXTRACTS — the parent table; joined on PSB_DATA_EXTRACT_ORGS.DATA_EXTRACT_ID = PSB_DATA_EXTRACTS.DATA_EXTRACT_ID.
• HR_ALL_ORGANIZATION_UNITS — organization master, joined on ORGANIZATION_ID.
• FND_USER — resolves LAST_UPDATED_BY and CREATED_BY to user names.
• PSB_DATA_EXTRACT_* sibling detail tables — parallel extraction components sharing DATA_EXTRACT_ID.

The table is populated and maintained by the PSB extraction processes rather than through direct DML; updates to SELECT_FLAG, COMPLETION_STATUS, and COMPLETION_TIME occur as the extraction engine processes each organization.