Search Results get_sqwl_extra_info

Overview

APPS.PAY_US_MMREF_FUNCTION_PKG is a United States legislative payroll utility package delivered with Oracle E-Business Suite, present in both Release 12.1.1 and 12.2.2. The package header identifies its purpose as supporting the generation of magnetic tape W-2 reports in compliance with US legislative requirements, incorporating magtape resilience and the end-of-year reporting design introduced by Oracle. Its functions were initially scoped to the MMREF (Magnetic Media Reporting and Electronic Filing) format and were intended to be extended to additional formats over time.

The package is owned by APPS and is classified as OTHER in the ETRM metadata, meaning it is not registered as a formal public API but is nevertheless callable from custom code and internal Oracle processes. The header is dated 2008 and carries the version identifier 120.0.12010000.1, consistent with the R12 code line. The package is declared AUTHID CURRENT_USER, so it executes with the privileges of the invoking schema rather than as definer-rights APPS code.

Key Procedures and Functions

The ETRM metadata documents three public program units:

• GET_CITY_VALUES — The function targeted by the search term. Its stated purpose is to fetch the city codes and names associated with a given jurisdiction code. It accepts a jurisdiction code and an effective date as inputs, together with a set of additional input parameters, and returns city-related values through several NOCOPY OUT parameters. This routine is the mechanism by which the MMREF W-2 generation process resolves the local city identifier required on the magnetic media file for jurisdictions that levy city-level tax.
• GET_HOURS_WORKED — Added in version 115.2 and subsequently modified in version 115.4 to correct a defect related to SUI hours by state. It supplies hours-worked values used in year-end reporting calculations.
• GET_SQWL_EXTRA_INFO — Created in version 115.5 to calculate the SUI_ER_SUBJ_WHABLE and SUI_ER_PRE_TAX amounts. Version 115.6 added IN OUT parameters to this function. It supports state unemployment insurance employer-level reporting detail.

The NOCOPY hint was added to OUT and IN OUT parameters in version 115.3, reflecting the large parameter payloads handled by these routines.

Tables Accessed

The package reads and writes several tables through APPS synonyms. PAY_US_CITY_NAMES and PAY_US_CITY_TAX_INFO_F supply the city code and name data consumed by GET_CITY_VALUES, with the latter providing the effective-dated city tax information. FF_ARCHIVE_ITEMS, FF_DATABASE_ITEMS, and FF_USER_ENTITIES are FastFormula metadata tables used to resolve and evaluate formula-based balances required for hours and SUI calculations. PAY_ASSIGNMENT_ACTIONS provides assignment-level context, and PLITBLM is the PL/SQL table-to-BLOB utility used to assemble the magtape output payload.

Usage Notes

This package is invoked internally during US year-end W-2 processing and magnetic media file generation rather than from a standard EBS form. It is not referenced by any other documented package, so its callers are Oracle-internal concurrent program logic and report generation code. Custom integrators who need to reproduce MMREF city code resolution or SUI employer detail outside the standard year-end flow can call these functions directly, but must account for the AUTHID CURRENT_USER declaration and the NOCOPY OUT parameters, which require caller-side variable declarations of sufficient size.