Results for “duty_station_code”

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

Overview

GHR_DUTY_STATIONS_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the GHR – US Federal Human Resources product family. Its documented purpose is to make a self reference to the GHR_DUTY_STATIONS_F table in order to produce an expanded duty station name. In federal HR terminology, a duty station identifies the geographic location at which an employee is officially assigned to work; the raw code alone (for example, a numeric or alphanumeric identifier) is not human-readable, so this view decodes and concatenates the city, county, and state/country names into a single descriptive string. The view is marked VALID in the ETRM 12.2.2 metadata. It is a reporting and integration convenience object rather than a transactional entity: consumers read from it to obtain display-ready duty station descriptions without having to reproduce the three-way join themselves. It carries no DML capability of its own and is intended purely for query access in forms, concurrent programs, BI Publisher reports, and interfaces that need a resolved duty station label.

Underlying Base Objects

The view is defined exclusively over GHR_DUTY_STATIONS_F, referenced through an APPS synonym. The view text aliases this single base table three times — as STATE, COUNTY, and CITY — creating the self reference described in the documentation. Two correlated joins are performed:

• The STATE alias joins on STATE.DUTY_STATION_CODE = CITY.STATE_OR_COUNTRY_CODE, resolving the state or country record that contains the city record.
• The COUNTY alias joins on COUNTY.DUTY_STATION_CODE = CITY.COUNTY_CODE, resolving the county record that contains the city record.

Each join is date-effective: the CITY row's EFFECTIVE_END_DATE must fall between the referenced row's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, ensuring that only the correctly dated parent rows are matched. A final filter, UPPER(NVL(CITY.IS_DUTY_STATION, 'Y')) = 'Y', restricts output to rows flagged as actual duty stations, treating a null flag as a default of 'Y'. Because the source is a date-effective (_F) table, the view exposes the effective dating columns directly to the caller.

Key Columns

• DUTY_STATION_CODE — The duty station identifier taken from the CITY alias. This is the primary business key used in HR and payroll records to reference a location.
• DUTY_STATION_ID — The internal surrogate identifier for the city-level duty station row, useful for foreign-key style joins to other federal HR tables.
• DUTY_STATION_DESC — The derived, expanded description. It is built as CITY.NAME || DECODE(SUBSTR(CITY.DUTY_STATION_CODE, 7, 3), '000', NULL, ' / ' || COUNTY.NAME) || ' / ' || STATE.NAME. In effect, the city name is followed by the county name (omitted when the seventh through ninth characters of the code are '000') and then the state or country name, separated by ' / '.
• EFFECTIVE_START_DATE — The date from which the city-level duty station row is active.
• EFFECTIVE_END_DATE — The date until which the city-level duty station row is active; also used in the join predicates that select the matching state and county rows.

Common Use Cases and Queries

Typical scenarios include resolving a duty station code to a printable description on employee assignment reports, validating that a code references an active duty station, and driving location-based reporting for federal agencies. A simple lookup by code is shown below.

SELECT duty_station_code,
       duty_station_id,
       duty_station_desc,
       effective_start_date,
       effective_end_date
  FROM apps.ghr_duty_stations_v
 WHERE duty_station_code = :p_duty_station_code;

To restrict results to stations that are currently active, filter on the effective dates exposed by the view:

SELECT duty_station_code, duty_station_desc
  FROM apps.ghr_duty_stations_v
 WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date
 ORDER BY duty_station_desc;

For ad hoc searching, callers commonly filter on the derived description, since the code itself is rarely meaningful to end users:

SELECT duty_station_code, duty_station_desc
  FROM apps.ghr_duty_stations_v
 WHERE UPPER(duty_station_desc) LIKE UPPER('%' || :p_search_text || '%');

Because the view resolves effective-dated hierarchy information at query time, applications should always constrain by date where the current active description is required, and should treat DUTY_STATION_ID rather than DUTY_STATION_CODE as the stable join key when integrating with other federal HR entities.