Results for “assign_start_date”

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

Overview

PAY_ASGS_WITHOUT_PAYROLLS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, registered under the Payroll (PAY) product family. Its documented purpose in ETRM is simply "Report view," and its name accurately describes its function: it returns assignment records that are attached to a valid Payroll payroll system status but have no payroll defined (ASG.PAYROLL_ID IS NULL). In effect, the view isolates the population of employee assignments that exist within the HRMS person/assignment model yet are not associated with any payroll, which is a common data-quality gap following conversions, re-orgs, or new-hire loads.

Because it is a view rather than a table, it carries no storage of its own and reflects the current state of PER_PEOPLE_F, PER_ASSIGNMENTS_F and related date-tracked entities at query time. It presents a flattened, human-readable projection — full name, assignment number, organization, location, status — that is convenient for ad-hoc reporting, reconciliation extracts, and integration feeds without requiring the caller to reconstruct the underlying joins.

Underlying Base Objects

The documented base objects are HR_GENERAL (package), HR_PERSON_NAME (package), HR_SECURITY (package), HR_LOCATIONS (view), HR_ORGANIZATION_UNITS (view), PER_ASSIGNMENTS_F (view), PER_PEOPLE_F (view), PER_ASSIGNMENT_STATUS_TYPES (synonym) and PER_PERSON_TYPES (synonym). The view text joins PER_PEOPLE_F to PER_ASSIGNMENTS_F on PERSON_ID, to PER_PERSON_TYPES on PERSON_TYPE_ID, to PER_ASSIGNMENT_STATUS_TYPES on ASSIGNMENT_STATUS_TYPE_ID, to HR_ORGANIZATION_UNITS on ORGANIZATION_ID, and to HR_LOCATIONS through an outer join on LOCATION_ID.

Because PER_PEOPLE_F and PER_ASSIGNMENTS_F are date-tracked (the _F suffix denotes the effective-dated versions of PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F), the view applies explicit date overlap logic: the assignment’s EFFECTIVE_START_DATE must be less than or equal to the person’s EFFECTIVE_END_DATE, and the assignment’s EFFECTIVE_END_DATE must be greater than or equal to the person’s EFFECTIVE_START_DATE. HR_SECURITY and HR_PERSON_NAME are referenced by the underlying objects for row-level security and name formatting respectively.

Key Columns

• ASSIGN_START_DATE — exposed as ASG.EFFECTIVE_START_DATE; the effective start of the assignment. This is the column most commonly retrieved by users searching on "assign_start_date."
• ASSIGN_END_DATE — ASG.EFFECTIVE_END_DATE, the effective end of the assignment.
• PERSON_START_DATE / PERSON_END_DATE — the effective dating of the person record.
• FULL_NAME / ORDER_NAME — formatted person name and the ordering name from HR_PERSON_NAME.
• ASSIGNMENT_NUMBER — the assignment identifier shown to end users.
• ORGANIZATION_NAME / ADDRESS — organization unit name and location code.
• USER_STATUS — the user-defined assignment status text.
• PERSON_ID, ASSIGNMENT_ID, BUSINESS_GROUP_ID, ASSIGNMENT_STATUS_TYPE_ID, ORGANIZATION_ID, LOCATION_ID, PERSON_TYPE_ID — surrogate keys supporting joins back to base entities.

Common Use Cases and Queries

The primary use case is identifying assignments lacking a payroll assignment, typically ahead of payroll processing or during data clean-up. A representative query filtering by assignment start date:

• SELECT full_name, assignment_number, assign_start_date, organization_name, user_status FROM apps.pay_asgs_without_payrolls_v WHERE assign_start_date BETWEEN :start_date AND :end_date ORDER BY assign_start_date;
• SELECT assignment_id, person_id, assign_start_date FROM apps.pay_asgs_without_payrolls_v WHERE business_group_id = :bg_id;
• SELECT organization_name, COUNT(*) FROM apps.pay_asgs_without_payrolls_v GROUP BY organization_name;

Because HR_SECURITY is referenced, results are constrained by the security profile of the querying user, so the view is safe for delegated HR reporting. It is read-only and should be treated as a reporting artifact rather than a transactional interface.