Results for “pqh_position_transactions_v”

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

Overview

PQH_POSITION_TRANSACTIONS_V is a Public Sector HR (PQH) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes position transaction records maintained by the Position Control component of Oracle HRMS and enriches them with decoded, human-readable descriptions. Position transactions capture the life-cycle events applied to a position — creation, reclassification, budget changes, FTE adjustments, and eventual deactivation — each stamped with an action date and an effective date range. The view therefore functions as the principal read-only surface for position transactional history, allowing reporting tools, concurrent programs, and integration interfaces to retrieve position data without joining the many HR lookup and decode routines individually.

The base object is documented as VALID in ETRM for release 12.2.2, confirming that the definition remains supported across both the 12.1.1 and 12.2.2 code lines. Because the view supplies descriptive columns such as POSITION_TYPE_DESC, GRADE_DESC, and JOB_DESC alongside their corresponding identifier columns, it is well suited to both operational reports and inbound/outbound interfaces that require both the code and its display value.

Underlying Base Objects

The ETRM metadata documents three referenced base objects for this view:

• PQH_POSITION_TRANSACTIONS (SYNONYM) — the primary source. The view selects from PTX, the alias for this position transaction entity, supplying all ID, date, flag, and numeric columns.
• HR_GENERAL (PACKAGE) — provides the decode functions used to translate lookup codes and foreign-key identifiers into descriptions: DECODE_LOOKUP, DECODE_LOCATION, DECODE_ORGANIZATION, DECODE_JOB, DECODE_AVAILABILITY_STATUS, DECODE_PAYROLL, DECODE_PAY_BASIS, DECODE_GRADE, DECODE_STEP, and DECODE_GRADE_RULE.
• PQH_UTILITY (PACKAGE) — supplies grade-rule resolution functions, notably DECODE_GRADE_RULE, which returns the VALUE, MIN, and MID attributes associated with the entry grade rule on the transaction.

The synonym resolves to the underlying position transaction table, and the two packages are invoked at query time; no additional joins to lookup tables are performed directly in the view text.

Key Columns

• POSITION_TRANSACTION_ID — primary identifier for the transaction row.
• ACTION_DATE, DATE_EFFECTIVE (aliased START_DATE), DATE_END (aliased END_DATE) — the action timestamp and the effective date range of the position change.
• NAME, POSITION_TYPE, POSITION_TYPE_DESC — the position name and its classification.
• LOCATION_ID / LOCATION_DESC, ORGANIZATION_ID / ORGANIZATION_DESC, JOB_ID / JOB_DESC — decoded organizational placement of the position.
• FTE, FTE_CAPACITY, FTE_CAPACITY_MEANING, MAX_PERSONS — headcount and capacity attributes; FTE_CAPACITY_MEANING is decoded from the PER_FTE_CAPACITY lookup.
• EARLIEST_HIRE_DATE, FILL_BY_DATE, PERMIT_RECRUITMENT_FLAG, REPLACEMENT_REQUIRED_FLAG — recruitment controls.
• PROPOSED_FTE_FOR_LAYOFF, PROPOSED_DATE_FOR_LAYOFF — reduction-in-force planning fields.
• BARGAINING_UNIT_CD / BARGAINING_UNIT_DESC, PAY_FREQ_PAYROLL_ID / PAYROLL_DESC, PAY_BASIS_ID / PAY_BASIS_DESC — bargaining unit and payroll attributes.
• ENTRY_GRADE_ID / GRADE_DESC, ENTRY_STEP_ID / STEP_DESC, ENTRY_GRADE_RULE_ID — entry compensation attributes, with POINT_VALUE, GRADE_RATE_VALUE, GRADE_RATE_MIN, and GRADE_RATE_MID resolved through the grade-rule decodes.
• REVIEW_FLAG, PERMANENT_TEMPORARY_FLAG, SEASONAL_FLAG — status and eligibility indicators.

The documented extract truncates within the grade-rule decode block; additional columns beyond that point are not enumerated in the ETRM metadata. Note that no column named WORK_DURATION appears in the documented view text, despite the originating search term; duration must be derived from DATE_EFFECTIVE and DATE_END.

Common Use Cases and Queries

Typical uses include position history reporting, budget and FTE reconciliation, and extraction of position changes for downstream interfaces. To list effective-dated position changes for an organization:

SELECT position_transaction_id, name, position_type_desc,
       organization_desc, fte, start_date, end_date
FROM   apps.pqh_position_transactions_v
WHERE  organization_id = :org_id
AND    start_date >= :from_date
ORDER BY start_date;

To calculate an effective duration in the absence of a WORK_DURATION column:

SELECT position_transaction_id, name,
       (end_date - start_date) AS duration_days
FROM   apps.pqh_position_transactions_v
WHERE  end_date IS NOT NULL;

To report recruitment-eligible positions pending approval:

SELECT name, position_type_desc, earliest_hire_date, fill_by_date
FROM   apps.pqh_position_transactions_v
WHERE  permit_recruitment_flag = 'Y'
AND    replacement_required_flag = 'Y';

Because the decode functions are invoked internally, queries remain concise and consistent with HRMS lookup configuration, making the view a dependable foundation for custom reports and interface extracts across both 12.1.1 and 12.2.2.