Search Results original_date_of_hire

Overview

PAY_AU_TERM_EMP_ASG_V is an Oracle E-Business Suite view owned by the APPS schema and classified under the PAY (Payroll) product family. It is an Australian localisation view whose primary purpose is to present termination-related assignment, person, and period-of-service information for employees governed by the Australian (AU) legislation. The view consolidates data that would otherwise require joining assignment, person, and period-of-service entities, and it resolves the termination type from an Australian lookup so that downstream reporting and integration processes can consume a single, pre-joined result set.

Because the view contains a date-effective join driven by the current FND_SESSIONS effective date, it returns the row versions that are valid for the user's active session context. This design makes it suitable for concurrent programs, BI Publisher reports, Oracle Reports, and custom interfaces that need a consistent, point-in-time snapshot of Australian termination data without re-implementing the effective-dating logic. The view is documented as VALID and is treated as an Australian localisation artifact, so its output is restricted to business groups whose legislation code is 'AU'.

Underlying Base Objects

The view is defined over several documented base objects. The principal sources are PER_ASSIGNMENTS_F, PER_PEOPLE_V, and PER_PERIODS_OF_SERVICE_V, which supply assignment, person, and period-of-service attributes respectively. PER_BUSINESS_GROUPS provides the business group context and is filtered so that only business groups with LEGISLATION_CODE = 'AU' qualify. FND_SESSIONS is used to obtain the session identifier and the effective date, which is then constrained to fall between the effective start and end dates of the assignment and person records.

The termination type is derived from HR_LOOKUPS using the lookup type 'AU_TERMINATION_TYPE', matched against the PDS_INFORMATION1 attribute of the period of service. The join to HR_LOOKUPS is an outer join and is further restricted to enabled lookup values. The ETRM metadata additionally lists the HR_API, HR_GENERAL, HR_PERSON_NAME, HR_PERSON_TYPE_USAGE_INFO, and HR_SECURITY packages as referenced objects; these are typically invoked indirectly through the person and period-of-service views (for example, for name formatting and security predicates) rather than being selected directly by the view text. Collectively, these objects establish person-level security and formatting conventions that the view inherits.

Key Columns

The view exposes a focused set of columns that span assignment, person, and service data. ASSIGNMENT_ID and ASSIGNMENT_NUMBER identify the assignment, while PERSON_ID and EMPLOYEE_NUMBER identify the person. PERIOD_OF_SERVICE_ID links the record to the underlying period of service, and BUSINESS_GROUP_ID establishes the operating context.

• FULL_NAME and DATE_OF_BIRTH provide personal identifiers.
• ORIGINAL_DATE_OF_HIRE — the column most relevant to the user's search — carries the person's original hire date, which is distinct from the assignment start date and is frequently required for continuous service and long-service-leave calculations.
• PAYROLL_ID identifies the payroll to which the assignment is attached.
• START_DATE and ACTUAL_TERMINATION_DATE bracket the period of service and define the effective employment window.
• TERMINATION_TYPE is the decoded meaning from HR_LOOKUPS for the AU_TERMINATION_TYPE lookup, describing the reason for termination.

Note that the documented column list includes START_DATE, which corresponds to DATE_START in the underlying view definition, and TERMINATION_TYPE, which corresponds to the lookup MEANING. These aliases reflect the localisation's presentation conventions.

Common Use Cases and Queries

Typical uses include Australian termination reporting, continuity-of-service analysis, and payroll reconciliation. The view is well suited to identifying leavers and their termination reasons, and to pairing ORIGINAL_DATE_OF_HIRE with ACTUAL_TERMINATION_DATE for service-length calculations. Because it is filtered to Australian business groups, it can be queried directly without additional legislation filters.

A representative query retrieving termination details for leavers is shown below:

• SELECT assignment_number, employee_number, full_name, original_date_of_hire, start_date, actual_termination_date, termination_type FROM apps.pay_au_term_emp_asg_v WHERE actual_termination_date IS NOT NULL ORDER BY actual_termination_date DESC;
• SELECT employee_number, full_name, original_date_of_hire, TRUNC(actual_termination_date) - TRUNC(original_date_of_hire) AS service_days FROM apps.pay_au_term_emp_asg_v WHERE termination_type = 'Voluntary' ;

When embedding the view in custom code, developers should ensure a valid FND_SESSIONS context exists, because the effective-date join depends on USERENV('SESSIONID'). Where ORIGINAL_DATE_OF_HIRE is needed for non-terminated employees, PER_PEOPLE_V or PER_ALL_PEOPLE_F should be queried directly, since this view's effective-date and period-of-service joins are oriented toward termination reporting.