Results for “hr_organization_units_pk”

30 results




Overview
Explore in AI Chat

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

Overview

APPS.PAY_JP_PRE_TAX_V is a reporting view in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2 that consolidates Japanese pre-tax payroll processing details into a single query surface. The view joins payroll action records, element and consolidation set definitions, assignment actions, person and assignment data, and organization unit descriptions to present statutory deductions and premiums required under Japanese payroll law — including Health Insurance (HI), Childcare Insurance (CI), Workers' Compensation (WP), Workers' Property Fund (WPF), Unemployment Insurance (UI), Workmen's Accident Insurance (WAI), and Income Tax (ITAX), together with Local Tax (LTAX) and Special Local Tax amounts.

The view serves as a denormalized read layer for Japanese statutory reporting, payroll reconciliation, and downstream integration. Its role is presentation-oriented: rather than enforcing new business logic, it resolves codes to meanings via FND_MESSAGE and HR_LOOKUPS, and resolves organization identifiers to names, so that reports and extracts can be produced without further joins at the reporting layer.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects: FND_MESSAGE (PACKAGE), FND_NUMBER (PACKAGE), HR_ALL_ORGANIZATION_UNITS_VL (VIEW), HR_API (PACKAGE), HR_LOOKUPS (VIEW), PAY_ACTION_INTERLOCKS (SYNONYM), PAY_ASSIGNMENT_ACTIONS (SYNONYM), PAY_CONSOLIDATION_SETS (SYNONYM), PAY_ELEMENT_SETS (SYNONYM), PAY_JP_PRE_TAX (VIEW), PAY_JP_SWOT_NUMBERS (SYNONYM), PAY_PAYROLL_ACTIONS (SYNONYM), PER_ALL_ASSIGNMENTS_F (SYNONYM), and PER_ALL_PEOPLE_F (SYNONYM).

The core transactional source is PAY_JP_PRE_TAX, which holds the Japanese pre-tax line attributes such as taxable salary and material amounts, premium splits between employee and employer, and category codes. PAY_JP_SWOT_NUMBERS supplies the Social Welfare Office (SWOT) numbering used for local tax district reporting. PAY_PAYROLL_ACTIONS and PAY_ASSIGNMENT_ACTIONS provide the action header and assignment-level context, including action type, effective date, date earned, and time period. PAY_CONSOLIDATION_SETS and PAY_ELEMENT_SETS identify the consolidation and element groupings to which the action belongs. PER_ASSIGNMENTS_F and PER_ALL_PEOPLE_F supply the person identity columns (employee number, full name, global name, local name). Organization unit names originate from HR_ALL_ORGANIZATION_UNITS_VL. Package references FND_MESSAGE, FND_NUMBER, and HR_API are used for message retrieval and numeric handling.

The view's inline hint clause requests ordered nested-loop execution and explicitly indexes each organization unit alias with HR_ORGANIZATION_UNITS_PK, which is the primary key index on HR_ORGANIZATION_UNITS referenced six times (HOU1 through HOU6) for the HI, WP, WPF, UI, WAI, and ITAX organization lookups. This index-directed plan explains why a search for hr_organization_units_pk surfaces this view: the index appears repeatedly in the view's access path.

Key Columns

Common Use Cases and Queries

Typical scenarios include Japanese statutory premium reconciliation for a payroll period, income and local tax reporting by district and SWOT number, and verification of employee versus employer premium splits by organization. The view is also used to validate that each tax type is attributed to the correct HR organization unit, which is why the plan repeatedly probes HR_ORGANIZATION_UNITS_PK.

The following query extracts pre-tax details for a given payroll action and date range:

SELECT employee_number, full_name, action_status_meaning, salary_category_meaning, taxable_sal_amt, taxable_mat_amt, hi_prem_ee, hi_prem_er, itax, itax_adjustment, ltax, ltax_lumpsum FROM apps.pay_jp_pre_tax_v WHERE payroll_id = :p_payroll_id AND effective_date BETWEEN :p_start AND :p_end ORDER BY employee_number;

A second scenario reconciles employer and employee premiums by organization:

SELECT hi_organization_id, name, SUM(hi_prem_ee) ee_total, SUM(hi_prem_er) er_total FROM apps.pay_jp_pre_tax_v WHERE business_group_id = :p_bg AND effective_date >= :p_start GROUP BY hi_organization_id, name;

A third use case isolates local tax by district and SWOT number for remittance files:

SELECT employee_number, ltax_district_code, ltax_swot_no, ltax, ltax_lumpsum, sp_ltax_district_code, sp_ltax_swot_no, sp_ltax FROM apps.pay_jp_pre_tax_v WHERE ltax IS NOT NULL OR sp_ltax IS NOT NULL ORDER BY ltax_district_code, employee_number;

Because the view is a read-only projection, queries against it inherit the ordered nested-loop execution plan and the repeated HR_ORGANIZATION_UNITS_PK index lookups mandated by the inline hints. Report consumers should expect six organization unit joins per qualifying row and should constrain by business group, payroll, or effective date to bound result sets.