Search Results pay_amount

Overview

The PA_PAY_EXT_INTERAFACE_V view is an APPS-owned, VALID database object within the Oracle E-Business Suite PA – Projects product family. It is defined to store and expose the payroll cost list of values used during the transfer of external payroll costs into Oracle Projects. Operationally, the view flattens and joins payroll interface header data, external payroll time-period definitions, payroll interface detail lines, and the corresponding HR organization and assignment records, so that payroll cost entries — for example, those loaded from a third-party payroll system via the external payroll interface — can be reviewed and validated prior to import.

In EBS 12.1.1 and 12.2.2 this view is not a transactional base table but a query-only construct. It is typically consumed by Oracle Projects forms, concurrent programs, and custom reporting or integration logic that needs to reconcile external payroll cost batches against Project and Task distributions. Because it exposes a denormalized, human-readable result set (combining header, period, detail, and HR attributes), it is well suited to LOV-driven screens and ad hoc reporting, but it carries no independent storage and should not be treated as an update target.

Underlying Base Objects

The documented base objects underlying PA_PAY_EXT_INTERAFACE_V are a mix of synonyms, views, and packages:

• PA_PAY_EXT_INTERFACE_HEADER (SYNONYM) — the header record for each payroll interface batch, aliased as IHEADER; supplies interface header ID, payroll ID, payroll batch ID, and the period start/end dates.
• PA_PAY_EXTERNAL_TIME_PERIODS (SYNONYM) — aliased as PAY_PERIOD; supplies the validated time period ID.
• PA_PAY_EXT_INTERFACE_DETAILS (SYNONYM) — aliased as DETAILS; supplies interface line ID, person, assignment, element type, currency, amount, transfer status, rejection code, and retro period dates.
• HR_ORGANIZATION_UNITS (VIEW) — aliased as HR; supplies organization ID and name.
• PER_ASSIGNMENTS_F (VIEW) and PER_PEOPLE_F (VIEW) — aliased as PAF and PPF; provide assignment and person context.
• HR_LOCATIONS (VIEW) — aliased as HL; joined via the assignment location ID.
• HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY (PACKAGES) — supporting HR security and name/date-effective logic.

Joins are enforced on INTERFACE_HEADER_ID (header-to-detail), PAYROLL_ID (header-to-period), truncated TIME_PERIOD_START_DATE / TIME_PERIOD_END_DATE equality between the period and header, BUSINESS_GROUP_ID, PERSON_ID, ASSIGNMENT_ID, ORGANIZATION_ID, and LOCATION_ID. The truncation of the period start and end dates is significant because it permits date-only matching between the payroll period and the interface header, avoiding spurious mismatches caused by time components.

Key Columns

• INTERFACE_HEADER_ID — Primary linkage between the payroll interface header and its detail lines.
• PAYROLL_ID and PAYROLL_BATCH_ID — Identify the payroll and the batch that produced the cost entries.
• TIME_PERIOD_START_DATE / TIME_PERIOD_END_DATE — The reporting period of the interface header; these are the columns that drive the truncation join to PA_PAY_EXTERNAL_TIME_PERIODS and are the attributes a user searching for "time_period_start_date" is normally trying to report on.
• TIME_PERIOD_ID — Surrogate from the external time periods view, resolved from the payroll ID plus the truncated period dates.
• INTERFACE_LINE_ID — Unique identifier of the payroll cost detail line.
• PERSON_ID and ASSIGNMENT_ID — The employee and assignment to which the payroll cost relates.
• ORGANIZATION_ID and NAME — The HR organization and its name for the assignment.
• PAY_SOURCE_ORGANIZATION_ID, PAY_SOURCE_PROJECT_ID, PAY_SOURCE_TASK_ID — The originating organization, project, and task to which the cost is charged.
• PAY_ELEMENT_TYPE_CODE and PAY_CURRENCY_CODE — Element classification and currency of the payroll amount.
• PAY_AMOUNT — The monetary value presented for transfer into Oracle Projects.
• TRANSFER_STATUS_FLAG and REJECTION_CODE — Indicate whether the line transferred successfully and, if not, the reason for rejection.
• RETRO_PERIOD_START_DATE / RETRO_PERIOD_END_DATE — The retroactive coverage period, where applicable.

Common Use Cases and Queries

The principal use cases are review of pending payroll cost batches, identification of rejected lines, reconciliation of transferred amounts to Projects and Tasks, and extraction of payroll costs by reporting period. A typical query filtering on the requested date column is:

• SELECT interface_header_id, payroll_id, payroll_batch_id, time_period_start_date, time_period_end_date, time_period_id, person_id, assignment_id, name, pay_source_project_id, pay_source_task_id, pay_amount, transfer_status_flag FROM apps.pa_pay_ext_interface_v WHERE time_period_start_date = :p_start_date;
• Rejected-line analysis: SELECT interface_line_id, person_id, pay_amount, rejection_code FROM apps.pa_pay_ext_interface_v WHERE transfer_status_flag = 'R';
• Period-level cost roll-up: SELECT time_period_start_date, time_period_end_date, SUM(pay_amount) FROM apps.pa_pay_ext_interface_v GROUP BY time_period_start_date, time_period_end_date;

Because TRUNC is applied to the period date columns in the join, date range predicates on TIME_PERIOD_START_DATE perform predictably at the date level. Applications with large external payroll volumes should filter by PAYROLL_ID or INTERFACE_HEADER_ID in addition to date to limit row counts.