Results for “paylv_us_state”
17 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
PAYLV_US_STATE is a public Oracle E-Business Suite database view owned by the APPS schema and classified under the PAY (Payroll) product family. Although it is delivered within the Oracle HRMS/Payroll footprint, the view is not payroll-processing logic in the strict sense; it is a lightweight reference (lookup) view that exposes the standard United States state code, abbreviation, and name combinations in a single, consistently ordered result set. It presents two identifiers for each U.S. state — a numeric or coded STATE_CODE and a short STATE_ABBREV — together with the full descriptive STATE_NAME, plus a derived ORDER_BY column used purely for sorting.
In Oracle EBS 12.1.1 and 12.2.2, the object carries a VALID status and is intended for read-only consumption: reporting, list-of-values (LOV), and integration scenarios where a canonical, authoritative list of U.S. states is required. Because it is an APPS-owned view, it is accessible to any responsibility or concurrent program that can reference APPS objects, making it a convenient single source of truth for state reference data across multiple modules rather than a private payroll-only artifact.
Underlying Base Objects
The documented base objects referenced by PAYLV_US_STATE are two synonyms: PAY_US_STATES and DUAL. The view is defined as a UNION ALL of two branches. The first branch selects STATE_CODE, STATE_ABBREV, STATE_NAME, and STATE_NAME from PAY_US_STATES, aliasing the final column as ORDER_BY. The second branch selects NULL, NULL, NULL, and a single-space literal (' ') from DUAL. The effect is that the underlying table provides the actual state rows, while the DUAL branch injects a single blank placeholder row. This blank row is a common EBS design convention that lets a UI or LOV present an empty or "not applicable" selection without requiring the calling form or report to construct one.
p>The use of UNION ALL rather than UNION preserves all rows without the deduplication overhead, which is appropriate because the placeholder row is deliberately distinct from any real state record. Note that the exposed column is named ORDER_BY, not STATE_NAME as it is aliased in the raw SELECT; the ORDER_BY alias becomes the column label that consumers see.Key Columns
- STATE_CODE — The coded identifier for the state as stored in the payroll reference data. For the placeholder DUAL row this value is NULL.
- STATE_ABBREV — The two-character postal-style abbreviation (for example, the standard USPS abbreviation) used for compact display and matching. NULL for the placeholder row.
- STATE_NAME — The full human-readable state name, suitable for prompts, reports, and LOV displays. NULL for the placeholder row.
- ORDER_BY — A sort key. For real rows it duplicates STATE_NAME, so the dataset orders naturally by state name; for the placeholder row it is a single space, which sorts before alphabetic text and therefore places the blank entry first.
Common Use Cases and Queries
The view is typically joined to or referenced from other payroll/HR tables that store a state code, and is frequently used to drive LOVs in forms or parameters in concurrent programs. A basic listing that respects the intended ordering is:
SELECT state_code, state_abbrev, state_name FROM apps.paylv_us_state ORDER BY order_by;
A query that excludes the injected blank row, for example when populating a validation table or an interface file, might read:
SELECT state_code, state_name FROM apps.paylv_us_state WHERE state_code IS NOT NULL ORDER BY state_name;
A lookup by abbreviation or by name is equally straightforward:
SELECT state_code FROM apps.paylv_us_state WHERE state_abbrev = 'CA';
When the source table's state names differ from a consuming application's expectations, the view provides a stable, documented join point. Because the metadata records only this view and its base synonyms, consumers should treat PAY_US_STATES as the maintained reference and this view as the supported interface for retrieving it. As with all APPS-owned EBS objects, the view should be referenced rather than modified, and any custom logic should be layered on top through custom views or queries that filter the supplied rows as needed.
-
View: PAYLV_US_STATE 12.1.1
APPS.PAYLV_US_STATE·↳ PAY_US_STATES·↳ DUAL·Explore PAY module →
-
View: PAYLV_US_STATE 12.2.2
APPS.PAYLV_US_STATE·↳ PAY_US_STATES·↳ DUAL·Explore PAY module →
-
VIEW: APPS.PAYLV_US_STATE 12.1.1
-
VIEW: APPS.PAYLV_US_STATE 12.2.2
-
SYNONYM: APPS.PAY_US_STATES 12.1.1
-
SYNONYM: APPS.PAY_US_STATES 12.2.2
-
12.2.2 FND Design Data 12.2.2
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
SYNONYM: PUBLIC.DUAL 12.2.2
-
SYNONYM: PUBLIC.DUAL 12.1.1
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.