Results for “pqh_tjr_shadow”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PQH_TJR_SHADOW is a transaction staging table owned by the HR schema within the PQH (Public Sector HR) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description, "Transaction Job Requirements Shadow Table," identifies its role: it stores a shadow copy of job requirement records associated with position transactions before those changes are validated, approved, and ultimately applied to the live job requirements model. This staging pattern is common in Oracle HRMS, where a pending transaction (such as a position change) can carry multiple requirement line items, and the application needs an isolated working area so that unapproved edits do not corrupt production data. Once the underlying transaction is processed, the shadow rows are either promoted into the operative tables or discarded.

ETRM documents 41 columns for this table, with the primary key PQH_TJR_SHADOW_PK defined on TXN_JOB_REQUIREMENT_ID. The heuristic Data Vault classification mined from the foreign key structure is standalone. As a modeling suggestion, this reflects the fact that the table's two outbound foreign keys — to PER_JOB_REQUIREMENTS and PER_ANALYSIS_CRITERIA — point outward to reference data rather than inward from dependent children. Consequently, PQH_TJR_SHADOW does not behave as a classic hub or link, and may be modeled as an independent staging satellite whose natural relationship to its parent transaction is carried structurally rather than through a rigid Data Vault pattern.

Key Information Stored

The most significant columns in PQH_TJR_SHADOW are:

• TXN_JOB_REQUIREMENT_ID — the surrogate primary key for the table and the column most frequently searched by name; uniquely identifies each shadow requirement line.
• POSITION_TRANSACTION_ID — the transaction header this shadow line belongs to, linking the requirement to a specific pending position action.
• JOB_REQUIREMENT_ID — the foreign key to PER_JOB_REQUIREMENTS, identifying the underlying job requirement being shadowed.
• ANALYSIS_CRITERIA_ID — the foreign key to PER_ANALYSIS_CRITERIA, identifying the criteria against which the requirement is assessed (for example, a skill, competency, or qualification).
• BUSINESS_GROUP_ID — the HR business group (legislative data group) that owns the record, essential for multi-organization filtering and security.
• DATE_FROM and DATE_TO — the effective dating window for the shadow requirement, supporting date-tracked analysis.
• ESSENTIAL — a flag/indicator denoting whether the requirement is mandatory for the job.
• JOB_ID — the job to which the requirement relates.
• OBJECT_VERSION_NUMBER — the optimistic locking counter used by the HRMS APIs and OA Framework to detect concurrent updates.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the standard concurrent program audit columns identifying which submission created or last processed the rows.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the WHO columns used for audit tracking and change reporting.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the standard DFF flexfield segment set for extensible, client-specific data.
• COMMENTS — free-form text accompanying the shadow requirement line.

The only documented unique index beyond the primary key is SYS_IL0000171384C00041$$, which is an internal LOB or index segment rather than a business-key candidate. No composite business key is documented.

Common Use Cases and Queries

The table is most commonly queried during diagnostics of position or job requirement transactions that fail to approve or apply. A typical pattern joins the shadow lines to their parent transaction and to the referenced requirement:

• Transaction diagnostics: SELECT t.txn_job_requirement_id, t.position_transaction_id, t.job_requirement_id, t.essential FROM pqh_tjr_shadow t WHERE t.position_transaction_id = :id; — returns all requirement lines staged for a given position transaction.
• Reconciliation against live requirements: joining JOB_REQUIREMENT_ID to PER_JOB_REQUIREMENTS to confirm which staged lines have not yet been promoted.
• Criteria reporting: joining ANALYSIS_CRITERIA_ID to PER_ANALYSIS_CRITERIA to describe the skills or qualifications attached to pending requirements.
• Concurrent program auditing: filtering on REQUEST_ID and PROGRAM_ID to isolate rows created by a specific process run, and using LAST_UPDATE_DATE to trace processing order.
• Data cleanup: identifying orphaned shadow rows whose POSITION_TRANSACTION_ID no longer corresponds to an open transaction.

Related Objects

• PER_JOB_REQUIREMENTS — the operative job requirements table; joined via PQH_TJR_SHADOW.JOB_REQUIREMENT_ID = PER_JOB_REQUIREMENTS.JOB_REQUIREMENT_ID. This is the primary foreign key target.
• PER_ANALYSIS_CRITERIA — the analysis criteria definition table; joined via PQH_TJR_SHADOW.ANALYSIS_CRITERIA_ID = PER_ANALYSIS_CRITERIA.ANALYSIS_CRITERIA_ID.
• PQH_POSITION_TRANSACTIONS (or the equivalent position transaction header object) — the parent transaction identified by POSITION_TRANSACTION_ID; the relationship is structural rather than a documented foreign key.
• PER_JOBS — the job definition referenced by JOB_ID, used to describe the job to which the requirement attaches.
• FND_CONCURRENT_REQUESTS — joined via REQUEST_ID to identify the concurrent program run that populated the shadow rows.
• HR_ORGANIZATION_UNITS / PER_BUSINESS_GROUPS — used with BUSINESS_GROUP_ID for legislative data group filtering and security.
• HRMS job requirement and position transaction PL/SQL APIs — the application programmatic interfaces that insert, validate, and promote these shadow rows as part of the transaction lifecycle.

In practice, PQH_TJR_SHADOW is a support and diagnostic object rather than a reporting subject area. Direct DML should be avoided; maintenance belongs to the delivered HRMS transaction APIs.