Search Results pay_in_arch_le_emp_v




Overview
Explore in AI Chat

Overview

PAY_IN_ARCH_LE_EMP_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product family, available in both 12.1.1 and 12.2.2. It exposes employee-level archival data related to the India End-of-Year (EOY) statutory process, specifically the linkage between an employee, their assigned GRE (Government Reporting Entity), and the corresponding assessment year. The view is a reporting and integration artifact rather than a transactional object; it consolidates information that would otherwise require joining the PAY_ACTION_INFORMATION table to assignment actions, payroll actions, and HR organization units.

The name encodes its purpose: "IN" denotes India localization, "ARCH" denotes archival/EOY, "LE" refers to the legal employer or legislative entity context, and "EMP" indicates employee-level granularity. The presence of ACTION_INFORMATION_CATEGORY = 'IN_EOY_PERSON' confirms that the view is scoped to India EOY person-level action data. The user's search term "gre_id" maps directly to one of the view's four exposed columns, which is the primary identifier for the Government Reporting Entity.

Underlying Base Objects

The view is defined over four joined objects: PAY_ACTION_INFORMATION (accessed via synonym), PAY_PAYROLL_ACTIONS (synonym), PAY_ASSIGNMENT_ACTIONS (synonym), and HR_ORGANIZATION_UNITS (view). Join conditions tie the action information record (ACTION_CONTEXT_TYPE = 'AAP') to an assignment action through ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID, then to a payroll action via PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID, and finally to the GRE organization via HOU.ORGANIZATION_ID = PAI.ACTION_INFORMATION3.

The view also references FND_PROFILE (HR_GENERAL and HR_SECURITY packages are documented dependencies). The Business Group filter PPA.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') restricts results to the current session's business group. HR_SECURITY is applied implicitly to enforce organization-level access, meaning users only see GREs and employees their security profile permits.

Key Columns

  • EMPLOYEE_NUMBER — Sourced from PAI.ACTION_INFORMATION1; the employee's assignment number/person identifier as stored in the EOY action record.
  • GRE_ID — Sourced from PAI.ACTION_INFORMATION3; the organization_id of the Government Reporting Entity associated with the employee's EOY record. This is the search term of interest and serves as the join key to HR_ORGANIZATION_UNITS.
  • GRE_NAME — Sourced from HOU.NAME; the descriptive name of the GRE organization unit.
  • ASSESSMENT_YEAR — Sourced from PAI.ACTION_INFORMATION2; the statutory assessment year to which the EOY record applies.

The SELECT DISTINCT clause prevents duplicate rows where multiple action information records satisfy the same key combination.

Common Use Cases and Queries

The view is typically used to report which employees belong to which GRE for a given assessment year, to validate EOY archival completeness, and to feed downstream statutory reporting or reconciliation extracts.

Listing all employee-to-GRE mappings for the active business group:

SELECT employee_number, gre_id, gre_name, assessment_year
FROM   apps.pay_in_arch_le_emp_v
ORDER BY assessment_year, gre_name, employee_number;

Filtering by a specific GRE to list its employees:

SELECT employee_number, assessment_year
FROM   apps.pay_in_arch_le_emp_v
WHERE  gre_id = :p_gre_id;

Aggregating headcount by GRE and year:

SELECT gre_name, assessment_year, COUNT(DISTINCT employee_number) emp_count
FROM   apps.pay_in_arch_le_emp_v
GROUP BY gre_name, assessment_year;

Because the view enforces HR security and business group context through profile values, queries must be run in an environment where the correct PER_BUSINESS_GROUP_ID is set. When results appear empty, the business group profile or the user's organization security access is the most common cause. For performance, restricting on assessment_year or gre_id is advisable, as the underlying PAY_ACTION_INFORMATION table can be large in EOY archival scenarios.