Results for “address_line4”

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

Overview

The PAY_IE_P45_EMPLOYEE_DETAILS view is an Oracle Payroll (PAY) database object owned by the APPS schema and delivered as part of the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases. Its sole documented purpose is to supply the data required by the Irish (IE) P45 form — the statutory end-of-employment tax certificate issued by an employer to a departing employee in the Republic of Ireland. Because Ireland is legislated as a separate payroll territory within Oracle Payroll, the P45 reporting logic, formatting, and datelines are packaged into dedicated Irish legislative views such as this one, rather than being shared with UK or other regional P45 processing.

At runtime the view does not hold persisted data; it is a relational projection assembled from payroll action information records generated during the assignment action and payroll action lifecycle. The view is therefore best understood as a reporting and integration artefact: it feeds the Irish P45 report/runset and can be queried directly by downstream reports, extracts, or interfaces that need employee name, PPS number, works number, date of birth, date of leaving, date of commencement, and deceased-indicator information in a single flattened structure.

Underlying Base Objects

The view is defined over the following documented base objects:

• PAY_ASSIGNMENT_ACTIONS (synonym) — aliased PAA. Provides the assignment action and payroll action linkage, tax unit, and the joining key across the action information tables.
• PAY_ACTION_INFORMATION (synonym) — accessed three times under the aliases PAI_ED (category EMPLOYEE DETAILS), PAI_IEED (category IE EMPLOYEE DETAILS), and PAI_IEP45 (category IE P45 INFORMATION). Each access supplies a different logical block of the P45 payload.
• PAY_PAYROLL_ACTIONS (synonym) — aliased PACT, supplying the payroll action effective date used in the date-of-commencement derivation.
• PAY_LEGISLATION_RULES (synonym) — aliased LEG, restricted with LEGISLATION_CODE = 'IE' and RULE_TYPE = 'L' to enforce Irish legislative context and provide the rule mode used in the date comparison.
• FND_DATE (package) — Oracle Application Object Library date conversion package, invoked as FND_DATE.CANONICAL_TO_DATE and FND_DATE.DISPLAYDATE_TO_DATE to normalise the date-of-commencement string held in action information depending on its stored format (canonical versus display).

All three PAY_ACTION_INFORMATION accesses join to PAY_ASSIGNMENT_ACTIONS on ACTION_CONTEXT_ID = ASSIGNMENT_ACTION_ID with ACTION_CONTEXT_TYPE = 'AAP', ensuring that employee details, Irish employee details, and Irish P45 information are aligned to the same assignment action instance. PAY_ASSIGNMENT_ACTIONS is further joined to PAY_PAYROLL_ACTIONS on PAYROLL_ACTION_ID to obtain the payroll action effective date.

Key Columns

• ASSIGNMENT_ACTION_ID — Identifier of the assignment action row driving the P45 record.
• ASSIGNMENT_ID — Identifier of the assignment (employment relationship).
• LAST_NAME — Sourced from PAI_IEED.ACTION_INFORMATION29.
• FIRST_NAME — Sourced from PAI_IEED.ACTION_INFORMATION28.
• PPS_NO — The employee's Irish Personal Public Service number, from PAI_ED.ACTION_INFORMATION4.
• DATE_OF_BIRTH — Converted from PAI_IEED.ACTION_INFORMATION25 using TO_DATE(..., 'DD-MON-RRRR').
• WORKS_NO — Employer works number, from PAI_ED.ACTION_INFORMATION14.
• DATE_OF_LEAVING — The employee's leaving date, converted from PAI_IEP45.ACTION_INFORMATION3 with TO_DATE(..., 'DD-MON-RRRR'). This is the column most commonly targeted in ad hoc reporting, since it defines the P45 period and the terminal date of the employment.
• DATE_OF_COMMENCEMENT — Derived from PAI_IEED.ACTION_INFORMATION30, converted according to stored length (19 characters indicates canonical format, otherwise display format) and then compared against the legislative rule mode combined with the payroll action effective year; if the calculated date exceeds the payroll effective date, NULL is returned.
• DECEASED — Indicator from PAI_IEP45.ACTION_INFORMATION1.
• PERSON_ID — Party/person identifier from PAI_IEP45.ACTION_INFORMATION8.
• TAX_UNIT_ID — Tax unit from PAY_ASSIGNMENT_ACTIONS.

Common Use Cases and Queries

The primary use case is producing the Irish P45 output for a leaver, and the most frequent ad hoc requirement is locating records by leaving date. A representative query follows:

SELECT assignment_action_id
     , assignment_id
     , last_name
     , first_name
     , pps_no
     , works_no
     , date_of_birth
     , date_of_leaving
     , date_of_commencement
     , deceased
     , person_id
     , tax_unit_id
FROM   apps.pay_ie_p45_employee_details
WHERE  date_of_leaving BETWEEN TO_DATE('01-JAN-2024','DD-MON-RRRR')
                           AND TO_DATE('31-DEC-2024','DD-MON-RRRR');

Other practical scenarios include reconciling leaver populations for Irish payroll periods, extracting P45 data into statutory filing or third-party payroll bureaux formats, and validating that date_of_leaving and date_of_commencement values have been populated correctly in the underlying action information categories before running the P45 report.

Because the view converts character-based action information into DATE values at runtime, it is advisable to filter on the converted columns rather than the raw action information attributes, and to account for possible NULLs arising from unpopulated P45 information categories or DATE_OF_COMMENCEMENT derivation logic. Queries should be executed against the APPS schema or a reporting schema with appropriate synonyms and grants.