Using the Payroll Archive Reporting (PAR) process, you can produce complex payroll reports on employee assignments on a periodic basis, for example at the end of the tax year, or for each tax quarter. You can submit these reports to a tax authority or other governmental body using magnetic tape.
If necessary, you can archive the data reported on exactly as it appears in the reports. This covers the possibility that the payroll department, or external authorities receiving the reports, may need to review the data at some future time.
If archiving is not required, you can still retain a record of the production of the reports and which employee assignments were included in them.
The primary use of the PAR process is for magnetic tape reporting, but you can also use it (in Archive mode) for reports delivered using Oracle Report Writer.
The generic PAR process described here may not meet the payroll reporting requirements of all HRMS payroll localizations. Therefore your localization team may have made changes such as extending the data reported on to include payroll actions, payrolls, or organizations.
To support flexibility in its use, PAR can be run in three different modes:
Magnetic Tape with Archive
In this mode, PAR archives the values needed for reporting in the FastFormula archive tables (FF_ARCHIVE_ITEMS and FF_ARCHIVE_ITEM_CONTEXTS). It then produces a report on magnetic tape based on the archived values.
Archive
In this mode, PAR only archives values needed for reporting in the FastFormula archive tables.
Having run the PAR process in Archive mode, you can extract data from the FastFormula archive tables using either Oracle Report Writer or a magnetic tape process.
Magnetic Tape without Archive
In this mode, PAR produces a report on magnetic tape and maintains a record of the report production (in the table PAY_PAYROLL_ACTIONS) and/or records of the individual assignments reported on (in the table PAY_ASSIGNMENT_ACTIONS).
Note: When you produce magnetic tape reports using the alternative process PYUMAG, there is no record of the report production.
Notice that running PAR in Archive mode and then in Magnetic Tape without Archive mode is convenient if you need to produce a number of reports by magnetic tape, each of which requires a subset of a large set of data. All the data can be archived at once in Archive mode, and then the individual reports can be produced for magnetic tape delivery in Magnetic Tape without Archive mode.
The PAR process operates as follows:
It creates a payroll action with associated assignment actions. In these actions, PAR code evaluates live database items (that is, items that point to live tables) representing the data needed for a payroll report. The PAR code uses contexts for the database items as necessary.
When run in the Archiver or Magnetic Tape with Archiver modes, PAR then stores the results of the database evaluations in the FastFormula archive tables (FF_ARCHIVE_ITEMS and FF_ARCHIVE_ITEM_CONTEXTS).
When run in the Magnetic Tape with Archiver or Magnetic Tape without Archiver modes, PAR code retrieves values from the archive tables by evaluating archive database items, and includes the values in reports delivered by magnetic tape.
Decide on the employee data to report on and to archive, and the formatting of the reports.
Create the archive and live database items that are needed to produce the data in the reports, setting contexts for them as necessary.
For Archive mode or Magnetic Tape with Archive mode, write formulas that determine which database items are to be archived. For Magnetic Tape with Archiver and Magnetic Tape without Archiver modes, write formulas that format strings as required by tape formats, and provide error and warning messages to users.
See: Write Formulas
Write package procedures that determine the assignments and assignment actions for PAR to process for the reports.
See: Write Package Procedures for Assignments and Assignment Actions
Provide an SRS (Standard Report Submission) definition from which users can launch the PAR process.
Identify your custom reports, formulas and package procedures to the system by making the appropriate entries in the table PAY_REPORT_FORMAT_MAPPINGS_F.
See: Populate Rows in the PAY_REPORT_FORMAT_MAPPINGS_F Table
For its archiving function, PAR uses both live database items (which point at live tables), and archive database items (which point at the archive tables to retrieve archived data). For each archive database item, there must be a corresponding live database item. You are responsible for creating the archive database items, and for any live database items you need that do not already exist.
For example, for the archive database item A_INCOME_TAX_YTD referenced in a formula, there must be a live database item INCOME_TAX_YTD. PAR runs this live database item and places the value in the archive table FF_ARCHIVE_ITEMS.
The entity relationship diagram below shows the relationship of the PAR tables to other tables in generic HRMS:
The FF_ARCHIVE_ITEMS table records a snapshot of what particular database items evaluate to on a run of PAR.
The creation of archive database items includes the creation of archive routes. You define these in FF_ROUTES, with definition texts that are simple select statements from the two tables FF_ARCHIVE_ITEM_CONTEXTS and FF_ARCHIVE_ITEMS. Notice however that you must define these based on the number of contexts being passed into the routes, and the data type of the contexts. There are however, seeded Archive Routes, which you may be able to make use of rather than defining your own; these are detailed in the next section.
You define the route context usages in the table FF_ROUTE_CONTEXT_USAGES. The recommended way to do this is to retrieve from FF_CONTEXTS the context IDs that the live and archive routes require, and then define new route context usages based on the new archive routes. The route parameter is always defined based on the new archive route and a parameter name of User Entity ID.
Here is an example of a more complex archive route:
l_text := 'ff_archive_items target, ff_archive_item_contexts fac, ff_archive_item_contexts fac1 where target.user_entity_id = &U1 and target.context1 = &B1 /* context assignment action id */ and fac.archive_item_id = target.archive_item_id and fac.context = to_char(&B2) /* 2nd context of source_id */ and fac1.archive_item_id = target.archive_item_id
The simple structure underlying this relatively complex route is still evident. Each context added just represents a further join to FF_ARCHIVE_ITEM_CONTEXTS.
The seeded generic archive routes fall into two categories: routes that have only one context (using ASSIGNMENT_ACTION_ID) and routes that have two contexts.
For the generic archive routes with one context, three datatypes are supported for that context, and therefore three such routes are automatically created when you run the automatic database item generator:
A Character Context route, mapping onto a FF_CONTEXT of datatype 'T' (Text). This is named ARCHIVE_SINGLE_CHAR_ROUTE.
A Numeric Context route, mapping onto a FF_CONTEXT of datatype 'N' (Number). This is named ARCHIVE_SINGLE_NUMBER_ROUTE.
A Date Context route, mapping onto a FF_CONTEXT of datatype 'D' (Date). This is named ARCHIVE_SINGLE_DATE_ROUTE.
Here is the text for ARCHIVE_SINGLE_CHAR_ROUTE:
ff_archive_items target where target.user_entity_id = &U1 and target.context1 = &B1
For the generic archive routes that have two contexts, the automatic database item generator references the table FF_ARCHIVE_ITEM_CONTEXTS, whose column CONTEXT is stored as a Varchar2(30). It makes the assumption that the first context stored in FF_ARCHIVE_ITEMS is a number, and is an assignment action ID. It can seed only one such 'two-context archive route' by decoding the where clause of the generic archive route as follows:
ff_archive_items target, ff_archive_item_contexts context ff_contexts ffc where target.user_entity_id = &U1 and target.context1 = &B1 and target.archive_item_id = context.archive_item_id and ffc.context_id = context.context_id and context.context = decode(ffc.data_type,'T', &B2, 'D', fnd_date.date_to_canonical(&B2), to_char(&B2));
You make several calls to the procedure for running the interface to the archive database item generator, one for each of the database items that you want to archive. The procedure is as follows:
procedure pay_archive_utils.create_archive_dbi( p_live_dbi_name IN VARCHAR2(30), p_archive_route_name IN VARCHAR2(30) DEFAULT NULL, p_secondary_context_name IN VARCHAR2(30));
Using the standard set_context procedure, you set global contexts or assignment level contexts for those database items that require contexts. INITIALIZATION_CODE sets the global contexts for formulas, for example, PAYROLL_ID. ARCHIVE_CODE sets the context for the assignment level contexts, such as ASSIGNMENT_ID.
See: Examples: INITIALIZATION_CODE and ARCHIVE_CODE.
To run PAR in Archive or Magnetic Tape with Archive mode, you write formulas that identify the database items used in the archiving process. To run PAR in Magnetic Tape with Archive or Magnetic Tape without Archive modes, you must write formulas to format strings as required, and to provide warnings and errors.
The PAR process uses the entry existing for a report in the column REPORT_FORMAT of the table PAY_REPORT_FORMAT_MAPPING_F to find the formulas associated with the appropriate magnetic tape format in the table PAY_MAGNETIC_BLOCKS.
See also: Populate Rows in the PAY_REPORT_FORMAT_MAPPINGS_F Table.
You must code two package procedures as follows:
The RANGE_CODE procedure, to specify ranges of assignments to be processed in the archive.
The ASSIGNMENT_ACTION_CODE procedure, to create the assignment actions to be processed.
This package procedure returns a select statement. This select statement returns the person_id that has the assignment for which PAR must create an assignment action.
-- procedure range_cursor (pactid in number, sqlstr out varchar2) is begin -- sqlstr := 'select distinct person_id from per_people_f ppf, pay_payroll_actions ppa where ppa.payroll_action_id = :payroll_action_id and ppa.business_group_id = ppf.business_group_id order by ppf.person_id'; -- end range_cursor;
Note: There must be one and only one entry of :payroll_action_id in the string, and the statement must be, order by person_id.
This package procedure further restricts and creates the assignment action.
-- procedure action_creation(pactid in number, stperson in number, endperson in number, chunk in number) is -- CURSOR c_state IS SELECT ASG.assignment_id assignment_id FROM per_assignments_f ASG, pay_payroll_actions PPA WHERE PPA.payroll_action_id = pactid AND ASG.business_group_id = PPA.business_group_id AND ASG.person_id between stperson and endperson AND PPA.effective_date between ASG.effective_start_date and ASG.effective_end_date ORDER BY ASG.assignment_id; -- lockingactid number; begin for asgrec in c_state loop -- -- Create the assignment action to represent the person / tax unit -- combination. -- select pay_assignment_actions_s.nextval into lockingactid from dual; -- -- insert into pay_assignment_actions. hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id, pactid,chunk, NULL); end loop; end action_creation; --
Note: Four values are passed into the procedure. Start and End person MUST be used to restrict the creation here, as these are used for multithreading. Similarly, chunk must also be used and passed to the insact procedure. This actually creates the action.
The PAR process is a batch process that users start from the Submit Requests window. You need to set up the SRS definition for your process. The parameters for this definition are as follows:
Parameter Name | Mandatory? |
---|---|
report_type | Yes |
report_qualifier | Yes |
start_date | No * |
effective_date | No * |
report_category | Yes |
business_group_id | Yes |
magnetic_file_name | No |
report_file_name | No |
legislative_parameters | No * |
* The PAR process requires the start_date and effective_date. However, these can be set either by entries to the standard parameters or by using special legislative parameters START_DATE and END_DATE. These special parameters are passed to the parameter legislative_parameters in the form START_DATE=<date> and END_DATE=<date>.
You control PAR processing by entries you make in the table PAY_REPORT_FORMAT_MAPPINGS_F. The columns for this table are as shown in the following table:
Column Name | Type | Comments |
---|---|---|
REPORT_TYPE | NOT NULL VARCHAR2(30) | A short name of the report. Example: SQWL (for State Quarterly Wage Listing) |
REPORT_QUALIFIER | NOT NULL VARCHAR2(30) | A qualifying name for the report. Example: for SQWL it could be the state name (such as Texas or California). |
REPORT_FORMAT | NOT NULL VARCHAR2(30) | A foreign key to the PAY_MAGNETIC_BLOCKS table. Needed when running in ALL modes. |
EFFECTIVE_START_DATE | NOT NULL DATE | |
EFFECTIVE_END_DATE | NOT NULL DATE | |
RANGE_CODE | VARCHAR2(60) | The name of a package procedure that you code to specify ranges of assignments to be processed in the archive. For example code, see: Write Package Procedure for Assignments and Assignment Actions. |
ASSIGNMENT_ACTION_CODE | VARCHAR2(60) | The name of a package procedure that you code to create the assignment actions to be processed. For example code, see: Write Package Procedure for Assignments and Assignment Actions. |
INITIALIZATION_CODE | VARCHAR2(60) | A package procedure that sets any global contexts needed for the lifetime of the archiving. Will likely be used infrequently, but you must create the procedure (see: Contexts for Database Items and Examples: INITIALIZATION_CODE and ARCHIVE_CODE. If no value is entered in this column, PAR performs no archiving. |
ARCHIVE_CODE | VARCHAR2(60) | Sets contexts at the assignment action level to be used during the archive. Will likely be used instead of INITIALIZATION_CODE. See: Contexts for Database Items and Examples: INITIALIZATION_CODE and ARCHIVE_CODE. |
MAGNETIC_CODE | VARCHAR2(60) | The standard generic magnetic tape driving PL/SQL procedure (see: Magnetic Tape Process, Oracle HRMS Payroll Processing Management Guide). To produce the magnetic tape, PAR uses REPORT_FORMAT as a foreign key to the table PAY_MAGNETIC_BLOCKS. If no value is entered for MAGNETIC_CODE, PAR does not produce a magnetic tape. |
REPORT_CATEGORY | NOT NULL VARCHAR2(30) | Indicator of the media type. Naming standards are: RT - Reel to Reel Tape SD - Floppy Disk REPORT - Paper Report ARCHIVE - Archive |
REPORT_NAME | VARCHAR2(60) | This remains null for runs in the Magnetic Tape with Archive, Archive, and Magnetic Tape without Archive modes. Available for future use with other possible modes. |
SORT_CODE | VARCHAR2(60) | Entered only when processing a report for which the delivery vehicle is Oracle Report Writer. Enter the name of a package procedure, which you have coded, that returns the assignment actions in the order they should be processed in. |
The key to this table is REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
/* Name : archinit Purpose : This performs the US specific initialization section. */ procedure archinit(p_payroll_action_id in number) is jurisdiction_code pay_state_rules.jurisdiction_code%TYPE; l_state VARCHAR2(30); begin null; end archinit;
Note: This code sets the contexts by assignment action. There are two ways of setting contexts, one using the set_context function, the other using the PL/SQL context table. The context table is used only when contexts can have multiple values, as in this example for SOURCE_ID and SOURCE_TEXT.
/* Name : archive_data Purpose : This performs the ZA specific employee context setting. */ procedure archive_data(p_assactid in number, p_effective_date in date) is asgid pay_assignment_actions.assignment_id%type; l_count number; l_context_no number; aaseq number; aaid number; paid number; cursor cursars is select distinct code from pay_za_irp5_bal_codes where code in (4001, 4002, 4003, 4004, 4005, 4006, 4007); cursor curclr is select distinct nvl(pet.element_information1, '&&&') element_information1 from pay_element_types_f pet, pay_element_classifications pec, pay_assignment_actions paa, pay_payroll_actions ppa where paa.assignment_action_id = p_assactid and pec.classification_name = 'Deductions' and pec.classification_id = pet.classification_id and ppa.payroll_action_id = paa.payroll_action_id and exists (select '' from pay_assignment_actions paa2, pay_payroll_actions ppa2, pay_run_results prr where paa2.assignment_id = paa.assignment_id and paa2.payroll_action_id = ppa2.payroll_action_id and paa2.assignment_action_id = prr.assignment_action_id and prr.element_type_id = pet.element_type_id and ppa2.effective_date between ppa.start_date and ppa.effective_date ); begin SELECT aa.assignment_id into asgid FROM pay_assignment_actions aa WHERE aa.assignment_action_id = p_assactid; l_context_no := pay_archive.g_context_values.sz; for i in 1..l_context_no loop pay_archive.g_context_values.name(i) := NULL; pay_archive.g_context_values.value(i) := NULL; end loop; pay_archive.g_context_values.sz := 0; l_count := 0; /* Set up the assignment id, date earned and tax unit id contexts */ l_count := l_count + 1; pay_archive.g_context_values.name(l_count) := 'ASSIGNMENT_ID'; pay_archive.g_context_values.value(l_count) := asgid; SELECT MAX(paa.action_sequence) INTO aaseq FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_classifications pac, pay_payroll_actions ppa_arch, pay_assignment_actions paa_arch WHERE paa_arch.assignment_action_id = p_assactid and paa_arch.payroll_action_id = ppa_arch.payroll_action_id and paa.assignment_id = paa_arch.assignment_id AND paa.payroll_action_id = ppa.payroll_action_id AND ppa.action_type = pac.action_type AND pac.classification_name = 'SEQUENCED' AND ppa.effective_date between ppa_arch.start_date and ppa_arch.effective_date and exists (select '' from pay_payroll_actions ppa2, pay_assignment_actions paa2, pay_run_results prr, pay_element_types_f pet where ppa2.time_period_id = ppa.time_period_id and ppa2.payroll_action_id = paa2.payroll_action_id and paa2.assignment_action_id = prr.assignment_action_id and prr.element_type_id = pet.element_type_id and ppa2.effective_date between pet.effective_start_date and pet.effective_end_date and paa2.assignment_id = paa.assignment_id and pet.element_name = 'ZA_Tax_On_Lump_Sums') and not exists (select '' from pay_assignment_actions paa3, ff_archive_items fai, ff_user_entities fue where paa3.assignment_id = paa_arch.assignment_id and paa_arch.payroll_action_id = paa3.payroll_action_id and paa3.assignment_action_id = fai.context1 and fai.user_entity_id = fue.user_entity_id and fue.user_entity_name = 'A_PAY_PROC_PERIOD_ID' and fai.value = ppa.time_period_id); if aaseq is null then SELECT MAX(paa.action_sequence) INTO aaseq FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_classifications pac WHERE paa.assignment_id = asgid AND paa.payroll_action_id = ppa.payroll_action_id AND ppa.action_type = pac.action_type AND pac.classification_name = 'SEQUENCED' AND ppa.effective_date <= p_effective_date; end if; SELECT assignment_action_id, payroll_action_id INTO aaid, paid FROM pay_assignment_actions WHERE assignment_id = asgid AND action_sequence = aaseq; l_count := l_count + 1; pay_archive.g_context_values.name(l_count) := 'ASSIGNMENT_ACTION_ID'; pay_archive.g_context_values.value(l_count) :=aaid ; pay_archive.balance_aa := aaid; l_count := l_count + 1; pay_archive.g_context_values.name(l_count) := 'PAYROLL_ACTION_ID'; pay_archive.g_context_values.value(l_count) :=paid ; for clrrev in curclr loop l_count := l_count + 1; pay_archive.g_context_values.name(l_count) := 'SOURCE_TEXT'; pay_archive.g_context_values.value(l_count) := clrrev.element_information1; end loop; for sarrec in cursars loop l_count := l_count + 1; pay_archive.g_context_values.name(l_count) := 'SOURCE_ID'; pay_archive.g_context_values.value(l_count) := sarrec.code; end loop; - pay_archive.g_context_values.sz := l_count; - end archive_data;