Payment Processes

Payment Processes

After running the Pre-Payments process to prepare the results for payment (according to the payment methods), you produce payments for your employees.

With Oracle Payroll, you can run the following types of payment process:

The payment processes take the unpaid prepayment values allocated to each payment type and produce the required payment file.

You can also record any manual payments you make to a specific employee. These payments are not handled by the Payments processes. Recording a manual payment has the effect of marking the prepayment as paid.

Magnetic Tape Process

The Magnetic Tape process generates the payment due and writes the data to a file on magnetic tape. It is this tape that is taken to the bank for payment.

There are two types of magnetic tape file, which are created differently:

The actual format of these tapes is legislation specific.

The tape process is a simple 'C' harness which calls Oracle stored procedures and FastFormula formulas to produce the required tape file. The routine is generic: you can use it for any task that requires magnetic tape reporting. The actual structure and content of the tape is defined entirely by the stored procedure and a series of formulas.

Some examples that use the routine are:

Note: The order of the entries in the magnetic file is critical. Therefore the Magnetic Tape process cannot run with multiple threads (unlike the PrePayments or Cheque/Check Writer processes).

See also:

The Payroll Archive Reporter (PAR) Process

Running the Magnetic Tape Payments Process

The payroll assignment action creation code is the entry point to the Magnetic Tape Payments process. Employee magnetic tape payments are recorded in Oracle HRMS as payroll and assignment actions with interlocks to the relevant pre-payment assignment actions. The interlocks prevent the pre-payments actions being rolled back while the magnetic tape actions exist.

Third party payments (such as the company's health plan contributions) do not result in payroll and assignment actions, and therefore would use the magnetic tape report interface.

Batch Process Parameters

You run PYUGEN with the following parameters:

PL/SQL Procedure for the Payment Type

The system uses the PL/SQL driving procedure specified for the payment type on the database (for example, <package name>.<procedure name>). The PL/SQL procedure for the Magnetic Tape Writer process must drive off the assignment actions and not further restrict the assignments processed. Further restricting the assignments presents the danger of leaving some magnetic tape assignment actions never processed. When the process first runs the PL/SQL, one of the parameters passed is the payroll action id (PAYROLL_ACTION_ID).

The Magnetic Tape process actions prepayments with an effective date on or before the effective date of the magnetic tape action. The magnetic tape effective date defaults to session date in an AOL environment, and sysdate outside AOL.

Output Filenames

The magnetic tape file generated is named as per the normal file-naming standards:

p<trunc(conc_request_id, 5)>.mf

The file name is padded with zeros if the length of the request id is shorter than five characters, (for example, p03451.mf).

It is written to the $APPLCSF/$APPLOUT directory, if $APPLCSF is defined, and otherwise to $PAY_TOP/$APPLOUT.

Several other files can be produced by this process. You can use these files to audit the assignments that are being processed. The audit files are created in the same way, except that the file extension .a<file_number>. So if a formula returns a value for audit file 6 then a file with the extension .a6 is created in the correct directory using the concurrent request id as described above.

Running Magnetic Tape Reports

Magnetic Tape reports are not recorded as payroll and assignment actions. The entry point is the specific Magnetic Tape code, PYUMAG. The PL/SQL determines which assignments to process.

Mandatory Parameters

Optional Parameters

The optional parameters to the PL/SQL must be tokenized, so that the generic tape writer process can populate the PL/SQL tables for parameter name and parameter value. These tables constitute the interface between the generic writer process and the driving PL/SQL procedure.

See: The PL/SQL Driving Procedure

The magnetic tape action only processes formulas with an effective date on or before the effective date of the magnetic tape action. The magnetic tape effective date defaults to session date, in an AOL environment, and sysdate outside AOL.

Output Filenames

The magnetic tape filename is generated if it is not supplied to the process. The filename is in the format:

o<trunc(conc_request_id, 5)>.mf

When an audit file prefix is not set but the process tries to write to an audit, the concurrent request id is used as the prefix and .out used as the extension. In these circumstances all audit returns are written to this file.

SRS Definitions

Using SRS, the generic tape writer process is defined once as an executable. You can then define any number of concurrent programs that invoke that executable. Each concurrent program can have its own set of parameters, its own hidden parameters, defaults and so on. For example, we can define two concurrent programs:

They would both use the magnetic tape writer executable PYUMAG, each with a hidden parameter specifying the appropriate PL/SQL procedure, and possibly, each with specific parameters. They appear as completely distinct reports to the user. This would be set up in the SRS process interface.

Similarly, magnetic payments can be made to appear as distinct processes to the user - the only difference is that the payment type is the hidden parameter, and the generic code determines the driving PL/SQL procedure from that.

How the Magnetic Tape Process Works

Magnetic tapes are usually broken down into:

The sequence in which the process writes the records to tape follows strictly defined rules. As a result, you can write a piece of code to return the name of the next record to write to tape.

Similarly, the actual records have strict field place and length requirements. For example:

Record Fields
   
Tape Header Batch Id, Company Name, Batch Record Length, and so on
Employee Employee Id, Salary, Age, Job, and so on
Tape Footer No. of Records Processed, Salary Total, and so on

C Harness, PL/SQL, and Formulas

The following figure illustrates the Magnetic Tape process.

the picture is described in the document text

A C code harness performs the file handling (opening, closing and writing to files), and enables the PL/SQL and the formulas to interface.

The driving PL/SQL code sequences records by returning the name of a formula.

Each formula writes one type of record, such as the Tape Header, to tape. It defines the contents of the record.

The process of getting the formula and record name, then writing the record to tape is repeated until all the records are processed.

Context and Parameter Values

The driving PL/SQL determines which type of record is required at any stage of the processing, and uses context and parameter values to communicate with the formula.

The following figure illustrates how the C code acts as an interface between the PL/SQL and formula, and how the data is passed as context values.

the picture is described in the document text

Context Values

Formulas use database items to reference variable values. For example, the employee and assignment number could be different for each run of the formula and record.

The database item is held within the database, which consists of components to make up a SQL statement. As the value could be different for each run of the formula, the 'where' clause of the statement is slightly different. This is done by substituting key values into the 'where' clause that uniquely select the required value. These substitution values are known as context values.

Context values are set by the driving PL/SQL procedure that places the values into a PL/SQL table. The PL/SQL table is passed back to the C code, which in turn places it in the formula structure.

Parameter Values

Parameter values are used to store the variable data to be transferred between the formula and the PL/SQL. For example, the running totals are passed to the formula in this way.

The parameters can be:

Only the driving PL/SQL procedure and the formula can update the values.

The PL/SQL Driving Procedure

The PL/SQL driving procedure determines the format of the magnetic tape file. You can write this procedure from scratch by opening cursors processing a particular formula for each fetch of the cursor, or you can use the generic PL/SQL. The generic PL/SQL drives off the magnetic tape batch tables.

The interface between the 'C' process and the stored procedure makes extensive use of PL/SQL tables. PL/SQL tables are single column tables that are accessed by an integer index value. Items in the tables use indexes beginning with 1 and increasing contiguously to the number of elements. The index number is used to match items in the name and value tables.

The names of the tables used to interface with the PL/SQL procedure are:

The first two tables (pay_mag_tape.internal_prm_names and pay_mag_tape.internal_prm_values) are used to pass parameter details to the PL/SQL and formula. These are reserved for the number of entries in the parameter tables and the formula ID that is to be executed. The second two tables (pay_mag_tape.internal_cxt_names and pay_mag_tape.internal_cxt_values) are used to set the context rules for the database items in the formula. These are reserved for the number of entries in the context tables.

The Generic PL/SQL

The Magnetic Tape process uses generic PL/SQL that drives off several tables that contain cursor names. These cursors and tables control the format of the magnetic tape.

These cursors retrieve three types of data:

Example

Here are two select statements as examples:

cursor business is
select business_group_id,
'DATE_EFFECTIVE=C', effective_start_date
from per_business_groups
cursor assignment is
select 'ASSIGN_NO=P', assignment_id
from pay_assignments

In the above example, the first select (DATE_EFFECTIVE) is a context value that is passed to a subsequent formula. The business_group_id column is retrieved for use in subsequent cursors. It is accessed by using a function described later.

The second select (ASSIGN_NO=P) is used as a parameter.

When the cursor is opened, it assigns rows in a retrieval table that it can select into (the number of rows depends on the number of columns retrieved by the cursor). For example, if the above cursors were used, and the previous example was run, the retrieval table would look like this:

After First Run After Second Run
   
50000 50000
DATE_EFFECTIVE= DATE_EFFECTIVE=C
16-MAR-1997 16-MAR-1997
  ASSIGN_NO=P
  50367

Functions to Access Data

Some cursors require access to data previously selected. This can be achieved in two ways:

Context and Parameter Data

The formula requires two types of data:

The context data is held in PL/SQL tables, which are filled by the PL/SQL with data retrieved by the cursors, as described above. The context rules are inherited to lower levels unless the lower level cursor retrieves a different value for that context name. The PL/SQL always uses the lowest level context value for a particular context. For example, if the second cursor above retrieved a context value for DATE_EFFECTIVE, this value would be used for the formula until the cursor is closed. It is at a lower level in the retrieval table than the previous DATE_EFFECTIVE. When the cursor is closed, the rows in the retrieval table are reclaimed and the DATE_EFFECTIVE context reverts to the first one.

The Parameter data is also held in tables, but unlike context values the values are not level dependent. The formula can access these values by selecting the parameter on the input line. If the formula returns a value for that parameter, it overwrites the entry in the table. If the formula returns a parameter that does not exist, the parameter is entered in the table.

Cursor/Block Table

The driving structure for the package procedure is held in two database tables:

The PAY_MAGNETIC_BLOCKS table is as follows:

Name Null? Type
MAGNETIC_BLOCK_ID NOT NULL NUMBER (9)
BLOCK_NAME NOT NULL VARCHAR2 (80)
MAIN_BLOCK_FLAG NOT NULL VARCHAR2 (30)
REPORT_FORMAT NOT NULL VARCHAR2 (30)
CURSOR_NAME   VARCHAR2 (80)
NO_COLUMN_RETURNED   NUMBER (5)

Example

block_id cursor_name block_name no_of_select_ values main_block type
1 company_curs companies 2 Y CA
2 employee_curs employees 2 N CA
3 assignment_curs assignments 1 N CA

Formula/Record Table

The PAY_MAGNETIC_RECORDS table is as follows:

Name Null? Type
FORMULA_ID NOT NULL NUMBER (9)
MAGNETIC_BLOCK_ID NOT NULL NUMBER (9)
NEXT_BLOCK_ID   NUMBER (9)
LAST_RUN_EXECUTED_MODE NOT NULL VARCHAR2 (30)
OVERFLOW_MODE NOT NULL VARCHAR2 (30)
SEQUENCE NOT NULL NUMBER (5)
FREQUENCY   NUMBER (5)

Example

formula_name block_id seq next_block frequency O/F exec.last
formula 1 1 1 - - N N
formula 2 1 2 2 - N N
formula 3 2 1 - - N N
formula 4 2 2 3 - N N
formula 5 3 1 - - N N
formula 6 2 3 - - N N
formula 7 1 3 - - N N

Formulas/records can be of three general types:

The table columns are as follows:

Using the above specification the formulas could be retrieved in the following sequence:

the picture is described in the document text

The generic PL/SQL procedure identifies which type of report to process. It does this by passing the parameter MAGTAPE_REPORT_ID when calling the process. The previous figure illustrates how MAGTAPE_REPORT_ID=CA is passed when calling the process.

The Formula Interface

Typically, a magnetic tape consists of a number of record types. Oracle suggests having a formula associated with (generating) each record type. The formulas do the following:

A PL/SQL stored procedure provides the main control flow and determines the order in which the formulas are called.

The routine uses FastFormula to prepare records The records are written to an ASCII file in preparation for transfer to magnetic tape. To implement the required actions, there are more formula result rule types. These are listed below:

Result Rule Types Purpose
TRANSFER This transfers the output parameter to the input of the stored procedure. The parameter may or may not be modified by the stored procedure before being used in the next execution of the formula.
WRITE TO TAPE This instructs the process to write the result to the magnetic tape file. This is always a character string that represents the desired record. The writes are performed in the order in which they are returned from the formula.
REPORT FILE This writes the string result to an "audit" file.
ERROR This instructs the process that an ERROR/WARNING has been detected within the formula. Thus the process should handle the error appropriately.

Naming Convention

These are not implemented in the traditional manner using the formula result rules table. They use the naming convention:

WRITE TO TAPE results are named WRITE_<result_name>.

TRANSFER results follow a similar convention, but the result_name part must be the name of the parameter. For example, a result company_total_income would be named transfer_company_total_income.

The REPORT result must identify which file is to be written to. The file number is embedded in the formula return name For example: REPORT1_<result_name> - this writes to report/audit file 1.

Reports

Reports can be written during the production of the magnetic tape file. These reports could be used to check the details that are produced. A number of reports can be created in the same run. The number can be limited by using the ADD_MAG_REP_FILES action parameter in the PAY_ACTION_PARAMETERS table.

Each report is accessed by using a prefix that denotes the file, for example, REPORT1_ to denote report number 1, REPORT2_to denote report number 2, and so on. If a report number is outside the range of the ADD_MAG_REP_FILES value, an invalid return error is reported. The report files are opened as and when needed with the names of the files previously described.

FastFormula Errors

Errors can be of three types:

The actual messages themselves have to be prefixed with the assignment action id or payroll action id. This is done to insert the messages into the PAY_MESSAGE_LINES table. Warning messages are regarded as being at the assignment action level and require the assignment action id. If no id is supplied, the message is only written to the log file. No id must be supplied when running a magnetic tape report, since no actions exist for reports. Only payments have actions.

Example

Here are some examples of the format to use:

Error Message Meaning
ERROR_PAY_TEXT1 = '50122: Unexpected value' - Payroll action id 50122 with message 'Unexpected Value'
ERROR_PAY_TEXT1 = ':Unexpected value' - No payroll action id just a message
ERROR_ASS_TEXT1 = '56988: Unexpected value'  
ERROR_ASS_TEXT1 = 'Unexpected value'  
ERROR_WARN_TEXT1 = '56988: Unexpected value'  
ERROR_WARN_TEXT1 = ':Unexpected value'  

Error Handling

Magnetic tape either fully completes the process, or marks the whole run with a status of error.

Within this there are two types of errors:

The payroll action errors at the end of the run if assignment action level errors are encountered.

A description of the error message is written to the Log file. Also an entry is placed in the PAY_MESSAGE_LINES table if the action id is known.

Example PL/SQL

The following piece of PL/SQL code could be used to format a magnetic tape payment (drives off assignment actions). An alternative to writing a PL/SQL procedure would be to use the generic procedure and populate the batch magnetic tape tables.

Note: This example only works for a business group of 'MAG Test GB' (the legislative formula is for GB only).

create or replace package body pytstm1 
as
CURSOR get_assignments( p_payroll_action_id NUMBER)
 IS
 SELECT ppp.org_payment_method_id,ppp.personal_payment_method_id,
ppp.value, paa.assignment_id
 FROM pay_assignment_actions paa, pay_pre_payments ppp
 WHERE paa.payroll_action_id = p_payroll_action_id
 AND ppp.pre_payment_id = paa.pre_payment_id
 ORDER BY ppp.org_payment_method_id;
Also need to: 
Test that the assignment are date effective?
Order by name or person_number or other ?  
p_business_grp NUMBER;
--
--
PROCEDURE new_formula 
IS
-- 
p_payroll_action_id NUMBER; 
assignment      NUMBER;
p_org_payment_method_id NUMBER;
p_personal_payment_method_id NUMBER;
p_value NUMBER;
--
-- 
FUNCTION get_formula_id ( p_formula_name IN VARCHAR2)
   RETURN NUMBER IS   
p_formula_id NUMBER;  
BEGIN
 SELECT formula_id
 INTO p_formula_id
 FROM ff_formulas_f
 WHERE formula_name = p_formula_name
  AND (business_group_id = p_business_grp
   OR (business_group_id IS NULL
    AND legislation_code = 'GB')
  OR (business_group_id IS NULL AND legislation_code IS NULL)
);
--   RETURN p_formula_id;
-- 
END get_formula_id;
-- 
BEGIN
-- 
pay_mag_tape.internal_prm_names(1) :=
'NO_OF_PARAMETERS'; -- Reserved positions
pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';-- -- 
Number of parameters may be greater than 2 because formulas 
may be -- keeping running totals.-- 
pay_mag_tape.internal_cxt_names(1)  := 'Number_of_contexts'; 
pay_mag_tape.internal_cxt_values(1) := 1;            -- 
Initial value---- IF NOT get_assignments%ISOPEN  THEN
-- New file--   pay_mag_tape.internal_prm_values(1) := 2;
pay_mag_tape.internal_prm_values(2) := get_formula_id
('REPORT_HEADER_1');--   if 
pay_mag_tape.internal_prm_names(3) = 'PAYROLL_ACTION_ID'
  then p_payroll_action_id :=
to_number(pay_mag_tape.internal_prm_values(3));   end if;-- 
OPEN get_assignments (p_payroll_action_id);-- ELSE----
FETCH get_assignments INTO
p_org_payment_method_id,
p_personal_payment_method_id,           p_value,
assignment;--   IF get_assignments%FOUND THEN              
-- New company        
pay_mag_tape.internal_prm_values(1) := 2;
pay_mag_tape.internal_cxt_names(2)  := 'ASSIGNMENT_ID';
pay_mag_tape.internal_cxt_values(2) := assignment;
pay_mag_tape.internal_cxt_names(3)  := 'DATE_EARNED';
pay_mag_tape.internal_cxt_values(3) := to_char (sysdate,'DD-MON-YYYY');
pay_mag_tape.internal_cxt_values(1) := 3;
pay_mag_tape.internal_prm_values(2) := get_formula_id
('ENTRY _DETAIL');
ELSE--       pay_mag_tape.internal_prm_values(1) := 2;
pay_mag_tape.internal_prm_values(2) := get_formula_id
('REPORT_CONTROL_1');
CLOSE get_assignments;
--  END IF;
--END IF;--
END new_formula;
BEGIN   
-- 'MAG test BG' used as an example. The business group could be
-- retrieved using the payroll action id.
 select business_group_id
 into p_business_grp
 from per_business_groups
 where name = 'MAG test BG';
--END pytstm1;

Cheque Writer/Check Writer Process

Note: For ease, we refer to the Cheque Writer/Check Writer process as Cheque Writer throughout this technical essay.

You run the Cheque Writer process to produce cheque payments for unpaid pre-payment actions. Before you run the process, you need to set up certain things, for example, the SRW2 report and the 'order by' option to sequence cheques (if required).

You run Cheque Writer through Standard Reports Submission (SRS). Unlike the Magnetic Tape process, you can have multiple threads in Cheque Writer.

Process Definition

The Cheque Writer process has two distinct steps:

the picture is described in the document text

Step 1 - Create Cheque Assignment Actions

Cheque Writer creates cheque assignment actions for each of the target pre-payments, subject to the restrictions of the parameters specified. The target pre-payments must be unpaid-that is, never been paid-or if they have been paid, then voided.

Cheque Writer creates assignment actions in two stages:

  1. Multiple threads insert ranges of assignment actions, which interlock back to previous actions.

    This happens in the same way as Pre-Payments and Magnetic Tape create assignment actions.

    See: The Process (Pre-Payments)

    See: Running the Magnetic Tape Payments Process

  2. A single thread runs through all the assignment actions in a specific order to update the chunk and cheque number.

    The order is specified by a PL/SQL procedure that you can customize. The thread divides the assignment actions equally into chunks, one chunk per thread. It assigns each action a cheque number.

    See: Using or Changing the PL/SQL Procedure

At this stage, the status of the assignment actions is 'Unprocessed'.

Note: Cheque Writer creates an assignment action and cheque for each target pre-payment of the assignment. Consequently, a single Cheque Writer run can produce more than one cheque for a single assignment.

Step 2 - Submit SRW2 Report

When Cheque Writer has created the assignment actions and interlocks, each thread submits the specified SRW2 report as a synchronously spawned concurrent process. The reports produce files in a specific cheque format.

If the spawned concurrent process is successful, the status of the assignment actions is changed to 'Complete'. If the process fails, the status of the assignment actions is changed to 'In Error'. So, if you resubmit Cheque Writer, it can start at the point of submitting the report.

In this respect, Cheque Writer is similar to the magnetic tape process: the whole process must be successful before the payroll action is Complete. But, while the Magnetic Tape process can mark individual assignment actions In Error, Cheque Writer marks all assignment actions In Error.

Batch Process Parameters

The batch process has a number of parameters users can enter. The definition of the printer type (for example, laser or line printer for the report output) is not a parameter. The default for this is specified as part of the registration of the concurrent process for the report. Consult your Oracle Applications System Administrators Guide for more information on printers and concurrent programs.

Cheque Numbering

The cheque stationery onto which the details are printed is typically authorized, and has the cheque number preprinted on it. It is common in the UK for there to be a further cheque number box which is populated when the cheque is finally printed. It is this number that the generating payroll system uses.

Usually, these two numbers are the same. It is not known whether any clearing system invalidates the cheque if they are not. However, it seems likely that if you need to trace the path of a cheque through a clearing system, the preprinted cheque number would prove most useful, and hence, it should be the number recorded for the cheque payment on the payroll system.

It is a user's responsibility to ensure that the cheque numbers used by Cheque Writer (and recorded on the system) are identical to those on the preprinted stationery. In certain circumstances, you might want to use numbers that are not the same. In this case, the cheque number recorded by the payroll system is simply a different cheque identifier from the preprinted cheque number.

Note: Preprinted stationery usually comes in batches, for example, boxes of 10000. Therefore, you may want to use different ranges of cheque numbers when printing off cheques at the end of the pay period. For example, you may have to print off 2500 cheques using the remains of one box (numbered 9500 - 10000) and then an unopened box (numbered 20001 - 30000). Cheque Writer uses the start and end cheque number parameters to enforce these ranges.

Voiding and Reissuing Cheques

Under some circumstances, users might need to void a cheque and optionally issue a replacement. For example, an employee loses their cheque and requests a replacement, or you discover that the employee has previously left employment and should not have been paid. In both cases the first step is to void the cheque. This activity may also involve contacting the bank that holds the source account and cancelling the cheque.

Note: Voiding a cheque does not prevent the payment from being made again.

Voiding and reissuing a cheque is different from rolling back and reprinting a cheque. You void a cheque when it has actually been issued and you need to keep a record of the voided cheque. You rollback when a cheque has not yet been issued. For example, during a print run your printer might jam on a single cheque and think it has printed more than one. These cheques have not been issued and the batch process should be rolled back and restarted for those actions.

Depending on the reason for voiding, a user may want to issue another cheque. This is known as 'reissuing'. This requires no extra functionality. The user has the choice of issuing a manual cheque and recording the details online, or of resubmitting the batch process for automatic printing.

You cannot reprocess actions that have already been paid. The process only creates payments for those actions that have never been paid, or have been voided.

Mark for Retry

Cheque Writer actions can be marked for retry. As with the rollback process, when marking a Cheque Writer payroll action for retry, the user can determine which assignment actions are to be marked by specifying an assignment set parameter.

Marking cheque assignment actions for retry does not remove the assignment actions, but simply updates their status to 'Marked For Retry' (standard behavior for all action types). The assigned cheque numbers are left unaltered. Hence, on retry, Cheque Writer generates a new print file.

The reason for this is that we cannot reassign cheque numbers for assignment actions of a cheque payroll action. The payroll action stores the start and end cheque numbers specified. If different ranges of numbers could be used on several retries of the payroll action, then some of its assignment actions could be assigned numbers outside the range held on the payroll action.

Rolling Back the Payments

If a user wants to assign new cheque numbers, they must rollback the Cheque Writer payroll and assignment actions, and submit a separate batch request.

Note: It usually makes sense to roll back all of the cheques. If you mark individual cheques for retry, their cheque numbers are unlikely to be contiguous and it would be difficult to print these on the correct preprinted cheque stationery.

SRW2 Report

You may need to set up the format for the cheque stationery. The SRW2 report, invoked by Cheque Writer is passed in two parameters:

For this purpose, the report must take the parameters named PACTID and CHNKNO.

By the time the report is run, the appropriate assignment actions have been created and cheque numbers assigned according to the order specified in the order by parameter.

The report must drive off the assignment actions for the cheque payroll action and chunk number specified. It must generate one cheque for each assignment action. The cheque number is held directly on the assignment action, while the amount to be paid is retrieved from the associated pre-payment.

The report must maintain the order of the cheques when printed out, the report must process the assignment actions in order of cheque number.

Example SELECT statement

The following select statement illustrates how to drive a report:

select to_number(ass.serial_number),
ass.assignment_action_id,
round(ppa.value,2),
ppf.last_name,
ppf.first_name
from per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions ass,
pay_pre_payments ppa
where ass.payroll_action_id =:PACTID 
and ass.chunk_number =:CHNKNO
and     ppa.pre_payment_id = ass.pre_payment_id 
and     ass.assignment_id = paf.assignment_id 
and     ass.status <>'C'
and     paf.person_id = ppf.person_id
order by  to_number(ass.serial_number)

Registering the Report

Once the SRW2 report is written, you must register it as a Cheque Writer report. This is similar to registering 'Cash Analysis Rules' for the Pre-Payments process.

You must also define a new Lookup Value for the Type of 'CHEQUE_REPORT'. Enter the report name and description.

In a similar way to the Magnetic Tape process, the file generated by the report is named:

p<trunc(conc_request_id,5)>.c<chunk_number>

The file name is padded with zeros if the length of the request id is shorter than five characters, for example, p03451.cl.

It is written to the $APPLCSF/$APPLOUT directory, if $APPLCSF is defined, and otherwise to $PAY_TOP/$APPLOUT.

If Cheque Writer is run with multiple threads, it produces several files. This is because Cheque Writer assignment actions are split into several chunks, one chunk per thread. So, each thread can pick a chunk and process it. This is done to improve performance on machines with multiple processors. For example, if there are four threads processing, there would be four files produced:

Cheque Writer creates a fifth file (by the process that concatenates the four files into one). The name of this file is p03451.ch.

Using or Changing the PL/SQL Procedure

Cheque Writer updates the assignment actions with the cheque and chunk number in the sequence determined by a PL/SQL procedure, called anonymously from the process. A default PL/SQL procedure is provided with the generic product - pay_chqwrt_pkg.chqsql.

The default sort order is:

  1. Organization

  2. Department

  3. Surname

  4. First name

You can change this procedure to set up several different sorting orders by criteria, denoted by a flag passed to the procedure. You should copy the core select statement, and alter the subquery to order according to your own business needs.

The advantage of giving access to the whole SQL statement is that the cheques can be ordered by any criteria. If we had only allowed specification of an ORDER BY clause, then the ordering would have been restricted to attributes on those tables already in the FROM clause of the core SQL statement.

To set up new order by requirements, change the pay_chqwrt_pkg.chqsql package procedure. You could add the following IF statement when checking the procname variable:

else if procname = 'NEW ORDER BY' then 
   sqlstr := 'select ....'

The select statement could be a copy of the existing select statement but with the order by clause changed. The select statement must return the assignment action's rowid.

Based on this information the assignment action can be given a serial/cheque number and assigned to a chunk.

Similarly, as with the SRW2 report the new order by option has to be registered before it can be used. This is done in a similar manner except that the Lookup Type is CHEQUE PROCEDURE. Enter a meaningful description in the Meaning field and the name of the option, for example NEW ORDER BY, in the Description field.

Cash Process

The Cash process indicates to the system that payment has been made, and prevents pre-payments from being rolled back.

Note: This is a UK-only process.