The Payroll Archive Reporter (PAR) Process
The Payroll Archive Reporter (PAR) Process
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.
PAR Modes
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.
Overview of the PAR Process
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.
Overview of the Setup Steps
-
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
Create Database Items for Archiving
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.
Archive Database Item Creation: Background
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.
Seeded Generic Archive Routes
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.
Routes with One Context
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
Routes with Two Contexts
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));
Running the Archive Database Item Generator
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));
Contexts for Database Items
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.
Write Formulas
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.
Write Package Procedures For Assignments And Assignment Actions
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.
RANGE_CODE Example
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.
ASSIGNMENT_ACTION_CODE Example
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.
Provide an SRS Definition for the PAR Process
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>.
Populate Rows in the PAY_REPORT_FORMAT_MAPPINGS_F Table
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.
Examples: INITIALIZATION_CODE and ARCHIVE_CODE
INITIALIZATION_CODE
/* 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;
ARCHIVE_CODE
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;