Search Results get_fw_between_dates

Overview

PAY_AU_FOREIGN_WORKERS is an Oracle EBS Payroll package owned by APPS that supports Australian payroll statutory processing for foreign workers. Its responsibilities centre on identifying employees who hold foreign worker status, tracking the dates and periods during which that status applies, and supplying the balance and payment information required for reporting and validation. The package was introduced in 2010 (bug 9147421) and subsequently extended as part of the Foreign Worker Phase II changes and several GCC remediation cycles.

The package is classified as OTHER rather than a public API. It exposes a set of constants, global variables and utility routines that are consumed internally by other Australian payroll packages and by the Foreign Worker concurrent and reporting processes. A key characteristic of the design is the use of assignment status identifiers to mark foreign worker state, rather than relying solely on element or balance-driven logic.

Key Procedures and Functions

The package specification exposes nine documented routines:

• POPULATE_ASG_STATUS_ID — derives and stores the assignment status identifier relevant to foreign worker processing. Its visibility was deliberately made public in version 115.4 to allow callers outside the package to refresh this value.
• CHECK_FOREIGN_WORKER — determines whether a given assignment meets the criteria for foreign worker treatment, using the package constants and the assignment status data.
• GET_FOREIGN_WORKER_DATES — the routine most commonly searched for by developers. It returns the dates associated with foreign worker status for an assignment, allowing callers to establish the effective window during which foreign worker rules apply.
• CHECK_FW_TERMINATED — establishes whether the foreign worker arrangement has ended, typically by inspecting the period of service and assignment status.
• GET_DEFINED_BALANCES — retrieves the defined balances maintained for the assignment that are relevant to foreign worker calculations.
• IS_LE_CHANGED — detects whether the legal employer associated with the assignment has changed, which can affect foreign worker eligibility and reporting.
• GET_FOREIGN_PAYMENT_AMOUNTS — reads the payment amounts associated with foreign worker earnings, using the balance dimensions to resolve the correct period and value.
• GET_FW_BETWEEN_DATES — added in version 115.5 (bug 9950136). It returns the foreign worker records that fall between a supplied pair of dates, complementing GET_FOREIGN_WORKER_DATES.
• Package constants and globals — the specification defines foreign worker type constants, including g_fw_f_type, used throughout the package to branch on the applicable worker category.

Tables Accessed

The package reads and writes a focused set of payroll and HR tables through APPS synonyms:

• PER_ASSIGNMENT_STATUS_TYPES and PAY_ASSIGNMENT_ACTIONS — supply the assignment status identifiers and the historical status changes used to determine foreign worker status.
• PER_PERIODS_OF_SERVICE — provides the start and end dates needed by the date-related functions and termination checks.
• PAY_DEFINED_BALANCES, PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS — resolve the balances and dimensions required by GET_DEFINED_BALANCES and GET_FOREIGN_PAYMENT_AMOUNTS.
• PER_TIME_PERIODS and PAY_PAYROLL_ACTIONS — define the payroll period context within which balances and payments are evaluated.
• HR_SOFT_CODING_KEYFLEX — used to interpret soft-coded key flexfield information attached to assignments or balances.
• DUAL and PLITBLM — a scalar helper select and the PL/SQL table type construct used for internal collection handling.

Usage Notes

PAY_AU_FOREIGN_WORKERS is not intended as a general-purpose integration API. It is referenced by five other packages and is typically invoked from Australian payroll processing logic, foreign worker reporting, and validation routines that must decide whether an assignment qualifies for foreign worker treatment in a given period. Because several routines return date and amount information rather than performing the update themselves, custom code that needs the same answers should call the package rather than re-implementing the derivations.

Developers searching for get_foreign_worker_dates should note that the routine returns the effective window for foreign worker status and is complemented by GET_FW_BETWEEN_DATES for range queries. Both should be called after POPULATE_ASG_STATUS_ID has ensured the status identifier is current. The package uses AUTHID CURRENT_USER, so grants and synonyms must be correct for the calling schema.