Results for “soft_coding_keyflex_id”

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

Overview

PAY_PAYROLLS is a date-effective view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) product. It exposes the definition of payrolls as they exist across effective dates, rather than presenting only the current definition. In Oracle HRMS and Payroll, date-effective (datetracked) entities retain a historical record of changes; each row carries an EFFECTIVE_START_DATE and EFFECTIVE_END_DATE that bound the interval during which a given version of the payroll record was valid. The view therefore returns multiple rows per payroll when its definition has been altered over time, allowing consumers to reconstruct the state of a payroll as of any point in time.

Because it is a view rather than a table, PAY_PAYROLLS contains no data of its own. It is defined over the base table PAY_PAYROLLS_F — the "_F" suffix denoting a datetracked "full" table in the Oracle HRMS date-effective model — joined to PER_TIME_PERIOD_TYPES_VL so that the period type can be presented in its translated, user-visible form. The view is documented as VALID and is intended primarily for reporting, querying, and integration purposes where a denormalised, human-readable payroll definition is required.

Underlying Base Objects

The view text is a straightforward equijoin between two objects:

• PAY_PAYROLLS_F — the datetracked base table holding all payroll definitions, including all historical versions. Aliased as PP in the view definition.
• PER_TIME_PERIOD_TYPES_VL — the translated (language-specific) view over time period types, aliased as PTPTV, supplying DISPLAY_PERIOD_TYPE.

The ETRM 12.2.2 metadata additionally lists the following referenced objects, which reflect the security and session context applied when the view is queried through the standard HRMS framework: FND_SESSIONS (synonym), HR_GENERAL (package), and HR_SECURITY (package). HR_SECURITY is the mechanism that enforces row-level access, such that a user only sees payrolls associated with business groups they are permitted to access. HR_GENERAL provides the PL/SQL routines used for date-effective filtering and session attribute retrieval. Unlike the "_F" base tables, the view does not include a corresponding "_TL" translation join for the payroll name itself; PAYROLL_NAME is stored directly on PAY_PAYROLLS_F.

Key Columns

• PAYROLL_ID — primary identifier of the payroll; together with the effective dates it uniquely identifies a datetracked row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack interval defining when this version of the payroll is valid.
• BUSINESS_GROUP_ID — the business group (organisation) that owns the payroll; central to HR_SECURITY filtering.
• PAYROLL_NAME — the user-defined name of the payroll.
• PERIOD_TYPE / DISPLAY_PERIOD_TYPE — the payroll period frequency (for example, monthly or weekly); the latter is the translated display value from PER_TIME_PERIOD_TYPES_VL.
• FIRST_PERIOD_END_DATE — the end date of the first payroll period.
• PAY_DATE_OFFSET, CUT_OFF_DATE_OFFSET, DIRECT_DEPOSIT_DATE_OFFSET, PAY_ADVICE_DATE_OFFSET, MIDPOINT_OFFSET — the offset values used to derive key payroll dates relative to the period end.
• DEFAULT_PAYMENT_METHOD_ID — the default payment method for the payroll.
• GL_SET_OF_BOOKS_ID, COST_ALLOCATION_KEYFLEX_ID, SUSPENSE_ACCOUNT_KEYFLEX_ID, SOFT_CODING_KEYFLEX_ID, CONSOLIDATION_SET_ID — accounting and costing configuration references.
• NEGATIVE_PAY_ALLOWED_FLAG, ARREARS_FLAG, MULTI_ASSIGNMENTS_FLAG, WORKLOAD_SHIFTING_LEVEL, NUMBER_OF_YEARS — payroll behaviour and processing attributes.
• ATTRIBUTE1–ATTRIBUTE20 and PRL_INFORMATION1–PRL_INFORMATION30 — descriptive flexfield and Payroll Information (PRL) flexfield segments.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical usage includes validating payroll setup, joining payroll definitions to assignments or payroll actions for reporting, and extracting configuration for data migration or integration. Because the view is date-effective, queries commonly constrain the effective dates to return only the current definition, or to reconstruct a point-in-time definition.

• Listing all current payrolls in a business group:

SELECT payroll_id, payroll_name, display_period_type,
       effective_start_date, effective_end_date
FROM   apps.pay_payrolls
WHERE  business_group_id = :p_business_group_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

• Reconstructing a payroll definition as of a past date:

SELECT payroll_id, payroll_name, period_type, first_period_end_date
FROM   apps.pay_payrolls
WHERE  payroll_id = :p_payroll_id
AND    :p_as_of_date BETWEEN effective_start_date AND effective_end_date;

• Auditing changes to a payroll's definition over time:

SELECT payroll_id, effective_start_date, effective_end_date,
       payroll_name, period_type, last_update_date, last_updated_by
FROM   apps.pay_payrolls
WHERE  payroll_id = :p_payroll_id
ORDER  BY effective_start_date;

In each case the effective date predicates are essential; omitting them returns every historical version of each payroll. Note also that rows remain subject to HR_SECURITY, so results depend on the querying user's business group access.