Search Results per_ca_ee_extract_pkg




Overview
Explore in AI Chat

Overview

APPS.PER_CA_EE_EXTRACT_PKG is a PL/SQL package body in the Oracle E-Business Suite Applications schema that supports Canadian employee legislative reporting requirements. Its name and object references indicate that it produces the extract and report lines required for Canadian employment equity (EE) and related payroll/personnel statutory reporting — most notably the federal Employment Equity forms (Form 1, Form 2, Form 2N, Form 3, Form 4, Form 5, Form 6) that employers must file with Human Resources and Skills Development Canada. The package consolidates assignment, organization, job, and person-type data from the Oracle Human Resources (PER) tables into staging and report structures held in PER_CA_EE_REPORT_LINES.

Residing in the APPS schema and classified as a non-API (OTHER) object, it is an internal implementation object rather than a supported public API. It is registered as VALID in the data dictionary and is not referenced by any other database object, though it itself references many HR and payroll entities. This indicates it operates at the top of a call hierarchy — invoked by concurrent programs, report definitions, or Forms-based procedures — and does not serve as a dependency for downstream packages.

Key Procedures and Functions

Twelve documented program units are exposed by the package body:

  • FORM1, FORM2, FORM2N, FORM3, FORM4, FORM5, FORM6 — Each of these procedures corresponds to one of the Canadian Employment Equity report forms. They assemble the data set (employee demographics, occupational group, salary, and workforce composition) required for the respective filing and write the resulting rows into the extract/report tables. They share common helper logic for employee selection and job/person-type validation.
  • EMPLOYEE_PROMOTIONS — Derives promotion activity for employees over a reporting period, feeding workforce-movement sections of the equity reports.
  • JOB_EXISTS — A validation function confirming that a given job definition is present and usable in the extract, guarding against invalid or obsolete job records.
  • PERSON_TYPE_EXISTS — Validation counterpart for person types, ensuring only eligible employees (based on configured person types) are included in reporting.
  • CHECK_GRE_WITHOUT_NAIC — Specialized validation relating to Canadian occupational classification (GRE — Group/Region/Equivalent, and NAIC — North American Industry Classification). It verifies records that lack a NAIC code, supporting the classification logic required by the equity forms.
  • UPDATE_REC — General-purpose record update procedure used to persist or refresh extract rows in the report lines structure.

Tables Accessed

The package reads and writes a defined set of HR and payroll objects via APPS synonyms:

Usage Notes

PER_CA_EE_EXTRACT_PKG is invoked indirectly, typically from a concurrent program or a report/form driver that submits the Canadian Employment Equity extract, not by end-user form screens. Because it is not referenced by any other database object and is classified as OTHER, it should be treated as an internal implementation package: customizations should avoid calling it directly and instead use supported extract submission paths. The presence of UPDATE_REC and the staging table PER_CA_EE_REPORT_LINES indicates a generate-then-persist pattern, so reruns should be scheduled within the extract's own purge/reload cycle to avoid duplicate lines. As with all seeded EBS packages, modifications require careful regression testing across both 12.1.1 and 12.2.2, since table and synonym structures may differ between releases.