Search Results post_code

Overview

PAY_IE_P45_ADDRESS_DETAILS is an Oracle E-Business Suite view owned by the APPS schema and delivered within the Payroll (PAY) product module. Its documented purpose is to supply address information to the Irish P45 form, the statutory end-of-employment tax certificate issued under Irish Revenue requirements. The view is therefore a reporting-only construct: it does not store data, but projects a denormalised, form-ready address record from the underlying payroll action and action information tables.

The view is functionally bound to the P45 archive process. Its driving query filters PAY_PAYROLL_ACTIONS for REPORT_TYPE = 'P45', REPORT_QUALIFIER = 'IE', REPORT_CATEGORY = 'ARCHIVE' and ACTION_STATUS = 'C'. Only completed Irish P45 archive actions are visible, which means the view returns data exclusively for employees whose P45 has already been processed and stored. The object is reported as VALID in EBS 12.1.1 and 12.2.2, and its definition is unchanged between those releases.

Underlying Base Objects

The view is defined over three base objects plus one PL/SQL package, joined with a USE_NL hint that forces nested-loop execution between PAY_PAYROLL_ACTIONS and PAY_ASSIGNMENT_ACTIONS:

• PAY_PAYROLL_ACTIONS (PPA) — supplies the P45 archive action header; drives the report type, qualifier, category and completion status filters.
• PAY_ASSIGNMENT_ACTIONS (PAA) — links each payroll action to its constituent assignment actions via PAYROLL_ACTION_ID.
• PAY_ACTION_INFORMATION (PAI_ADD) — holds the actual address attribute values, restricted to ACTION_CONTEXT_TYPE = 'AAP' and ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS'. The join key is PAA.ASSIGNMENT_ACTION_ID = PAI_ADD.ACTION_CONTEXT_ID.
• PAY_IE_P45_ARCHIVE (package) — supplies the GET_LOOKUP_MEANING function, called twice to translate stored lookup codes into displayable county and postal code values.

Address segments are held positionally in ACTION_INFORMATION5 through ACTION_INFORMATION7, with county in ACTION_INFORMATION9 and postal code in ACTION_INFORMATION12. Each is truncated to 30 characters via SUBSTR.

Key Columns

• ASSIGNMENT_ACTION_ID — surrogate key linking the returned address row to a specific assignment action; the primary correlation column for downstream joins.
• ADDRESS1, ADDRESS2, ADDRESS3 — the three free-form address lines, each capped at 30 characters.
• COUNTY — the decoded Irish county name, resolved through GET_LOOKUP_MEANING('IE_COUNTY', ...).
• POST_CODE — the decoded Irish postal code, resolved through GET_LOOKUP_MEANING('IE_POSTAL_CODE', ...).
• PHONE_NO — exposed as a literal NULL. The view does not populate a telephone number from any source column; the column exists purely to satisfy the P45 layout contract. Any query filtering or sorting on PHONE_NO will return no rows or an arbitrary ordering.

Common Use Cases and Queries

The view is used to reproduce or audit P45 address blocks for terminated Irish employees, and to validate that address information was archived correctly at the point of P45 generation.

SELECT assignment_action_id
     , address1
     , address2
     , address3
     , county
     , post_code
  FROM apps.pay_ie_p45_address_details
 WHERE assignment_action_id = :p_assignment_action_id;

Because PHONE_NO is a constant NULL, it should never be used as a filter predicate. A typical diagnostic query joins the view back to assignment actions to reconcile archived address data:

SELECT paa.assignment_id
     , a.address1
     , a.county
     , a.post_code
  FROM apps.pay_ie_p45_address_details a
     , apps.pay_assignment_actions paa
 WHERE paa.assignment_action_id = a.assignment_action_id
   AND paa.assignment_id = :p_assignment_id;

Since all filters are embedded in the view definition, no additional REPORT_TYPE or status predicates are required by callers.