Search Results fetch_rl1_xml

Overview

APPS.PAY_CA_RL1_MAG is a Canadian payroll legislative reporting package body that generates the RL-1 annual tax slip (Relevé 1) required by Revenu Québec. In Oracle EBS 12.1.1 and 12.2.2, the package is invoked as part of the Canadian Payroll year-end processing cycle and is responsible for extracting assignment, employer, and employee level data for the reporting period and rendering it into the XML format mandated by the provincial tax authority.

The package body combines report parameter resolution, cursor-driven data retrieval from the payroll action and assignment tables, XML document assembly, and LOB manipulation utilities. Two utility functions, append_to_lob and clob_to_blob, support the construction of large XML payloads by converting and appending character data into BLOB containers suitable for file transmission. The header version string (pycarlmg.pkb 120.119.12020000.10, dated 2013) confirms the module was maintained through the 12.2.x release line.

Key Procedures and Functions

The package exposes twenty-two documented procedures and functions. The principal entry points and their purposes are:

• GET_REPORT_PARAMETERS — Resolves the period, province, and business organization parameters for the RL-1 run, using the payroll_action_id passed from the PYUGEN process driver.
• GET_PARAMETER — Returns an individual report or action parameter used during document generation.
• RANGE_CURSOR and CREATE_ASSIGNMENT_ACT — Establish the driving cursor over assignments and create the assignment action rows required for the reporting action.
• GET_SLIP_SEQ_NO — Determines the sequential slip number assigned to each RL-1 slip in the output file.
• VALIDATE_QUEBEC_NUMBER and CONVERT_SPECIAL_CHAR — Validate the Quebec identification number and normalize special characters for XML-safe output.
• XML_EMPLOYER_START, XML_EMPLOYER_RECORD, RL1XML_EMPLYER_DATA — Emit the employer identification and summary segments of the RL-1 XML document.
• XML_EMPLOYEE_RECORD, XML_FOOTNOTE_BOXO, FETCH_RL1_XML, GET_FINAL_XML, XML_REPORT_END — Produce the employee-level slip detail, footnote boxes, and the assembled final XML document; FETCH_RL1_XML is the fetch routine that returns the consolidated XML for transmission.
• XML_TRANSMITTER_RECORD and END_OF_FILE — Write the transmitter header and end-of-file trailer required by Revenu Québec’s XML schema.
• STORE_FURTHER_INFORMATION, GET_FURTHER_INFORMATION, GET_ARCH_VAL — Persist and retrieve supplementary and archived values used across the reporting run.

Tables Accessed

The package reads and writes several payroll and HR tables through APPS synonyms. PAY_PAYROLL_ACTIONS, PAY_ACTION_INTERLOCKS, PAY_ACTION_INFORMATION, PAY_ASSIGNMENT_ACTIONS, and PAY_ASSIGNMENT_ACTIONS_S provide the action context, action parameters, and assignment-level results for the period. PER_ALL_ASSIGNMENTS_F, PER_ALL_PEOPLE_F, and PER_ADDRESSES supply employee and address details. HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL, and HR_ORGANIZATION_INFORMATION supply employer and establishment information. PAY_CA_LEGISLATION_INFO holds Canadian legislative configuration. FF_ARCHIVE_ITEMS and FF_DATABASE_ITEMS back the archived and database item retrieval routines, while DUAL supports simple scalar queries.

Usage Notes

PAY_CA_RL1_MAG is normally invoked by the Canadian Payroll year-end concurrent programs, which populate PAY_PAYROLL_ACTIONS and pass the resulting payroll_action_id into the package. It is referenced by two other packages, indicating reuse in the broader RL-1 and year-end reporting flow. Customizations should invoke the public routines through the standard concurrent program interface rather than calling the XML generation routines directly, since the LOB assembly and slip sequencing rely on state established earlier in the run.